From 21e1015e5266fda74853e69bcfb8027802e6dbc3 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc" Date: Sun, 24 Sep 2023 16:59:03 -0500 Subject: [PATCH v2 11/11] Add a sub-section to describe schema resolution There are some subtleties to schema resolution. At first glance it seems like schema resolution is "for runtime", i.e. it matters when querying or altering database data. But it takes a little bit of thought to reason through what schema resolution means for DDL. It is almost surprising that the search path in effect at DDL time persists, in a sense, in a useful way, regardless of the search path in effect during "regular" database use. This explanation also sheds light on the use of CREATE OR REPLACE sorts of syntaxes, and what gets changed when such syntax is used and what does not, and why. At least for those who do not poke about in the system catalogs and understand the oid relationships. Using the word "manipulate" is a bit awkward, but is what I came up with and I believe is made clear. Maybe there's better phrasing. Lead the reader through the implications of the search path in a DDL context so they can better reason about the best search path to use in their problem domain and better understand why PG behaves as it does. The whole thing is a little bit wordy and repeats some information present in other sections. But this allows the new section to stand on its own. --- doc/src/sgml/ddl.sgml | 48 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 48 insertions(+) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index ee30b7b575..6fc6c37b1d 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -3164,6 +3164,54 @@ SELECT 3 OPERATOR(pg_catalog.+) 4; + + Schema Resolution + + + schema + resolution + + + + Schema resolution happens when SQL is run. + + Exactly what this means is usually obvious; using an unqualified table + name when creating a table creates the table in the first schema of the + search path in effect, the current search path is used to find + unqualified table names when executing a SELECT, and + so forth. + But there are less obvious implications when it comes to statements + that manipulate database objects: + tables, triggers, functions and the like. + + + + Most SQL expressions appearing within the statements that manipulate + database objects are resolved at the time of manipulation. + Consider the creation of tables and triggers. + The schemas of foreign key table references, the functions and operators + used in table and column constraint expressions, table partition + expressions, and so forth are resolved at the time of table creation. + So is the schema of the function named when a trigger is created. + These already-resolved tables, functions, operators, + etc. need not be in the search path when the + constraints or triggers are executed, when the content of the table is + modified and the data validation occurs. + This is just as if all the objects were fully schema qualified in the + SQL that created the table, trigger, or other database object. + + + + But functions are different. + The point of function creation is to store code for execution later. + Schemas are resolved within a function body when the function is called, + not when the function is created. + This has implications for function behavior consistency and + security. + For these reasons function bodies can have their own search paths. + + + Schemas and Privileges -- 2.30.2