3

I'm having a locking issue on PostgreSQL 9.4.5. I run two or more transactions simultaneously (generally there are 2 running at the same time, but 2+ may happen sometimes) reading from the same tables but writing on different output tables. Inside my transactions I have several DROP FUNCTION IF EXISTS and CREATE OR REPLACE FUNCTION statements that declare some functions.

My guess is that DROP FUNCTION IF EXISTS and CREATE OR REPLACE FUNCTION are locking. Am I right? I can solve the problem by moving the statement outside the transaction.

I've observed the following: two transactions A and B contains some DROP FUNCTION and CREATE OR REPLACE FUNCTION; if I execute transaction B while transaction A is still running, transaction B waits on the first DROP FUNCTION statements it encounters.

Paul White
  • 94,921
  • 30
  • 437
  • 687
pietrop
  • 756
  • 1
  • 7
  • 16

1 Answers1

1

Community wiki answer:

Yes of course the other transaction will wait. (Nearly) Every DDL statement takes an exclusive lock on the object being modified (dropped, created). See Explicit Locking in the documentation.