BUG #1733: Function visibility in transactions error
The following bug has been logged online:
Bug reference: 1733
Logged by: Robert Grabowski
Email address: grabba@env.pl
PostgreSQL version: 8.0.1
Operating system: Linux 2.6.12-gentoo
Description: Function visibility in transactions error
Details:
I have problem with function visibility in transaction when I do create and
drop function. There is an example.
I have two sessions:
1: CREATE FUNCTION test () RETURNS integer AS 'select 1' LANGUAGE 'sql';
CREATE FUNCTION
1: SELECT test();
test
------
1
(1 row)
2: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
2: SELECT test();
test
------
1
(1 row)
1: DROP FUNCTION test ();
DROP FUNCTION
1: CREATE FUNCTION test () RETURNS integer AS 'select 2' LANGUAGE 'sql';
CREATE FUNCTION
1: SELECT test();
test
------
2
(1 row)
2: SELECT test();
test
------
1
(1 row)
It's ok. In transaction I have old definition of test function.
2: SELECT test();
test
------
2
(1 row)
!!!! Strange! Second call of the same function gets other result.
In READ COMMITED transactions I have the same results.
"Robert Grabowski" <grabba@env.pl> writes:
I have problem with function visibility in transaction when I do create and
drop function.
System catalog lookups generally follow SnapshotNow rules, not MVCC.
So the new function definition is visible as soon as it's committed,
whether you are in a serializable transaction or not.
regards, tom lane
Tom Lane wrote:
"Robert Grabowski" <grabba@env.pl> writes:
I have problem with function visibility in transaction when I do create and
drop function.System catalog lookups generally follow SnapshotNow rules, not MVCC.
So the new function definition is visible as soon as it's committed,
whether you are in a serializable transaction or not.regards, tom lane
Hi!
OK. I can understand that. But why at first call after update function
have I results of the old function, and at second call in the same
transaction I have results of the new function. It is strange for me.
pozdrawiam
Robert Grabowski
Tom Lane wrote:
System catalog lookups generally follow SnapshotNow rules, not MVCC.
So the new function definition is visible as soon as it's committed,
whether you are in a serializable transaction or not.
IMHO this is a bug, or at least not optimal behavior. But per the
discussion on -hackers on this topic a few months ago, it seems pretty
difficult to fix.
-Neil