Обсуждение: 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