On Mon, Jan 24, 2022 at 9:25 AM Dominique Devienne <ddevie...@gmail.com> wrote:
> On Mon, Jan 24, 2022 at 3:48 PM David G. Johnston > <david.g.johns...@gmail.com> wrote: > > On Monday, January 24, 2022, Dominique Devienne <ddevie...@gmail.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.
