Re: Trigger transaction isolation

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(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
>
>