Re: pg_dump fails to include sequences, leads to restore fail in any version - Mailing list pgsql-hackers
| From | Jeffrey Baker |
|---|---|
| Subject | Re: pg_dump fails to include sequences, leads to restore fail in any version |
| Date | |
| Msg-id | fd145f7d0806171855t1e14f3ecy45d206251de7f62c@mail.gmail.com Whole thread Raw |
| In response to | Re: pg_dump fails to include sequences, leads to restore fail in any version (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: pg_dump fails to include sequences, leads to restore fail in any version
|
| List | pgsql-hackers |
On Tue, Jun 17, 2008 at 6:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
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).
-jwb
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 olderThere's something interesting in the original report:
> 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.So pg_dump found a pg_depend entry linking that sequence to some table
> --
> -- 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);
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
-jwb
pgsql-hackers by date: