Rules for updatable views (was Re: [PATCHES] Revised Patch to allow multiple table locks in "Unison")

[ it's past time to move this thread over to pghackers ]

Fernando Nasser <fnasser(at)cygnus(dot)com> writes:
> Tom Lane wrote:
>> Fernando Nasser <fnasser(at)cygnus(dot)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