Re: [HACKERS] wrong optimization ( postgres 8.0.3 )

[Gaetano Mendola]

-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

Re: [HACKERS] wrong optimization ( postgres 8.0.3 )

[Tom Lane]

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

[HACKERS] wrong optimization ( postgres 8.0.3 )

[Gaetano Mendola]

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











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