Subject: Re: Soundness of strategy for detecting locks acquired by DDL statements

From: Laurenz Albe <[email-masked]>
Date: Tue, 06 May 2025 23:57:47 +0200

On Tue, 2025-05-06 at 12:06 +0300, Agis Anastasopoulos wrote:
> 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?

I think that that is a good strategy, as long as you run all DDL statements
in a single transaction.

Yours,
Laurenz Albe

---

From: Agis <[email-masked]>
Date: Wed, 7 May 2025 06:08:34 +0300

> I think that that is a good strategy, as long as you run all DDL statements

Can you elaborate on that?

I was thinking that we should mirror the way the statements are going to be
executed in production: if they're all going to be executed inside a single
tx, then we should do the same. But if not, them we should follow course
and execute them in separate txs.

Am I missing something?

Thanks

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 07 May 2025 07:27:46 +0200

On Wed, 2025-05-07 at 06:08 +0300, Agis wrote:
> tx, then we should do the same. But if not, them we should follow course and
> execute them in separate txs.
> 
> Am I missing something?

No; I was sloppy.

What I wanted to emphasize is that you have to look at "pg_locks" *before*
the transaction is committed, otherwise you won't see any locks.
It doesn't have to be one single transaction.

Yours,
Laurenz Albe