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
