Re: [HACKERS] pg_dump fails to include sequences, leads to restore fail in any version
Jeffrey Baker [EMAIL PROTECTED] 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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)
[EMAIL PROTECTED]:~$ pg_dump -s -n mercado think | grep CREATE\ SEQUENCE
[EMAIL PROTECTED]:~$
Therefore when the restore is attempted, the table using the sequence as
default value cannot be created.
Re: [HACKERS] pg_dump fails to include sequences, leads to restore fail in any version
Jeffrey Baker [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump fails to include sequences, leads to restore fail in any version
On Tue, Jun 17, 2008 at 10:59 AM, Tom Lane [EMAIL PROTECTED] wrote: Jeffrey Baker [EMAIL PROTECTED] 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
Re: [HACKERS] pg_dump fails to include sequences, leads to restore fail in any version
Jeffrey Baker escribió: On Tue, Jun 17, 2008 at 10:59 AM, Tom Lane [EMAIL PROTECTED] wrote: Jeffrey Baker [EMAIL PROTECTED] 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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump fails to include sequences, leads to restore fail in any version
On Tue, Jun 17, 2008 at 2:43 PM, Alvaro Herrera [EMAIL PROTECTED]
wrote:
Jeffrey Baker escribió:
On Tue, Jun 17, 2008 at 10:59 AM, Tom Lane [EMAIL PROTECTED] wrote:
Jeffrey Baker [EMAIL PROTECTED] 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_idINTEGER,
seller_account_id INTEGER,
dateDATE,
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
Re: [HACKERS] pg_dump fails to include sequences, leads to restore fail in any version
Jeffrey Baker escribió: The table was originally created this way: CREATE TABLE transaction ( transaction_id SERIAL PRIMARY KEY, buyer_account_idINTEGER, seller_account_id INTEGER, dateDATE, 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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump fails to include sequences, leads to restore fail in any version
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.
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump fails to include sequences, leads to restore fail in any version
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
