René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Postgres Online store does not always manage transactions gracefully","articleBody":"## Expected Behavior \n\nWhenever transactions are used with a Postgres online store, changes should be committed. If transactions are not required, we shouldn't open transactions (e.g. when simply reading features).\n\n## Current Behavior\n\nWe are using `psycopg3` in case we use Postgres for the online store. If you create connections with `psycopg3` using a context manager (i.e. a `with ...` block), it will automatically commit the transaction once you exit the context. However, in `feast` we are manually creating (and in the case of connection pools, putting) connections, so we need to ensure that we also explicitly call `commit` after firing queries to the database. \n\nThe `commit` statements were missing for (async) read methods of the `PostgresOnlineStore` and the `teardown` method. For the read statements, this wasn't an issue as we just do a `SELECT`. However, it does add warning statements and quite a bit of latency as transactions needed to be rolled back. \n\nThe warning statement: \n```\n2025-02-07 14:05:40,301 WARNING [psycopg.pool] rolling back returned connection: \u003cpsycopg.AsyncConnection [INTRANS] (host=\u003cREDACTED\u003e port=\u003cREDACTED\u003e user=\u003cREDACTED\u003e database=\u003cREDACTED\u003e) at 0x314498790\u003e\n\n```\n\n### Specifications\n\n- Version: 0.40.1\n- Platform: MacOS\n- Subsystem: Sequoia 15.2\n\n## Possible Solution\n\nAdd missing `commit` calls and enable `autocommit` if a transaction is not needed for improved performance (e.g. for read methods).\n\nFiled a PR here: https://github.com/feast-dev/feast/pull/5039","author":{"url":"https://github.com/TomSteenbergen","@type":"Person","name":"TomSteenbergen"},"datePublished":"2025-02-10T13:57:00.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":0},"url":"https://github.com/5038/feast/issues/5038"}