On Tue, Mar 13, 2018 at 6:50 PM, Michael Paquier <michael@paquier.xyz> wrote:
On Sat, Mar 10, 2018 at 08:36:34AM +0000, Noah Misch wrote: > This qualifies some functions, but it leaves plenty of unqualified operators.
Yeah, I know that, and i don't have a perfect reply to offer to you. There are a couple of methods that we could use to tackle that: 1) For functions, enforce search_path with a SET search_path = 'pg_catalog' command. However this has a performance impact. 2) Enforce operators qualification with operator(pg_catalog.foo). This has no impact on performance, but repeating that all over the place is rather ugly, particularly for psql's describe.c and tab-completion.c. 3) Tweak dynamically search_path before running a query: - Save the existing search_path value by issuing SHOW search_path. - Use ALWAYS_SECURE_SEARCH_PATH_SQL to enforce the path. - Set back search_path based on the previous value. This logic can happen in a dedicated wrapper, but this impacts performance as it requires extra round trips to the server.
For information_schema.sql, we are talking about tweaking 12 functions. So I think that we could live with 2).
That seems ideal.
To simplify user's life, we could also recommend just to users to issue a ALTER FUNCTION SET search_path to fix the problem for all functions, that's easier to digest.
I'm unclear as to what scope you are suggesting the above advice (and option #1) apply to. All pg_catalog/information_schema functions or all functions including those created by users?
For the rest, which basically concerns psql, I have been thinking that actually using 2) would be the most painful approach, still something which does not impact the user experience, while 3) is easier to back-patch by minimizing the code footprint and avoids also any kind of future problems.
In furtherance of option 2 is there some way to execute a query (at least in a development build) with no search_path in place - thus requiring every object reference to be schema-qualified - and in doing so all such unadorned operators/functions/relations would fail to be found quickly at parse time? Given the number of user-hours spent running describe commands and tab-completion the extra round-time solution is definitely less appealing in terms of long term time expended.
