On Mon, 25 Sep 2023 14:14:37 +0200
Daniel Gustafsson <dan...@yesql.se> wrote:
> > On 25 Sep 2023, at 14:00, Karl O. Pinc <k...@karlpinc.com> wrote:
>
> > Is there a preferred data format or should I send
> > each patch in a separate attachment with description?
> Once done you can do "git format-patch origin/master -v 1" which will
> generate a set of n patches named v1-0001 through v1-000n. You can
> then attache those to the thread.
Done. 11 patches attached. Thanks for the help.
(This is v2, since I made some changes upon review.)
I am not particularly confident in the top-line commit
descriptions. Some seem kind of long and not a whole
lot of thought went into them. But the commit descriptions
are for the committer to decide anyway.
The bulk of the commit descriptions are very wordy
and will surely need at least some editing.
Listing all the attachments here for future discussion:
v2-0001-Change-section-heading-to-better-reflect-saving-a.patch
v2-0002-Change-section-heading-to-better-describe-referen.patch
v2-0003-Better-section-heading-for-plpgsql-exception-trap.patch
v2-0004-Describe-how-to-raise-an-exception-in-the-excepti.patch
v2-0005-Improve-sentences-in-overview-of-system-configura.patch
v2-0006-Provide-examples-of-listing-all-settings.patch
v2-0007-Cleanup-summary-of-role-powers.patch
v2-0008-Explain-the-difference-between-role-attributes-an.patch
v2-0009-Document-the-oidvector-type.patch
v2-0010-Improve-sentences-about-the-significance-of-the-s.patch
v2-0011-Add-a-sub-section-to-describe-schema-resolution.patch
Regards,
Karl <k...@karlpinc.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
>From 122665c4155698abe88e2bd17639a991791b94e3 Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc" <k...@karlpinc.com>
Date: Sun, 24 Sep 2023 15:49:30 -0500
Subject: [PATCH v2 01/11] Change section heading to better reflect saving a
result in variable(s)
The current section title of "Executing a Command with a Single-Row
Result" does not reflect what the section is really about. Other
sections make clear how to _execute_ commands, single-row result or not.
What this section is about is how to _save_ a single row of results into
variable(s).
It would be nice to talk about saving results into variables in the
section heading but I couldn't come up with anything pithy. "Saving a
Single-Row of a Command's Result" seems good enough, especially since
there's few other places to save results other than in variables.
---
doc/src/sgml/plpgsql.sgml | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index f55e901c7e..8747e84245 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1126,7 +1126,7 @@ PERFORM create_mv('cs_session_page_requests_mv', my_query);
</sect2>
<sect2 id="plpgsql-statements-sql-onerow">
- <title>Executing a Command with a Single-Row Result</title>
+ <title>Saving a Single-Row of a Command's Result</title>
<indexterm zone="plpgsql-statements-sql-onerow">
<primary>SELECT INTO</primary>
--
2.30.2
>From 4de4a31d41124dfa793cc5cce0516673811ea414 Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc" <k...@karlpinc.com>
Date: Sun, 24 Sep 2023 15:52:21 -0500
Subject: [PATCH v2 02/11] Change section heading to better describe reference
of existing types
The section heading of "Copying Types" does not reflect what the
section is about. It is not about making copies of data types but
about using the data type of existing columns (or rows) in new type
declarations without having to know what the existing type is.
"Re-Using the Type of Columns and Variables" seems adequate. Getting
something in there about declartions seems too wordy. I thought
perhaps "Referencing" instead of "Re-Using", but "referencing" isn't
perfect and "re-using" is generic enough, shorter, and simpler to read.
---
doc/src/sgml/plpgsql.sgml | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 8747e84245..874578265e 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -672,7 +672,7 @@ DECLARE
</sect2>
<sect2 id="plpgsql-declaration-type">
- <title>Copying Types</title>
+ <title>Re-Using the Type of Columns and Variables</title>
<synopsis>
<replaceable>variable</replaceable>%TYPE
--
2.30.2
>From 0252dd434bb9ab2487cd37a93912d19ca1ef5149 Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc" <k...@karlpinc.com>
Date: Sun, 24 Sep 2023 16:03:29 -0500
Subject: [PATCH v2 03/11] Better section heading for plpgsql exception
trapping
The docs seem to use "error" and "exception" interchangeably, perhaps
50% each. But they never say that the are the same thing, and in the
larger world they are not. Errors tend to be something that drop on
the floor and usually halt execution whereas exceptions can be trapped
and give the programmer more control over the flow of the program.
"Trapping Errors" is not a good section title for these reasons, and
because when it comes to programmatically raising errors in Pl/PgSQL
you don't, you raise exceptions. The current section heading does not
stand out in a scan of the table of contents when you're looking for
exception handling, IMHO.
"Error Handling and Exception Trapping" is a little long but it does
accurately reflect that the section is about how Pl/PgSQL behaves
under error conditions and how the programmer can trap exceptions and
affect the program's flow of control. The "Exception" does not stand
out as much as I'd like on a scan of the table of contents, but
enough. I left "Error" in the title since the word "error" is used so
frequently in the docs, and the section is also about what happens
when there is any sort of PG error, not just those raised in user
code.
---
doc/src/sgml/plpgsql.sgml | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 874578265e..646d0305eb 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -2756,7 +2756,7 @@ NOTICE: row = {10,11,12}
</sect2>
<sect2 id="plpgsql-error-trapping">
- <title>Trapping Errors</title>
+ <title>Error Handling and Exception Trapping</title>
<indexterm>
<primary>exceptions</primary>
--
2.30.2
>From f1547ccab10a41ac3d58aa49c540d5bb845507a1 Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc" <k...@karlpinc.com>
Date: Sun, 24 Sep 2023 16:03:59 -0500
Subject: [PATCH v2 04/11] Describe how to raise an exception in the exception
section
Most of this section is about managing program control flow, but the
section does not mention how to raise an exception anywhere. This new
sentence says how, and provides a link into the section on raising
exceptions for those who want to know more.
Line break after end of sentence to simplify reading of future patches.
---
doc/src/sgml/plpgsql.sgml | 7 +++++--
1 file changed, 5 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 646d0305eb..3cecf7bbd9 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -2765,8 +2765,11 @@ NOTICE: row = {10,11,12}
<para>
By default, any error occurring in a <application>PL/pgSQL</application>
- function aborts execution of the function and the
- surrounding transaction. You can trap errors and recover
+ function aborts execution of the function and the surrounding
+ transaction.
+ You can raise an exception and throw an error
+ with <link linkend="plpgsql-statements-raise">RAISE EXCEPTION ...</link>.
+ You can trap errors and recover
from them by using a <command>BEGIN</command> block with an
<literal>EXCEPTION</literal> clause. The syntax is an extension of the
normal syntax for a <command>BEGIN</command> block:
--
2.30.2
>From 43901ae60cc8471c99f585283bc0d756b4166d6e Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc" <k...@karlpinc.com>
Date: Mon, 25 Sep 2023 15:32:23 -0500
Subject: [PATCH v2 05/11] Improve sentences in overview of system
configuration parameters
Get rid of "we" wording. Remove extra words in sentences.
Line break after end of each sentence to ease future patch reading.
---
doc/src/sgml/config.sgml | 7 ++++---
1 file changed, 4 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 6bc1b215db..97f9838bfb 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -10,9 +10,10 @@
<para>
There are many configuration parameters that affect the behavior of
- the database system. In the first section of this chapter we
- describe how to interact with configuration parameters. The subsequent sections
- discuss each parameter in detail.
+ the database system.
+ The first section of this chapter describes how to interact with
+ configuration parameters.
+ Subsequent sections discuss each parameter in detail.
</para>
<sect1 id="config-setting">
--
2.30.2
>From e9aa07fdb7ea6beb3d62f821e42bace5cecb6ce7 Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc" <k...@karlpinc.com>
Date: Mon, 25 Sep 2023 15:36:23 -0500
Subject: [PATCH v2 06/11] Provide examples of listing all settings
This commit is problematic in that it does something that is not done
elsewhere in the documentation, it provides example SELECTs as a
stand-in for a regular tabular documentation element. It is almost
something that should go in its own appendix, but I don't think that
is warranted. Likewise, I think that having another tabular
documentation element that must be maintained and kept up-to-date is
also not appropriate.
It is useful to have tabular overviews of all available system
settings, as is having an overview of the setting values of your
particular cluster. This commit is an attempt in that direction.
---
doc/src/sgml/config.sgml | 37 +++++++++++++++++++++++++++++++++++++
1 file changed, 37 insertions(+)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 97f9838bfb..0af4e6dcae 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -11,6 +11,43 @@
<para>
There are many configuration parameters that affect the behavior of
the database system.
+ A single master list of all parameters and their characteristics is not
+ provided in this document.
+ Use the <link linkend="bookindex">index</link> (or web search) to find
+ configuration parameter documentation by name.
+ </para>
+
+ <indexterm>
+ <primary>pg_settings</primary>
+ <secondary>example queries</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>configuration</primary>
+ <secondary>query the current settings</secondary>
+ </indexterm>
+
+ <para>
+ The <link linkend="view-pg-settings">pg_settings</link>
+ <link linkend="tutorial-views">view</link> into
+ the <link linkend="catalogs">system catalogs</link> provides summaries of
+ all, or selected, configuration parameters, e.g:
+ </para>
+
+<programlisting>
+-- Describe all configuration parameters.
+SELECT name, short_desc FROM pg_settings ORDER BY name;
+
+-- Show the current configuration of the connected cluster, database,
+-- and session.
+SELECT name, setting, unit FROM pg_settings ORDER BY name;
+
+-- Show the means available to change the setting; whether the setting is
+-- per-cluster, per-database, per-session, etc.
+SELECT name, context FROM pg_settings ORDER BY name;
+</programlisting>
+
+ <para>
The first section of this chapter describes how to interact with
configuration parameters.
Subsequent sections discuss each parameter in detail.
--
2.30.2
>From 94cd14a74a19c3332b1a0d33484d12ab7a60d596 Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc" <k...@karlpinc.com>
Date: Mon, 25 Sep 2023 16:39:02 -0500
Subject: [PATCH v2 07/11] Cleanup summary of role powers.
Make sentences shorter. Explain privileges v.s. permissions.
This commit assumes that there is a distinction in the PostgreSQL
vocabulary between role privileges and permissions. Privileges being
a specific Postgres term for powers granted to roles via GRANT.
Permissions being abilities to perform specific operations that comes
with privileges, but also with role attributes like CREATEDB. So
permissions are a broader category than privileges, in terms of where
they come from. As well as being a narrower category in another
sense, in that some privileges, like object ownership, carry with them
a swath of individual permissions/abilities, like USAGE.
It seems a useful distinction to make, in terms of thinking about how
object access works. Essential in fact, since both privileges
(e.g. ownership) and role attributes (e.g. SUPERUSER) give roles
abilities, aka permissions (e.g. SELECT). Without the distinction you
can't describe role inheritance (attributes don't, privileges do) or
discuss the source of particular access rights. If access rights, aka
permissions, aka "abilities to do things", are the same as privileges
how come some privileges convey multiple abilities, abilities that
have their own names; how can you get an ability via a role attribute
but not have the privilege? It seems most clear to, conceptually at
least, have a separate set of permissions (abilities) that come via
privileges granted to roles and role attributes assigned to roles.
So, I'm not sure my choice of vocabulary is perfect. One might use
"rights" or some other word rather than "permissions". But I think
that there should be a clear distinction between the 3 concepts of
granted privileges, role attributes, and permissions to perform
specific operations.
I also would argue that it is not necessary to scour the existing
documentation and patch to obtain perfect consistency in vocabulary
usage. The existing wording seems acceptable in practice and I don't
see anyone who's read what's already there to be confused by the
changes presented here.
---
doc/src/sgml/user-manag.sgml | 13 ++++++++-----
1 file changed, 8 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 27c1f3d703..492325e8a2 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -7,11 +7,14 @@
<productname>PostgreSQL</productname> manages database access permissions
using the concept of <firstterm>roles</firstterm>. A role can be thought of as
either a database user, or a group of database users, depending on how
- the role is set up. Roles can own database objects (for example, tables
- and functions) and can assign privileges on those objects to other roles to
- control who has access to which objects. Furthermore, it is possible
- to grant <firstterm>membership</firstterm> in a role to another role, thus
- allowing the member role to use privileges assigned to another role.
+ the role is set up.
+ Roles can own database objects (for example, tables and functions).
+ They can assign privileges on the owned objects, and thus the permissions
+ the privileges carry, to other roles.
+ Roles therefore control who has what access to which objects.
+ It is possible to grant <firstterm>membership</firstterm> in a role to
+ another role, thus allowing the member role to use the privileges assigned
+ to another role.
</para>
<para>
--
2.30.2
>From e838989f040dfced1533729ead0fca3551cdc78b Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc" <k...@karlpinc.com>
Date: Mon, 25 Sep 2023 17:18:58 -0500
Subject: [PATCH v2 08/11] Explain the difference between role attributes and
role privileges
All of the information presented here is present elsewhere in the
documentation, but scattered about. It is useful to have a summary;
one place where the interactions between INHERIT, the other role
attributes, and granting of privileges with roles is explained.
Otherwise, it is hard to synthesize this out of bits and pieces
mentioned elsewhere.
---
doc/src/sgml/user-manag.sgml | 28 ++++++++++++++++++++++++++++
1 file changed, 28 insertions(+)
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 492325e8a2..4a26ede8fb 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -12,9 +12,37 @@
They can assign privileges on the owned objects, and thus the permissions
the privileges carry, to other roles.
Roles therefore control who has what access to which objects.
+ </para>
+
+ <indexterm>
+ <primary>role</primary>
+ <secondary>inheriting permissions</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>role</primary>
+ <secondary>attributes</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>role</primary>
+ <secondary>current role</secondary>
+ </indexterm>
+
+ <para>
It is possible to grant <firstterm>membership</firstterm> in a role to
another role, thus allowing the member role to use the privileges assigned
to another role.
+ This acquisition of privilege can happen automatically, if the role given
+ membership has the <literal>INHERIT</literal> attribute, or manually, via
+ a <literal>SET ROLE</literal> to the granted role.
+ But it is important to distinguish between privileges, which are assigned
+ with <literal>GRANT</literal>, and role attributes,
+ like <literal>INHERIT</literal>, <literal>SUPERUSER</literal>, and
+ <literal>CREATEDB</literal>, which are assigned with <literal>CREATE
+ ROLE</literal> or <literal>ALTER ROLE</literal>.
+ Privileges may be inherited, role attributes cannot and are only effective
+ when <literal>SET ROLE</literal> changes the current role.
</para>
<para>
--
2.30.2
>From 20bf83047dcf08bd3d65b25889dee9e559a152db Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc" <k...@karlpinc.com>
Date: Sun, 24 Sep 2023 16:36:36 -0500
Subject: [PATCH v2 09/11] Document the oidvector type
The oidvector type is used in pg_proc.proargtypes, and perhaps
elsewhere in the catalogs. But there is no documentation on how to
get oids out of a oidvector or otherwise manipulate the data type for
those who wish to do so. This seems the only place where such
documentation would go.
---
doc/src/sgml/datatype.sgml | 11 +++++++++++
1 file changed, 11 insertions(+)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 8d32a8c9c5..91cc1e5cb0 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4754,6 +4754,17 @@ INSERT INTO mytable VALUES(-1); -- fails
signed-versus-unsigned confusion if you do this.)
</para>
+ <indexterm zone="datatype-oid">
+ <primary>oidvector</primary>
+ </indexterm>
+
+ <para>
+ The legacy <type>oidvector</type> type can be cast to an array of OIDs,
+ and vice versa, for examination and manipulation.
+ The resultant array of OIDs, unlike a typical array, is indexed
+ zero-relative.
+ </para>
+
<para>
The OID alias types have no operations of their own except
for specialized input and output routines. These routines are able
--
2.30.2
>From efba1692e299769ff42dc1ecdc6f4d60daf54df7 Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc" <k...@karlpinc.com>
Date: Sun, 24 Sep 2023 16:39:24 -0500
Subject: [PATCH v2 10/11] Improve sentences about the significance of the
search path's first schema
These two sentences have extra words, and can be improved with small
word re-ordering. The word "again" could probably be removed as well
but it reads ok with it and it does not hurt to pound the point into
the reader's brain.
Note however that the original last sentence still isn't 100% correct,
because the default configuration includes "$user" at the start of the
search path. So if an object is in the user's schema an unqualified
mention refers to something in the user's schema. Hence, the
qualification that comes after the semicolon. I thought about a
separate sentence, but the 2 parts are closely intertwined. So,
semicolon. It's all kind of a lot. But, although I do believe in
removing extra content from sentences to make them shorter, more
clear, and memorable, I do think it's ok to repeat things when writing
narrative. And the existing documentation is going for repetition so I
stuck with that.
Line break after each end of sentence to improve readability of future
patches.
---
doc/src/sgml/ddl.sgml | 14 ++++++++------
1 file changed, 8 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 075ff32991..ee30b7b575 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3106,12 +3106,14 @@ SHOW search_path;
<para>
The first schema in the search path that exists is the default
location for creating new objects. That is the reason that by
- default objects are created in the public schema. When objects
- are referenced in any other context without schema qualification
- (table modification, data modification, or query commands) the
- search path is traversed until a matching object is found.
- Therefore, in the default configuration, any unqualified access
- again can only refer to the public schema.
+ default objects are created in the public schema.
+ When objects are referenced in a context without schema qualification
+ (table modification, data modification, or query commands) the search path
+ is traversed until a matching object is found.
+ Therefore, again, in the default configuration, any unqualified name
+ refers to an object in the public schema; unless, given the default search
+ path, there is an object with that name accessible in the schema having
+ the name of the current user.
</para>
<para>
--
2.30.2
>From 21e1015e5266fda74853e69bcfb8027802e6dbc3 Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc" <k...@karlpinc.com>
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;
</para>
</sect2>
+ <sect2 id="ddl-schema-resolution">
+ <title>Schema Resolution</title>
+
+ <indexterm>
+ <primary>schema</primary>
+ <secondary>resolution</secondary>
+ </indexterm>
+
+ <para>
+ 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 <literal>SELECT</literal>, and
+ so forth.
+ But there are less obvious implications when it comes to statements
+ that manipulate <link linkend="ddl-others">database objects</link>:
+ tables, triggers, functions and the like.
+ </para>
+
+ <para>
+ 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 <emphasis>not</emphasis> 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.
+ </para>
+
+ <para>
+ 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
+ <link linkend="sql-createfunction-security">security</link>.
+ For these reasons function bodies can have their own search paths.
+ </para>
+ </sect2>
+
<sect2 id="ddl-schemas-priv">
<title>Schemas and Privileges</title>
--
2.30.2
