Re: BUG #18178: New Restriction on "ON SELECT" rules on tables

Thank you (and Laurenz and Daniel) for the collectively comprehensive
response and background information.

Makes sense that supporting workaround hacks is not worthwhile given it's
actually causing a problem. I'm now actually more curious why it even
worked given the explanations and linked discussions.

As far as the documentation error, on closer review, Part V, 41.2 ("Views
and the Rules system") was updated to reflect the change, but the Reference
entry for Create Rule in Part VI was not:

Presently, ON SELECT rules must be unconditional INSTEAD rules and must
> have actions that consist of a single SELECT command. Thus, an ON SELECT rule
> effectively turns the table into a view, whose visible contents are the
> rows returned by the rule's SELECT command rather than whatever had been
> stored in the table (if anything). It is considered better style to write a CREATE
> VIEW command than to create a real table and define an ON SELECT rule for
> it.

Regards,
Josh Uyehara

On Thu, Nov 2, 2023 at 4:20 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > The documentation for rules specifically states that "ON SELECT" rules
> can
> > be created on tables (and I have functionality/use cases that rely on
> that)
> > behavior, but pg16 breaks that.
>
> If the documentation still says that, it needs to be updated.
> What are you looking at precisely?
>
> > The issue is, basically, that there are broad classes of database
> > abstraction middleware that are configured via database introspection at
> > runtime and do not understand or implement the concept of updateable
> views
> > and foreign tables. The easiest workaround for that limitation in
> postgres
> > has always been to create a local table stand-in with all operations
> > rerouted to the foreign table via rules.
>
> TBH, I don't believe that argument for a second. Pre-v16, what
> happened when you added an ON SELECT rule to a table is that *the
> table got changed into a view* (which is what caused the bugs
> mentioned in the thread Daniel pointed you to). There is no
> difference between the subsequent catalog state and what you would
> have if you'd just created it as a view in the first place. So
> do that and then add whatever non-SELECT rules you need, and you
> should be in the same place as before (and your code will still
> work with pre-v16 releases, too).
>
> regards, tom lane
>