Add TG_* vars for FOR PORTION OF

Started by Paul A Jungwirth3 months ago3 messageshackers
Jump to latest
#1Paul A Jungwirth
pj@illuminatedcomputing.com

Dear Hackers,

Here are patches adding new TG_* vars to PL/pgSQL, PL/Python, PL/Perl,
and PL/Tcl, holding FOR PORTION OF details. These let triggers respond
appropriately when FOR PORTION OF is used in the original query. That
is useful for replication, auditing, and general functionality. We
ourselves need this information for temporal foreign key actions (but
in C, not in a PL)[0]/messages/by-id/CA+renyUts-ksRKL3V+9kTnRCAJdb8Xc3T1L+yCVscNZQKT4Yvw@mail.gmail.com. This also came up a few times in the v19 beta
feedback to FOR PORTION OF. In particular, it would help to include
FOR PORTION OF information even for INSERT triggers fired because of
the temporal leftovers.

Patch 1 is the same as patch 2 from [0]/messages/by-id/CA+renyUts-ksRKL3V+9kTnRCAJdb8Xc3T1L+yCVscNZQKT4Yvw@mail.gmail.com, but I'm repeating it here so
that the main patches compile.

The PL/pgSQL patch is fairly mature: I've been including it in
valid-time submissions for at least a few years now. The other PL/*
patches are new. They don't have to all be committed together, but I
think we should make the behavior as consistent as possible across
languages, so one commitfest entry seems best.

For Tcl, it's hard for me to judge if I'm presenting the information
in an idiomatic way. Any feedback would be appreciated!

[0]: /messages/by-id/CA+renyUts-ksRKL3V+9kTnRCAJdb8Xc3T1L+yCVscNZQKT4Yvw@mail.gmail.com

Yours,

--
Paul ~{:-)
pj@illuminatedcomputing.com

Attachments:

t253625_1
v1-0005-Expose-FOR-PORTION-OF-to-pltcl-triggers.patchtext/x-patch; charset=US-ASCII; name=v1-0005-Expose-FOR-PORTION-OF-to-pltcl-triggers.patchDownload+183-2
v1-0004-Expose-FOR-PORTION-OF-to-plpython-triggers.patchtext/x-patch; charset=US-ASCII; name=v1-0004-Expose-FOR-PORTION-OF-to-plpython-triggers.patchDownload+150-1
v1-0003-Expose-FOR-PORTION-OF-to-plperl-triggers.patchtext/x-patch; charset=US-ASCII; name=v1-0003-Expose-FOR-PORTION-OF-to-plperl-triggers.patchDownload+143-2
v1-0002-Expose-FOR-PORTION-OF-to-plpgsql-triggers.patchtext/x-patch; charset=US-ASCII; name=v1-0002-Expose-FOR-PORTION-OF-to-plpgsql-triggers.patchDownload+264-68
v1-0001-Add-tg_temporal-to-TriggerData.patchtext/x-patch; charset=US-ASCII; name=v1-0001-Add-tg_temporal-to-TriggerData.patchDownload+155-13
#2Zsolt Parragi
zsolt.parragi@percona.com
In reply to: Paul A Jungwirth (#1)
Re: Add TG_* vars for FOR PORTION OF

Hello!

I have a generic design question: as I mentioned in [1]/messages/by-id/CA+renyV1Y4hzdG=btbUc69=cCxOGBFqSGJ5CrOrB15YH6PXdrg@mail.gmail.com, we also have a similar situation outside FOR PORTION OF when a plain update moves a record across partitions. Maybe we should have a detection logic that can answer this question in a generic way, that could be also used by every similar code in the future? Giving users simply the ability to ask "Was this before insert trigger caused by an implicit insert?", for example.

@@ -2453,6 +2453,7 @@ typedef struct ForPortionOfExpr
Oid withoutPortionProc; /* SRF proc for old_range - target_range */
ParseLoc location; /* token location, or -1 if unknown */
ParseLoc targetLocation; /* token location, or -1 if unknown */
+ char *range_name; /* Range name */
} ForPortionOfExpr;

Is this an (accidental) partial revert of 7090c696cc9?

+        Also set for the implicit <literal>INSERT</literal> statements to add
+        the <glossterm linkend="glossary-temporal-leftovers">temporal
+        leftovers</glossterm>.

This doesn't seem to work on partitioned tables to me. With a hash partitioned setup, I see:

NOTICE: BEFORE UPDATE on ph0 : TG_PERIOD_NAME=valid_at
NOTICE: BEFORE DELETE on ph0 : TG_PERIOD_NAME=valid_at
NOTICE: BEFORE INSERT on ph3 : TG_PERIOD_NAME=<NULL>
NOTICE: AFTER INSERT on ph3 : TG_PERIOD_NAME=<NULL>
NOTICE: BEFORE INSERT on ph1 : TG_PERIOD_NAME=<NULL>
NOTICE: AFTER INSERT on ph1 : TG_PERIOD_NAME=<NULL>
NOTICE: BEFORE INSERT on ph2 : TG_PERIOD_NAME=<NULL>
NOTICE: AFTER DELETE on ph0 : TG_PERIOD_NAME=valid_at
NOTICE: AFTER INSERT on ph2 : TG_PERIOD_NAME=<NULL>

Or with cross-partition row movement:

NOTICE: BEFORE DELETE on pl1 : TG_PERIOD_NAME=valid_at
NOTICE: BEFORE INSERT on pl2 : TG_PERIOD_NAME=<NULL>
NOTICE: AFTER DELETE on pl1 : TG_PERIOD_NAME=valid_at
NOTICE: AFTER INSERT on pl2 : TG_PERIOD_NAME=<NULL>

+ <structfield>fp_leftoverstypcache</structfield> is the type cache entry of the range column,

This seems to be NULL for BEFORE ROW triggers, as it is populated after them? That should be at least mentioned.

+  END IF;
+  RETURN CASE WHEN 'TG_OP' = 'DELETE' THEN OLD ELSE NEW END;

This will always evaluate to false, it compares 'TG_OP' as string

One more documentation / possibly release note question: should we mention that some additional names are now 'reserved'? If there is a preexisting plpgsql function using a variable named tg_period_name, it will start failing with this patch applied. Even if that is unlikely, it seems like a good idea to mention this change somewhere.

[1]: /messages/by-id/CA+renyV1Y4hzdG=btbUc69=cCxOGBFqSGJ5CrOrB15YH6PXdrg@mail.gmail.com

#3Paul A Jungwirth
pj@illuminatedcomputing.com
In reply to: Zsolt Parragi (#2)
Re: Add TG_* vars for FOR PORTION OF

On Sun, Sep 6, 2026 at 6:10 AM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

Hello!

I have a generic design question: as I mentioned in [1], we also have
a similar situation outside FOR PORTION OF when a plain update moves a
record across partitions. Maybe we should have a detection logic that
can answer this question in a generic way, that could be also used by
every similar code in the future? Giving users simply the ability to
ask "Was this before insert trigger caused by an implicit insert?",
for example.

Thank you for the review! Since FPO was reverted from v19, I will fold
your feedback into a new submission for v20. I'm going to mark the
commitfest entry for this with as Withdrawn.

Yours,

--
Paul ~{:-)
pj@illuminatedcomputing.com