Skip site navigation (1) Skip section navigation (2)

Re: example for read committed/volitile functions

From: elein(at)varlena(dot)com (elein)
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: elein <elein(at)varlena(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: example for read committed/volitile functions
Date: 2005-01-30 23:21:22
Message-ID: 20050130232122.GF1898@varlena.com (view raw or flat)
Thread:
Lists: pgsql-general
On Sun, Jan 30, 2005 at 05:46:03PM -0500, Tom Lane wrote:
> elein(at)varlena(dot)com (elein) writes:
> > I tried to reproduce the case where 7.4 and 8.0 differ
> > on the scope of volitile function with READ COMMITTED 
> > transactions.
> 
> > In an open transaction in read committed mode.
> > Then I access the table and sum columns in a function.  
> > The results are always consistent between 7.4 and 8.0 
> > and can see committed insert transactions by another session
> > while the first transaction is open.
> 
> What's your test case?  8.0 very definitely behaves differently from
> prior releases --- try for instance Oliver Garcia's example:
> http://archives.postgresql.org/pgsql-bugs/2002-02/msg00142.php
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq
> 

Garcia is using LOCK EXCLUSIVE where I am trying to see how
READ COMMITTED differs.  I'm not sure if we're talking about
the same thing.

I'm getting the text from the release
notes:
    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. 

I have multiple statements in a READ COMMITTED transaction that
can see COMMITs from another concurrent transaction (although not 
vice versa, of course).  The behaviour is consistent in 7.4 and 8.0
and correct imho.

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
insert into foo select 1, 'one', sumfoo();
...
	(in another session, no isolation level set
	insert into foo select 5, 'five', sumfoo(); -- can't see 1
	)
insert into foo select 2, 'two', sumfoo(); -- can see 5
...
END;

Where sumfoo() selects and sums the first column of foo;

I'm thinking I'm not setting up the test case to show the right thing.

elein

In response to

Responses

pgsql-general by date

Next:From: Rick ApichairukDate: 2005-01-30 23:36:54
Subject: Re: FreeBSD 5.2.1, postgresql 7.4.5 and shared memory settings
Previous:From: Greg StarkDate: 2005-01-30 23:05:37
Subject: Re: [pgsql-advocacy] MySQL worm attacks Windows servers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group