Skip to content

Archived: 2025-11-01 Reason: Completed - Issue resolved Status: Fix implemented and verified

Test Database Connection Issue - Fix Summary

Problem Description

When running Django tests (particularly TransactionTestCase with multi-threading), the test teardown phase would fail with:

django.db.utils.OperationalError: database "test_piquetickets" is being accessed by other users
DETAIL:  There are 2 other sessions using the database.

This error occurred during test database cleanup when Django attempted to drop the test database but found lingering database connections.

Root Cause

The issue was caused by:

  1. TransactionTestCase with Threading: Tests using ThreadPoolExecutor or concurrent operations created multiple database connections
  2. Connection Pooling: Even with CONN_MAX_AGE = 0, some connections weren't properly closed after threaded operations
  3. Background Services: Celery workers and other Docker services potentially holding connections
  4. Django's Default Behavior: Django's standard test runner doesn't forcibly terminate connections before dropping the test database

Solution

Implemented a three-layer fix:

1. Custom Test Runner (brktickets/test_runner.py)

Created ForceCleanupTestRunner that: - Extends Django's DiscoverRunner - Overrides teardown_databases() to forcibly terminate all connections to the test database before dropping it - Uses PostgreSQL's pg_terminate_backend() to kill lingering connections - Prevents the "database is being accessed by other users" error

Location: /apps/api/brktickets/test_runner.py

Key Features: 

def _force_disconnect_users(self, connection, test_db_name):
    """Forcibly disconnect all users from the test database."""
    cursor.execute(
        """
        SELECT pg_terminate_backend(pg_stat_activity.pid)
        FROM pg_stat_activity
        WHERE pg_stat_activity.datname = %s
        AND pid <> pg_backend_pid();
        """,
        [test_db_name]
    )

2. Settings Configuration Update

Added custom test runner to Django settings:

# Test configuration
# Use custom test runner to handle connection cleanup for TransactionTestCase with threading
TEST_RUNNER = 'brktickets.test_runner.ForceCleanupTestRunner'

Location: /apps/api/brktickets/settings.py:345

3. Base Test Class (Optional Enhancement)

Created CleanupTransactionTestCase for tests that need threading:

Location: /apps/api/tickets/tests/base.py

Benefits: - Ensures connections are closed in setUp(), tearDown(), and class-level setup/teardown - Clears cache to prevent cross-test pollution - Provides better cleanup even when tests fail

Usage (optional): 

from tickets.tests.base import CleanupTransactionTestCase

class MyThreadedTest(CleanupTransactionTestCase):
    def test_concurrent_operations(self):
        # Your threaded test here
        pass

Verification

All tests now pass successfully:

# Single test
docker compose exec api python manage.py test tickets.tests.test_checkout_performance.TestCheckoutPerformance.test_p2_001_concurrent_checkout_load_test

# All performance tests
docker compose exec api python manage.py test tickets.tests.test_checkout_performance

# Result: All 8 tests passed, database destroyed successfully

Additional Safeguards Already in Place

The codebase already had these preventive measures:

  1. Connection Age Settings (settings.py:338-339): 

    "CONN_MAX_AGE": 0 if 'test' in sys.argv or 'pytest' in sys.modules else 60,
"CONN_HEALTH_CHECKS": False if 'test' in sys.argv or 'pytest' in sys.modules else True,

  2. Thread-level Connection Cleanup (in test files): 

    finally:
    connections.close_all()

These were necessary but not sufficient to prevent all connection leaks.

Manual Database Cleanup (If Needed)

If you ever need to manually clean up a stuck test database:

# Terminate all connections
docker compose exec db psql -U user -d postgres -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'test_piquetickets' AND pid <> pg_backend_pid();"

# Drop the database
docker compose exec db psql -U user -d postgres -c "DROP DATABASE IF EXISTS test_piquetickets;"

Files Modified

  1. Created: /apps/api/brktickets/test_runner.py - Custom test runner with connection cleanup
  2. Modified: /apps/api/brktickets/settings.py - Added TEST_RUNNER configuration
  3. Created: /apps/api/tickets/tests/base.py - Enhanced base test class (optional)

Impact

  • ✅ All tests now complete successfully
  • ✅ Test database cleanup works reliably
  • ✅ No more "database is being accessed by other users" errors
  • ✅ Supports multi-threaded TransactionTestCase tests
  • ✅ Compatible with existing test infrastructure

Best Practices

When writing new tests that use threading or multiprocessing:

  1. Use TransactionTestCase (not TestCase)
  2. Always include connections.close_all() in thread finally blocks
  3. Consider using CleanupTransactionTestCase base class for enhanced cleanup
  4. Keep CONN_MAX_AGE = 0 for tests (already configured)

References

  • Django Issue: https://code.djangoproject.com/ticket/22414
  • PostgreSQL pg_terminate_backend: https://www.postgresql.org/docs/current/functions-admin.html
  • Django Test Runner Customization: https://docs.djangoproject.com/en/stable/topics/testing/advanced/#defining-a-test-runner