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@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
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
