A problem with pg_dump?
От | Alessio Bragadini |
---|---|
Тема | A problem with pg_dump? |
Дата | |
Msg-id | 3AF12483.BAE1CDB7@albourne.com обсуждение исходный текст |
Ответы |
Re: A problem with pg_dump?
|
Список | pgsql-hackers |
I have a table with a FK on itself: in fact a record may depend on another table ("pig's ear" :-) I may run into a problem dumping/restoring using pg_dump, PostgreSQL 7.1.0. Here's a simplification of the table: provo=# SELECT version(); version --------------------------------------------------------------PostgreSQL 7.1 on alphaev67-dec-osf4.0f, compiled by cc -std provo=# CREATE TABLE t1 (id serial, val text, ref integer references t1(id)); provo=# \d t1 Table "t1"Attribute | Type | Modifier -----------+---------+-----------------------------------------------id | integer | not null default nextval('"t1_id_seq"'::text)val | text |ref | integer | Index: t1_id_key If I perform some UPDATEs after the INSERTs, rows in the table are not "ordered" (in a physical sense) according to the serial id, as obvious: provo=# INSERT INTO t1 (val) VALUES ('A'); INSERT 2361407 1 provo=# INSERT INTO t1 (val, ref) VALUES ('B', 1); INSERT 2361408 1 provo=# SELECT oid,* from t1; oid | id | val | ref ---------+----+-----+-----2361407 | 1 | A |2361408 | 2 | B | 1 (2 rows) provo=# UPDATE t1 SET val = 'A+' WHERE id = 1; UPDATE 1 provo=# SELECT oid,* from t1; oid | id | val | ref ---------+----+-----+-----2361408 | 2 | B | 12361407 | 1 | A+ | (2 rows) Now, if I dump *in INSERT mode and only the data* the table, the ordering makes it unusable: -- -- Data for TOC Entry ID 5 (OID 2361370) TABLE DATA t1 -- \connect - alessio INSERT INTO t1 (id,val,ref) VALUES (2,'B',1); INSERT INTO t1 (id,val,ref) VALUES (1,'A+',NULL); which fails since row '1' is not defined while entering '2', because I want to put an older database's data into a newer (compatible) structure, so FK triggers and other stuff is working. Is there a different solution that disabling FK or editing (argh!) the dump? It should work fine if rows would be dumped according to oid. Can this be considered a bug? -- Alessio F. Bragadini alessio@albourne.com APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-2-755750 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925
В списке pgsql-hackers по дате отправления: