Thread: Bug #918: pg_dump problem w/ SEQUENCE
laurent faillie (l_faillie@yahoo.com) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
pg_dump problem w/ SEQUENCE
Long Description
Hi all,
in order to correct bug #899, I have downloaded the current snapshot and I discover a new trouble w/ pg_dump.
If you have a sequence in your database, pg_dump produce something like :
CREATE SEQUENCE seq_id_fch
START WITH
INCREMENT BY 1
MAXVALUE 2147483647
NO MINVALUE
CACHE 1;
and psql rise following error :
ERROR: parser: parse error at or near "WITH" at character 38
PS: I have downloaded the snapshot monday 24/03/2003
Bye
Laurent
Sample Code
No file was uploaded with this report
I just did 'CREATE SEQUENCE x' and pg_dump produced:
CREATE SEQUENCE x
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
which has the proper value after START WITH. Any ideas why your setup
is different?
---------------------------------------------------------------------------
pgsql-bugs@postgresql.org wrote:
> laurent faillie (l_faillie@yahoo.com) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description
> pg_dump problem w/ SEQUENCE
>
> Long Description
> Hi all,
>
> in order to correct bug #899, I have downloaded the current snapshot and I discover a new trouble w/ pg_dump.
>
> If you have a sequence in your database, pg_dump produce something like :
>
> CREATE SEQUENCE seq_id_fch
> START WITH
> INCREMENT BY 1
> MAXVALUE 2147483647
> NO MINVALUE
> CACHE 1;
>
> and psql rise following error :
>
> ERROR: parser: parse error at or near "WITH" at character 38
>
> PS: I have downloaded the snapshot monday 24/03/2003
>
> Bye
>
> Laurent
>
>
> Sample Code
>
>
> No file was uploaded with this report
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Hi Bruce, I duno where is the problem, because : 1/ if I do a simple CREATE SEQUENCE x; as you do, the output is ok, 2/ It works also if I do a CREATE SEQUENCE x START 1 INCREMENT 1 MAXVALUE 2147483647 MINVALUE 1 CACHE 1; which is the command used to recreate seq_id_fch when I have upgraded to 7.3.2 (full ascii dump / rm of pg's data directory / inidb ...). 3/ the problem is only related to this sequence, other work. Using the pg_dump shipped w/ 7.3.2, it works also, and the output is like seen in point 2. So, it's why I think it's something in the pg_dump's code in the current snapshot. I'm investingating on my side. Bye Laurent PS: it should take some time as my workstation is terribly ssssllloooowwww ... ===== The misspelling master is on the Web. _________ 100 % Dictionnary Free ! / /(/ Dico / / Pleins d'autres fautessur /________/ / http://go.to/destroyedlolo (#######( / http://destroyedlolo.homeunix.org:8080 Quoi, des fautes d'orthographe! Pas possible ;-D. ___________________________________________________________ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com
Ok, I found what's append : 1/ current value of the sequence data are following : scheduling=# select * from seq_id_fch;sequence_name | last_value | increment_by | max_value| min_value | cache_value | log_cnt| is_cycled | is_called ---------------+------------+--------------+------------+-----------+-------------+---------+-----------+-----------seq_id_fch | 1 | 1 | 2147483647 | 1 | 1 | 0 | f | t (1 row) 2/ Line 6083, we have a request to get these informations : SELECT sequence_name, last_value, increment_by, CASE WHEN increment_by > 0 AND max_value = 9223372036854775807 THEN NULL WHEN increment_by < 0 AND max_value = -1 THEN NULL ELSE max_value END AS max_value, CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL WHEN increment_by < 0 AND min_value = -9223372036854775807 THEN NULL ELSE min_value END AS min_value, cache_value, is_cycled, is_called from seq_id_fch;sequence_name | last_value | increment_by | max_value| min_value | cache_value| is_cycled | is_called ---------------+------------+--------------+------------+-----------+-------------+-----------+-----------seq_id_fch | 1 | 1 | 2147483647 | | 1 | f | t As you can see, "min_value" is NULL because CASE WHEN increment_by (=1) > 0 AND min_value (=1) = 1 THEN NULL 3/ Line 6156, we're creating the request appendPQExpBuffer(query, "CREATE SEQUENCE %s\n START WITH %s\n INCREMENT BY %s\n", fmtId(tbinfo->relname), (called ? minv : last), incby); so, with values, it's seq:'seq_id_fch', [called : 1] ? [minv : '' (00000000)], [last : '1' (400303eb)] seq:'x', [called : 0] ? [minv : '' (00000000)], [last : '1' (400303e2)] and we create a request w/ a NULL pointer w/ "seq_id_fch" whereas it's ok for "x". I duno what is the goal of "is_called" field, it's the only difference b/w "x" and "seq_id_fch", and the only thing touching "seq_id_fch" is some granting. Anyway, I think the fault is on request line 6083 because we may create erroneous lines for nothing. Why can't we take directly informations from sequence data ? Bye Laurent ===== The misspelling master is on the Web. _________ 100 % Dictionnary Free ! / /(/ Dico / / Pleins d'autres fautessur /________/ / http://go.to/destroyedlolo (#######( / http://destroyedlolo.homeunix.org:8080 Quoi, des fautes d'orthographe! Pas possible ;-D. ___________________________________________________________ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com
It's my bad. Has to do with the sequence having the default value and *NOT* having been called. See -patches for update to simply skip START WITH in that case. On Tue, 2003-03-25 at 10:08, Bruce Momjian wrote: > I just did 'CREATE SEQUENCE x' and pg_dump produced: >=20 > CREATE SEQUENCE x > START WITH 1 > INCREMENT BY 1 > NO MAXVALUE > NO MINVALUE > CACHE 1; >=20 > which has the proper value after START WITH. Any ideas why your setup > is different? >=20 > -------------------------------------------------------------------------= -- >=20 > pgsql-bugs@postgresql.org wrote: > > laurent faillie (l_faillie@yahoo.com) reports a bug with a severity of 2 > > The lower the number the more severe it is. > >=20 > > Short Description > > pg_dump problem w/ SEQUENCE > >=20 > > Long Description > > Hi all, > >=20 > > in order to correct bug #899, I have downloaded the current snapshot an= d I discover a new trouble w/ pg_dump. > >=20 > > If you have a sequence in your database, pg_dump produce something like= : > >=20 > > CREATE SEQUENCE seq_id_fch > > START WITH=20 > > INCREMENT BY 1 > > MAXVALUE 2147483647 > > NO MINVALUE > > CACHE 1; > >=20 > > and psql rise following error : > >=20 > > ERROR: parser: parse error at or near "WITH" at character 38 > >=20 > > PS: I have downloaded the snapshot monday 24/03/2003 > >=20 > > Bye > >=20 > > Laurent > >=20 > >=20 > > Sample Code > >=20 > >=20 > > No file was uploaded with this report > >=20 > >=20 > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > >=20 > > http://www.postgresql.org/docs/faqs/FAQ.html > >=20 --=20 Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc