Re: 15.7 and 16.3 release notes are exactly the same on postgresql.org
On Thu, Sep 19, 2024 at 02:50:51PM +0100, Ned Grady wrote: > https://www.postgresql.org/docs/release/15.7/ > https://www.postgresql.org/docs/release/16.3/ > > When I view both of the above pages, I see the same set of release notes > image.png > > image.png Uh, they are slightly different in cases were changes apply to only one release, e.g. this is only in 16.3: In initdb's -c option, match parameter names case-insensitively (Tom Lane) The server treats parameter names case-insensitively, so this code should too. This avoids putting redundant entries into the generated postgresql.conf file. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com When a patient asks the doctor, "Am I going to die?", he means "Am I going to die soon?"
Re: Documentation of .pgpass for Unix is incomplete
On Thu, Aug 8, 2024 at 09:44:51AM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/libpq-pgpass.html > Description: > > The documentation of the .pgpass password file is incomplete in the Unix > case (https://www.postgresql.org/docs/16/libpq-pgpass.html): > > It does not mention how the .pgpass File is actually found. One would > assume it uses the getpwent() function to find the current users > homedirectory and locate the .pgpass file there, but this is not the case. > It only looks at the HOME environment variable. > > If you change the user using setuid() and do not change HOME as well, the > file not be found. Or assume you start a DB client as root by using su to > change the user id, things will not work: > > # /bin/su -c "startx -- " - xpos > > This starts X11 and changes to the user xpos, but it does only change the > user id, not $HOME. Well, it is more complicated than checking just HOME because it calls getpwuid_r() if HOME is not set: https://doxygen.postgresql.org/fe-connect_8c.html#a3f49cbb20595c1765bd0db5ff434c9c3 Is it worth going into that detail in the docs? -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Managing SSL Connections
On Thu, Aug 8, 2024 at 08:42:33AM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/ssl-tcp.html > Description: > > It would be handy to see documentation on the process of renewing a > certificate in terms of the impact it would have if you force clients to do > TLS connections. For example is there a way to load a new certificate whilst > the old one is still active to prevent outages or can this only be done in a > cluster setup? Does this blog post help you? https://momjian.us/main/blogs/pgblog/2020.html#July_17_2020 -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: add 'from the command line' to tutorial-createdb.html
On Thu, Aug 1, 2024 at 12:17:15PM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/tutorial-createdb.html > Description: > > https://www.postgresql.org/docs/16/tutorial-createdb.html > > it says ... > >"To create a new database, in this example named mydb, you use the > following command:" > > To make it more clear that this is being executed from the cli (and not the > psql console) I suggest to change this to > >"To create a new database from the command line, in this example named > mydb, you use the following command:" Yes, good idea, patch applied to master, so it will appear in PG 18. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: pg_createsubscriber clarification
Thanks, patch applied to PG 17 and master. --- On Tue, Jul 30, 2024 at 06:02:51PM -0300, Euler Taveira wrote: > On Sun, Jul 28, 2024, at 1:17 PM, Masahiko Sawada wrote: > > I think the term "synchronization phase" comes from the description in > "29.8.1. Initial Snapshot" section[1]. > > > Yes. > > > https://www.postgresql.org/docs/17/logical-replication-architecture.html# > LOGICAL-REPLICATION-SNAPSHOT > > How about the following? > > The main difference between the logical replication setup and > pg_createsubscriber is how they synchronize table data. > pg_createsubscriber doesn't copy the initial table data because it > uses the tables with their initial data on the target server. It only > does the synchronization phase, which ensures each table is brought up > to a synchronized state by applying changes using standard logical > replication. > > > I slightly modified your proposal in the attached patch. > > Thoughts? > > > -- > Euler Taveira > EDB https://www.enterprisedb.com/ > > diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml > b/doc/src/sgml/ref/pg_createsubscriber.sgml > index 87a9d3db28e..6ebf918b636 100644 > --- a/doc/src/sgml/ref/pg_createsubscriber.sgml > +++ b/doc/src/sgml/ref/pg_createsubscriber.sgml > @@ -57,9 +57,10 @@ PostgreSQL documentation > > After a successful run, the state of the target server is analogous to a > fresh logical replication setup. The main difference between the logical > - replication setup and pg_createsubscriber is > the > - initial data copy. It does only the synchronization phase, which ensures > - each table is brought up to a synchronized state. > + replication setup and pg_createsubscriber is > how > + the data synchronization is done. > pg_createsubscriber > + does not copy the initial table data. It does only the synchronization > phase, > + which ensures each table is brought up to a synchronized state. > > > -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Change detail text in last example of 43.5.3. Executing a Command with a Single-Row Result
On Tue, Jul 16, 2024 at 09:11:35AM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/plpgsql-statements.html > Description: > > The last example in the chapter uses a named parameter "username". As a > result, the detail message should use "username" instead of "$1" to match > the runtime behavior. > > I suggest to change this line > > DETAIL: parameters: $1 = 'nosuchuser' > > to > > DETAIL: parameters: username = 'nosuchuser' Wow, this error goes all the way back to Postgres 12, which is our earliest supported release. I have patched all our supported releases with this fix. Thanks. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
pg_createsubscriber clarification
The pg_createsubscriber docs say: https://www.postgresql.org/docs/17/app-pgcreatesubscriber.html After a successful run, the state of the target server is analogous to a fresh logical replication setup. The main difference between the logical replication setup and pg_createsubscriber is the initial data copy. It does only the synchronization phase, which ensures each table is brought up to a synchronized state. What does "it" and "synchronization phase" mean in "It does only the synchronization phase"? Seems this needs improvement. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Need clarification on "field"
On Wed, Jun 5, 2024 at 12:15:45PM +0200, Laurenz Albe wrote: > On Tue, 2024-06-04 at 19:54 +, PG Doc comments form wrote: > > Under page "https://www.postgresql.org/docs/current/limits.html";, below > > limitations are mentioned: > > > > field size - 1 GB > > identifier length - 63 bytes > > > > I understand "identifier" as the name we provide for tables, columns etc. > > > > By the way, what is "field" in Postgresql? I don't see any official > > page/explanation for this. > > The PostgreSQL term would be "attribute". Perhaps we should use that. > Alternatively, what about "column value"? It is perhaps not accurate, > because a Datum need not be stored in a column, but it might be readily > understandable. The way I understand it, we have rows and columns, and each "cell" is a field. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: psql option
On Wed, Apr 10, 2024 at 12:01:39AM +0200, Daniel Gustafsson wrote: > > On 9 Apr 2024, at 18:33, Daniel Gustafsson wrote: > > > >> On 9 Apr 2024, at 16:05, PG Doc comments form > >> wrote: > >> > >> The following documentation comment has been logged on the website: > >> > >> Page: https://www.postgresql.org/docs/16/app-psql.html > >> Description: > >> > >> option -X, > >> > >> the specified comma after the letter X > >> > >> https://www.postgresql.org/docs/current/app-psql.html > > > > > > Nice catch, will fix. > > Committed, turns out the stray comma has been there since 7.3, so backpatched > it all the way. Thanks for the report! Yikes! :-) -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: CREATE ROLE
On Wed, Mar 27, 2024 at 12:52:55AM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/sql-createrole.html > Description: > > https://www.postgresql.org/docs/16/sql-createrole.html > > ROLE role_name > The ROLE clause causes one or more specified existing roles to be > automatically added as members, with the SET option enabled. This in effect > makes the new role a “group”. Roles named in this clause with role-level the > INHERIT attribute will have the INHERIT option enabled in the new > membership. New memberships will have the ADMIN option disabled. > > The following sentence is badly worded: > Roles named in this clause with role-level the INHERIT attribute will have > the INHERIT option enabled in the new membership Yep, fixed in the attached patch, applied back to PG 16 where this typo was created. Thanks for the report. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml index f3b89e7239b..1a875e04a74 100644 --- a/doc/src/sgml/ref/create_role.sgml +++ b/doc/src/sgml/ref/create_role.sgml @@ -309,7 +309,7 @@ in sync when changing the above synopsis! existing roles to be automatically added as members, with the SET option enabled. This in effect makes the new role a group. Roles named in this clause -with role-level the INHERIT attribute will have +with the role-level INHERIT attribute will have the INHERIT option enabled in the new membership. New memberships will have the ADMIN option disabled.
Re: substring start position behavior
On Tue, Mar 26, 2024 at 11:07:07AM -0400, Bruce Momjian wrote: > On Wed, Mar 6, 2024 at 12:42:09AM -0500, Tom Lane wrote: > > I'm kind of inclined to not document this weirdness. I especially > > don't think it's worth giving an example that neither explains the > > "disregarded" bit nor highlights the dependency on L being given. > > I retract my doc patch for this. I did add a C comment about the fact we don't document it, and added a URL to this discussion. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: substring start position behavior
On Wed, Mar 6, 2024 at 12:42:09AM -0500, Tom Lane wrote: > I'm kind of inclined to not document this weirdness. I especially > don't think it's worth giving an example that neither explains the > "disregarded" bit nor highlights the dependency on L being given. I retract my doc patch for this. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Minor typo in Section, 27.2.8.4.
On Fri, Mar 8, 2024 at 09:15:35PM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/warm-standby.html > Description: > > Section, 27.2.8.4. Planning For High Availability is missing the word "the" > as shown below .. > "Such transaction commits may never be completed if any one of THE > synchronous standbys should crash." Agreed, word "the" added to all supported versions. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: substring start position behavior
On Tue, Mar 5, 2024 at 09:47:54PM -0500, dansonlineprese...@gmail.com wrote:
> Thanks for all that context and diff! The odd behavior is indeed in
> the SQL spec. At least I was convinced of that on postgres IRC by
> someone who seemed to have quoted from the spec. I don't think the
> feature ought to be hidden because it's odd.
>
> If I may suggest some specificity to the wording here, I think "start
> values less than 1" would avoid confusion about whether 0 is
> nonpositive or not, and bring attention to the function being
> 1-indexed rather than 0-indexed.
Sure, updated patch attached.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e5fa82c161..f981793f00 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -2783,6 +2783,13 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
specified. Provide at least one of start
and count.
+
+start values less then one specify
+a start position before the first character, and therefore
+count must be two greater than the absolute
+value of start to begin returning characters
+from the start of the string.
+
substring('Thomas' from 2 for 3)
hom
@@ -2794,6 +2801,10 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
substring('Thomas' for 2)
Th
+
+
+substring('Thomas' from -4 for 8)
+Tho
Re: substring start position behavior
On Tue, Feb 27, 2024 at 05:20:23PM +, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/functions-string.html
> Description:
>
> Hey,
>
> I was confused by substring behavior today, when giving 0 as a start
> position. I understand now that string indices are 1-based, have a certain
> flexibility about where to start (allowing negative start positions), and
> that this is defined in the standard SQL spec.
>
> I'm comfy with all this, but I think it'd be nice to have a hint in the pg
> substring docs for nonpositive start positions, so that users don't have to
> have paid for the standard SQL spec to get past this. To me, substring seems
> like a relatively common function with relatively surprising behavior.
I dug into this and quickly became as confused as you were. The best
explanation I found of the current behavior is here (with diagram):
https://www.mssqltips.com/sqlservertutorial/9374/sql-substring-function/
SELECT SUBSTRING('Hello world',-2,5) as msg
The last Postgres community discussion of this behavior I could find was
from 2007:
https://www.postgresql.org/message-id/flat/12803.1168804636%40sss.pgh.pa.us#8316fb2298c9e49f77867a1ae2ead447
This web page explains the feature:
https://stackoverflow.com/questions/33462061/sql-server-substring-position-negative-value
but also asks:
now the only question that remains is, "why would anyone need it
to behave this way?"
and the answer given is:
@mao47 Well, it depends. I am not an author of implementation of
SUBSTR but I guess with negative index it behaves like LEFT(string,
LEN(string) - 1 - index). It works the same way in PostgreSQL so maybe
it is SQL standard.
Informix has substring() which matches the SQL standard, and substr()
which uses negative start from the end of the string:
https://www.ibm.com/docs/en/informix-servers/14.10?topic=smf-substr-function
Oracle doesn't have substring(), just substr(), and matches Informix
behavior, I think.
I have developed the attached doc patch to document this. The only
question is whether this substring behavior is so odd that we should not
document it.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e5fa82c161..45553ab824 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -2783,6 +2783,13 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
specified. Provide at least one of start
and count.
+
+Non-positive start values specify a
+start position before the first character, and therefore the
+count must be two greater than the absolute
+value of start to begin returning characters
+from the start of the string.
+
substring('Thomas' from 2 for 3)
hom
@@ -2794,6 +2801,10 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
substring('Thomas' for 2)
Th
+
+
+substring('Thomas' from -4 for 8)
+Tho
Re: CREATE ROLE inheritance details
On Tue, Jan 30, 2024 at 01:49:42PM -0700, David G. Johnston wrote: > Almost there I think. The comment regarding seeing GRANT really applies to IN > ROLE, ROLE, and ADMIN. Repeating it seemed a poor choice and upon thinking > further it really makes sense to consider how this command and GRANT work > together part of the description of create role. So I moved that commentary > to > a new description paragraph. I liked all your changes so applied your composite patch. You can see the results here: https://momjian.us/pgsql_docs/ These improvements will appear in the PG 16 minor release which will be packaged on Monday. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: CREATE ROLE inheritance details
On Sun, Jan 28, 2024 at 10:11:18AM -0700, David G. Johnston wrote: > On Fri, Jan 26, 2024 at 5:18 PM Bruce Momjian wrote: > > + Role membership with the inherit attribute can automatically use > + whatever database privileges have been granted to all roles it > + is directly or indirectly a member of, though the chain stops > + at memberships lacking the inherit attribute. Without role > + inheritance, the only other value of membership is the use of > + SET ROLE, assuming the membership chain has > + SET attributes. > > > > I really think it is clearer if we consistently call the property attached to > the membership an "option" (the grant command calls them options) and restrict > the word attribute to only when talking about the role property. The > following > refers to the option. I dislike burying this description about how the option > works within create role like this. It is already in chapter 22 and this > attribute description should point the reader there, not repeat the > information. Okay, I made these changes in the attached version of the patch. > > In PostgreSQL versions before 16, > - the GRANT statement did not support > - WITH INHERIT. Therefore, changing this role-level > - property would also change the behavior of already-existing grants. > - This is no longer the case. > + inheritance was a role-level attribute. It could not be specified > + during role addition with GRANT, and changing > + this role-level property would also change the inheritance behavior > + of all existing memberships. This is no longer the case. > > > My first reaction to the wording here is negative. > > I agree that the pre-v16 behavior dynamic should be documented but maybe l > eave a note with a bit more detail in chapter 22 and leave the following in > place here: > > Prior to version 16 this attribute directly controlled runtime privilege > inheritance instead of now only providing a default for when role membership > is > established. Okay, I simplified the explaination. > @@ -285,9 +294,13 @@ in sync when changing the above synopsis! > > The IN ROLE clause causes the new role to > be automatically added as a member of the specified existing > - roles. (Note that there is no option to add the new role as an > - administrator; use a separate GRANT command > - to do that.) > + roles. The new membership will have the SET > + option enabled and the ADMIN option disabled. > + The INHERIT option will be enabled unless the > + NOINHERIT attribute is specified. See the + linkend="sql-grant"/> command, which has additional attribute > + control during membership creation and to modify these options > + after the new role is created. > > > additional attribute control s/b additional option control Done > @@ -307,10 +324,10 @@ in sync when changing the above synopsis! > ADMIN role_name > > > > - The ADMIN clause is like ROLE, > - but the named roles are added to the new role WITH ADMIN > - OPTION, giving them the right to grant membership in this > role > - to others. > + The ADMIN clause is similar to > + ROLE, but the named roles are added as members > + of the new role with ADMIN enabled, giving > + them the right to grant membership in this role to others. > > > > > I was trying to be explicitly clear that the ADMIN clause is effectively > additive to what ROLE does. "similar to + but" makes it easier to interpret > as > something that only controls ADMIN, not SET or INHERIT. Okay, I used new wording. > The ADMIN clause behaves like ROLE but the ADMIN option is enabled. > > And modify ROLE as such: > > + INHERIT enabled in the new membership. New > + memberships will have the ADMIN option disabled. > + Use the ADMIN clause instead if you want the admin option enabled. > > This variant of the GRANT command grants membership > - in a role to one or more other roles. Membership in a role is significant > + in a role to one or more other roles, and the modification of > + membership attributes. Membership in a role is significant > > and (allows) the modification of three membership options, set, inherit, and > admin, described in chapter 22. Membership in a role is significant... Done. > To modify that attributes of > + an existing membership, simply specify the membership with
Re: CREATE ROLE inheritance details
On Wed, Jan 17, 2024 at 02:32:47PM -0700, David G. Johnston wrote: > I had a go at this. > > I went with a more "bullet item" approach with my wording for INHERIT/ > NOINHERIT. I tried to address that. > The entire paragraph regarding how the INHERIT "option" works, as opposed to > the attribute, seems out of place where it was and the material is already > covered in the GRANT page. We should either improve that page or extract this > level of detail somewhere else, not try to clutter up the CREATE ROLE page > with > it. Because the CREATE ROLE is the way to create roles, and GRANT does much more than just grant membership, I ended up moving the details from the GRANT page to the CREATE ROLE page. > We consistently say what the default is for these attribute pairs, do so here > as well. I added that, I hope in all the right places. > Turn the parenthetical in the IN ROLE section into actual assertive > documentation of what the clause does. Tweak ROLE and ADMIN as well to fit in > better. Yes, that needed help. > Reword the discussion regarding non-inheritance to be more direct. > > I added mention of the grantee aspect of privileges as a soft way of further > pointing out that the IN ROLE, ROLE, ADMIN clauses are limited in what they > can > control in the resulting membership grants. > > I choose to use the phrasing "giving ... roles" for both parts of the sentence > instead of switching to "... roles are given" for the second half. More of a > style choice but I didn't think switching really added much and just makes it > a > bit wordier and possibly a bit more effort to mentally parse. I adjusted that working, but in a different way. Patch attached. I also found we didn't document that GRANT can be used to modify a membership's attributes even after it is created; I added that. Just a reminder, this is for PG 16 and master. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml index 8dd2a6395c..f2dc841b85 100644 --- a/doc/src/sgml/ref/create_role.sgml +++ b/doc/src/sgml/ref/create_role.sgml @@ -133,24 +133,33 @@ in sync when changing the above synopsis! NOINHERIT -When the GRANT statement is used to confer -membership in one role to another role, the GRANT -may use the WITH INHERIT clause to specify whether -the privileges of the granted role should be inherited -by the new member. If the GRANT statement does not -specify either inheritance behavior, the new GRANT -will be created WITH INHERIT TRUE if the member -role is set to INHERIT and to -WITH INHERIT FALSE if it is set to -NOINHERIT. +This affects the membership inheritance status when this +role is added as a member of another role, both in this and +future commands. Specifically, it controls the inheritance +status of memberships added with this command using the +IN ROLE clause, and in later commands using +the ROLE clause. It is also used as the +default inheritance status when adding this role as a member +using the GRANT command. If not specified, +INHERIT is the default. + + + +Role membership with the inherit attribute can automatically use +whatever database privileges have been granted to all roles it +is directly or indirectly a member of, though the chain stops +at memberships lacking the inherit attribute. Without role +inheritance, the only other value of membership is the use of +SET ROLE, assuming the membership chain has +SET attributes. In PostgreSQL versions before 16, -the GRANT statement did not support -WITH INHERIT. Therefore, changing this role-level -property would also change the behavior of already-existing grants. -This is no longer the case. +inheritance was a role-level attribute. It could not be specified +during role addition with GRANT, and changing +this role-level property would also change the inheritance behavior +of all existing memberships. This is no longer the case. @@ -285,9 +294,13 @@ in sync when changing the above synopsis! The IN ROLE clause causes the new role to be automatically added as a member of the specified existing -roles. (Note that there is no option to add the new role as an -administrator; use a separate GRANT command -to do that.) +roles. The new membership will have the SET +option enabled and the ADMIN option
Re: CREATE ROLE inheritance details
On Wed, Jan 17, 2024 at 09:28:38AM +0100, Laurenz Albe wrote: > On Tue, 2024-01-16 at 17:03 -0500, Bruce Momjian wrote: > > I am unhappy with the documentation adjustments made to CREATE ROLE in > > Postgres 16 by this commit: > > > > commit e3ce2de09d > > > > I have attached a patch to re-add this information, and clarify it. I > > would like to apply this to PG 16 and master. > > I had to read the text twice before I understood it, but I cannot think > of a simpler way to write it. Yeah, I had the same feeling. A bullet list would be nice but overkill for a manual page. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
CREATE ROLE inheritance details
I am unhappy with the documentation adjustments made to CREATE ROLE in Postgres 16 by this commit: commit e3ce2de09d Author: Robert Haas Date: Thu Aug 25 10:06:02 2022 -0400 Allow grant-level control of role inheritance behavior. The GRANT statement can now specify WITH INHERIT TRUE or WITH INHERIT FALSE to control whether the member inherits the granted role's permissions. For symmetry, you can now likewise write WITH ADMIN TRUE or WITH ADMIN FALSE to turn ADMIN OPTION on or off. If a GRANT does not specify WITH INHERIT, the behavior based on whether the member role is marked INHERIT or NOINHERIT. This means that if all roles are marked INHERIT or NOINHERIT before any role grants are performed, the behavior is identical to what we had before; otherwise, it's different, because ALTER ROLE [NO]INHERIT now only changes the default behavior of future grants, and has no effect on existing ones. Patch by me. Reviewed and testing by Nathan Bossart and Tushar Ahuja, with design-level comments from various others. Discussion: http://postgr.es/m/CA+Tgmoa5Sf4PiWrfxA=sGzDKg0Ojo3dADw=wAHOhR9dggV=r...@mail.gmail.com It seems to have removed important details about how inherit works beyond GRANT. Using the attached script, test_inh.sh, I generated SQL queries contained in attached file test_inh.sql, and this generated this output showing that the inheritance setting of the role being added as a member controls the inheritance status of the membership: rolname | Is member of role | inherit_option --+---+ init_1_noinherit | user_1_inherit| f init_1_noinherit | user_2_noinherit | f init_1_noinherit | user_3_inherit| f init_1_noinherit | user_4_noinherit | f init_2_inherit | user_5_inherit| t init_2_inherit | user_6_noinherit | t init_2_inherit | user_7_inherit| t init_2_inherit | user_8_noinherit | t user_1_inherit | init_3_noinherit | t user_2_noinherit | init_3_noinherit | f user_3_inherit | init_4_inherit| t user_4_noinherit | init_4_inherit| f user_5_inherit | init_3_noinherit | t user_6_noinherit | init_3_noinherit | f user_7_inherit | init_4_inherit| t user_8_noinherit | init_4_inherit| f I have attached a patch to re-add this information, and clarify it. I would like to apply this to PG 16 and master. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. test_inh.sh Description: Bourne shell script test_inh.sql Description: application/sql diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml index 8dd2a6395c..5c2b71bf69 100644 --- a/doc/src/sgml/ref/create_role.sgml +++ b/doc/src/sgml/ref/create_role.sgml @@ -133,16 +133,24 @@ in sync when changing the above synopsis! NOINHERIT -When the GRANT statement is used to confer -membership in one role to another role, the GRANT -may use the WITH INHERIT clause to specify whether -the privileges of the granted role should be inherited -by the new member. If the GRANT statement does not -specify either inheritance behavior, the new GRANT -will be created WITH INHERIT TRUE if the member -role is set to INHERIT and to -WITH INHERIT FALSE if it is set to -NOINHERIT. +This controls the membership inheritance status when a new role +is added as a member using the IN ROLE clause, +when this role is later added as a member of a new role with the +ROLE clause, and the default inheritance when +adding this role as a member using the GRANT +statement. In such GRANT statements, the +role's inheritance status will be used unless overridden by the +GRANT WITH INHERIT clause. + + + +Role membership with the inherit attribute can automatically +use whatever database privileges have been granted to all +roles it is directly or indirectly a member of, though the +chain stops at memberships without the inherit attribute. +Without role inheritance, the only other value of membership +is via SET ROLE, assuming the membership has +the SET attribute. @@ -286,8 +294,8 @@ in sync when changing the above synopsis! The IN ROLE clause causes the new role to be automatically added as a member of the specified existing roles. (Note t
Re: initdb username doc bug
On Wed, Jan 10, 2024 at 06:05:17PM +0100, Álvaro Herrera wrote: > On 2024-Jan-10, Bruce Momjian wrote: > > > On Mon, Jan 8, 2024 at 04:50:19PM -0500, Tom Lane wrote: > > > Bruce Momjian writes: > > > > Agreed, updated patch attached. > > > > > > WFM. > > > > Patch applied back to PG 16, which is where the erroneous text was > > added. > > Thank you! It looks good to me too. Sorry I got a bit too late to this > thread. Sure, no problem, we got it! -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: initdb username doc bug
On Mon, Jan 8, 2024 at 04:50:19PM -0500, Tom Lane wrote: > Bruce Momjian writes: > > Agreed, updated patch attached. > > WFM. Patch applied back to PG 16, which is where the erroneous text was added. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: initdb username doc bug
On Sat, Jan 6, 2024 at 11:27:14PM -0500, Tom Lane wrote: > "David G. Johnston" writes: > > Or just the bit more verbose “user running the initdb command” and don’t > > bother giving it a label, which is basically the approach used in the > > description for initdb anyway. > > Or if you want a few more words, "name of the operating-system user > running initdb". I don't like "installation user", that's just about > as vague as could be. Agreed, updated patch attached. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/ref/initdb.sgml b/doc/src/sgml/ref/initdb.sgml index d43c91575c..cd75cae10e 100644 --- a/doc/src/sgml/ref/initdb.sgml +++ b/doc/src/sgml/ref/initdb.sgml @@ -417,10 +417,10 @@ PostgreSQL documentation --username=username -Selects the user name of the +Sets the user name of the bootstrap superuser. -This defaults to the name of the -cluster owner. +This defaults to the name of the operating-system user running +initdb.
Re: initdb username doc bug
On Sat, Jan 6, 2024 at 08:11:14PM -0700, David G. Johnston wrote: > On Saturday, January 6, 2024, Bruce Momjian wrote: > > This commit: > > commit 910cab820d > Author: Alvaro Herrera > Date: Fri Nov 18 11:59:26 2022 +0100 > > Add glossary entries related to superusers > > Extracted from a more ambitious patch. > > Author: David G. Johnston > Discussion: https://postgr.es/m/CAKFQuwZC4K0XYBm0bwBMDOZySBqhO > sekdhluaw4vpi+ozi8...@mail.gmail.com > > used this text for the --username option: > > -U username > --username=username > Selects the user name of the bootstrap superuser. This defaults > to the name of the cluster owner. > > This seems wrong since the cluster owner doesn't exist until the cluster > is created. The text exists in PG 16 and master. The attached patch > fixes this. > > > The glossary defines cluster owner as the pre-existing operating system user. No, it does not. > There may be an argument that installation user is a better term but the > existing choice isn’t wrong. If you are going to change it you need to update > the glossary as well. The description in initdb uses cluster owner as well. > > I agree we presently use a mix of set and specify in these definitions so the > word choice of selects is out-of-place. The patch says: + + Cluster owner + + --> + The operating system user that owns the --> + data directory + and under which the postgres process is run. + It is required that this user exist prior to creating a new + database cluster. + + + On operating systems with a root user, + said user is not allowed to be the cluster owner. + + + How can you default to be the owner of something that doesn't exist before it is created? Also, the initdb code gets the default username from geteuid(), the effective process owner; man geteuid says: geteuid() returns the effective user ID of the calling process. so it is not the owner of the data dirctory, but the installation user. I am open to saying that it is the owner of the data directory but we would then need to change initdb to do that, and we aren't going to backpatch that. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
initdb username doc bug
This commit: commit 910cab820d Author: Alvaro Herrera Date: Fri Nov 18 11:59:26 2022 +0100 Add glossary entries related to superusers Extracted from a more ambitious patch. Author: David G. Johnston Discussion: https://postgr.es/m/cakfquwzc4k0xybm0bwbmdozysbqhosekdhluaw4vpi+ozi8...@mail.gmail.com used this text for the --username option: -U username --username=username Selects the user name of the bootstrap superuser. This defaults to the name of the cluster owner. This seems wrong since the cluster owner doesn't exist until the cluster is created. The text exists in PG 16 and master. The attached patch fixes this. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/ref/initdb.sgml b/doc/src/sgml/ref/initdb.sgml index d43c91575c..17363c63b7 100644 --- a/doc/src/sgml/ref/initdb.sgml +++ b/doc/src/sgml/ref/initdb.sgml @@ -417,10 +417,9 @@ PostgreSQL documentation --username=username -Selects the user name of the +Sets the user name of the bootstrap superuser. -This defaults to the name of the -cluster owner. +This defaults to the name of the installation user.
Re: [DOCS] Add example about date ISO format
On Thu, Dec 7, 2023 at 02:18:28PM -0500, Bruce Momjian wrote: > Yes, there were a few problems with my initial patch. First, I should > have used "Z" instead of a Z which could be interpreted as a format > specification; I had already done that for "T" in the example. > > Second, I was missing the trailing "Z" in the output --- not sure how > that happened. > > Erik and Alvaro made a great point --- we are using a literal "Z" as the > time zone output specification, but as Alvaro pointed out, there is no > way to get the time zone _name_ or even the offset from the AT TIME ZONE > value, so we must just pass the literal "Z" from the input to the output. > This proves Alvaro's point on this issue: > > SELECT pg_typeof(current_timestamp AT TIME ZONE 'America/Santiago'); > pg_typeof > - >timestamp without time zone > > Updated patch attached. Thank you for the feedback. Patch applied to master. Thanks for all the suggestions. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: unclear wording re: spoofing prevention on network connections
On Fri, Dec 8, 2023 at 05:42:27PM +, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/preventing-server-spoofing.html
> Description:
>
> When I read:
> To prevent spoofing on TCP connections, either use SSL certificates and make
> sure that clients check the server's certificate, or use GSSAPI encryption
> (or both, if they're on separate connections).
>
> It takes some thought to figure out what "separate connections" are being
> referred to. Does it mean separate TLS connection and
> non-tls-with-gssapi-encryption?
I have no idea. It was added in this commit:
commit b0b39f72b9
Author: Stephen Frost
Date: Wed Apr 3 15:02:33 2019 -0400
GSSAPI encryption support
On both the frontend and backend, prepare for GSSAPI encryption
support by moving common code for error handling into a separate
file.
Fix a TODO for handling multiple status messages in the process.
Eliminate the OIDs, which have not been needed for some time.
...
I have CC'ed the patch author.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
Re: [DOCS] Confusing Trigger Docs.
On Sun, Nov 26, 2023 at 08:59:02PM +0100, Laurenz Albe wrote: > On Fri, 2023-11-24 at 13:14 -0500, Bruce Momjian wrote: > > On Thu, Nov 23, 2023 at 08:36:34AM +0100, Laurenz Albe wrote: > > > On Wed, 2023-11-22 at 14:49 -0800, Peter Geoghegan wrote: > > > > I don't think that your proposed wording for this is an improvement. > > > > > > Well, the existing wording is impenetrable even for someone with some > > > PostgreSQL knowledge, like me. > > > > I moved the partition paragraph to a more logical location and tried to > > clarify the new paragraph to be more targeted on the goal, patch > > attached. > > I cannot tell if that covers everything that the original text (which > I failed to understand) did, but your wording makes sense to me. Patch applied to master. It doesn't have everything that the previous docs had, but I considered that an improvement. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases
On Mon, Nov 20, 2023 at 08:20:57PM -0700, David G. Johnston wrote: > On Mon, Nov 20, 2023 at 8:16 PM Bruce Momjian wrote: > > On Mon, Nov 20, 2023 at 07:19:39PM -0700, David G. Johnston wrote: > > The placement in the numbered listing section feels wrong, I am OK with > > the wording. It should be down in the clause details. > > > > FOR lock_strength [ OF from_reference [, ...] ] [ NOWAIT | SKIP LOCKED > ] > -- > > need to change this spot to match > > > > where lock_strength can be one of > > > > [...] > > > > + and from_reference must be a table alias or non-hidden table_name > referenced > > in the FROM clause. > > > > For more information on each [...] > > Ah, good point. I was searching for "FOR UPDATE" so I missed that > section; updated patch attached. > > > > WFM. Patch applied to master. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: [DOCS] intagg.sgml: example wrongly named and does not compile
On Wed, Nov 22, 2023 at 10:19:57AM +0100, Laurenz Albe wrote: > On Tue, 2023-11-21 at 22:27 -0500, Bruce Momjian wrote: > > I like this six year old patch so would like to apply it to master, > > attached. > > +1, since it is arguably a bug fix. Patch applied to master. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: [DOCS] Add example about date ISO format
On Mon, Nov 27, 2023 at 04:52:20PM +0100, Erik Wienhold wrote:
> On 2023-11-27 15:22 +0100, Alvaro Herrera wrote:
> > On 2023-Nov-27, Erik Wienhold wrote:
> >
> > > Two issues that I fixed in the attached patch:
> > >
> > > * it's called "extended format" not "T format" (the "T" is mandatory
> > > anyway)
> >
> > +1
> >
> > > * the time zone was missing from the result output
> >
> > This is wrong. Actually, there's no timezone in value, because the use
> > of the AT TIME ZONE operator makes the type be TIMESTAMP WITHOUT TIME
> > ZONE. You would notice this if you were to change the incorrect literal
> > Z in your format string with "TZ" (which expands to empty), with "OF"
> > (which expands to "+00"), or with "TZH:TZM" (which expands to the full
> > timezone shift):
>
> Thanks for the explanation. Bruce used literal Z in the format string.
> I just corrected the sample output to match the format.
>
> Or we just use current_timestamp along with pattern TZH:TZM which also
> adds less to the width of the displayed table in case Bruce is concerned
> about that ;)
Yes, there were a few problems with my initial patch. First, I should
have used "Z" instead of a Z which could be interpreted as a format
specification; I had already done that for "T" in the example.
Second, I was missing the trailing "Z" in the output --- not sure how
that happened.
Erik and Alvaro made a great point --- we are using a literal "Z" as the
time zone output specification, but as Alvaro pointed out, there is no
way to get the time zone _name_ or even the offset from the AT TIME ZONE
value, so we must just pass the literal "Z" from the input to the output.
This proves Alvaro's point on this issue:
SELECT pg_typeof(current_timestamp AT TIME ZONE 'America/Santiago');
pg_typeof
-
timestamp without time zone
Updated patch attached. Thank you for the feedback.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 20da3ed033..cec21e42c0 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -8748,6 +8748,12 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
to_char(current_timestamp, 'FMDay, FMDD HH12:MI:SS')
'Tuesday, 6 05:39:18'
+
+to_char(current_timestamp AT TIME ZONE
+'UTC', '-MM-DD"T"HH24:MI:SS"Z"')
+'2022-12-06T05:39:18Z',
+ISO 8601 extended format
+
to_char(-0.1, '99.99')
' -.10'
Re: [DOCS] Rewording for ALTER TABLE ... ADD table_constraint_using_index
On Wed, Aug 26, 2015 at 07:31:53PM -0400, Josh Kupershmidt wrote: > I'd like to propose the following slight rewording for the note about ALTER > TABLE ... ADD table_constraint_using_index. The existing text says one may > "install it [the existing index] as an official constraint". > > The "official constraint" phrase sounds like weasel wording to me -- how is a > UNIQUE constraint more "official" than an UNIQUE index? Then "install" makes > it > sound like some installation process is going on that you should be aware of. > I > like "convert" slightly better instead. Nine year old patch, but still valid, so applied to master. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: [DOCS] Confusing Trigger Docs.
On Thu, Nov 23, 2023 at 08:36:34AM +0100, Laurenz Albe wrote: > On Wed, 2023-11-22 at 14:49 -0800, Peter Geoghegan wrote: > > I don't think that your proposed wording for this is an improvement. > > Well, the existing wording is impenetrable even for someone with some > PostgreSQL knowledge, like me. I moved the parition pagagraph to a more logical location and tried to clarify the new paragraph to be more targeted on the goal, patch attached. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index 6e1f370b21..a5390ff644 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -132,29 +132,19 @@ -A statement that targets a parent table in an inheritance or partitioning -hierarchy does not cause the statement-level triggers of affected child -tables to be fired; only the parent table's statement-level triggers are -fired. However, row-level triggers of any affected child tables will be -fired. +If an INSERT contains an ON CONFLICT +DO UPDATE clause, it is possible for row-level +BEFORE INSERT and then +BEFORE UPDATE triggers +to be executed on triggered rows. Such interactions can be +complex if the triggers are not idempotent because change made by +BEFORE INSERT triggers will be +seen by BEFORE UPDATE triggers, +including changes to EXCLUDED columns. -If an INSERT contains an ON CONFLICT -DO UPDATE clause, it is possible that the effects of -row-level BEFORE INSERT triggers and -row-level BEFORE UPDATE triggers can -both be applied in a way that is apparent from the final state of -the updated row, if an EXCLUDED column is referenced. -There need not be an EXCLUDED column reference for -both sets of row-level BEFORE triggers to execute, -though. The -possibility of surprising outcomes should be considered when there -are both BEFORE INSERT and -BEFORE UPDATE row-level triggers -that change a row being inserted/updated (this can be -problematic even if the modifications are more or less equivalent, if -they're not also idempotent). Note that statement-level +Note that statement-level UPDATE triggers are executed when ON CONFLICT DO UPDATE is specified, regardless of whether or not any rows were affected by the UPDATE (and @@ -169,6 +159,14 @@ triggers. + +A statement that targets a parent table in an inheritance or partitioning +hierarchy does not cause the statement-level triggers of affected child +tables to be fired; only the parent table's statement-level triggers are +fired. However, row-level triggers of any affected child tables will be +fired. + + If an UPDATE on a partitioned table causes a row to move to another partition, it will be performed as a DELETE
Re: [DOCS] Add example about date ISO format
On Thu, Nov 23, 2023 at 09:29:39AM +0100, Laurenz Albe wrote:
> On Wed, 2023-11-22 at 11:58 -0500, Bruce Momjian wrote:
> > Okay, I moved it into the "Note" section that talked about ISO 8601
> > output with "T", in the attached patch.
>
> Fine by me, except that I would rather have "returns" or "produces"
> instead of the questionable verb "outputs".
The majority of people seem to want it in table 9.31, so I have moved it
there. It does almost double the width of the displayed table though.
You can see the new output here:
https://momjian.us/tmp/pgsql/functions-formatting.html#FUNCTIONS-FORMATTING-EXAMPLES-TABLE
Patch attached.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 93f068edcf..4c15eed66f 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -8748,6 +8748,12 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
to_char(current_timestamp, 'FMDay, FMDD HH12:MI:SS')
'Tuesday, 6 05:39:18'
+
+to_char(current_timestamp AT TIME ZONE
+'UTC', '-MM-DD"T"HH24:MI:SSZ')
+'2022-12-06T05:39:18',
+ISO 8601 T format
+
to_char(-0.1, '99.99')
' -.10'
Re: [DOCS] Double negation in parameter description
On Wed, Feb 8, 2017 at 03:27:43PM +, a...@robillo.net wrote: > The following documentation comment has been logged on the website: > > Page: > https://www.postgresql.org/docs/9.6/static/sql-refreshmaterializedview.html > Description: > > In the description of the CONCURRENTLY parameter (3rd line/paragraph, the > statement employs double negation which makes it hard to read and > understand. > > "This option may not be used when the materialized view is not already > populated." > > I guess the author would want to mean: > > "This option may only be used on a populated materialized view."? > > Your clarification is much appreciated. I know this is six years old, but fixed in master with the attached patch. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/ref/refresh_materialized_view.sgml b/doc/src/sgml/ref/refresh_materialized_view.sgml index 675d6090f3..7a019162c3 100644 --- a/doc/src/sgml/ref/refresh_materialized_view.sgml +++ b/doc/src/sgml/ref/refresh_materialized_view.sgml @@ -67,7 +67,7 @@ REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] nameWHERE clause. - This option may not be used when the materialized view is not already + This option can only be used when the materialized view is already populated.
Re: [DOCS] Confusing Trigger Docs.
On Wed, Nov 22, 2023 at 10:31:25AM +0100, Laurenz Albe wrote: > I agree that the paragraph you are trying to improve needs it. > > I am not sure about that last sentence you added: > > The modification of > EXCLUDED columns has similar interactions. > > How do you modify an EXCLUDED column? Are you talking about a BEFORE > INSERT trigger? Reading the original text, I get the impression that > it means "the behavior is obvious if you modify a column that is used > with EXCLUDED in the DO UPDATE clause, but it can also happen if that > column is not user with EXCLUDED". > > Perhaps you should omit that sentence for clarity. I think I found out what it trying to say by looking at the INSERT manual page: Note that the effects of all per-row BEFORE INSERT triggers are reflected in excluded values, since those effects may have contributed to the row being excluded from insertion. I modified the attached patch to explain this since it is not really the same as modifying the actual row. Does that add any value? If not, let's remove it. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml new file mode 100644 index 6e1f370..e3ec784 *** a/doc/src/sgml/trigger.sgml --- b/doc/src/sgml/trigger.sgml *** *** 140,160 ! If an INSERT contains an ON CONFLICT ! DO UPDATE clause, it is possible that the effects of ! row-level BEFORE INSERT triggers and ! row-level BEFORE UPDATE triggers can ! both be applied in a way that is apparent from the final state of ! the updated row, if an EXCLUDED column is referenced. ! There need not be an EXCLUDED column reference for ! both sets of row-level BEFORE triggers to execute, ! though. The ! possibility of surprising outcomes should be considered when there ! are both BEFORE INSERT and ! BEFORE UPDATE row-level triggers ! that change a row being inserted/updated (this can be ! problematic even if the modifications are more or less equivalent, if ! they're not also idempotent). Note that statement-level UPDATE triggers are executed when ON CONFLICT DO UPDATE is specified, regardless of whether or not any rows were affected by the UPDATE (and --- 140,160 ! If an INSERT contains an ON ! CONFLICT DO UPDATE clause, it is possible for both ! row-level BEFORE INSERT and ! BEFORE UPDATE triggers to be ! executed on the same row. Surprising outcomes are possible when ! they change rows being inserted/updated; this can happen even if ! the modifications are more or less equivalent, if they're not also ! idempotent. BEFORE INSERT ! triggers can also affect the value of EXCLUDED ! columns that are seen by BEFORE ! UPDATE triggers. ! ! ! ! Note that statement-level UPDATE triggers are executed when ON CONFLICT DO UPDATE is specified, regardless of whether or not any rows were affected by the UPDATE (and
Re: [DOCS] Add example about date ISO format
On Wed, Nov 22, 2023 at 06:26:45PM +0100, Álvaro Herrera wrote: > On 2023-Nov-22, Laurenz Albe wrote: > > > I think the example had best be at "8.5.2. Date/Time Output", in > > doc/src/sgml/datatype.sgml around line 2552. > > Actually, isn't that a strange location? Chapter 8.5.2 is about the > datatype itself, and there's already a cross-link to Section 9.8 for > to_char() stuff. Since this is to_char() that the example wants to add, > I think the to_char reference is a more appropriate place -- probably > table "9.31 to_char Examples". I originally thought it belonged in section 9.8 too, but I think the value of this example is ISO 8601 and I don't see how we can cleanly mention that in table 9.31. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: [DOCS] Add example about date ISO format
On Wed, Nov 22, 2023 at 02:02:02PM +0100, Erik Wienhold wrote: > > > + > > > + > > > +to_char(current_timestamp AT TIME ZONE 'UTC', > > > +'-MM-DD"T"HH24:MI:SSZ') outputs the current UTC > > This might be excessive, but should we have an example with other time > zones? ISO 8601 is not limited to UTC. For example: > -MM-DD"T"HH24:MI:SSOF or -MM-DD"T"HH24:MI:SSTZH:TZM > > Fractional seconds are also possible: -MM-DD"T"HH24:MI:SS,FF6 Uh, I think the goal was to show how to output ISO 8601 output with "T". I assume they can figure out how to customize that. > > > +date/time in ISO 8601 date/time format. > > > + > > > + > > > + > > > > > > > > > > > > > +1 on the idea, but from the context it looks like you added that example > > at the regular expression matching functions. > > > > I think the example had best be at "8.5.2. Date/Time Output", in > > doc/src/sgml/datatype.sgml around line 2552. > > +1 for moving it to section 8.5.2. Okay, I moved it into the "Note" section that talked about ISO 8601 output with "T", in the attached patch. I will apply this only to master since it is not a correction. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index e4a7b07033..4943f63871 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -2472,7 +2472,11 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02' input, but on output it uses a space rather than T, as shown above. This is for readability and for consistency with https://tools.ietf.org/html/rfc3339";>RFC 3339 as - well as some other database systems. + well as some other database systems. The function call + to_char(current_timestamp AT TIME ZONE 'UTC', + '-MM-DD"T"HH24:MI:SSZ') outputs the current + UTC date/time in ISO 8601 format with + T.
Re: [DOCS] Add example about date ISO format
On Fri, Feb 17, 2017 at 04:01:54PM +, juha.musto...@iki.fi wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/9.6/static/functions-formatting.html
> Description:
>
> The documentation should include an example how to format datetime entry
> into most commonly known ISO format. This is a bit tricky as literal
> character needs to included with quotes:
>
> to_char(NOW(), '-MM-DD"T"HH24:MI:SSZ')
I know this is a six-year-old idea, but it is still a good one. I have
developed the attached patch I would like to apply to master.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 93f068edcf..297cafb341 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -8489,6 +8489,14 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
+
+
+to_char(current_timestamp AT TIME ZONE 'UTC',
+'-MM-DD"T"HH24:MI:SSZ') outputs the current UTC
+date/time in ISO 8601 date/time format.
+
+
+
Re: [DOCS] intagg.sgml: example wrongly named and does not compile
On Fri, Apr 21, 2017 at 06:07:13PM +0200, Christophe Courtois wrote: > Le 21/04/2017 à 17:45, Tom Lane a écrit : > > Christophe Courtois writes: > >> I've found out that the example in intagg.sgml is wrongly named: the > >> one-to-many table is a many-to-many. > > Well, it'd depend on how it was used. The example clearly intends that > > it be one-to-many, and I'm not sure it still makes sense without that > > restriction. Maybe better to add a unique constraint on > > one_to_many(left)? > > Perhaps the whole example can be simplified to get rid of the "left" > table, but I didn't intend to rewrite it. > > >> And my colleague Thibaut Madeleine has seen that the "CREATE TABLE > >> right" and "CREATE TABLE left" examples cannot compile due to the > >> reserved words. > > Ouch. Shows you how old this module is :-( > > Indeed. > > >> I propose the attached patch to fix that. > > Um, the attached file seems empty from here. > > Ooops, sorry. It is attached. I like this six year old patch so would like to apply it to master, attached. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/intagg.sgml b/doc/src/sgml/intagg.sgml index 44a766eb4b..29e74ce146 100644 --- a/doc/src/sgml/intagg.sgml +++ b/doc/src/sgml/intagg.sgml @@ -54,20 +54,22 @@ Sample Uses - Many database systems have the notion of a one to many table. Such a table + Many database systems have the notion of a many to many table. Such a table usually sits between two indexed tables, for example: -CREATE TABLE left (id INT PRIMARY KEY, ...); -CREATE TABLE right (id INT PRIMARY KEY, ...); -CREATE TABLE one_to_many(left INT REFERENCES left, right INT REFERENCES right); +CREATE TABLE left_table (id INT PRIMARY KEY, ...); +CREATE TABLE right_table (id INT PRIMARY KEY, ...); +CREATE TABLE many_to_many(id_left INT REFERENCES left_table, + id_right INT REFERENCES right_table); It is typically used like this: -SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right) - WHERE one_to_many.left = item; +SELECT right_table.* +FROM right_table JOIN many_to_many ON (right_table.id = many_to_many.id_right) +WHERE many_to_many.id_left = item; This will return all the items in the right hand table for an entry @@ -76,7 +78,7 @@ SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right) Now, this methodology can be cumbersome with a very large number of - entries in the one_to_many table. Often, + entries in the many_to_many table. Often, a join like this would result in an index scan and a fetch for each right hand entry in the table for a particular left hand entry. If you have a very dynamic system, there is not much you @@ -85,9 +87,9 @@ SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right) CREATE TABLE summary AS - SELECT left, int_array_aggregate(right) AS right - FROM one_to_many - GROUP BY left; + SELECT id_left, int_array_aggregate(id_right) AS rights + FROM many_to_many + GROUP BY id_left; This will create a table with one row per left item, and an array @@ -95,33 +97,35 @@ CREATE TABLE summary AS the array; that's why there is an array enumerator. You can do -SELECT left, int_array_enum(right) FROM summary WHERE left = item; +SELECT id_left, int_array_enum(rights) FROM summary WHERE id_left = item; The above query using int_array_enum produces the same results as -SELECT left, right FROM one_to_many WHERE left = item; +SELECT id_left, id_right FROM many_to_many WHERE id_left = item; The difference is that the query against the summary table has to get only one row from the table, whereas the direct query against - one_to_many must index scan and fetch a row for each entry. + many_to_many must index scan and fetch a row for each entry. On one system, an EXPLAIN showed a query with a cost of 8488 was reduced to a cost of 329. The original query was a join involving the - one_to_many table, which was replaced by: + many_to_many table, which was replaced by: -SELECT right, count(right) FROM - ( SELECT left, int_array_enum(right) AS right -FROM summary JOIN (SELECT left FROM left_table WHERE left = item) AS lefts - ON (summary.left = lefts.left) +SELECT id_right, count(id_right) FROM + ( SELECT id_left, int_array_enum(rights) AS id_right +FROM summary +JOIN (SELECT id FROM left_table + WHERE id = item) AS lefts +ON (summary.id_left = lefts.id) ) AS list - GROUP BY right + GROUP BY id_right ORDER BY count DESC;
Re: [DOCS] The reference to 'atacontrol' on FreeBSD is outdated.
On Wed, Nov 22, 2023 at 03:25:07PM +1300, Thomas Munro wrote: > On Wed, Nov 22, 2023 at 2:12 PM Bruce Momjian wrote: > > On Sat, Aug 12, 2017 at 10:47:00PM +0100, John Ekins wrote: > > > Sure. Camcontrol does indeed cover IDE, SCSI and SAS drives. > > > > > > Replace this line: > > > On FreeBSD, IDE drives can be queried using atacontrol... > > > > > > With this: > > > On FreeBSD, IDE drives can be queried using camcontrol identify... > > > > > > In other words, the query is the same as for SCSI drives. For > > > verification this > > > is what the output looks like: > > > > > > root@bsd11desk:/home/jre # camcontrol identify 0:0:0 | egrep > > > "Feature|write > > > cache" > > > Feature Support Enabled Value Vendor > > > write cacheyes yes > > > > > > I appreciate the Postgresql docs are not meant to be FreeBSD tutorials. > > > > I know this email is six years old, but I have applied this fix to all > > supported Postgres versions. A huge apology for this delay. > > It's funny that we discuss IDE drives at all. They stopped being > manufactured a decade ago and are not supported by modern chipsets or > boards etc[1]. I've been meaning to do something about that, but I > didn't have enough round tuits to research what to write about current > storage technologies... but also in the case of FreeBSD, it doesn't > let you turn the write cache on and off with nvmecontrol yet, but > someone has a relevant patch out (D32700) so I was also waiting for > that to go somewhere before writing in... I don't think it is safe to disable the nvme cache since it is a cache and a staging area. If you turn it off, it will burn out the chips over time. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: [DOCS] Confusing Trigger Docs.
On Thu, Aug 31, 2017 at 09:22:22AM -0700, Peter Geoghegan wrote: > On Thu, Aug 31, 2017 at 6:25 AM, Bruce Momjian wrote: > > On Mon, Jul 3, 2017 at 08:07:10PM +, n...@fairwindsoft.com wrote: > >> The following documentation comment has been logged on the website: > >> > >> Page: https://www.postgresql.org/docs/9.6/static/trigger-definition.html > >> Description: > >> > >> https://www.postgresql.org/docs/devel/static/trigger-definition.html > >> > >> This sentence: > >> > >> "If an INSERT contains an ON CONFLICT DO UPDATE clause, it is > >> possible that > >> the effects of all row-level BEFORE INSERT triggers and all row-level > >> BEFORE > >> UPDATE triggers can both be applied in a way that is apparent from the > >> final > >> state of the updated row, if an EXCLUDED column is referenced." > >> > >> is very hard to digest. > > EXCLUDED.* is exactly what the name suggests -- the tuple that was not > inserted because of a conflict. So, naturally it has the effects of > any before insert trigger, and carries them forward. But you still > have before triggers on the update side. > > Typically, this won't matter at all, because before triggers tend to > be written in an idempotent fashion -- something gets filled in. But I > can imagine cases where it is not idempotent, and apply a before > update trigger modifies the row in a way that is surprising. Just > because ON CONFLICT DO UPDATE was used rather than UPDATE. That's what > the documentation warns about. I know this thread is six years old, but I still found it confusing, so the attached patch tries to simplify it. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml new file mode 100644 index 6e1f370..0615f8a *** a/doc/src/sgml/trigger.sgml --- b/doc/src/sgml/trigger.sgml *** *** 140,160 ! If an INSERT contains an ON CONFLICT ! DO UPDATE clause, it is possible that the effects of ! row-level BEFORE INSERT triggers and ! row-level BEFORE UPDATE triggers can ! both be applied in a way that is apparent from the final state of ! the updated row, if an EXCLUDED column is referenced. ! There need not be an EXCLUDED column reference for ! both sets of row-level BEFORE triggers to execute, ! though. The ! possibility of surprising outcomes should be considered when there ! are both BEFORE INSERT and ! BEFORE UPDATE row-level triggers ! that change a row being inserted/updated (this can be ! problematic even if the modifications are more or less equivalent, if ! they're not also idempotent). Note that statement-level UPDATE triggers are executed when ON CONFLICT DO UPDATE is specified, regardless of whether or not any rows were affected by the UPDATE (and --- 140,158 ! If an INSERT contains an ON ! CONFLICT DO UPDATE clause, it is possible for both ! row-level BEFORE INSERT and ! BEFORE UPDATE triggers to be ! executed on the same row. The possibility of surprising outcomes ! should be considered when they change rows being inserted/updated ! (this can be problematic even if the modifications are more or less ! equivalent, if they're not also idempotent). The modification of ! EXCLUDED columns has similar interactions. ! ! ! ! Note that statement-level UPDATE triggers are executed when ON CONFLICT DO UPDATE is specified, regardless of whether or not any rows were affected by the UPDATE (and
Re: [DOCS] The reference to 'atacontrol' on FreeBSD is outdated.
On Sat, Aug 12, 2017 at 10:47:00PM +0100, John Ekins wrote: > Hi, > > Sure. Camcontrol does indeed cover IDE, SCSI and SAS drives. > > Replace this line: > On FreeBSD, IDE drives can be queried using atacontrol... > > With this: > On FreeBSD, IDE drives can be queried using camcontrol identify... > > In other words, the query is the same as for SCSI drives. For verification > this > is what the output looks like: > > root@bsd11desk:/home/jre # camcontrol identify 0:0:0 | egrep "Feature|write > cache" > Feature Support Enabled Value Vendor > write cacheyes yes > > I appreciate the Postgresql docs are not meant to be FreeBSD tutorials. I know this email is six years old, but I have applied this fix to all supported Postgres versions. A huge apology for this delay. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Documentation of FOR ROLE clause of ALTER DEFAULT PRIVILEGES missing
On Thu, Feb 22, 2018 at 07:50:19PM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: > https://www.postgresql.org/docs/10/static/sql-alterdefaultprivileges.html > Description: > > The documentation of ALTER DEFAULT PRIVILEGES statements seems to lack an > elaboration of the FOR ROLE clause. > > It specifies what restrictions exist (namely "The name of an existing role > of which the current role is a member."), but what the specified role is > actually used for remains a mystery. > > The documentation should read something like: "The default permissions are > only granted on objects created by this role." I know this is five years late, but we fixed this issue and the new docs will appear in Postgres 17: https://www.postgresql.org/docs/devel/sql-alterdefaultprivileges.html We felt the doc rewrite was too extensive to backpatch. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases
On Mon, Nov 20, 2023 at 07:19:39PM -0700, David G. Johnston wrote:
> The placement in the numbered listing section feels wrong, I am OK with
> the wording. It should be down in the clause details.
>
> FOR lock_strength [ OF from_reference [, ...] ] [ NOWAIT | SKIP LOCKED ] --
> need to change this spot to match
>
> where lock_strength can be one of
>
> [...]
>
> + and from_reference must be a table alias or non-hidden table_name referenced
> in the FROM clause.
>
> For more information on each [...]
Ah, good point. I was searching for "FOR UPDATE" so I missed that
section; updated patch attached.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 227ba1993b..9917df7839 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -45,7 +45,7 @@ SELECT [ ALL | DISTINCT [ ON ( expressioncount | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]
-[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
+[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF from_reference [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
where from_item can be one of:
@@ -1577,7 +1577,7 @@ FETCH { FIRST | NEXT } [ count ] {
The locking clause has the general form
-FOR lock_strength [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ]
+FOR lock_strength [ OF from_reference [, ...] ] [ NOWAIT | SKIP LOCKED ]
where lock_strength can be one of
@@ -1591,8 +1591,11 @@ KEY SHARE
-For more information on each row-level lock mode, refer to
-.
+from_reference must be a
+table alias or non-hidden
+table_name referenced
+in the FROM clause. For more information on each
+row-level lock mode, refer to .
Re: SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases
On Fri, Nov 17, 2023 at 03:44:04PM -0700, David G. Johnston wrote:
> I don't like this particular solution to the stated complaint. When a FROM
> entry has an alias it must be referenced via that alias anywhere it is
> referenced in the query - and indeed it is an error to not write the alias in
> your example. It is not an improvement to write [ table_name | alias ] in our
> syntax to try and demonstrate this requirement. If we do want to not say
> "table_name" I suggest we say instead "from_reference" and then just define
> what that means (i.e., an unaliased table name or an alias in the sibling FROM
> clause attached to this level of the query). I like this better anyway on the
> grounds that the thing being referenced can be a subquery or a view as well as
> a table.
Okay, how is the attached patch?
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 227ba1993b..c98ee8f7ee 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -45,7 +45,7 @@ SELECT [ ALL | DISTINCT [ ON ( expressioncount | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]
-[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
+[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF from_reference [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
where from_item can be one of:
@@ -202,7 +202,11 @@ TABLE [ ONLY ] table_name [ * ]
is specified, the
SELECT statement locks the selected rows
against concurrent updates. (See
- below.)
+ below.) from_reference must be a table
+ alias or non-hidden
+ table_name referenced
+ in the FROM clause.
Re: SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases
On Fri, Apr 27, 2018 at 01:47:49PM +, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/9.5/static/sql-select.html
> Description:
>
> In the SELECT statement page the argument type of the (FOR SHARE/UPDATE) OF
> clause is listed to be a table_name. This is not *quite* accurate - it
> should reference the *alias* assigned to the table if one was given. The
> distinction is subtly important, as without this information the
> documentation implies that the choice of rows to lock can only be done
> per-table (i.e. that in a query mentioning the same table twice, *any*
> tuples being pulled from that table would be given the same treatment).
>
> But in fact postgres supports specifying the locking behaviour per-alias,
> which is a really powerful ability. And actually, trying to specify it by
> actual "table name" where an alias has been assigned won't work either.
I can confirm this report from 2018:
CREATE TABLE test ( x INT );
SELECT * FROM test AS t1 JOIN test AS t2 ON (TRUE) FOR UPDATE OF t1;
x | x
---+---
SELECT * FROM test AS t1 JOIN test AS t2 ON (TRUE) FOR UPDATE OF t2;
x | x
---+---
The attached patch documents this.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 227ba1993b..ab3760057c 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -45,7 +45,7 @@ SELECT [ ALL | DISTINCT [ ON ( expressioncount | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]
-[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
+[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF { table_name | alias } [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
where from_item can be one of:
Re: Missing documentation for FETCH FIRST in chapter 7.6
On Wed, Jun 13, 2018 at 04:17:55PM -0300, Euler Taveira wrote:
> 2018-05-16 10:22 GMT-03:00 PG Doc comments form :
> > The documentation of the SELECT statement lists FETCH FIRST/OFFSET as an
> > alternative to the proprietary LIMIT clause.
> >
> > https://www.postgresql.org/docs/current/static/sql-select.html#SQL-LIMIT
> >
> > However, chapter 7.6 about LIMIT/OFFSET does not mention the alternative.
> >
> > https://www.postgresql.org/docs/current/static/queries-limit.html
> >
> Make sense. I propose the attached patch that adds a link to the LIMIT
> section in the SELECT reference page. I also renamed the "number"
> variables to the same names used in the SELECT reference page and put
> OFFSET in a new line (it looks visually better).
I applied the attached patch based on your patch to master. Thanks.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
commit 8127e6e8ad
Author: Bruce Momjian
Date: Fri Nov 17 16:46:43 2023 -0500
doc: update query section to show LIMIT/OFFSET like SELECT
The parameter names were slightly better in SELECT, so make them match.
Reported-by: Euler Taveira
Discussion: https://postgr.es/m/cahe3wgh-eyuablg1vs3qthii1tgws31h-fyegrdda7otous...@mail.gmail.com
Backpatch-through: master
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index 8a4674e5f2..648b283b06 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -1912,7 +1912,8 @@ SELECT a + b AS sum, c FROM table1 ORDER BY sum + c; -- wrong
SELECT select_list
FROM table_expression
ORDER BY ...
- LIMIT { number | ALL } OFFSET number
+ LIMIT { count | ALL }
+ OFFSET start
Re: Documentation of return values of range functions lower and upper
On Thu, Nov 2, 2023 at 06:22:59PM -0400, Bruce Momjian wrote: > On Thu, Nov 2, 2023 at 03:42:53PM +0100, Laurenz Albe wrote: > > On Thu, 2023-11-02 at 10:14 -0400, Bruce Momjian wrote: > > > Better, though "Is the range's upper bound unbounded?" makes me cringe. > > > > > > Oh, yeah, totally cringe, me too. :-) > > > > > > > It is not the bound that is bounded or not, but the range. > > > > > > > > How about "Is the range unbounded at the upper end?" or "Does the range > > > > have no upper bound?" > > > > > > I used your "end" idea to modify the patch, attached. > > > > There are still some loose ends: > > > > - you lost the specification whether it is the upper or the lower bound > > > > - "Infinity" is a literal > > > > - "-Infinity" is a very unlikely value for an upper bound > > > > How about the attached version? > > Agreed, yours is much better. Backpatched to PG 16. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: 'pg_global' cannot be used as default_tablespace.
On Thu, Nov 2, 2023 at 11:08:53AM -0400, Bruce Momjian wrote: > On Thu, Nov 2, 2023 at 09:58:54AM -0400, Tom Lane wrote: > > Bruce Momjian writes: > > > I found a cleaner improvement, attached. > > > > OK by me. Maybe that doesn't make the point strongly enough, > > but we can hope it's enough. > > Agreed. I thought last night about the sentence and realized when we > say pg_global is used for shared system catalogs, we are not saying > other object can't also be put there, so the "only" seemed required, and > once I added that, the second sentence fragment seemed unnecessary. > > Plus, this report is from three years ago and I haven't seen any other > reports since then. > > I also prefer manage-ag.sgml because we already talk about pg_global > there, and hopefully users of GUC tablespaces and CREATE TABLE will see > it there, or at least look for it when they get the error on creation. Patch applied to master. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Typo in documentation - runtime-config-wal
On Fri, Feb 15, 2019 at 07:51:31PM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/10/runtime-config-wal.html > Description: > > This page: https://www.postgresql.org/docs/10/runtime-config-wal.html > (And probably others in other versions) has – I believe – an error in the > description for "wal_writer_delay". > > It says "woken up by an asynchronously committing transaction". I suspect > that should read "synchronously". Uh, is this correction accurate? It seems so to me. After flushing WAL the writer sleeps for the length of time given by wal_writer_delay, unless woken up sooner --> by an asynchronously committing transaction. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: WAL Configuration Grammar Fix
On Fri, Mar 8, 2019 at 10:39:16PM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/11/wal-configuration.html > Description: > > On the WAL Configuration page - > https://www.postgresql.org/docs/current/wal-configuration.html > It reads: "On Linux and POSIX platforms checkpoint_flush_after allows to > force the OS that pages written by the checkpoint should be flushed to disk > after a configurable number of bytes." > > I believe a "you" is missing. I also suggest a more clear sentence > construction might be: > "On Linux and POSIX platforms you can use checkpoint_flush_after to set a > configurable number of bytes after which the OS will be forced to flush to > disk the pages written by the checkpoint." This patch is 4.5 years old, but still valid. Fixed with the attached patch applied to master. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/wal.sgml b/doc/src/sgml/wal.sgml index 4aad0e1a07..184404c6c5 100644 --- a/doc/src/sgml/wal.sgml +++ b/doc/src/sgml/wal.sgml @@ -602,7 +602,7 @@ On Linux and POSIX platforms - allows to force the OS that pages written by the checkpoint should be + allows you to force OS pages written by the checkpoint to be flushed to disk after a configurable number of bytes. Otherwise, these pages may be kept in the OS's page cache, inducing a stall when fsync is issued at the end of a checkpoint. This setting will
Re: User mapping security
On Thu, Nov 9, 2023 at 05:03:33PM -0500, Tom Lane wrote: > Bruce Momjian writes: > > On Tue, Jul 16, 2019 at 02:01:00AM +, PG Doc comments form wrote: > >> I suppose it should be warned on the pages that foreign credentials with be > >> stored as simple text and will be available for viewing in > >> pg_user_mappings. > > > I know this is four years old, but the attached patch documents it. I > > don't think postgresql-fdw needs it since it relies on user mapping and > > discourages passwords in the connection string. > > This is far too alarmist. It ignores the privilege restrictions that > are built into the pg_user_mappings view. Random users can't see > umoptions. True. I wasn't sure how much of an issue it was to expose passwords at the SQL level, but I will drop the idea. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: User mapping security
On Tue, Jul 16, 2019 at 02:01:00AM +, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/10/postgres-fdw.html
> Description:
>
> Regarding the documentation pages
> https://www.postgresql.org/docs/10/postgres-fdw.html?origin_team=T02HEPYKQ
> and https://www.postgresql.org/docs/10/sql-createusermapping.html
>
> I suppose it should be warned on the pages that foreign credentials with be
> stored as simple text and will be available for viewing in pg_user_mappings.
I know this is four years old, but the attached patch documents it. I
don't think postgresql-fdw needs it since it relies on user mapping and
discourages passwords in the connection string.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/ref/create_user_mapping.sgml b/doc/src/sgml/ref/create_user_mapping.sgml
index 55debd5401..e93bfe48f6 100644
--- a/doc/src/sgml/ref/create_user_mapping.sgml
+++ b/doc/src/sgml/ref/create_user_mapping.sgml
@@ -92,7 +92,11 @@ CREATE USER MAPPING [ IF NOT EXISTS ] FOR { user_
This clause specifies the options of the user mapping. The
options typically define the actual user name and password of
the mapping. Option names must be unique. The allowed option
- names and values are specific to the server's foreign-data wrapper.
+ names and values are specific to the server's foreign-data
+ wrapper. Option values, including passwords, are visible in the
+ pg_user_mapping
+ system view.
Re: readability changes to postgres.sgml
On Mon, Aug 19, 2019 at 04:00:13PM -0700, Joshua D. Drake wrote: > Team, > > New version attached with spelling errors fixed. After four years, patch applied to master. I know reviews had trouble reviewing this, and I found git diff --word-diff=color to be very helpful for this patch. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Required locks for ANALYZE
On Mon, Sep 30, 2019 at 04:46:56PM +0900, Aramaki Zyake wrote: > Hi, > > I’m terribly sorry for the delay of response. > > > > >This does not really seem like an improvement. The second formulation is > > >pedantically correct, but also unintelligible. > > > > > > Maybe we could make it say "run in parallel with non-DDL activity" ? > > > > I completely agree with you, therefore, I amended it as follows. > > > > ANALYZE requires only a read lock on the target table, so it can run in > > parallel with non-DDL activity on the table. I know this report is four years old, but attached patch applied to master. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml index 4c79a01eb6..6ed8e7241b 100644 --- a/doc/src/sgml/ref/analyze.sgml +++ b/doc/src/sgml/ref/analyze.sgml @@ -208,7 +208,7 @@ ANALYZE [ ( option [, ...] ) ] [ ANALYZE requires only a read lock on the target table, so it can run in - parallel with other activity on the table. + parallel with other non-DDL activity on the table.
Re: Usage instructions for pg_hba.conf file
On Sat, Oct 28, 2023 at 04:04:47PM -0400, Bruce Momjian wrote: > On Tue, Aug 2, 2022 at 08:08:07PM +, PG Doc comments form wrote: > > 21.1.1 Loading or Reloading the Configuration > > The pg_hba.conf file is read on start-up... > > Note: The preceding statement... > > The system view pg_hba_file_rules can be helpful... > > This is a good point. I moved the reload sections for pg_hba.conf and > pg_ident.conf up near the top of their sections in the attached patch. Patch applied to master. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: "22.3. Template Databases" documentation is incomplete
On Sat, Oct 28, 2023 at 03:14:32PM -0400, Bruce Momjian wrote: > On Sat, Oct 28, 2023 at 02:42:06PM -0400, Tom Lane wrote: > > Bruce Momjian writes: > > > Sorry, I attached a _colorized_ diff; here is a normal one. > > > > The new sentence is fine in isolation, but this doesn't feel like > > a great spot to put it. The preceding several lines are all about > > copying of objects within the database, and this isn't. In > > particular, the antecedent of "it" is unclear and is different > > from what "it" means in the immediately preceding sentence. > > > > It might be best to split the thing out into its own para? > > Something like > > > > action being taken when those databases are created. > > > > > > + > > + However, CREATE DATABASE does not copy > > + database-level GRANT permissions attached to the > > + source database. The new database has default permissions. > > + > > + > > > > There is a second standard system database named > > Sure. Separate paragraph version applied to all supported versions. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Incorrect mention of number of columns?
On Fri, Nov 3, 2023 at 05:04:46PM +, Daniel Westermann (DWE) wrote: > >"Daniel Westermann (DWE)" writes: > >>> Yeah, but populating it would not (unless many of the columns were > >>> NULL). > > >> Ok, but then should the documentation be more precise? It seems a bit odd > >> to > let users create such a table without at least a warning. > > >Given the impact of NULLs, and the fact that usually tables have some > >variable-width columns, I doubt that a creation-time warning could be > >accurate enough to be useful. > > I am not speaking about a warning at creation time, but rather a warning in > the > docs. Something like: Although a table with e.g. 1600 bigint columns can be > created, creating a tuple of more than 8160 bytes will fail. It is in the "limits" docs: https://www.postgresql.org/docs/current/limits.html -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Document target_role param of ALTER DEFAULT PRIVILEGES
On Thu, Nov 2, 2023 at 09:51:58PM -0400, Bruce Momjian wrote: > On Wed, Nov 1, 2023 at 09:31:52PM +0100, Laurenz Albe wrote: > > There is another patch in the current commitfest that addresses a similar > > problem, triggered by another complaint: > > https://commitfest.postgresql.org/45/4633/ > > > > That patch does not cover role membership. Perhaps you could adopt that > > patch and merge the information from your patch into it. > > Agreed. I found this doc report from 2020 with the same confusion about > what target_role means: > > > https://www.postgresql.org/message-id/flat/160003085417.31338.6391977653235501273%40wrigleys.postgresql.org > > I will apply my patch tomorrow/Friday and then work on the commitfest > one. Okay, patch applied to all supported version. I will now work on the similar commitfest item. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Document target_role param of ALTER DEFAULT PRIVILEGES
On Wed, Nov 1, 2023 at 09:31:52PM +0100, Laurenz Albe wrote: > On Wed, 2023-11-01 at 13:09 -0400, Bruce Momjian wrote: > > On Thu, Feb 18, 2021 at 08:16:13AM +0100, Laurenz Albe wrote: > > > On Wed, 2021-02-17 at 15:08 -0500, Jordi Gutiérrez Hermoso wrote: > > > > I just had a confusing moment trying to figure out why my roles > > > > weren't creating tables the way I was expecting them to. I didn't > > > > understand what the `target_role` parameter did. No matter how I try > > > > to read this page, I can't see an explanation for that parameter: > > > > > > > > https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html > > > > > > > > I propose an explanation like the following be added to the > > > > `target_role` parameter: > > > > > > > > This is the role that will create objects with altered default > > > > privileges. Note that changes in default privileges aren't > > > > inherited, so altering the default privileges of created objects > > > > for a parent role will not change the default privileges of > > > > objects created by inherited roles. > > > > > > +1. I cannot count the number of times I have seen people confused by > > > that. > > > > > > Suggested patch attached. > > > > I see how people can be confused. I wrote this patch, which I think is > > simpler than the previously posted one. > > Oh, I had forgotten about this patch. I like your version better. > > There is another patch in the current commitfest that addresses a similar > problem, triggered by another complaint: > https://commitfest.postgresql.org/45/4633/ > > That patch does not cover role membership. Perhaps you could adopt that > patch and merge the information from your patch into it. Agreed. I found this doc report from 2020 with the same confusion about what target_role means: https://www.postgresql.org/message-id/flat/160003085417.31338.6391977653235501273%40wrigleys.postgresql.org I will apply my patch tomorrow/Friday and then work on the commitfest one. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Documentation of return values of range functions lower and upper
On Thu, Nov 2, 2023 at 03:42:53PM +0100, Laurenz Albe wrote: > On Thu, 2023-11-02 at 10:14 -0400, Bruce Momjian wrote: > > Better, though "Is the range's upper bound unbounded?" makes me cringe. > > > > Oh, yeah, totally cringe, me too. :-) > > > > > It is not the bound that is bounded or not, but the range. > > > > > > How about "Is the range unbounded at the upper end?" or "Does the range > > > have no upper bound?" > > > > I used your "end" idea to modify the patch, attached. > > There are still some loose ends: > > - you lost the specification whether it is the upper or the lower bound > > - "Infinity" is a literal > > - "-Infinity" is a very unlikely value for an upper bound > > How about the attached version? Agreed, yours is much better. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: 'pg_global' cannot be used as default_tablespace.
On Thu, Nov 2, 2023 at 09:58:54AM -0400, Tom Lane wrote: > Bruce Momjian writes: > > I found a cleaner improvement, attached. > > OK by me. Maybe that doesn't make the point strongly enough, > but we can hope it's enough. Agreed. I thought last night about the sentence and realized when we say pg_global is used for shared system catalogs, we are not saying other object can't also be put there, so the "only" seemed required, and once I added that, the second sentence fragment seemed unnecessary. Plus, this report is from three years ago and I haven't seen any other reports since then. I also prefer manage-ag.sgml because we already talk about pg_global there, and hopefully users of GUC tablespaces and CREATE TABLE will see it there, or at least look for it when they get the error on creation. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Documentation of return values of range functions lower and upper
On Thu, Nov 2, 2023 at 08:56:13AM +0100, Laurenz Albe wrote:
> On Wed, 2023-11-01 at 18:03 -0400, Bruce Momjian wrote:
> > On Wed, Nov 1, 2023 at 09:40:43PM +0100, Laurenz Albe wrote:
> > > > Yes, I agree this documentation needs help.
> > > >
> > > > For upper/lower(), it is clear that the documentation is better saying
> > > > "unspecified" rather than infinite. The fact that upper/lower_inf()
> > > > returns false for +/-Infinity is quite odd, but should at least be
> > > > documented.
> > > >
> > > > Patch attached. It is odd that +Infinity (vs. Infinity) wasn't
> > > > supported for datetime input until PG 16, but I think we have to say
> > > > +/-infinity vs (blank)/-Infinity.
> > > >
> > > > Patch attached.
> > >
> > > I am unhappy with "unspecified". A NULL value as upper or lower bound
> > > has a very
> > > specific meaning, namely that the range is unbounded in that direction.
> > > This is
> > > a bit confusing, since NULL is typically used for unknown or undefined
> > > values.
> > >
> > > I think it would be better to say "returns NULL if the range is empty or
> > > unbounded"
> > > and "is the range unbounded on the upper end?".
> >
> > I had to go with "Is the multirange's lower bound unbounded?" because
> > the surrounding items use that sentence structure. Patch attached.
>
> Better, though "Is the range's upper bound unbounded?" makes me cringe.
Oh, yeah, totally cringe, me too. :-)
> It is not the bound that is bounded or not, but the range.
>
> How about "Is the range unbounded at the upper end?" or "Does the range
> have no upper bound?"
I used your "end" idea to modify the patch, attached.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c76ec52c55..c2f266ea24 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19905,7 +19905,7 @@ SELECT NULLIF(value, '(none)') ...
Extracts the lower bound of the range (NULL if the
-range is empty or the lower bound is infinite).
+range is empty or unbounded).
lower(numrange(1.1,2.2))
@@ -19923,7 +19923,7 @@ SELECT NULLIF(value, '(none)') ...
Extracts the upper bound of the range (NULL if the
-range is empty or the upper bound is infinite).
+range is empty or unbounded).
upper(numrange(1.1,2.2))
@@ -19991,7 +19991,8 @@ SELECT NULLIF(value, '(none)') ...
boolean
-Is the range's lower bound infinite?
+Is the range's lower end unbounded? A +/-Infinity lower
+bound returns false.
lower_inf('(,)'::daterange)
@@ -20008,7 +20009,8 @@ SELECT NULLIF(value, '(none)') ...
boolean
-Is the range's upper bound infinite?
+Is the range's upper end unbounded? A +/-Infinity upper
+bound returns false.
upper_inf('(,)'::daterange)
@@ -20063,7 +20065,7 @@ SELECT NULLIF(value, '(none)') ...
Extracts the lower bound of the multirange (NULL if the
-multirange is empty or the lower bound is infinite).
+multirange is empty or unbounded).
lower('{[1.1,2.2)}'::nummultirange)
@@ -20081,7 +20083,7 @@ SELECT NULLIF(value, '(none)') ...
Extracts the upper bound of the multirange (NULL if the
-multirange is empty or the upper bound is infinite).
+multirange is empty or unbounded).
upper('{[1.1,2.2)}'::nummultirange)
@@ -20149,7 +20151,8 @@ SELECT NULLIF(value, '(none)') ...
boolean
-Is the multirange's lower bound infinite?
+Is the multirange's lower end unbounded? A +/-Infinity lower
+bound returns false.
lower_inf('{(,)}'::datemultirange)
@@ -20166,7 +20169,8 @@ SELECT NULLIF(value, '(none)') ...
boolean
-Is the multirange's upper bound infinite?
+Is the multirange's upper end unbounded? A +/-Infinity upper
+bound returns false.
upper_inf('{(,)}'::datemultirange)
Re: 'pg_global' cannot be used as default_tablespace.
On Wed, Nov 1, 2023 at 07:34:59PM -0400, Bruce Momjian wrote: > On Wed, Nov 1, 2023 at 07:12:48PM -0400, Tom Lane wrote: > > Bruce Momjian writes: > > > On Wed, Nov 1, 2023 at 06:32:37PM -0400, Tom Lane wrote: > > >> But it *is* permissible, unless we add code to reject it during > > >> SET as Bruce mentioned. Which seems fairly pointless to me. It's not > > >> like there is anything unclear about the CREATE TABLE error message. > > > > > Yeah, from the report I thought something bad happened if you tried to > > > use it and that is why we had to document it. By documenting it we are > > > just giving the user advice before they get the error. I wrote up this > > > minimal patch which might have the right level of detail to avoid > > > errors, if people think this is useful. > > > > I think this will lead to just as much confusion, because people > > will read it and expect that SET will fail. > > > > If we need to document any more than we have now, we should point > > out in the CREATE TABLE man page that you can't create a table in > > the pg_global tablespace. That will cover both this case and the > > case of trying to select pg_global explicitly in the CREATE. > > > > Another idea could be to adjust this bit in manage-ag.sgml: > > > >Two tablespaces are automatically created when the database cluster > >is initialized. The > > - pg_global tablespace is used for shared system > > catalogs. The > > + pg_global tablespace is used for shared system > > catalogs, > > + and cannot be used for user-defined tables. The > >pg_default tablespace is the default tablespace of the > > I like the manage-ag.sgml change myself. I found a cleaner improvement, attached. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml index 192c500994..a02ef632c3 100644 --- a/doc/src/sgml/manage-ag.sgml +++ b/doc/src/sgml/manage-ag.sgml @@ -498,7 +498,7 @@ CREATE TABLE foo(i int); Two tablespaces are automatically created when the database cluster is initialized. The - pg_global tablespace is used for shared system catalogs. The + pg_global tablespace is used only for shared system catalogs. The pg_default tablespace is the default tablespace of the template1 and template0 databases (and, therefore, will be the default tablespace for other databases as well, unless
Re: 'pg_global' cannot be used as default_tablespace.
On Wed, Nov 1, 2023 at 07:12:48PM -0400, Tom Lane wrote: > Bruce Momjian writes: > > On Wed, Nov 1, 2023 at 06:32:37PM -0400, Tom Lane wrote: > >> But it *is* permissible, unless we add code to reject it during > >> SET as Bruce mentioned. Which seems fairly pointless to me. It's not > >> like there is anything unclear about the CREATE TABLE error message. > > > Yeah, from the report I thought something bad happened if you tried to > > use it and that is why we had to document it. By documenting it we are > > just giving the user advice before they get the error. I wrote up this > > minimal patch which might have the right level of detail to avoid > > errors, if people think this is useful. > > I think this will lead to just as much confusion, because people > will read it and expect that SET will fail. > > If we need to document any more than we have now, we should point > out in the CREATE TABLE man page that you can't create a table in > the pg_global tablespace. That will cover both this case and the > case of trying to select pg_global explicitly in the CREATE. > > Another idea could be to adjust this bit in manage-ag.sgml: > >Two tablespaces are automatically created when the database cluster >is initialized. The > - pg_global tablespace is used for shared system > catalogs. The > + pg_global tablespace is used for shared system catalogs, > + and cannot be used for user-defined tables. The >pg_default tablespace is the default tablespace of the I like the manage-ag.sgml change myself. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: 'pg_global' cannot be used as default_tablespace.
On Wed, Nov 1, 2023 at 06:32:37PM -0400, Tom Lane wrote: > "David G. Johnston" writes: > > On Wednesday, November 1, 2023, Bruce Momjian wrote: > >> Did you want an error from the SET command? > > > That would probably be a decent addition but the request was for us to add > > “it is not permissible to specify the pg_global tablespace for either > > default_tablespace or temp_tablespace”. In the tablespace section per the > > request but maybe also within the settings definition section. > > But it *is* permissible, unless we add code to reject it during > SET as Bruce mentioned. Which seems fairly pointless to me. It's not > like there is anything unclear about the CREATE TABLE error message. Yeah, from the report I thought something bad happened if you tried to use it and that is why we had to document it. By documenting it we are just giving the user advice before they get the error. I wrote up this minimal patch which might have the right level of detail to avoid errors, if people think this is useful. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index bd70ff2e4b..7179d6273c 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8706,7 +8706,8 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; The value is either the name of a tablespace, or an empty string -to specify using the default tablespace of the current database. +to specify using the default tablespace of the current database; +pg_global cannot be used. If the value does not match the name of any existing tablespace, PostgreSQL will automatically use the default tablespace of the current database. If a nondefault tablespace @@ -8774,7 +8775,8 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; This variable specifies tablespaces in which to create temporary objects (temp tables and indexes on temp tables) when a -CREATE command does not explicitly specify a tablespace. +CREATE command does not explicitly specify a tablespace; +pg_global cannot be used. Temporary files for purposes such as sorting large data sets are also created in these tablespaces.
Re: 'pg_global' cannot be used as default_tablespace.
On Tue, Nov 10, 2020 at 08:28:08AM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/13/bug-reporting.html > Description: > > Tablespace 'pg_global' is one of the two auto-generated tablespace by > initdb, and 'pg_global' should not be used as the default_tablespace, since > it is used for shared system catalogs. However, none of these information is > recorded in the doc > https://www.postgresql.org/docs/11/runtime-config-client.html and > https://www.postgresql.org/docs/11/manage-ag-tablespaces.html. In case of > some mishandling to use 'pg_global' as a default_tablespace value, it is > better to record it in the doc of > https://www.postgresql.org/docs/11/manage-ag-tablespaces.html. I know this is three years old, but I am now looking at this email can can't see the problem: SET default_tablespace = 'pg_global'; CREATE TABLE test (x int); ERROR: only shared relations can be placed in pg_global tablespace Did you want an error from the SET command? -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Documentation of return values of range functions lower and upper
On Wed, Nov 1, 2023 at 09:40:43PM +0100, Laurenz Albe wrote:
> > Yes, I agree this documentation needs help.
> >
> > For upper/lower(), it is clear that the documentation is better saying
> > "unspecified" rather than infinite. The fact that upper/lower_inf()
> > returns false for +/-Infinity is quite odd, but should at least be
> > documented.
> >
> > Patch attached. It is odd that +Infinity (vs. Infinity) wasn't
> > supported for datetime input until PG 16, but I think we have to say
> > +/-infinity vs (blank)/-Infinity.
> >
> > Patch attached.
>
> I am unhappy with "unspecified". A NULL value as upper or lower bound has a
> very
> specific meaning, namely that the range is unbounded in that direction. This
> is
> a bit confusing, since NULL is typically used for unknown or undefined values.
>
> I think it would be better to say "returns NULL if the range is empty or
> unbounded"
> and "is the range unbounded on the upper end?".
I had to go with "Is the multirange's lower bound unbounded?" because
the surrounding items use that sentence structure. Patch attached.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c76ec52c55..aa82f8dd81 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19905,7 +19905,7 @@ SELECT NULLIF(value, '(none)') ...
Extracts the lower bound of the range (NULL if the
-range is empty or the lower bound is infinite).
+range is empty or unbounded).
lower(numrange(1.1,2.2))
@@ -19923,7 +19923,7 @@ SELECT NULLIF(value, '(none)') ...
Extracts the upper bound of the range (NULL if the
-range is empty or the upper bound is infinite).
+range is empty or unbounded).
upper(numrange(1.1,2.2))
@@ -19991,7 +19991,8 @@ SELECT NULLIF(value, '(none)') ...
boolean
-Is the range's lower bound infinite?
+Is the range's lower bound unbounded? A +/-Infinity lower
+bound returns false.
lower_inf('(,)'::daterange)
@@ -20008,7 +20009,8 @@ SELECT NULLIF(value, '(none)') ...
boolean
-Is the range's upper bound infinite?
+Is the range's upper bound unbounded? A +/-Infinity upper
+bound returns false.
upper_inf('(,)'::daterange)
@@ -20063,7 +20065,7 @@ SELECT NULLIF(value, '(none)') ...
Extracts the lower bound of the multirange (NULL if the
-multirange is empty or the lower bound is infinite).
+multirange is empty or unbounded).
lower('{[1.1,2.2)}'::nummultirange)
@@ -20081,7 +20083,7 @@ SELECT NULLIF(value, '(none)') ...
Extracts the upper bound of the multirange (NULL if the
-multirange is empty or the upper bound is infinite).
+multirange is empty or unbounded).
upper('{[1.1,2.2)}'::nummultirange)
@@ -20149,7 +20151,8 @@ SELECT NULLIF(value, '(none)') ...
boolean
-Is the multirange's lower bound infinite?
+Is the multirange's lower bound unbounded? A +/-Infinity lower
+bound returns false.
lower_inf('{(,)}'::datemultirange)
@@ -20166,7 +20169,8 @@ SELECT NULLIF(value, '(none)') ...
boolean
-Is the multirange's upper bound infinite?
+Is the multirange's upper bound unbounded? A +/-Infinity upper
+bound returns false.
upper_inf('{(,)}'::datemultirange)
Re: Documentation of return values of range functions lower and upper
On Wed, Nov 18, 2020 at 05:28:44PM +0100, Laurenz Albe wrote:
> On Wed, 2020-11-18 at 22:49 +0900, Fujii Masao wrote:
> > On 2020/11/12 17:14, Laurenz Albe wrote:
> >
> > > On Wed, 2020-11-11 at 18:19 +0100, Laurenz Albe wrote:
> > > > > Table 9.54 in page
> > > > > https://www.postgresql.org/docs/current/functions-range.html states
> > > > > that the
> > > > > functions lower and upper return NULL if the requested bound is
> > > > > infinite. If
> > > > > the element type of the range contains the special values infinity and
> > > > > -infinity, this is not correct, as those values are returned if
> > > > > explicitly
> > > > > used as either bound.
> > > > +1
> > > > Perhaps it would be better to say
> > > > NULL if the range is empty or has no lower/upper bound
> >
> > I agree this description looks a bit confusing. But according to the section
> > "Infinite (Unbounded) Ranges" (*1), we already call "lower/upper bound
> > omitted" just infinite. So I don't think the current description is
> > incorrect.
> >
> > (*1)
> > https://www.postgresql.org/docs/devel/rangetypes.html#RANGETYPES-INFINITE
>
> That is correct, but I'd argue that it would be better to clarify the
> paragraph too,
> in particular:
>
> The functions lower_inf and upper_inf test for infinite lower and upper
> bounds of a range, respectively.
>
> should better read
>
> The functions lower_inf and upper_inf test for omitted lower and upper
> bounds of a range, respectively.
>
> The rest of the paragraph is pretty unambiguous.
>
>
> Independent of this, I think that my patch for "upper" and "lower" would make
> the
> documentation clearer.
Yes, I agree this documentation needs help. Look at this output I
verified in PG 11 and master:
SELECT upper('[now,]'::tstzrange);
upper
(null)
SELECT upper('[now,infinity]'::tstzrange);
upper
--
infinity
SELECT upper('[-infinity,-infinity]'::tstzrange);
upper
---
-infinity
SELECT upper_inf('[now,]'::tstzrange);
upper_inf
---
t
SELECT upper_inf('[now,infinity]'::tstzrange);
upper_inf
---
f
SELECT upper_inf('[-infinity,-infinity]'::tstzrange);
upper_inf
---
f
For upper/lower(), it is clear that the documentation is better saying
"unspecified" rather than infinite. The fact that upper/lower_inf()
returns false for +/-Infinity is quite odd, but should at least be
documented.
Patch attached. It is odd that +Infinity (vs. Infinity) wasn't
supported for datetime input until PG 16, but I think we have to say
+/-infinity vs (blank)/-Infinity.
Patch attached.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c76ec52c55..fd390b03ac 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19905,7 +19905,7 @@ SELECT NULLIF(value, '(none)') ...
Extracts the lower bound of the range (NULL if the
-range is empty or the lower bound is infinite).
+range is empty or unspecified).
lower(numrange(1.1,2.2))
@@ -19923,7 +19923,7 @@ SELECT NULLIF(value, '(none)') ...
Extracts the upper bound of the range (NULL if the
-range is empty or the upper bound is infinite).
+range is empty or unspecified).
upper(numrange(1.1,2.2))
@@ -19991,7 +19991,8 @@ SELECT NULLIF(value, '(none)') ...
boolean
-Is the range's lower bound infinite?
+Is the range's lower bound unspecified? A +/-Infinity lower
+bound returns false.
lower_inf('(,)'::daterange)
@@ -20008,7 +20009,8 @@ SELECT NULLIF(value, '(none)') ...
boolean
-Is the range's upper bound infinite?
+Is the range's upper bound unspecified? A +/-Infinity upper
+bound returns false.
upper_inf('(,)'::daterange)
@@ -20063,7 +20065,7 @@ SELECT NULLIF(value, '(none)') ...
Extracts the lower bound of the multirange (NULL if the
-multirange
Re: Document target_role param of ALTER DEFAULT PRIVILEGES
On Thu, Feb 18, 2021 at 08:16:13AM +0100, Laurenz Albe wrote: > On Wed, 2021-02-17 at 15:08 -0500, Jordi Gutiérrez Hermoso wrote: > > I just had a confusing moment trying to figure out why my roles > > weren't creating tables the way I was expecting them to. I didn't > > understand what the `target_role` parameter did. No matter how I try > > to read this page, I can't see an explanation for that parameter: > > > > https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html > > > > I propose an explanation like the following be added to the > > `target_role` parameter: > > > > This is the role that will create objects with altered default > > privileges. Note that changes in default privileges aren't > > inherited, so altering the default privileges of created objects > > for a parent role will not change the default privileges of > > objects created by inherited roles. > > +1. I cannot count the number of times I have seen people confused by that. > > Suggested patch attached. I see how people can be confused. I wrote this patch, which I think is simpler than the previously posted one. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml index f1d54f5aa3..8a6006188d 100644 --- a/doc/src/sgml/ref/alter_default_privileges.sgml +++ b/doc/src/sgml/ref/alter_default_privileges.sgml @@ -137,7 +137,11 @@ REVOKE [ GRANT OPTION FOR ] The name of an existing role of which the current role is a member. - If FOR ROLE is omitted, the current role is assumed. + Default access privileges are not inherited, so member roles + must use SET ROLE to access these privileges, + or ALTER DEFAULT PRIVILEGES must be run for + each member role. If FOR ROLE is omitted, + the current role is assumed.
Re: doc-fix-for-POSIX-Time-Zone-Specifications
On Mon, May 17, 2021 at 11:36:34AM +, tanghy.f...@fujitsu.com wrote: > Hi > > Attached a patch to delete "CURRENT(as of 2020) " description in POSIX Time > Zone Specifications. > I'm not a native English speaker, if my fix is not appropriate or > insufficient, please be kind to share your thought with me. I think "the" was missing, so I added it: As an example, CET-1CEST,M3.5.0,M10.5.0/3 describes the current (as of 2020) timekeeping practice in Paris. This specification --- -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Typo in "43.9.1. Reporting Errors and Messages"?
On Tue, Aug 2, 2022 at 09:49:47AM -0300, Euler Taveira wrote:
> On Sun, Jul 31, 2022, at 8:37 PM, PG Doc comments form wrote:
>
> Towards the end of the "43.9.1. Reporting Errors and Messages" section
> (here
> https://www.postgresql.org/docs/current/plpgsql-errors-and-messages.html#
> PLPGSQL-STATEMENTS-RAISE)
> we have the following sentence:
>
> > If no condition name nor SQLSTATE is specified in a RAISE EXCEPTION
> command, the default is to use ERRCODE_RAISE_EXCEPTION (P0001).
>
> Looking at the list of error codes (here
> https://www.postgresql.org/docs/current/errcodes-appendix.html) I think
> the
> "ERRCODE_RAISE_EXCEPTION (P0001)" is a typo and should remove "ERRCODE_"
> and
> simply read "RAISE_EXCEPTION (P0001)" or perhaps "ERRCODE =
> 'RAISE_EXCEPTION'" since that's how the default behaviour would be written
> in a RAISE statement.
>
> It is referring to the internal constant (see src/backend/utils/errcodes.h).
> It
> was like you are proposing and it was changed in
> 66bde49d96a9ddacc49dcbdf1b47b5bd6e31ead5. Reading the original thread, there
> is
> no explanation why it was changed. Refer to internal names is not good for a
> user-oriented text. I think it would be better to use the condition name (in
> lowercase) like it is referred to in [1]. I mean, change
> ERRCODE_RAISE_EXCEPTION to raise_exception.
>
> [1] https://www.postgresql.org/docs/current/errcodes-appendix.html
Alexander, Michael, can you explain why this commit removed ERRCODE_:
commit 66bde49d96
Author: Michael Paquier
Date: Tue Aug 13 13:53:41 2019 +0900
Fix inconsistencies and typos in the tree, take 10
This addresses some issues with unnecessary code comments, fixes
various
typos in docs and comments, and removes some orphaned structures and
definitions.
Author: Alexander Lakhin
Discussion:
https://postgr.es/m/9aabc775-5494-b372-8bcb-4dfc0bd37...@gmail.com
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
Re: 'value' has special behaviour in alter system
On Tue, Oct 24, 2023 at 02:36:14PM -0400, Bruce Momjian wrote: > On Tue, Oct 24, 2023 at 02:05:53PM -0400, Tom Lane wrote: > > Bruce Momjian writes: > > > I have developed the attached patch to document this. > > > > This seems a little imprecise: > > > > + Values with non-alphanumeric characters must be quoted. > > > > I think accurate is more like "Values that are neither a number > > nor a valid identifier must be quoted." > > > > Also, that para already mentions that the input can be a > > comma-separated list when appropriate, so your add-on para seems > > partially repetitive. I think you could just drop the first > > sentence of it. > > Agreed, updated patch attached. Patch applied to all supported versions. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Usage instructions for pg_hba.conf file
On Tue, Aug 2, 2022 at 08:08:07PM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/14/auth-pg-hba-conf.html > Description: > > On the pg_hba.conf page, > https://www.postgresql.org/docs/current/auth-pg-hba-conf.html, the bulk of > the page involves the format of the file and what the fields mean. There are > a couple of sentences of usage information (how to reload the file after > changes) in the middle of the reference info. It's possible to overlook the > how-to info on the long page if you don't need the details about the file > contents, just that one tip about reloading. > > > The pg_hba.conf file is read on start-up and when the main server process > receives a SIGHUP signal. If you edit the file on an active system, you will > need to signal the postmaster (using pg_ctl reload, calling the SQL function > pg_reload_conf(), or using kill -HUP) to make it re-read the file. > > I suggest moving the usage info to one end of the page or the other, and > adding a subheading to clarify that the page covers more than the syntax of > the file contents. Possibilities: > > 21.1. The pg_hba.conf File > Client authentication is controlled... (same first paragraph as before) > > 21.1.1 Loading or Reloading the Configuration > The pg_hba.conf file is read on start-up... > Note: The preceding statement... > The system view pg_hba_file_rules can be helpful... > > 21.1.2 Contents of pg_hba.conf > The general format of the pg_hba.conf file is a set of records... > > > Example 21.1. Example pg_hba.conf Entries > > or > > 21.1. The pg_hba.conf File > Client authentication is controlled... > The general format of the pg_hba.conf file is a set of records... > > > Example 21.1. Example pg_hba.conf Entries > > 21.1.1 Loading or Reloading the Configuration > The pg_hba.conf file is read on start-up... > Note: The preceding statement... > The system view pg_hba_file_rules can be helpful... This is a good point. I moved the reload sections for pg_hba.conf and pg_ident.conf up near the top of their sections in the attached patch. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml index 2f1bd6fc8a..477f70a65d 100644 --- a/doc/src/sgml/client-auth.sgml +++ b/doc/src/sgml/client-auth.sgml @@ -73,6 +73,35 @@ however; see the configuration parameter. + + The pg_hba.conf file is read on start-up and when + the main server process receives a + SIGHUPSIGHUP + signal. If you edit the file on an + active system, you will need to signal the postmaster + (using pg_ctl reload, calling the SQL function + pg_reload_conf(), or using kill + -HUP) to make it re-read the file. + + + + +The preceding statement is not true on Microsoft Windows: there, any +changes in the pg_hba.conf file are immediately +applied by subsequent new connections. + + + + + The system view + pg_hba_file_rules + can be helpful for pre-testing changes to the pg_hba.conf + file, or for diagnosing problems if loading of the file did not have the + desired effects. Rows in the view with + non-null error fields indicate problems in the + corresponding lines of the file. + + The general format of the pg_hba.conf file is a set of records, one per line. Blank lines are ignored, as is any @@ -733,35 +762,6 @@ openssl x509 -in myclient.crt -noout --subject -nameopt RFC2253 | sed "s/^subjec range of allowed client IP addresses. - - The pg_hba.conf file is read on start-up and when - the main server process receives a - SIGHUPSIGHUP - signal. If you edit the file on an - active system, you will need to signal the postmaster - (using pg_ctl reload, calling the SQL function - pg_reload_conf(), or using kill - -HUP) to make it re-read the file. - - - - -The preceding statement is not true on Microsoft Windows: there, any -changes in the pg_hba.conf file are immediately -applied by subsequent new connections. - - - - - The system view - pg_hba_file_rules - can be helpful for pre-testing changes to the pg_hba.conf - file, or for diagnosing problems if loading of the file did not have the - desired effects. Rows in the view with - non-null error fields indicate problems in the - corresponding lines of the file. - - To connect to a particular database, a user must not only pass the @@ -933,6 +933,28 @@ local db1,db2,@demodbs all md5 As for pg_hba.conf, the lines in this file can be in
Re: "22.3. Template Databases" documentation is incomplete
On Sat, Oct 28, 2023 at 02:42:06PM -0400, Tom Lane wrote: > Bruce Momjian writes: > > Sorry, I attached a _colorized_ diff; here is a normal one. > > The new sentence is fine in isolation, but this doesn't feel like > a great spot to put it. The preceding several lines are all about > copying of objects within the database, and this isn't. In > particular, the antecedent of "it" is unclear and is different > from what "it" means in the immediately preceding sentence. > > It might be best to split the thing out into its own para? > Something like > > action being taken when those databases are created. > > > + > + However, CREATE DATABASE does not copy > + database-level GRANT permissions attached to the > + source database. The new database has default permissions. > + > + > > There is a second standard system database named Sure. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Intro enhancement proposal
On Tue, Aug 9, 2022 at 01:18:25PM +0200, Daniel Gustafsson wrote: > > On 9 Aug 2022, at 13:13, Elena Indrupskaya > > wrote: > > > It occurred to me that adding some cross-references to intro.sgml could add > > value to the PostgreSQL 15 documentation through a possibility to > > immediately figure out what is meant by specific list items and through > > providing even better navigation. Details are in the attached patch. > > I haven't reviewed the patch in detail yet, but skimming it I think that seems > like a good idea. Agreed. I adjusted some of the links and applied the attached patch to master. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/intro.sgml b/doc/src/sgml/intro.sgml index 63eda059f0..fec72481b5 100644 --- a/doc/src/sgml/intro.sgml +++ b/doc/src/sgml/intro.sgml @@ -101,22 +101,22 @@ - complex queries + complex queries - foreign keys + foreign keys - triggers + triggers - updatable views + updatable views - transactional integrity + transactional integrity - multiversion concurrency control + multiversion concurrency control @@ -125,22 +125,22 @@ - data types + data types - functions + functions - operators + operators - aggregate functions + aggregate functions - index methods + index methods - procedural languages + procedural languages
Re: "22.3. Template Databases" documentation is incomplete
On Sat, Oct 28, 2023 at 12:18:50PM -0400, Bruce Momjian wrote: > On Tue, Aug 9, 2022 at 08:33:11PM +, PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/14/manage-ag-templatedbs.html > > Description: > > > > The documentation for creating databases from templates is incomplete, and > > should mention that access privileges are not copied. So, for example, > > issuing: > > > > create database newdb with template existingdb owner someuser; > > > > results in the newly-created database lacking the access privileges that > > exist in the template. > > > > cf: https://www.postgresql.org/docs/13/manage-ag-templatedbs.html > > I have created the attached patch to document this. Sorry, I attached a _colorized_ diff; here is a normal one. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml index 192c500994..b852783a6c 100644 --- a/doc/src/sgml/manage-ag.sgml +++ b/doc/src/sgml/manage-ag.sgml @@ -210,7 +210,8 @@ createdb -O rolename dbnamePL/Perl in template1, it will automatically be available in user databases without any extra - action being taken when those databases are created. + action being taken when those databases are created. It does not copy + database-level GRANT permissions.
Re: "22.3. Template Databases" documentation is incomplete
On Tue, Aug 9, 2022 at 08:33:11PM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/14/manage-ag-templatedbs.html > Description: > > The documentation for creating databases from templates is incomplete, and > should mention that access privileges are not copied. So, for example, > issuing: > > create database newdb with template existingdb owner someuser; > > results in the newly-created database lacking the access privileges that > exist in the template. > > cf: https://www.postgresql.org/docs/13/manage-ag-templatedbs.html I have created the attached patch to document this. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. [1mdiff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml[m [1mindex 192c500994..b852783a6c 100644[m [1m--- a/doc/src/sgml/manage-ag.sgml[m [1m+++ b/doc/src/sgml/manage-ag.sgml[m [36m@@ -210,7 +210,8 @@[m [mcreatedb -O rolename dbnamePL/Perl in template1, it will[m automatically be available in user databases without any extra[m action being taken when those databases are created. [32mIt does not copy[m [32m database-level GRANT permissions.[m [m [m
Re: Typo in docs for "recovery_init_sync_method" parameter.
On Tue, Aug 23, 2022 at 02:38:13PM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/14/runtime-config-error-handling.html > Description: > > The second paragraph contains the sentence: > > > ...to ask the operating system to synchronize the whole file systems > that contain the data directory... > > I believe the word "the" in "the whole file systems" should be removed so it > reads: > > > ...to ask the operating system to synchronize whole file systems that > contain the data directory... I ended up with this rewording, attached, and applied to master. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 4047eefc64..985cabfc0b 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -10554,7 +10554,7 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' On Linux, syncfs may be used instead, to ask the -operating system to synchronize the whole file systems that contain the +operating system to synchronize the file systems that contain the data directory, the WAL files and each tablespace (but not any other file systems that may be reachable through symbolic links). See for more information about using
Re: Minor inconsistencies
On Sun, Nov 6, 2022 at 04:16:07PM +0200, Ekaterina Kiryanova wrote: > Hello! > > I've prepared another patch, which contains the previous corrections and > some new found ones concerning upper/lower case letters, misplaced periods > and missing tags. > Please review. I liked all these improvements and applied a modified version of your patch to master, thanks. Sorry for the delay. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: [very minor] Documentation suggestion
On Fri, Oct 21, 2022 at 02:19:12AM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/logicaldecoding-synchronous.html > Description: > > Very minor suggestion. Earlier on this page, commands are referred to that > can cause "a deadlock". Later on the page, it says "cause deadlock". > > https://www.postgresql.org/account/comments/new/15/logicaldecoding-synchronous.html/ > > "Note that these commands that can cause deadlock" The full sentence is: Note that these commands that can cause deadlock apply to not only explicitly indicated system catalog tables above but also to any other [user] catalog table. What I think it means is: Note that these commands, that can cause deadlock, apply to not only explicitly indicated system catalog tables above but also to any other [user] catalog table. so with "a" it would be: Note that these commands, that can cause a deadlock, apply to not only explicitly indicated system catalog tables above but also to any other [user] catalog table. but it still needs help ;-) Here is the text I just applied: Note that these commands can cause deadlocks not only for the system catalog tables listed above but for other catalog tables. Attached is the applied patch to master. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. commit 16ace6f745 Author: Bruce Momjian Date: Fri Oct 27 11:35:47 2023 -0400 doc: clarify logical decoding's deadlock of system tables Reported-by: andyatkin...@gmail.com Discussion: https://postgr.es/m/166631875298.630.2861049399528658...@wrigleys.postgresql.org Backpatch-through: master diff --git a/doc/src/sgml/logicaldecoding.sgml b/doc/src/sgml/logicaldecoding.sgml index 5af016cfa9..8067946a25 100644 --- a/doc/src/sgml/logicaldecoding.sgml +++ b/doc/src/sgml/logicaldecoding.sgml @@ -1197,9 +1197,8 @@ OutputPluginWrite(ctx, true); - Note that these commands that can cause deadlock apply to not only explicitly - indicated system catalog tables above but also to any other [user] catalog - table. + Note that these commands can cause deadlocks not only for the system + catalog tables listed above but for other catalog tables.
Re: First Person (I) Should be avoided
On Fri, Oct 28, 2022 at 02:56:59AM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/oid2name.html > Description: > > This feels a little nit-picky. > > In the Examples > $ # I wonder what file 155173 is ... > > suggested change: > $ # what Postgres object does file 155173 represent ... Fixed with the attached, applied patch to master. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/oid2name.sgml b/doc/src/sgml/oid2name.sgml index dfe3682739..54cc9be2b8 100644 --- a/doc/src/sgml/oid2name.sgml +++ b/doc/src/sgml/oid2name.sgml @@ -281,7 +281,7 @@ $ ls -lS * | head -10 -rw--- 1 alvherre alvherre163840 sep 14 09:50 16699 -rw--- 1 alvherre alvherre122880 sep 6 17:51 16751 -$ # I wonder what file 155173 is ... +$ # What file is 155173? $ oid2name -d alvherre -f 155173 From database "alvherre": Filenode Table Name
Re: 19.9. Secure TCP/IP Connections with SSL
On Wed, Oct 25, 2023 at 03:44:04PM +, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/ssl-tcp.html
> Description:
>
> There is no mention of key lengths on the manual page about SSL/TLS
> connections even though there are restrictions. It probably depends on the
> build which is why it's been omitted, but I think to help new people it
> would be great to have a Note box that covers key lengths restrictions. It
> came up on reddit today and while the following is in the error log..
>
>FATAL: could not load server certificate file
> "/etc/postgresql/16/main/server.crt": ee key too small
>
> It is amongst a chain of other messages and has a long line such that it
> could be missed as it's truncated (though it should still have been spotted
> of course). Regardless, I like the idea of all the things you need to do/be
> mindful of being in the manual. It could be something like..
>
> Note
> Some builds of PostgreSQL specify a minimum key length for certificates to
> enforce best-practices. If the key you use is does not meet or exceed this
> minimum length PostgreSQL will fail to start. It's common practice to
> require a key of at least length 2048.
I think this is based on the SSL/TLS library in use which is why we
don't mention it in our docs, e.g.:
https://stackoverflow.com/questions/61626206/what-could-cause-dh-key-too-small-error
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
Re: Getting started
xOn Mon, Dec 12, 2022 at 08:20:03AM +0100, Laurenz Albe wrote: > On Sat, 2022-12-10 at 18:46 +, PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/15/user-manag.html > > Description: > > > > https://www.postgresql.org/docs/current/tutorial-createdb.html > > > > The "getting started" page skips right from the installing to already having > > started the program, but on linux machines, you cannot start psql without > > inputting a special command, "sudo -u postgres psql". Typing psql will make > > the program tell you that your username doesn't exist, and you can't create > > a username for psql without starting psql first. I had to search somewhere > > else to learn that I had to start psql with that special command. > > Yes, the "initdb" step is missing. > > If we had that, it would be clear that the rest of the chapter assumes that > you are running shell commands with an operating system user whose name > is identical to the bootstrap superuser. I looked at this and initdb is mentioned in the installation section, and an incorrect user name is mentions as part of craetedb failure. I don't see what we can add to improve things here. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: 'value' has special behaviour in alter system
On Tue, Oct 24, 2023 at 02:05:53PM -0400, Tom Lane wrote:
> Bruce Momjian writes:
> > I have developed the attached patch to document this.
>
> This seems a little imprecise:
>
> + Values with non-alphanumeric characters must be quoted.
>
> I think accurate is more like "Values that are neither a number
> nor a valid identifier must be quoted."
>
> Also, that para already mentions that the input can be a
> comma-separated list when appropriate, so your add-on para seems
> partially repetitive. I think you could just drop the first
> sentence of it.
Agreed, updated patch attached.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/ref/alter_system.sgml b/doc/src/sgml/ref/alter_system.sgml
index 6f8bd39eaf..bea5714ba1 100644
--- a/doc/src/sgml/ref/alter_system.sgml
+++ b/doc/src/sgml/ref/alter_system.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
-ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }
+ALTER SYSTEM SET configuration_parameter { TO | = } { value [, ...] | DEFAULT }
ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM RESET ALL
@@ -83,9 +83,17 @@ ALTER SYSTEM RESET ALL
New value of the parameter. Values can be specified as string
constants, identifiers, numbers, or comma-separated lists of
these, as appropriate for the particular parameter.
+ Values that are neither numbers nor valid identifiers must be quoted.
DEFAULT can be written to specify removing the
parameter and its value from postgresql.auto.conf.
+
+
+ For some list-accepting parameters, quoted values will produce
+ double-quoted output to preserve whitespace and commas; for others,
+ double-quotes must be used inside single-quoted strings to get
+ this effect.
+
Re: 'value' has special behaviour in alter system
On Thu, Dec 15, 2022 at 12:10:27PM -0500, Tom Lane wrote:
> "Jonathan S. Katz" writes:
> > On 12/15/22 10:50 AM, David G. Johnston wrote:
> >> I suggest changing it to:
> >> SET configuration_parameter { TO | = } { value [, ...] | DEFAULT }
>
> > +1 in general. I would also suggest we add an example in the Examples
> > section to show what the output is when you add single-quotes.
>
> I think the core problem here is that the syntax diagram and discussion
> don't clearly discuss the behavior for list values. David's version of
> the syntax diagram looks fine, but not sure about the text. There likely
> needs to be some explicit acknowledgement of the fact that some GUCs
> act differently than others (cf GUC_LIST_INPUT and GUC_LIST_QUOTE flags).
>
> +1 for examples, for sure.
Finally getting back to this, there is a lot going on and Tom is right
that the inconsistent use of GUC_LIST_QUOTE adds a lot of confusion.
Here are the list settings, and which ones add double-quotes to
non-alphanumeric quoted values:
GUC_LIST_QUOTE (adds quotes)
temp_tablespaces
session_preload_libraries
shared_preload_libraries
local_preload_libraries
search_path
unix_socket_directories
NO GUC_LIST_QUOTE (does not add quotes)
DateStyle
createrole_self_grant
log_destination
listen_addresses
synchronous_standby_names
wal_consistency_checking
debug_io_direct
and this leads to different quoting behaviors depending on which
category of list you are using. First, let's look at alphanumeric
values, which are treated the same by list types with different
GUC_LIST_QUOTE statuses:
ALTER SYSTEM SET shared_preload_libraries TO a, b, c;
.conf shared_preload_libraries = 'a, b, c'
ALTER SYSTEM SET listen_addresses TO a, b, c;
.conf listen_addresses = 'a, b, c'
Even with quotes, the output is the same:
ALTER SYSTEM SET shared_preload_libraries TO a, 'b', c;
.conf shared_preload_libraries = 'a, b, c'
ALTER SYSTEM SET shared_preload_libraries TO a, "b", c;
.conf shared_preload_libraries = 'a, b, c'
ALTER SYSTEM SET listen_addresses TO a, 'b', c;
.conf listen_addresses = 'a, b, c'
ALTER SYSTEM SET listen_addresses TO a, "b", c;
.conf listen_addresses = 'a, b, c'
With non-alphanumeric (spaces), there is a difference:
ALTER SYSTEM SET shared_preload_libraries TO a, 'b x', c;
.conf shared_preload_libraries = 'a, "b x", c'
ALTER SYSTEM SET listen_addresses TO a, 'b x', c;
.conf listen_addresses = 'a, b x, c'
For listen_addresses to get the quoting behavior of
shared_preload_libraries, I have to use double-quotes inside single
quotes:
ALTER SYSTEM SET listen_addresses TO 'a, "b x", c';
.conf listen_addresses = 'a, "b x", c'
Do we want to retain this difference in list processing?
I have developed the attached patch to document this.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/ref/alter_system.sgml b/doc/src/sgml/ref/alter_system.sgml
index 6f8bd39eaf..14def69419 100644
--- a/doc/src/sgml/ref/alter_system.sgml
+++ b/doc/src/sgml/ref/alter_system.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
-ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }
+ALTER SYSTEM SET configuration_parameter { TO | = } { value [, ...] | DEFAULT }
ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM RESET ALL
@@ -83,9 +83,18 @@ ALTER SYSTEM RESET ALL
New value of the parameter. Values can be specified as string
constants, identifiers, numbers, or comma-separated lists of
these, as appropriate for the particular parameter.
+ Values with non-alphanumeric characters must be quoted.
DEFAULT can be written to specify removing the
parameter and its value from postgresql.auto.conf.
+
+
+ Parameters that accept lists of strings can specify multiple values
+ separated by commas. For some list-accepting parameters, quoted
+ values will produce double-quoted output to preserve whitespace and
+ commas; for others, double-quotes must be used inside single-quoted
+ strings to get this effect.
+
Re: pg_upgrade doc uses inconsistent versions within the doc.
On Wed, Sep 27, 2023 at 09:42:49AM +0200, Daniel Gustafsson wrote: > > On 26 Sep 2023, at 22:26, Bruce Momjian wrote: > > On Tue, Sep 26, 2023 at 10:56:27AM -0700, David G. Johnston wrote: > > >> I would get rid of any mentions of our old pre-v10 versioning scheme in the > >> current documentation. > > For content such as this, a very big +1. > > > Good point, how is this attached patch? > > LGTM. Patch applied back to PG 16. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Make SSPI documentation clearer
On Thu, Sep 28, 2023 at 09:55:43AM -0400, Bruce Momjian wrote: > On Wed, Sep 27, 2023 at 07:09:02PM -0400, Bruce Momjian wrote: > > On Sun, Mar 12, 2023 at 08:36:53PM -0400, Stephen Frost wrote: > > > > When the server is on a non-Windows platform then the server must use > > > > GSSAPI > > > > if it wants to authenticate the client either via Kerberos or via Active > > > > Directory. A client on a Windows platform that connects to a non-Windows > > > > Postgresql server can either use SSPI (strongly encouraged) or GSS (much > > > > more difficult to set up) if it wants to authenticate via Kerberos or > > > > Active > > > > Directory. A client from a non-Windows platform must use GSS if it > > > > wants to > > > > authenticate via Kerberos or Active Directory." > > > > > > Rather than work in negative, I feel like it might make more sense to > > > work in positives? That is, perhaps this instead: > > > > > > On Windows platforms, SSPI is the default and most commonly used > > > mechanism. Note that an SSPI client can authenticate to a server which > > > is using either SSPI or GSSAPI, and a GSSAPI client can authenticate to > > > a server which is using either SSPI or GSSAPI. Generally speaking, > > > clients and servers on Windows are recommended to use SSPI while clients > > > and servers on Unix (non-Windows) platforms use GSSAPI. > > > > I developed the attached patch. > > My first attempt was too terse, so here is a more detailed version, > attached. Patch applied back to PG 11. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Suggestion for deprecated spellings
On Wed, Sep 27, 2023 at 08:17:54PM -0400, Bruce Momjian wrote: > On Mon, Jan 30, 2023 at 04:17:14PM -0500, Bruce Momjian wrote: > > On Mon, Jan 30, 2023 at 04:07:46PM -0500, Tom Lane wrote: > > > Bruce Momjian writes: > > > > On Thu, Jan 26, 2023 at 12:19:29PM +, PG Doc comments form wrote: > > > >> From time to time some spelling for given command gets obsolete, yet > > > >> it is > > > >> shown in the syntax on "equal rights" as other valid clauses. > > > > > > > We don't need to show all _supported_ syntaxes in the "Synopsis" > > > > section, so we could just remove them. > > > > > > IIRC, there is precedent in COPY for moving obsolete alternatives > > > to a separate part of the man page. I'd prefer that to just > > > removing them, because then there is no documentation to help > > > someone understand what an old SQL script is doing. > > > > Yeah, I remember that with COPY. t.kityn...@gmail.com, please us that > > as a guide. Thanks. > > I developed the attached patch to move the deprecated clauses to the > bottom. Applied to master. I didn't want to backpatch a command syntax adjustment like this. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Certificate authentication docs in multiple places
On Wed, Sep 27, 2023 at 05:28:04PM -0400, Bruce Momjian wrote: > On Tue, Mar 28, 2023 at 04:28:24PM +0200, Peter Eisentraut wrote: > > On 23.03.23 14:33, Steve Atkins wrote: > > > A couple of times recently I’ve been chatting with someone about using > > > certificate authentication, and the docs they’ve found on it are the ones > > > in section 21.12 (Client Authentication -> Certificate Authentication). > > > > > > But the useful documentation about how to set it up and use it is in > > > section 19.9.3 (Secure TCP/IP Connections with SSL -> Using Client > > > Certificates), where you’re less likely to find it while thinking about > > > authentication. > > > > > > Should we add a link from the former to the latter? > > > > Sure, some cross-linking between those two sections seems sensible. > > Attached is a patch which accomplishes this. Patch applied back to PG 11. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Seeming contradiction in 22.1
On Wed, Sep 27, 2023 at 06:11:44PM -0400, Bruce Momjian wrote: > On Thu, Mar 23, 2023 at 05:45:03PM +0100, maja zaloznik wrote: > > Thanks David, this is very helpful! > > > > I would have to agree with Tom, that while technically true, the text could > > be > > reworded. namely it was not obvious to me that i had used a 'packager' to > > install the clusters and therefore was doing something outside the purview > > of > > the postgres documentation. > > > > To me the last sentence would be clearer if it read something like: > > > > "Most packagers will name this role `postgres` by default, but this is not > > required". > > > > That way it avoids the passive tense which leaves some ambiguity as to who > > or > > what and when is naming this superuser. > > I think the paragraph was trying to do too much so I simplified it, > patch attached. Patch applied to PG 16. I didn't go further because this is only a clarification, rather than missing or incorrect information, and the patch didn't cleanly apply to PG 15. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: ODBC options
On Tue, Oct 3, 2023 at 05:34:01PM -0500, Brad White wrote: > I was trying to figure how to set the log directory on my Windows client > using a parameter in the connection string. Short answer is that you can't. > But you can using the DSN settings. > I was under the impression that going DSN-less and using the parameters on the > connection string, we were free from the effects of the DSN settings. > That's not true. > The DSN settings become the default values, overridden by anything in the > connection string. > Conversely, there are two settings which, for reasons that escape me, can't be > set via the connection string. > In the process, I found the documentation on the ODBC settings to be scattered > and inconsistent, so I documented all the settings and their abbreviations in > a > spreadsheet. Is there a useful place or format where I can post that? > > Spreadsheet is here if anyone wants to look at it. > https://www.dropbox.com/scl/fi/v1uj1umtj20k1ljenodvy/ > psqlODBC-Configuration-Options.xlsx?rlkey=fit9kbgy0fv0fr9vt0u0a9oim&dl=0 Please see: https://odbc.postgresql.org/faq.html#1.6 -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Do we really want to mention 8.3 in current versions of the docs?
On Thu, Jan 5, 2023 at 07:05:02PM +, Daniel Westermann (DWE) wrote: > Hi, > > do we really want to reference version 8.3 in current docs? > https://www.postgresql.org/docs/current/row-estimation-examples.html -> "The > outputs shown are taken from version 8.3" > > The examples seem to be more or less fine,. Agreed. Removed in the attached applied patch. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/planstats.sgml b/doc/src/sgml/planstats.sgml index d2b84b301f..43ad57253e 100644 --- a/doc/src/sgml/planstats.sgml +++ b/doc/src/sgml/planstats.sgml @@ -30,8 +30,6 @@ The examples shown below use tables in the PostgreSQL regression test database. - The outputs shown are taken from version 8.3. - The behavior of earlier (or later) versions might vary. Note also that since ANALYZE uses random sampling while producing statistics, the results will change slightly after any new ANALYZE.
Re: The documentation for storage type 'plain' actually allows single byte header
On Thu, Jan 12, 2023 at 03:43:57PM +0100, Laurenz Albe wrote:
> On Tue, 2023-01-10 at 15:53 +, PG Doc comments form wrote:
> > https://www.postgresql.org/docs/devel/storage-toast.html - This is the
> > development version.
> >
> > > PLAIN prevents either compression or out-of-line storage; furthermore it
> > > disables use of single-byte headers for varlena types. This is the only
> > > possible strategy for columns of non-TOAST-able data types.
> >
> > However, it does allow "single byte" headers. How to verify this?
> >
> > CREATE EXTENSION pageinspect;
> > CREATE TABLE test(a VARCHAR(1) STORAGE PLAIN);
> > INSERT INTO test VALUES (repeat('A',10));
> >
> > Now peek into the page with pageinspect functions
> >
> > SELECT left(encode(t_data, 'hex'), 40) FROM
> > heap_page_items(get_raw_page('test', 0));
> >
> > This returned value of "1741414141414141414141".
> > Here the first byte 0x17 = 0001 0111 in binary.
> > Length + 1 is stored in the length bits (1-7). So Len = 0001011-1 = (11-1)
> > [base-10] = 10 [base-10]
> > which exactly matches the expected length. Further the data "41" repeated 10
> > times also indicates character A (65 or 0x41 in ASCII) repeated 10 times.
> >
> > SoThis does **not** disable 1-B header. That sentence should be removed
> > from the documentation unless this is a bug.
>
> I think that the documentation is wrong. The attached patch removes the
> offending half-sentence.
>
> Yours,
> Laurenz Albe
> From 5bf0b43fe73384a21f59d9ad1f7a8d7cbc81f8c4 Mon Sep 17 00:00:00 2001
> From: Laurenz Albe
> Date: Thu, 12 Jan 2023 15:41:56 +0100
> Subject: [PATCH] Fix documentation for STORAGE PLAIN
>
> Commit 3e23b68dac0, which introduced single-byte varlena headers,
> added documentation that STORAGE PLAIN would prevent such single-byte
> headers. This has never been true.
> ---
> doc/src/sgml/storage.sgml | 4 +---
> 1 file changed, 1 insertion(+), 3 deletions(-)
>
> diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml
> index e5b9f3f1ff..4795a485d0 100644
> --- a/doc/src/sgml/storage.sgml
> +++ b/doc/src/sgml/storage.sgml
> @@ -456,9 +456,7 @@ for storing TOAST-able columns on disk:
>
>
>PLAIN prevents either compression or
> - out-of-line storage; furthermore it disables use of single-byte headers
> - for varlena types.
> - This is the only possible strategy for
> + out-of-line storage. This is the only possible strategy for
>columns of non-TOAST-able data types.
>
>
> --
> 2.39.0
>
Where did we end with this? Is a doc patch the solution?
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
Re: Make SSPI documentation clearer
On Wed, Sep 27, 2023 at 07:09:02PM -0400, Bruce Momjian wrote: > On Sun, Mar 12, 2023 at 08:36:53PM -0400, Stephen Frost wrote: > > > When the server is on a non-Windows platform then the server must use > > > GSSAPI > > > if it wants to authenticate the client either via Kerberos or via Active > > > Directory. A client on a Windows platform that connects to a non-Windows > > > Postgresql server can either use SSPI (strongly encouraged) or GSS (much > > > more difficult to set up) if it wants to authenticate via Kerberos or > > > Active > > > Directory. A client from a non-Windows platform must use GSS if it wants > > > to > > > authenticate via Kerberos or Active Directory." > > > > Rather than work in negative, I feel like it might make more sense to > > work in positives? That is, perhaps this instead: > > > > On Windows platforms, SSPI is the default and most commonly used > > mechanism. Note that an SSPI client can authenticate to a server which > > is using either SSPI or GSSAPI, and a GSSAPI client can authenticate to > > a server which is using either SSPI or GSSAPI. Generally speaking, > > clients and servers on Windows are recommended to use SSPI while clients > > and servers on Unix (non-Windows) platforms use GSSAPI. > > I developed the attached patch. My first attempt was too terse, so here is a more detailed version, attached. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml index a72f80f033..9d1e7d63ef 100644 --- a/doc/src/sgml/client-auth.sgml +++ b/doc/src/sgml/client-auth.sgml @@ -1505,10 +1505,12 @@ omicron bryanh guest1 negotiate mode, which will use Kerberos when possible and automatically fall back to NTLM in other cases. -SSPI authentication only works when both -server and client are running Windows, -or, on non-Windows platforms, when GSSAPI -is available. +SSPI and GSSAPI +interoperate as clients and servers, e.g., an +SSPI client can authenticate to an +GSSAPI server. It is recommended to use +SSPI on Windows clients and servers and +GSSAPI on non-Windows platforms.
Re: Suggestion for deprecated spellings
On Mon, Jan 30, 2023 at 04:17:14PM -0500, Bruce Momjian wrote: > On Mon, Jan 30, 2023 at 04:07:46PM -0500, Tom Lane wrote: > > Bruce Momjian writes: > > > On Thu, Jan 26, 2023 at 12:19:29PM +, PG Doc comments form wrote: > > >> From time to time some spelling for given command gets obsolete, yet it > > >> is > > >> shown in the syntax on "equal rights" as other valid clauses. > > > > > We don't need to show all _supported_ syntaxes in the "Synopsis" > > > section, so we could just remove them. > > > > IIRC, there is precedent in COPY for moving obsolete alternatives > > to a separate part of the man page. I'd prefer that to just > > removing them, because then there is no documentation to help > > someone understand what an old SQL script is doing. > > Yeah, I remember that with COPY. t.kityn...@gmail.com, please us that > as a guide. Thanks. I developed the attached patch to move the deprecated clauses to the bottom. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml index 7249fc7432..6a3cd6d808 100644 --- a/doc/src/sgml/ref/create_role.sgml +++ b/doc/src/sgml/ref/create_role.sgml @@ -36,10 +36,8 @@ CREATE ROLE name [ [ WITH ] password' | PASSWORD NULL | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...] -| IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] -| USER role_name [, ...] | SYSID uid @@ -294,15 +292,6 @@ in sync when changing the above synopsis! - - IN GROUP role_name - - IN GROUP is an obsolete spelling of -IN ROLE. - - - - ROLE role_name @@ -326,16 +315,6 @@ in sync when changing the above synopsis! - - USER role_name - - -The USER clause is an obsolete spelling of -the ROLE clause. - - - - SYSID uid @@ -484,6 +463,22 @@ CREATE ROLE name [ WITH ADMIN NOINHERIT attribute, while roles are given the INHERIT attribute. + + + The USER clause has the same behavior as + ROLE but has been deprecated: + +USER role_name [, ...] + + + + + The IN GROUP clause has the same behavior as IN + ROLE but has been deprecated: + +IN GROUP role_name [, ...] + +
Re: MERGE examples not clear
On Tue, Feb 21, 2023 at 08:56:50AM -0700, David G. Johnston wrote:
> On Tue, Feb 21, 2023 at 8:35 AM PG Doc comments form
> wrote:
>
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/15/sql-merge.html
> Description:
>
> On this page: https://www.postgresql.org/docs/15/sql-merge.html
> the first and second examples seems to be contrasted (by "this would be
> exactly equivalent to the following statement"), however the difference
> does
> not seem to related to the stated reason ("the MATCHED result does not
> change"). It seems like the difference should involve the order of WHEN
> clauses?
> Of course, it might be that I don't understand the point, in which case
> maybe the point could be stated more clearly?
>
>
> Yeah, that is a pretty poor pair of examples. Given that a given customer can
> reasonably be assumed to have more than one recent transaction the MERGE has a
> good chance of failing.
>
> The only difference between the two is the second one uses an explicit
> subquery
> as the source while the first simply names a table. If the subquery had a
> GROUP BY customer_id that would be a good change explaining that the second
> query is different because it is resilient in the face of duplicate customer
> recent transactions.
>
> While here...source_alias (...completely hides...the fact that a query was
> issued). What? Probably it should read (not verified) that it is actually
> required when the source is a query (maybe tweaking the syntax to match).
The attached patch removes the second example, which doesn't seem to add
much.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml
index 0995fe0c04..4544ce92b3 100644
--- a/doc/src/sgml/ref/merge.sgml
+++ b/doc/src/sgml/ref/merge.sgml
@@ -582,23 +582,6 @@ WHEN NOT MATCHED THEN
-
- Notice that this would be exactly equivalent to the following
- statement because the MATCHED result does not change
- during execution.
-
-
-MERGE INTO customer_account ca
-USING (SELECT customer_id, transaction_value FROM recent_transactions) AS t
-ON t.customer_id = ca.customer_id
-WHEN MATCHED THEN
- UPDATE SET balance = balance + transaction_value
-WHEN NOT MATCHED THEN
- INSERT (customer_id, balance)
- VALUES (t.customer_id, t.transaction_value);
-
-
-
Attempt to insert a new stock item along with the quantity of stock. If
the item already exists, instead update the stock count of the existing
Re: Mistake in statement example
On Wed, Mar 1, 2023 at 09:45:00AM -0700, David G. Johnston wrote: > On Wed, Mar 1, 2023 at 9:34 AM Tom Lane wrote: > > PG Doc comments form writes: > > I believe there is a mistake in an example on > > https://www.postgresql.org/docs/current/transaction-iso.html section > > 13.2.1: > > BEGIN; > > UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; > > UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534; > > COMMIT; > > > The acctnum is expected to be 12345 in both cases. > > No, I think that's intentional: the example depicts transferring > $100 from account 7534 to account 12345. > > > > That may be, but the descriptive text and point of the example (which isn't > atomicity, but concurrency) doesn't even require the second update command to > be present. What the example could use is a more traditional two-session > depiction of the commands instead of having a single transaction and letting > the user envision the correct concurrency. > > Something like: > > S1: SELECT balance FROM accounts WHERE acctnum = 12345; //100 > S1: BEGIN; > S2: BEGIN; > S1: UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; > //200 > S2: UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; // > WAITING ON S1 > S1: COMMIT; > S2: UPDATED; balance = 300 > S2: COMMIT; > > Though maybe "balance" isn't a good example domain, the incrementing example > used just after this one seems more appropriate along with the added benefit > of > consistency. I developed the attached patch. I explained the example, I mentioned a "second" transaciton, I changed the account number so I can talk about the second statement, because read committed changes the row visibility of the non-first statements, and I changed "transaction" to "statement". -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml new file mode 100644 index f8f83d4..189cab0 *** a/doc/src/sgml/mvcc.sgml --- b/doc/src/sgml/mvcc.sgml *** *** 413,420 does not see effects of those commands on other rows in the database. This behavior makes Read Committed mode unsuitable for commands that involve complex search conditions; however, it is just right for simpler ! cases. For example, consider updating bank balances with transactions ! like: BEGIN; --- 413,420 does not see effects of those commands on other rows in the database. This behavior makes Read Committed mode unsuitable for commands that involve complex search conditions; however, it is just right for simpler ! cases. For example, consider transferring $100 from one account ! to another: BEGIN; *** UPDATE accounts SET balance = balance - *** 423,430 COMMIT; ! If two such transactions concurrently try to change the balance of account ! 12345, we clearly want the second transaction to start with the updated version of the account's row. Because each command is affecting only a predetermined row, letting it see the updated version of the row does not create any troublesome inconsistency. --- 423,430 COMMIT; ! If another transactions concurrently tries to change the balance of account ! 7534, we clearly want the second statement to start with the updated version of the account's row. Because each command is affecting only a predetermined row, letting it see the updated version of the row does not create any troublesome inconsistency.
Re: Make SSPI documentation clearer
On Sun, Mar 12, 2023 at 08:36:53PM -0400, Stephen Frost wrote: > > When the server is on a non-Windows platform then the server must use GSSAPI > > if it wants to authenticate the client either via Kerberos or via Active > > Directory. A client on a Windows platform that connects to a non-Windows > > Postgresql server can either use SSPI (strongly encouraged) or GSS (much > > more difficult to set up) if it wants to authenticate via Kerberos or Active > > Directory. A client from a non-Windows platform must use GSS if it wants to > > authenticate via Kerberos or Active Directory." > > Rather than work in negative, I feel like it might make more sense to > work in positives? That is, perhaps this instead: > > On Windows platforms, SSPI is the default and most commonly used > mechanism. Note that an SSPI client can authenticate to a server which > is using either SSPI or GSSAPI, and a GSSAPI client can authenticate to > a server which is using either SSPI or GSSAPI. Generally speaking, > clients and servers on Windows are recommended to use SSPI while clients > and servers on Unix (non-Windows) platforms use GSSAPI. I developed the attached patch. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml new file mode 100644 index a72f80f..fa53a0f *** a/doc/src/sgml/client-auth.sgml --- b/doc/src/sgml/client-auth.sgml *** omicron bryanh *** 1505,1514 negotiate mode, which will use Kerberos when possible and automatically fall back to NTLM in other cases. ! SSPI authentication only works when both ! server and client are running Windows, ! or, on non-Windows platforms, when GSSAPI ! is available. --- 1505,1514 negotiate mode, which will use Kerberos when possible and automatically fall back to NTLM in other cases. ! SSPI and GSSAPI ! interoperate as clients and servers. It is recommended to use ! SSPI on Windows clients and servers and ! GSSAPI on non-Windows platforms.
