On Thu, 2 Jan 2025 13:48:29 +0100 Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote: > čt 2. 1. 2025 v 13:15 odesílatel Jan Behrens <jbe-mlist@xxxxxxxxxxxxx> > napsal: > > > On Thu, 2 Jan 2025 12:40:59 +0100 > > Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote: > > > > > How can you identify unwanted usage of non qualified identifiers from > > > wanted usage of non qualified identifiers? It is a common pattern for > > > sharding. Using not qualified identifiers of operators, functions is > > common > > > when you are using orafce extensions, etc. > > > > I don't fully understand the use-case. Could you elaborate? > > > > As I understand, even if identifiers are not fully-qualified, it is > > forbidden to use the search_path to refer to different database > > entities at run-time (as David pointed out). > > > > So I don't understand how a dynamic "search_path" could be used in any > > scenario within functions except when EXECUTE is involved. > > > > you don't need more databases > > schema one - customer x > schema two - customer y > > create table one.t1(..); create table one.t2(..); > create table two.t1(..); create table two.t2(..); > > set search_path to one; > -- work with data set of customer x > > set search_path to two; > -- work wit data set of customer y > > some times can be pretty ineffective to have database per customer - more > connect, disconnect in postgres is much more expensive than SET search_path > TO .. and maybe RESET plans; I guess that means there is a practical application where search_path MAY change at runtime IF done in different sessions or if the cache is reset using the DISCARD command: https://www.postgresql.org/docs/17/sql-discard.html I assume DISCARD PLANS would be the right command? This seems to be a very special case though. I think there should be a warning in the documentation of CREATE FUNCTION with regard to schemas anyway, though. Regards, Jan
