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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
