postgres engine default keepalives_idle value causes setsockopt(TCP_KEEPIDLE) invalid value

[ttzhou]

Reprex:

1. Fresh sqlmesh init
2. Modify config.yaml to use postgres

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.

3. Run sqlmesh plan
4. 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!

[tobymao]

thanks for filing this, we'll take a look on monday or so

[eakmanrq]

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 psycopg2 and allow it to implement the default behavior.

PR: #758

[ttzhou]

Thanks so much for looking into this!