Hello. I think this CAN happen in practice when the constraints are DEFERRED, because as such are checked at COMMIT time. HTH, Ladislav Lenart On 21.6.2013 05:08, Tom Lane wrote: > Melvin Call <melvincall979@xxxxxxxxx> writes: >> I was given a dump of an existing remote schema and database, and the >> restore on my local system failed. Looking into it, I found a circular >> parent-child/child-parent relationship, and I don't believe this existing >> structure is viable. To summarize, the organization entity has an attribute >> of creator, which is a foreign key to the user table, but the user has to >> belong to an organization, which is a foreign key to the organization >> table. Since neither are nullable, there is no way to create even an >> initial record. My guess is one or both of the tables was first populated, >> and then the FK constraint(s) created. > >> So, my question is just a request to confirm that I haven't lost my mind >> and/or am missing something. Is there any way this could work? The relevant >> table structures are listed below. > > I think you're right: there's no way that such a structure would be > very useful in practice, because inserting any new data would have a > chicken-vs-egg problem. However, I'm curious about your statement that > dump/restore failed. I tried this test case: > > regression=# create database bogus; > CREATE DATABASE > regression=# \c bogus > You are now connected to database "bogus" as user "postgres". > bogus=# create table t1 (f1 int primary key); > CREATE TABLE > bogus=# insert into t1 values (1),(2); > INSERT 0 2 > bogus=# create table t2 (f1 int primary key); > CREATE TABLE > bogus=# insert into t2 values (1),(2); > INSERT 0 2 > bogus=# alter table t1 add foreign key (f1) references t2; > ALTER TABLE > bogus=# alter table t2 add foreign key (f1) references t1; > ALTER TABLE > > and then did a pg_dump and restore; and for me, the restore went > through just fine, because the dump script did exactly the same > thing, ie issue ALTER ADD FOREIGN KEY commands only after populating > the tables. Was your dump from an ancient version of pg_dump? > Or maybe you tried to use separate schema and data dumps? > If neither, could you show a self-contained case where it fails? > > regards, tom lane > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
