Re: Circular references

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