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

It is impossible to dump (with pg_dump -Ocx) and restore (with psql) a
database which contains sequences in any of 8.1, 8.2, or 8.3:

[...]

--
-- 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);

--
-- 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
);

[...]

2008-06-16 19:26:41 PDT ERROR: relation "transaction_transaction_id_seq"
does not exist

Why? Because pg_dump mysteriously omits all sequences:

think=# \d transaction_transaction_id_seq
Sequence "mercado.transaction_transaction_id_seq"
Column | Type
---------------+---------
sequence_name | name
last_value | bigint
increment_by | bigint
max_value | bigint
min_value | bigint
cache_value | bigint
log_cnt | bigint
is_cycled | boolean
is_called | boolean

think=# \ds
List of relations
Schema | Name | Type | Owner
---------+------------------------------------+----------+-------
mercado | account_account_id_seq | sequence | prod
mercado | account_stat_account_stat_id_seq | sequence | prod
mercado | category_category_id_seq | sequence | prod
mercado | category_stat_category_stat_id_seq | sequence | prod
mercado | country_country_id_seq | sequence | prod
mercado | country_stat_country_stat_id_seq | sequence | prod
mercado | dict_dict_id_seq | sequence | prod
mercado | expire_icon_expire_icon_id_seq | sequence | prod
mercado | expire_time_expire_time_id_seq | sequence | prod
mercado | fx_fx_id_seq | sequence | prod
mercado | icon_icon_id_seq | sequence | prod
mercado | item_icon_item_icon_id_seq | sequence | prod
mercado | item_item_id_seq | sequence | prod
mercado | item_stat_item_stat_id_seq | sequence | prod
mercado | transaction_transaction_id_seq | sequence | prod
(15 rows)

postgres(at)think:~$ pg_dump -s -n mercado think | grep CREATE\ SEQUENCE
postgres(at)think:~$

Therefore when the restore is attempted, the table using the sequence as
default value cannot be created.

"Jeffrey Baker" <jwbaker(at)gmail(dot)com> writes:
> It is impossible to dump (with pg_dump -Ocx) and restore (with psql) a
> database which contains sequences in any of 8.1, 8.2, or 8.3:

I should think we would have heard about it before now if such a
sweeping claim were true.

What I suspect is that you are using 8.1's pg_dump, and you have tripped
over one of the corner cases that made us redesign dumping of serial
sequences for 8.2. Do you get better results if you dump the problem
database with 8.2 or 8.3 pg_dump?

regards, tom lane

On Tue, Jun 17, 2008 at 10:59 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Jeffrey Baker" <jwbaker(at)gmail(dot)com> writes:
> > It is impossible to dump (with pg_dump -Ocx) and restore (with psql) a
> > database which contains sequences in any of 8.1, 8.2, or 8.3:
>
> I should think we would have heard about it before now if such a
> sweeping claim were true.

Last time this problem came up, in August, you dismissed it somewhat
rudely. So perhaps the lack of reports is due more to perception than any
other thing.

> What I suspect is that you are using 8.1's pg_dump, and you have tripped
> over one of the corner cases that made us redesign dumping of serial
> sequences for 8.2. Do you get better results if you dump the problem
> database with 8.2 or 8.3 pg_dump?
>

What's the corner case exactly? 8.3 dumps it correctly, but that's not
really much of a consolation because I need to restore _this_ dump, not some
other one. It was necessary for me to recreate all the sequences and set
the curvals manually.

Can't the fix be backported to 8.1?

-jwb

Jeffrey Baker escribió:
> On Tue, Jun 17, 2008 at 10:59 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> > "Jeffrey Baker" <jwbaker(at)gmail(dot)com> writes:
> > > It is impossible to dump (with pg_dump -Ocx) and restore (with psql) a
> > > database which contains sequences in any of 8.1, 8.2, or 8.3:
> >
> > I should think we would have heard about it before now if such a
> > sweeping claim were true.
>
> Last time this problem came up, in August, you dismissed it somewhat
> rudely. So perhaps the lack of reports is due more to perception than any
> other thing.

How did you set it up exactly? I have no problem with this situation:

$ psql
Welcome to psql 8.1.10, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

alvherre=# create table foo (a serial);
NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for serial colum
n "foo.a"
CREATE TABLE
alvherre=# \q
$ pg_dump -t foo | psql foo
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
setval
--------
1
(1 row)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Tue, Jun 17, 2008 at 2:43 PM, Alvaro Herrera <alvherre(at)commandprompt(dot)com>
wrote:

> Jeffrey Baker escribió:
> > On Tue, Jun 17, 2008 at 10:59 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > > "Jeffrey Baker" <jwbaker(at)gmail(dot)com> writes:
> > > > It is impossible to dump (with pg_dump -Ocx) and restore (with psql)
> a
> > > > database which contains sequences in any of 8.1, 8.2, or 8.3:
> > >
> > > I should think we would have heard about it before now if such a
> > > sweeping claim were true.
> >
> > Last time this problem came up, in August, you dismissed it somewhat
> > rudely. So perhaps the lack of reports is due more to perception than
> any
> > other thing.
>
> How did you set it up exactly? I have no problem with this situation:
>

[snip]

The table was originally created this way:

CREATE TABLE transaction
(
transaction_id SERIAL PRIMARY KEY,
buyer_account_id INTEGER,
seller_account_id INTEGER,
date DATE,
item_id INTEGER,
source TEXT
);

However, when dumped with pg_dump 8.1, it comes out this way:

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
);

.. and the sequence does not get dumped with it.

-jwb

Jeffrey Baker escribió:

> The table was originally created this way:
>
> CREATE TABLE transaction
> (
> transaction_id SERIAL PRIMARY KEY,
> buyer_account_id INTEGER,
> seller_account_id INTEGER,
> date DATE,
> item_id INTEGER,
> source TEXT
> );

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.

--
-- Name: transaction; Type: TABLE; Schema: public; Owner: alvherre; Tablespace:
--

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

$ pg_dump --version
pg_dump (PostgreSQL) 8.1.10

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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.

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

None of those reports seem to exactly match the described behavior, but
anyway I'd bet a good deal that either the table or the sequence has
been altered in some way since they were created. Given that Jeffrey
says all his sequences fail the same way, it must've been something
he did to all his tables/sequences ...

regards, tom lane

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

"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