Soundness of strategy for detecting locks acquired by DDL statements

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Agis Anastasopoulos <[email protected]>
To: [email protected]
Subject: Soundness of strategy for detecting locks acquired by DDL statements
Date: Tue, 6 May 2025 12:06:45 +0300
Message-ID: <[email protected]> (raw)

Hello! I'd like to "preflight" a given schema migration (i.e. one or 
more DDL statements) before applying it to the production database (e.g. 
for use in a CI pipeline). I'm thinking of a strategy and would like to 
know about its soundness.

The general idea is:

- you have a test database that's a clone of your production one (with 
or without data but with the schema being identical)
- given the DDL statements, you open a transaction, grab its pid, and 
for each statement:
   1. from a different "observer" connection, you read pg_locks, 
filtering locks for that pid. This is the "before" locks
   2. from the first tx, you execute the statement
   3. from the observer, you grab again pg_locks and compute the diff 
between this and the "before" view
   4. from the first tx, you rollback the transaction

By diffing the after/before pg_locks view, my assumption is that you 
know what locks will be acquired by the DDL statements (but not for how 
long). The query I'm thinking is:

     SELECT locktype, database, relation, objid, mode FROM 
pg_catalog.pg_locks WHERE pid = $1 AND locktype IN ('relation', 
'object') AND granted";

The type of statements that would be fed as input would be `ALTER|CREATE 
TABLE`, `CREATE|DROP INDEX` and perhaps DML statements (`UPDATE`, 
`INSERT`, `DELETE`).

Do you think this is a robust way to detect the locks that were 
acquired? Are there any caveats/drawbacks/flaws in this strategy?

Thanks in advance

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Soundness of strategy for detecting locks acquired by DDL statements
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox