Thread: Delete / F/K error
I tried to delete a row from 'sites' and get this rather complex error: SQL error: ERROR: insert or update on table "types" violates foreign key constraint "$1" DETAIL: Key (page_template_id)=(8) is not present in table "templates". CONTEXT: SQL statement "UPDATE ONLY "public"."types" SET "item_template_id" = NULL WHERE "item_template_id" = $1" SQL statement "DELETE FROM ONLY "public"."templates" WHERE "site_id" = $1" In statement: DELETE FROM "sites" WHERE "id"='1' I'm not sure what's wrong, or how to fix it. Any ideas? thanks csn __________________________________________ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com
On Fri, Dec 30, 2005 at 12:09:12PM -0800, CSN wrote: > ERROR: insert or update on table "types" violates > foreign key constraint "$1" > DETAIL: Key (page_template_id)=(8) is not present in > table "templates". > CONTEXT: SQL statement "UPDATE ONLY "public"."types" > SET "item_template_id" = NULL WHERE "item_template_id" > = $1" > SQL statement "DELETE FROM ONLY "public"."templates" > WHERE "site_id" = $1" > > In statement: > DELETE FROM "sites" WHERE "id"='1' What are the table definitions for sites, templates, and types? I'd guess you have some ON DELETE CASCADE and ON DELETE SET NULL foreign key constraints in templates and types. Think through what happens when those constraints are triggered by the delete on sites; somehow you're ending up with a foreign key that violates its constraint so the delete fails. What version of PostgreSQL is this? -- Michael Fuhr
--- Michael Fuhr <mike@fuhr.org> wrote:
> On Fri, Dec 30, 2005 at 12:09:12PM -0800, CSN wrote:
> > ERROR: insert or update on table "types" violates
> > foreign key constraint "$1"
> > DETAIL: Key (page_template_id)=(8) is not present
> in
> > table "templates".
> > CONTEXT: SQL statement "UPDATE ONLY
> "public"."types"
> > SET "item_template_id" = NULL WHERE
> "item_template_id"
> > = $1"
> > SQL statement "DELETE FROM ONLY
> "public"."templates"
> > WHERE "site_id" = $1"
> >
> > In statement:
> > DELETE FROM "sites" WHERE "id"='1'
>
> What are the table definitions for sites, templates,
> and types?
> I'd guess you have some ON DELETE CASCADE and ON
> DELETE SET NULL
> foreign key constraints in templates and types.
> Think through what
> happens when those constraints are triggered by the
> delete on sites;
> somehow you're ending up with a foreign key that
> violates its
> constraint so the delete fails.
>
> What version of PostgreSQL is this?
>
> --
> Michael Fuhr
>
Here's the DDL for types:
CREATE TABLE types (
id integer DEFAULT nextval('"types_id_seq"'::text)
NOT NULL,
name character varying(255) NOT NULL,
item_count integer DEFAULT 0 NOT NULL,
page_template_id integer,
type_template_id integer,
item_template_id integer,
content_template_id integer,
items_template_id integer,
site_id integer NOT NULL
);
ALTER TABLE ONLY types
ADD CONSTRAINT "$1" FOREIGN KEY (page_template_id)
REFERENCES templates(id) ON UPDATE CASCADE ON DELETE
SET NULL;
ALTER TABLE ONLY types
ADD CONSTRAINT "$2" FOREIGN KEY (type_template_id)
REFERENCES templates(id) ON UPDATE CASCADE ON DELETE
SET NULL;
ALTER TABLE ONLY types
ADD CONSTRAINT "$3" FOREIGN KEY (item_template_id)
REFERENCES templates(id) ON UPDATE CASCADE ON DELETE
SET NULL;
ALTER TABLE ONLY types
ADD CONSTRAINT "$4" FOREIGN KEY
(content_template_id) REFERENCES templates(id) ON
UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE ONLY types
ADD CONSTRAINT "$5" FOREIGN KEY
(items_template_id) REFERENCES templates(id) ON UPDATE
CASCADE ON DELETE SET NULL;
ALTER TABLE ONLY types
ADD CONSTRAINT fk_types_sites FOREIGN KEY
(site_id) REFERENCES sites(id) ON UPDATE CASCADE ON
DELETE CASCADE;
And templates:
CREATE TABLE templates (
id integer DEFAULT
nextval('"templates_id_seq"'::text) NOT NULL,
name character varying(255) NOT NULL,
type_id integer,
site_id integer
);
ALTER TABLE ONLY templates
ADD CONSTRAINT fk_templates_sites FOREIGN KEY
(site_id) REFERENCES sites(id) ON UPDATE CASCADE ON
DELETE CASCADE;
Hmm, looks like I forgot a templates_types f/k.
Sites doesn't have any f/k's or constraints.
I'm using version 8.0.2.
It turns out there were no corresponding records in
table 'types'. Furthermore, 'delete from templates
where site_id=1;' resulted in this error:
ERROR: insert or update on table "types" violates
foreign key constraint "$1"
DETAIL: Key (page_template_id)=(8) is not present
in table "templates".
CONTEXT: SQL statement "UPDATE ONLY
"public"."types" SET "item_template_id" = NULL WHERE
"item_template_id" = $1"
But I was able to individually delete each template
record, then do 'delete from sites where id=1' with no
resulting errors. I'm still confused what the problem
was.
thanks
csn
__________________________________
Yahoo! for Good - Make a difference this year.
http://brand.yahoo.com/cybergivingweek2005/
On Fri, Dec 30, 2005 at 02:38:48PM -0800, CSN wrote: > I'm still confused what the problem was. I think the problem is related to having multiple foreign key constraints with ON DELETE SET NULL referencing the same target. The triggers that enforce those constraints are fired one at a time with a query like "UPDATE ONLY tbl SET col = NULL WHERE col = val". Each update changes only one column; the other columns still have their old values, so when the update checks those columns' foreign key constraints you get an error because the referenced key has already been deleted. Interestingly, this only appears to be a problem if the delete takes place in the same (sub)transaction that inserted the referencing row. Example: test=> CREATE TABLE foo ( test(> id integer PRIMARY KEY test(> ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" CREATE TABLE test=> test=> CREATE TABLE bar ( test(> foo_id1 integer REFERENCES foo ON DELETE SET NULL, test(> foo_id2 integer REFERENCES foo ON DELETE SET NULL test(> ); CREATE TABLE test=> BEGIN; BEGIN test=> INSERT INTO foo VALUES (1); INSERT 0 1 test=> INSERT INTO bar VALUES (1, 1); INSERT 0 1 test=> DELETE FROM foo WHERE id = 1; ERROR: insert or update on table "bar" violates foreign key constraint "bar_foo_id2_fkey" DETAIL: Key (foo_id2)=(1) is not present in table "foo". CONTEXT: SQL statement "UPDATE ONLY "public"."bar" SET "foo_id1" = NULL WHERE "foo_id1" = $1" test=> ROLLBACK; ROLLBACK But: test=> BEGIN; BEGIN test=> INSERT INTO foo VALUES (1); INSERT 0 1 test=> INSERT INTO bar VALUES (1, 1); INSERT 0 1 test=> COMMIT; COMMIT test=> DELETE FROM foo WHERE id = 1; DELETE 1 And: test=> BEGIN; BEGIN test=> INSERT INTO foo VALUES (1); INSERT 0 1 test=> INSERT INTO bar VALUES (1, 1); INSERT 0 1 test=> SAVEPOINT x; SAVEPOINT test=> DELETE FROM foo WHERE id = 1; DELETE 1 test=> COMMIT; COMMIT And: test=> BEGIN; BEGIN test=> INSERT INTO foo VALUES (1); INSERT 0 1 test=> SAVEPOINT x; SAVEPOINT test=> INSERT INTO bar VALUES (1, 1); INSERT 0 1 test=> RELEASE x; RELEASE test=> DELETE FROM foo WHERE id = 1; DELETE 1 test=> COMMIT; COMMIT Any developers following this? Is this behavior bogus or correct? The above examples are in 8.1.1 from CVS. -- Michael Fuhr
On Fri, 30 Dec 2005, Michael Fuhr wrote: > On Fri, Dec 30, 2005 at 02:38:48PM -0800, CSN wrote: > > I'm still confused what the problem was. > > I think the problem is related to having multiple foreign key > constraints with ON DELETE SET NULL referencing the same target. > The triggers that enforce those constraints are fired one at a time > with a query like "UPDATE ONLY tbl SET col = NULL WHERE col = val". > Each update changes only one column; the other columns still have > their old values, so when the update checks those columns' foreign > key constraints you get an error because the referenced key has > already been deleted. Interestingly, this only appears to be a > problem if the delete takes place in the same (sub)transaction that > inserted the referencing row. Hmm, yes, IIRC there's code that overrides the key check for things done in this transaction right now because there's a potential failure case the other way (where a row can get in that violates the constraint under certain sequences). I think the trigger timing changes exposed some holes in that. It's possible that the experimental fk timing patches I'd sent to -patches a while back might make these cases work.