postgres trigger constraints - ghdrako/doc_snipets GitHub Wiki

• https://www.cybertec-postgresql.com/en/triggers-to-enforce-constraints/#what-is-wrong-with-our-trigger-constraint

Problem jest ze trigger nie widzi zmian nieskomitowanych w innej sesji przez MVCC wiec nie może wykryć race condition.

Systemowe constrainty nie maja tego problemu. constrainty systemowe NIE używają zwykłego MVCC.PostgreSQL ma specjalny kod w C dla:UNIQUE,PRIMARY KEY,FOREIGN KEY który potrafi:

• zaglądać do uncommitted tuples
• robić specjalne locki
• sprawdzać pending rows PostgreSQL also checks rows that would normally not be visible

Unikalnosc z uzyciem tabeli unikalnosci i trigera dla tabel partycjonowanych jest bezpiieczny : trigger + registry table + UNIQUE bo unikalnosci plinuje PRIMARY KEY na registry table wiec uzywane sa specjalne mechanizmy postgresa zapobiegajace race condition co opisano wyzej.

Czyli niebezpieczne sa triggery w ktorych wykonujemy sql-a i na tej podstawie cos robimy bo podlegaja MVCC.

Przeciwdzialanie:

1. explicit locking - czyli ręczne blokowanie wierszy (SELECT ... FOR UPDATE) tak, żeby konkurencyjne transakcje nie mogły równocześnie przejść walidacji constraintu.

Lockowanie rekordów

Zamiast:

SELECT count(*)

robi:

SELECT *
FROM on_duty
WHERE prison_id = OLD.prison_id
FOR UPDATE;

Jeśli robisz constraint triggerem:

musisz samodzielnie zapewnić serializację

bo PostgreSQL:

• nie zrobi tego za Ciebie
• trigger działa normalnie pod MVCC

Trzeba lockować w ustalonej kolejności

W artykule jest:

ORDER BY guard_id
FOR KEY SHARE

Dlaczego ORDER BY Żeby uniknąć deadlock

FOR KEY SHARE

• to słabszy lock niż FOR UPDATE
• wystarcza do ochrony przed DELETE
• daje lepszą concurrency