Thread: bigserial problem

<br /><font face="sans-serif" size="2">pgAdmin reports a SQL error when attempting to add a bigserial column to an
existingtable.</font><br /><br /><br /><font face="sans-serif" size="2">server: CentOS 4.1 and Postgre 7.4</font><br
/><fontface="sans-serif" size="2">client: XP Sp 2 and pgAdmin 1.2.2</font><br /><br /><font face="sans-serif"
size="2">Addingthe column produces this SQL statement:</font><br /><br /><font face="sans-serif" size="2">CREATE
SEQUENCEpublic.tblenglish_in_trnas_id_seq;</font><br /><font face="sans-serif" size="2">ALTER TABLE
tblenglish_in</font><br/><font face="sans-serif" size="2">   ADD COLUMN trnas_id int8;</font><br /><font
face="sans-serif"size="2">ALTER TABLE tblenglish_in</font><br /><font face="sans-serif" size="2">   ALTER COLUMN
trnas_idSET DEFAULT nextval('public.tblenglish_in_trnas_id_seq'::text);</font><br /><font face="sans-serif"
size="2">INSERTINTO pg_depend(classid, objid, objsubid, refclassid, refobjid, refobjsubid, deptype)</font><br /><font
face="sans-serif"size="2">SELECT cl.oid, seq.oid, 0, cl.oid, 17154::oid, attnum, 'i'</font><br /><font
face="sans-serif"size="2">  FROM pg_class cl, pg_attribute, pg_class seq</font><br /><font face="sans-serif" size="2"> 
JOINpg_namespace sn ON sn.OID=seq.relnamespace</font><br /><font face="sans-serif" size="2"> WHERE
cl.relname='pg_class'</font><br/><font face="sans-serif" size="2">  AND seq.rel</font><br /><br /><font
face="sans-serif"size="2">and results in this error:</font><br /><br /><font face="sans-serif" size="2">ERROR:  column
seq.reldoes not exist</font><br /><br /><br /><font face="sans-serif" size="2">If there is a comment, a differene error
results:</font><br/><br /><font face="sans-serif" size="2">CREATE SEQUENCE public.tblenglish_in_trans_id_seq;</font><br
/><fontface="sans-serif" size="2">ALTER TABLE tblenglish_in</font><br /><font face="sans-serif" size="2">   ADD COLUMN
trans_idint8;</font><br /><font face="sans-serif" size="2">ALTER TABLE tblenglish_in</font><br /><font
face="sans-serif"size="2">   ALTER COLUMN trans_id SET DEFAULT
nextval('public.tblenglish_in_trans_id_seq'::text);</font><br/><font face="sans-serif" size="2">INSERT INTO
pg_depend(classid,objid, objsubid, refclassid, refobjid, refobjsubid, deptype)</font><br /><font face="sans-serif"
size="2">SELECTcl.oid, seq.oid, 0, cl.oid, 17154::oid, attnum, 'i'</font><br /><font face="sans-serif" size="2">  FROM
pg_classcl, pg_attribute, pg_class seq</font><br /><font face="sans-serif" size="2">  JOIN pg_namespace sn ON
sn.OID=seq.relnamespace</font><br/><font face="sans-serif" size="2"> WHERE cl.relname='pg_class'</font><br /><font
face="sans-serif"size="2">  AND seq.relCOMMENT ON COLUMN tblenglish_in.trans_id IS 'id of transmission';</font><br
/><br/><br /><font face="sans-serif" size="2">ERROR:  syntax error at or near "ON" at character 530</font><br /><br
/><br/><font face="sans-serif" size="2">It appears that the SQL that pgAdmin is building is cut off. Maybe the buffer
beingwritten to is too small?</font><br /><br /><font face="sans-serif" size="2">Here's the SQL for creating a table
witha bigserial:</font><br /><font face="sans-serif" size="2">CREATE TABLE test</font><br /><font face="sans-serif"
size="2">(</font><br/><font face="sans-serif" size="2">   test bigserial</font><br /><font face="sans-serif" size="2">)
WITHOUTOIDS;</font><br /><br /><font face="sans-serif" size="2">This is much shorter than the otherer SQL
statement.</font><br/><br /><font face="sans-serif" size="2"><br /> Thank you,<br /><br /> Richard Morrison<br /> Sr.
SoftwareEngineer<br /> ATX II, LLC<br /> "In Rich We Trust!"</font> 

rich.morrison@atxinc.com wrote:
> 
> pgAdmin reports a SQL error when attempting to add a bigserial column to 
> an existing table.

I checked this, it works.

> 
> Adding the column produces this SQL statement:
> 
> CREATE SEQUENCE public.tblenglish_in_trnas_id_seq;
> ALTER TABLE tblenglish_in
>    ADD COLUMN trnas_id int8;
> ALTER TABLE tblenglish_in
>    ALTER COLUMN trnas_id SET DEFAULT 
> nextval('public.tblenglish_in_trnas_id_seq'::text);
> INSERT INTO pg_depend(classid, objid, objsubid, refclassid, refobjid, 
> refobjsubid, deptype)
> SELECT cl.oid, seq.oid, 0, cl.oid, 17154::oid, attnum, 'i'
>   FROM pg_class cl, pg_attribute, pg_class seq
>   JOIN pg_namespace sn ON sn.OID=seq.relnamespace
>  WHERE cl.relname='pg_class'
>   AND seq.rel

Not quite. The resulting sql query can be taken from the SQL page of the 
dialog. What you present here is probably what's logged in pgadmin.log, 
which might get truncated to 1k (AFAIR) for technical reasons.

Regards,
Andresa

<br /><font face="sans-serif" size="2"><br /> Thank you,<br /><br /> Richard Morrison<br /> Sr. Software Engineer<br />
ATXII, LLC<br /> "In Rich We Trust!"</font><br /><br /><font size="2"><tt>pgadmin-support-owner@postgresql.org wrote on
10/04/200501:33:06 PM:<br /><br /> > rich.morrison@atxinc.com wrote:<br /> > > <br /> > > pgAdmin
reportsa SQL error when attempting to add a bigserial column to <br /> > > an existing table.<br /> > <br />
>I checked this, it works.<br /></tt></font><br /><font size="2"><tt>Are you using the same program version that I
am?It fails on my computer with the previously stated error. I'm using the US English, Win32, verion 1.2.2.0 build
installedwith the msi installer.</tt></font><br /><br /><font size="2"><tt>> <br /> > > <br /> > >
Addingthe column produces this SQL statement:<br /> > > <br /> > > CREATE SEQUENCE
public.tblenglish_in_trnas_id_seq;<br/> > > ALTER TABLE tblenglish_in<br /> > >    ADD COLUMN trnas_id
int8;<br/> > > ALTER TABLE tblenglish_in<br /> > >    ALTER COLUMN trnas_id SET DEFAULT <br /> > >
nextval('public.tblenglish_in_trnas_id_seq'::text);<br/> > > INSERT INTO pg_depend(classid, objid, objsubid,
refclassid,refobjid, <br /> > > refobjsubid, deptype)<br /> > > SELECT cl.oid, seq.oid, 0, cl.oid,
17154::oid,attnum, 'i'<br /> > >   FROM pg_class cl, pg_attribute, pg_class seq<br /> > >   JOIN
pg_namespacesn ON sn.OID=seq.relnamespace<br /> > >  WHERE cl.relname='pg_class'<br /> > >   AND seq.rel<br
/>> <br /> > Not quite. The resulting sql query can be taken from the SQL page of the <br /> > dialog. What
youpresent here is probably what's logged in pgadmin.log, <br /> > which might get truncated to 1k (AFAIR) for
technicalreasons.<br /></tt></font><br /><font face="sans-serif" size="2">Nope. I copied the statement from the SQL tab
fromthe properties dialog of a table I created earlier.</font><br /><br /><font size="2"><tt>I copied this one from the
samedialog as well:</tt></font><br /><br /><font size="2"><tt>CREATE SEQUENCE public.tbltest_s_seq;</tt></font><br
/><fontsize="2"><tt>ALTER TABLE tbltest</tt></font><br /><font size="2"><tt>   ADD COLUMN s int8;</tt></font><br
/><fontsize="2"><tt>ALTER TABLE tbltest</tt></font><br /><font size="2"><tt>   ALTER COLUMN s SET DEFAULT
nextval('public.tbltest_s_seq'::text);</tt></font><br/><font size="2"><tt>INSERT INTO pg_depend(classid, objid,
objsubid,refclassid, refobjid, refobjsubid, deptype)</tt></font><br /><font size="2"><tt>SELECT cl.oid, seq.oid, 0,
cl.oid,17474::oid, attnum, 'i'</tt></font><br /><font size="2"><tt>  FROM pg_class cl, pg_attribute, pg_class
seq</tt></font><br/><font size="2"><tt>  JOIN pg_namespace sn ON sn.OID=seq.relnamespace</tt></font><br /><font
size="2"><tt> WHEREcl.relname='pg_class'</tt></font><br /><font size="2"><tt>  AND
seq.relname='tbltest_s_seq'</tt></font><br/><font size="2"><tt>  AND sn.nspname='public'</tt></font><br /><font
size="2"><tt> ANDALTER TABLE tbltest</tt></font><br /><font size="2"><tt>   ADD COLUMN test varchar(30);</tt></font><br
/><br/><font size="2"><tt>Notice the 'ANDALTER'? Part of the previous query is definately missing.</tt></font><br /><br
/><fontsize="2"><tt>Clicking ok on the dialog gives the error: ERROR:  syntax error at or near "ANDALTER" at character
509.</tt></font><br/><br /><font size="2"><tt>Running the SQL statement in the SQL window gives the error: ERROR:
 syntaxerror at or near "ANDALTER" at character 521.</tt></font><br /><br /><br /><br /><font size="2"><tt>> <br />
>Regards,<br /> > Andresa<br /> > <br /> > ---------------------------(end of
broadcast)---------------------------<br/> > TIP 9: In versions below 8.0, the planner will ignore your desire to<br
/>>        choose an index scan if your joining column's datatypes do not<br /> >        match<br /></tt></font> 

rich.morrison@atxinc.com wrote:
> 
>
>  >
>  > I checked this, it works.
> 
> Are you using the same program version that I am? It fails on my 
> computer with the previously stated error. I'm using the US English, 
> Win32, verion 1.2.2.0 build installed with the msi installer.

No, I used CVS head, the 1.4beta1-to-be.
That code didn't change for quite a while, so I don't know what should 
be different. Anyway, please try the snapshot version. Somehow the sql 
string seems to be truncated while it is constructed, that sounds really 
weird.

Regards,
Andreas