Thread: BUG #3869: A scenario where pg_dump doesn't dump sequence

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

"guillaume (ioguix) de Rorthais" <ioguix@free.fr> writes:
> 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.

This is fixed (along with lots of other corner cases for altered serial
columns) in 8.2.  In prior releases, serial columns are best treated
as black boxes.

            regards, tom lane