public inbox for [email protected]
help / color / mirror / Atom feedDELETE/UPDATE FOR PORTION OF with rule system is not working
9+ messages / 4 participants
[nested] [flat]
* DELETE/UPDATE FOR PORTION OF with rule system is not working
@ 2026-04-13 01:44 jian he <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: jian he @ 2026-04-13 01:44 UTC (permalink / raw)
To: pgsql-hackers
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: DELETE/UPDATE FOR PORTION OF with rule system is not working
@ 2026-04-13 05:00 Kirill Reshke <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Kirill Reshke @ 2026-04-13 05:00 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: pgsql-hackers; Paul A Jungwirth <[email protected]>; Peter Eisentraut <[email protected]>
On Mon, 13 Apr 2026 at 06:45, jian he <[email protected]> wrote:
>
> 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.
Thanks! +cc Paul & Peter as authors of [0]
> 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.
+1 for banning
[0] https://git.postgresql.org/cgit/postgresql.git/commit/?id=8e72d914c52876525a90b28444453de8085c866f
--
Best regards,
Kirill Reshke
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: DELETE/UPDATE FOR PORTION OF with rule system is not working
@ 2026-04-13 09:01 jian he <[email protected]>
parent: Kirill Reshke <[email protected]>
0 siblings, 2 replies; 9+ messages in thread
From: jian he @ 2026-04-13 09:01 UTC (permalink / raw)
To: Kirill Reshke <[email protected]>; +Cc: pgsql-hackers; Paul A Jungwirth <[email protected]>; Peter Eisentraut <[email protected]>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: DELETE/UPDATE FOR PORTION OF with rule system is not working
@ 2026-04-13 15:10 Kirill Reshke <[email protected]>
parent: jian he <[email protected]>
1 sibling, 1 reply; 9+ messages in thread
From: Kirill Reshke @ 2026-04-13 15:10 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: pgsql-hackers; Paul A Jungwirth <[email protected]>; Peter Eisentraut <[email protected]>
On Mon, 13 Apr 2026 at 14:01, jian he <[email protected]> wrote:
>
> 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/
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: DELETE/UPDATE FOR PORTION OF with rule system is not working
@ 2026-04-13 15:13 Kirill Reshke <[email protected]>
parent: Kirill Reshke <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Kirill Reshke @ 2026-04-13 15:13 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: pgsql-hackers; Paul A Jungwirth <[email protected]>; Peter Eisentraut <[email protected]>
On Mon, 13 Apr 2026 at 20:10, Kirill Reshke <[email protected]> wrote:
> If yes, why are we
> missing ON DELETE TO ... DO INSTEAD INSERT ?
Oh sorry... i mean ON UPDATE TO DO INSTEAD UPDATE FROM FOR PORTION OF
--
Best regards,
Kirill Reshke
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: DELETE/UPDATE FOR PORTION OF with rule system is not working
@ 2026-04-15 22:40 Paul A Jungwirth <[email protected]>
parent: jian he <[email protected]>
1 sibling, 1 reply; 9+ messages in thread
From: Paul A Jungwirth @ 2026-04-15 22:40 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: Kirill Reshke <[email protected]>; pgsql-hackers; Peter Eisentraut <[email protected]>
On Mon, Apr 13, 2026 at 2:01 AM jian he <[email protected]> wrote:
>
> hi.
> Actually it's supported.
I also don't see any reason not to support this.
> 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.
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 protected]
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: DELETE/UPDATE FOR PORTION OF with rule system is not working
@ 2026-04-17 03:20 jian he <[email protected]>
parent: Paul A Jungwirth <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: jian he @ 2026-04-17 03:20 UTC (permalink / raw)
To: Paul A Jungwirth <[email protected]>; +Cc: Kirill Reshke <[email protected]>; pgsql-hackers; Peter Eisentraut <[email protected]>
On Thu, Apr 16, 2026 at 6:40 AM Paul A Jungwirth
<[email protected]> wrote:
>
> 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;
>
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/
Attachments:
[text/x-patch] v2-0001-fix-DELETE-UPDATE-FOR-PORTION-OF-with-rules.patch (5.0K, 2-v2-0001-fix-DELETE-UPDATE-FOR-PORTION-OF-with-rules.patch)
download | inline diff:
From 2b9b04c9668c9a617e9bceebe2956133c2323ffe Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
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 protected]>
Reviewed-by: Kirill Reshke <[email protected]>
Reviewed-by: Paul A Jungwirth <[email protected]>
Discussion: https://postgr.es/m/CACJufxHs1Hs00EqsZ4NbuAjmYzMzjJyP1sAj12Ne=cBsEVmQOA@mail.gmail.com
---
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(+)
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..0c0a205c44b 100644
--- a/src/test/regress/expected/for_portion_of.out
+++ b/src/test/regress/expected/for_portion_of.out
@@ -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;
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;
+
+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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: DELETE/UPDATE FOR PORTION OF with rule system is not working
@ 2026-04-19 18:07 Paul A Jungwirth <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Paul A Jungwirth @ 2026-04-19 18:07 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: Kirill Reshke <[email protected]>; pgsql-hackers; Peter Eisentraut <[email protected]>
On Thu, Apr 16, 2026 at 8:20 PM jian he <[email protected]> wrote:
>
> On Thu, Apr 16, 2026 at 6:40 AM Paul A Jungwirth
> <[email protected]> wrote:
> >
> > 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;
> >
> Please check the attached v2.
>
> V1 only has DO INSTEAD rules, adding one DO ALSO rule would make the
> test coverage more robust.
Thanks for those changes. This looks great to me!
Yours,
--
Paul ~{:-)
[email protected]
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: DELETE/UPDATE FOR PORTION OF with rule system is not working
@ 2026-04-27 08:46 Peter Eisentraut <[email protected]>
parent: Paul A Jungwirth <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Peter Eisentraut @ 2026-04-27 08:46 UTC (permalink / raw)
To: Paul A Jungwirth <[email protected]>; jian he <[email protected]>; +Cc: Kirill Reshke <[email protected]>; pgsql-hackers
On 19.04.26 20:07, Paul A Jungwirth wrote:
> On Thu, Apr 16, 2026 at 8:20 PM jian he <[email protected]> wrote:
>>
>> On Thu, Apr 16, 2026 at 6:40 AM Paul A Jungwirth
>> <[email protected]> wrote:
>>>
>>> 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;
>>>
>> Please check the attached v2.
>>
>> V1 only has DO INSTEAD rules, adding one DO ALSO rule would make the
>> test coverage more robust.
>
> Thanks for those changes. This looks great to me!
committed
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2026-04-27 08:46 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-13 01:44 DELETE/UPDATE FOR PORTION OF with rule system is not working jian he <[email protected]>
2026-04-13 05:00 ` Kirill Reshke <[email protected]>
2026-04-13 09:01 ` jian he <[email protected]>
2026-04-13 15:10 ` Kirill Reshke <[email protected]>
2026-04-13 15:13 ` Kirill Reshke <[email protected]>
2026-04-15 22:40 ` Paul A Jungwirth <[email protected]>
2026-04-17 03:20 ` jian he <[email protected]>
2026-04-19 18:07 ` Paul A Jungwirth <[email protected]>
2026-04-27 08:46 ` Peter Eisentraut <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox