Re: wrong optimization ( postgres 8.0.3 )

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:
> 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.

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