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.
* 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&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.
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 |