Better late than sorry.. :) 
Author Message
 Better late than sorry.. :)

This is a multi-part message in MIME format.

------=_NextPart_000_001C_01C1D4DB.44FCD070
Content-Type: text/plain;
        charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

you need to edit the function pgr_replica_table
and under

spi_exec "alter table ${quotechar}[quote $1]${quotechar} add column pgr_gri=
d int8"

add:

spi_exec "alter table ${quotechar}[quote $1]${quotechar} add constraint pgr=
_gridnotnull check (pgr_grid notnull)"

This took care of that problem for me :) im using PGSQL 7.2..

hope it works for you..

In reply to:



  c.. Subject: pgreplicator in 7.2b4=20
  d.. Date: 15 Dec 2001 01:44:02 -0300=20

Hello:

I'm trying to get pgreplicator running under 7.2b4.

When issuing the "select pgr_replica_table('some_table')", it fails
with:

ERROR:  Adding NOT NULL columns is not implemented.
        Add the column, then use ALTER TABLE ADD CONSTRAINT.

I tried to make sense of what's doing and ran into this:

DEBUG:  query: alter table "prueba" add column pgr_grid int8 not null

Under 7.1, the column is appended to the table without the NOT NULL
constraint, silently ignoring the constraint. Under 7.2 the command is
noted as invalid, as it should be (I wonder why the pgreplicator
developers never noted that the NOT NULL constraint never made into the
table). But it breaks pgreplicator. For now I'm sticking to 7.1.3, but
I'm wondering whether the "right" behaviour is to wait for ALTER TABLE
... NOT NULL to be implemented.

I'm currently trying to hack pgreplicator into issuing the right ALTER
TABLE ADD CONSTRAINT, but cannot find the right way to add a NOT NULL
clause. In CREATE TABLE, NOT NULL is only a column_constraint, and ALTER
TABLE seems to like only table_constraint syntax (or so suggest the \h
command in pqsl and the reference docs). I cannot figure it out, and the
ALTER TABLE reference for 7.2 in Bruce's site doesn't give hints on it.

Also, if adding NOT NULL constraints is not supported by ALTER TABLE ADD
CONTRAINT, I think the error message for ALTER TABLE ADD COLUMN should
not suggest otherwise.

There is a workaround by Joel Burton
( http://www.***.com/ ) by hacking
pg_attribute manually, but the gotchas are dangerous (and anyway, now
that I think of it, what value should one populate the new NOT NULL
column with?).

--=20

Alexis Maldonado
Instructional Technologist
Distance Learning Department
Central Texas College
Phone #: 254- 526-1743

------=_NextPart_000_001C_01C1D4DB.44FCD070
Content-Type: text/html;
        charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.3315.2870" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2>you need to edit the function pgr_replica_table</FONT><=
/DIV>
<DIV><FONT size=3D2>and under</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT size=3D1>
<P>spi_exec "</FONT><FONT color=3D#0000ff size=3D1>alter</FONT><FONT size=
=3D1>=20
</FONT><FONT color=3D#ff0000 size=3D1>table</FONT><FONT size=3D1> ${quotech=
ar}[quote=20
$1]${quotechar} add column pgr_grid int8"</FONT></P>
<P><FONT size=3D1><FONT size=3D2>add:</FONT></P>
<P>spi_exec "</FONT><FONT color=3D#0000ff size=3D1>alter</FONT><FONT size=
=3D1>=20
</FONT><FONT color=3D#ff0000 size=3D1>table</FONT><FONT size=3D1> ${quotech=
ar}[quote=20
$1]${quotechar} add </FONT><FONT color=3D#ff0000 size=3D1>constraint</FONT>=
<FONT=20
size=3D1> pgr_gridnotnull check (pgr_grid notnull)"</P></FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>This took care of that problem for me :) im using PGSQL=
=20
7.2..</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT size=3D2>hope it works for you..</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>In reply to:</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT size=3D2>
<UL>
  <LI><STRONG>From</STRONG>: <STRONG>Alvaro Herrera &lt;<A=20

Quote:
>=20

  <LI><STRONG>To</STRONG>: <STRONG>PgSQL-General &lt;<A=20

</A>&gt;</STRONG>=20

  <LI><STRONG>Subject</STRONG>: <STRONG>pgreplicator in 7.2b4</STRONG>=20
  <LI>Date: 15 Dec 2001 01:44:02 -0300 </LI></UL><!--X-Head-of-Message-End-=
-><!--X-Head-Body-Sep-Begin--></FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Hello:<BR><BR>I'm trying to get pgreplicator running un=
der=20
7.2b4.<BR><BR>When issuing the "select pgr_replica_table('some_table')", it=
=20
fails<BR>with:<BR><BR>ERROR:&nbsp; Adding NOT NULL columns is not=20
implemented.<BR>  Add the column, then use ALTER TABLE ADD CONSTRAINT.<BR><B=
R>I=20
tried to make sense of what's doing and ran into this:<BR><BR>DEBUG:&nbsp;=
=20
query: alter table "prueba" add column pgr_grid int8 not null<BR><BR>Under =
7.1,=20
the column is appended to the table without the NOT NULL<BR>constraint, sil=
ently=20
ignoring the constraint. Under 7.2 the command is<BR>noted as invalid, as i=
t=20
should be (I wonder why the pgreplicator<BR>developers never noted that the=
 NOT=20
NULL constraint never made into the<BR>table). But it breaks pgreplicator. =
For=20
now I'm sticking to 7.1.3, but<BR>I'm wondering whether the "right" behavio=
ur is=20
to wait for ALTER TABLE<BR>... NOT NULL to be implemented.<BR><BR>I'm curre=
ntly=20
trying to hack pgreplicator into issuing the right ALTER<BR>TABLE ADD=20
CONSTRAINT, but cannot find the right way to add a NOT NULL<BR>clause. In C=
REATE=20
TABLE, NOT NULL is only a column_constraint, and ALTER<BR>TABLE seems to li=
ke=20
only table_constraint syntax (or so suggest the \h<BR>command in pqsl and t=
he=20
reference docs). I cannot figure it out, and the<BR>ALTER TABLE reference f=
or=20
7.2 in Bruce's site doesn't give hints on it.<BR><BR>Also, if adding NOT NU=
LL=20
constraints is not supported by ALTER TABLE ADD<BR>CONTRAINT, I think the e=
rror=20
message for ALTER TABLE ADD COLUMN should<BR>not suggest=20
otherwise.<BR><BR><BR>There is a workaround by Joel Burton<BR>(<A=20
href=3D" http://www.***.com/ ;>http:/=/techdocs.postgresql.org/techdocs/updatingcolumns.php</A>)=20
by hacking<BR>pg_attribute manually, but the gotchas are dangerous (and any=
way,=20
now<BR>that I think of it, what value should one populate the new NOT=20
NULL<BR>column with?).<BR><BR>-- <BR>Alvaro Herrera=20

<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT size=3D2>Alexis Maldonado<BR>Instructional Technologist<BR>Dista=
nce=20
Learning Department<BR>Central Texas College<BR>Phone #: 254-=20
526-1743</FONT></DIV></BODY></HTML>

------=_NextPart_000_001C_01C1D4DB.44FCD070--



Sun, 19 Sep 2004 02:16:38 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. Sorry,sorry! Import and Export

2. Sorry,sorry! Import and Export

3. Query - how to find later dates that latest in detail table

4. Sorry, @ADJ should be @PMT, mis-spell.

5. sorry about that

6. Win '95 Clients and SQL 6.5 and TCP/IP Socked (sorry about formatting on previous)

7. Sorry, but HELP!

8. Question (I'm a Newbie, Sorry)

9. Sorry for being newbie...

10. A slight oversight? Sorry need more help

11. Sorry. Access 2000 conversion problem


 
Powered by phpBB® Forum Software