Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wBJue-000wPu-32 for pgsql-hackers@arkaria.postgresql.org; Fri, 10 Apr 2026 22:01:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wBJuc-00Efxf-2g for pgsql-hackers@arkaria.postgresql.org; Fri, 10 Apr 2026 22:01:15 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wBJuc-00EfxW-0y for pgsql-hackers@lists.postgresql.org; Fri, 10 Apr 2026 22:01:15 +0000 Received: from mail-vk1-xa33.google.com ([2607:f8b0:4864:20::a33]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wBJuX-00000000Os8-4B6D for pgsql-hackers@lists.postgresql.org; Fri, 10 Apr 2026 22:01:14 +0000 Received: by mail-vk1-xa33.google.com with SMTP id 71dfb90a1353d-56dfd007d31so1766354e0c.3 for ; Fri, 10 Apr 2026 15:01:10 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1775858469; cv=none; d=google.com; s=arc-20240605; b=iUoKkHetpIhu4YzuInNNX2SI4QTCkMnuhvhqcrEIyHQ7uhuMyqwaANQTVyERr4uGTN TCFWh/DbnAYcU5Kr0isI4bdid8q7zH+uHhwd+su1ihzhdA96O1jRw6Q/1hpEB1hU4e3r Yy8peP1krStjmerQ7PWqzalfQoXvT/ht5Oq6i+oXc2Qi4qFAmfoLHgUq+gurCekNF1cc +qd25JQd9KKqSkKEIdtIlR5dLaDPgPFlOiAej+RQStYB3BRC0Kc2NkzoE5IKjvFOG12q Z6I3vOExw41YOMLRjaybmJFbJ47UEo8gHOlJuwkjfUJix3r5gi2ioYS2Vw4xaLsJyb6c nPMw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=OF14sAEZsgYdCP1gFu1przb4H839MBRkUpgokFsPsfA=; fh=qau0P7ny56wCSSjdZJ6KwctdnQX8zRgSOYLlT23xjRY=; b=anj4QDwY9FeWkHAWNpqn7kI+5AXfPC6fMI+rQ7f+6xEJl1ztGtOyN6sLRMi3sg6TgF gM5HxkNVvvQri/Ms5hbbm72mWcy2QeTIA+kYHqyGDwCB4aHcBeerZXMdzOBQfqm1EOjr mYyjXeCmauOPb54yJTSWKl0Ap1IrYDvhAEFLanwu1WxGY0WHDwEtVWwvSiaTwJsl/l0Z NpyH08wEKi78HdlQ2fe+3icEWa/XU9+q/vZIW9nzkhh6wjnLpALCwwRNS1EhRALFs/Qq rpAyik5GtiupEErTP3ZP1ptTwexP7pU8n09MoaU8PrDwuPVqPYTwKAb6AJvK74grUsQZ 5rWg==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1775858469; x=1776463269; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=OF14sAEZsgYdCP1gFu1przb4H839MBRkUpgokFsPsfA=; b=Plm4IOrTNm03Z4+ji7IirsN3cm74MWdBVXnc1qdwP6Gzqjl9AUS5VVffRrRxvtwNst c6ynUft60KQTtww153Yum3OA51HDFcv5JBpKhsCx7v1vctCuyG7S3xcH2VAiHQpe/FcO lkaocYj2dO/vcXGZ0l1V8AuqzTzcXn6PmNOGadXOgCAb9pOvqWwy6hdEBLluSyKBIqpz JpPnGPe7FL7GEqJTVsYB+yVpl/yzUXyb8kqoolBoVNiC+T8LBDyyB0KmEIkLVpkGFgYC o3zCESO5vO0P17kkiCgdlFzt8CYx9dX0Mek8UFibmPjJa/q8uBZwD4Rm9uOaNyijQdjr TR6A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1775858469; x=1776463269; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=OF14sAEZsgYdCP1gFu1przb4H839MBRkUpgokFsPsfA=; b=EUwRVKlBjilv2LnQc93Ix16BYONQ9Spu8hGZBLmtdMS0bCZBxMTUnkA9LUfDUrDvzh DwUTZoj9XYxgKtKtyv3VWXiM+yO/BWJnHMW4Q1f6q74sWl7afruE3H4CW1zszJoBVdv+ ndT3olkFB6IKnu0d0fySnu0ys+5LmtK5q96iMSytoMIc+6DDslpKxLpy6by2v0TUTjbA R/NxSCYKTR9AIrL7dXbH1kcepQopZR7XERmNy9uPupMCocbL+QIzDoVDs5qazTCurlFI 1Bhq4qBfVDZBSDXtRFn1Kfm/qJT5u717BfFbCkxFPDvPTzMop9oU53ZPPBkkAXjwqhQU GSgA== X-Gm-Message-State: AOJu0YxqVlVWmhqdz5I1mnWUq2+2pn+4z+mkhSlRewlY27Flyrr7840X etqdl7FcXorVR+TVWLjSH4uJE8LAGRWG/Wy4ftGtKf/ZvXveyHAwk1qwOHCaGTleRwMm9JOVq6a Xe8gLjpP9CumyEKBcm2lrmupc8RX0m1c= X-Gm-Gg: AeBDievDf7Bl8YxFJ2Z2MB8IbkIpHYKQtpU+eKnVIc84sONk5DYMQLzCTgajM/TkBBs aZZP1aL2LyCCJ2cnKpcxjT/slIBHfyiPt+EuO+DwOvF366rfGcgPFHb3bbT8UOhw1OwWf5lIure a3TSqYA8r2IZ78rwMNEB5jDj5GKIbeZaVkm0EnZm5Kdi2TMsaNC9pbszlTGIxfpeB1C7v0+x+Sj 6NNjldEhi56eSH6dh2Ys//bZZoC4NP4Xgftxt8J+gFWa4JZ/zSMmgjM7arada880ea7/t6IJxMT l9fj7Ya9PEbWRw16qYZy8DjOI9k5oOtlChy3zrUg2pOenVqM X-Received: by 2002:a05:6102:3f12:b0:603:273f:3576 with SMTP id ada2fe7eead31-609ff5cdf82mr2725659137.10.1775858468661; Fri, 10 Apr 2026 15:01:08 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: SATYANARAYANA NARLAPURAM Date: Fri, 10 Apr 2026 15:00:57 -0700 X-Gm-Features: AQROBzBCmWG3WdiDC8pD4RAfWjVrD0v04zU7rUpnthMjCOaWswwjdsdVc3WrWxU Message-ID: Subject: Re: FOR PORTION OF does not recompute GENERATED STORED columns that depend on the range column To: jian he Cc: PostgreSQL Hackers Content-Type: multipart/alternative; boundary="000000000000322e33064f2245ea" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000322e33064f2245ea Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Jian, On Fri, Apr 10, 2026 at 2:11=E2=80=AFAM jian he wrote: > Hi. > > ExecUpdate->ExecUpdateEpilogue->ExecForPortionOfLeftovers > In ExecForPortionOfLeftovers, we have > """ > if (!resultRelInfo->ri_forPortionOf) > { > /* > * If we don't have a ForPortionOfState yet, we must be a partiti= on > * child being hit for the first time. Make a copy from the root, > with > * our own tupleTableSlot. We do this lazily so that we don't pay > the > * price of unused partitions. > */ > ForPortionOfState *leafState =3D makeNode(ForPortionOfState); > } > """ > We reached the end of ExecUpdate, and then suddenly initialized > resultRelInfo->ri_forPortionOf. > That seems wrong; we should initialize resultRelInfo->ri_forPortionOf > earlier so other places can use that information, such as > ForPortionOfState->fp_rangeAttno. > > We can initialize ForPortionOfState right after ExecModifyTable: > """ > /* If it's not the same as last time, we need to locate the rel */ > if (resultoid !=3D node->mt_lastResultOid) > resultRelInfo =3D ExecLookupResultRelByOid(node, resultoid, > false, true); > """ > > In ExecForPortionOfLeftovers, we should use ForPortionOfState more and > ForPortionOfExpr less. > (ForPortionOfExpr and ForPortionOfState share some overlapping informatio= n; > maybe we can eliminate some common fields or put ForPortionOfExpr into > ForPortionOfState). > > > As noted in [1], the FOR PORTION OF column is physically modified, > even though we didn't require explicit UPDATE privileges, > we failed to track this column in ExecGetUpdatedCols and > ExecGetExtraUpdatedCols. > This omission directly impacts the ExecInsertIndexTuples -> > index_unchanged_by_update -> ExecGetExtraUpdatedCols execution path. > We should ensure ExecGetExtraUpdatedCols also accounts for this column. > Otherwise, we need a clearer explanation for why > index_unchanged_by_update can safely ignore a column that is being > physically modified. > > I have added regression test cases for CREATE TRIGGER UPDATE OF > column_name. > > The attached patch also addressed the table inheritance issue in > > https://postgr.es/m/CAHg+QDcsXsUVaZ+JwM02yDRQEi=3DcL_rTH_ROLDYgOx004sQu7A= @mail.gmail.com > > I've combined all these changes into a single patch for now, as they > seem closely related. > > [1]: > https://postgr.es/m/CACJufxHALFKca5SMn5DNnbrX2trPamVL6napn_nm35p15yw+rg@m= ail.gmail.com I applied your patch and tested. The following scenarios are now passing: (1) table inheritance issue I reported in [1], (2) issue reported in this thread. Following are still failing: (1) instead of triggers + views, mentioned in the thread [2], it has both the test case and the fix. (2) For Portion Of DELETE loses rows when a BEFORE INSERT trigger returns NULL DROP TABLE IF EXISTS subscriptions CASCADE; CREATE TABLE subscriptions ( sub_id int, period int4range NOT NULL, plan text ); CREATE OR REPLACE FUNCTION reject_new_subscriptions() RETURNS trigger AS $$ BEGIN -- Business rule: no new subscription rows allowed via INSERT. RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER no_new_subs BEFORE INSERT ON subscriptions FOR EACH ROW EXECUTE FUNCTION reject_new_subscriptions(); -- Pre-existing row (bypass trigger to seed it). ALTER TABLE subscriptions DISABLE TRIGGER no_new_subs; INSERT INTO subscriptions VALUES (1, '[1,100)', 'premium'); ALTER TABLE subscriptions ENABLE TRIGGER no_new_subs; SELECT * FROM subscriptions; -- 1 row: (1, [1,100), premium) -- Delete just the [40,60) slice. DELETE FROM subscriptions FOR PORTION OF period FROM 40 TO 60; SELECT * FROM subscriptions ORDER BY period; -- Should be two rows: [1,40) and [60,100) -- Actually: 0 rows. The whole subscription vanished. SELECT count(*) AS remaining FROM subscriptions; -- Expected 2, got 0. (3) FPO UPDATE loses leftovers the same way DROP TABLE IF EXISTS room_bookings CASCADE; CREATE TABLE room_bookings ( booking_id int, slot int4range NOT NULL, note text ); CREATE OR REPLACE FUNCTION block_booking_inserts() RETURNS trigger AS $$ BEGIN -- Business rule: bookings created only through an API layer. RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER booking_guard BEFORE INSERT ON room_bookings FOR EACH ROW EXECUTE FUNCTION block_booking_inserts(); ALTER TABLE room_bookings DISABLE TRIGGER booking_guard; INSERT INTO room_bookings VALUES (1, '[1,100)', 'team meeting'); ALTER TABLE room_bookings ENABLE TRIGGER booking_guard; SELECT * FROM room_bookings; -- 1 row: (1, [1,100), team meeting) -- Shorten the meeting to only [40,60). UPDATE room_bookings FOR PORTION OF slot FROM 40 TO 60 SET note =3D 'shortened'; SELECT * FROM room_bookings ORDER BY slot; -- Should be three rows: -- [1,40) team meeting -- [40,60) shortened -- [60,100) team meeting -- Actually: only the [40,60) row survives. SELECT count(*) AS remaining FROM room_bookings; -- Expected 3, got 1. [1]: https://postgr.es/m/CAHg+QDcsXsUVaZ+JwM02yDRQEi=3DcL_rTH_ROLDYgOx004sQu7A@m= ail.gmail.com [2]: https://www.postgresql.org/message-id/flat/CACJufxHALFKca5SMn5DNnbrX2trPamV= L6napn_nm35p15yw%2Brg%40mail.gmail.com#ab4216dc6828fb0d7ada48aab9e330d0 Thanks, Satya --000000000000322e33064f2245ea Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Jian,

On Fri, Apr= 10, 2026 at 2:11=E2=80=AFAM jian he <jian.universality@gmail.com> wrote:
Hi.

ExecUpdate->ExecUpdateEpilogue->ExecForPortionOfLeftovers
In ExecForPortionOfLeftovers, we have
"""
if (!resultRelInfo->ri_forPortionOf)
=C2=A0 =C2=A0 {
=C2=A0 =C2=A0 =C2=A0 =C2=A0 /*
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* If we don't have a ForPortionOfStat= e yet, we must be a partition
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* child being hit for the first time. Mak= e a copy from the root, with
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* our own tupleTableSlot. We do this lazi= ly so that we don't pay the
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* price of unused partitions.
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0*/
=C2=A0 =C2=A0 =C2=A0 =C2=A0 ForPortionOfState *leafState =3D makeNode(ForPo= rtionOfState);
}
"""
We reached the end of ExecUpdate, and then suddenly initialized
resultRelInfo->ri_forPortionOf.
That seems wrong; we should initialize resultRelInfo->ri_forPortionOf earlier so other places can use that information, such as
ForPortionOfState->fp_rangeAttno.

We can initialize ForPortionOfState right after ExecModifyTable:
"""
/* If it's not the same as last time, we need to locate the rel */
if (resultoid !=3D node->mt_lastResultOid)
=C2=A0 =C2=A0 resultRelInfo =3D ExecLookupResultRelByOid(node, resultoid, =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 false, true);
"""

In ExecForPortionOfLeftovers, we should use ForPortionOfState more and
ForPortionOfExpr less.
(ForPortionOfExpr and ForPortionOfState share some overlapping information;=
maybe we can eliminate some common fields or put ForPortionOfExpr into
ForPortionOfState).


As noted in [1], the FOR PORTION OF column is physically modified,
even though we didn't require explicit UPDATE privileges,
we failed to track this column in ExecGetUpdatedCols and
ExecGetExtraUpdatedCols.
This omission directly impacts the ExecInsertIndexTuples ->
index_unchanged_by_update -> ExecGetExtraUpdatedCols execution path.
We should ensure ExecGetExtraUpdatedCols also accounts for this column.
Otherwise, we need a clearer explanation for why
index_unchanged_by_update can safely ignore a column that is being
physically modified.

I have added regression test cases for CREATE TRIGGER UPDATE OF column_name= .

The attached patch also addressed the table inheritance issue in
https://post= gr.es/m/CAHg+QDcsXsUVaZ+JwM02yDRQEi=3DcL_rTH_ROLDYgOx004sQu7A@mail.gmail.co= m

I've combined all these changes into a single patch for now, as they seem closely related.

[1]: https://p= ostgr.es/m/CACJufxHALFKca5SMn5DNnbrX2trPamVL6napn_nm35p15yw+rg@mail.gmail.c= om

I applied your patch and tested. The= following scenarios are now passing:=C2=A0 (1) table inheritance issue I r= eported in [1], (2) issue reported in this thread.

Following are still failing:=C2=A0

(1) instead of= triggers + views, mentioned in the thread [2], it has both the test case a= nd the fix.




(2)=C2=A0For Portion Of DELETE loses rows when a BEFORE INSERT trigg= er returns NULL

DROP TABLE IF EXISTS subscriptions= CASCADE;
CREATE TABLE subscriptions (
=C2=A0 =C2=A0 sub_id =C2=A0 in= t,
=C2=A0 =C2=A0 period =C2=A0 int4range NOT NULL,
=C2=A0 =C2=A0 plan= =C2=A0 =C2=A0 text
);

CREATE OR REPLACE FUNCTION reject_new_subs= criptions() RETURNS trigger AS $$
BEGIN
=C2=A0 =C2=A0 -- Business rul= e: no new subscription rows allowed via INSERT.
=C2=A0 =C2=A0 RETURN NUL= L;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER no_new_subs
=C2= =A0 =C2=A0 BEFORE INSERT ON subscriptions
=C2=A0 =C2=A0 FOR EACH ROW EXE= CUTE FUNCTION reject_new_subscriptions();

-- Pre-existing row (bypas= s trigger to seed it).
ALTER TABLE subscriptions DISABLE TRIGGER no_new_= subs;
INSERT INTO subscriptions VALUES (1, '[1,100)', 'premi= um');
ALTER TABLE subscriptions ENABLE TRIGGER no_new_subs;

S= ELECT * FROM subscriptions;
-- 1 row: (1, [1,100), premium)

-- De= lete just the [40,60) slice.
DELETE FROM subscriptions FOR PORTION OF pe= riod FROM 40 TO 60;

SELECT * FROM subscriptions ORDER BY period;
= -- Should be two rows: [1,40) and [60,100)
-- Actually: 0 rows.=C2=A0 Th= e whole subscription vanished.

SELECT count(*) AS remaining FROM sub= scriptions;
-- Expected 2, got 0.

(3)=C2=A0FPO = UPDATE loses leftovers the same way

DROP TABLE IF = EXISTS room_bookings CASCADE;
CREATE TABLE room_bookings (
=C2=A0 =C2= =A0 booking_id int,
=C2=A0 =C2=A0 slot =C2=A0 =C2=A0 =C2=A0 int4range NO= T NULL,
=C2=A0 =C2=A0 note =C2=A0 =C2=A0 =C2=A0 text
);

CREATE= OR REPLACE FUNCTION block_booking_inserts() RETURNS trigger AS $$
BEGIN=
=C2=A0 =C2=A0 -- Business rule: bookings created only through an API la= yer.
=C2=A0 =C2=A0 RETURN NULL;
END;
$$ LANGUAGE plpgsql;

C= REATE TRIGGER booking_guard
=C2=A0 =C2=A0 BEFORE INSERT ON room_bookings=
=C2=A0 =C2=A0 FOR EACH ROW EXECUTE FUNCTION block_booking_inserts();
ALTER TABLE room_bookings DISABLE TRIGGER booking_guard;
INSERT INT= O room_bookings VALUES (1, '[1,100)', 'team meeting');
A= LTER TABLE room_bookings ENABLE TRIGGER booking_guard;

SELECT * FROM= room_bookings;
-- 1 row: (1, [1,100), team meeting)

-- Shorten t= he meeting to only [40,60).
UPDATE room_bookings FOR PORTION OF slot FRO= M 40 TO 60 SET note =3D 'shortened';

SELECT * FROM room_book= ings ORDER BY slot;
-- Should be three rows:
-- =C2=A0 [1,40) =C2=A0 = team meeting
-- =C2=A0 [40,60) =C2=A0shortened
-- =C2=A0 [60,100) tea= m meeting
-- Actually: only the [40,60) row survives.

SELECT coun= t(*) AS remaining FROM room_bookings;
-- Expected 3, got 1.


[2]:=C2=A0