Thread: BUG #5374: NULLed SERIAL improperly dumped

The following bug has been logged online:

Bug reference:      5374
Logged by:          Wojciech Scigala
Email address:      postgresql.org@wojtus.net
PostgreSQL version: 8.1.18
Operating system:   Linux 2.6.18
Description:        NULLed SERIAL improperly dumped
Details:

If a SERIAL type is modified by ALTER TABLE .. DROP NOT NULL, backups made
with pg_dump does not contain this change.

Test were done on (unsupported) 8.1.18, I've checked release notes for
8.1.19 - no fix regarding this found.

Steps to reproduce:

CREATE TABLE bugtest ( nullserial SERIAL );

ALTER TABLE bugtest ALTER nullserial DROP NOT NULL;

INSERT INTO bugtest VALUES ( NULL );

Shell commands:
$ pg_dump -Fc -f dumpfile database
$ pg_restore -c -d database dumpile

Resulting error:
CONTEXT:  COPY bugtest, line 1: "\N" pg_restore [archiver (db)] error
returned by PQendcopy: ERROR:  null value in column "nullserial" violates
not-null constraint



Best regards,

Wojciech

"Wojciech Scigala" <postgresql.org@wojtus.net> writes:
> If a SERIAL type is modified by ALTER TABLE .. DROP NOT NULL, backups made
> with pg_dump does not contain this change.

> Test were done on (unsupported) 8.1.18, I've checked release notes for
> 8.1.19 - no fix regarding this found.

Yeah, this is one of a number of reasons why we decided to stop using
the "SERIAL" construct in pg_dump output in 8.2 and up --- it's just not
capable of dealing with manual meddling with the serial column's
properties.  This is not going to be changed in 8.1.x though.  The best
recommendation before 8.2 is "don't do that".

FWIW, I believe that if you run 8.2 or later pg_dump against this
database and load the output into 8.2 or later server, the state of the
column will be restored properly.  But that behavior depends on ALTER
SEQUENCE OWNED BY which is a command 8.1.x hasn't got; it was invented
specifically to deal with this type of situation.

            regards, tom lane