Re: Foreign key behavior different in a function and outside

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).

-mridulan

-----Original Message-----
From: Richard Huxton [mailto:dev(at)archonet(dot)com]
Sent: Friday, March 05, 2010 1:27 AM
To: Mridula Mahadevan
Cc: Tom Lane; pgsql-general(at)postgresql(dot)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