Обсуждение: Is NEW.ctid usable as table_tuple_satisfies_snapshot?
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;
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!
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!
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?
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.
On Fri, May 26, 2023 at 11:34 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
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?
Kaiting Chen <ktchen14@gmail.com> writes:
> On Fri, May 26, 2023 at 11:34 AM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>> On Fri, May 26, 2023 at 8:04 AM Kaiting Chen <ktchen14@gmail.com> wrote:
>>> 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.
It'd be a waste of cycles to consult the map in this usage, since the
tuple of interest is surely not all-visible and thus the page couldn't
be either.
> 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?
No, a ctid test isn't magic in that way; nodeTidscan.c applies the
same snapshot check as any other relation scan.
regards, tom lane
On Fri, May 26, 2023 at 12:49 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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?
No, a ctid test isn't magic in that way; nodeTidscan.c applies the
same snapshot check as any other relation scan.
Okay thanks!