Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uCb7l-00HEWU-0c for pgsql-general@arkaria.postgresql.org; Wed, 07 May 2025 09:31:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uCb7j-00DDFa-UD for pgsql-general@arkaria.postgresql.org; Wed, 07 May 2025 09:31:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uCV9O-00BGCA-Ud for pgsql-general@lists.postgresql.org; Wed, 07 May 2025 03:08:51 +0000 Received: from mail-yb1-xb2b.google.com ([2607:f8b0:4864:20::b2b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uCV9K-000YUv-0h for pgsql-general@lists.postgresql.org; Wed, 07 May 2025 03:08:48 +0000 Received: by mail-yb1-xb2b.google.com with SMTP id 3f1490d57ef6-e7271b39a4fso826286276.3 for ; Tue, 06 May 2025 20:08:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1746587325; x=1747192125; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=PAKy2GIAL2gKxd1ugQMP6cQpRvyvfbswT9UNnpQH1Pg=; b=ah0OxHJg7+FJoJPAgX6cnIq4B6KokJC/ZZDbyN7MAsN28h5Z81AejVrFmvTugFBB1l TMnVDWE/RuOALUpUqPqGhd2ano2wZroNs29cRb1NyL+FKAhI3fCrqIOxLgeYK3F5V8v7 YM+piJRP0tt0hBJH8LCHIHb+gCbL84BDdAhgZb0ZjCHjPSvrpuxtR/0VKY1ilh0qcidN XnwC6dp7xluPjHQUmVzzVOXeVIqYjmkM4edyRhTYQFO1sVFPE+tqQY5c+xCdDoK06ND2 10zF5B+FhxDGDzEP/hs2y/iUDAGwvVfsEZDt3aQe5UMjK11hNyuEtNvCzlD3jUZt4Goa WpeA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1746587325; x=1747192125; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=PAKy2GIAL2gKxd1ugQMP6cQpRvyvfbswT9UNnpQH1Pg=; b=lAWyrAWVrGnF104pTKmOk6Vfx9ziNUL5x/sxqUc52SwrchJS4ry/9noWWK0UYAp33H i8bVUhIlWcJVUhNbDmra34DlzH+oBBsIETDfbcFz/9WGtL8W6r+bU2RNKiNmr644Q0wj r9DCF4h4RTOy4ppn2Gb9C5m3vClx5LAfTOSxTI8chU7TV13twF//lDRIU6XyzKT1SXP/ nuA0aG/UB8PahmQNL/LeTLo9wk5u0hESX66MeRt/ZNgmEfkz+L/nKI8zbr0UV7Q+wvcs X3L39KQZIC+LAU7plLbltsDVLneS3IJdvL+pCmNR+lEX1HQXuV8gt+wr3eSdHKEEEMbf cMBQ== X-Gm-Message-State: AOJu0Yxz5F5voIFezxl09p4L/47vST5ADWBmT3hAJYzUCQSySbxrBMMN T1KrD1ggsXtZTQcy1j6mDX1neG2GNdtIsab3lp4g6HyiDcPjrdG5/Ph3HcW1FM6bBsPuMHgkJOc J7mfcZXjbmLGu4xDQrcEyVg6LYTc= X-Gm-Gg: ASbGnctCoC0W1sUTzrPMuZp/0BjN28l6NoMbWYNiBpiL5ZEcuOmYj8CjdD8Y8K7UJ+5 /q0bBxKtbpuD6fUlqY3Vz+wa/T6CdI2WukBCjKciKTXmtxOI2lXvea5JIiB4cUG5kbflSj66iGh KfjxSV2DYH3EjwblRj1VzNC+U= X-Google-Smtp-Source: AGHT+IEE0WXfbcrirSfviDOnRDc10blPOc/9/jvzbATlGxb30RG8k76XC6zMmLs/YJJCMmFNnBwG7kx7LzLSiTDmACY= X-Received: by 2002:a05:690c:c:b0:708:d8b:cd24 with SMTP id 00721157ae682-70a1daf282amr9994157b3.8.1746587324802; Tue, 06 May 2025 20:08:44 -0700 (PDT) MIME-Version: 1.0 References: <2d0e4eac41147fed7e09a05e8f9318e119dd995a.camel@cybertec.at> In-Reply-To: <2d0e4eac41147fed7e09a05e8f9318e119dd995a.camel@cybertec.at> From: Agis Date: Wed, 7 May 2025 06:08:34 +0300 X-Gm-Features: ATxdqUFN0sFTU5p9ur3EyKWhpm5JQEuJtId41UNie7PcqLBy6IdGSGS-0p8d7sQ Message-ID: Subject: Re: Soundness of strategy for detecting locks acquired by DDL statements To: Laurenz Albe Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000001072b10634830d1d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001072b10634830d1d Content-Type: text/plain; charset="UTF-8" On Wed, May 7, 2025, 00:57 Laurenz Albe 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 > --0000000000001072b10634830d1d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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. o= ne 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 li= ke 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:
> =C2=A0=C2=A0 1. from a different "observer" connection, you = read pg_locks,
> 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= the diff
> between this and the "before" view
> =C2=A0=C2=A0 4. from the first tx, you rollback the transaction
>
> By diffing the after/before pg_locks view, my assumption is that you <= br> > know what locks will be acquired by the DDL statements (but not for ho= w
> long). The query I'm thinking is:
>
> =C2=A0=C2=A0=C2=A0=C2=A0 SELECT locktype, database, relation, objid, m= ode FROM
> pg_catalog.pg_locks WHERE pid =3D $1 AND locktype IN ('relation= 9;,
> 'object') AND granted";
>
> The type of statements that would be fed as input would be `ALTER|CREA= TE
> 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


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 exec= uted inside a single tx, then we should do the same. But if not, them we sh= ould follow course and execute them in separate txs.

Am I missing something?

Thanks
--0000000000001072b10634830d1d--