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

On Tue, Jun 17, 2008 at 6:31 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Jeffrey Baker escribió:
> >> The table was originally created this way:
>
> > Okay, but was it created on 8.1 or was it already created on an older
> > version and restored? I don't see this behavior if I create it in 8.1
> > -- the field is dumped as SERIAL, unlike what you show.
>
> There's something interesting in the original report:
>
> > --
> > -- Name: transaction_transaction_id_seq; Type: SEQUENCE SET; Schema:
> mercado; Owner: prod
> > --
> >
> > SELECT
> > pg_catalog.setval(pg_catalog.pg_get_serial_sequence('transaction_backup',
> ^^^^^^^^^^^^^^^^^^
> > 'transaction_id'), 6736138, true);
>
> So pg_dump found a pg_depend entry linking that sequence to some table
> named transaction_backup, not transaction. That explains why
> transaction isn't being dumped using a SERIAL keyword --- it's not
> linked to this sequence. But how things got this way is not apparent
> from the stated facts.

Hrmm, I think that's a bit of a red herring. I probably should not have
pasted that part of the dump, because it's misleading. There really is a
table transaction_backup, definition is the same as transaction.

Reading from that part of the dump again, just for clarity:

--
-- Name: transaction_backup; Type: TABLE; Schema: mercado; Owner: prod;
Tablespace:
--

CREATE TABLE transaction_backup (
transaction_id serial NOT NULL,
buyer_account_id integer,
seller_account_id integer,
date date,
item_id integer,
source text
);

ALTER TABLE mercado.transaction_backup OWNER TO prod;

--
-- Name: transaction_transaction_id_seq; Type: SEQUENCE SET; Schema:
mercado; Owner: prod
--

SELECT
pg_catalog.setval(pg_catalog.pg_get_serial_sequence('transaction_backup',
'transaction_id'), 6736139, true);

--
-- Name: transaction; Type: TABLE; Schema: mercado; Owner: prod; Tablespace:

--

CREATE TABLE "transaction" (
transaction_id integer DEFAULT
nextval('transaction_transaction_id_seq'::regclass) NOT NULL,
buyer_account_id integer,
seller_account_id integer,
date date,
item_id integer,
source text
);

ALTER TABLE mercado."transaction" OWNER TO prod;

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.

> One possibility is that Jeffrey is getting bit by this bug or
> something related:
> http://archives.postgresql.org/pgsql-bugs/2006-07/msg00021.php

I don't think it's that one. All this stuff is in the same schema (and in
any case the dump file contains all schemas).

> There are links to some other known serial-sequence problems in 8.1
> in this message:
> http://archives.postgresql.org/pgsql-hackers/2006-08/msg01250.php

That one seems closer to the point.
<http://archives.postgresql.org/pgsql-hackers/2006-08/msg01250.php>

-jwb