public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Agis <[email protected]>
Cc: [email protected]
Subject: Re: Soundness of strategy for detecting locks acquired by DDL statements
Date: Wed, 07 May 2025 07:27:46 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CALkdH=DgNmAD+yS6Qoq_rVCK_3wD=OB3Kvt9B5rzMBnG6MgYyg@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<CALkdH=DgNmAD+yS6Qoq_rVCK_3wD=OB3Kvt9B5rzMBnG6MgYyg@mail.gmail.com>

On Wed, 2025-05-07 at 06:08 +0300, Agis wrote:
> On Wed, May 7, 2025, 00:57 Laurenz Albe <[email protected]> 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

view thread (3+ messages)

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], [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