Обсуждение: 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-----