public inbox for [email protected]  

help / color / mirror / Atom feed

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

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
> >
> > 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
>

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

>

view thread (3+ messages)  latest in thread

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: <CALkdH=DgNmAD+yS6Qoq_rVCK_3wD=OB3Kvt9B5rzMBnG6MgYyg@mail.gmail.com>

* 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