Re: query visibility - trigger order - bug?

[ resending because private mail bounced ]

"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <surrano@mailbox.hu> writes:
> Now I think I can see the problem. To make it simple:

> * pack_biu BEFORE INSERT runs first. It's OK.
> * pack_biu BEFORE UPDATE runs before ...
> * ... pack_aiu AFTER INSERT, so it doesn't see the rows that pack_aiu
> "inserted" (past tense is supposed by me, not the facts) into pack_item.

> The contradiction: the INSERT query precedes the UPDATE query in time (see
> source of parcel_biud), so the latter should see the effects of the former.

Oh, I see the issue.  The problem here is that AFTER triggers run at
completion of the outermost command --- which here is the "UPDATE
parcel" command that fired parcel_biu.  We don't fire AFTER triggers
at completion of individual SQL commands invoked by a plpgsql function.

(One reason for not doing that is that as the code is presently set up,
it would also fire any queued AFTER triggers belonging to the outer
command ... which would definitely be wrong.  There'd need to be some
way to distinguish which command had queued a trigger.)

I'd suggest getting rid of your AFTER trigger (pack_aiu) and performing
the same actions in pack_biu --- is there a reason to have two triggers
here?

            regards, tom lane