The following bug has been logged online:
Bug reference: 3869
Logged by: guillaume (ioguix) de Rorthais
Email address: ioguix@free.fr
PostgreSQL version: 8.1
Operating system: Linux, MacOSX 10.4.10
Description: A scenario where pg_dump doesn't dump sequence
Details:
Hello,
I think I found a bug in pg_dump from PostgreSQL 8.1.
When creating a table with a SERIAL column, thn alter this column as
smallint, pg_dump doesn't create the sequence anymore.
Which naturaly lead to an error when trying to restore the database.
I can reproduce it under pg 8.0 but not in pg 8.2 and 8.3. Moreover, I had
the oportunity to test it under Linux and MacOSX 10.4.10.
Here the steps to reproduce it :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$ sudo su postgres -c '/sw/opt/pg81/bin/psql -p 5431'
Welcome to psql 8.1.11, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=# CREATE DATABASE seq;
CREATE DATABASE
postgres=# \c seq
You are now connected to database "seq".
seq=# CREATE TABLE test (id SERIAL PRIMARY KEY, txt varchar(127) NOT NULL
UNIQUE);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for serial
column "test.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey"
for table "test"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "test_txt_key" for
table "test"
CREATE TABLE
seq=# ALTER TABLE test ALTER id TYPE smallint ;
ALTER TABLE
seq=# \q
$ sudo su postgres -c '/sw/opt/pg81/bin/pg_dump -F c -p 5431 -d seq' >
dump_seq
$ sudo su postgres -c '/sw/opt/pg81/bin/pg_restore -p 5431 -d seq_restore
dump_seq'
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1505; 2604 24586 DEFAULT id
postgres
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"test_id_seq" does not exist
Command was: ALTER TABLE test ALTER COLUMN id SET DEFAULT
nextval('test_id_seq'::regclass);
WARNING: errors ignored on restore: 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Here another way to check this bug, grep doesn't find any CREATE SEQUENCE
with pg_dump 8.1. With pg_dump 8.3, it does:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$ sudo su postgres -c '/sw/opt/pg81/bin/pg_dump -p 5431 -d seq' | grep
'CREATE SEQUENCE'
$ sudo su postgres -c '/sw/opt/pg83/bin/pg_dump -p 5431 -d seq' | grep
'CREATE SEQUENCE'
CREATE SEQUENCE test_id_seq
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--
guillaume (ioguix) de Rorthais
