Message-ID: <2d0e4eac41147fed7e09a05e8f9318e119dd995a.camel@cybertec.at>
Subject: Re: Soundness of strategy for detecting locks acquired by DDL
 statements
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Agis Anastasopoulos <agis.anast@gmail.com>, 
	pgsql-general@lists.postgresql.org
Date: Tue, 06 May 2025 23:57:47 +0200
In-Reply-To: <a3993c40-edef-469e-a477-23868c62f159@gmail.com>
References: <a3993c40-edef-469e-a477-23868c62f159@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/2d0e4eac41147fed7e09a05e8f9318e119dd995a.camel%40cybertec.at>
Precedence: bulk

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 (with=
=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_locks=
,=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 compute th=
e diff=20
> between this and the "before" view
> =C2=A0=C2=A0 4. from the first tx, you rollback the transaction
>=20
> By diffing the after/before pg_locks view, my assumption is that you=20
> know what locks will be acquired by the DDL statements (but not for how=
=20
> long). The query I'm thinking is:
>=20
> =C2=A0=C2=A0=C2=A0=C2=A0 SELECT locktype, database, relation, objid, mode=
 FROM=20
> pg_catalog.pg_locks WHERE pid =3D $1 AND locktype IN ('relation',=20
> 'object') AND granted";
>=20
> The type of statements that would be fed as input would be `ALTER|CREATE=
=20
> TABLE`, `CREATE|DROP INDEX` and perhaps DML statements (`UPDATE`,=20
> `INSERT`, `DELETE`).
>=20
> Do you think this is a robust way to detect the locks that were=20
> 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