Re: FOR PORTION OF silently ignored on views with DO INSTEAD rules

On Fri, Sep 4, 2026 at 1:45 AM Paul A Jungwirth
<pj(at)illuminatedcomputing(dot)com> wrote:
>
> On Thu, Sep 3, 2026 at 9:46 AM Andres Freund <andres(at)anarazel(dot)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 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.

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(at)illuminatedcomputing(dot)com

--
Regards,
Ewan Young