docs(db): clarify migration transaction and recovery comments #874

Summary

Clarify the migration transaction comments without changing runtime migration behavior.

Current behaviour

migration_transactional_test.go:3-4 says the driver wraps each file in BEGIN/COMMIT. The pinned golang-migrate v4.19.1 pgx/v5 driver actually reads the file and calls ExecContext; the configured pgx path uses simple protocol for this no-argument call. Atomicity of an ordinary multi-statement file follows PostgreSQL's implicit transaction semantics. The same inaccurate wrapper explanation appears in migrations 000109 and 000111. The guard also describes recovery differently from the current fail-loud dirty-schema behavior.

This does not invalidate the guard's policy of keeping non-transactional operations out of request-time automatic migrations. A single concurrent index operation being technically executable does not establish a safe request-time cancellation/recovery contract.

Steps to verify

1. Trace migrate_cancel.go:118-142 into golang-migrate v4.19.1 database/pgx/v5/pgx.go:252-283.
2. Compare pgx v5.9.2 conn.go:515-517 and PostgreSQL simple-query implicit transaction semantics.
3. Compare migration_transactional_test.go:1-31, migrations 000109:22-26 and 000111:21-25 with migrate.go:348-430 and the dirty-state regression tests.

Expected behaviour / proposed fix

Correct the explanatory comments to distinguish driver execution, PostgreSQL file atomicity, the non-transactional-operation policy and manual dirty-state recovery. Verify any referenced deploy-runner issue actually exists. Preserve the static guard and runtime behavior; broader non-transactional migration support needs its own reviewed design.

References

Found while independently triaging PR #821 at eb9c3a7, closing issue #806. Both native migration round-trip verification and required CI passed on that head. No runtime defect is asserted by this documentation issue.

Severity: low. Impact: internal maintainers. Priority: P3.