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:
- 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?
- 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? - 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.