Open thoughts about updateable views - Mailing list pgsql-hackers

As somebody already noticed i'm working on view update rules for (currently 
SQL92 only) updateable views. There are some issues i would like to hear 
the opinion of experienced pgsql-hackers about (and maybe get some pointers 
to solve them):

Column DEFAULT values aren't automatically "inherited" from the base 
relation. This means somebody has  to do the following, to get view updates 
with related sequences succeed:

=> Issue an ALTER TABLE view ALTER COLUMN col1 SET DEFAULT nextval('...') 
to get an DEFAULT value from a sequence for example. Is it a good idea to 
move this into the view update rule code or into the CREATE VIEW command? 
Is the possibility to ALTER a view bulletproof or only a side-effect with 
the future to be broken someday (because it looks not very intuitive...)? 
Here's a short example, what i mean:

bommel@[local]:yomama #= CREATE OR REPLACE VIEW vabteilung_edv AS SELECT 
bezeichnung AS c2, abteilungsnummer AS c1 from abteilung where bezeichnung 
LIKE 'EDV%' WITH LOCAL CHECK OPTION;
NOTICE:  CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules
CREATE VIEW

bommel@[local]:yomama #= \d abteilung                                      Table "public.abteilung"     Column      |
Type  |                                Modifiers 
 

------------------+---------+-------------------------------------------------------------------------abteilungsnummer
|integer | not null default 
 
nextval('public.abteilung_abteilungsnummer_seq'::text)bezeichnung      | text    | not null
Indexes:   "abteilung_pkey" PRIMARY KEY, btree (abteilungsnummer)

bommel@[local]:yomama #= INSERT INTO vabteilung_edv( c2 ) VALUES ( 'edv 
abteilung' );
ERROR:  view update commands violates rule condition

bommel@[local]:yomama #= INSERT INTO vabteilung_edv( c2 ) VALUES ( 'EDV 
abteilung' );
ERROR:  null value in column "abteilungsnummer" violates not-null constraint

==> the error message is confusing.....

bommel@[local]:yomama #= ALTER TABLE vabteilung_edv ALTER COLUMN c1 SET 
DEFAULT nextval('public.abteilung_abteilungsnummer_seq'::text);
ALTER TABLE

bommel@[local]:yomama #= INSERT INTO vabteilung_edv( c2 ) VALUES ( 'EDV 
abteilung' );
INSERT 107905 1

bommel@[local]:yomama #= select * from vabteilung_edv;     c2       | c1
---------------+----EDV abteilung |  6
(1 row)


Implicit created rules are named _INSERT, _DELETE and _UPDATE and so no 
other rules are allowed to live besides them to get the view update code 
working. However, this breaks pg_dump restore's likely, but there is 
another issue:

=> Views without the CHECK OPTION are intended to be updated in any manner 
you can imagine. So, if a view only displays ID's > 5, you are allowed to 
update ID's <= 5 according to the SQL92 Standard. With rules only, this is 
not possible, since the planner doesn't see the affected tuples through the 
view. What should be done in this case? One possibility is to hack the 
planner/rewriter to get the specific tuples visible, but i don't think this 
is a good idea. The other possibility is to consider views without the 
CHECK OPTION read-only, which would help to not break any database dump's 
with views, which have user defined update rules.
 Any comments, thoughts or opinions?

-- 
 Bernd