On Mon, Oct 5, 2009 at 10:17 AM, Josh Berkus <j...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers