Unsupported parameter "jit" when using postgres with pgbouncer

[bakahoui]

Hi Prefect team,

we are using prefect server succefully with a PostgreSQL database v12.20. Right now we would like to upgrade our database to version v16.04. The data migration was done without errors. But during perfect server restart, the error below occurs. Even upgrading prefect (and its transitive dependecies) to the latest version didn't solve the problem!

Our setup (dependecies are controlled by poetry):

python = "3.11.9"
prefect = "2.18.3"
google-cloud-storage = "2.18.1"
psycopg2-binary = "2.9.9"
sqlalchemy = "2.0.32"
asyncpg = "0.29.0"

Thanks a lot for any hint !

Error trace:
File "/srv/venv/prefect-server/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 146, in init
Sep 03 16:35:43 mlserver prefect[18538]: self._dbapi_connection = engine.raw_connection()
Sep 03 16:35:43 mlserver prefect[18538]: ^^^^^^^^^^^^^^^^^^^^^^^
Sep 03 16:35:43 mlserver prefect[18538]: File "/srv/venv/prefect-server/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3302, in raw_connection
Sep 03 16:35:43 mlserver prefect[18538]: return self.pool.connect()
Sep 03 16:35:43 mlserver prefect[18538]: ^^^^^^^^^^^^^^^^^^^
Sep 03 16:35:43 mlserver prefect[18538]: File "/srv/venv/prefect-server/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 449, in connect
Sep 03 16:35:43 mlserver prefect[18538]: return _ConnectionFairy._checkout(self)
Sep 03 16:35:43 mlserver prefect[18538]: ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Sep 03 16:35:43 mlserver prefect[18538]: File "/srv/venv/prefect-server/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
Sep 03 16:35:43 mlserver prefect[18538]: fairy = _ConnectionRecord.checkout(pool)
Sep 03 16:35:43 mlserver prefect[18538]: ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Sep 03 16:35:43 mlserver prefect[18538]: File "/srv/venv/prefect-server/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
Sep 03 16:35:43 mlserver prefect[18538]: rec = pool._do_get()
Sep 03 16:35:43 mlserver prefect[18538]: ^^^^^^^^^^^^^^
Sep 03 16:35:43 mlserver prefect[18538]: File "/srv/venv/prefect-server/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 179, in _do_get
Sep 03 16:35:43 mlserver prefect[18538]: with util.safe_reraise():
Sep 03 16:35:43 mlserver prefect[18538]: File "/srv/venv/prefect-server/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in exit
Sep 03 16:35:43 mlserver prefect[18538]: raise exc_value.with_traceback(exc_tb)
Sep 03 16:35:43 mlserver prefect[18538]: File "/srv/venv/prefect-server/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 177, in _do_get
Sep 03 16:35:43 mlserver prefect[18538]: return self._create_connection()
Sep 03 16:35:43 mlserver prefect[18538]: ^^^^^^^^^^^^^^^^^^^^^^^^^
Sep 03 16:35:43 mlserver prefect[18538]: File "/srv/venv/prefect-server/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
Sep 03 16:35:43 mlserver prefect[18538]: return _ConnectionRecord(self)
Sep 03 16:35:43 mlserver prefect[18538]: File "/srv/venv/prefect-server/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 674, in init
Sep 03 16:35:43 mlserver prefect[18538]: self.__connect()
Sep 03 16:35:43 mlserver prefect[18538]: File "/srv/venv/prefect-server/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 900, in __connect
Sep 03 16:35:43 mlserver prefect[18538]: with util.safe_reraise():
Sep 03 16:35:43 mlserver prefect[18538]: File "/srv/venv/prefect-server/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in exit
Sep 03 16:35:43 mlserver prefect[18538]: raise exc_value.with_traceback(exc_tb)
Sep 03 16:35:43 mlserver prefect[18538]: File "/srv/venv/prefect-server/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 896, in __connect
Sep 03 16:35:43 mlserver prefect[18538]: self.dbapi_connection = connection = pool._invoke_creator(self)
Sep 03 16:35:43 mlserver prefect[18538]: ^^^^^^^^^^^^^^^^^^^^^^^^^^
Sep 03 16:35:43 mlserver prefect[18538]: File "/srv/venv/prefect-server/lib/python3.11/site-packages/sqlalchemy/engine/create.py", line 643, in connect
Sep 03 16:35:43 mlserver prefect[18538]: return dialect.connect(*cargs, **cparams)
Sep 03 16:35:43 mlserver prefect[18538]: ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Sep 03 16:35:43 mlserver prefect[18538]: File "/srv/venv/prefect-server/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 621, in connect
Sep 03 16:35:43 mlserver prefect[18538]: return self.loaded_dbapi.connect(*cargs, **cparams)
Sep 03 16:35:43 mlserver prefect[18538]: ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Sep 03 16:35:43 mlserver prefect[18538]: File "/srv/venv/prefect-server/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 932, in connect
Sep 03 16:35:43 mlserver prefect[18538]: await_only(creator_fn(*arg, **kw)),
Sep 03 16:35:43 mlserver prefect[18538]: ^^^^^^^^^^^^^^^^^^^^^^
Sep 03 16:35:43 mlserver prefect[18538]: TypeError: connect() got an unexpected keyword argument 'jit'
Sep 03 16:35:43 mlserver prefect[18538]: Application startup failed. Exiting.
Sep 03 16:35:44 mlserver prefect[18538]: Server stopped!

[cicdw]

We've seen this issue when using PGBouncer (and I suggested a fix for that situation here). As far as I'm aware, PostgreSQL still supports this configuration option (e.g., https://www.postgresql.org/docs/16/jit-decision.html).

Are you using some sort of load balancer or anything in front of PSQL that might cause this?

[bakahoui]

hi @cicdw
i have to correct myself. With a local postgres v.16.4 everything works fine! The problem is occuring only with a remote database!
I am not sure if any component (like a loadbalancer) is placed in front of the db! I ll ask our DB team. but just for my understanding: why do you think that it might have something to do with a such component ?

[cicdw]

That exact error is what happens currently when you open a connection through PGBouncer, which is a postgresql load balancer, so I'm just assuming that's what's happening to you as well. Because load balancers manage connection lifecycles, they have a different configuration interface and sometimes don't support the same fields that the underlying databases do.

[bakahoui]

thanks for the answer!
in fact, in our company we have a pgboucer placed in front of the Postgresql cluster. I have now found out from our DB team, and also that it is definitely not possible to change the configuration of the pgbouncer, as this would have an impact on the entire PG cluster where databases for multiple teams are hosted...
so i have 2 questions:

1. Does this mean now we have no way to get around this problem without adjusting the pgbouncer, because this is simply not possible?
2. does it not make sense to offer this jit related adjustment on the prefect side, so that you can, for example, use a prefect config parameter to set whether jit can be used or not? as one cannot always assume that the prefect users are also owners of external libraries (such as pgbouncer) or even of the postgres databases...

thx

[cicdw]

@bakahoui no worries we won't leave you hanging like that, we can expose this as a setting! Let's mark this discussion as resolved and I'll post an update in the issue that we'll add this.