(Bug? or Intended?) Inconsistent search_path Behavior in Function Calls via Materialized View in PostgreSQL 17

From: Kanitchet Vaiassava <kanichet(at)hotmail(dot)com>
To: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: (Bug? or Intended?) Inconsistent search_path Behavior in Function Calls via Materialized View in PostgreSQL 17
Date: 2025-02-17 15:10:42
Message-ID: KL1PR0401MB51326DEE3C1BA7DE89488B92D8FB2@KL1PR0401MB5132.apcprd04.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.
* PostgreSQL’s 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&inputFormat=png&cs=NWQ2NjE5NTAtMzQ3NS00MWNkLWEyYzMtZDY3MWEzMTYyYmMxfFNQTw&docid=https%3A%2F%2Fmy.microsoftpersonalcontent.com%2F_api%2Fv2.0%2Fdrives%2Fb!dTiwujxvOUe3u_8OJjgrL8hvNJlI0RZAl1i1RYBh88PAeozHo3jfT6GBTccPrk1R%2Fitems%2F01A5LBA3CF5L4FZWG73NHLTFPRDRLRDRDU%3Ftempauth%3Dv1e.eyJzaXRlaWQiOiJiYWIwMzg3NS02ZjNjLTQ3MzktYjdiYi1mZjBlMjYzODJiMmYiLCJhcHBpZCI6IjVkNjYxOTUwLTM0NzUtNDFjZC1hMmMzLWQ2NzFhMzE2MmJjMSIsImF1ZCI6IjAwMDAwMDAzLTAwMDAtMGZmMS1jZTAwLTAwMDAwMDAwMDAwMC9teS5taWNyb3NvZnRwZXJzb25hbGNvbnRlbnQuY29tQDkxODgwNDBkLTZjNjctNGM1Yi1iMTEyLTM2YTMwNGI2NmRhZCIsImV4cCI6IjE3Mzk4MjYwMDAifQ.Sses1lRiW_QUosBbYtU7xg5N2e-BIKwjOn2E0KQ_f86SHip-qTIBDzwgsPlLorTyIrE3nX17TpfIgjmxZB0oxZEkjPYFCdFT9Ma-bOvtMu6eHjmxaQNlvnTEa65H1GLdxB1rrnVbnyF4ftQsy8F2a5BjiQTFsAwDaSKTFu6OVzlE55jWSKgcVqhBAKDA9PStGodep0qVBXxXW3ADnQh7B1D22oirS1XYGDc7w3ylrLVB4xiXXQaaw4qiMANce-7IlFutBsrgMCH4N5Iv76Lj2KtCtswR7foKa4L3S5HHo4vskSt22xaCgmnp3vvua9LzHm4o6m0vBQz-NknDmKplGZA0MPO2nRckMj0PIkEqaEF5dbPxdTbUy7v9sT01p1zVEuxi7NHCiJWtpiddVXOj2F8pyVYXEclFf9HGdpSPN4vFgAzJEUnEbUrtqoWvNf18.0jfQ2ztmFtTrl1SI3yZa7_XylN7hP-XGjSzshSbKpZA%26version%3DPublished&width=176&height=176&cb=63875401440]
[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&inputFormat=png&cs=NWQ2NjE5NTAtMzQ3NS00MWNkLWEyYzMtZDY3MWEzMTYyYmMxfFNQTw&docid=https%3A%2F%2Fmy.microsoftpersonalcontent.com%2F_api%2Fv2.0%2Fdrives%2Fb!dTiwujxvOUe3u_8OJjgrL8hvNJlI0RZAl1i1RYBh88PAeozHo3jfT6GBTccPrk1R%2Fitems%2F01A5LBA3E7I6QVFYASHJHZOGC2TNRYZOOM%3Ftempauth%3Dv1e.eyJzaXRlaWQiOiJiYWIwMzg3NS02ZjNjLTQ3MzktYjdiYi1mZjBlMjYzODJiMmYiLCJhcHBpZCI6IjVkNjYxOTUwLTM0NzUtNDFjZC1hMmMzLWQ2NzFhMzE2MmJjMSIsImF1ZCI6IjAwMDAwMDAzLTAwMDAtMGZmMS1jZTAwLTAwMDAwMDAwMDAwMC9teS5taWNyb3NvZnRwZXJzb25hbGNvbnRlbnQuY29tQDkxODgwNDBkLTZjNjctNGM1Yi1iMTEyLTM2YTMwNGI2NmRhZCIsImV4cCI6IjE3Mzk4MjYwMDAifQ.oWdVb6aLVtr-Qj77nU3avxAVz47973415Hu_bLACZJ1q825gAOQWWKXY7tvBV7ZhQReZ_7amYSfQi-iLqMK-8u8-wlIBcD7Usp_9HcBPBks5ySMw-Z4j3DDEemPI7poaVGYZ57-PnoOh75bmqRwNwVRnuiDo-3stbsWOxO8qOK-QfAOKTHM27K-6Spcbiea_rLJxTS2j2u2Q9zDJULxzel6EpKjTwBDHOiFLegvmi45SoIFg7FBSdefnAF5hd7gAExgUODyH2jBFRalikFs0W9LQbGWMYuu3_j1UKZhYJZ-dNi_YGib6M3JjTUSkh9sw1MLnSIMtcuRtCtOmricYCMHJ4skG_QBNz8BHu3q5mrZpHWa3TirVA-C7OXzvRiIhNpMobxqGHu-2qH7oE43fZqt6v4qkvw15NoV0zPgmdLiPUri-29rKauNKyyuZeXYL.tV-qVlDnCySwGU7ftAZRPOlAS9dKtzSkhlHpPtmITo4%26version%3DPublished&width=176&height=176&cb=63875401445]
[https://res.public.onecdn.static.microsoft/assets/mail/file-icon/png/cloud_blue_16x16.png]image-mb2.png

Regard.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2025-02-17 15:31:08 Re: issue with PSQL 17.1 on Suse 15 Linux SP6
Previous Message PG Bug reporting form 2025-02-17 15:09:04 BUG #18816: pg16 requires perl 5.14+ but is not documented