Re: Proof of concept: auto updatable views

On Sun, Jul 1, 2012 at 6:35 PM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> I've been playing around with the idea of supporting automatically
> updatable views, and I have a working proof of concept. I've taken a
> different approach than the previous attempts to implement this
> feature (e.g., http://archives.postgresql.org/pgsql-hackers/2009-01/msg01746.php),
> instead doing all the work in the rewriter, substituting the view for
> its base relation rather than attempting to auto-generate any rules or
> triggers.
>
> Basically what it does is this: in the first stage of query rewriting,
> just after any non-SELECT rules are applied, the new code kicks in -
> if the target relation is a view, and there were no unqualified
> INSTEAD rules, and there are no INSTEAD OF triggers, it tests if the
> view is simply updatable. If so, the target view is replaced by its
> base relation and columns are re-mapped. Then the remainder of the
> rewriting process continues, recursively handling any further
> non-SELECT rules or additional simply updatable views. This handles
> the case of views on top of views, with or without rules and/or
> triggers.

Regrettably, I don't have time to look at this in detail right now,
but please add it to the next CommitFest so it gets looked at. It
sounds like it could be very cool.

> The consensus last time seemed to be that backwards compatibility
> should be offered through a new GUC variable to allow this feature to
> be disabled globally, which I've not implemented yet.

I think the backward-compatibility concerns with this approach would
be much less than with the previously-proposed approach, so I'm not
100% sure we need a backward compatibility knob. If we're going to
have one, a reloption would probably be a better fit than a GUC, now
that views support reloptions.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company