SQL registry disconnections

[jiewpeng]

Expected Behavior

SQL registry works.

Current Behavior

When a SQL registry is used, there is a chance that we may hit a psycopg2.OperationalError: SSL connection has been closed unexpectedly issue. This can happen due to the connection being closed for being idle for too long.

Steps to reproduce

Use a SQL registry and set a connection idle timeout at the DB. Create a feature store object to connect to this registry, and wait for the timeout (i.e. don't query the feature store). Then try to query the feature store.

Specifications

• Version: 0.25.0 (but should not matter)
• Platform: Linux

Possible Solution

When creating the SQL registry, use self.engine = create_engine(..., pool_pre_ping=True). See https://docs.sqlalchemy.org/en/14/core/pooling.html#pool-disconnects

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[tokoko]

@jeremyary looks like this was fixed and accidentally left open.

[blopezpi]

@jeremyary @tokoko We encountered the same issue with Feast version 0.35.0. It seems the issue was resolved for using PostgreSQL as the online store, but when utilizing PostgreSQL as a registry, we cannot configure a connection pool. Instead, we've adjusted the Keepalive idle parameters to a larger value or used a proxy like RDS Proxy or PgBouncer to mitigate this error. This error occurs only when using the Python API Feature Server, and we need to adjust these values or attempt a retry to retrieve the features. Could we open this issue again? Thank you.

[tokoko]

@blopezpi you're right, sorry about that. Have you tried the solution that's suggested in the issue? You can pass {'pool_pre_ping': True} as sqlalchemy_config_kwargs if you're using sql registry. Something like this if you're configuring from feature_store.yaml:

registry:
    registry_type: sql
    path: postgresql://postgres:mysecretpassword@127.0.0.1:55001/feast
    sqlalchemy_config_kwargs:
        pool_pre_ring: true