Thread: Trigger transaction isolation

Trigger transaction isolation

From
Dirk Lattermann
Date:
Hello,

I'd like to understand the visibility of data changes made by other
transactions when executing SQL commands in a trigger function in READ
COMMITTED isolation level.
I could not find this covered in the trigger documentation (which
already has some good sections about SQL command visibility for several
trigger types), and I don't think it is practically possible to infer
this from observations of the behaviour.

So, if during an SQL command that triggers a trigger, another
transaction commits, do the SQL commands in the trigger that start
after that commit see the changes of the other transaction or do they
see the state as it was when the triggering command was
started?

If they do see the changes, then I could implement a
constraint check without race condition based on the contents of some
other table using a lock on that table (say, to check for relation
cycles, or, in the same table, to limit the number of records).
If they don't see the changes, then I fear the race condition free check
can only be implemented using the SERIALIZABLE isolation level, which I
cannot really use in my situation for performance reasons and the retry
overhead. I know that using a lock might lead to a deadlock, but I'd
want to give it a try.

Thank you very much.
Dirk Lattermann



Re: Trigger transaction isolation

From
Dirk Lattermann
Date:
Hello!

Since unfortunately nobody has yet replied to my question, I'd like to
know if this is the right list to ask this question on or if I should
try another mailing list.
Maybe the answer is too obvious, but in that case I'd appreciate a
short hint to help me finding it.
Maybe it's a hard question, then the answer will be even more
interesting...?

Thanks again,
Dirk Lattermann

On Tue, 25 Aug 2020 11:12:35 +0200
Dirk Lattermann <dlatt@alqualonde.de> wrote:

> Hello,
> 
> I'd like to understand the visibility of data changes made by other
> transactions when executing SQL commands in a trigger function in READ
> COMMITTED isolation level.
> I could not find this covered in the trigger documentation (which
> already has some good sections about SQL command visibility for
> several trigger types), and I don't think it is practically possible
> to infer this from observations of the behaviour.
> 
> So, if during an SQL command that triggers a trigger, another
> transaction commits, do the SQL commands in the trigger that start
> after that commit see the changes of the other transaction or do they
> see the state as it was when the triggering command was
> started?
> 
> If they do see the changes, then I could implement a
> constraint check without race condition based on the contents of some
> other table using a lock on that table (say, to check for relation
> cycles, or, in the same table, to limit the number of records).
> If they don't see the changes, then I fear the race condition free
> check can only be implemented using the SERIALIZABLE isolation level,
> which I cannot really use in my situation for performance reasons and
> the retry overhead. I know that using a lock might lead to a
> deadlock, but I'd want to give it a try.
> 
> Thank you very much.
> Dirk Lattermann
> 
> 




Re: Trigger transaction isolation

From
Adrian Klaver
Date:
On 9/1/20 7:07 AM, Dirk Lattermann wrote:
> Hello!
> 
> Since unfortunately nobody has yet replied to my question, I'd like to
> know if this is the right list to ask this question on or if I should
> try another mailing list.
> Maybe the answer is too obvious, but in that case I'd appreciate a
> short hint to help me finding it.
> Maybe it's a hard question, then the answer will be even more
> interesting...?

As I understand it a trigger function runs in its own transaction so the 
rules from below apply:

https://www.postgresql.org/docs/12/transaction-iso.html

13.2.1. Read Committed Isolation Level

> 
> Thanks again,
> Dirk Lattermann
> 
> On Tue, 25 Aug 2020 11:12:35 +0200
> Dirk Lattermann <dlatt@alqualonde.de> wrote:
> 
>> Hello,
>>
>> I'd like to understand the visibility of data changes made by other
>> transactions when executing SQL commands in a trigger function in READ
>> COMMITTED isolation level.
>> I could not find this covered in the trigger documentation (which
>> already has some good sections about SQL command visibility for
>> several trigger types), and I don't think it is practically possible
>> to infer this from observations of the behaviour.
>>
>> So, if during an SQL command that triggers a trigger, another
>> transaction commits, do the SQL commands in the trigger that start
>> after that commit see the changes of the other transaction or do they
>> see the state as it was when the triggering command was
>> started?
>>
>> If they do see the changes, then I could implement a
>> constraint check without race condition based on the contents of some
>> other table using a lock on that table (say, to check for relation
>> cycles, or, in the same table, to limit the number of records).
>> If they don't see the changes, then I fear the race condition free
>> check can only be implemented using the SERIALIZABLE isolation level,
>> which I cannot really use in my situation for performance reasons and
>> the retry overhead. I know that using a lock might lead to a
>> deadlock, but I'd want to give it a try.
>>
>> Thank you very much.
>> Dirk Lattermann
>>
>>
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Trigger transaction isolation

From
Tom Lane
Date:
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> As I understand it a trigger function runs in its own transaction so the 
> rules from below apply:
> https://www.postgresql.org/docs/12/transaction-iso.html

No, a trigger is part of the calling transaction.  There's nothing special
about it other than the condition causing the call; 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

non serializable, volatile function -> each statement in the function
takes a fresh snapshot

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.

            regards, tom lane



Re: Trigger transaction isolation

From
Dirk Lattermann
Date:
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