FOR PORTION OF silently ignored on views with DO INSTEAD rules
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253654psql -h localhost -U postgresBuilt from patchset v6 (message #6), September 15, 2026 at 02:52 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253654_6 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253654_6 && git checkout t253654_6Patchset v6 (message #6) is on t253654_6
Hi,
An UPDATE/DELETE ... FOR PORTION OF against a view that has an
unqualified DO INSTEAD rule silently ignores the FOR PORTION OF clause
and modifies (or deletes) the whole temporal row instead of just the
requested portion -- no error, no warning:
CREATE TABLE t (id int, valid_at daterange, name text);
INSERT INTO t VALUES (1, '[2020-01-01,2021-01-01)', 'a');
CREATE VIEW v AS SELECT * FROM t;
CREATE RULE v_upd AS ON UPDATE TO v DO INSTEAD
UPDATE t SET name = NEW.name WHERE id = OLD.id;
UPDATE v FOR PORTION OF valid_at FROM '2020-06-01' TO '2020-07-01'
SET name = 'b';
SELECT * FROM t;
id | valid_at | name
----+-------------------------+------
1 | [2020-01-01,2021-01-01) | b -- whole row changed
The same statement on the base table (or a plain auto-updatable view)
correctly splits the row three ways. DELETE is worse: DELETE ... FOR
PORTION OF through such a view removes the entire row.
The cause is that an unqualified INSTEAD rule replaces the original
query with the rule's action during rewriting, so rewriteTargetView()
-- the path that carries the query's forPortionOf through a view -- is
never reached, and the rule action has forPortionOf = NULL. This is
the exact analog of the INSTEAD OF trigger case that dfce19c2300
("Forbid FOR PORTION OF on views with INSTEAD OF triggers") already
rejects with a feature-not-supported error (5b5e99047ab on
REL_19_STABLE); the DO INSTEAD rule sibling was left unguarded.
The attached patch rejects FOR PORTION OF when an unqualified INSTEAD
rule fires on a view, using the same error as the trigger case. A
DO ALSO rule does not replace the query, so FOR PORTION OF keeps working
on the automatically-updatable path; the tests cover that as well as the
UPDATE and DELETE INSTEAD-rule cases. make check passes. The patch
is against master; the same code (and the same behavior) is present on
REL_19_STABLE.
--
Regards,
Ewan Young
On Wed, Sep 2, 2026 at 9:20 PM Ewan Young <kdbase.hack@gmail.com> wrote:
An UPDATE/DELETE ... FOR PORTION OF against a view that has an
unqualified DO INSTEAD rule silently ignores the FOR PORTION OF clause
and modifies (or deletes) the whole temporal row instead of just the
requested portion -- no error, no warning:CREATE TABLE t (id int, valid_at daterange, name text);
INSERT INTO t VALUES (1, '[2020-01-01,2021-01-01)', 'a');
CREATE VIEW v AS SELECT * FROM t;
CREATE RULE v_upd AS ON UPDATE TO v DO INSTEAD
UPDATE t SET name = NEW.name WHERE id = OLD.id;UPDATE v FOR PORTION OF valid_at FROM '2020-06-01' TO '2020-07-01'
SET name = 'b';
SELECT * FROM t;
id | valid_at | name
----+-------------------------+------
1 | [2020-01-01,2021-01-01) | b -- whole row changedThe same statement on the base table (or a plain auto-updatable view)
correctly splits the row three ways. DELETE is worse: DELETE ... FOR
PORTION OF through such a view removes the entire row.
IMO this is working as intended. If you replace the original query, we
shouldn't skip just part of it and still execute one clause. If you
wanted to keep the FOR PORTION OF, your rule would have said that.
I have the same opinion for INSTEAD OF triggers, but we disabled them
for v19 since the issue came up late in the cycle, and trigger
functions have some extra complexity (especially because of DO ATOMIC
functions). It doesn't seem like DO INSTEAD rules have that same
issue. None of the examples here actually look like bugs.
But now we are even later in the cycle than before. If people want to
disable this for v19 too, I'm okay with that. In that case, the patch
looks good to me.
Yours,
--
Paul ~{:-)
pj@illuminatedcomputing.com
Hi,
On 2026-09-03 09:08:10 -0700, Paul A Jungwirth wrote:
On Wed, Sep 2, 2026 at 9:20 PM Ewan Young <kdbase.hack@gmail.com> wrote:
An UPDATE/DELETE ... FOR PORTION OF against a view that has an
unqualified DO INSTEAD rule silently ignores the FOR PORTION OF clause
and modifies (or deletes) the whole temporal row instead of just the
requested portion -- no error, no warning:CREATE TABLE t (id int, valid_at daterange, name text);
INSERT INTO t VALUES (1, '[2020-01-01,2021-01-01)', 'a');
CREATE VIEW v AS SELECT * FROM t;
CREATE RULE v_upd AS ON UPDATE TO v DO INSTEAD
UPDATE t SET name = NEW.name WHERE id = OLD.id;UPDATE v FOR PORTION OF valid_at FROM '2020-06-01' TO '2020-07-01'
SET name = 'b';
SELECT * FROM t;
id | valid_at | name
----+-------------------------+------
1 | [2020-01-01,2021-01-01) | b -- whole row changedThe same statement on the base table (or a plain auto-updatable view)
correctly splits the row three ways. DELETE is worse: DELETE ... FOR
PORTION OF through such a view removes the entire row.IMO this is working as intended. If you replace the original query, we
shouldn't skip just part of it and still execute one clause. If you
wanted to keep the FOR PORTION OF, your rule would have said that.
That makes no sense to me. The writer of an instead-of rule can't
control/predict whether the user uses FOR PORTION OF? How could one possibly
write such a rule that works both when FOR PORTION OF is used and when not?
I think rules, except for being an implementation detail of views, are a crazy
feature that should have been removed long ago, but I don't think that really
makes the behaviour here defensible.
Greetings,
Andres Freund
On Thu, Sep 3, 2026 at 9:46 AM Andres Freund <andres@anarazel.de> wrote:
IMO this is working as intended. If you replace the original query, we
shouldn't skip just part of it and still execute one clause. If you
wanted to keep the FOR PORTION OF, your rule would have said that.That makes no sense to me. The writer of an instead-of rule can't
control/predict whether the user uses FOR PORTION OF? How could one possibly
write such a rule that works both when FOR PORTION OF is used and when not?I think rules, except for being an implementation detail of views, are a crazy
feature that should have been removed long ago, but I don't think that really
makes the behaviour here defensible.
In the case of triggers, we could convey the FOR PORTION OF via TG_*
variables.[0]https://commitfest.postgresql.org/patch/7239/ My hope was that we could offer that information to
rules, too. But you're right that today a rule has no way to know
whether the clause appeared or not.
I think trying to apply the FOR PORTION OF clause to the rule's output
is impossible though. The rule could have done anything. We can't
partially-apply a statement a rule asked us to replace.
But I understand why we would be reluctant to allow this today, if
there is no way to write a rule that responds to FOR PORTION OF. So
let's forbid the combination and raise an error.
I was wondering how rules deal with other "add-on" clauses. I tried ON
CONFLICT DO UPDATE. That also gets rejected:
[v19beta3:15432][71204] postgres=# create table t (id integer primary
key, name text);
CREATE TABLE
[v19beta3:15432][71204] postgres=# create rule r as on insert to t do
instead select 1;
CREATE RULE
[v19beta3:15432][71204] postgres=# insert into t (id, name) values (1,
'foo') on conflict (id) do nothing;
ERROR: INSERT with ON CONFLICT clause cannot be used with table that
has INSERT or UPDATE rules
On the other hand a DO INSTEAD NOTHING rule is allowed. Ewan's patch
also allows DO INSTEAD NOTHING. That makes sense to me. Here is a
revised patch adding it to Ewan's tests.
[0]: https://commitfest.postgresql.org/patch/7239/
--
Paul ~{:-)
pj@illuminatedcomputing.com
On Fri, Sep 4, 2026 at 1:45 AM Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:
On Thu, Sep 3, 2026 at 9:46 AM Andres Freund <andres@anarazel.de> wrote:
IMO this is working as intended. If you replace the original query, we
shouldn't skip just part of it and still execute one clause. If you
wanted to keep the FOR PORTION OF, your rule would have said that.That makes no sense to me. The writer of an instead-of rule can't
control/predict whether the user uses FOR PORTION OF? How could one possibly
write such a rule that works both when FOR PORTION OF is used and when not?I think rules, except for being an implementation detail of views, are a crazy
feature that should have been removed long ago, but I don't think that really
makes the behaviour here defensible.In the case of triggers, we could convey the FOR PORTION OF via TG_*
variables.[0] My hope was that we could offer that information to
rules, too. But you're right that today a rule has no way to know
whether the clause appeared or not.I think trying to apply the FOR PORTION OF clause to the rule's output
is impossible though. The rule could have done anything. We can't
partially-apply a statement a rule asked us to replace.But I understand why we would be reluctant to allow this today, if
there is no way to write a rule that responds to FOR PORTION OF. So
let's forbid the combination and raise an error.
Thanks Paul and Andres for looking at this.
I was wondering how rules deal with other "add-on" clauses. I tried ON
CONFLICT DO UPDATE. That also gets rejected:[v19beta3:15432][71204] postgres=# create table t (id integer primary
key, name text);
CREATE TABLE
[v19beta3:15432][71204] postgres=# create rule r as on insert to t do
instead select 1;
CREATE RULE
[v19beta3:15432][71204] postgres=# insert into t (id, name) values (1,
'foo') on conflict (id) do nothing;
ERROR: INSERT with ON CONFLICT clause cannot be used with table that
has INSERT or UPDATE rulesOn the other hand a DO INSTEAD NOTHING rule is allowed. Ewan's patch
also allows DO INSTEAD NOTHING. That makes sense to me. Here is a
revised patch adding it to Ewan's tests.
One small thing: the new test cases use "do nothing", which the grammar
treats as DO ALSO NOTHING (opt_instead defaults to ALSO), so those rules
don't replace the query and FOR PORTION OF keeps working through the
auto-updatable path. An actual unqualified "do instead nothing" rule
sets the instead flag in fireRules(), so the patch rejects it with the
same error, for both UPDATE and DELETE. I think that is fine and
consistent with the ON CONFLICT precedent you mention, but the test
comment and the last paragraph of the commit message say the opposite,
so they should be adjusted one way or the other. (Also, the second
"do nothing" rule says "on update" where "on delete" was intended.)
Other than that v2 looks good to me.
[0] https://commitfest.postgresql.org/patch/7239/
--
Paul ~{:-)
pj@illuminatedcomputing.com
--
Regards,
Ewan Young
On Thu, Sep 3, 2026 at 7:38 PM Ewan Young <kdbase.hack@gmail.com> wrote:
On the other hand a DO INSTEAD NOTHING rule is allowed. Ewan's patch
also allows DO INSTEAD NOTHING. That makes sense to me. Here is a
revised patch adding it to Ewan's tests.One small thing: the new test cases use "do nothing", which the grammar
treats as DO ALSO NOTHING (opt_instead defaults to ALSO), so those rules
don't replace the query and FOR PORTION OF keeps working through the
auto-updatable path. An actual unqualified "do instead nothing" rule
sets the instead flag in fireRules(), so the patch rejects it with the
same error, for both UPDATE and DELETE. I think that is fine and
consistent with the ON CONFLICT precedent you mention, but the test
comment and the last paragraph of the commit message say the opposite,
so they should be adjusted one way or the other. (Also, the second
"do nothing" rule says "on update" where "on delete" was intended.)
Oh, good catch! I've updated the test. Even with "DO INSTEAD NOTHING",
ON CONFLICT does allow the rule. I think this is important, since DO
INSTEAD NOTHING has a somewhat special role as a fallback when there
are other conditional rules.[0]https://www.postgresql.org/docs/current/sql-createrule.html So I'd like to make FOR PORTION OF
work the same way. Making the fixed test pass required a very small
code change. Here is a v3 with those edits.
[0]: https://www.postgresql.org/docs/current/sql-createrule.html
Yours,
--
Paul ~{:-)
pj@illuminatedcomputing.com
On Fri, Sep 4, 2026 at 11:55 AM Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:
On Thu, Sep 3, 2026 at 7:38 PM Ewan Young <kdbase.hack@gmail.com> wrote:
On the other hand a DO INSTEAD NOTHING rule is allowed. Ewan's patch
also allows DO INSTEAD NOTHING. That makes sense to me. Here is a
revised patch adding it to Ewan's tests.One small thing: the new test cases use "do nothing", which the grammar
treats as DO ALSO NOTHING (opt_instead defaults to ALSO), so those rules
don't replace the query and FOR PORTION OF keeps working through the
auto-updatable path. An actual unqualified "do instead nothing" rule
sets the instead flag in fireRules(), so the patch rejects it with the
same error, for both UPDATE and DELETE. I think that is fine and
consistent with the ON CONFLICT precedent you mention, but the test
comment and the last paragraph of the commit message say the opposite,
so they should be adjusted one way or the other. (Also, the second
"do nothing" rule says "on update" where "on delete" was intended.)Oh, good catch! I've updated the test. Even with "DO INSTEAD NOTHING",
ON CONFLICT does allow the rule. I think this is important, since DO
INSTEAD NOTHING has a somewhat special role as a fallback when there
are other conditional rules.[0] So I'd like to make FOR PORTION OF
work the same way. Making the fixed test pass required a very small
code change. Here is a v3 with those edits.
Thanks. Agreed, and checking product_queries != NIL matches the ON
CONFLICT check in the same function, so DO INSTEAD NOTHING stays a
no-op while any INSTEAD rule with a real action is rejected.
v3 looks good to me.
[0] https://www.postgresql.org/docs/current/sql-createrule.html
Yours,
--
Paul ~{:-)
pj@illuminatedcomputing.com
--
Regards,
Ewan Young