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@xxxxxxxx 9d552d9c47203a1acf20da5599fd31e5 2012-03-27 14:36:58.706-04 2012-08-20 16:07:43.588-04 2 6 109 5 15 16
test02@xxxxxxxx be5d669d8f34582e2e36c709fdea981b 2013-03-06 12:34:39.075-05 2013-03-06 12:38:11.142-05 2 2 361 17 31 32
test03@xxxxxxxx d7c1232848eaa89c5bf41f5e8e0eb3d8 2012-10-05 13:12:05.427-04 \N \N 7 353 2 26 27
test04@xxxxxxxx be5d669d8f34582e2e36c709fdea981b 2012-05-03 16:38:35.932-04 2013-03-12 09:44:53.799-04 31 7 177 17 22 23
test05@xxxxxxxx cc361159b96f905532ffc694246d4b2e 2012-03-22 15:34:49.049-04 \N \N 7 101 2 13 14
test06@xxxxxxxx 2ae147682a0db11b0ff0188a81e2df70 2012-10-19 10:33:12.013-04 2012-10-19 10:38:16.091-04 16 16 357 17 27 28
test07@xxxxxxxx 76470d7a9c77ff7f43c11474a1caaecc 2012-10-19 14:48:17.865-04 \N \N 2 358 3 28 29
test08@xxxxxxxx 5597e5a3044b8704cd0308dbe3a946ff 2012-10-19 14:51:29.358-04 \N \N 28 359 3 29 30
test09@xxxxxxxx f53a0ccbbb982308870318bddc9fd29a 2012-03-22 15:22:04.486-04 2012-10-19 16:07:05.986-04 16 7 99 6 11 12
ndatar@xxxxxxxx c23fd541fadae121cb1c3cc92e4edf53 2012-01-30 17:17:37.142-05 2012-02-08 17:45:44.113-05 2 1 4 2 2 3
test10@xxxxxxxx 7dc43cfb2b8462e216113939d0724511 2012-03-13 12:00:51.256-04 2012-10-19 16:20:32.849-04 2 2 67 3 5 6
test11@xxxxxxxx 2ffe4c86efaf30f220569b76630b44f2 2012-03-22 16:03:32.853-04 2012-05-03 16:28:56.412-04 7 7 102 2 14 15
test12@xxxxxxxx fb371212a03c92d6be71d37b5d6fa4ae 2012-03-22 11:23:29.302-04 2012-10-19 16:20:58.63-04 2 5 91 3 6 7
test13@xxxxxxxx 2ffe4c86efaf30f220569b76630b44f2 2012-03-22 14:58:01.773-04 2012-06-07 09:58:26.421-04 7 7 97 19 9 10
test14@xxxxxxxx cb311a5205919bf0d37fef818174cabc 2012-03-06 10:44:54.236-05 2012-10-21 13:33:54.804-04 2 2 50 3 4 5
test15@xxxxxxxx e8f9d12503ca7fb3f4e60ed646788a4c 2012-05-17 12:46:36.572-04 2012-10-23 11:21:10.759-04 4 7 241 2 24 25
test16@xxxxxxxx 412c43ce8940b77484e278a76fd3156c 2012-03-22 15:09:30.948-04 2012-10-23 11:21:43.6-04 4 7 98 2 10 11
test17@xxxxxxxx 480235819e570f74f0420fa224700f77 2012-01-30 18:25:41.072-05 2013-01-22 12:47:22.301-05 2 2 5 2 3 4
test18@xxxxxxxx 7fadd070a753a06aef85a8d5a02ca148 2012-03-22 13:33:05.959-04 2012-10-21 13:34:07.383-04 2 5 92 2 7 8
test19@xxxxxxxx daa4201f66367570a6e7b917acccec97 2012-03-22 14:37:52.505-04 \N \N 7 96 2 8 9
test20@xxxxxxxx 2af01256d414eb5b3d517966784f6d13 2013-03-05 12:09:07.233-05 \N \N 4 360 3 30 31
test21@xxxxxxxx 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@xxxxxxxxxxxxx> 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
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@xxxxxxxx 9d552d9c47203a1acf20da5599fd31e5 2012-03-27 14:36:58.706-04 2012-08-20 16:07:43.588-04 2 6 109 5 15 16
test02@xxxxxxxx be5d669d8f34582e2e36c709fdea981b 2013-03-06 12:34:39.075-05 2013-03-06 12:38:11.142-05 2 2 361 17 31 32
test03@xxxxxxxx d7c1232848eaa89c5bf41f5e8e0eb3d8 2012-10-05 13:12:05.427-04 \N \N 7 353 2 26 27
test04@xxxxxxxx be5d669d8f34582e2e36c709fdea981b 2012-05-03 16:38:35.932-04 2013-03-12 09:44:53.799-04 31 7 177 17 22 23
test05@xxxxxxxx cc361159b96f905532ffc694246d4b2e 2012-03-22 15:34:49.049-04 \N \N 7 101 2 13 14
test06@xxxxxxxx 2ae147682a0db11b0ff0188a81e2df70 2012-10-19 10:33:12.013-04 2012-10-19 10:38:16.091-04 16 16 357 17 27 28
test07@xxxxxxxx 76470d7a9c77ff7f43c11474a1caaecc 2012-10-19 14:48:17.865-04 \N \N 2 358 3 28 29
test08@xxxxxxxx 5597e5a3044b8704cd0308dbe3a946ff 2012-10-19 14:51:29.358-04 \N \N 28 359 3 29 30
test09@xxxxxxxx f53a0ccbbb982308870318bddc9fd29a 2012-03-22 15:22:04.486-04 2012-10-19 16:07:05.986-04 16 7 99 6 11 12
ndatar@xxxxxxxx c23fd541fadae121cb1c3cc92e4edf53 2012-01-30 17:17:37.142-05 2012-02-08 17:45:44.113-05 2 1 4 2 2 3
test10@xxxxxxxx 7dc43cfb2b8462e216113939d0724511 2012-03-13 12:00:51.256-04 2012-10-19 16:20:32.849-04 2 2 67 3 5 6
test11@xxxxxxxx 2ffe4c86efaf30f220569b76630b44f2 2012-03-22 16:03:32.853-04 2012-05-03 16:28:56.412-04 7 7 102 2 14 15
test12@xxxxxxxx fb371212a03c92d6be71d37b5d6fa4ae 2012-03-22 11:23:29.302-04 2012-10-19 16:20:58.63-04 2 5 91 3 6 7
test13@xxxxxxxx 2ffe4c86efaf30f220569b76630b44f2 2012-03-22 14:58:01.773-04 2012-06-07 09:58:26.421-04 7 7 97 19 9 10
test14@xxxxxxxx cb311a5205919bf0d37fef818174cabc 2012-03-06 10:44:54.236-05 2012-10-21 13:33:54.804-04 2 2 50 3 4 5
test15@xxxxxxxx e8f9d12503ca7fb3f4e60ed646788a4c 2012-05-17 12:46:36.572-04 2012-10-23 11:21:10.759-04 4 7 241 2 24 25
test16@xxxxxxxx 412c43ce8940b77484e278a76fd3156c 2012-03-22 15:09:30.948-04 2012-10-23 11:21:43.6-04 4 7 98 2 10 11
test17@xxxxxxxx 480235819e570f74f0420fa224700f77 2012-01-30 18:25:41.072-05 2013-01-22 12:47:22.301-05 2 2 5 2 3 4
test18@xxxxxxxx 7fadd070a753a06aef85a8d5a02ca148 2012-03-22 13:33:05.959-04 2012-10-21 13:34:07.383-04 2 5 92 2 7 8
test19@xxxxxxxx daa4201f66367570a6e7b917acccec97 2012-03-22 14:37:52.505-04 \N \N 7 96 2 8 9
test20@xxxxxxxx 2af01256d414eb5b3d517966784f6d13 2013-03-05 12:09:07.233-05 \N \N 4 360 3 30 31
test21@xxxxxxxx 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@xxxxxxxxxxxxx> 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
