[HACKERS] wrong optimization ( postgres 8.0.3 )
Hi all, take a look at this simple function and view: CREATE OR REPLACE FUNCTION sp_connected_test ( INTEGER ) RETURNS BOOLEAN AS' DECLARE a_id_user ALIAS FOR $1; BEGIN PERFORM * FROM v_current_connection WHERE id_user = a_id_user; IF NOT FOUND THEN RETURN FALSE; END IF; RETURN TRUE; END; ' LANGUAGE 'plpgsql'; CREATE VIEW v_current_connection_test AS SELECT ul.id_user, cc.connected FROM current_connection cc, user_login ul WHERE cc.id_user = ul.id_user AND connected = TRUE; SELECT * FROM v_current_connection_test WHERE sp_connected_test(id_user) = FALSE; this line shall produce no row, but randomly does. If I put a RAISE NOTICE before RETURN NULL with the id_user I notice that the function is called on records present on user_login but discarged because the join with current_connectin have connected = FALSE! I can work_around the problem rewriting the view: CREATE VIEW v_current_connection_test AS SELECT cc.id_user, cc.connected FROM current_connection cc, user_login ul WHERE cc.id_user = ul.id_user AND connected = TRUE; Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] wrong optimization ( postgres 8.0.3 )
Gaetano Mendola [EMAIL PROTECTED] writes: What I'm experiencing is a problem ( I upgraded today from 7.4.x to 8.0.3 ) that I explain here: The following function just return how many records there are inside the view v_current_connection CREATE OR REPLACE FUNCTION sp_count ( ) RETURNS INTEGER AS' DECLARE c INTEGER; BEGIN SELECT count(*) INTO c FROM v_current_connection; RETURN c; END; ' LANGUAGE 'plpgsql'; I have the following select # select count(*), sp_count() from v_current_connection; count | sp_count - ---+-- 977 | 978 as you can see the two count are returning different record numbers ( in meant time other transactions are updating tables behind the view v_current_connection ). This isn't surprising at all, if other transactions are actively changing the table. See the release notes for 8.0: : Observe the following incompatibilities: : : In READ COMMITTED serialization mode, volatile functions now see : the results of concurrent transactions committed up to the : beginning of each statement within the function, rather than up to : the beginning of the interactive command that called the function. : : Functions declared STABLE or IMMUTABLE always use the snapshot of : the calling query, and therefore do not see the effects of actions : taken after the calling query starts, whether in their own : transaction or other transactions. Such a function must be : read-only, too, meaning that it cannot use any SQL commands other : than SELECT. If you want this function to see the same snapshot as the calling query sees, declare it STABLE. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] wrong optimization ( postgres 8.0.3 )
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] writes: What I'm experiencing is a problem ( I upgraded today from 7.4.x to 8.0.3 ) that I explain here: The following function just return how many records there are inside the view v_current_connection CREATE OR REPLACE FUNCTION sp_count ( ) RETURNS INTEGER AS' DECLARE c INTEGER; BEGIN SELECT count(*) INTO c FROM v_current_connection; RETURN c; END; ' LANGUAGE 'plpgsql'; I have the following select # select count(*), sp_count() from v_current_connection; count | sp_count - ---+-- 977 | 978 as you can see the two count are returning different record numbers ( in meant time other transactions are updating tables behind the view v_current_connection ). This isn't surprising at all, if other transactions are actively changing the table. See the release notes for 8.0: : Observe the following incompatibilities: : : In READ COMMITTED serialization mode, volatile functions now see : the results of concurrent transactions committed up to the : beginning of each statement within the function, rather than up to : the beginning of the interactive command that called the function. : : Functions declared STABLE or IMMUTABLE always use the snapshot of : the calling query, and therefore do not see the effects of actions : taken after the calling query starts, whether in their own : transaction or other transactions. Such a function must be : read-only, too, meaning that it cannot use any SQL commands other : than SELECT. If you want this function to see the same snapshot as the calling query sees, declare it STABLE. I think I understood :-( Just to be clear: select work_on_connected_user(id_user) from v_connected_user; if that function is not stable than it can work on an id_user that is not anymore on view v_connected_user. Is this right ? Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFDRDPh7UpzwH2SGd4RAnPVAJ9PdcVoUoOh7U4poR0Hd9uT4l/QgACg9nXg sebdHozcBV7t7JZslluGzB8= =rFgE -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
