Thread: Soundness of strategy for detecting locks acquired by DDL statements

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

On 5/6/25 2:06 AM, Agis Anastasopoulos wrote:
> 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.

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


Why not just read this?:

https://www.postgresql.org/docs/current/explicit-locking.html

> 
> Thanks in advance
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 6/5/25 12:06, Agis Anastasopoulos wrote:

> 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?
>
Why consume your time in a guessing game, instead of reading the actual 
docs? In addition to the doc that Adrian suggested, there are the 
individual docs for each DDL, that list exactly the locks acquired. You 
may categorize those and think of he estimated traffic of conflicting 
DML statements from the app. Another more brute force method is to 
attempt the schema changes while replaying the actual production traffic 
or something that looks like this. Example : you prepare a logical 
replicated subscriber that gets all the changes from the production, and 
attempt the schema migration there : beware to religiously monitor your 
space and any replication breakage, although all those issues are not 
show stoppers in pgsql 16+ IIRC.

PS

I am happy to see more Greeks in the lists! How about creating some user 
group sometime !

> Thanks in advance
>
>
>

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

On Wed, 2025-05-07 at 06:08 +0300, Agis wrote:
> On Wed, May 7, 2025, 00:57 Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > 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
> >
> > I think that that is a good strategy, as long as you run all DDL statements
> > in a single transaction.
>
> 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?

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