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

From: "Jeffrey Baker" <jwbaker(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: pg_dump fails to include sequences, leads to restore fail in any version
Date: 2008-06-17 16:53:13
Message-ID: fd145f7d0806170953n380b6cadvfe14d6ca384da647@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2008-06-17 17:29:56 Cleaning up cross-type arithmetic operators
Previous Message Kris Jurka 2008-06-17 16:25:09 Re: sh -> pl