How to Resolve Issue with Prepared Statements: 'Statement Already Exists' on Server Deployment

[akanksha314e]

When deploying my code using psqlpy on the server, I am encountering an intermittent issue where queries fail with the error cannot prepare statement: statement already exists. However, the same APIs work fine in the local environment. After a few retries, the queries run as expected.

I have tried the following to troubleshoot the issue:

• Disabled caching by removing the @alru_cache decorator.
• Set the prepared parameter to False when executing queries.

Despite these changes, the issue persists. The problem appears intermittently, failing a couple of times and then succeeding.

Code Context: The code executes queries using psqlpy and JinjaSQL for preparing the queries from templates. Here's the relevant part of the code:

• DBManager is initialized and manages database queries.
• Prepared Statements are used in the form of queries generated from the Jinja2 templates.

sample code :
`@asynccontextmanager
async def get_db(pg_dsn: str) -> AsyncGenerator[Connection, None]:

db_pool = ConnectionPool(dsn=pg_dsn)
conn = await db_pool.connection()
try:
    yield conn
finally:
    db_pool.close()

`

[chandr-andr]

@akanksha314e Hello!

Could you please add more details about your PostgreSQL setup on the server? Maybe you have PGBouncer and/or Patroni?

[chandr-andr]

It looks like you have some custom setup on the server.
And add more examples of how you execute your queries, please.

[akanksha314e]

The queries are being executed like this:

     # Load the SQL template from the JinjaSQL environment
         template = self.jsql.env.get_template(sqlfile)
        # Prepare the query and parameters using the JinjaSQL template
        query, values = self.jsql._prepare_query(template, data=kwargs)
        try:
            async with get_db(pg_dsn=self.pg_dsn) as conn:
                async with conn.transaction():
                    result = await conn.execute(query, values, prepared=False)

[chandr-andr]

@akanksha314e What about PostgreSQL setup? I've checked right now, and if you specify prepared=False, the query won't be prepared

[akanksha314e]

Hello @chandr-andr ,

We have identified the cause of the issue. It occurs specifically when we use the Supavisor DB link instead of directly connecting to the PostgreSQL server. Is there any issue with using connection pooler in general or any configuration you know which need to be done? We have been using asyncpg with supavisor and not encountered this issue before.
https://github.com/supabase/supavisor

[chandr-andr]

@akanksha314e Hello!

There are some problems with the external connection pool.
I haven't used Supavisor DB but I believe it's the same (approximately) with PGBouncer, we have some info in the documentation: https://psqlpy-python.github.io/introduction/introduction.html#important-notes.

I recommend checking again if you have queries with prepared=True and change them to prepared=False OR change pool mode in Supavisor DB in (doc) to Session Mode

[akanksha314e]

Hello @chandr-andr,

Thank you for the suggestion. I tried using prepared=False with Transaction Mode, but unfortunately, the same error persists. However, it works correctly when using prepared=True in Session Mode.

[chandr-andr]

@akanksha314e It's really strange, are you sure you set prepared=False everywhere? It must be good with Transaction Mode.

[akanksha314e]

@chandr-andr Yes, I have ensured that prepared=False is set everywhere in the code where queries are executed.

[chandr-andr]

Okay, I'll deploy Supavisor DB and check PSQLPy with it.

[chandr-andr]

@akanksha314e Could you please tell me the version of Supavisor DB and maybe you have a configuration file to reproduce the problem?

[akanksha314e]

`apiVersion: apps/v1
kind: Deployment
metadata:
  name: supavisor-cluster-ha
  namespace: supavisor
  labels:
    app: supavisor-cluster-ha
spec:
  replicas: 1
  selector:
    matchLabels:
      cdk8s.io/metadata.addr: supavisor-label
  template:
    metadata:
      labels:
        cdk8s.io/metadata.addr: supavisor-label
    spec:
      containers:
        - name: supavisor-cluster-ha
          image: supabase/supavisor:1.1.68 # image tag of supavisor
          command:
            - /bin/sh
          args:
            - '-c'
            - /app/bin/migrate && /app/bin/server
          ports:
            - name: api
              containerPort: 4000
              protocol: TCP
            - name: transaction
              containerPort: 6543
              protocol: TCP
          env:
            - name: PORT
              value: '4000'
            - name: PROXY_PORT_SESSION
              value: '5432'
            - name: PROXY_PORT_TRANSACTION
              value: '6543'
            - name: DATABASE_URL
              value: >-
                postgresql://username:[email protected]/postgres
            - name: CLUSTER_POSTGRES
              value: 'true'
            - name: SECRET_KEY_BASE
              value: >-
                test_value
            - name: VAULT_ENC_KEY
              value: 'secret'
            - name: API_JWT_SECRET
              value: secret
            - name: METRICS_JWT_SECRET
              value: secret
            - name: REGION
              value: local
            - name: ERL_AFLAGS
              value: '-proto_dist inet_tcp'
          resources:
            limits:
              cpu: 1500m
              memory: 2Gi
            requests:
              cpu: '1'
              memory: 512Mi
          terminationMessagePath: /dev/termination-log
          terminationMessagePolicy: File
          imagePullPolicy: Always
          securityContext:
            privileged: false
            runAsNonRoot: false
            readOnlyRootFilesystem: false
            allowPrivilegeEscalation: false
      restartPolicy: Always
      terminationGracePeriodSeconds: 30
      dnsPolicy: ClusterFirst
      nodeSelector:
        app: label
      automountServiceAccountToken: false
      securityContext:
        runAsNonRoot: false
        fsGroupChangePolicy: Always
      schedulerName: default-scheduler
      setHostnameAsFQDN: false
  strategy:
    type: RollingUpdate
    rollingUpdate:
      maxUnavailable: 25%
      maxSurge: 25%
  revisionHistoryLimit: 10
  progressDeadlineSeconds: 600
---
apiVersion: v1
kind: Service
metadata:
  name: supavisor-cluster-ha
  namespace: supavisor
spec:
  ports:
    - name: api
      protocol: TCP
      port: 4000
      targetPort: 4000
    - name: transaction
      protocol: TCP
      port: 6543
      targetPort: 6543
  selector:
    label.io/metadata.addr: supavisor-label
  type: ClusterIP`

[chandr-andr]

@akanksha314e Thanks, will check soon

[chandr-andr]

@akanksha314e One more thing which I've missed it
You wrote that you have something like this:

@asynccontextmanager
async def get_db(pg_dsn: str) -> AsyncGenerator[Connection, None]:
    db_pool = ConnectionPool(dsn=pg_dsn)
    conn = await db_pool.connection()
    try:
        yield conn
    finally:
        db_pool.close()

Each time you call the get_db context manager you recreate ConnectionPool which is very bad, please make db_pool = ConnectionPool(dsn=pg_dsn) global or add it to some state.
In general, ConnectionPool must be created only once at the start of the application.

[akanksha314e]

Hi, @chandr-andr is there any update on using transaction mode with Supavisor DB.

[akanksha314e]

Hi @chandr-andr is there a way to use transaction mode with Supavisor DB currently supported by psqlpy?

[chandr-andr]

@akanksha314e Hello!
Sorry for not getting back to you sooner, I had a lot of things to do.

I've tested psqlpy with Supavisor DB and cannot reproduce your problem, I'll try one more time when I have time.

Did you try to change your get_db asynccontextmanager as I wrote before #122 (comment)?

[akanksha314e]

Hi @chandr-andr even with the global declaration the issue persists.

Try executing the same query multiple times using a Supavisor connection. Sometimes its getting executed for the first time but fails when tried multiple times. Here's a sample code snippet that demonstrates the issue:

PG_DSN = ""

db_pool = ConnectionPool(dsn=PG_DSN, max_db_pool_size=2)

async def run_query(query: str):
    async with db_pool.acquire() as conn:  # Acquire a new connection per query
        try:
            result = await conn.execute(query,prepared=False)
            return result.result()
        except Exception as e:
            print(f"Error executing query: {e}")

async def main():
    queries = [
        "SELECT * FROM TABLE",
        "SELECT * FROM TABLE",
        "SELECT * FROM TABLE"
    ]

    results = await asyncio.gather(*(run_query(q) for q in queries))

    for i, res in enumerate(results):
        print(f"Query {i+1} result:", res)

    db_pool.close()

asyncio.run(main())

Following is the error log :
Connection execute error: Cannot execute statement, error - db error: ERROR: prepared statement "s1" does not exist.

[chandr-andr]

@akanksha314e Thanks! I'll have time to continue researching this problem only on weekends. I'll come back here on Saturday/Sunday

[akanksha314e]

@chandr-andr Sure, thanks.

[akanksha314e]

Hi, @chandr-andr Is there any update on this issue ?

[chandr-andr]

@akanksha314e Hello!
I've reproduced the problem and found a problem. PSQLPy by default will prepare all statements because we need to get type information from Postgres.
I'll make sure to fix this. It will take some time though.

Thanks for finding this problem! I'll text u when it's fixed. For now, just use session mode in supavisor - it will be fine.

[akanksha314e]

Hi @chandr-andr I understand you may have a lot on your plate, but I was wondering if there’s any update or if this issue is planned for an upcoming release.

If it might take some time, would you be open to me contributing a fix? If so, could you please point me to the relevant file(s) or logic where the changes would need to be made? I’d be happy to open a PR for your review.

[chandr-andr]

@akanksha314e Hello!

Just yesterday, I found how to fix it completely and started implementing it here - #131

I have non-working days since Thursday, so by the end of the week, there will be a release that fixes your problem and I think some interesting things also.

[akanksha314e]

@chandr-andr Great, Thanks.

[chandr-andr]

@akanksha314e Hello!

Here are some updates for you: I've supported external connection pools like PgBouncer and SupaVisor.
Now I need to make more code changes to provide a more usable way of configuring prepared statements.

So, the new release will be next week.

For now, you can build psqlpy locally from this branch and test it.

[chandr-andr]

@akanksha314e Hello!

There is a new release https://github.com/psqlpy-python/psqlpy/releases/tag/0.10.0

Please read the documentation related to external connection pools: https://psqlpy-python.github.io/external_connection_pools.html

I'm rly waiting for u feedback, thank u!

[chandr-andr]

@akanksha314e Hello! Did you test it already?