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.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
