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

Search Discussions

Discussion Posts

Previous

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 3 of 3 | next ›
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedOct 5, '05 at 3:34p
activeOct 5, '05 at 8:14p
posts3
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Gaetano Mendola: 2 posts Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase