Thread: 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:<br /><br />[...]<br /><br />--<br />-- Name: transaction_transaction_id_seq; Type: SEQUENCE SET; Schema:
mercado;Owner: prod<br /> --<br /><br />SELECT
pg_catalog.setval(pg_catalog.pg_get_serial_sequence('transaction_backup','transaction_id'), 6736138, true);<br /><br
/><br/>--<br />-- Name: transaction; Type: TABLE; Schema: mercado; Owner: prod; Tablespace: <br /> --<br /><br />CREATE
TABLE"transaction" (<br />    transaction_id integer DEFAULT nextval('transaction_transaction_id_seq'::regclass) NOT
NULL,<br/>    buyer_account_id integer,<br />    seller_account_id integer,<br />     date date,<br />    item_id
integer,<br/>    source text<br />);<br /><br />[...]<br /><br />2008-06-16 19:26:41 PDT ERROR:  relation
"transaction_transaction_id_seq"does not exist<br /><br />Why?  Because pg_dump mysteriously omits all sequences:<br
/><br/>think=# \d transaction_transaction_id_seq <br />Sequence "mercado.transaction_transaction_id_seq"<br />   
Column    |  Type   <br />---------------+---------<br /> sequence_name | name<br /> last_value    | bigint<br />
 increment_by | bigint<br /> max_value     | bigint<br /> min_value     | bigint<br /> cache_value   | bigint<br
/> log_cnt      | bigint<br /> is_cycled     | boolean<br /> is_called     | boolean<br /><br />think=# \ds<br
/>                       List of relations<br />  Schema  |                Name                |   Type   | Owner <br
/>---------+------------------------------------+----------+-------<br/> mercado | account_account_id_seq             |
sequence| prod<br /> mercado | account_stat_account_stat_id_seq   | sequence | prod<br />  mercado |
category_category_id_seq          | sequence | prod<br /> mercado | category_stat_category_stat_id_seq | sequence |
prod<br/> mercado | country_country_id_seq             | sequence | prod<br /> mercado |
country_stat_country_stat_id_seq  | sequence | prod<br />  mercado | dict_dict_id_seq                   | sequence |
prod<br/> mercado | expire_icon_expire_icon_id_seq     | sequence | prod<br /> mercado |
expire_time_expire_time_id_seq    | sequence | prod<br /> mercado | fx_fx_id_seq                       | sequence |
prod<br/>  mercado | icon_icon_id_seq                   | sequence | prod<br /> mercado |
item_icon_item_icon_id_seq        | sequence | prod<br /> mercado | item_item_id_seq                   | sequence |
prod<br/> mercado | item_stat_item_stat_id_seq         | sequence | prod<br />  mercado |
transaction_transaction_id_seq    | sequence | prod<br />(15 rows)<br /><br />postgres@think:~$ pg_dump -s -n mercado
think| grep CREATE\ SEQUENCE<br />postgres@think:~$ <br /><br />Therefore when the restore is attempted, the table
usingthe sequence as default value cannot be created.<br /> 

"Jeffrey Baker" <jwbaker@gmail.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

Jeffrey Baker escribió:
> On Tue, Jun 17, 2008 at 10:59 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> > "Jeffrey Baker" <jwbaker@gmail.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
orterminate 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 TABLEsetval 
--------     1
(1 row)

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

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@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.

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

"Jeffrey Baker" <jwbaker@gmail.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 astransaction_id integer
defaultnextval('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