Tom Lane wrote:
Gaetano Mendola <mendola@bigfoot.com> writes:
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.
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 actively7.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 ).
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
