Thread: Bug #918: pg_dump problem w/ SEQUENCE

Bug #918: pg_dump problem w/ SEQUENCE

From
pgsql-bugs@postgresql.org
Date:
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

Re: Bug #918: pg_dump problem w/ SEQUENCE

From
Bruce Momjian
Date:
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

Re: Bug #918: pg_dump problem w/ SEQUENCE

From
Laurent FAILLIE
Date:
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



Re: Bug #918: pg_dump problem w/ SEQUENCE

From
Laurent FAILLIE
Date:
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



Re: Bug #918: pg_dump problem w/ SEQUENCE

From
Rod Taylor
Date:
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