Re: wrong optimization ( postgres 8.0.3 )

Gaetano Mendola <mendola(at)bigfoot(dot)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