Re: Fixes for missing schema qualifications

On Tue, Mar 13, 2018 at 6:50 PM, Michael Paquier <michael(at)paquier(dot)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.
​