Re: pg_dump sequence problem

Q Beukes <pgsql-general(at)list(dot)za(dot)net> writes:
> Is this a bug or a feature (PG 8.1.3)?

> If have a two schemas called: cbt and core.
> I have a sequence: core.invoicesids_seq.
> I have a couple of tables in cbt having columns with defaults:
> nextval('core.invoicesids_seq')

> When I dump the database, the tables dumped for "cbt" dont have alter
> commands to set the default values to
> "nextval('core.invoicesids_seq')" again. Those columns are simply
> created as serial fields, and their values set to "1, false".

Let me guess: those columns were originally defined as "serial"s,
and then you hand-modified their default expressions to reference
a different sequence?

pg_dump can still see that they're supposed to be serials (there's
still a dependency to their original sequence in pg_depend), and
so it dumps them that way without noticing that you've messed with
the default.

There are several schools of thought on what to do about this. One says
that a serial column is a black box and you shouldn't be allowed to
change its default. Another thinks that we should try to get rid of the
magic behaviors of serials, rather than add more. And some have
proposed just trying to move the dependency from the column itself to
the default expression, which might or might not make everything work
nicely. It's not been resolved yet, but in the meantime I counsel not
messing with the default of a serial column.

To get out of your immediate problem you could delete the rows in
pg_depend that link those columns to their original sequences. Look for
rows with objid = the table's OID, objsubid = the column's number, and
refobjid = the original sequence's OID. With those gone, pg_dump should
go back to dumping the columns as regular columns.

regards, tom lane