tuple concurrently updated when creating functions in postgresql / PL/pgSQL -

when initializing process, runs pl/pgsql statement below creating 2 functions. however, every time create multiple processes simultaneously part of end-to-end test, parallel execution of statement leads tuple concurrently updated error can't seem around. appreciated.

create or replace function   count_rows(schema text, tablename text) returns integer     $body$   declare     result integer;     query varchar;   begin     query := 'select count(1) "' || schema || '"."' || tablename || '"';     execute query result;     return result;   end;   $body$   language plpgsql;  create or replace function   delete_if_empty(schema text, tablename text) returns integer     $$   declare     result integer;     query varchar;   begin     query := 'select count(*) "' || schema || '"."' || tablename || '"';     execute query result;     if result = 0       execute 'drop table "' || schema || '"."' || tablename || '" cascade;';       execute 'notify "' || schema || '", ''destroy_table:' || tablename || ''';';       return 1;     end if;     return 0;   end;   $$   language plpgsql;    select version() 

as described here, postgres doesn't allow use create function concurrently:

it'd necessary add kind of locking scheme if want avoid "tuple concurrently updated" errors. not different situation 2 transactions both want update same row in user table: unless application takes steps serialize updates, you're going "tuple concurrently updated" errors.

we have such locking ddl on tables/indexes, theory in past has been it's not worth trouble objects represented single catalog rows, such functions or roles.

a solution ensure no 2 transaction try create function @ same time.

you can use posgres advisory locks that.

a introduction advisory locks can found here: https://vladmihalcea.com/2017/04/12/how-do-postgresql-advisory-locks-work/

for example, can use:

begin; -- start of transaction  select pg_advisory_xact_lock(2142616474639426746); -- random 64-bit signed ('bigint') lock number  create or replace function myfunction ...  commit; 

this takes transaction-level exclusive advisory lock, no 2 concurrent transaction can run create function @ same time. @ end of transaction, lock automatically released.