Re: Circular references

Melvin Call <melvincall979(at)gmail(dot)com> 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