BUG #4636: dump/restore of table inheritance fails with serial colums - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      4636
Logged by:          Shane Caraveo
Email address:      shanec@activestate.com
PostgreSQL version: 8.3.5
Operating system:   osx 10.5 and linux
Description:        dump/restore of table inheritance fails with serial
colums
Details:

Given two tables with inheritance:

CREATE TABLE child
(
  id serial NOT NULL,
  ....
)

CREATE TABLE parent
(
  id serial NOT NULL,
  ....
)

-- alter table done at some point after table creation
ALTER TABLE child INHERIT parent;

When I pg_dump and restore (using psql < dump_file), the sequence for the id
column in the child table points to the sequence of the parent, rather than
it's own, in pgadmin I see:


CREATE TABLE child
(
  id integer NOT NULL DEFAULT nextval('parent_id_seq'::regclass),
  ...
)

Using the following fixes the problem, pg_dump should handle this somehow.

ALTER TABLE child
    ALTER COLUMN id DROP DEFAULT,
    ALTER COLUMN id SET DEFAULT nextval('child_id_seq'::regclass);

The closest reference I could find to this bug is in
http://archives.postgresql.org/pgsql-bugs/2007-11/msg00246.php

However, this is still happening in 8.3.5 on both osx and linux.