Re: Fix RETURNING side effects for FOR PORTION OF leftover rows

On Sat, Jul 25, 2026 at 3:16 AM Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>
> The fix looks good to me, and the point about cross-partition updates
> is a good one.
>
> Attached is a v3 patch, in which I reworked the comments a bit, and
> moved the logic of the new check to the place it's actually used. I
> also changed the tests to use a NOTICE-raising function instead of a
> sequence, since that makes it clearer which rows are/were being
> processed by the RETURNING clause.

This all seems good. Using NOTICE does seem a little nicer.

The commit message has a small typo:

. . . so that the auxiliary INSERT of a
cross-partition UPDATE with a FOR PORTION OF clause still process
<-- s/b processes
RETURNING normally.

I tried to break the fix by using a CTE, but (as expected) that wasn't
a problem, since the outer query has a different executor node. I
don't think my tests need to be included in the commit, but if you
want them, here they are:

+-- The RETURNING list is not evaluated for the leftovers when the
+-- UPDATE ... FOR PORTION OF is in a CTE either, whether the outer query
+-- is a SELECT ...
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[12,13)', '[2018-01-01,2020-01-01)', 'twelve');
+WITH updated AS (
+ UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-05-01'
+ SET name = 'twelve^1'
+ WHERE id = '[12,13)'
+ RETURNING *, fpo_returning_row(for_portion_of_test::text)
+)
+SELECT * FROM updated;
+SELECT * FROM for_portion_of_test WHERE id = '[12,13)' ORDER BY id, valid_at;
+
+-- ... or an INSERT:
+CREATE TABLE fpo_returning_log (id int4range, valid_at daterange,
name text, r text);
+INSERT INTO for_portion_of_test (id, valid_at, name) VALUES
+ ('[13,14)', '[2018-01-01,2020-01-01)', 'thirteen');
+WITH updated AS (
+ UPDATE for_portion_of_test
+ FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-05-01'
+ SET name = 'thirteen^1'
+ WHERE id = '[13,14)'
+ RETURNING *, fpo_returning_row(for_portion_of_test::text)
+)
+INSERT INTO fpo_returning_log SELECT * FROM updated;
+SELECT * FROM fpo_returning_log ORDER BY id, valid_at;
+SELECT * FROM for_portion_of_test WHERE id = '[13,14)' ORDER BY id, valid_at;
+
+-- clean up:
+DROP TABLE fpo_returning_log;
+DELETE FROM for_portion_of_test WHERE id IN ('[12,13)', '[13,14)');

Yours,

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com