Re: Trigger transaction isolation

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(at)alqualonde(dot)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(dot)klaver(at)aklaver(dot)com