Обсуждение: 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
Gaetano Mendola <mendola@bigfoot.com> 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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Tom Lane wrote: > Gaetano Mendola <mendola@bigfoot.com> 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-----