8

I found

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?

2 Answers 2

5

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

3
  • Great! So to check my understanding, if step 5 is omitted, then whether step 4 was dropping or modifying the function, step 6 will succeed and will run the original version?
    – Wildcard
    Dec 24, 2017 at 8:45
  • 1
    @Wildcard: If you have step 3 (BEGIN;), but not step 5 (COMMIT;) your change, whether DROP or REPLACE is never visible to any other transaction than transaction B. You'll have to COMMIT or ROLLBACK sooner or later, though. Better sooner than later, idle in transaction shouldn't go on for too long, it's blocking other business ... Dec 24, 2017 at 14:03
  • Thanks; the point about never visible to any other transaction may seem obvious but the exception for TRUNCATE made me unsure.
    – Wildcard
    Mar 17, 2018 at 0:30
6

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.

5
  • 2
    I don't agree with the conclusion. If you remove the first select * from f() from TRAN1 in read commited mode, then the second select * from f() fails. The above test seems to demonstrate the effect of a lookup cache local to TRAN1 rather than what you conclude in the answer. Dec 23, 2017 at 11:09
  • @DanielVérité I tried various things, incuding what you say, in all isolation levels. It didn't matter. Do you disagree with the first part (or the second about both trans modifying the function?) Dec 23, 2017 at 12:45
  • @DanielVérité did you test with a different function (that involved reading from tables)? Dec 23, 2017 at 13:54
  • Exactly the same test as you, copy-pasted from the answer, PG 10, all defaults, except not calling select * from f() in TRAN1 before dropping the function in the other transaction. Dec 23, 2017 at 14:06
  • Weird. I get what you say, only if I use a reading data function (Postgres 9.6) I'll do more tests in 10 as well and edit the answer. Dec 23, 2017 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.