Is a dropped (or altered) function still available inside already opened transactions?

I found

• PostgreSQL - Are CREATE FUNCTION and DROP FUNCTION locking when used inside different transactions?

but there are no answers and isn't exactly the same as my question (though very similar).

Let's say I do the following:

• Create a function myfunc()

• Start a transaction from client A

• Start a transaction from client B

• In transaction B, use "create or replace function" to revise the definition of myfunc()

• Commit transaction B

• Call myfunc() from transaction A

What happens in step 6? Am I calling the original function as defined in step 1? Or the modified form from step 4 (committed in step 5)?

And if the function is dropped in step 4 rather than being modified, will step 6 fail or succeed? (This is probably the same question but modifications may work differently.)

Where is the documentation about this?

Interesting question.

From a small test, it appears that function modifications and deletes are transactional. Meaning, that - in any isolation level - when transaction 2 modifies or deletes the function, transaction 1 is oblivious to it and still uses the old version of the function.

Any changes to the function become visible only after the transaction commits and only to transactions that start after that commit. The isolation level is irrelevant, as the function tested was not reading any data from any tables.

-- Create Function
x=# create or replace function f() returns integer as
$ select 1 ; $ immutable language sql ;
CREATE FUNCTION

-- TRAN 1
x=# begin ;
BEGIN
x=# select * from f() ;
 f 
---
 1
(1 row)
                    -- TRAN 2
                    x=# begin ;
                    BEGIN
                    x=# drop function f () ;
                    DROP FUNCTION
                    x=# commit ;
                    COMMIT
-- TRAN 1
x=# select * from f() ;
 f 
---
 1
(1 row)
x=# commit ;
COMMIT

-- After COMMIT
x=# select * from f() ;
ERROR:  function f() does not exist
LINE 1: select * from f() ;
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
x=#

In a slightly different scenario, if both transactions try to modify the function, then only one succeeds and the other gets blocked and then fails if the first commits.

StackExchange Database Administrators Q#193875, answer score: 6