golang-migrate / migrate / 22585591733

55%

master: 54%

Pull #1364

github

Add x-use-transaction mode to postgres driver for pg-proxy compatibility

Session-level advisory locks (pg_advisory_lock) are not supported by
pg-proxies such as Orgstore's pg-proxy, which route each statement to a
potentially different backend connection. This makes it impossible to
guarantee that Lock and Unlock execute on the same connection.

Add a new x-use-transaction URL option (UseTransaction config field) that
switches the driver to transaction-scoped advisory locks
(pg_advisory_xact_lock). In this mode, a single transaction spans the
advisory lock acquisition, migration SQL, and version table update, with
the lock released automatically on commit or rollback. This is compatible
with pg-proxy deployments since all statements are issued within one
transaction on a single connection.

Key changes:
- Add withExistingOrNewTxRollbackOnErr to manage a shared *sql.Tx,
  including proper rollback and cleanup of p.tx on error
- Add executeExecContext, executeQueryContext, executeQueryRowContext
  wrappers that route through the shared transaction when enabled
- Update Lock/Unlock, SetVersion, Run, Drop, and ensureVersionTable to
  use the new wrappers
- Fix transaction leak when SET LOCAL statement_timeout fails on startup
- Ensure p.tx is cleared after rollback so subsequent calls start fresh
- Return nil from Unlock when the transaction is already done (rolled
  back by a failed migration), since the lock is already released
- Update dktesting cleanup client to use API version negotiation
- Document the feature and its limitations (CONCURRENTLY, VACUUM, etc.)

Co-Authored-By: Claude Sonnet 4.6 (1M context) <noreply@anthropic.com>

65 of 82 new or added lines in 2 files covered. (79.27%)

4435 of 8116 relevant lines covered (54.65%)

51.57 hits per line