Re: Trigger transaction isolation

Thank you, Tom, for this valuable information.

On Tue, 01 Sep 2020 11:02:01 -0400
1Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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