Re: Calculated view fields (8.1 != 8.2)

Florian G. Pflug wrote:
> 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.

I full agree with this, that was just an extreme example of an hidden undesired
call. In my framework I don't have by "coding rule" any function with side effects
applied at view fields, however I have some functions not marked correctly as
STABLE ( mea culpa ) that degraded the performances until I realized what was going
on; I'm in the opinion that is not sane call a function not marked as stable/immutable
for discarded column (I can in some way accept this ) and most of all on discarded
rows.

Regards
Gaetano Mendola