From: Laurenz Albe <laurenz.albe@cybertec.at>
To: joshua.uyehara@gmail.com
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18178: New Restriction on "ON SELECT" rules on tables
Date: Thu, 02 Nov 2023 11:40:51 +0100
Message-ID: <61d0757cd0a4414e9f733071dbf07a314fc0d5d6.camel@cybertec.at> (raw)
In-Reply-To: <18178-05534d7064044d2d@postgresql.org>
References: <18178-05534d7064044d2d@postgresql.org>
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=b23cd185fd5410e5204683933f848d4583e34...
and it links to this discussion thread:
https://www.postgresql.org/message-id/flat/CALDaNm2yXz%2BzOtv7y5zBd5WKT8O0Ld3YxikuU3dcyCvxF7gypA%40m...
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: laurenz.albe@cybertec.at, joshua.uyehara@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18178: New Restriction on "ON SELECT" rules on tables
In-Reply-To: <61d0757cd0a4414e9f733071dbf07a314fc0d5d6.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox