On Fri, May 26, 2023 at 8:04 AM Kaiting Chen <ktchen14@gmail.com> wrote:
I need to implement a trigger that will behave similarly to a foreign key constraint. The trigger itself will be created with:
CREATE CONSTRAINT TRIGGER ... AFTER INSERT OR UPDATE OF ... ON foo
I'd like to skip execution of the trigger logic if, by the time that the trigger is executed, the NEW row is no longer valid.
To be clear, when using deferrable constraints and insert then subsequently delete a row you wish to return-early in the insert trigger body as if the row had never been inserted in the first place?
The table_tuple_satisfies_snapshot() function is obviously unavailable from PL/pgSQL. Is this a reliable substitute?
If a row is not visible at the time the trigger fires the SELECT will not return it. When the deferred trigger eventually fires the inserted row will no longer exist and SELECT will not return it.
The above is not tested; assuming it does indeed behave that way I would expect the behavior to be deterministic given that there are no concurrency issues involved.
IF NOT EXISTS (SELECT FROM foo WHERE ctid = NEW.ctid) THEN RETURN NULL;
END IF;
Specifically:
1. Is there any possibility that, by the time the trigger function is called, the NEW row's ctid no longer refers to the row version in NEW, but to an entirely different row? For example, is it possible for VACUUM to reclaim the space at that page number and offset in between the INSERT/UPDATE and when the trigger function is called?
No. Transaction and MVCC semantics prevent that from happening.
2. If I lookup the row by its ctid, will the visibility map be consulted.
No, but that doesn't seem to be material anyway. Your user-space pl/pgsql function shouldn't care about such a purely performance optimization.