7

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?

4

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

|improve this answer|||||
  • 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 '17 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 ... – Erwin Brandstetter Dec 24 '17 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 '18 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.

|improve this answer|||||
  • 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. – Daniel Vérité Dec 23 '17 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?) – ypercubeᵀᴹ Dec 23 '17 at 12:45
  • @DanielVérité did you test with a different function (that involved reading from tables)? – ypercubeᵀᴹ Dec 23 '17 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. – Daniel Vérité Dec 23 '17 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. – ypercubeᵀᴹ Dec 23 '17 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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