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
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 tb ( b integer, c integer );
CREATE OR REPLACE FUNCTION sp_delete_selected_row ( INTEGER )
RETURNS INTEGER AS'
a_id ALIAS FOR $1;
DELETE FROM ta where a = a_id;
' LANGUAGE 'plpgsql';
CREATE OR REPLACE VIEW v_ta AS
sp_delete_selected_row(a) AS a,
b AS b
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
select * from ta;
a | b
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
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