A problem with pg_dump?

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

Responses

Browse pgsql-hackers by date

  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