pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedSoundness of strategy for detecting locks acquired by DDL statements
6+ messages / 5 participants
[nested] [flat]
* Soundness of strategy for detecting locks acquired by DDL statements
@ 2025-05-06 09:06 Agis Anastasopoulos <agis.anast@gmail.com>
2025-05-06 18:31 ` Re: Soundness of strategy for detecting locks acquired by DDL statements Adrian Klaver <adrian.klaver@aklaver.com>
2025-05-06 18:58 ` Re: Soundness of strategy for detecting locks acquired by DDL statements Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
2025-05-06 21:57 ` Re: Soundness of strategy for detecting locks acquired by DDL statements Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 3 replies; 6+ messages in thread
From: Agis Anastasopoulos @ 2025-05-06 09:06 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hello! 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?
Thanks in advance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Soundness of strategy for detecting locks acquired by DDL statements
2025-05-06 09:06 Soundness of strategy for detecting locks acquired by DDL statements Agis Anastasopoulos <agis.anast@gmail.com>
@ 2025-05-06 18:31 ` Adrian Klaver <adrian.klaver@aklaver.com>
2 siblings, 0 replies; 6+ messages in thread
From: Adrian Klaver @ 2025-05-06 18:31 UTC (permalink / raw)
To: Agis Anastasopoulos <agis.anast@gmail.com>; pgsql-general@lists.postgresql.org
On 5/6/25 2:06 AM, Agis Anastasopoulos wrote:
> Hello! 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.
> Do you think this is a robust way to detect the locks that were
> acquired? Are there any caveats/drawbacks/flaws in this strategy?
Why not just read this?:
https://www.postgresql.org/docs/current/explicit-locking.html
>
> Thanks in advance
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Soundness of strategy for detecting locks acquired by DDL statements
2025-05-06 09:06 Soundness of strategy for detecting locks acquired by DDL statements Agis Anastasopoulos <agis.anast@gmail.com>
@ 2025-05-06 18:58 ` Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
2 siblings, 0 replies; 6+ messages in thread
From: Achilleas Mantzios @ 2025-05-06 18:58 UTC (permalink / raw)
To: Agis Anastasopoulos <agis.anast@gmail.com>; pgsql-general@lists.postgresql.org
On 6/5/25 12:06, Agis Anastasopoulos wrote:
> Hello! 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?
>
Why consume your time in a guessing game, instead of reading the actual
docs? In addition to the doc that Adrian suggested, there are the
individual docs for each DDL, that list exactly the locks acquired. You
may categorize those and think of he estimated traffic of conflicting
DML statements from the app. Another more brute force method is to
attempt the schema changes while replaying the actual production traffic
or something that looks like this. Example : you prepare a logical
replicated subscriber that gets all the changes from the production, and
attempt the schema migration there : beware to religiously monitor your
space and any replication breakage, although all those issues are not
show stoppers in pgsql 16+ IIRC.
PS
I am happy to see more Greeks in the lists! How about creating some user
group sometime !
> Thanks in advance
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Soundness of strategy for detecting locks acquired by DDL statements
2025-05-06 09:06 Soundness of strategy for detecting locks acquired by DDL statements Agis Anastasopoulos <agis.anast@gmail.com>
@ 2025-05-06 21:57 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-05-07 03:08 ` Re: Soundness of strategy for detecting locks acquired by DDL statements Agis <agis.anast@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2025-05-06 21:57 UTC (permalink / raw)
To: Agis Anastasopoulos <agis.anast@gmail.com>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Soundness of strategy for detecting locks acquired by DDL statements
2025-05-06 09:06 Soundness of strategy for detecting locks acquired by DDL statements Agis Anastasopoulos <agis.anast@gmail.com>
2025-05-06 21:57 ` Re: Soundness of strategy for detecting locks acquired by DDL statements Laurenz Albe <laurenz.albe@cybertec.at>
@ 2025-05-07 03:08 ` Agis <agis.anast@gmail.com>
2025-05-07 05:27 ` Re: Soundness of strategy for detecting locks acquired by DDL statements Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 6+ messages in thread
From: Agis @ 2025-05-07 03:08 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-general@lists.postgresql.org
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
> > 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Soundness of strategy for detecting locks acquired by DDL statements
2025-05-06 09:06 Soundness of strategy for detecting locks acquired by DDL statements Agis Anastasopoulos <agis.anast@gmail.com>
2025-05-06 21:57 ` Re: Soundness of strategy for detecting locks acquired by DDL statements Laurenz Albe <laurenz.albe@cybertec.at>
2025-05-07 03:08 ` Re: Soundness of strategy for detecting locks acquired by DDL statements Agis <agis.anast@gmail.com>
@ 2025-05-07 05:27 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 6+ messages in thread
From: Laurenz Albe @ 2025-05-07 05:27 UTC (permalink / raw)
To: Agis <agis.anast@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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
> > > 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-05-07 05:27 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-05-06 09:06 Soundness of strategy for detecting locks acquired by DDL statements Agis Anastasopoulos <agis.anast@gmail.com>
2025-05-06 18:31 ` Adrian Klaver <adrian.klaver@aklaver.com>
2025-05-06 18:58 ` Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
2025-05-06 21:57 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-05-07 03:08 ` Agis <agis.anast@gmail.com>
2025-05-07 05:27 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox