On Tue, Jun 17, 2008 at 6:31 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> 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
