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

Question

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:

1. Create a function myfunc()
2. Start a transaction from client A
3. Start a transaction from client B
4. In transaction B, use "create or replace function" to revise the definition of myfunc()
5. Commit transaction B
6. 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?

Answer 1

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.

Answer 2

What happens in step 6?

Transaction A sees the updated definition of function myfunc() immediately. (But see the effect of cache below.)

And if the function is dropped in step 4 rather than being modified, will step 6 fail or succeed?

It will fail. (But see the effect of cache below.)

Postgres DDL commands are fully transactional. While transaction B does not commit, both transactions would continue to see different versions of the function. But concurrent transactions do see committed changes in system catalogs. Would seem obvious in default isolation level READ COMMITTED. But you cannot even prevent this with isolation levels REPEATABLE READ or SERIALIZABLE.

If you should have called the function in transaction A before transaction B committed a change, the local cache can interfere. In my tests, one more call worked with the cached (old) function before the next call was aware of the change and answered accordingly.

I did not find documentation how the system catalog cache behaves for this exactly (still might exist somewhere). I am not convinced the last bit (one more call answered from cache) is the best possible behavior.

---

BTW, your steps 3. - 5. can be reduced to just 4., without any difference. Explicit or implicit transaction wrappers work the same:

3. Start a transaction from client B
4. In transaction B, use "create or replace function" to revise the definition of myfunc()
5. Commit transaction B