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

Question

When initializing my process, it runs the PL/pgSQL statement below creating two functions. However, every time I create multiple processes simultaneously as part of an end-to-end test, parallel execution of this statement leads to a tuple concurrently updated error that I can't seem to get around. Any help would be much appreciated.

CREATE OR REPLACE FUNCTION
  count_rows(schema text, tablename text) returns integer
  AS
  $body$
  DECLARE
    result integer;
    query varchar;
  BEGIN
    query := 'SELECT count(1) FROM "' || schema || '"."' || tablename || '"';
    execute query into result;
    return result;
  END;
  $body$
  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION
  delete_if_empty(schema text, tablename text) RETURNS INTEGER
  AS
  $$
  DECLARE
    result integer;
    query varchar;
  BEGIN
    query := 'SELECT COUNT(*) FROM "' || schema || '"."' || tablename || '"';
    execute query into result;
    IF result = 0 THEN
      EXECUTE 'DROP TABLE "' || schema || '"."' || tablename || '" CASCADE;';
      EXECUTE 'NOTIFY "' || schema || '", ''DESTROY_TABLE:' || tablename || ''';';
      RETURN 1;
    END IF;
    RETURN 0;
  END;
  $$
  LANGUAGE plpgsql;

  SELECT version()

This at least explains why it happens (why CREATE OR REPLACE FUNCTION cannot be run concurrently without the tuple concurrently updated error): postgresql.org/message-id/3473.1393693757%40sss.pgh.pa.us — nh2
– nh2, Commented May 21, 2017 at 19:12

Vlad Mihalcea · Accepted Answer · 2018-01-05 19:04:43Z

As described here, postgres doesn't currently allow you to use CREATE FUNCTION concurrently:

It'd be necessary to add some kind of locking scheme if you want to avoid "tuple concurrently updated" errors. This is not really any different from the situation where two transactions both want to update the same row in a user table: unless the application takes extra steps to serialize the updates, you're going to get "tuple concurrently updated" errors.

We do have such locking for DDL on tables/indexes, but the theory in the past has been that it's not worth the trouble for objects represented by single catalog rows, such as functions or roles.

A solution to this is to ensure that no two transaction try to do the CREATE FUNCTION at the same time.

You can use posgres advisory locks for that.

A good introduction to advisory locks can be found here: https://vladmihalcea.com/how-do-postgresql-advisory-locks-work/

For example, you 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 a transaction-level exclusive advisory lock, so that no two concurrent transaction can run create the function at the same time. At the end of the transaction, the lock is automatically released.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related