Thread: Rules for updatable views (was Re: [PATCHES] Revised Patch to allow multiple table locks in "Unison")
Rules for updatable views (was Re: [PATCHES] Revised Patch to allow multiple table locks in "Unison")
From
Tom Lane
Date:
[ it's past time to move this thread over to pghackers ] Fernando Nasser <fnasser@cygnus.com> writes: > Tom Lane wrote: >> Fernando Nasser <fnasser@cygnus.com> writes: >>> BTW, it seems we have a SQL non-conformance issue here: views that are >>> only projections+selections of a single base table are SQL-updatable. >> >> Indeed. In Postgres terms I think this means that if a CREATE VIEW >> describes a view that meets the spec's constraints to be "updatable", >> we should automatically create a default set of insert/update/delete >> rules for it. This is (or should be) on the TODO list. > Agreed. > We should also emit an error if someone tries to update a > non-updatable view (i.e., it is a view and there is no user defined > rules for that update operation). Silently ignoring the update scares > me and I bet it is not what the standard would tell us to do. Any > suggestion on how can we do this? It's already there as of 7.1: regression=# create view v as select * from a; CREATE regression=# insert into v default values; ERROR: Cannot insert into a view without an appropriate rule regression=# The parts of the behavior that actually need some debate are what the interaction should be between default rules and explicitly created rules --- in particular, how not to break existing pg_dump scripts. Here's a very off-the-cuff suggestion that might or might not survive scrutiny: 1. Add an "is_default" boolean column to pg_rewrite. This will always be FALSE for entries made by explicit CREATE RULE commands, but will be TRUE for entries created automatically when a CREATE VIEW is done for an updatable view. 2. When a CREATE RULE is done, look to see if there is an is_default rule for the same ev_class and ev_type (ie, same target table/view and same action type). If so, delete it. This allows CREATE RULE following CREATE VIEW to override the default rules. A variant is to delete *all* default rules for the target object regardless of action type --- this might be safer, on the theory that if you have a nondefault ON INSERT rule you likely don't want a default ON DELETE. 3. pg_dump would ignore (ie, not dump) is_default rules, knowing that they'd get remade by CREATE VIEW. This prevents default rules from becoming "real" rules after a dump/reload cycle. Comments? regards, tom lane