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

On 2 Nov 2023, at 00:25, PG Bug reporting form <noreply@postgresql.org> wrote:

In investigating the issue I did find what is likely the related release
note for 16 "Remove the ability to create views manually with ON SELECT
rules (Tom Lane)", but couldn't find any related discussion of the
rationale, suggested workarounds, or whether anyone else has yet run into
the same class of issues (possible given the recency of the release).

The discussion which led to this change can be found in this thread:

/messages/by-id/CALDaNm2yXz+zOtv7y5zBd5WKT8O0Ld3YxikuU3dcyCvxF7gypA@mail.gmail.com

While it might not contain any workarounds, it might at least share insights
into the rationale for it.

--
Daniel Gustafsson

On Wed, 2023-11-01 at 23:25 +0000, PG Bug reporting form wrote:

PostgreSQL version: 16.0
Operating system: Windows Server 2022

Apologies if this is not the right place to report this, but it is either a
'bug' in the documentation, or (I'm hoping) a breaking change that could be
reverted.

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.

Where does it say that? That would be a documentation bug.

In investigating the issue I did find what is likely the related release
note for 16 "Remove the ability to create views manually with ON SELECT
rules (Tom Lane)", but couldn't find any related discussion of the
rationale, suggested workarounds, or whether anyone else has yet run into
the same class of issues (possible given the recency of the release).

The relevant commit is here:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b23cd185fd5410e5204683933f848d4583e34b35
and it links to this discussion thread:
/messages/by-id/CALDaNm2yXz+zOtv7y5zBd5WKT8O0Ld3YxikuU3dcyCvxF7gypA@mail.gmail.com

Basically, the behavior was considered a wart, and it caused problems,
so it was decided to get rid of it.

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.

So these "broad classes of database abstraction middleware" are smart
enough to tell that a foreign table is not a table, but too dumb to
recognize a table that has been converted to a view.

What keeps you from creating a view instead of creating a table and
then turning it into a view? Can the "broad classes" distinguish between
these two things? How? It cannot be pg_class.relkind, because
that is changed when you create an ON SELECT rule called _RETURN.
Perhaps pg_class.relfilenode?

If I need to report this somewhere else, or send to one of the mailing
lists, please let me know.

You have been relying on a hack to work around a deficiency in the "broad
classes". I can't speak for PostgreSQL, but I see little chance for this
change to be reverted.

I'd say that the correct thing to do would be to ask the "broad classes"
to enhance their code so that they work properly with foreign tables.

Yours,
Laurenz Albe

PG Bug reporting form <noreply@postgresql.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

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@sss.pgh.pa.us> wrote:

On Fri, 2023-11-03 at 00:44 -1000, Joshua Uyehara wrote:

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.

Yes, that paragraph has to go.

But I wonder if it would not be better to completely remove ON SELECT from the CREATE RULE
command. Attached is a patch that does that.

The documentation part of this patch should be backpatched to v16, but the rest is
HEAD only.

Yours,
Laurenz Albe

Joshua Uyehara <joshua.uyehara@gmail.com> writes:

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:

Hah! That text is so old that it still refers to the pre-7.1 behavior
where a view really was a plain table (in the sense of having some
underlying storage). I pushed an update. Thanks for noting that.

regards, tom lane

Laurenz Albe <laurenz.albe@cybertec.at> writes:

But I wonder if it would not be better to completely remove ON SELECT from the CREATE RULE
command. Attached is a patch that does that.

I don't think this is a great idea. There are perhaps still pg_dump
scripts out there that use CREATE OR REPLACE RULE "_RETURN" to adjust
view definitions.

regards, tom lane