Re: [HACKERS] 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@sss.pgh.pa.us> wrote:

Alvaro Herrera <alvherre@commandprompt.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.

-jwb