Thank you, Tom, for this valuable information.
On Tue, 01 Sep 2020 11:02:01 -0400
1Tom Lane <tgl@sss.pgh.pa.us> wrote:
> visibility rules are the same as for any other function. So the
> answer to the OP's question depends on the transaction's isolation
> level and (for typical PLs) on whether the function is VOLATILE or
> not.
>
> serializable -> the transaction's initial snapshot is used throughout
I suppose by writing serializable, you include repeatable read here,
too?
>
> non serializable, volatile function -> each statement in the function
> takes a fresh snapshot
This is the needed behaviour for the validations I intend to do, then.
>
> non serializable, non-volatile function -> the function uses a single
> snapshot. For a non-deferred trigger, I think it shares the snapshot
> used by the triggering query. Not sure about exactly when the
> snapshot is taken for a deferred trigger.
So this case seems to be trickier than it looks at a first glance.
It depends heavily on the use case, of course, in how far the snapshot
time influences the correctness of a trigger written in this mode.
I suggest adding this to the documentation and will try to use the
commenting function there for it.
Thanks!
Dirk