public inbox for [email protected]
help / color / mirror / Atom feedRe: Soundness of strategy for detecting locks acquired by DDL statements
3+ messages / 2 participants
[nested] [flat]
* Re: Soundness of strategy for detecting locks acquired by DDL statements
@ 2025-05-06 21:57 Laurenz Albe <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2025-05-06 21:57 UTC (permalink / raw)
To: Agis Anastasopoulos <[email protected]>; [email protected]
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] 3+ messages in thread
* Re: Soundness of strategy for detecting locks acquired by DDL statements
@ 2025-05-07 03:08 Agis <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Agis @ 2025-05-07 03:08 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: [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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Soundness of strategy for detecting locks acquired by DDL statements
@ 2025-05-07 05:27 Laurenz Albe <[email protected]>
parent: Agis <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2025-05-07 05:27 UTC (permalink / raw)
To: Agis <[email protected]>; +Cc: [email protected]
On Wed, 2025-05-07 at 06:08 +0300, Agis wrote:
> 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
> >
> > 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] 3+ messages in thread
end of thread, other threads:[~2025-05-07 05:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-05-06 21:57 Re: Soundness of strategy for detecting locks acquired by DDL statements Laurenz Albe <[email protected]>
2025-05-07 03:08 ` Agis <[email protected]>
2025-05-07 05:27 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox