I have a stored procedure on Postgres, which processes large data and takes a good time to complete. In my application, there is a chance that 2 processes or schedulers can run this procedure at same time. I want to know if there is a built in mechanism in db to allow only instance of this procedure to run at db level.

I searched the internet, but didn't find anything concrete.

CodePudding user response：

There is nothing built in to define a procedure (or function) so that concurrent execution is prevented.

But you can use advisory locks to achieve something like that.

At the very beginning of the procedure, you can add something like:

perform pg_advisory_lock(987654321);

which will then wait to get the lock. If a second session invokes the procedure it will have to wait.

Make sure you release the lock at the end of the procedure using pg_advisory_unlock() as they are not released when the transaction is committed.

If you use advisory locks elsewhere, make sure you use a key that can't be used in other places.