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
