Re: [HACKERS] Updatable views

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Am Donnerstag, 31. August 2006 15:55 schrieb Tom Lane:
>> The proposed WITH CHECK OPTION implementation is unworkable for exactly
>> this reason --- it will give the wrong answers in the presence of
>> volatile functions such as nextval().

> I'm not sure why anyone would want to define a view condition containing a
> volatile function. At least it wouldn't put a major dent into this feature
> if such views were decreed not updatable.

The problem is not with the view condition. Consider

CREATE TABLE data (id serial primary key, ...);

CREATE VIEW only_new_data AS SELECT * FROM data WHERE id > 12345
WITH CHECK OPTION;

INSERT INTO only_new_data VALUES(nextval('data_id_seq'), ...);

The proposed implementation will execute nextval twice (bad), and will
apply the WITH CHECK OPTION test to the value that isn't the one stored
(much worse). It doesn't help if the id is defaulted.

regards, tom lane