Postgres Pro
Downloads
Home   >   mailing lists

Thread: Bug with foreign keys

Bug with foreign keys

From
"Donald Fraser"
Date:
Hiya Dave,
hope you had a good new years and all that.
I know you're busy, probably working on the new version or something.
 
Anyway found another bug in pgAdmin.
When you add foreign key constraints pgAdmin doesn't always tell the truth about them. This seems to be the case only when you have more than one.
 
The following example:
 
CREATE
 TABLE "tbl_useraudit" (
"id_user" int4 NOT NULL,
"id_contrib" int4 NOT NULL,
"id_editedby" int4 NOT NULL,
"dt_edited" timestamp (0) without time zone NOT NULL,
CONSTRAINT
 "tbl_useraudit_pkey" PRIMARY KEY ("id_user", "dt_edited"),
CONSTRAINT
 "fkey_user" FOREIGN KEY ("id_user") REFERENCES "tbl_user" ("id") ON DELETE CASCADE ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT
 "fkey_editedby" FOREIGN KEY ("id_editedby") REFERENCES "tbl_user" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED
) WITHOUT OIDS;
 
Produces the following display in pgAdmin:
-- Table: public.tbl_useraudit
CREATE TABLE public.tbl_useraudit (
id_user int4 NOT NULL,
id_contrib int4 NOT NULL,
id_editedby int4 NOT NULL,
dt_edited timestamp NOT NULL,
CONSTRAINT tbl_useraudit_pkey PRIMARY KEY (id_user, dt_edited),
CONSTRAINT fkey_editedby FOREIGN KEY (id_editedby) REFERENCES tbl_user (id) ON DELETE CASCADE ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT fkey_user FOREIGN KEY (id_user) REFERENCES tbl_user (id) ON DELETE CASCADE ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED
) WITHOUT OIDS;
 
You will notice that ON DELETE CASCADE is displayed for both constraints. This is incorrect given the original definition. I checked the output from pg_dumpall on the backend and it gives the correct definition hence I'm guessing pgAdmin is at fault.
 
Regards
Donald

Re: Bug with foreign keys

From
"Dave Page"
Date:
Thanks Donald, I'll look into this...
 
Regards, Dave.
-----Original Message-----
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 16 January 2003 19:31
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] Bug with foreign keys

Hiya Dave,
hope you had a good new years and all that.
I know you're busy, probably working on the new version or something.
 
Anyway found another bug in pgAdmin.
When you add foreign key constraints pgAdmin doesn't always tell the truth about them. This seems to be the case only when you have more than one.
 
The following example:
 
CREATE
 TABLE "tbl_useraudit" (
"id_user" int4 NOT NULL,
"id_contrib" int4 NOT NULL,
"id_editedby" int4 NOT NULL,
"dt_edited" timestamp (0) without time zone NOT NULL,
CONSTRAINT
 "tbl_useraudit_pkey" PRIMARY KEY ("id_user", "dt_edited"),
CONSTRAINT
 "fkey_user" FOREIGN KEY ("id_user") REFERENCES "tbl_user" ("id") ON DELETE CASCADE ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT
 "fkey_editedby" FOREIGN KEY ("id_editedby") REFERENCES "tbl_user" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED
) WITHOUT OIDS;
 
Produces the following display in pgAdmin:
-- Table: public.tbl_useraudit
CREATE TABLE public.tbl_useraudit (
id_user int4 NOT NULL,
id_contrib int4 NOT NULL,
id_editedby int4 NOT NULL,
dt_edited timestamp NOT NULL,
CONSTRAINT tbl_useraudit_pkey PRIMARY KEY (id_user, dt_edited),
CONSTRAINT fkey_editedby FOREIGN KEY (id_editedby) REFERENCES tbl_user (id) ON DELETE CASCADE ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT fkey_user FOREIGN KEY (id_user) REFERENCES tbl_user (id) ON DELETE CASCADE ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED
) WITHOUT OIDS;
 
You will notice that ON DELETE CASCADE is displayed for both constraints. This is incorrect given the original definition. I checked the output from pg_dumpall on the backend and it gives the correct definition hence I'm guessing pgAdmin is at fault.
 
Regards
Donald

Re: Bug with foreign keys

From
"Dave Page"
Date:
Hi Donald, sorry for the delay in replying to this fun little bug.
 
It's now fixed, and I've updated the snapshot binary for pgSchema.dll in the binaries folder on http://cvs.pgadmin.org.
 
You might want to hang on a minute before updating though as I'm about to look at the other bug you reported.
 
Thanks, Dave.
-----Original Message-----
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 16 January 2003 19:31
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] Bug with foreign keys

Hiya Dave,
hope you had a good new years and all that.
I know you're busy, probably working on the new version or something.
 
Anyway found another bug in pgAdmin.
When you add foreign key constraints pgAdmin doesn't always tell the truth about them. This seems to be the case only when you have more than one.
 
The following example:
 
CREATE
 TABLE "tbl_useraudit" (
"id_user" int4 NOT NULL,
"id_contrib" int4 NOT NULL,
"id_editedby" int4 NOT NULL,
"dt_edited" timestamp (0) without time zone NOT NULL,
CONSTRAINT
 "tbl_useraudit_pkey" PRIMARY KEY ("id_user", "dt_edited"),
CONSTRAINT
 "fkey_user" FOREIGN KEY ("id_user") REFERENCES "tbl_user" ("id") ON DELETE CASCADE ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT
 "fkey_editedby" FOREIGN KEY ("id_editedby") REFERENCES "tbl_user" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED
) WITHOUT OIDS;
 
Produces the following display in pgAdmin:
-- Table: public.tbl_useraudit
CREATE TABLE public.tbl_useraudit (
id_user int4 NOT NULL,
id_contrib int4 NOT NULL,
id_editedby int4 NOT NULL,
dt_edited timestamp NOT NULL,
CONSTRAINT tbl_useraudit_pkey PRIMARY KEY (id_user, dt_edited),
CONSTRAINT fkey_editedby FOREIGN KEY (id_editedby) REFERENCES tbl_user (id) ON DELETE CASCADE ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT fkey_user FOREIGN KEY (id_user) REFERENCES tbl_user (id) ON DELETE CASCADE ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED
) WITHOUT OIDS;
 
You will notice that ON DELETE CASCADE is displayed for both constraints. This is incorrect given the original definition. I checked the output from pg_dumpall on the backend and it gives the correct definition hence I'm guessing pgAdmin is at fault.
 
Regards
Donald