> On 03/05/2023 20:17 CEST Nagendra Mahesh (namahesh) <namah...@cisco.com> > wrote: > > I have a Postgres 14.4 cluster (AWS Aurora) to which I connect from my > application using JDBC. > > I use liquibase for schema management - not only tables, but also a bunch of > SQL stored procedures and functions. Basically, there is one liquibase > changeSet that runs last and executes a set of SQL files which contain stored > procedures and functions. > > CREATE OR REPLACE FUNCTION bar(arg1 text, arg2 text) RETURNS record LANGUAGE > "plpgsql" AS ' > BEGIN > // function body > END; > '; > > These functions / procedures are replaced ONLY when there is a change in one / > more SQL files which are part of this changeSet. (runOnChange: true). > > Whenever I do a rolling deployment of my application (say, with a change in > the function body of bar()), liquibase will execute the CREATE OR REPLACE > FUNCTION bar() > as part of a transaction. > > In the few milliseconds while bar() is being replaced, there are other ongoing > transactions (from other replicas of my application) which are continuously > trying to invoke bar(). > > Only in this tiny time window, few transactions fail with the following error: > > ERROR: function bar(arg1 => text, arg2 => text) does not exist > Hint: No function matches the given name and argument types. You might need > to add explicit type casts. > Position: 4 : errorCode = 42883
CREATE OR REPLACE FUNCTION should be atomic and cannot change the function signature. I don't see how a function cannot exist at some point in this case. Are you sure that Liquibase is not dropping the function before re-creating it? If Liquibase drops and re-creates the function in separate transactions, the transactions trying to execute that function may find it dropped when using the read committed isolation level. There's also a race condition bug in v14.4 that may be relevant. It got fixed in v14.5. See "Fix race condition when checking transaction visibility" in https://www.postgresql.org/docs/14/release-14-5.html. -- Erik
