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