Thread: 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:
> 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

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