On Tue, Mar 13, 2018 at 6:50 PM, Michael Paquier <mich...@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. David J.
