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

Bernd Helmle <mailings(at)oopsware(dot)de> writes:
> --On 23. Januar 2009 13:28:27 -0500 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> In short, I don't feel that this was ready to be applied.

> Uh well, i'd be happier if such review comments would have been made
> earlier in the CommitFest.

[ shrug... ] I've been busting my butt since 1 November to try to
review everything. Some things are going to get left to the end.
I have to admit having ranked this one lower because it was marked
WIP for a good part of the commitfest, and so I'd assumed it was not
really a serious candidate to get applied.

Anyway, it's here now, and what we have to figure out is whether it's
fixable on a time scale that's realistic for 8.4. I would really rather
sidestep the whole btree-equality issue if possible, but that doesn't
seem possible without some amount of changes to the rule mechanism
itself. The idea I was toying with when I posted earlier is that the
rules should look more like

on update to view do instead
update base_table set c1 = new.c1, etc
where base_table.ctid = old.ctid

but of course that doesn't work as-is because views don't expose
old.ctid, and even if they did (which doesn't seem impossible) we'd need
some planner fixes in order to get a non-silly plan out of it, because
joins on ctid aren't implemented very well today.

Another gotcha is that read-committed updates wouldn't work properly.
If the row first identified by the view has been outdated by someone
else's update, we're supposed to try to apply the update to the newest
version of the row, if it still passes the update's WHERE clause.
This would fail a priori with the ctid-based approach since the new row
version is guaranteed not to have the same ctid. Even in the current
equate-all-the-visible-fields approach it doesn't work if the someone
else updated any of the visible fields: the row would now fail one of
the added where conditions, which have got nothing to do with anything
that the user wrote, so it's not expected behavior.

I'm inclined to think that this is all pretty much insoluble within the
current rule mechanism. The existing definition of rules makes it
basically impossible to do INSTEAD UPDATE or INSTEAD DELETE without
creating a self-join; if we don't get around that somehow we're never
going to be very satisfied with either the performance or the
corner-case semantics of this thing. What we get now from a rewritten
view update is something that looks like

UPDATE base_table new SET ... FROM base_table old
WHERE view's-conditions-on-old AND user's-conditions-on-old
AND exposed-fields-of-new-and-old-are-equal

and just replacing the last part of that with a ctid equality is only
nibbling at the margins of its suckiness. What we really want is that
the rewritten query is just

UPDATE base_table SET ...
WHERE view's-conditions AND user's-conditions

with no join at all.

Perhaps the right answer is to invent some new rule syntax to "redirect"
inserts/updates/deletes, say something like

on update to foo do instead redirect to bar

and then put some logic that's not so much different from what you've
got here into the rule engine itself ... or maybe better, just have the
rule engine automatically try to redirect if it's faced with having to
raise error for lack of a rule? 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.

regards, tom lane