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@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

Thu, 03 May 2001 12:27:31 +0300
Alessio Bragadini <alessio@albourne.com>
A problem with pg_dump?
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

Thu, 03 May 2001 10:33:43 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: A problem with pg_dump? 
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

Thu, 03 May 2001 17:50:15 +0300
Alessio Bragadini <alessio@albourne.com>
Re: A problem with pg_dump?
> 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

Thu, 03 May 2001 18:19:59 +0300
Alessio Bragadini <alessio@albourne.com>
Re: A problem with pg_dump?
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

Thu, 03 May 2001 11:32:45 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: A problem with pg_dump? 
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

Thu, 03 May 2001 18:43:55 +0300
Alessio Bragadini <alessio@albourne.com>
Re: A problem with pg_dump?