Re: [HACKERS] Calculated view fields (8.1 != 8.2)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] writes: I can immagine a case when a lower module exports a view to upper layer stating the interface as list of fields: first_name, last_name, with an *hidden* field that is a function call that updates the statistics on how many time a given record was selected, then this technique can not be used anymore starting with 8.2.x. You're living in a dream world if you think that works reliably in *any* version of Postgres. But for starters, what is your definition of selected --- pulled from the physical table? Accumulated into an aggregate? Delivered as a recognizable row to the client? Delivered N times to the client due to joining N times to some other table? Well that was a not good example, I don't have any problem in mark from now on all my function as stable/immutable (the one I use on views) but still I believe is source of bad performance evaluate a function on rows discarded and at same time this break the principle of least surprise. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFF9U8R7UpzwH2SGd4RAhoGAKDSpUSQ3lGEdIdFWLwQjxoZXUAS1ACdGtht TZg9BKScbzGO0MzpHy0Gr80= =auwk -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Calculated view fields (8.1 != 8.2)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Gaetano Mendola wrote: Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] writes: [ 8.2 evaluates volatile functions in the targetlist of a view ] If I mark the function as STABLE or IMMUTABLE then even with version 8.2 the function is not evaluated. Is this the intended behavior? Yes; people complained that we needed to be more careful about the number of times volatile functions get evaluated. I suspect that functions are evaluated also for record discarded due to joins. Is that the case? Like: SELECT * FROM ta JOIN tb USING (id) where tb.foo = 4; If ta is a view with some calculated fields are the function on ta evaluated only for record matching the filters or in some case ( like a full scan on ta ) also for the records discarded due to the join? I did a check on a 8.2 and I can confirm my suspects: kalman=# create table ta ( a integer, b integer ); CREATE TABLE kalman=# create table tb ( b integer, c integer ); CREATE TABLE kalman=# kalman=# CREATE OR REPLACE FUNCTION sp_test ( INTEGER ) kalman-# RETURNS INTEGER AS' kalman'# DECLARE kalman'# a_idALIAS FOR $1; kalman'# BEGIN kalman'# RAISE NOTICE ''here''; kalman'# kalman'# return 3; kalman'# END; kalman'# ' LANGUAGE 'plpgsql'; CREATE FUNCTION kalman=# kalman=# CREATE OR REPLACE VIEW v_ta AS kalman-#SELECT kalman-# sp_test(a) AS a, kalman-# b AS b kalman-#FROM kalman-# ta c kalman-# ; CREATE VIEW kalman=# kalman=# insert into ta values (2,3); INSERT 0 1 kalman=# insert into ta values (3,4); INSERT 0 1 kalman=# insert into tb values (4,5); INSERT 0 1 kalman=# kalman=# select * from v_ta join tb using (b) where c = 5; NOTICE: here NOTICE: here b | a | c - ---+---+--- 4 | 3 | 5 (1 row) 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? Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFF8TAc7UpzwH2SGd4RAgajAKCvIxLH9JSBk4gxSbuaq4WE2y7v2wCfbnRa jWDV3hlEq/Loye6G+E2S9Ew= =LR5T -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Calculated view fields (8.1 != 8.2)
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. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Calculated view fields (8.1 != 8.2)
Martijn van Oosterhout kleptog@svana.org writes: 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. Volatile functions that are not at the top level of a query are *always* going to be a risk factor, in that you don't know quite where the planner is going to evaluate them. While I'm not by any means wedded to the 8.2 no-flattening patch, it seems to me to be reasonable because it reduces that uncertainty a bit. The fact that Gaetano's code depended on the uncertainty being resolved in a different direction is unfortunate, but I think his code is really to blame, because postponing the function eval like that couldn't be guaranteed anyway across all queries. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Calculated view fields (8.1 != 8.2)
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. If your function is already marked immutable or stable, then nothing changes for you. If you *did* call volatile functions inside your select, then you now get consistens behaviour. Since you don't want your function to be evaluated in all cases, I assume that it shouldn't be marked volatile in the first place. I think a lot of people forget to mark their functions volatile/stable/immutable correctly, or don't know about the implications of these flags. Maybe there should be a guc force_explicit_sideeffeect_declaration (defaulting to on) which makes specifying either volatile, stable or immutable mandatory. Then people would (hopefully) read the relevant part of the docs before creating a function, and probably get the declaration right in the first place. greetings, Florian Pflug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Calculated view fields (8.1 != 8.2)
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_idALIAS 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
Re: [HACKERS] Calculated view fields (8.1 != 8.2)
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_idALIAS 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
Re: [HACKERS] Calculated view fields (8.1 != 8.2)
Florian G. Pflug wrote:
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_idALIAS 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.
I full agree with this, that was just an extreme example of an hidden undesired
call. In my framework I don't have by coding rule any function with side
effects
applied at view fields, however I have some functions not marked correctly as
STABLE ( mea culpa ) that degraded the performances until I realized what was
going
on; I'm in the opinion that is not sane call a function not marked as
stable/immutable
for discarded column (I can in some way accept this ) and most of all on
discarded
rows.
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Calculated view fields (8.1 != 8.2)
Florian G. Pflug 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. If your function is already marked immutable or stable, then nothing changes for you. If you *did* call volatile functions inside your select, then you now get consistens behaviour. Since you don't want your function to be evaluated in all cases, I assume that it shouldn't be marked volatile in the first place. Well some function are volatile and can not be marked as stable. We develop our applications layering the modules, we didn't have any reason to forbid as coding rule to put function call on view in low level layers. After all views are there also to build up your schema layering the info. I can immagine a case when a lower module exports a view to upper layer stating the interface as list of fields: first_name, last_name, with an *hidden* field that is a function call that updates the statistics on how many time a given record was selected, then this technique can not be used anymore starting with 8.2.x. The above is not my case but it can be a possible scenario (I admit not a sane one ). Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Calculated view fields (8.1 != 8.2)
Gaetano Mendola [EMAIL PROTECTED] writes: I can immagine a case when a lower module exports a view to upper layer stating the interface as list of fields: first_name, last_name, with an *hidden* field that is a function call that updates the statistics on how many time a given record was selected, then this technique can not be used anymore starting with 8.2.x. You're living in a dream world if you think that works reliably in *any* version of Postgres. But for starters, what is your definition of selected --- pulled from the physical table? Accumulated into an aggregate? Delivered as a recognizable row to the client? Delivered N times to the client due to joining N times to some other table? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Calculated view fields (8.1 != 8.2)
Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] writes: [ 8.2 evaluates volatile functions in the targetlist of a view ] If I mark the function as STABLE or IMMUTABLE then even with version 8.2 the function is not evaluated. Is this the intended behavior? Yes; people complained that we needed to be more careful about the number of times volatile functions get evaluated. I suspect that functions are evaluated also for record discarded due to joins. Is that the case? Like: SELECT * FROM ta JOIN tb USING (id) where tb.foo = 4; If ta is a view with some calculated fields are the function on ta evaluated only for record matching the filters or in some case ( like a full scan on ta ) also for the records discarded due to the join? Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Calculated view fields (8.1 != 8.2)
Gaetano Mendola [EMAIL PROTECTED] writes: [ 8.2 evaluates volatile functions in the targetlist of a view ] If I mark the function as STABLE or IMMUTABLE then even with version 8.2 the function is not evaluated. Is this the intended behavior? Yes; people complained that we needed to be more careful about the number of times volatile functions get evaluated. I didn't see something about it in the release note. listitem para Do not flatten subqueries that contain literalvolatile/ functions in their target lists (Jaime Casanova) /para para This prevents surprising behavior due to multiple evaluation of a literalvolatile/ function (such as functionrandom()/ or functionnextval()/). It might cause performance degradation in the presence of functions that are unnecessarily marked as literalvolatile/. /para /listitem regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
