Thread: Circular references
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.
Thanks a million,
Melvin
\d organization
Table "project.organization"
Column | Type | Modifiers
-----------------+--------------------------+------------------------------------------------------------------------
organization_id | bigint | not null default nextval('organization_organization_id_seq'::regclass)
name | character varying(300) | not null
type_id | bigint | not null
description | text | not null default '-'::text
website | character varying(500) | default '-'::character varying
date_created | timestamp with time zone | not null default ('now'::text)::date
created_by | bigint | not null
date_updated | timestamp with time zone |
updated_by | bigint |
Indexes:
"p_key_org_id" PRIMARY KEY, btree (organization_id)
Foreign-key constraints:
"f_key_org_org_type_id" FOREIGN KEY (type_id) REFERENCES organization_type(type_id)
"f_key_org_user_created_by" FOREIGN KEY (created_by) REFERENCES "user"(user_id)
"f_key_org_user_updated_by" FOREIGN KEY (updated_by) REFERENCES "user"(user_id)
Referenced by:
TABLE "program" CONSTRAINT "f_key_program_org_id" FOREIGN KEY (organization_id) REFERENCES organization(organization_id)
TABLE ""user"" CONSTRAINT "f_key_user_org_id" FOREIGN KEY (organization_id) REFERENCES organization(organization_id)
\d user
Table "project.user"
Column | Type | Modifiers
-----------------+--------------------------+--------------------------------------------------------
username | character varying(100) | not null
password | character varying(100) | not null
date_created | timestamp with time zone | not null
date_updated | timestamp with time zone |
updated_by | bigint |
created_by | bigint | not null
person_id | bigint | not null
organization_id | bigint | not null
user_id | bigint | not null default nextval('user_user_id_seq'::regclass)
user_role_id | bigint | not null
Indexes:
"p_key_user_id" PRIMARY KEY, btree (user_id)
Foreign-key constraints:
"f_key_user_org_id" FOREIGN KEY (organization_id) REFERENCES organization(organization_id)
"f_key_user_person_id" FOREIGN KEY (person_id) REFERENCES person(person_id)
"f_key_user_user_role_id" FOREIGN KEY (user_role_id) REFERENCES user_role(user_role_id)
Referenced by:
TABLE "observation_parameter" CONSTRAINT "f_key_observation_param_user_created_by" FOREIGN KEY (created_by) REFERENCES "user"(user_id)
TABLE "observation_parameter" CONSTRAINT "f_key_observation_param_user_updated_by" FOREIGN KEY (updated_by) REFERENCES "user"(user_id)
TABLE "observation_tuple" CONSTRAINT "f_key_observation_tuple_user_created_by" FOREIGN KEY (created_by) REFERENCES "user"(user_id)
TABLE "observation_tuple" CONSTRAINT "f_key_observation_tuple_user_updated_by" FOREIGN KEY (updated_by) REFERENCES "user"(user_id)
TABLE "organization" CONSTRAINT "f_key_org_user_created_by" FOREIGN KEY (created_by) REFERENCES "user"(user_id)
TABLE "organization" CONSTRAINT "f_key_org_user_updated_by" FOREIGN KEY (updated_by) REFERENCES "user"(user_id)
TABLE "program_admin" CONSTRAINT "f_key_prog_admin_user_id" FOREIGN KEY (user_id) REFERENCES "user"(user_id)
TABLE "program" CONSTRAINT "f_key_program_user_created_by" FOREIGN KEY (created_by) REFERENCES "user"(user_id)
TABLE "program" CONSTRAINT "f_key_program_user_owner_id" FOREIGN KEY (owner_id) REFERENCES "user"(user_id)
TABLE "program" CONSTRAINT "f_key_program_user_updated_by" FOREIGN KEY (updated_by) REFERENCES "user"(user_id)
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.
Thanks a million,
Melvin
\d organization
Table "project.organization"
Column | Type | Modifiers
-----------------+--------------------------+------------------------------------------------------------------------
organization_id | bigint | not null default nextval('organization_organization_id_seq'::regclass)
name | character varying(300) | not null
type_id | bigint | not null
description | text | not null default '-'::text
website | character varying(500) | default '-'::character varying
date_created | timestamp with time zone | not null default ('now'::text)::date
created_by | bigint | not null
date_updated | timestamp with time zone |
updated_by | bigint |
Indexes:
"p_key_org_id" PRIMARY KEY, btree (organization_id)
Foreign-key constraints:
"f_key_org_org_type_id" FOREIGN KEY (type_id) REFERENCES organization_type(type_id)
"f_key_org_user_created_by" FOREIGN KEY (created_by) REFERENCES "user"(user_id)
"f_key_org_user_updated_by" FOREIGN KEY (updated_by) REFERENCES "user"(user_id)
Referenced by:
TABLE "program" CONSTRAINT "f_key_program_org_id" FOREIGN KEY (organization_id) REFERENCES organization(organization_id)
TABLE ""user"" CONSTRAINT "f_key_user_org_id" FOREIGN KEY (organization_id) REFERENCES organization(organization_id)
\d user
Table "project.user"
Column | Type | Modifiers
-----------------+--------------------------+--------------------------------------------------------
username | character varying(100) | not null
password | character varying(100) | not null
date_created | timestamp with time zone | not null
date_updated | timestamp with time zone |
updated_by | bigint |
created_by | bigint | not null
person_id | bigint | not null
organization_id | bigint | not null
user_id | bigint | not null default nextval('user_user_id_seq'::regclass)
user_role_id | bigint | not null
Indexes:
"p_key_user_id" PRIMARY KEY, btree (user_id)
Foreign-key constraints:
"f_key_user_org_id" FOREIGN KEY (organization_id) REFERENCES organization(organization_id)
"f_key_user_person_id" FOREIGN KEY (person_id) REFERENCES person(person_id)
"f_key_user_user_role_id" FOREIGN KEY (user_role_id) REFERENCES user_role(user_role_id)
Referenced by:
TABLE "observation_parameter" CONSTRAINT "f_key_observation_param_user_created_by" FOREIGN KEY (created_by) REFERENCES "user"(user_id)
TABLE "observation_parameter" CONSTRAINT "f_key_observation_param_user_updated_by" FOREIGN KEY (updated_by) REFERENCES "user"(user_id)
TABLE "observation_tuple" CONSTRAINT "f_key_observation_tuple_user_created_by" FOREIGN KEY (created_by) REFERENCES "user"(user_id)
TABLE "observation_tuple" CONSTRAINT "f_key_observation_tuple_user_updated_by" FOREIGN KEY (updated_by) REFERENCES "user"(user_id)
TABLE "organization" CONSTRAINT "f_key_org_user_created_by" FOREIGN KEY (created_by) REFERENCES "user"(user_id)
TABLE "organization" CONSTRAINT "f_key_org_user_updated_by" FOREIGN KEY (updated_by) REFERENCES "user"(user_id)
TABLE "program_admin" CONSTRAINT "f_key_prog_admin_user_id" FOREIGN KEY (user_id) REFERENCES "user"(user_id)
TABLE "program" CONSTRAINT "f_key_program_user_created_by" FOREIGN KEY (created_by) REFERENCES "user"(user_id)
TABLE "program" CONSTRAINT "f_key_program_user_owner_id" FOREIGN KEY (owner_id) REFERENCES "user"(user_id)
TABLE "program" CONSTRAINT "f_key_program_user_updated_by" FOREIGN KEY (updated_by) REFERENCES "user"(user_id)
Melvin Call <melvincall979@gmail.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
On 21 June 2013 05:08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
That should be possible if you create one of the FK constraints as a DEFERRED constraint. Which one depepnds on what order you supply the data in.
And make sure you supply all the data in the circular reference in the same transaction, or the deferred constraint check will kick in too early.
-- Melvin Call <melvincall979@gmail.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.
That should be possible if you create one of the FK constraints as a DEFERRED constraint. Which one depepnds on what order you supply the data in.
And make sure you supply all the data in the circular reference in the same transaction, or the deferred constraint check will kick in too early.
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
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@gmail.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 > >
Hi Tom,
First an apology of sorts. The restore doesn't fail, but the COPY statements generate errors about the referenced table not containing the key values (as you seem to have figured out that I meant). I have no idea what version of PostgreSQL the remote system is running, I just have the dump that was given to me. However I would venture to say it is probably 9.x.
There are no ALTER FOREIGN KEY statements in the file. As far as I can tell, the only ALTER statements are those that assign ownership to objects, and those that set the sequence defaults. There are probably others, but I didn't see them. I am including below, all of the references in the file to the user table, except the ones that alter columns in the tables that reference it. I am not including the whole file as it contains binary data and is 32 MB total.
The failures start when the first user record fails because there is no organization ID of 5 in the organization table, and organization ID 5 is looking for user ID of 2 for the created_by field, and it just goes on.
Originally I was given a set of files that each contain a CREATE TABLE statement, and a series of INSERT statements. They would work, but only because there are no constraints defined in any of the files. So they would just create a series of stand-alone tables with no relationship between any of them.
In all honesty, this is just a very poorly developed product and I think I'm through trying to mess with it. I could probably re-design it from scratch faster than I can work this out.
I appreciate your help and confirmation that I'm not near as crazy as the person(s) that designed this.
Melvin
--
-- Name: user; Type: TABLE; Schema: project; Owner: projlead; Tablespace:
--
CREATE TABLE "user" (
username character varying(100) NOT NULL,
password character varying(100) NOT NULL,
date_created timestamp with time zone NOT NULL,
date_updated timestamp with time zone,
updated_by bigint,
created_by bigint NOT NULL,
person_id bigint NOT NULL,
organization_id bigint NOT NULL,
user_id bigint NOT NULL,
user_role_id bigint NOT NULL
);
ALTER TABLE project."user" OWNER TO projlead;
<snip>
--
-- Name: user_id; Type: DEFAULT; Schema: project; Owner: projlead
--
ALTER TABLE ONLY "user" ALTER COLUMN user_id SET DEFAULT nextval('user_user_id_seq'::regclass);
<snip>
--
-- Data for Name: user; Type: TABLE DATA; Schema: project; Owner: projlead
--
COPY "user" (username, password, date_created, date_updated, updated_by, created_by, person_id, organization_id, user_id, user_role_id) FROM stdin;
test01@test.com 9d552d9c47203a1acf20da5599fd31e5 2012-03-27 14:36:58.706-04 2012-08-20 16:07:43.588-04 2 6 109 5 15 16
test02@test.com be5d669d8f34582e2e36c709fdea981b 2013-03-06 12:34:39.075-05 2013-03-06 12:38:11.142-05 2 2 361 17 31 32
test03@test.com d7c1232848eaa89c5bf41f5e8e0eb3d8 2012-10-05 13:12:05.427-04 \N \N 7 353 2 26 27
test04@test.edu be5d669d8f34582e2e36c709fdea981b 2012-05-03 16:38:35.932-04 2013-03-12 09:44:53.799-04 31 7 177 17 22 23
test05@test.com cc361159b96f905532ffc694246d4b2e 2012-03-22 15:34:49.049-04 \N \N 7 101 2 13 14
test06@test.com 2ae147682a0db11b0ff0188a81e2df70 2012-10-19 10:33:12.013-04 2012-10-19 10:38:16.091-04 16 16 357 17 27 28
test07@test.com 76470d7a9c77ff7f43c11474a1caaecc 2012-10-19 14:48:17.865-04 \N \N 2 358 3 28 29
test08@test.com 5597e5a3044b8704cd0308dbe3a946ff 2012-10-19 14:51:29.358-04 \N \N 28 359 3 29 30
test09@test.com f53a0ccbbb982308870318bddc9fd29a 2012-03-22 15:22:04.486-04 2012-10-19 16:07:05.986-04 16 7 99 6 11 12
ndatar@test.com c23fd541fadae121cb1c3cc92e4edf53 2012-01-30 17:17:37.142-05 2012-02-08 17:45:44.113-05 2 1 4 2 2 3
test10@test.com 7dc43cfb2b8462e216113939d0724511 2012-03-13 12:00:51.256-04 2012-10-19 16:20:32.849-04 2 2 67 3 5 6
test11@test.com 2ffe4c86efaf30f220569b76630b44f2 2012-03-22 16:03:32.853-04 2012-05-03 16:28:56.412-04 7 7 102 2 14 15
test12@test.com fb371212a03c92d6be71d37b5d6fa4ae 2012-03-22 11:23:29.302-04 2012-10-19 16:20:58.63-04 2 5 91 3 6 7
test13@test.com 2ffe4c86efaf30f220569b76630b44f2 2012-03-22 14:58:01.773-04 2012-06-07 09:58:26.421-04 7 7 97 19 9 10
test14@test.com cb311a5205919bf0d37fef818174cabc 2012-03-06 10:44:54.236-05 2012-10-21 13:33:54.804-04 2 2 50 3 4 5
test15@test.com e8f9d12503ca7fb3f4e60ed646788a4c 2012-05-17 12:46:36.572-04 2012-10-23 11:21:10.759-04 4 7 241 2 24 25
test16@test.com 412c43ce8940b77484e278a76fd3156c 2012-03-22 15:09:30.948-04 2012-10-23 11:21:43.6-04 4 7 98 2 10 11
test17@test.com 480235819e570f74f0420fa224700f77 2012-01-30 18:25:41.072-05 2013-01-22 12:47:22.301-05 2 2 5 2 3 4
test18@test.com 7fadd070a753a06aef85a8d5a02ca148 2012-03-22 13:33:05.959-04 2012-10-21 13:34:07.383-04 2 5 92 2 7 8
test19@test.com daa4201f66367570a6e7b917acccec97 2012-03-22 14:37:52.505-04 \N \N 7 96 2 8 9
test20@test.com 2af01256d414eb5b3d517966784f6d13 2013-03-05 12:09:07.233-05 \N \N 4 360 3 30 31
test21@test.com cb311a5205919bf0d37fef818174cabc 2012-04-03 15:54:06.863-04 2013-03-05 12:25:16.322-05 4 5 129 7 16 17
<snip>
--
-- Name: p_key_user_id; Type: CONSTRAINT; Schema: project; Owner: projlead; Tablespace:
--
ALTER TABLE ONLY "user"
ADD CONSTRAINT p_key_user_id PRIMARY KEY (user_id);
<snip>
--
-- Name: f_key_org_user_created_by; Type: FK CONSTRAINT; Schema: project; Owner: projlead
--
ALTER TABLE ONLY organization
ADD CONSTRAINT f_key_org_user_created_by FOREIGN KEY (created_by) REFERENCES "user"(user_id);
<snip>
--
-- Name: f_key_user_org_id; Type: FK CONSTRAINT; Schema: project; Owner: projlead
--
ALTER TABLE ONLY "user"
ADD CONSTRAINT f_key_user_org_id FOREIGN KEY (organization_id) REFERENCES organization(organization_id);
--
-- Name: f_key_user_person_id; Type: FK CONSTRAINT; Schema: project; Owner: projlead
--
ALTER TABLE ONLY "user"
ADD CONSTRAINT f_key_user_person_id FOREIGN KEY (person_id) REFERENCES person(person_id);
--
-- Name: f_key_user_user_role_id; Type: FK CONSTRAINT; Schema: project; Owner: projlead
--
ALTER TABLE ONLY "user"
ADD CONSTRAINT f_key_user_user_role_id FOREIGN KEY (user_role_id) REFERENCES user_role(user_role_id);
On Thu, Jun 20, 2013 at 10:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Melvin Call <melvincall979@gmail.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
First an apology of sorts. The restore doesn't fail, but the COPY statements generate errors about the referenced table not containing the key values (as you seem to have figured out that I meant). I have no idea what version of PostgreSQL the remote system is running, I just have the dump that was given to me. However I would venture to say it is probably 9.x.
There are no ALTER FOREIGN KEY statements in the file. As far as I can tell, the only ALTER statements are those that assign ownership to objects, and those that set the sequence defaults. There are probably others, but I didn't see them. I am including below, all of the references in the file to the user table, except the ones that alter columns in the tables that reference it. I am not including the whole file as it contains binary data and is 32 MB total.
The failures start when the first user record fails because there is no organization ID of 5 in the organization table, and organization ID 5 is looking for user ID of 2 for the created_by field, and it just goes on.
Originally I was given a set of files that each contain a CREATE TABLE statement, and a series of INSERT statements. They would work, but only because there are no constraints defined in any of the files. So they would just create a series of stand-alone tables with no relationship between any of them.
In all honesty, this is just a very poorly developed product and I think I'm through trying to mess with it. I could probably re-design it from scratch faster than I can work this out.
I appreciate your help and confirmation that I'm not near as crazy as the person(s) that designed this.
Melvin
--
-- Name: user; Type: TABLE; Schema: project; Owner: projlead; Tablespace:
--
CREATE TABLE "user" (
username character varying(100) NOT NULL,
password character varying(100) NOT NULL,
date_created timestamp with time zone NOT NULL,
date_updated timestamp with time zone,
updated_by bigint,
created_by bigint NOT NULL,
person_id bigint NOT NULL,
organization_id bigint NOT NULL,
user_id bigint NOT NULL,
user_role_id bigint NOT NULL
);
ALTER TABLE project."user" OWNER TO projlead;
<snip>
--
-- Name: user_id; Type: DEFAULT; Schema: project; Owner: projlead
--
ALTER TABLE ONLY "user" ALTER COLUMN user_id SET DEFAULT nextval('user_user_id_seq'::regclass);
<snip>
--
-- Data for Name: user; Type: TABLE DATA; Schema: project; Owner: projlead
--
COPY "user" (username, password, date_created, date_updated, updated_by, created_by, person_id, organization_id, user_id, user_role_id) FROM stdin;
test01@test.com 9d552d9c47203a1acf20da5599fd31e5 2012-03-27 14:36:58.706-04 2012-08-20 16:07:43.588-04 2 6 109 5 15 16
test02@test.com be5d669d8f34582e2e36c709fdea981b 2013-03-06 12:34:39.075-05 2013-03-06 12:38:11.142-05 2 2 361 17 31 32
test03@test.com d7c1232848eaa89c5bf41f5e8e0eb3d8 2012-10-05 13:12:05.427-04 \N \N 7 353 2 26 27
test04@test.edu be5d669d8f34582e2e36c709fdea981b 2012-05-03 16:38:35.932-04 2013-03-12 09:44:53.799-04 31 7 177 17 22 23
test05@test.com cc361159b96f905532ffc694246d4b2e 2012-03-22 15:34:49.049-04 \N \N 7 101 2 13 14
test06@test.com 2ae147682a0db11b0ff0188a81e2df70 2012-10-19 10:33:12.013-04 2012-10-19 10:38:16.091-04 16 16 357 17 27 28
test07@test.com 76470d7a9c77ff7f43c11474a1caaecc 2012-10-19 14:48:17.865-04 \N \N 2 358 3 28 29
test08@test.com 5597e5a3044b8704cd0308dbe3a946ff 2012-10-19 14:51:29.358-04 \N \N 28 359 3 29 30
test09@test.com f53a0ccbbb982308870318bddc9fd29a 2012-03-22 15:22:04.486-04 2012-10-19 16:07:05.986-04 16 7 99 6 11 12
ndatar@test.com c23fd541fadae121cb1c3cc92e4edf53 2012-01-30 17:17:37.142-05 2012-02-08 17:45:44.113-05 2 1 4 2 2 3
test10@test.com 7dc43cfb2b8462e216113939d0724511 2012-03-13 12:00:51.256-04 2012-10-19 16:20:32.849-04 2 2 67 3 5 6
test11@test.com 2ffe4c86efaf30f220569b76630b44f2 2012-03-22 16:03:32.853-04 2012-05-03 16:28:56.412-04 7 7 102 2 14 15
test12@test.com fb371212a03c92d6be71d37b5d6fa4ae 2012-03-22 11:23:29.302-04 2012-10-19 16:20:58.63-04 2 5 91 3 6 7
test13@test.com 2ffe4c86efaf30f220569b76630b44f2 2012-03-22 14:58:01.773-04 2012-06-07 09:58:26.421-04 7 7 97 19 9 10
test14@test.com cb311a5205919bf0d37fef818174cabc 2012-03-06 10:44:54.236-05 2012-10-21 13:33:54.804-04 2 2 50 3 4 5
test15@test.com e8f9d12503ca7fb3f4e60ed646788a4c 2012-05-17 12:46:36.572-04 2012-10-23 11:21:10.759-04 4 7 241 2 24 25
test16@test.com 412c43ce8940b77484e278a76fd3156c 2012-03-22 15:09:30.948-04 2012-10-23 11:21:43.6-04 4 7 98 2 10 11
test17@test.com 480235819e570f74f0420fa224700f77 2012-01-30 18:25:41.072-05 2013-01-22 12:47:22.301-05 2 2 5 2 3 4
test18@test.com 7fadd070a753a06aef85a8d5a02ca148 2012-03-22 13:33:05.959-04 2012-10-21 13:34:07.383-04 2 5 92 2 7 8
test19@test.com daa4201f66367570a6e7b917acccec97 2012-03-22 14:37:52.505-04 \N \N 7 96 2 8 9
test20@test.com 2af01256d414eb5b3d517966784f6d13 2013-03-05 12:09:07.233-05 \N \N 4 360 3 30 31
test21@test.com cb311a5205919bf0d37fef818174cabc 2012-04-03 15:54:06.863-04 2013-03-05 12:25:16.322-05 4 5 129 7 16 17
<snip>
--
-- Name: p_key_user_id; Type: CONSTRAINT; Schema: project; Owner: projlead; Tablespace:
--
ALTER TABLE ONLY "user"
ADD CONSTRAINT p_key_user_id PRIMARY KEY (user_id);
<snip>
--
-- Name: f_key_org_user_created_by; Type: FK CONSTRAINT; Schema: project; Owner: projlead
--
ALTER TABLE ONLY organization
ADD CONSTRAINT f_key_org_user_created_by FOREIGN KEY (created_by) REFERENCES "user"(user_id);
<snip>
--
-- Name: f_key_user_org_id; Type: FK CONSTRAINT; Schema: project; Owner: projlead
--
ALTER TABLE ONLY "user"
ADD CONSTRAINT f_key_user_org_id FOREIGN KEY (organization_id) REFERENCES organization(organization_id);
--
-- Name: f_key_user_person_id; Type: FK CONSTRAINT; Schema: project; Owner: projlead
--
ALTER TABLE ONLY "user"
ADD CONSTRAINT f_key_user_person_id FOREIGN KEY (person_id) REFERENCES person(person_id);
--
-- Name: f_key_user_user_role_id; Type: FK CONSTRAINT; Schema: project; Owner: projlead
--
ALTER TABLE ONLY "user"
ADD CONSTRAINT f_key_user_user_role_id FOREIGN KEY (user_role_id) REFERENCES user_role(user_role_id);
On Thu, Jun 20, 2013 at 10:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Melvin Call <melvincall979@gmail.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
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@volny.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@gmail.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
>
>
Melvin Call wrote > 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. You only need solve the chicken-egg problem once with pre-loaded data then all new organizations can be added normally. What this requires is that someone external to the organization be the creation user. So "admin@admin_company" is the "created by" user for organization "@client_company" and that user should also then create an "admin@client_company" who would then be - generally - the creator for other organizational objects. You just need to get "@admin_company" and "admin@admin_company" into the database (via deferred constraint resolution or before constraints are added) and you are good to go. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Circular-references-tp5760228p5760340.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.