Re: Circular references

Hi Ladislav and Alban,

Thanks for the suggestions regarding the DEFERRED constraint. While
certainly a viable option, I would expect the dump to maintain those
constraints. The lack of them indicates to me that somehow an initial
insert was created and then the structure was modified to support the
circular reference. FWIW, there is yet another circular reference in this
database. I think it is just plain poor design work, especially when I look
at some of the other entities and attributes. There is a lot of redundancy
and dependencies in this structure. I think I'm going to let them fix it
before I mess with it. I think they need the practice.

Regards,
Melvin

On Fri, Jun 21, 2013 at 4:20 AM, Ladislav Lenart <lenartlad(at)volny(dot)cz> wrote:

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