Обсуждение: Foreign key behavior different in a function and outside
I have 3 tables say A, B, C
CREATE TABLE A
(
A_id integer NOT NULL,
CONSTRAINT A_pkey PRIMARY KEY (A_id)
)
;
CREATE TABLE B
(
B_id serial NOT NULL,
A_id integer NOT NULL,
CONSTRAINT B_pkey PRIMARY KEY (B_id),
CONSTRAINT fkd08b6eeeb4f3a730 FOREIGN KEY (A_id)
REFERENCES A(A_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
;
CREATE TABLE C
(
C_id serial NOT NULL,
B_id integer NOT NULL,
CONSTRAINT C_pkey PRIMARY KEY (C_id),
CONSTRAINT fk31a4865653f36b09 FOREIGN KEY (B_id)
REFERENCES B(B_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
;
Insert into A values (1);
Insert into B values (1,1);
Insert into C values (1,1);
I try this query -
delete from B where B_id = 1;
As expected I get this error -
ERROR: update or delete on table "b" violates foreign key constraint "fk31a4865653f36b09" on table "c"
DETAIL: Key (b_id)=(1) is still referenced from table "c".
********** Error **********
ERROR: update or delete on table "b" violates foreign key constraint "fk31a4865653f36b09" on table "c"
SQL state: 23503
Detail: Key (b_id)=(1) is still referenced from table "c".
Now if I have this delete within a function
CREATE OR REPLACE FUNCTION delete_B(id integer)
RETURNS void AS
$BODY$
declare
vSql varchar;
BEGIN
delete from B where id = id;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
And I try to execute this –
select delete_B(1)
This even with no cascade delete, will succeed and child records are deleted from C. This does not happen on all my set ups but has been happening consistently on more than one. Any known issue here?
-mridula
Mridula Mahadevan <mmahadevan@stratify.com> writes:
> CREATE OR REPLACE FUNCTION delete_B(id integer)
> RETURNS void AS
> $BODY$
> declare
> vSql varchar;
> BEGIN
> delete from B where id = id;
> END;
That's a really dangerous function definition --- the system is not by
any means bright enough to figure out that you'd like one instance of
"id" to refer to B's column and the other instance to refer to the
function parameter. It's going to resolve both the same way (both as
the function parameter, as it happens); meaning that what you actually
have here is "delete from B where true".
I'm not sure how that ties into your claimed issue with foreign keys,
and maybe the above is just a hastily oversimplified version of what
you really did. But we aren't going to be able to figure out the
problem without an exact example.
FWIW, I seem to remember that really old versions of Postgres used to
have some issues with the timing of foreign key checks for updates
issued inside functions. That's probably not relevant, but since
you also failed to mention what Postgres version you're dealing with,
it's hard to be sure.
regards, tom lane
Thanks for the response Tom. I am running postgres 8.3.7.
Yes, his is a highly simplified version, but I also didn't get the column name right. One more attempt at that.
CREATE OR REPLACE FUNCTION delete_B(id integer)
RETURNS void AS
$BODY$
declare
vSql varchar;
BEGIN
delete from B where B_id = id;
END;
The core issue is the foreign key reference being deleted even though there is no cascade delete defined.
Thanks again.
-mridula
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, March 04, 2010 7:45 PM
To: Mridula Mahadevan
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Foreign key behavior different in a function and outside
Mridula Mahadevan <mmahadevan@stratify.com> writes:
> CREATE OR REPLACE FUNCTION delete_B(id integer)
> RETURNS void AS
> $BODY$
> declare
> vSql varchar;
> BEGIN
> delete from B where id = id;
> END;
That's a really dangerous function definition --- the system is not by
any means bright enough to figure out that you'd like one instance of
"id" to refer to B's column and the other instance to refer to the
function parameter. It's going to resolve both the same way (both as
the function parameter, as it happens); meaning that what you actually
have here is "delete from B where true".
I'm not sure how that ties into your claimed issue with foreign keys,
and maybe the above is just a hastily oversimplified version of what
you really did. But we aren't going to be able to figure out the
problem without an exact example.
FWIW, I seem to remember that really old versions of Postgres used to
have some issues with the timing of foreign key checks for updates
issued inside functions. That's probably not relevant, but since
you also failed to mention what Postgres version you're dealing with,
it's hard to be sure.
regards, tom lane
On 05/03/10 06:45, Mridula Mahadevan wrote: > Thanks for the response Tom. I am running postgres 8.3.7. > > Yes, his is a highly simplified version, but I also didn't get the > column name right. > The core issue is the foreign key reference being deleted even though > there is no cascade delete defined. Thanks again. Doesn't do it here. And I wouldn't expect it to. This was using the precise text of your example (with the corrected funciton, which seems to contain an un-needed vSql variable, and with a RAISE NOTICE line to make sure the function was called.). It's possible you've found a bug, but more likely that there's something unusual in your setup that you don't know about. Two questions: 1. Are you running this actual test, on a newly created database? 2. Previously you said the following: > This even with no cascade delete, will succeed and child records are > deleted from C. This does not happen on all my set ups but has been > happening consistently on more than one. Any known issue here? Are you saying the test you sent us doesn't behave the same on different installations, or your actual application doesn't behave the same? I'm guessing that you either: 1. Have a trigger you don't know about. 2. Have another function of the same name, but in a different schema that is being called by mistake. Add a "RAISE NOTICE" to the function to find out. -- Richard Huxton Archonet Ltd
Richard, To answer your questions, I have a live application that is running on postgresql. We are seeing this issue on certaininstallations and not on others. So the code is no different in each set up. I also added the trigger to table B andthen the foreign key error is thrown in all set ups. But without the trigger a delete on table B from within a functionassumes cascade delete even when one is not specified. Again only in some cases, I can send you the entire procedureif it helps (the one I have below only has the relevant parts). -mridulan -----Original Message----- From: Richard Huxton [mailto:dev@archonet.com] Sent: Friday, March 05, 2010 1:27 AM To: Mridula Mahadevan Cc: Tom Lane; pgsql-general@postgresql.org Subject: Re: [GENERAL] Foreign key behavior different in a function and outside On 05/03/10 06:45, Mridula Mahadevan wrote: > Thanks for the response Tom. I am running postgres 8.3.7. > > Yes, his is a highly simplified version, but I also didn't get the > column name right. > The core issue is the foreign key reference being deleted even though > there is no cascade delete defined. Thanks again. Doesn't do it here. And I wouldn't expect it to. This was using the precise text of your example (with the corrected funciton, which seems to contain an un-needed vSql variable, and with a RAISE NOTICE line to make sure the function was called.). It's possible you've found a bug, but more likely that there's something unusual in your setup that you don't know about. Two questions: 1. Are you running this actual test, on a newly created database? 2. Previously you said the following: > This even with no cascade delete, will succeed and child records are > deleted from C. This does not happen on all my set ups but has been > happening consistently on more than one. Any known issue here? Are you saying the test you sent us doesn't behave the same on different installations, or your actual application doesn't behave the same? I'm guessing that you either: 1. Have a trigger you don't know about. 2. Have another function of the same name, but in a different schema that is being called by mistake. Add a "RAISE NOTICE" to the function to find out. -- Richard Huxton Archonet Ltd
On 05/03/10 18:12, Mridula Mahadevan wrote: > Richard, To answer your questions, I have a live application that is > running on postgresql. We are seeing this issue on certain > installations and not on others. So the code is no different in each > set up. I also added the trigger to table B and then the foreign key > error is thrown in all set ups. But without the trigger a delete on > table B from within a function assumes cascade delete even when one > is not specified. Again only in some cases, I can send you the entire > procedure if it helps (the one I have below only has the relevant > parts). But does the test code you sent show this problem on: 1. all installations 2. some installations 3. none of the installations 4. Don't know - haven't tried the test code If the test code shows the problem then we know it's something basic in your PostgreSQL installations. If it doesn't then it's something in the setup of the databases. I don't think the problem has anything to do with the code of the function. You have checked that the code in your functions makes sense and looked at it on servers where it works and it doesn't. If the problem was there I'm sure you'd have seen it. -- Richard Huxton Archonet Ltd