Psycopg 3 & SQLAlchemy 2: what would cause DuplicatePreparedStatement in normal operation?

346 Views Asked by At

We are using SQLAlchemy in our application to interact with a TimescaleDB database with Psycopg as the database driver.

Recently we started seeing this error every time the application tries to select from the database:

psycopg.errors.DuplicatePreparedStatement: prepared statement "_pg3_3" already exists

No change to the application was deployed, and overall database load is very low.

Apparently there is something wrong in our application, but I am not an expert in any of these technologies. I can identify when the errors first appeared in our logs, but I cannot identify any change in activity or behavior that might point to a root cause.

My questions are:

  1. What might cause this to happen during otherwise-normal operation, not under heavy load? Is this possibly a bug in one of the underlying libraries?
  2. What is the recommended way to remediate this without significant downtime? Do I need to manually log in as an admin user and DEALLOCATE this _pg_3 prepared statement? Do I need to restart the Python app workers?
  3. What should be done in order to prevent this from happening again?

The relevant code looks something like this:

from datetime import datetime as Datetime
from typing import Any

from sqlalchemy import TIMESTAMP, select
from sqlalchemy.ext.asyncio import AsyncSession as BaseAsyncSession, async_sessionmaker, create_async_engine
from sqlalchemy.orm import DeclarativeBase

from ..config import settings


class Base(DeclarativeBase):
    type_annotation_map = {
        Datetime: TIMESTAMP(timezone=True),
    }


async_engine = create_async_engine(
    settings.SQLALCHEMY_DATABASE_URI,
    isolation_level="READ COMMITTED",
    pool_pre_ping=True,
    pool_size=20,
    pool_recycle=3600,
    max_overflow=5,
)


AsyncSession = async_sessionmaker(async_engine, autobegin=False)


class SensorRecord(Base):
    __tablename__ = "sensor_records"

    ...  # attributes here


async def fetch_prev_record(session: BaseAsyncSession, ...):
    query = select(SensorRecord)
    return ...  # more SQL and logic here

Update: I ended up simply re-deploying the Python application with a blue/green deployment. This seems to have fixed the problem, presumably by causing all connection sessions to be refreshed and thereby dropping these "stuck" prepared statements. However I want to leave this question open because I want to understand the actual cause, to prevent this from happening again.

0

There are 0 best solutions below