Thread

Re: UPDATE/DELETE FOR PORTION OF fire FOR EACH STATEMENT more than once

Paul A Jungwirth <pj@illuminatedcomputing.com> — 2026-05-04T01:14:21Z

On Sun, May 3, 2026 at 3:50 PM Thom Brown <thom@linux.com> wrote:
>
> Actually, I'm wrong here. It outputs all at depth 1. This would have
> been a way to implement a barrier into the function so that you can
> decide whether the trigger only fires for the original statement.
>
> So might there be a scenario where a user would wish to distinguish
> between an explicit INSERT from an INSERT generated by the temporal
> table machinery? None of the TG_* variables expose anything that would
> allow one to do this. Sure, the user could pattern match against
> current_query() and see that it really is an INSERT, but it's somewhat
> kludgy.

That's one way, but I agree it's kludgy. I have some uncommitted
patches at [1] to (1) expose the FOR PORTION OF parameters to triggers
in the C struct (2) use that info ourselves to implement CASCADE/SET
NULL/SET DEFAULT against temporal foreign keys (3) expose the same
information in PL/pgSQL TG_* variables. But those would only be set
for UPDATE/DELETE triggers, not the INSERT trigger. But maybe we
should pass them to INSERT triggers too, if the insert is the result
of an UPDATE/DELETE FOR PORTION OF? It seems a little strange, but it
would be unambiguous, and it would give useful information to users
who want to bypass triggers in that case.

[1] https://commitfest.postgresql.org/patch/5836/

Yours,

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