Thread: pg_dump + serial + sequence problem

Hi,

I found a problem with pg_dump in pg 8.0 and  8.1  ( Postgres 8.2 works
fine)

Scenario 1:

CREATE TABLE teste
(
   id serial,
   campo character(10),
   CONSTRAINT pk_teste PRIMARY KEY (id)
) WITHOUT OIDS;


 CREATE TABLE will create implicit sequence "teste_id_seq" for serial
column "teste.id"
Then for some reason, you don't want that id to be auto-generated by
serial anymore, but want to use a sequence
that will managed for your application, then :

ALTER TABLE teste  ALTER COLUMN id DROP DEFAULT;

The implicit sequence teste_id_seq isn't dropped, and you think: since I
will need to use a sequence, I can the teste_id_seq that is good to remember
where it is used.

But if we do a pg_dump of this squema (versions 8.0.3 and 8.1.4 tested)
the SEQUENCE IS NOT RECREATED in the sql script generated.

In the other hand, if we do

Scenario 2:

CREATE TABLE teste
(
   id integer not null,
   campo character(10),
   CONSTRAINT pk_teste PRIMARY KEY (id)
) WITHOUT OIDS;


CREATE SEQUENCE teste_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

and then run pg_dump, now the sequence is correctly created by sql script.

I found this problem when migrating version from pg 8.0 to pg 8.2
Unfortunately, I run pg_dump of 8.0 instead of 8.2 to make backup, but
if someone will use then only for backup the database in 8.0 and 8.1 ,
this problem will occur when try to restore backup.


By the way in the Scenario 1 if we drop the table teste, the sequence is
automatically droped (in PG 8.2.4 this occurs too)
even the column id isn't referencing the sequence teste_id_seq anymore.


--
Luiz K. Matsumura
Plan IT Tecnologia Informática Ltda.

"Luiz K. Matsumura" <luiz@planit.com.br> writes:
> CREATE TABLE teste
> (
>    id serial,
>    ...
> ALTER TABLE teste  ALTER COLUMN id DROP DEFAULT;

This isn't supported before 8.2, since as you found out pg_dump is
unable to reproduce the situation in the database --- the sequence is
still marked as "owned by" the column even though there's no default
anymore.  We aren't going to try to fix that behavior in the older
branches.

            regards, tom lane

Hi, Tom
Thanks for reply

I understood, that this isn't so critical, but may causes surprises, so
I reported for someone pay atention that this can occur.
By the way I try to drop the sequence, but I can't because they still
owned by the column. So I can't be able to remove this
reference without drop de column and recreate it.


Tom Lane wrote:
> "Luiz K. Matsumura" <luiz@planit.com.br> writes:
>
>> CREATE TABLE teste
>> (
>>    id serial,
>>    ...
>> ALTER TABLE teste  ALTER COLUMN id DROP DEFAULT;
>>
>
> This isn't supported before 8.2, since as you found out pg_dump is
> unable to reproduce the situation in the database --- the sequence is
> still marked as "owned by" the column even though there's no default
> anymore.  We aren't going to try to fix that behavior in the older
> branches.
>
>             regards, tom lane
>
>
>

--
Luiz K. Matsumura
Plan IT Tecnologia Informática Ltda.