Skip to content

fix: Column quoting in query of PostgreSQLOfflineStore.pull_all_from_table_or_query#5621

Merged
franciscojavierarceo merged 2 commits intofeast-dev:masterfrom
hungnguyen94:fix/postgres-offline-column-quoting
Sep 19, 2025
Merged

fix: Column quoting in query of PostgreSQLOfflineStore.pull_all_from_table_or_query#5621
franciscojavierarceo merged 2 commits intofeast-dev:masterfrom
hungnguyen94:fix/postgres-offline-column-quoting

Conversation

@hungnguyen94
Copy link
Contributor

@hungnguyen94 hungnguyen94 commented Sep 19, 2025

What this PR does / why we need it:

This fixes an issue with the PostgreSQLOfflineStore we had encountered. The implementation of materialization, using the local compute engine, changed after feast>=0.50.
The invocation to FeatureStore.materialize now calls PostgreSQLOfflineStore.pull_all_from_table_or_query (instead of PostgreSQLOfflineStore.pull_latest_from_table_or_query).

In this method, the columns in the query are not quoted. This causes issues when columns contain e.g. capitalized characters, spaces etc. PostgreSQL by default lowercases everything when not quoted.

example error traceback 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
/.../lib/python3.12/site-packages/feast/feature_store.py:1604: in materialize
    provider.materialize_single_feature_view(
/.../lib/python3.12/site-packages/feast/infra/passthrough_provider.py:454: in materialize_single_feature_view
    raise e
/.../lib/python3.12/site-packages/feast/infra/compute_engines/local/compute.py:84: in _materialize_one
    plan.execute(context)
/.../lib/python3.12/site-packages/feast/infra/compute_engines/dag/plan.py:51: in execute
    output = node.execute(context)
/.../lib/python3.12/site-packages/feast/infra/compute_engines/local/nodes.py:48: in execute
    arrow_table = retrieval_job.to_arrow()
/.../lib/python3.12/site-packages/feast/infra/offline_stores/offline_store.py:111: in to_arrow
    features_table = self._to_arrow_internal(timeout=timeout)
/.../lib/python3.12/site-packages/feast/infra/offline_stores/contrib/postgres_offline_store/postgres.py:353: in _to_arrow_internal
    cur.execute(query)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <psycopg.Cursor [closed] [BAD] at 0x116ba0410>
query = '\n            SELECT CUSTOMER_ID, FEATURE_INT, FEATURE_FLOAT, FEATURE_STRING, FEATURE_BOOL, FEATURE_TIMESTAMP\n      ...TIMESTAMP" BETWEEN \'1900-01-01 00:00:00+00:00\'::timestamptz AND \'9999-12-31 00:00:00+00:00\'::timestamptz\n        '
params = None

    def execute(
        self,
        query: Query,
        params: Params | None = None,
        *,
        prepare: bool | None = None,
        binary: bool | None = None,
    ) -> Self:
        """
        Execute a query or command to the database.
        """
        try:
            with self._conn.lock:
                self._conn.wait(
                    self._execute_gen(query, params, prepare=prepare, binary=binary)
                )
        except e._NO_TRACEBACK as ex:
>           raise ex.with_traceback(None)
E           psycopg.errors.UndefinedColumn: column "customer_id" does not exist
E           LINE 2:             SELECT CUSTOMER_ID, FEATURE_INT, FEATURE_FLOAT, ...
E                                      ^

/.../lib/python3.12/site-packages/psycopg/cursor.py:97: UndefinedColumn

This PR adds a invocation to _append_alias to the columns, which also adds quotes to the column names. This is the same behaviour used in PostgreSQLOfflineStore.pull_latest_from_table_or_query.

Misc

cc @TomSteenbergen

@hungnguyen94 hungnguyen94 requested a review from a team as a code owner September 19, 2025 13:11
@hungnguyen94 hungnguyen94 changed the title fix: column quoting in query of PostgreSQLOfflineStore.pull_all_from_table_or_query Fix: column quoting in query of PostgreSQLOfflineStore.pull_all_from_table_or_query Sep 19, 2025
@hungnguyen94 hungnguyen94 changed the title Fix: column quoting in query of PostgreSQLOfflineStore.pull_all_from_table_or_query fix: Column quoting in query of PostgreSQLOfflineStore.pull_all_from_table_or_query Sep 19, 2025
@franciscojavierarceo franciscojavierarceo merged commit e8eae71 into feast-dev:master Sep 19, 2025
17 checks passed
franciscojavierarceo pushed a commit that referenced this pull request Sep 30, 2025
@feast-ci-bot
chore(release): release 0.54.0
99a321a 
# [0.54.0](v0.53.0...v0.54.0) (2025-09-30)

### Bug Fixes

* Column quoting in query of `PostgreSQLOfflineStore.pull_all_from_table_or_query` ([#5621](#5621)) ([e8eae71](e8eae71))
* Correct column list polars materialization engine ([#5595](#5595)) ([39aeb0c](39aeb0c))
* Fix Go feature server entitykey serialization for version 3 ([#5622](#5622)) ([5ab18a6](5ab18a6))
* Fix hostname resolution for spark tests ([#5610](#5610)) ([8f0e22d](8f0e22d))
* Fixed filtering based on data_source for ODFVs ([#5593](#5593)) ([c3e6c56](c3e6c56))
* Fixed project_description to set in registry and UI ([#5602](#5602)) ([02c3006](02c3006))
* Fixed Registry Cache Refresh Issues ([#5604](#5604)) ([3c7a022](3c7a022))
* Fixed tls issue when running both grpc and rest servers ([#5617](#5617)) ([51c16b1](51c16b1))
* Fixed transaction handling with SQLite registry ([#5588](#5588)) ([0052754](0052754))
* Update the deprecated functions in Go feature server. ([#5632](#5632)) ([a24e06e](a24e06e))
* Updated python packages conflicting with kserve dependencies ([#5580](#5580)) ([d56baf4](d56baf4))

### Features

* Add 'featureView' in global search api result for features. ([#5626](#5626)) ([76590bf](76590bf))
* Add aggregation in OnDemandFeatureView ([#5629](#5629)) ([8715ae8](8715ae8))
* Added codeflare-sdk to requirements ([#5640](#5640)) ([51a0ee6](51a0ee6))
* Added RemoteDatasetProxy that executes Ray Data operations remotely ([7128024](7128024))
* Added support for image search ([#5577](#5577)) ([56c5910](56c5910))
* Enable ingestion without event timestamp ([#5625](#5625)) ([eb51f00](eb51f00))
* Feast dataframe phase1 ([#5611](#5611)) ([2ce4198](2ce4198))
* Feast dataframe phase2 ([#5612](#5612)) ([1d08786](1d08786))
* Feast Namespaces registry for client ConfigMaps availability ([#5599](#5599)) ([728589a](728589a))
* Support hdfs:// uris in to_remote_storage for Spark offline store ([#5635](#5635)) ([5e4b9fd](5e4b9fd))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

2 participants

@hungnguyen94 @franciscojavierarceo