Phase 2.7: Advisory lock monitoring

[aesslinger]

Part of the Phase 2 set of PostgreSQL-specific features beyond the built-in driver.

Problem

When a query appears to hang, one common cause is contention on a PostgreSQL advisory lock (pg_advisory_lock), often taken explicitly by application code for coordination. There's currently no way to see held advisory locks from Tabularis — diagnosing this requires a separate psql session and manual pg_locks queries.

Proposed approach

SELECT locktype, objid, mode, granted, pid,
       (SELECT usename FROM pg_stat_activity WHERE pid = l.pid) as held_by
FROM pg_locks l WHERE locktype = 'advisory';

Security considerations

Revealing lock holders exposes information about other active sessions (who's connected, what they're doing). Gate visibility the same way pg_stat_activity itself is gated — typically requires the pg_monitor role or superuser.

Acceptance criteria

• Currently held advisory locks list with lock mode, granted status, and holding session
• Visibility is gated consistently with pg_stat_activity access, not shown to arbitrary connected roles
• No regressions in existing Phase 1 parity tests

References

• Full spec: docs/planning/03-phase-2-issue-16.md (2.7)
• Priority: Sprint 6 — lower priority, quick win