Обсуждение: A problem with pg_dump?
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
Tom Lane wrote: > It's not much > harder than your given example to construct cases where dumping the rows > in OID order would be wrong too (just takes some UPDATEs). Yes, I figured out myself quickly. :-( > like you may have some pre-release copy of pg_dump that gets this wrong > (the comment format in your example is not exactly like current pg_dump, It's pg_dump from 7.0, I am trying to move my 7.0 installation to a 7.1 database, including all the structure changes that we made in our development system. That's why I am stuck. Is it possible to use pg_dump 7.1 on a 7.0 database? -- 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
Alessio Bragadini <alessio@albourne.com> writes:
> It should work fine if rows would be dumped according to oid. Can this
> be considered a bug?
No; or at least, that solution would be equally buggy. It's not much
harder than your given example to construct cases where dumping the rows
in OID order would be wrong too (just takes some UPDATEs). In fact, I
could easily build a version of your table in which there is a circular
chain of dependencies and so *no* dump order will work.
pg_dump scripts ordinarily turn off foreign-key checking while loading
data, and this sort of consideration is the reason why. It looks to me
like you may have some pre-release copy of pg_dump that gets this wrong
(the comment format in your example is not exactly like current pg_dump,
which seems suspicious). Try pg_dump -V to see what it says.
regards, tom lane
Alessio Bragadini <alessio@albourne.com> writes:
>> Is it possible to use pg_dump 7.1 on a 7.0 database?
> Tried. Nope.
Current CVS pg_dump (grab the nightly snapshot if you don't use CVS,
or wait for 7.1.1 in a day or two) is alleged to be able to work
against a 7.0 database. Give it a try.
regards, tom lane
Tom Lane wrote: > Current CVS pg_dump (grab the nightly snapshot if you don't use CVS, > or wait for 7.1.1 in a day or two) is alleged to be able to work > against a 7.0 database. Give it a try. That would be great, I had plans to wait for 7.1.1 anyway. Thanks -- 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
> Is it possible to use pg_dump 7.1 on a 7.0 database? Tried. Nope. -- 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