Re: [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle

I wrote:
> ... It seems to me that the rule engine
> has probably got all the infrastructure needed to convert the query the
> way we'd like, we just don't have a suitable API to tell it to do that.

I have in mind a couple of quite different approaches to this, and wanted
to solicit some feedback about which direction to pursue.

The idea I'd originally had was something along the lines of

ON UPDATE DO INSTEAD SUBSTITUTE base_table [ (base_column_name, ...) ]

where the intended transformation is that you take the update command
on the view as-written, substitute base_table for the view name and
appropriate base_column_names for each view column name, and presto
you have your update command for the base table. The list of column names
would be there to let you specify the correspondence between base columns
and view columns. One thing this is lacking is anything corresponding
to the view's WHERE clause to ensure that the update is restricted to
rows that are visible through the view. We could just have the rewriter
copy over the view's WHERE clause, or we could insist that the clause
be repeated in the rule, ie

ON UPDATE DO INSTEAD SUBSTITUTE base_table [ (base_column_name, ...) ]
[ WHERE ... ]

That would be pretty tedious to write or maintain by hand, but in simple
cases the automatic rewriter should do it for you.

(Note: I'm focusing on UPDATE here because that's the hardest case.
DELETE is easier because there's no new column values to compute,
and INSERT is easy because there's no need to worry about matching
to an existing view row.)

Plan B was to not have any of this syntax exposed at all, but just have
the rewriter try to do it automatically when no update rule exists for
a view. I think the main argument in favor of exposing syntax would be if
the syntax allows you to do things above and beyond the cases that we're
willing to take care of automatically. Some examples of that would be
ignoring attempted updates on derived columns of a view, or reversing
invertible functions in the view. (A trivial example of that: if the
view exposes "base_col + 1", you could allow updates that subtract one
from the value the user tries to store.) The above syntax doesn't work
very well for doing such things, though.

I came up with a Plan C, which is to keep mostly the current syntax for
update rules but invent some notation that says "apply the update to the
view's underlying row". There's an obvious candidate for existing syntax
to abuse for this purpose: WHERE CURRENT OF. So we'd write something like

ON UPDATE DO INSTEAD
UPDATE base_table SET base_col_1 = new.derived_col_1, base_col_2 ...
WHERE CURRENT OF VIEW;

and the rewriter would interpret this appropriately. You'd end up with
essentially the same results as with the other syntax, but there is more
flexibility here to omit columns, store results computed from columns,
etc.

This is a bit ugly because of the potential conflict with regular
"WHERE CURRENT OF cursor", but I find it hard to see a use-case for that
in a rule, since cursors are so much shorter-lived than rules. Anyway
you could avoid the conflict by not naming your cursor "view". A bigger
objection is that the semantics would be just a little bit different from
regular WHERE CURRENT OF cursor, because our implementation of that is
effectively a ctid match; and as I explained before, that's not what we
want for an updatable view.

Does anyone find any of these examples particularly attractive or
horrific? Got any better ideas?

regards, tom lane