vatsrahul1001 opened a new pull request, #76976: URL: https://github.com/apache/airflow/pull/76976
`airflow db migrate` can hang forever on Postgres with psycopg3 when an external DB manager runs a migration that uses `CREATE INDEX CONCURRENTLY`. `_create_global_lock_postgresql` opens its own connection, runs `SELECT pg_advisory_lock(:id)` and leaves the connection idle in transaction for the whole migration. With psycopg2 the parameter is inlined and the query's snapshot is released when it finishes. psycopg3 uses the extended protocol, so the snapshot stays registered until the next statement or the end of the transaction. `CREATE INDEX CONCURRENTLY` waits for every older snapshot, including that one, and the lock connection only moves on after the migration finishes, so the process waits on itself. The earlier `work_session.rollback()` in `_run_upgradedb` only ends the work session's transaction, not the lock connection's. The fix opens the lock connection in AUTOCOMMIT, as the MySQL path already does. `pg_advisory_lock` and `set_config(..., false)` are session-level, so the lock and `lock_timeout` behave as before. A connection passed in by the caller is left as is, so a caller's transaction is never committed. The docstring is corrected too: these are session-level advisory locks, not transactional ones. We hit this when upgrading a provider whose DB manager adds an index concurrently on an existing 3.4 deployment. A fresh database goes through `initdb` and isn't affected. The new Postgres test holds the migration lock and runs `CREATE INDEX CONCURRENTLY` on another connection with a statement timeout. On psycopg3 it fails with `canceling statement due to statement timeout` without the fix and passes with it. This should be backported to v3-4-test. --- ##### Was generative AI tooling used to co-author this PR? - [X] Yes — Claude Code (Claude Opus 5.5) Generated-by: Claude Code (Claude Opus 5.5) following [the guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#gen-ai-assisted-contributions) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
