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
