postgres engine default keepalives_idle value causes setsockopt(TCP_KEEPIDLE) invalid value #750
Description
Reprex:
- Fresh
sqlmesh init - Modify
config.yamlto usepostgres
connections:
local:
type: postgres
host: ***
port: ***
user: ***
password: ***
database: ***
default_connection: local
To be precise, I'm using a local running postgres15 Docker container, which I can successfully connect to via psql on Linux.
- Run
sqlmesh plan - Following error comes up
Traceback (most recent call last):
File "/home/ttzhou/.pyenv/versions/sqlmesh/bin/sqlmesh", line 8, in <module>
sys.exit(cli())
^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/click/core.py", line 1130, in __call__
return self.main(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/click/core.py", line 1055, in main
rv = self.invoke(ctx)
^^^^^^^^^^^^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/click/core.py", line 1657, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/click/core.py", line 1404, in invoke
return ctx.invoke(self.callback, **ctx.params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/click/core.py", line 760, in invoke
return __callback(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/click/decorators.py", line 26, in new_func
return f(get_current_context(), *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/sqlmesh/cli/__init__.py", line 19, in wrapper
return func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/sqlmesh/cli/main.py", line 225, in plan
context.plan(environment, restate_models=restate_models, **kwargs)
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/sqlmesh/core/context.py", line 634, in plan
context_diff=self._context_diff(environment or c.PROD, create_from=create_from),
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/sqlmesh/core/context.py", line 880, in _context_diff
snapshots=snapshots or self.snapshots,
^^^^^^^^^^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/sqlmesh/core/context.py", line 417, in snapshots
prod = self.state_reader.get_environment(c.PROD)
^^^^^^^^^^^^^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/sqlmesh/core/context.py", line 327, in state_reader
self._state_reader = self.state_sync
^^^^^^^^^^^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/sqlmesh/core/context.py", line 319, in state_sync
if self._state_sync.get_versions(validate=False).schema_version == 0:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/sqlmesh/core/state_sync/base.py", line 208, in get_versions
versions = self._get_versions()
^^^^^^^^^^^^^^^^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/sqlmesh/core/state_sync/engine_adapter.py", line 363, in _get_versions
if not self.engine_adapter.table_exists(self.versions_table):
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/sqlmesh/core/engine_adapter/base_postgres.py", line 52, in table_exists
self.execute(sql)
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/sqlmesh/core/engine_adapter/base.py", line 760, in execute
self.cursor.execute(sql, **kwargs)
^^^^^^^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/sqlmesh/core/engine_adapter/postgres.py", line 25, in cursor
cursor = self._connection_pool.get_cursor()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/sqlmesh/utils/connection_pool.py", line 107, in get_cursor
self._thread_cursors[thread_id] = self.get().cursor()
^^^^^^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/sqlmesh/utils/connection_pool.py", line 114, in get
self._thread_connections[thread_id] = self._connection_factory()
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/sqlmesh/core/config/connection.py", line 51, in <lambda>
lambda: self._connection_factory(
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/ttzhou/.pyenv/versions/sqlmesh/lib/python3.11/site-packages/psycopg2/__init__.py", line 122, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.OperationalError: connection to server at "localhost" (::1), port 5433 failed: setsockopt(TCP_KEEPIDLE) failed: Invalid argument
connection to server at "localhost" (127.0.0.1), port 5433 failed: setsockopt(TCP_KEEPIDLE) failed: Invalid argument
Possible Cause
I believe this is caused by the default keepalives_idle value of 0 and how it's interpreted by psycopg2. If I explicitly set keepalives_idle: 1 in the YAML (in accordance with the psycopg2 documentation here
This method also sends feedback messages to the server every keepalive_interval (in seconds). The value of this parameter must be set to at least 1 second, but it can have a fractional part.
then sqlmesh plan executes correctly.
Possible Solution
Not sure what makes most sense, which is why I haven't filed PR: should we tweak the default keepalives_idle value in the Postgres engine itself, or have the yaml parsed to have default value 1 if keepalives_idle not specified?
Let me know if I missed something obvious where this isn't an issue, and close in that case!