Re: Is NEW.ctid usable as table_tuple_satisfies_snapshot?

On Fri, May 26, 2023 at 8:04 AM Kaiting Chen <ktchen14(at)gmail(dot)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.

David J.