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
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