Is NEW.ctid usable as table_tuple_satisfies_snapshot?

From: Kaiting Chen <ktchen14(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Is NEW.ctid usable as table_tuple_satisfies_snapshot?
Date: 2023-05-26 15:04:08
Message-ID: CA+CLzG9Ht1-CuzZfhgsGV1Lauxt6Qh1vf-i02iOnkHyryae0rw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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!

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David G. Johnston 2023-05-26 15:33:43 Re: Is NEW.ctid usable as table_tuple_satisfies_snapshot?
Previous Message Jonathan S. Katz 2023-05-26 14:37:57 Re: vector search support