Thread: example for read committed/volitile functions
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. Perhaps I'm missing something basic? Or the fix was back ported to 7.4? Prompt replies would be very helpful. --elein
elein@varlena.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
On Sun, Jan 30, 2005 at 05:46:03PM -0500, Tom Lane wrote: > elein@varlena.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
elein@varlena.com (elein) writes:
> 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.
Right, but put those same statements inside a plpgsql function and
then you'll see a difference.
Essentially, 8.0 causes SQL commands executed inside a function to
behave the same as they would if issued interactively. Before, there
was a difference.
regards, tom lane
That example is good, except I see the opposite of what the release notes say. In 7.4 I see committed transactions from another session in my function and in 8.0 I do not. I will go back and verify my tests :( I don't suppose it is likely that the release notes have it backwards. Perhaps I am reading it backwards. --elein On Sun, Jan 30, 2005 at 06:51:42PM -0500, Tom Lane wrote: > elein@varlena.com (elein) writes: > > 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. > > Right, but put those same statements inside a plpgsql function and > then you'll see a difference. > > Essentially, 8.0 causes SQL commands executed inside a function to > behave the same as they would if issued interactively. Before, there > was a difference. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >