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

--On 23. Januar 2009 17:32:55 -0500 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

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

Oh, please, don't get me wrong: i never intended to attack you personally.
I can imagine how much of work you are faced with this release. I got the
feeling that it's simply the wrong way chosen, a little bit frustrating,
isn't it?

Apologize for that.

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

Yeah, that's exactly the same feeling i got when reading your last mail.
I'm very uncomfortable now that we know the "real" gotchas with the whole
rule approach. Normally you'll get some ideas when thinking about a
solution, but instead i have to think "omg, is that really doable within
the rewriter in any ways?" getting disappointed.

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

Hmm this would mean that the rewriter bypasses all the rule stuff itself
when faced with a view update and completely replacing the original query?
Looks kinda of it. Oracle has INSTEAD OF triggers which are going to do
nearly the same thing, afaiks.

Bernd