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. For a normal FOREIGN KEY trigger,
this is handled in ri_triggers.c by:
/*
* We should not even consider checking the row if it is no longer valid,
* since it was either deleted (so the deferred check should be skipped)
* or updated (in which case only the latest version of the row should be
* checked). Test its liveness according to SnapshotSelf. We need pin
* and lock on the buffer to call HeapTupleSatisfiesVisibility. Caller
* should be holding pin, but not lock.
*/
if (!table_tuple_satisfies_snapshot(trigdata->tg_relation, newslot, SnapshotSelf))
return PointerGetDatum(NULL);
The table_tuple_satisfies_snapshot() function is obviously unavailable from
PL/pgSQL. Is this a reliable substitute?
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?
2. If I lookup the row by its ctid, will the visibility map be consulted. And if
so, is there any material difference between what that would do vs what
table_tuple_satisfies_snapshot() does?
Thanks!