Re: [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle - Mailing list pgsql-hackers
From | Tom Lane |
---|---|
Subject | Re: [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle |
Date | |
Msg-id | 28419.1232824678@sss.pgh.pa.us Whole thread Raw |
In response to | Re: [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
Re: [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle |
List | pgsql-hackers |
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
pgsql-hackers by date: