public inbox for [email protected]
help / color / mirror / Atom feedFrom: jian he <[email protected]>
To: Kirill Reshke <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Cc: Paul A Jungwirth <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Subject: Re: DELETE/UPDATE FOR PORTION OF with rule system is not working
Date: Mon, 13 Apr 2026 17:01:06 +0800
Message-ID: <CACJufxGjgdWnhq5X5VL9orLXyKx-ZayT_Zj_-bAKr=kf0pZ_Nw@mail.gmail.com> (raw)
In-Reply-To: <CALdSSPiRHCyC0fYy_E=gO+c7sbwU=7=Fz2CF-4aEa3mAe7yYAg@mail.gmail.com>
References: <CACJufxHs1Hs00EqsZ4NbuAjmYzMzjJyP1sAj12Ne=cBsEVmQOA@mail.gmail.com>
<CALdSSPiRHCyC0fYy_E=gO+c7sbwU=7=Fz2CF-4aEa3mAe7yYAg@mail.gmail.com>
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=8e72d914c528765...
--- 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/
Attachments:
[text/x-patch] v1-0001-fix-DELETE-UPDATE-FOR-PORTION-OF-with-rule.patch (4.3K, 2-v1-0001-fix-DELETE-UPDATE-FOR-PORTION-OF-with-rule.patch)
download | inline diff:
From a378f471886400831aed8c7deb4aeaa1a92811b2 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
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=cBsEVmQOA@mail.gmail.com
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
view thread (9+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: DELETE/UPDATE FOR PORTION OF with rule system is not working
In-Reply-To: <CACJufxGjgdWnhq5X5VL9orLXyKx-ZayT_Zj_-bAKr=kf0pZ_Nw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox