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

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:
https://www.postgresql.org/message-id/flat/CALDaNm2yXz%2BzOtv7y5zBd5WKT8O0Ld3YxikuU3dcyCvxF7gypA%40mail.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