From: | Alessio Bragadini <alessio(at)albourne(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | A problem with pg_dump? |
Date: | 2001-05-03 09:27:31 |
Message-ID: | 3AF12483.BAE1CDB7@albourne.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 | 1
2361407 | 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(at)albourne(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Elphick | 2001-05-03 09:28:39 | Unix sockets connection authentication - patch |
Previous Message | Kovacs Zoltan | 2001-05-03 09:18:15 | Re: \c connects as another user instead I want in psql |