Re: [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle - Mailing list pgsql-hackers

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

> Bernd Helmle <mailings@oopsware.de> writes:
>> --On 23. Januar 2009 13:28:27 -0500 Tom Lane <tgl@sss.pgh.pa.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