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