Re: pg_dump fails to include sequences, leads to restore fail in any version

"Jeffrey Baker" <jwbaker(at)gmail(dot)com> writes:
> The two tables are defined the same way, but one of them gets dumped with a
> SERIAL declaration and the other gets dumped with a DEFAULT nextval().

> Is it possible that pg_dump became confused if transaction was renamed
> transaction_backup and then redefined? I can't guarantee that did in fact
> happen, but it's within the realm of possibility. I don't see the backup
> table in the sql source code for this product, so it's likely that it was
> created by a user in the course of maintenance.

That might be one component of the reason, but it's not the only one.
If I do

foo=# create table transaction (transaction_id serial);
NOTICE: CREATE TABLE will create implicit sequence "transaction_transaction_id_seq" for serial column "transaction.transaction_id"
CREATE TABLE
foo=# alter table transaction rename to transaction_backup;
ALTER TABLE
foo=# create table transaction (transaction_id serial);
NOTICE: CREATE TABLE will create implicit sequence "transaction_transaction_id_seq1" for serial column "transaction.transaction_id"
CREATE TABLE

then I still see both tables dumped properly with "serial". So
something else was done to the table.

As the above example illustrates, if the second generation of the table
was created using "serial", its sequence would not have been named
exactly 'transaction_transaction_id_seq', because that name was already
in use. I'm suspecting that the second-generation table was actually
NOT created using "serial", but was spelled out as
transaction_id integer default
nextval('transaction_transaction_id_seq'::regclass) not null,
This is one of the cases that 8.1's pg_dump can't handle, since
reloading transaction_backup with a column declared "serial" will
generate a differently-named sequence.

regards, tom lane