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?
Yes this is exactly the behavior I'm looking for. Furthermore, if the row is updated more than once in the same transaction and the constraint has been deferred, or even if the constraint hasn't been deferred but the row has been updated since the trigger is queued (for example, if there are multiple writeable CTEs), then I'd like to skip the trigger body as if that update didn't occur. Essentially I'm looking for the same behavior as the builtin triggers that enforce referential integrity.
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.
Okay I think this is exactly what I'm looking for.
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.
Just to clarify, there's no way for SELECT FROM foo WHERE ctid = NEW.ctid to return a row that ordinary wouldn't be visible right? There's no magic going on with the qual on ctid that skips a visibility check right?