On Mon, Oct 5, 2009 at 10:17 AM, Josh Berkus <josh@agliodbs.com> wrote:
> So while rules are hard to use and easy to mess up, so are triggers. So
> while an (arguable) problem is being pointed out, no real solution is
> being proposed.
If you want to implement updatable views I still stand by my (much)
earlier design suggestion. They should be implemented just like SELECT
on views is currently. The rule is a simple substitution and doesn't
try to analyze and decompose the query and figure out how to rewrite
it into a complete different query. Most of the work is done, not in
the rule, but in the regular SQL parser and statement analyzer where
it has a lot more information available to it.
So for example this view
CREATE VIEW foo AS SELECT a AS aa, b+1 AS bb FROM tab
expands this sql:
SELECT bb FROM foo
into this:
SELECT bb FROM (SELECT a AS aa, b+1 AS bb FROM tab) AS foo
and it should expand this sql:
UPDATE foo SET a=1 WHERE bb=1
into this:
UPDATE (SELECT a AS aa, b+1 AS bb FROM tab) AS foo SET a=1 WHERE bb=1
This means extending our regular UPDATE syntax to allow arbitrary
inline views in place of the update target. That's harder than the
hacks we've been playing with so far to try to reverse engineer the
right way to write the update statement for a given view but it would
be much much more robust. The statement analyzer handling the update
statement has a much better idea of what columns it needs to write to,
which tables they depend on, and so on.
The problems people run into with rules always come from trying to put
too much cleverness into the rule. When you put conditions on the rule
based on your partition key or put intelligence in the rule to handle
your updatable view logic it embeds dependencies on subtle assumptions
about the eventual query which will come along. We've never run into
any problems with regular rules used for regular views because all
they do is substitute the view in the right place in the query. The
select machinery takes care of figuring out how it relates to the rest
of the query. As long as the updatable views do the same thing then
rules will be exactly the right tool for the job.
--
greg
