Re: Circular references - Mailing list pgsql-general

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