pg.ddx.io pgsql-bugs@postgresql.org mailing list archivehelp / color / mirror / Atom feed
(Bug? or Intended?) Inconsistent search_path Behavior in Function Calls via Materialized View in PostgreSQL 17 2+ messages / 2 participants [nested] [flat]
* (Bug? or Intended?) Inconsistent search_path Behavior in Function Calls via Materialized View in PostgreSQL 17 @ 2025-02-17 15:10 Kanitchet Vaiassava <kanichet@hotmail.com> 2025-02-17 15:34 ` Re: (Bug? or Intended?) Inconsistent search_path Behavior in Function Calls via Materialized View in PostgreSQL 17 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 2+ messages in thread From: Kanitchet Vaiassava @ 2025-02-17 15:10 UTC (permalink / raw) To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org> I just upgrade from postgres 14 to 17 and found Inconsistent search_path Behavior in Function Calls via Materialized View in PostgreSQL 17 Summary According to image attached, when calling a function directly, it respects the configured search_path (public). However, when the same function is invoked through a REFRESH MATERIALIZED VIEW, the search_path defaults to pg_catalog, pg_temp, causing relation lookup failures. (the postgres 14 is consistency while postgres 17 is not) Expected Behavior * The function should inherit the search_path from its session (public). * The behavior of function execution should be consistent whether called directly or indirectly via a Materialized View. Actual Behavior (postgres 17) * Calling the function directly works fine (search_path = public). * Calling the function through a Materialized View defaults search_path to pg_catalog, pg_temp, causing it to fail to find tables Environment Details Configuration: search_path = 'public' in pg 17 && search_path = '"$user," public' in pg14 Possible Causes / Thoughts * The function execution context in a REFRESH MATERIALIZED VIEW may not inherit search_path correctly. * PostgreSQLs security model in newer versions (15+) might enforce a stricter search_path default (pg_catalog, pg_temp) inside Materialized View refresh execution ???? (which I have not found in web's document) <https://1drv.ms/i/c/cccf60f2871ac7bf/EUXq-FzY39tOuZXxHFcRxHQBtz0hi2c6BfCu1iTFDABL6w; [https://southcentralus1-mediap.svc.ms/transform/thumbnail?provider=spo&farmid=192074&inputFo...] [https://res-1.cdn.office.net/assets/mail/file-icon/png/cloud_blue_16x16.png]image-mb1.png <https://1drv.ms/i/c/cccf60f2871ac7bf/EZ9HoVLgEjpPlxham2OMucwBoiiWeUVTBCaR7Ni5YMJ7HQ; [https://southcentralus1-mediap.svc.ms/transform/thumbnail?provider=spo&farmid=192074&inputFo...] [https://res.public.onecdn.static.microsoft/assets/mail/file-icon/png/cloud_blue_16x16.png]image-mb2.png Regard. ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: (Bug? or Intended?) Inconsistent search_path Behavior in Function Calls via Materialized View in PostgreSQL 17 2025-02-17 15:10 (Bug? or Intended?) Inconsistent search_path Behavior in Function Calls via Materialized View in PostgreSQL 17 Kanitchet Vaiassava <kanichet@hotmail.com> @ 2025-02-17 15:34 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2025-02-17 15:34 UTC (permalink / raw) To: Kanitchet Vaiassava <kanichet@hotmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org> Kanitchet Vaiassava <kanichet@hotmail.com> writes: > Actual Behavior (postgres 17) > * Calling the function directly works fine (search_path = public). > * Calling the function through a Materialized View defaults search_path to pg_catalog, pg_temp, causing it to fail to find tables This is an intentional change in v17. > * PostgreSQLs security model in newer versions (15+) might enforce a stricter search_path default (pg_catalog, pg_temp) inside Materialized View refresh execution ???? (which I have not found in web's document) It's the first compatibility issue listed in the v17 release notes: https://www.postgresql.org/docs/17/release-17.html#RELEASE-17-MIGRATION regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-02-17 15:34 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-02-17 15:10 (Bug? or Intended?) Inconsistent search_path Behavior in Function Calls via Materialized View in PostgreSQL 17 Kanitchet Vaiassava <kanichet@hotmail.com> 2025-02-17 15:34 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox