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
