1

I would like to have some way of inspecting what locks a DDL operation will need to acquire.

Preferably something generic enough that I don't need to personally know/encode knowledge of all of the Postgres locking rules.

Something like EXPLAIN but specific to DDL operations.

I don't want to run the command and then look at any locks that are blocking... I want to know in advance what locks the DDL operation will need to acquire (e.g. ACCESS EXCLUSIVE on tables x, y, z), taking into account existing foreign key constraints etc.

1

What I do is BEGIN the transaction, run the DDL, then look at the pg_locks table to see what locks are held (not blocked, but held). I do this on the test server, so there is nothing to block it or be blocked by it. But the knowledge generally transfers to production. Some DDL can't be run in transactions, those generally require special handling.

| improve this answer | |
  • ok this can work, so I need a test db with same structure as the production db, and I need DDL commands that can be run in transaction (I think that covers all the ones I ever wanted so far). I have upvoted, would love to see some example code in the answer – Anentropic Sep 12 '18 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.