The following bug has been logged online:

Bug reference: 1733
Logged by: Robert Grabowski
Email address: grabba (AT) env (DOT) 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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

"Robert Grabowski" <grabba (AT) env (DOT) pl> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Tom Lane wrote:
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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Tom Lane wrote:
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





---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly