Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Updatable views

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: pgsql-hackers(at)postgresql(dot)org, Bernd Helmle <mailings(at)oopsware(dot)de>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: [HACKERS] Updatable views
Date: 2006-08-31 15:10:47
Message-ID: 7908.1157037047@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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

In response to

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2006-08-31 15:11:18
Subject: Re: GUC settings with units broken?
Previous:From: eleinDate: 2006-08-31 15:03:27
Subject: Re: gBorg status?

pgsql-patches by date

Next:From: Tom DunstanDate: 2006-08-31 16:09:34
Subject: Enums patch v1
Previous:From: Peter EisentrautDate: 2006-08-31 14:35:14
Subject: Re: [HACKERS] Updatable views

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group