Re: Is NEW.ctid usable as table_tuple_satisfies_snapshot?

On Fri, May 26, 2023 at 11:34 AM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> 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?
>

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?