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

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 8 of 13 | next ›
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMar 6, '07 at 3:58p
activeMar 12, '07 at 1:01p
posts13
users5
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase