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
Import Notes
Reply to msg id not found: 43441099.9040906@bigfoot.com
-----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_connectionCREATE 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 | 978as 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-----