Re: Calculated view fields (8.1 != 8.2)

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.

Regards
Gaetano Mendola