Fixes#6201

This PR migrates the Coder codebase from thelib/pq PostgreSQL driver topgx to address data race issues with context cancellation and improve overall database performance.

Problem

Thelib/pq library has a longstanding issue with data races during context cancellation (lib/pq#1021), which can cause instability in applications that heavily use contexts like Coder. Additionally,lib/pq is in maintenance mode and not actively developed.

Solution

Migrate topgx, which:

• Has better context cancellation handling (fixes the data race)
• Is actively maintained with regular updates
• Provides better performance and more PostgreSQL features
• Has improved error handling and logging

Changes Made

Core Database Layer

• Error Handling: Updated all error handling frompq.Error topgconn.PgError
• Error Codes: Migrated from named error codes to PostgreSQL numeric codes (e.g., "23505" for unique_violation)
• Array Handling: Replacedpq.Array() calls with direct Go slice usage (pgx handles this automatically)

LISTEN/NOTIFY (PubSub)

• Complete Rewrite: Replacedpq.Listener with custompgxListenerShim usingpgx.Conn.WaitForNotification()
• Notification Fields: Updated field names fromExtra toPayload forpgconn.Notification
• Connection Management: Simplified connection handling using pgx's built-in connection management

Dependencies

• Added:github.com/jackc/pgx/v5 andgithub.com/jackc/pgx/v5/stdlib
• Removed:github.com/lib/pq dependency and custom replace directive
• Driver Registration: Updated to usegithub.com/jackc/pgx/v5/stdlib fordatabase/sql compatibility

Testing

• ✅ All database packages build successfully
• ✅ PubSub LISTEN/NOTIFY functionality migrated and working
• ✅ Error handling properly converted to pgx error types
• ✅ Driver registration working correctly

Benefits

1. Fixes Data Race: Resolves the context cancellation data race issue mentioned inConsider migrating away fromlib/pq due do data race in context cancellation #6201
2. Better Performance: pgx is generally faster than lib/pq
3. Active Development: pgx is actively maintained vs lib/pq in maintenance mode
4. Enhanced Features: Better PostgreSQL feature support and error handling
5. Future-Proof: Ensures Coder stays on a supported, evolving database driver

Migration Notes

This is a significant but necessary change that touches the core database layer. The migration maintains full compatibility with existing database operations while providing the benefits of the more modern pgx driver.

The most complex part was migrating the pubsub LISTEN/NOTIFY implementation, which required a complete rewrite due to the different APIs between lib/pq and pgx. The new implementation is simpler and more robust.