Re: Invoking SQL function while doing CREATE OR REPLACE on it

> On 03/05/2023 20:17 CEST Nagendra Mahesh (namahesh) <namahesh(at)cisco(dot)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