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