From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Dominique Devienne <ddevienne(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Paul van der Linden <paul(dot)doskabouter(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Cannot find hstore operator |
Date: | 2022-01-24 17:59:45 |
Message-ID: | CAKFQuwbrDBirtzLeRjTzedaJY6OJBq9wffdasvA==qG7E1N8ug@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Jan 24, 2022 at 9:25 AM Dominique Devienne <ddevienne(at)gmail(dot)com>
wrote:
> On Mon, Jan 24, 2022 at 3:48 PM David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> > On Monday, January 24, 2022, Dominique Devienne <ddevienne(at)gmail(dot)com>
> wrote:
> >>
> >> After re-reading
> >> https://www.postgresql.org/docs/14/sql-createfunction.html in light of
> >> Tom's answer,
> >> does that mean that our `SET search_path TO {0}, 'pg_temp'`
> >> workaround, in the trigger below,
> >> to not depend on the search_path at runtime:
> >>
> >
> > The general trigger documentation is kind enough to point out that the
> sql language cannot be used to write trigger functions.
>
> OK, failr enough...
>
> But what about:
> > I'd really like my DDL statements to NOT contain schema-specific names,
> > to ensure proper name resolution independent of the search_path and
> > completely "self-contained" in the schema itself.
> > Is there any way to achieve that, beside our current `SET search_path`
> workaround?
>
> Could I please get a definitive answer about this?
>
There is no way to achieve that.
Objects do not know what schema they are in; and the search_path that is
present when the database initiates the SQL transaction itself (e.g.,
pg_dump, create index) provides only pg_catalog.
In short, search_path is a convenience for interactive users and external
applications. Never rely upon it including specific schemas - or any
schema at all (besides pg_catalog). Only if you set the search_path
yourself can the code that you write depend upon the search_path.
> David, in the "clone-schema" thread, you kind of implied I shouldn't
> have a set search_path in the triggers,
> thus making my DDL schema-specific, but then when I ask about ways to
> avoid thus, and have "static" resolution
> of names in those trigger functions, I'm not getting alternatives.
>
I rambled a bit in that email...but I did say "Sometimes limitations cannot
be avoided...". I then proceeded to show you an alternative...way of
thinking of the problem that would work with this limitation.
> Am I the only one to think that a session w/o a seach_path, which
> fully qualifies table names,
> should behaves exactly the same way than another session that has a
> search_path and does not fully qualify table names?
>
All sessions have a search_path...but if you schema-qualify everything
(except what is in pg_catalog) that doesn't really matter. But if the
schema-qualified name is "my_custom_schema" then, no, omitting the
schema-qualification and relying on search_path means you will get the
identical result when my_custom_schema is in the search_path but will get
an error when it is not. Both alternatives still include a search_path.
Because that's the only reason I added a set search_path to our
> trigger functions. The alternative being to fully-qualify
> all object references in those trigger functions, making the DDL even
> more "schema-specific". it feels like a catch-22...
>
Attaching a set search_path and schema-qualifying all object references are
equally "schema-specific". It isn't a catch-22, its two options for
interacting with a system that is designed to be schema-specific.
That is a lot of theory and generalities but I hope it helps clear things
up.
David J.
PS: Does INVOKER vs DEFINER affect name resolution?
Permissions and search_path resolution are independent.
From | Date | Subject | |
---|---|---|---|
Next Message | Hannes Erven | 2022-01-24 22:07:02 | Big variance in execution times of simple queries |
Previous Message | Michael Lewis | 2022-01-24 17:43:17 | Re: tstzrange on large table gives poor estimate of expected rows |