Message-ID: <eb95d93d9f472673e33036b5cd80518dcd06e443.camel@cybertec.at>
Subject: Re: Soundness of strategy for detecting locks acquired by DDL
 statements
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Agis <agis.anast@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Date: Wed, 07 May 2025 07:27:46 +0200
In-Reply-To: <CALkdH=DgNmAD+yS6Qoq_rVCK_3wD=OB3Kvt9B5rzMBnG6MgYyg@mail.gmail.com>
References: <a3993c40-edef-469e-a477-23868c62f159@gmail.com>
	 <2d0e4eac41147fed7e09a05e8f9318e119dd995a.camel@cybertec.at>
	 <CALkdH=DgNmAD+yS6Qoq_rVCK_3wD=OB3Kvt9B5rzMBnG6MgYyg@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/eb95d93d9f472673e33036b5cd80518dcd06e443.camel%40cybertec.at>
Precedence: bulk

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=20
> > > more DDL statements) before applying it to the production database (e=
.g.=20
> > > for use in a CI pipeline). I'm thinking of a strategy and would like =
to=20
> > > know about its soundness.
> > >=20
> > > The general idea is:
> > >=20
> > > - you have a test database that's a clone of your production one (wit=
h=20
> > > or without data but with the schema being identical)
> > > - given the DDL statements, you open a transaction, grab its pid, and=
=20
> > > for each statement:
> > > =C2=A0=C2=A0 1. from a different "observer" connection, you read pg_l=
ocks,=20
> > > filtering locks for that pid. This is the "before" locks
> > > =C2=A0=C2=A0 2. from the first tx, you execute the statement
> > > =C2=A0=C2=A0 3. from the observer, you grab again pg_locks and comput=
e the diff=20
> > > between this and the "before" view
> > > =C2=A0=C2=A0 4. from the first tx, you rollback the transaction
> >=20
> > I think that that is a good strategy, as long as you run all DDL statem=
ents
> > in a single transaction.
>=20
> Can you elaborate on that?
>=20
> 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 sing=
le
> tx, then we should do the same. But if not, them we should follow course =
and
> execute them in separate txs.
>=20
> 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