Re: Rules: A Modest Proposal - Mailing list pgsql-hackers

From Greg Stark
Subject Re: Rules: A Modest Proposal
Date
Msg-id 407d949e0910052034n7b7b8f60hac23f52ddc0728be@mail.gmail.com
Whole thread Raw
In response to Re: Rules: A Modest Proposal  (Josh Berkus <josh@agliodbs.com>)
List pgsql-hackers
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


pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: moving system catalogs to another tablespace
Next
From: KaiGai Kohei
Date:
Subject: Re: [PATCH] DefaultACLs