Re: 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