Reproduction of sqlalchemy freeze

docker-compose.yml

services:

  reproduction:
    build: .
    image: auth-ldap-pg-service:dev
    volumes:
      - ./postgres_reproduction.py:/home/auth/postgres_reproduction.py
    command: 'exec /bin/sh -c "trap : TERM INT; (while true; do sleep 1000; done) & wait"'
    stdin_open: true
    tty: true
    

Dockerfile

FROM python:3.6

ARG USER_NAME=auth
ARG GROUP_NAME=auth
ARG PORT=8005

RUN apt-get update && DEBIAN_FRONTEND=noninteractive apt-get install --yes  postgresql

# explicitly set user/group IDs
RUN set -eux; \
	groupadd -r $GROUP_NAME --gid=1005; \
	useradd  --gid $GROUP_NAME --uid=10001 --create-home $USER_NAME;
USER $USER_NAME:$GROUP_NAME
WORKDIR /home/$USER_NAME/

# Install dependencies
RUN \
    python3 -m venv venv \
    && venv/bin/pip install --upgrade pip setuptools wheel
COPY --chown=$USER_NAME:$GROUP_NAME requirements.txt requirements.txt
RUN venv/bin/pip install -r requirements.txt

Makefile

# Ease switching to podman
DOCKER = docker
DOCKER_COMPOSE = docker-compose

.PHONY:
test-reproduction:
	$(DOCKER_COMPOSE) build
	$(DOCKER_COMPOSE) run --rm reproduction venv/bin/watching_testrunner -- venv/bin/pytest --tb=short postgres_reproduction.py

postgres_reproduction.py

import contextlib
import time
import socket

import sqlalchemy as sa

from subprocess import Popen, PIPE, STDOUT, run
from pathlib import Path

import pytest

## This test is problematic, I can consistently get sqlalchemy to hang reliably 
## in this scenario without any ability to workaround
## I'm deferring this for now, as that is hopefully an error condition that we will not encounter.
## Should it become neccessary I'll come back to this. In the meantime, this becomes a bug report on sqlalchemy.
def test_reasonable_timeout_if_cannot_execute(postgres_server, postgress_server_connection_config):
    with pytest.raises(sa.exc.OperationalError), assert_timeout(5):
        with connection_form_config(postgress_server_connection_config) as connection:
            # postgres_server.kill()
            with pause_process(postgres_server):
                result = connection.execute(sa.text('select 1+1')).all()
                assert [(2,)] == result, "Couldn't establish connection"

@contextlib.contextmanager
def connection_form_config(config):
    timeout = config.get('POSTGRES_TIMEOUT', 30)
    engine = sa.create_engine(
        config["POSTGRES_URL"],
        pool_timeout=timeout,
        connect_args=dict(
            connect_timeout=timeout,
            options=f'-c lock_timeout={timeout} -c statement_timeout={timeout}',
        ),
        execution_options=dict(
            timeout=timeout,
            statement_timeout=timeout,
            query_timeout=timeout,
            execution_timeout=timeout,
        ),
        future=True,
        pool_pre_ping=True,
        echo=True,
    )
    with engine.connect() as connection:
        yield connection


@contextlib.contextmanager
def assert_timeout(max_seconds):
    start = time.perf_counter()
    try:
        yield
    finally:
        end = time.perf_counter()
        duration = end - start
        assert duration < max_seconds, f"Timeout {duration} exceded {max_seconds}"

@contextlib.contextmanager
def pause_process(process):
    run(['pkill', '-STOP', 'postgres'])
    try:
        yield process
    finally:
        run(['pkill', '-CONT', 'postgres'])


@pytest.fixture
def postgress_server_connection_config():
    return dict(
        POSTGRES_URL="postgresql://admin:admin@127.1/test?connect_timeout=10",
    )


@pytest.fixture
def postgres_server(tmp_path):
    
    postgres_dir = tmp_path / 'postgres'
    postgres_dir.mkdir()
    
    password_file = tmp_path / 'password_file'
    password_file.write_text('admin')
    
    bin = Path('/usr/lib/postgresql/13/bin/')
    result = run(
        [bin / 'initdb', '--pgdata', postgres_dir, '--username=admin', f'--pwfile={password_file}'],
    )
    
    (postgres_dir / 'postgresql.conf').touch()
    (postgres_dir / 'postgresql.auto.conf').touch()
    with Popen(
        [
            bin / 'postgres',
            '-D', postgres_dir,
            '-k', postgres_dir,
            '-h', '127.1',
        ],
    ) as server:
        wait_for_port(host='127.1', port=5432, timeout=10)

        result = run(
            [bin / 'createdb', 'test'],
            env=dict(PGPASSWORD='admin', PGUSER='admin', PGHOST='127.1', PGPORT='5432')
        )

        try:
            yield server
        finally:
            server.kill()

def wait_for_port(port, host='localhost', timeout=5.0):
    """Wait until a port starts accepting TCP connections.
    Args:
        port (int): Port number.
        host (str): Host address on which the port should exist.
        timeout (float): In seconds. How long to wait before raising errors.
    Raises:
        TimeoutError: The port isn't accepting connection after time specified in `timeout`.
    """
    start_time = time.perf_counter()
    while True:
        try:
            with socket.create_connection((host, port), timeout=timeout):
                break
        except OSError as ex:
            time.sleep(0.01)
            if time.perf_counter() - start_time >= timeout:
                raise TimeoutError('Waited too long for the port {} on host {} to start accepting '
                                   'connections.'.format(port, host)) from ex

requirements.txt

sqlalchemy==1.4.32
watching_testrunner
pytest
psycopg2