On Thu, Sep 3, 2026 at 7:10 AM Zsolt Parragi <[email protected]> wrote: > > Currently a before insert trigger returning NULL can drop FOR PORTION > OF leftovers. This seems unintuitive to me, and the original thread > only discussed trigger firing order, not whether it should allow > dropping the leftovers.
I think this behavior is correct. If you return NULL from an INSERT trigger, we should skip the insert. The leftovers are supposed to execute as separate insert statements, so they should have the same behavior as regular statements. But I agree we ought to document it to avoid confusion. Here is a patch doing that. > Shouldn't this scenario either result in an error, or be at least very > clearly documented, or print some diagnostics? I first considered > proposing a patch that errors out for this scenario, but I am not sure > if that's the proper way to handle this. I don't think it should be an error or print a warning. > While looking into this I also found out that there's already a > precedent for this in the code, a cross partition UPDATE similarly > fires a before insert trigger, but with an important difference: in > that case, if the insert drops the row the UPDATE reports 0 rows, > while FOR PORTION OF always reports 1 rows, regardless if the > leftovers gets inserted or not. (UPDATE is also questionable, as it > deletes 1 row in that case, I am not saying that it's better, it's > just different) The command tag refers to the rows updated/deleted by the primary statement, not the temporal leftovers. In the case of a cross-partition update canceled by an insert trigger, returning 0 is appropriate, since we're talking about the row actually being updated. If the wire protocol had a way to add extra command tag information, I wouldn't mind including a supplemental number for how many leftovers were inserted. (This would also give us a way for ON CONFLICT DO UPDATE to distinguish between inserted & updated rows.) But changing the existing number creates ambiguity about what it means. > Perhaps a better question for 20 and later is: shouldn't the trigger > be able to check if this is a leftover row, or if it's a > partition-moving update? I agree that would be very useful. It has come up in a few other conversations from people testing this feature. Here is a patch for it: https://commitfest.postgresql.org/patch/7239/ If that doesn't meet your needs, please let me know. Yours, -- Paul ~{:-) [email protected]
From aadb22cbe11754cdac5c8bae511d492dd754449d Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" <[email protected]> Date: Thu, 3 Sep 2026 09:55:11 -0700 Subject: [PATCH v1] Document trigger behavior for temporal leftovers The leftovers inserted by an UPDATE/DELETE FOR PORTION OF fire insert triggers (as if they were separate statements). If a row-level BEFORE INSERT trigger returns NULL, we skip inserting the leftover. This commit adds a note documenting that behavior. Reported-by: Zsolt Parragi <[email protected]> Author: Paul A. Jungwirth <[email protected]> Discussion: https://postgr.es/m/CAN4CZFNSOA_LXWKC6sAOduWVsaxjzmFBNEHUQii89KUnMhBtcw%40mail.gmail.com Backpatch-through: 19 --- doc/src/sgml/ref/delete.sgml | 3 +++ doc/src/sgml/ref/update.sgml | 4 +++- 2 files changed, 6 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml index 3ab1f60525a..2ac25a3af5e 100644 --- a/doc/src/sgml/ref/delete.sgml +++ b/doc/src/sgml/ref/delete.sgml @@ -86,6 +86,9 @@ DELETE FROM [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * application time was completely deleted, extended before/after the change, or both. Multiranges never require two temporal leftovers, because one value can always contain whatever application time remains. + <literal>INSERT</literal> triggers fire for each inserted leftover. If a + row-level <literal>BEFORE INSERT</literal> trigger returns + <literal>NULL</literal>, no leftover is inserted. </para> <para> diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml index 3175671d475..8227aa036ee 100644 --- a/doc/src/sgml/ref/update.sgml +++ b/doc/src/sgml/ref/update.sgml @@ -88,7 +88,9 @@ UPDATE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] two inserted records, depending on whether the original application time was completely updated, extended before/after the change, or both. Multiranges never require two temporal leftovers, because one value can - always contain whatever application time remains. + always contain whatever application time remains. <literal>INSERT</literal> + triggers fire for each inserted leftover. If a row-level <literal>BEFORE + INSERT</literal> trigger returns <literal>NULL</literal>, no leftover is inserted. </para> <para> -- 2.47.3
