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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

               http://www.postgresql.org/about/donate

Reply via email to