Re: Calculated view fields (8.1 != 8.2) - Mailing list pgsql-hackers

Gaetano Mendola wrote:
> Martijn van Oosterhout wrote:
>> On Fri, Mar 09, 2007 at 10:59:56AM +0100, Gaetano Mendola wrote:
>>> Is really this what we want? I did a migration 8.0.x => 8.2.3 and I had on first hour of service up
>>> lot of queries "blocked" due to this, consider in my case I have on v_ta milions of records and usually
>>> that join extracts 1 row. Is there a way to set till I don't check all my huge schema to disable this
>>> behaviour?
>> Most people figured it was a improvment. It's configured per function
>> now, which wasn't the case before. I dont't think there was ever any
>> discussion about having a global switch.
> 
> Well it's not an improvement in term of performances but a performance degradation in the best case and
> in the worst can be devastating:
> 
> create table ta ( a integer, b integer );
> CREATE TABLE
> create table tb ( b integer, c integer );
> CREATE TABLE
> 
> CREATE OR REPLACE FUNCTION sp_delete_selected_row ( INTEGER )
> RETURNS INTEGER AS'
> DECLARE
>    a_id    ALIAS FOR $1;
> BEGIN
>    DELETE FROM ta where a = a_id;
>    return 0;
> END;
> ' LANGUAGE 'plpgsql';
> CREATE FUNCTION
> 
> CREATE OR REPLACE VIEW v_ta AS
>   SELECT
>      sp_delete_selected_row(a) AS a,
>      b                         AS b
>   FROM
>      ta
> ;
> CREATE VIEW
> 
> insert into ta values (2,3);
> INSERT 0 1
> insert into ta values (3,4);
> INSERT 0 1
> insert into tb values (4,5);
> INSERT 0 1
> 
> select * from v_ta join tb using (b) where c = 5;
> 
>  b | a | c
> ---+---+---
>  4 | 0 | 5
> (1 row)
> 
> select * from ta;
>  a | b
> ---+---
> (0 rows)
> 
> 
> All rows are gone instead of the only one extracted from that query. IMHO is a undesired side effect.
> In my case I destroyed my application statistics on how many time a certain row was extracted.

This is insane. Whoever creates a view like that on a production system should 
*immediatly* be carried away from his keyboard, to prevent further damage. 
Imagine someone using "View Data" on  this view in pgadmin.. I don't wanna be 
near him when he clicks "Refresh", and suddenly all data is gone...

Maybe calling volatile functions in selects and views should be forbidden 
entirely, except for volatile functions in the top-level select clause,
to support things like "select ..., nextval('seq') from ...".

But it's probably not worth the effort - there will always be creative
ways to shoot yourself into your foot.

greetings, Florian Pflug