postgresql

Can I find out wheter a lock _can_ be acquired in PostgreSQL

Is it possible to find out whether, say ALTER TABLE ADD CONSTRAINT will acquire a lock immediately, or will have to wait on a different session?

Solution

  • I think that the best solution is to try and see. For that purpose, set lock_timeout to 1 millisecond, so that the ALTER TABLE statement fails if it cannot acquire a lock immediately:

    BEGIN;  -- start transaction

SET LOCAL lock_timeout = 1;  -- only for this transaction

ALTER TABLE ...

COMMIT;  -- will rollback if there was an error