Repository navigation
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!
Activity
tobymao commentedon Apr 22, 2023
thanks for filing this, we'll take a look on monday or so
eakmanrq commentedon Apr 24, 2023
Thanks for reporting this @ttzhou. There does seem to be some confusion/inconsistency in implementation for this field. Looking at some options and it seems like removing the default entirely is the right solution. Then we don't pass anything into
psycopg2and allow it to implement the default behavior.PR: #758
ttzhou commentedon Apr 24, 2023
Thanks so much for looking into this!