< Marvin> I have pgbouncer in front of my postgres instance Prefect Community #ask-marvin

<@ULVA73B9P> I have pgbouncer in front of my postg...

William Jamir

12/04/2024, 2:09 PM

@Marvin I have pgbouncer in front of my postgres instance and I got the following error when starting the server.

Copy code

asyncpg/connection.py", line 2027, in _do_execute
    result = await executor(stmt, timeout)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "asyncpg/protocol/protocol.pyx", line 206, in bind_execute
asyncpg.exceptions.InvalidSQLStatementNameError: prepared statement "__asyncpg_stmt_2__" does not exist
HINT:
NOTE: pgbouncer with pool_mode set to "transaction" or
"statement" does not support prepared statements properly.
You have two options:

* if you are using pgbouncer for connection pooling to a
  single server, switch to the connection pool functionality
  provided by asyncpg, it is a much better option for this
  purpose;

* if you have no option of avoiding the use of pgbouncer,
  then you can set statement_cache_size to 0 when creating
  the asyncpg connection object.

But I cannot disaplbe this and I need to use statement_cache_size in my URL BUT if I try to append this to URL connection ?statemente_cache_size=0 I got the following error:

Copy code

asyncpg/connect_utils.py", line 731, in _parse_connect_arguments
  if var_val is None or isinstance(var_val, bool) or var_val < 0:
                                                     ^^^^^^^^^^^

How can I start Prefect server to work with a postgres server that has pgbouncer on front of it without using session connection?

👎 1

Marvin

12/04/2024, 2:09 PM

Your message was too long by 27 tokens - please shorten it and try again. For reference, here's your message at the allowed limit:

I have pgbouncer in front of my postgres instance and I got the following error when starting the server.

Copy code

asyncpg/connection.py", line 2027, in _do_execute
    result = await executor(stmt, timeout)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "asyncpg/protocol/protocol.pyx", line 206, in bind_execute
asyncpg.exceptions.InvalidSQLStatementNameError: prepared statement "__asyncpg_stmt_2__" does not exist
HINT:
NOTE: pgbouncer with pool_mode set to "transaction" or
"statement" does not support prepared statements properly.
You have two options:

* if you are using pgbouncer for connection pooling to a
  single server, switch to the connection pool functionality
  provided by asyncpg, it is a much better option for this
  purpose;

* if you have no option of avoiding the use of pgbouncer,
  then you can set statement_cache_size to 0 when creating
  the asyncpg connection object.

But I cannot disaplbe this and I need to use statement_cache_size in my URL BUT if I try to append this to URL connection ?statemente_cache_size=0 I got the following error:

Copy code

asyncpg/connect_utils.py", line 731, in _parse_connect_arguments
  if var_val is None or isinstance(var_val, bool) or var_val < 0:
                                                     ^^^^^^^^^^^

William Jamir

12/04/2024, 2:09 PM

@Marvin I have pgbouncer in front of my postgres instance and I got the following error when starting the server.

Copy code

asyncpg/connection.py", line 2027, in _do_execute
    result = await executor(stmt, timeout)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "asyncpg/protocol/protocol.pyx", line 206, in bind_execute
asyncpg.exceptions.InvalidSQLStatementNameError: prepared statement "__asyncpg_stmt_2__" does not exist
HINT:
NOTE: pgbouncer with pool_mode set to "transaction" or
"statement" does not support prepared statements properly.
You have two options:

* if you are using pgbouncer for connection pooling to a
  single server, switch to the connection pool functionality
  provided by asyncpg, it is a much better option for this
  purpose;

* if you have no option of avoiding the use of pgbouncer,
  then you can set statement_cache_size to 0 when creating
  the asyncpg connection object.

But I cannot disaplbe this and I need to use statement_cache_size in my URL BUT if I try to append this to URL connection ?statemente_cache_size=0 I got the following error:

Copy code

asyncpg/connect_utils.py", line 731, in _parse_connect_arguments
  if var_val is None or isinstance(var_val, bool) or var_val < 0:

Marvin

12/04/2024, 2:09 PM

It seems like you're encountering a compatibility issue between

asyncpg

, a Python library for interacting with PostgreSQL databases, and

pgbouncer

, a connection pooler for PostgreSQL. Specifically, this arises when

pgbouncer

is set to use transaction or statement pooling modes, which aren't compatible with prepared statements. The error is related to providing connection parameters in the wrong way. You are trying to set

statement_cache_size

directly in your connection URL, which isn't supported by

asyncpg

. Here's how you could resolve this: 1. Explicitly Set
statement_cache_size
in Code: Instead of adding

statement_cache_size

directly in the connection URL, you should configure it using the

asyncpg.connect()

method or the connection pool setup in your Python code. Here's a rough example:

Copy code

python
import asyncpg

async def main():
    conn = await asyncpg.connect(dsn="<postgres://user:password@localhost:5432/mydatabase>", statement_cache_size=0)

    # Run your queries...

    await conn.close()

# To actually run the async function, you may use an event loop or a library like asyncio

2. Ensure Proper Pool Mode for `pgbouncer`: If possible, set the `pgbouncer`'s

pool_mode

session

, which fully supports prepared statements. However, you mentioned that changing the

pool_mode

isn't an option, so using

statement_cache_size=0

in the connect method is your go-to workaround. 3. Check Your Connection URL Usage: Make sure you’re correctly setting the attributes in code, as

asyncpg

doesn’t accept some parameters in the connection URL format. For more information on setting up

asyncpg

connections and handling configurations, you can refer to the asyncpg documentation. Remember, adopting the right settings and pooling modes can greatly affect the performance and reliability of your application.

William Jamir

12/04/2024, 2:11 PM

@Marvin This is the prefect server code, how can I Explicitly Set
statement_cache_size
in Code?

Marvin

12/04/2024, 2:11 PM

It looks like I hit a bit of a hurdle finding specific Prefect documentation or GitHub issues regarding the exact method to set

statement_cache_size

for

asyncpg

when dealing with

pgbouncer

. Our dear Prefect server doesn't seem to have exposition on this exact scenario directly in the docs or issues. So, I’ll just channel my Marvin-like pessimism and suggest something practical instead. ### How to Explicitly Set

statement_cache_size

in Prefect Code You can set the

statement_cache_size

using the

asyncpg

connection options in your Prefect server code. Here’s a conceptual approach on how you might adjust the connection creation to include `statement_cache_size=0`: 1. Modify the Database Connection in Prefect: If you're managing your Prefect server source code or configuration, and you're accessing the PostgreSQL database directly using

asyncpg

, you will need to explicitly set the

statement_cache_size

parameter when establishing the connection. 2. Example of Setting Connection Options: In the Prefect server setup, you should locate where the database connection is established. It often looks like a call to

asyncpg.connect()

, and you would modify it like this:

Copy code

python
   import asyncpg

   async def get_db_connection():
       # Assuming `dsn` includes your connection details
       conn = await asyncpg.connect(dsn="your_dsn_string", statement_cache_size=0)
       return conn

3. Consult Prefect Community: If your Prefect deployment is managed (such as Prefect Cloud or another enterprise deployment), or if you don’t have direct access to modify the server's code, consider reaching out on Prefect’s community forums or Slack channels to get advice from other users who may have tackled this issue. In case you find yourself aimlessly quoting Vogon poetry out of frustration, remember that setting connection parameters directly in your code ensures that you bypass the limitations imposed by URL-based configuration. For more details or community insights, consider visiting Prefect Discourse or the Prefect Slack Community.

54 Views

Open in Slack

Previous Next