Subject: DELETE/UPDATE FOR PORTION OF with rule system is not working

From: jian he <[email-masked]>
Date: Mon, 13 Apr 2026 09:44:38 +0800

Hi.

https://git.postgresql.org/cgit/postgresql.git/commit/?id=8e72d914c52876525a90b28444453de8085c866f
DELETE/UPDATE FOR PORTION OF with rule system is not working, and
there are no RULE related regession tests.

in gram.y, RuleStmt, RuleActionList, RuleActionStmt
RuleActionStmt:
            SelectStmt
            | InsertStmt
            | UpdateStmt
            | DeleteStmt
            | NotifyStmt
        ;

So far, I found 2 errors, and one crash.
There might be more bugs, I didn't try all the cases.

drop table if exists fpo_rule;
create table fpo_rule (f1 bigint, f2 int4range);
INSERT INTO fpo_rule values (1, '[1, 10]');
CREATE RULE rule3 AS ON INSERT TO fpo_rule DO INSTEAD UPDATE fpo_rule
FOR PORTION OF f2 FROM 1 to 4 SET F1 = 2;

INSERT INTO fpo_rule values (2, '[2, 12]');
ERROR:  range types do not match
\errverbose
ERROR:  XX000: range types do not match
LOCATION:  range_minus_multi, rangetypes.c:1260

CREATE RULE rule4 AS ON DELETE TO fpo_rule DO INSTEAD UPDATE fpo_rule
FOR PORTION OF f2 FROM 1 to 4 SET F1 = 2;
DELETE FROM fpo_rule;
ERROR:  no relation entry for relid 3
\errverbose
ERROR:  XX000: no relation entry for relid 3
LOCATION:  find_base_rel, relnode.c:556

DROP RULE rule4 ON fpo_rule;
CREATE RULE rule5 AS ON UPDATE TO fpo_rule DO INSTEAD DELETE FROM
fpo_rule FOR PORTION OF f2 FROM 1 to 4;
UPDATE fpo_rule FOR PORTION OF f2 FROM 1 to 4 SET F1 = 2; -- server crash

As of now, we should try to ban CREATE ROLE with UPDATE/DELETE FOR PORTION OF.

--
jian
https://www.enterprisedb.com/

---

From: Kirill Reshke <[email-masked]>
Date: Mon, 13 Apr 2026 10:00:03 +0500

> there are no RULE related regession tests.

Thanks! +cc Paul & Peter as authors of [0]

> As of now, we should try to ban CREATE ROLE with UPDATE/DELETE FOR PORTION OF.

+1 for banning

[0] https://git.postgresql.org/cgit/postgresql.git/commit/?id=8e72d914c52876525a90b28444453de8085c866f

-- 
Best regards,
Kirill Reshke

---

From: jian he <[email-masked]>
Date: Mon, 13 Apr 2026 17:01:06 +0800

hi.
Actually it's supported.

The issue mentioned in the first email is caused by:
https://git.postgresql.org/cgit/postgresql.git/commit/?id=8e72d914c52876525a90b28444453de8085c866f
https://git.postgresql.org/cgit/postgresql.git/diff/src/backend/nodes/nodeFuncs.c?id=8e72d914c52876525a90b28444453de8085c866f

--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -2579,6 +2579,20 @@ expression_tree_walker_impl(Node *node,
return true;
}
break;
+ case T_ForPortionOfExpr:
+ {
+ ForPortionOfExpr *forPortionOf = (ForPortionOfExpr *) node;
+
+ if (WALK(forPortionOf->targetFrom))
+ return true;
+ if (WALK(forPortionOf->targetTo))
+ return true;
+ if (WALK(forPortionOf->targetRange))
+ return true;
+ if (WALK(forPortionOf->overlapsExpr))
+ return true;
+ }
+ break;

We forgot to WALK (expression_tree_walker_impl)
ForPortionOfExpr->rangeVar and ForPortionOfExpr->rangeTargetList.
We need to WALK those two fields of ForPortionOfExpr in
rewriteRuleAction (ChangeVarNodes,
ReplaceVarsFromTargetList, etc.), and maybe elsewhere.

i am surprised that nothing else has broken because of this.

--
jian
https://www.enterprisedb.com/
From a378f471886400831aed8c7deb4aeaa1a92811b2 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Mon, 13 Apr 2026 16:44:43 +0800
Subject: [PATCH v1 1/1] fix DELETE/UPDATE FOR PORTION OF with rule

discussion: https://postgr.es/m/CACJufxHs1Hs00EqsZ4NbuAjmYzMzjJyP1sAj12Ne=[email-masked]
commitfest entry: https://commitfest.postgresql.org/patch/
---
 src/backend/nodes/nodeFuncs.c                |  4 ++
 src/test/regress/expected/for_portion_of.out | 40 ++++++++++++++++++++
 src/test/regress/sql/for_portion_of.sql      | 22 +++++++++++
 3 files changed, 66 insertions(+)

diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index c0b880ec233..7edbd5b7225 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -2583,6 +2583,8 @@ expression_tree_walker_impl(Node *node,
 			{
 				ForPortionOfExpr *forPortionOf = (ForPortionOfExpr *) node;
 
+				if (WALK(forPortionOf->rangeVar))
+					return true;
 				if (WALK(forPortionOf->targetFrom))
 					return true;
 				if (WALK(forPortionOf->targetTo))
@@ -2591,6 +2593,8 @@ expression_tree_walker_impl(Node *node,
 					return true;
 				if (WALK(forPortionOf->overlapsExpr))
 					return true;
+				if (WALK(forPortionOf->rangeTargetList))
+					return true;
 			}
 			break;
 		case T_PartitionPruneStepOp:
diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out
index 31f772c723d..08e0d99a9c3 100644
--- a/src/test/regress/expected/for_portion_of.out
+++ b/src/test/regress/expected/for_portion_of.out
@@ -2097,4 +2097,44 @@ SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;
 (4 rows)
 
 DROP TABLE temporal_partitioned;
+-- UPDATE/DELETE FOR PORTION OF with RULEs
+CREATE TABLE fpo_rule (f1 bigint, f2 int4range);
+INSERT INTO fpo_rule VALUES (1, '[1, 10]');
+CREATE RULE fpo_rule1 AS ON INSERT TO fpo_rule DO INSTEAD UPDATE fpo_rule FOR PORTION OF f2 FROM 1 TO 4 SET f1 = 2;
+INSERT INTO fpo_rule VALUES (1, '[1, 10]');
+SELECT * FROM fpo_rule ORDER BY f1;
+ f1 |   f2   
+----+--------
+  1 | [4,11)
+  2 | [1,4)
+(2 rows)
+
+CREATE RULE fpo_rule2 AS ON INSERT TO fpo_rule DO INSTEAD DELETE FROM fpo_rule FOR PORTION OF f2 FROM 1 TO 4;
+INSERT INTO fpo_rule VALUES (1, '[1, 10]');
+SELECT * FROM fpo_rule ORDER BY f1;
+ f1 |   f2   
+----+--------
+  1 | [4,11)
+(1 row)
+
+CREATE RULE fpo_rule3 AS ON DELETE TO fpo_rule DO INSTEAD UPDATE fpo_rule FOR PORTION OF f2 FROM 1 TO 8 SET f1 = 2;
+DELETE FROM fpo_rule FOR PORTION OF f2 FROM 1 TO 5;
+SELECT * FROM fpo_rule ORDER BY f1;
+ f1 |   f2   
+----+--------
+  1 | [8,11)
+  2 | [4,8)
+(2 rows)
+
+DROP RULE fpo_rule3 ON fpo_rule;
+CREATE RULE fpo_rule4 AS ON UPDATE TO fpo_rule DO INSTEAD DELETE FROM fpo_rule FOR PORTION OF f2 FROM 6 TO 9;
+UPDATE fpo_rule FOR PORTION OF f2 FROM 4 TO 9 SET f1 = 12;
+SELECT * FROM fpo_rule ORDER BY f1;
+ f1 |   f2   
+----+--------
+  1 | [9,11)
+  2 | [4,6)
+(2 rows)
+
+DROP TABLE fpo_rule;
 RESET datestyle;
diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql
index d4062acf1d1..53bfa705e39 100644
--- a/src/test/regress/sql/for_portion_of.sql
+++ b/src/test/regress/sql/for_portion_of.sql
@@ -1365,4 +1365,26 @@ SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;
 
 DROP TABLE temporal_partitioned;
 
+-- UPDATE/DELETE FOR PORTION OF with RULEs
+CREATE TABLE fpo_rule (f1 bigint, f2 int4range);
+INSERT INTO fpo_rule VALUES (1, '[1, 10]');
+
+CREATE RULE fpo_rule1 AS ON INSERT TO fpo_rule DO INSTEAD UPDATE fpo_rule FOR PORTION OF f2 FROM 1 TO 4 SET f1 = 2;
+INSERT INTO fpo_rule VALUES (1, '[1, 10]');
+SELECT * FROM fpo_rule ORDER BY f1;
+
+CREATE RULE fpo_rule2 AS ON INSERT TO fpo_rule DO INSTEAD DELETE FROM fpo_rule FOR PORTION OF f2 FROM 1 TO 4;
+INSERT INTO fpo_rule VALUES (1, '[1, 10]');
+SELECT * FROM fpo_rule ORDER BY f1;
+
+CREATE RULE fpo_rule3 AS ON DELETE TO fpo_rule DO INSTEAD UPDATE fpo_rule FOR PORTION OF f2 FROM 1 TO 8 SET f1 = 2;
+DELETE FROM fpo_rule FOR PORTION OF f2 FROM 1 TO 5;
+SELECT * FROM fpo_rule ORDER BY f1;
+
+DROP RULE fpo_rule3 ON fpo_rule;
+CREATE RULE fpo_rule4 AS ON UPDATE TO fpo_rule DO INSTEAD DELETE FROM fpo_rule FOR PORTION OF f2 FROM 6 TO 9;
+UPDATE fpo_rule FOR PORTION OF f2 FROM 4 TO 9 SET f1 = 12;
+SELECT * FROM fpo_rule ORDER BY f1;
+DROP TABLE fpo_rule;
+
 RESET datestyle;
-- 
2.34.1

---

From: Kirill Reshke <[email-masked]>
Date: Mon, 13 Apr 2026 20:10:17 +0500

> i am surprised that nothing else has broken because of this.

This fix looks valid for me.

Also, are all 4 new test cases really needed? If yes, why are we
missing  ON DELETE TO ... DO INSTEAD INSERT ?

-- 
Best regards,
Kirill Reshke

---

From: Kirill Reshke <[email-masked]>
Date: Mon, 13 Apr 2026 20:13:48 +0500

On Mon, 13 Apr 2026 at 20:10, Kirill Reshke <[email-masked]> wrote:
> If yes, why are we

Oh sorry... i mean  ON UPDATE TO DO INSTEAD UPDATE FROM FOR PORTION OF

-- 
Best regards,
Kirill Reshke

---

From: Paul A Jungwirth <[email-masked]>
Date: Wed, 15 Apr 2026 15:40:41 -0700

> Actually it's supported.

I also don't see any reason not to support this.

> i am surprised that nothing else has broken because of this.

Thank you for investigating and sending a fix.

The patch looks fine to me. I like the diversity of tests; I don't
think we need any more.

+-- UPDATE/DELETE FOR PORTION OF with RULEs
+CREATE TABLE fpo_rule (f1 bigint, f2 int4range);
+INSERT INTO fpo_rule VALUES (1, '[1, 10]');
+
+CREATE RULE fpo_rule1 AS ON INSERT TO fpo_rule DO INSTEAD UPDATE
fpo_rule FOR PORTION OF f2 FROM 1 TO 4 SET f1 = 2;
+INSERT INTO fpo_rule VALUES (1, '[1, 10]');
+SELECT * FROM fpo_rule ORDER BY f1;

I only have two small suggestions:

Please use '[1, 11)' syntax to match the other tests.

Breaking these long lines would be nice. For example:

+CREATE RULE fpo_rule1 AS ON INSERT TO fpo_rule
+  DO INSTEAD UPDATE fpo_rule FOR PORTION OF f2 FROM 1 TO 4 SET f1 = 2;

Yours,

-- 
Paul              ~{:-)
[email-masked]

---

From: jian he <[email-masked]>
Date: Fri, 17 Apr 2026 11:20:08 +0800

> +  DO INSTEAD UPDATE fpo_rule FOR PORTION OF f2 FROM 1 TO 4 SET f1 = 2;
Please check the attached v2.

V1 only has DO INSTEAD rules, adding one DO ALSO rule would make the
test coverage more robust.

--
jian
https://www.enterprisedb.com/
From 2b9b04c9668c9a617e9bceebe2956133c2323ffe Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Fri, 17 Apr 2026 11:13:00 +0800
Subject: [PATCH v2 1/1] fix DELETE/UPDATE FOR PORTION OF with rules

Author: jian he <[email-masked]>
Reviewed-by: Kirill Reshke <[email-masked]>
Reviewed-by: Paul A Jungwirth <[email-masked]>

Discussion: https://postgr.es/m/CACJufxHs1Hs00EqsZ4NbuAjmYzMzjJyP1sAj12Ne=[email-masked]
---
 src/backend/nodes/nodeFuncs.c                |  4 ++
 src/test/regress/expected/for_portion_of.out | 55 ++++++++++++++++++++
 src/test/regress/sql/for_portion_of.sql      | 33 ++++++++++++
 3 files changed, 92 insertions(+)

index 31f772c723d..0c0a205c44b 100644
@@ -2097,4 +2097,59 @@ SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;
 (4 rows)
 
 DROP TABLE temporal_partitioned;
+-- UPDATE/DELETE FOR PORTION OF with RULEs
+CREATE TABLE fpo_rule (f1 bigint, f2 int4range);
+INSERT INTO fpo_rule VALUES (1, '[1, 11)');
+CREATE RULE fpo_rule1 AS ON INSERT TO fpo_rule
+  DO INSTEAD UPDATE fpo_rule FOR PORTION OF f2 FROM 1 TO 4 SET f1 = 2;
+INSERT INTO fpo_rule VALUES (1, '[1, 11)');
+SELECT * FROM fpo_rule ORDER BY f1;
+ f1 |   f2   
+----+--------
+  1 | [4,11)
+  2 | [1,4)
+(2 rows)
+
+CREATE RULE fpo_rule2 AS ON INSERT TO fpo_rule
+  DO INSTEAD DELETE FROM fpo_rule FOR PORTION OF f2 FROM 1 TO 4;
+INSERT INTO fpo_rule VALUES (1, '[1, 11)');
+SELECT * FROM fpo_rule ORDER BY f1;
+ f1 |   f2   
+----+--------
+  1 | [4,11)
+(1 row)
+
+CREATE RULE fpo_rule3 AS ON DELETE TO fpo_rule
+  DO INSTEAD UPDATE fpo_rule FOR PORTION OF f2 FROM 1 TO 8 SET f1 = 2;
+DELETE FROM fpo_rule FOR PORTION OF f2 FROM 1 TO 5;
+SELECT * FROM fpo_rule ORDER BY f1;
+ f1 |   f2   
+----+--------
+  1 | [8,11)
+  2 | [4,8)
+(2 rows)
+
+DROP RULE fpo_rule3 ON fpo_rule;
+CREATE RULE fpo_rule4 AS ON UPDATE TO fpo_rule
+  DO INSTEAD DELETE FROM fpo_rule FOR PORTION OF f2 FROM 6 TO 9;
+UPDATE fpo_rule FOR PORTION OF f2 FROM 4 TO 9 SET f1 = 12;
+SELECT * FROM fpo_rule ORDER BY f1;
+ f1 |   f2   
+----+--------
+  1 | [9,11)
+  2 | [4,6)
+(2 rows)
+
+DROP RULE fpo_rule4 ON fpo_rule;
+CREATE RULE fpo_rule5 AS ON UPDATE TO fpo_rule
+  DO ALSO DELETE FROM fpo_rule FOR PORTION OF f2 FROM 4 TO 6;
+UPDATE fpo_rule FOR PORTION OF f2 FROM 9 TO 10 SET f1 = 3;
+SELECT * FROM fpo_rule ORDER BY f1;
+ f1 |   f2    
+----+---------
+  1 | [10,11)
+  3 | [9,10)
+(2 rows)
+
+DROP TABLE fpo_rule;
 RESET datestyle;
diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql
index d4062acf1d1..fd79a9b78e7 100644
--- a/src/test/regress/sql/for_portion_of.sql
+++ b/src/test/regress/sql/for_portion_of.sql
@@ -1365,4 +1365,37 @@ SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;

+-- UPDATE/DELETE FOR PORTION OF with RULEs
+CREATE TABLE fpo_rule (f1 bigint, f2 int4range);
+INSERT INTO fpo_rule VALUES (1, '[1, 11)');
+
+CREATE RULE fpo_rule1 AS ON INSERT TO fpo_rule
+  DO INSTEAD UPDATE fpo_rule FOR PORTION OF f2 FROM 1 TO 4 SET f1 = 2;
+INSERT INTO fpo_rule VALUES (1, '[1, 11)');
+SELECT * FROM fpo_rule ORDER BY f1;
+
+CREATE RULE fpo_rule2 AS ON INSERT TO fpo_rule
+  DO INSTEAD DELETE FROM fpo_rule FOR PORTION OF f2 FROM 1 TO 4;
+INSERT INTO fpo_rule VALUES (1, '[1, 11)');
+SELECT * FROM fpo_rule ORDER BY f1;
+
+CREATE RULE fpo_rule3 AS ON DELETE TO fpo_rule
+  DO INSTEAD UPDATE fpo_rule FOR PORTION OF f2 FROM 1 TO 8 SET f1 = 2;
+DELETE FROM fpo_rule FOR PORTION OF f2 FROM 1 TO 5;
+SELECT * FROM fpo_rule ORDER BY f1;
+
+DROP RULE fpo_rule3 ON fpo_rule;
+CREATE RULE fpo_rule4 AS ON UPDATE TO fpo_rule
+  DO INSTEAD DELETE FROM fpo_rule FOR PORTION OF f2 FROM 6 TO 9;
+UPDATE fpo_rule FOR PORTION OF f2 FROM 4 TO 9 SET f1 = 12;
+SELECT * FROM fpo_rule ORDER BY f1;
+
+DROP RULE fpo_rule4 ON fpo_rule;
+CREATE RULE fpo_rule5 AS ON UPDATE TO fpo_rule
+  DO ALSO DELETE FROM fpo_rule FOR PORTION OF f2 FROM 4 TO 6;
+UPDATE fpo_rule FOR PORTION OF f2 FROM 9 TO 10 SET f1 = 3;
+SELECT * FROM fpo_rule ORDER BY f1;
+
+DROP TABLE fpo_rule;
+
 RESET datestyle;
-- 
2.34.1

---

From: Paul A Jungwirth <[email-masked]>
Date: Sun, 19 Apr 2026 11:07:47 -0700

> test coverage more robust.

Thanks for those changes. This looks great to me!

Yours,

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 27 Apr 2026 10:46:39 +0200

> Thanks for those changes. This looks great to me!

committed