Postgres concurrent transactions unexpected issue

Question

When the following transaction is run concurrently on different connections it sometimes errors with

trigger "my_trigger" for relation "my_table" already exists

What am I doing wrong?

BEGIN;

DROP TRIGGER IF EXISTS my_trigger ON my_table;
CREATE TRIGGER my_trigger
  AFTER INSERT ON my_table
  REFERENCING NEW TABLE AS new_table
  FOR EACH STATEMENT EXECUTE PROCEDURE my_function();

COMMIT;

I am trying to set up a system where I can add triggers to notify about data changes in specific tables. If a table already has such a trigger then skip it. Otherwise CREATE all CRUD triggers. This logic needs to run sequentially in case of concurrent requests.

After trying ISOLATION LEVEL SERIALIZABLE I noticed that any conflicting transactions are failed and dropped (I would need to manually check sql status and retry). But what I want is to queue up these transactions and run afterwards one by one in the order they're sent.

At the moment I am trying to achieve this by having a my_triggers (table_name TEXT) table that has a BEFORE INSERT OR DELETE trigger. Within this trigger I do the actual table trigger upsert logic. Inserts or deletes on my_triggers are made with LOCK TABLE my_triggers IN ACCESS EXCLUSIVE MODE ... which should queue up conflicting CRUD transactions ?!

You probably need the serialisable transaction isolation level. — Bergi
– Bergi, Commented Apr 4, 2021 at 15:58
@Bergi replacing BEGIN with BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; does not seem to help :( — user5480949
– user5480949, Commented Apr 4, 2021 at 16:09
What version are you running? And what are you trying to do? — jjanes
– jjanes, Commented Apr 5, 2021 at 14:53

Julius Tuskenis · Accepted Answer · 2021-04-06 17:39:17Z

What happens is following:

BEGIN....DROP TRIGGER IF EXISTS....CREATE TRIGGER....COMMIT;
..BEGIN....DROP TRIGGER IF EXISTS....CREATE TRIGGER--------EXCEPTION.

Both transactions starts when trigger is not present.
Both succeed in drop trigger because of "IF EXISTS" statement.
First transaction starts creating a trigger. For that a SHARE ROW EXCLUSIVE lock is placed on table my_table. The lock SHARE ROW EXCLUSIVE conflicts with it self so no other transaction is allowed to create a trigger until the first one completes.
Second transaction blocks on CREATE TRIGGER.
First transaction completes.
Second transaction proceeds with CREATE TRIGGER but it already exists. Exception is raised.

What you need is adding a LOCK before DROP TRIGGER statement. This way you will ensure the trigger is dropped and not created in concurrent transaction.

BEGIN;

LOCK TABLE my_table IN SHARE ROW EXCLUSIVE MODE ;
DROP TRIGGER IF EXISTS my_trigger ON my_table;
CREATE TRIGGER my_trigger
  AFTER INSERT ON my_table
  REFERENCING NEW TABLE AS new_table
  FOR EACH STATEMENT EXECUTE PROCEDURE my_function();

COMMIT;

Collectives™ on Stack Overflow

Postgres concurrent transactions unexpected issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related