Re: RI within PLPGSQL

On Sat, 13 Dec 2003, cnliou wrote:

> Thank you very much for your explanation!
>
> Includem"Stephan Szabo" <sszabo(at)megazone(dot)bigpanda(dot)com>n
> wrote:
> >There have been discussions in the past about when cascade
> events
> >should occur. The code currently does what I believe was
> last
> >agreed upon, although its behavior is fairly wierd for
> deferred
> >constraints and functions. Right now the cascade happens
> at the
> >end of the full statement (in this case the call to the
> function)
> >which is why you get a key constraint error in the second
> call to f1
> >and why the later inserted row is removed in f2.
>
> It sounds to me that the only solution to my case is
> executing
>
> DELETE FROM referenced_table
>
> and
>
> INSERT INTO referencing_table
>
> in seperate transactions. Please correct me if I am wrong.

I think they only need to be in separate outer statements for non-deferred
triggers. It's just that the full set of triggered actions for the
function count as part of the one statement that calls it.

So, from psql, sending separate statements
DELETE FROM ... ;
INSERT INTO ... ;
should work, but a function body
'DELETE FROM ...;
INSERT INTO ...;'
counts as one statement and so the delete action happens after the insert.

> I also feel it might be a good idea to include an example
> like the one in my previous message in the documentation so
> that this question hopefully will not be asked repeatedly.

I think the behavior of this hasn't entirely solidified yet. It's still
possible that it'll change as although we came to a behavior set, if it
can be shown to break the spec's requirements, it'll be changed.