Contents
Can a constraint trigger be created in PostgreSQL?
Finally, PostgreSQL has the option to create “constraint triggers” with CREATE CONSTRAINT TRIGGER. It sounds like such triggers could be used to avoid the race condition. Constraint triggers respect the MVCC rules, so they cannot “peek” at uncommitted rows of concurrent transactions.
How are constraint triggers used to avoid the race condition?
It sounds like such triggers could be used to avoid the race condition. Constraint triggers respect the MVCC rules, so they cannot “peek” at uncommitted rows of concurrent transactions. But the trigger execution can be deferred to the end of the transaction. They also have an entry in the pg_constraint system catalog.
When to check the race condition in PostgreSQL?
By making the trigger INITIALLY DEFERRED, we tell PostgreSQL to check the condition at COMMIT time. This will reduce the window for the race condition a little, but the problem is still there. If concurrent transactions run the trigger function at the same time, they won’t see each other’s modifications.
How does PostgreSQL check constraints in MVCC?
When checking constraints, PostgreSQL also checks rows that would normally not be visible to the current transaction. This is against the normal MVCC rules but guarantees that constraints are not vulnerable to this race condition.
Are there NOT NULL constraints in PostgreSQL?
Not-null constraints are represented in the pg_attribute catalog, not here. User-defined constraint triggers (created with CREATE CONSTRAINT TRIGGER) also give rise to an entry in this table.
Can a trigger be associated with a DEFERRABLE Constraint?
However, it is possible for a non-deferrable trigger to be associated with a deferrable constraint: foreign key constraints can have some deferrable and some non-deferrable triggers. Note: pg_class.relhastriggers must be true if a relation has any triggers in this catalog.
What is the catalog of PG constraint in PostgreSQL?
PostgreSQL: Documentation: 9.1: pg_constraint 45.12. pg_constraint The catalog pg_constraint stores check, primary key, unique, foreign key, and exclusion constraints on tables. (Column constraints are not treated specially.