-----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_id ALIAS 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
