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@xxxxxxxxxxxxx> 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 > >
