Re: RETURNING order guarantees documentation

[David G. Johnston]

On Sat, May 4, 2024, 10:13 Dan Wainwright  wrote:

>
> 'Similar to' doesn't provide anything concrete to the reader. There was a 
> thread
> on [Hackers]
> 
> back in 2015 proposing some docs to clearly explain that ordering is
> preserved and I am keen to write the documentation if it's agreed to be
> worth doing.
>
>
Haven't reviewed the discussions recently but my understanding is that the
lack of guarantee is correct and intentional.  There is none.  Though we
are allergic to writing that out explicitly.  Observations that aren't
documented are not guaranteed.

David J.

>
>

Re: Update Help on PREPARE to mention DEALLOCATE

[David G. Johnston]

On Tue, Apr 16, 2024 at 1:27 PM Kirk Wolak  wrote:

> Could we make the PREPARE line read
>
> PREPARE [ DEALLOCATE ] ...?
>
> So it's more consistent, and the user using a PREPARE gets a clue to
> DEALLOCATE?
>
>
No. That is a syntax excerpt and the prepare command doesn't accept an
optional deallocate keyword at that position.

If we wanted to improve the psql help here we'd have two options:
Add to the description:
prepare a statement for execution (undo this with Deallocate)

or teach our build system to add our "see also" section to the psql help
pages.

The second seems reasonable but non-trivial.  The first just seems odd when
reading the website.

There may be a third option but I actually haven't ever explored the psql
help build module.

David J.

Re: three small improvements for "Composite Types" page

[David G. Johnston]

On Fri, Apr 12, 2024 at 1:20 PM Anton Voloshin 
wrote:

> Hello,
>
> While reading "Composite Types" manual page I've noticed that it is
> somewhat hard to follow by trying out given examples. I suggest three
> small changes which would make this page a little easier to follow for
> me:
>
> 1. Clarify "different kind" by adding a link to a section of "create
> type" page
>
>  > Note that the AS keyword is essential; without it, the system will
> think a different kind of CREATE TYPE command is meant, and you will get
> odd syntax errors.
>
>

> Here there is no link to CREATE TYPE, so it's not so easy to go there to
> see what is that "different kind" of CREATE TYPE. I suggest to add an
> anchor for the "Base Types" section there and link the words "different
> kind" there.
>

I'd much prefer to leave "different kind" alone and turn the immediately
following, first-on-the-page, instance of CREATE TYPE into a link.

 2. make first mention of CREATE TABLE a link

>
>  > The syntax is comparable to CREATE TABLE, except ...
>
> It would be useful if this CREATE TABLE (first on this page) would
> become a link.
>

I'm not all that convinced of that particular usefulness but also don't see
it hurting either.



> 3. Simplify CREATE TABLE example to make it self-sufficient
>
> One of the examples on the same "Composite Types" page is an example of
> CREATE TABLE:
>
> CREATE TABLE inventory_item (
>  nametext,
>  supplier_id integer REFERENCES suppliers,
>  price   numeric CHECK (price > 0)
> );
>
> This example is not self-sufficient: it requires one to have "suppliers"
> table with specific column to work as given.


Agreed.

David J.

Re: Mysteries of the future

[David G. Johnston]

On Thu, Apr 11, 2024 at 7:20 AM Tom Lane  wrote:

> PG Doc comments form  writes:
> > SELECT to_date('2-1131', '-MMDD');
> > ERROR:  22008: date/time field value out of range: "2-1131"
>
> What exactly do you find wrong with that?  November doesn't have
> 31 days.
>
> Sure, we could have a discussion about the probability of the
> Gregorian calendar still being in use 18000 years from now,
> but it doesn't seem very profitable.  What else do you want
> to use?
>

The request is to fix our documentation to use a valid date for the example
in the paragraph that describes the separator requirement for years greater
than 4 digits.

In to_timestamp and to_date, the  conversion has a restriction when
processing years with more than 4 digits. You must use some non-digit
character or template after , otherwise the year is always interpreted
as 4 digits. For example (with the year 2): to_date('21131',
'MMDD') will be interpreted as a 4-digit year; instead use a non-digit
separator after the year, like to_date('2-1131', '-MMDD') or
to_date('2Nov31', 'MonDD').

David J.

Re: 8.14.5 jsonb subscripting

[David G. Johnston]

On Tuesday, April 9, 2024, Arne Sommerfelt 
wrote:

> Thank you!  When googling it is easy to end up with latest docs,
> unfortunately
>

The 12 is a hyperlink so it is almost just as easy to view the current page
in the prior version.

David J.

Re: A typo?

[David G. Johnston]

On Sun, Apr 7, 2024 at 7:24 AM jian he  wrote:

> On Sun, Apr 7, 2024 at 6:30 PM PG Doc comments form
>  wrote:
> >
> > The following documentation comment has been logged on the website:
> >
> > Page: https://www.postgresql.org/docs/16/plpgsql-declarations.html
> > Description:
> >
> > Under 43.3.1, "Notice that we omitted RETURNS real — we could have
> included
> > it, but it would be redundant."
> > Should that be "RETURNS tax" instead of "RETURNS real"?
>
> I think it's related to the plpgsql "RETURNS" and "RETURN" confusion.
> RETURN  can appear between "begin", "end".
> RETURNS need to specify before "AS".
>
>
Right, the OP needs to have consulted the CREATE FUNCTION reference page to
find the definition of the RETURNS clause since it is language agnostic.

https://www.postgresql.org/docs/current/sql-createfunction.html

David J.

Re: A typo?

[David G. Johnston]

On Saturday, April 6, 2024, PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/plpgsql-declarations.html
> Description:
>
> Under 43.3.1, "Notice that we omitted RETURNS real — we could have included
> it, but it would be redundant."
> Should that be "RETURNS tax" instead of "RETURNS real"?
>

The docs are correct.

David J.

Re: CREATE ROLE inheritance details

[David G. Johnston]

On Sat, Mar 30, 2024 at 11:16 PM Noah Misch  wrote:

> On Tue, Jan 30, 2024 at 01:49:42PM -0700, David G. Johnston wrote:
> > +   The membership grants created by the
> > +   IN ROLE, ROLE, and
> ADMIN
> > +   clauses have the role executing this command as the grantee.
>
> This should be s/grantee/grantor/, right?
>

Correct.


> > +   If unspecified when create a new role membership this defaults to
> > +   the inheritance attribute of the role being added.
>
> Need s/create/creating/.  Missing comma.  While phrase "role being added"
> isn't wrong, the rest of the paragraph uses "member" and uses "granted
> role"
> to refer to the member-of role.  Putting those together:
>
>   If unspecified when creating a new role membership, this defaults to the
>   inheritance attribute of the new member.
>

I like that better.

David J.

Re: return type marked optional but isn't

[David G. Johnston]

On Wed, Feb 21, 2024 at 10:10 AM PG Doc comments form <
nore...@postgresql.org> wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/13/sql-createfunction.html
> Description:
>
> the documentation for creating a function shows [ RETURNS rettype | RETURNS
> TABLE ( column_name column_type [, ...] ) ] but returns is not optional. At
> least when specifying LANGUAGE plpgsql. All the online advice suggests
> specifying RETURNS void as the solution.
>

As with most conditionally option things in the docs reading the
description for the item tells you when it can be omitted.

rettype: "When there are OUT or INOUT parameters, the RETURNS clause can be
omitted."

David J.

Re: text and varchar are not equivalent

[David G. Johnston]

On Fri, Feb 9, 2024, 10:12 PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/datatype-character.html
> Description:
>
> The documentation implies that the data types text and varchar are
> equivalent, but this is not the case with this test in Postgresql version
> 16.
>

Fair point.  But I'd rather further emphasize that char should just be
avoided so this and other unexpected outcomes simply do not manifest in a
real database scenario.  Rather than try and document how odd it's behavior
is when dealing with intra-textual type conversions.

David J.

Re: 20.5.1

[David G. Johnston]

On Wednesday, February 7, 2024, PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/runtime-config-wal.html
> Description:
>
> The sentence in commit_siblings "A larger value makes it more probable that
> at least one other transaction will become ready to commit during the delay
> interval." seems to belong in commit_delay instead.
>

That sentence in that location is correct.  See “birthday paradox”.

Maybe phrasing it in the negative will make things clear - the more
required sessions needed before allowing a delay the less likely the delay
will be wasted.

David J.

Re: system column

[David G. Johnston]

On Wednesday, February 7, 2024, PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/ddl-system-columns.html
> Description:
>
> Will be clear to have a picture of how they are in the database.
>

Do you have a specific question you were trying to answer that you were
unable to using this documentation?  The internal implementation details of
these fields is not published intentionally.  The user needs only to know
that these column exist and can be retrieved in queries which ks what we
documented here.

David J.

Re: Missing information on '-X' in section 26.3.6.1.

[David G. Johnston]

On Wed, Jan 24, 2024 at 2:19 AM Daniel Gustafsson  wrote:

> > On 23 Jan 2024, at 21:43, David G. Johnston 
> wrote:
> >
> > On Tue, Jan 23, 2024 at 1:30 PM PG Doc comments form <
> nore...@postgresql.org <mailto:nore...@postgresql.org>> wrote:
> > The following documentation comment has been logged on the website:
> >
> > Page: https://www.postgresql.org/docs/16/continuous-archiving.html <
> https://www.postgresql.org/docs/16/continuous-archiving.html>
> > Description:
> >
> > I noticed, that in section 26.3.6.1. it's not specified, what the -X
> > parameter should be set to (stream or fetch, or whether it even
> matters). I
> > could continue with trial and error, but it confused me a bit.
> >
> > The -X parameter is documented to have a default; but since both fetch
> and stream are documented to give you the same end result it doesn't
> matter.  Of course you cannot specify the none method.
>
> Agreed.  Still, it doesn't hurt to spell out what we take for granted but a
> newcomer have to figure out in order to make the documentation easy to
> follow
> for new users. Something like the attached would be enough I think.
>
>
So I once again find a larger issue here, mostly unrelated to the complaint
at hand.

This entire paragraph is in the Continuous Archiving & PITR section but the
entire standalone concept is in opposition to that.  It is also in a "Tips"
section but doesn't really read as a tip.

Thinking on it further, and as the tip talks about, what we are really
doing here is describing a standalone physical file system backup in
contrast to a pg_dump backup.  We already have a chapter that does this -
the previous one named "File System Level Backup".

The attached patch moves this paragraph there.  I distilled the paragraph
down to its essence, but am open to being a bit more wordy, and consider
more how this fits into the existing content of that page.  I'm only really
married to two things - mentioning the -X argument to pg_basebackup here is
a bad idea and the content does not fit in the existing Tip area of
continuous archiving section.

David J.
From 0512ec27c52401b9ed7b468e7b68ff9ebec1584e Mon Sep 17 00:00:00 2001
From: "David G. Johnston" 
Date: Fri, 2 Feb 2024 12:32:51 -0700
Subject: [PATCH] docs: move standalone pg_basebackup docs to file system
 section

---
 doc/src/sgml/backup.sgml | 47 
 1 file changed, 24 insertions(+), 23 deletions(-)

diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
index b3468eea3c..1c5e48d6da 100644
--- a/doc/src/sgml/backup.sgml
+++ b/doc/src/sgml/backup.sgml
@@ -351,6 +351,29 @@ pg_dump -j num -F d -f 
   File System Level Backup
 
+  
+   In constrast to the logical backup that pg_dump performs there is
+   also the option to perform a physical backup.
+   PostgreSQL provides a tool,
+   pg_basebackup, that can produce a similar standalone
+   backup to the one produced by pg_dump, though the restoration point
+   of the physical backup is as of when the backup ended instead of when
+   it began. You may also choose to roll your own solution using the tools
+   available in your operating system.  This section describes both options.
+  
+
+  
+Built-In Standalone Backups
+
+ If all you want is a simple standalone backup of your cluster at some point in time
+ (specifically at roughly the moment the backup ended) you can just save the archive
+ produced by the  command. It handles, by default,
+ saving the write-ahead log (WAL) files produced during the backup to the archive.
+
+  
+
+  
+  Custom Physical Backups
   
An alternative backup strategy is to directly copy the files that
PostgreSQL uses to store the data in the database;
@@ -462,6 +485,7 @@ tar -cf backup.tar /usr/local/pgsql/data
the contents of indexes for example, just the commands to recreate
them.)  However, taking a file system backup might be faster.
   
+  
  
 
  
@@ -1442,29 +1466,6 @@ restore_command = 'cp /mnt/server/archivedir/%f %p'

 Some tips for configuring continuous archiving are given here.

-
-
- Standalone Hot Backups
-
- 
-  It is possible to use PostgreSQL's backup facilities to
-  produce standalone hot backups. These are backups that cannot be used
-  for point-in-time recovery, yet are typically much faster to backup and
-  restore than pg_dump dumps.  (They are also much larger
-  than pg_dump dumps, so in some cases the speed advantage
-  might be negated.)
- 
-
- 
-  As with base backups, the easiest way to produce a standalone
-  hot backup is to use the 
-  tool. If you include the -X parameter when calling
-  it, all the write-ahead log required to use the backup will be
-  included in the backup automatically, and no special action is
-  required to restore the backup.
- 
-
-
 
  Compressed Archive Logs
 
-- 
2.34.1

Re: Missed information about clientname=CN option

[David G. Johnston]

On Thu, Feb 1, 2024 at 3:16 AM Daniel Gustafsson  wrote:

> > On 1 Feb 2024, at 08:35, David G. Johnston 
> wrote:
>
> > maybe move the wording to the cert page and replace the content in
> pg_hba.conf with a link to there.  Leaning toward the later ATM.
>
> That sounds like the best option IMHO, care to propose a patch?
>
>
Done here:

https://www.postgresql.org/message-id/CAKFQuwa%3DiY13UkH2K4-Srut9iaXBi2FkLzWRxbok%2BmdSMPEDuA%40mail.gmail.com

The material here needed some attention too, both on its own and to fit in
with the changes to the client authentication section.

https://www.postgresql.org/docs/current/ssl-tcp.html#SSL-CLIENT-CERTIFICATES

David J.

Re: Missed information about clientname=CN option

[David G. Johnston]

On Wednesday, January 31, 2024, PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/auth-cert.html
> Description:
>
> Hello.
>
> This page missed information about supported clientname=CN/DN option and
> describes only `map` option.
> Also `clientcert` is described not in format. I expect it was documented
> under `map` as next list item of supported options
>


The description for pg_hba.conf auth-options covers this.  Since those two
options do not only apply to cert but any hostssl entry it was chosen to
document the ssl related options on the pg_hba.conf page.  I do see value
in pointing the reader back to that location from the cert page though.  Or
maybe move the wording to the cert page and replace the content in
pg_hba.conf with a link to there.  Leaning toward the later ATM.

David J.

Re: CREATE ROLE inheritance details

[David G. Johnston]

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.

diff --git a/doc/src/sgml/ref/create_role.sgml
b/doc/src/sgml/ref/create_role.sgml
index d0d3d7ed64..f3b89e7239 100644
--- a/doc/src/sgml/ref/create_role.sgml
+++ b/doc/src/sgml/ref/create_role.sgml
@@ -66,6 +66,17 @@ in sync when changing the above synopsis!
Note that roles are defined at the database cluster
level, and so are valid in all databases in the cluster.
   
+
+  
+   During role creation it is possible to immediately assign the newly
created
+   role to be a member of an existing role, and also assign existing roles
+   to be members of the newly created role.  The rules for which initial
+   role membership options are enabled are described below in the
+   IN ROLE, ROLE, and
+   ADMIN clauses.  Alternatively, the 
+   command has fine-grained option control during membership creation,
+   and also the ability to modify these options after the new role is
created.
+  
  

  
@@ -285,10 +296,7 @@ in sync when changing the above synopsis!
 roles. The new membership will have the SET
 option enabled and the ADMIN option disabled.
 The INHERIT option will be enabled unless the
-NOINHERIT option is specified.  See the  command, which has additional option
-control during membership creation and to modify these options
-after the new role is created.
+NOINHERIT option is specified.

   
  

And then fixed up one last instance of attribute/option specification.  I
left the term "  membership inheritance status" alone in the attribute
description since that really does refer to the concept and neither the
specific attribute here nor the membership option.  All of the other
references seemed better of being specific to the feature and not the
behavior.


@@ -301,9 +309,9 @@ 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 INHERIT options will have
-INHERIT enabled in the new membership.  New
-memberships will have the ADMIN option disabled.
+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 omission of an option during membership modification results in no
change, not applying the default, applies to all three, not just inherit.
Again, instead of documenting all three with this it seemed preferable to
add it once somewhere - in this case the paragraph that talks about the
option mode values seems appropriate.

diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index ee53871713..9d27b7fcde 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -266,7 +266,9 @@ GRANT role_name [, ...] TO TRUE or FALSE. The keyword
OPTION is accepted as a synonym for
TRUE, so that WITH ADMIN OPTION
-   is a synonym for WITH ADMIN TRUE.
+   is a synonym for WITH ADMIN TRUE.  When altering
+   an existing membership the omission of an option results in the current
+   value being retained.
   

   

And some final touch-ups; including removing the now redundant omission
sentence for inherit.

@@ -280,14 +282,13 @@ GRANT role_name [, ...] TO 

   
-   The INHERIT attribute controls the inheritance status
+   The INHERIT option controls the inheritance status
of the new membership;  see  for
details on inheritance.  If it is set to TRUE,
it causes the new member to inherit from the granted role. If
set to FALSE, the new member does not inherit.
-   If unspecified, new role membership defaults to the inheritance status
-   of the role being added.  If an existing membership, the inheritance
-   option is unchanged.
+   If unspecified when creating a new role membership this defaults to
+   the inheritance attribute of the role being added.
   

   


I've attached a revised role.diff patch as well as just the changes from
the original.

David J.
From 1c483856c6e98a9f6bbd627bf13a950ab3e95871 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" 
Date: Tue, 30 Jan 2024 13:17:47 -0700
Subject: [PATCH] base rev

description addition and tweaks

grant tweaks
---
 doc/src/sgml/ref/create_role.sgml | 87 +++
 doc/src/sgml/ref/grant.sgml   | 29 ++-
 doc/src/sgml/user-manag.sgml  |  8 +--
 3 files changed, 74 insertions(+), 50 deletions(-)

diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml
index 8d

Re: CREATE ROLE inheritance details

[David G. Johnston]

On Fri, Jan 26, 2024 at 5:18 PM Bruce Momjian  wrote:

>
> Just a reminder, this is for PG 16 and master.
>
>
 
+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.


 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 leave 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.

@@ -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  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

@@ -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.

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...

To modify that attributes of
+   an existing membership, simply specify the membership with updated
+   attribute values.

attribute s/b option

   
@@ -275,15 +278,13 @@ GRANT role_name [, ...] TO 

   
+   The INHERIT option controls the inheritance status
+   of the new membership;  see  for
+   details on inheritance.  If it is set to TRUE,
+   it causes the new member to inherit from the granted role. If
+   set to FALSE, the new member does not inherit.
+   If unspecified, it defaults to the inheritance status of the role
+   being added.
   

Suggest linking to chapter 22, not create role.

Unspecified has two outcomes: if the grant is establishing a new membership
the inheritance "attribute" value of the role being added is used, if the
grant is altering an existing membership the current 

Re: create table explicitly mention that unique|primary key constraint will create an

[David G. Johnston]

On Fri, Jan 19, 2024 at 3:46 AM Laurenz Albe 
wrote:

> In the attached patch, I have copied the enumeration of relations from
> the CREATE INDEX page.  I think this small redundance is alright, but I
> wouldn't mind if this gets removed from CREATE INDEX.
>
>
Tweaking the main paragraph a little.

We use examples elsewhere, it seems one for this makes the point very clear
with less description.

I removed it altogether but namespace is a word unto itself, not "name
space".

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index e103eddd40..25db985a56 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3025,10 +3025,11 @@ SELECT * FROM information WHERE group_id = 2 FOR
UPDATE;
A database contains one or more named schemas,
which
in turn contain tables.  Schemas also contain other kinds of named
objects, including data types, functions, and operators.  Within one
schema,
-   two objects of the same type cannot have the same name.  All relations
-   (tables, sequences, indexes, views, materialized views, and foreign
tables)
-   share one name space, so they need to have different names if they are
in
-   a single schema.  The same
+   two objects of the same type cannot have the same name.  The object type
+   of relations encompasses all of the following:
+   tables, sequences, indexes, views, materialized views, and foreign
tables.
+   Thus, for example, an index and a table must have different names if
they
+   are in the same schema.  The same
object name can be used in different schemas without conflict; for
example, both schema1 and
myschema can
contain tables named mytable.  Unlike databases,

David J.

Re: SQL command : ALTER DATABASE OWNER TO

[David G. Johnston]

Thank you for the feedback.  I've taken it into account and come up with a
partial rewrite of my first pass, and posted it to -hackers so we can close
this thread out.

https://www.postgresql.org/message-id/cakfquwyy3ei05kpggk2del_pe5yjkdybxb362xuthovpqjf...@mail.gmail.com

Some replies below.

On Thu, Jan 25, 2024 at 7:35 AM Laurenz Albe 
wrote:

> On Wed, 2024-01-24 at 16:04 -0700, David G. Johnston wrote:
> > Here's a slightly more detailed patch to consider to cover both the
> transference of ownership as well as documenting precisely what ownership
> means.
>
> Ok, you want to describe that in more detail.  But you should preserve
> the  when the term is used for the first time.
>

Yeah, I need to get better at looking for and applying semantic markup.

Note that the documentation is careful to avoid the term "privilege"
> when speaking about the latter: below, it says "The *right* to modify or
> destroy an object is inherent in being the object's owner".
>
> We should leave that as it is.
>

Agreed.  I was going for "first encounter clarification" only, not
intending to define the term fully.  I've taken a different approach with
the new patch - not redefining the main term but adding appropriate
qualifications for limited use in learning how things work.


> I think we should say "owner" instead of "recorded owner".  Also, is it
> necessary
> to detail to the level of system catalog columns?
>
>
Agreed, with the overall flow of the content and context improved that
specific sub-paragraph can be made much simpler without loss of clarity.

David J.

Re: SQL command : ALTER DATABASE OWNER TO

[David G. Johnston]

On Wed, Jan 24, 2024 at 7:23 AM Laurenz Albe 
wrote:

> On Wed, 2024-01-24 at 11:08 +0100, gp...@free.fr wrote:
> > for this "ALTER DATABASE" form, it should be mentioned that after
> execution of the command,
> > the old database owner loses all his privileges on it (even connection)
> although it might
> > still owns schemas or objects (tables, index,...) inside it.
> >
> > Thanks in advance to add this important precision.
>
> How about this:
>
> diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
> index 4044f0908f..44042f863c 100644
> --- a/doc/src/sgml/ddl.sgml
> +++ b/doc/src/sgml/ddl.sgml
> @@ -1891,6 +1891,8 @@ ALTER TABLE table_name
> OWNER TO new_owne
> Superusers can always do this; ordinary roles can only do it if they
> are
> both the current owner of the object (or inherit the privileges of the
> owning role) and able to SET ROLE to the new owning
> role.
> +   All object privileges of the old owner are transferred to the new owner
> +   along with the ownership.
>
>
>
>
>
Here's a slightly more detailed patch to consider to cover both the
transference of ownership as well as documenting precisely what ownership
means.

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index fc03a349f0..c8866ee9c7 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1856,15 +1856,12 @@ ALTER TABLE products RENAME TO items;

   
When an object is created, it is assigned an owner. The
-   owner is normally the role that executed the creation statement.
-   For most kinds of objects, the initial state is that only the owner
-   (or a superuser) can do anything with the object. To allow
-   other roles to use it, privileges must be
-   granted.
+   owner is the role that executed the creation statement
+   unless the statement itself specifies an owner.
   

   
-   There are different kinds of privileges: SELECT,
+   There are different kinds of grantable privileges:
SELECT,
INSERT, UPDATE,
DELETE,
TRUNCATE, REFERENCES,
TRIGGER,
CREATE, CONNECT,
TEMPORARY,
@@ -1877,13 +1874,27 @@ ALTER TABLE products RENAME TO items;
these privileges are used.
   

+  
+   Upon object creation the owner is granted all grantable privileges
+   on the object.  Additionally, the built-in PUBLIC privileges of
+   the associated object type are granted.  Lastly, if any have been
defined,
+   the system grants the default privileges for the object type to the
defined roles.
+   All of these privileges can be revoked.
+  
+
   
The right to modify or destroy an object is inherent in being the
object's owner, and cannot be granted or revoked in itself.
-   (However, like all privileges, that right can be inherited by
+   (However, like the grantable privileges, that right can be inherited by
members of the owning role; see .)
   

+  
+   Another inherent right the owner of an object has is to grant all
+   grantable privileges on that object to any database role, including
+   their own.
+  
+
   
An object can be assigned to a new owner with an
ALTER
command of the appropriate kind for the object, for example
@@ -1893,6 +1904,11 @@ ALTER TABLE table_name
OWNER TO new_owne
Superusers can always do this; ordinary roles can only do it if they are
both the current owner of the object (or inherit the privileges of the
owning role) and able to SET ROLE to the new owning
role.
+   The reassignment process involves changing the recorded owner of the
object in
+   the appropriate system catalog, as well as changing all references
+   (grantor and grantee) to the old role in the Access Control List (ACL,
see below)
+   column to the new role; leaving the old role without any direct
privileges on the object.
+   Multiple privilege entries with the same grantor and grantee are
consolidated into a single entry.
   

   

Laurenz has already commented to my accidentally off-list initial post that
this seems to be too much detail for this section.  But it is the language
specification Chapter, not the Tutorial, and I disagree.  It doesn't seem
like an internals topic and there just isn't anywhere else to define this
stuff.  The man pages I suppose work, and this does have some repetition of
the material there, but personally this feels like the more correct spot.
Some of the "where" language probably can be removed without loss of
clarity but I haven't added anything here that isn't already described in
even more detail at the end of this section.  I just used that material in
context.

David J.

Re: SQL command : ALTER DATABASE OWNER TO

[David G. Johnston]

On Wed, Jan 24, 2024 at 10:13 AM Tom Lane  wrote:

> "David G. Johnston"  writes:
> > postgres=# grant all on database newdb2 to testowner;
> > -- as I am logged in as davidj this grant should actually happen, with
> > davidj as the grantor
> > -- the grants that materialize from ownership has the owning role as the
> > grantor
>
> Yes.  The FM points out somewhere that if a superuser does a GRANT,
> it's executed as though by the object owner.  That provision predates
> when we supported explicit GRANTED BY clauses in GRANT.  I'm not sure
> we'd have made it work like that if we had GRANTED BY already, but
> I'm afraid of the compatibility implications if we change it now.
>
>
Agreed, and I do recall that - it is documented on the GRANT page.  Also
noted is I can "inherit ownership" if I exercise that inherited ability the
resultant grant still comes from the owner.  This unifies two of three ways
for these grants to be established.

If I give out the ability via a grant option only then does the grantor
become the grant optioned role.  This is the expected behavior and doesn't
require documentation explicitly.

The following testing of this behavior surprises me though:

List of databases
-[ RECORD 1 ]-+
Name  | newdb2
Owner | testowner
Encoding  | UTF8
Locale Provider   | libc
Collate   | en_US.UTF-8
Ctype | en_US.UTF-8
ICU Locale|
ICU Rules |
Access privileges | testowner=CTc/testowner+
  | to3=C*T*c*/testowner   +
  | to4=CTc/to3+
  | testowner=CTc/to3

postgres=> reset role;
RESET
postgres=# alter database newdb2 owner to davidj;
ALTER DATABASE
postgres=# \l newdb2
List of databases
-[ RECORD 1 ]-+--
Name  | newdb2
Owner | davidj
Encoding  | UTF8
Locale Provider   | libc
Collate   | en_US.UTF-8
Ctype | en_US.UTF-8
ICU Locale|
ICU Rules |
Access privileges | davidj=CTc/davidj+
  | to3=C*T*c*/davidj+
  | to4=CTc/to3  +
  | davidj=CTc/to3

I was expecting the privileges given to me by to3 to remain in place even
after I lost my ownership grants.

As you've noted it seems unlikely this is something we are willing to
change at this point.  So, in short, it seems impossible for an owner of an
object to be left with any direct permissions on said object after having
their ownership reassigned.  The role which gets the new assignment assumes
all of the explicit grants that exist for the old role.

postgres=# alter database newdb2 owner to to3;
ALTER DATABASE
postgres=# \l newdb2
List of databases
-[ RECORD 1 ]-+---
Name  | newdb2
Owner | to3
Encoding  | UTF8
Locale Provider   | libc
Collate   | en_US.UTF-8
Ctype | en_US.UTF-8
ICU Locale|
ICU Rules |
Access privileges | to3=C*T*c*/to3+
  | to4=CTc/to3

This makes sense since the three grants that to3 would have after merging
are consolidated into a single one - in an additive sense and the grant
options being retained if present.

David J.

Re: SQL command : ALTER DATABASE OWNER TO

[David G. Johnston]

On Wed, Jan 24, 2024 at 9:56 AM Alvaro Herrera 
wrote:

> On 2024-Jan-24, Laurenz Albe wrote:
>
> > The permissions are transferred to the new owner, so the old owner
> doesn't
> > have any privileges on the object (and, in your case, cannot connect to
> > the database any more).
>
> However, if the old owner had a pg_hba.conf line that allowed them in,
> and the new owner doesn't, then they're now both locked out of the
> database with no recourse.
>
>
The OP doesn't actually care about inherited permissions, just the stated
ones.  That said, I do think there is a problem here:

postgres=# select current_user;
-[ RECORD 1 ]+---
current_user | davidj

postgres=# revoke all on database  newdb2 from public;
REVOKE
postgres=# \l newdb2
List of databases
-[ RECORD 1 ]-+
Name  | newdb2
Owner | testowner
Encoding  | UTF8
Locale Provider   | libc
Collate   | en_US.UTF-8
Ctype | en_US.UTF-8
ICU Locale|
ICU Rules |
Access privileges | testowner=CTc/testowner

postgres=# grant all on database newdb2 to testowner;
-- as I am logged in as davidj this grant should actually happen, with
davidj as the grantor
-- the grants that materialize from ownership has the owning role as the
grantor
-- it is only those that should be removed upon reassigning ownership

GRANT
postgres=# \l newdb2
List of databases
-[ RECORD 1 ]-+
Name  | newdb2
Owner | testowner
Encoding  | UTF8
Locale Provider   | libc
Collate   | en_US.UTF-8
Ctype | en_US.UTF-8
ICU Locale|
ICU Rules |
Access privileges | testowner=CTc/testowner

-- I expect to see "testowner=CTc/davidj" here as well

David J.

Re: SQL command : ALTER DATABASE OWNER TO

[David G. Johnston]

On Wed, Jan 24, 2024 at 9:23 AM  wrote:-

> [postgres] $ psql
> psql (14.10)
>
>
You really should add commentary, especially since you never demonstrated
the tst role (I advise picking different names for all of the objects in
the future) being unable to login.  Which they should be able to since
public is shown to have "c" connect privileges (=Tc/tst)



> [postgres@PGDEV14] postgres=# create user tst password 'tst';
> CREATE ROLE
> [postgres@PGDEV14] postgres=# create database tst owner = tst;
> CREATE DATABASE
>

This next command is pointless, it is a no-op, as soon as you made them
owner of the tst database they already had all privileges to it, granted by
the same user that created the database.  And only it, that command is not
recursing through the database into schemas and tables and adding more
permissions.  That isn't how this all works, a database is an object.
While it is also a concept that encompasses the entire schema within it the
permissions system only cares about the first definition.

[postgres@PGDEV14] postgres=# grant all on database tst to tst;
> GRANT
> [postgres@PGDEV14] postgres=# \l+ tst
>   Liste des bases de données
>  Nom | Propriétaire | Encodage | Collationnement | Type caract. | Droits
> d'accès | Taille  | Tablespace | Description
>
> -+--+--+-+--++-++-
>  tst | tst  | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8  | =Tc/tst
>  +| 9809 kB | pg_default |
>  |  |  | |  |
> tst=CTc/tst| ||
> (1 ligne)
>
>
What are you trying to demonstrate here?


> [postgres@PGDEV14] tst=# \dn+ tst
>  Liste des schémas
>  Nom | Propriétaire | Droits d'accès | Description
> -+--++-
>  tst | tst  ||
> (1 ligne)
>
>
David J.

Re: SQL command : ALTER DATABASE OWNER TO

[David G. Johnston]

On Wed, Jan 24, 2024 at 9:13 AM Laurenz Albe 
wrote:

> On Wed, 2024-01-24 at 08:47 -0700, David G. Johnston wrote:
> > I dislike this change, ownership of an object is completely independent
> of
> > the grant system of privileges.  The granted privileges of the old row do
> > not transfer to the new owner when alter ... owner to is executed.
>
> CREATE TABLE mytab ();
>
> REVOKE ALL ON mytab FROM PUBLIC;
>
> \z mytab
>  Access privileges
>  Schema │ Name  │ Type  │ Access privileges │ Column privileges │
> Policies
>
> ╪═══╪═══╪═══╪═══╪══
>  public │ mytab │ table │ postgres=arwdDxt/postgres │   │
> (1 row)
>
> ALTER TABLE mytab OWNER TO laurenz;
>
> \z mytab
> Access privileges
>  Schema │ Name  │ Type  │Access privileges│ Column privileges │
> Policies
>
> ╪═══╪═══╪═╪═══╪══
>  public │ mytab │ table │ laurenz=arwdDxt/laurenz │   │
> (1 row)
>
>
>
You need to actually revoke something to make the point stand out.

postgres=# \z tt1
  Access privileges
 Schema | Name | Type  |   Access privileges   | Column privileges |
Policies
+--+---+---+---+--
 public | tt1  | table | davidj=arwdDxt/davidj |   |
(1 row)
postgres=# revoke update on tt1 from davidj;
REVOKE
postgres=# \z tt1
  Access privileges
 Schema | Name | Type  |  Access privileges   | Column privileges | Policies
+--+---+--+---+--
 public | tt1  | table | davidj=ardDxt/davidj |   |
(1 row)

postgres=# alter table tt1 owner to testowner;
ALTER TABLE
postgres=# \z tt1
 Access privileges
 Schema | Name | Type  | Access privileges  | Column privileges |
Policies
+--+---++---+--
 public | tt1  | table | testowner=ardDxt/testowner |   |
(1 row)

The new owner, testowner, is missing the same update privilege that davidj
removed from himself.  In short, setting owner does indeed cause explicit
grants to appear in the system, grants that can be revoked.  And so, yes,
transferring ownership transfers the set of grants currently in effect for
the existing owner.

I can see making this detail more clear in the DDL chapter.  It is
unrelated to the confusion behind the topic of this thread though.

David J.

Re: SQL command : ALTER DATABASE OWNER TO

[David G. Johnston]

On Wed, Jan 24, 2024 at 8:35 AM Laurenz Albe 
wrote:

> On Wed, 2024-01-24 at 15:40 +0100, gp...@free.fr wrote:
> > maybe a misunderstanding of my part, but your proposed modification
> doesn't matched
> > with the current behaviour of the command as precisely the object
> privileges of the old owner are **NOT** transferred
> > to the new owner along with the ownership
>
> But that is what happens.
>
> The permissions are transferred to the new owner, so the old owner doesn't
> have any privileges on the object (and, in your case, cannot connect to
> the database any more).
>
>
I dislike this change, ownership of an object is completely independent of
the grant system of privileges.  The granted privileges of the old row do
not transfer to the new owner when alter ... owner to is executed.  The
separate object attribute "owner" is the only thing that changes.  If the
old owner doesn't have any granted privileges on the modified object then
they will be left with no ability to interact with that object.  In the
case of Database the applicable interactions are Create and Connect.  The
permissions the old owner may have on any other objects in the database are
also left unaffected - such as those on a schema.  But if they have lost
the ability to Connect then actually exercising schema privileges becomes
impossible.  It really isn't any different than removing their login
attribute.

Note that since PUBLIC gets connect privileges on all databases by
default...

David J.

Re: Missing information on '-X' in section 26.3.6.1.

[David G. Johnston]

On Tue, Jan 23, 2024 at 1:30 PM PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/continuous-archiving.html
> Description:
>
> I noticed, that in section 26.3.6.1. it's not specified, what the -X
> parameter should be set to (stream or fetch, or whether it even matters). I
> could continue with trial and error, but it confused me a bit.
>
>
The -X parameter is documented to have a default; but since both fetch and
stream are documented to give you the same end result it doesn't matter.
Of course you cannot specify the none method.

David J.

Re: About COPY

[David G. Johnston]

On Friday, January 19, 2024, PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/tutorial-populate.html
> Description:
>
> COPY weather FROM '/home/user/weather.txt'; is not working it requires
> additional commands for me.
>

And your point is?  The table has to have already been created and the file
path needs to be modified to your personal circumstance.  What is it
exactly that you want to have changed?

David J.

Re: create table explicitly mention that unique|primary key constraint will create an

[David G. Johnston]

On Thu, Jan 18, 2024 at 7:54 AM Peter Eisentraut 
wrote:

>
> I find the specific change
> proposal for ddl.sgml a bit weird, though, because this is a very
> introductory section, and you are referring people to pg_class (what is
> that?!?) for details.  If we want to put something there, it should
> respect the order in which that chapter introduces concepts.
>
>
I started looking at this specific item and immediately got the idea to
actually document in user-facing (i.e., not system catalogs) what these
object categories are in which object types share the schema namespace.
The "Other Object Types" section already in the DDL chapter seems to
provide a near-perfect place to put this (not sure I like the word "other"
there being my only complaint).  The attached patch replaces Laurenz's v1,
leaving the create_table changes as-is but presenting an alternative
approach to introducing namespacing when we explain why schemas exist.

David J.
From 4ba026d1a42e074df103a769e0f6b71629631c87 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" 
Date: Thu, 18 Jan 2024 14:15:33 -0700
Subject: [PATCH] Doc-objects-in-pg_class-share-a-namespace

---
 doc/src/sgml/ddl.sgml  | 168 +++--
 doc/src/sgml/ref/create_table.sgml |   9 +-
 2 files changed, 163 insertions(+), 14 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index fc03a349f0..86f793f724 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3002,11 +3002,18 @@ SELECT * FROM information WHERE group_id = 2 FOR UPDATE;
 
   
A database contains one or more named schemas, which
-   in turn contain tables.  Schemas also contain other kinds of named
-   objects, including data types, functions, and operators.  The same
-   object name can be used in different schemas without conflict; for
-   example, both schema1 and myschema can
-   contain tables named mytable.  Unlike databases,
+   in turn contain all other database-specific objects (most importantly, tables).
+   The schema name, combined with an object's type category (i.e., tables are relationas),
+   forms a namespace in which any given object's name exists.
+   When writing an object's name the object type category component is inferred from
+   context.  The schema name component can be explicitly prepended to the name separated
+   by a period (e.g., schema.object_name).  If the schema name is not specified, the system
+   will search for the name in the schemas named in the search_path setting.
+   See ... for a listing of object types grouped by category.
+  
+
+  
+   Unlike databases,
schemas are not rigidly separated: a user can access objects in any
of the schemas in the database they are connected to, if they have
privileges to do so.
@@ -5274,31 +5281,172 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate = DATE '2008-01-01';
that exist in a database.  Many other kinds of objects can be
created to make the use and management of the data more efficient
or convenient.  They are not discussed in this chapter, but we give
-   you a list here so that you are aware of what is possible:
+   you a partial list here so that you are aware of what is possible.
+   The first tier of labels are the object type categories that combine
+   with the schema name to form a namespace.  If multiple specific
+   object types share the same category they will be listed in the
+   second tier of labels.
   
 
   

 
- Views
+ Relations
+
+
+ 
+  
+   
+Tables
+   
+  
+  
+   
+Indexes
+   
+  
+  
+   
+Views
+   
+  
+  
+   
+Materialized Views
+   
+  
+  
+   
+Foreign Tables
+   
+  
+  
+   
+Composite Types
+   
+  
+  
+   
+Sequences
+   
+  
+  
+   
+Partitioned Tables
+   
+  
+  
+   
+Partitioned Indexes
+   
+  
+ 
+
+   
+
+   
+
+ Routines
+
+
+ 
+  
+   
+Functions
+   
+  
+  
+   
+Aggregate Functions
+   
+  
+  
+   
+Window Functions
+   
+  
+  
+   
+Procedures
+   
+  
+ 
+
+   
+
+   
+
+ Data Types
+
+
+ 
+  
+   
+Base Types
+   
+  
+  
+   
+Composite Types
+   
+  
+  
+   
+Range Types
+   
+  
+  
+   
+Multi-Range Types
+   
+  
+  
+   
+Domains
+   
+  
+  
+   
+Enums
+   
+  
+ 
+
+   
+
+   
+
+ Operators
+
+   
+
+   
+
+ Triggers
+
+   
+
+   
+
+ Row-Level Security (RLS) Policies
 

 

 
- Functions, pr

Re: Creating the scripts and compiling the C files containing user-defined functions and types.

[David G. Johnston]

On Thursday, January 18, 2024, PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/tutorial-sql-intro.html
> Description:
>
> " Directory of C:\postgresql-16.1\src\tutorial
>
> 11/06/2023  11:17 PM  .
> 11/06/2023  11:17 PM  ..
> 11/06/2023  11:04 PM62 .gitignore
> 11/06/2023  11:04 PM 1,579 advanced.source
> 11/06/2023  11:04 PM 6,091 basics.source
> 11/06/2023  11:04 PM 5,169 complex.c
> 11/06/2023  11:04 PM 7,580 complex.source
> 11/06/2023  11:04 PM 3,078 funcs.c
> 11/06/2023  11:04 PM 4,531 funcs.source
> 11/06/2023  11:04 PM   900 Makefile
> 11/06/2023  11:04 PM   460 README
> 11/06/2023  11:04 PM 5,506 syscat.source
>   10 File(s) 34,956 bytes
>2 Dir(s)  153,064,722,432 bytes free"
>
> it shows
>
> C:\postgresql-16.1\src\tutorial>make
> 'make' is not recognized as an internal or external command,
> operable program or batch file.


Yeah, the “prior chapter” needed to create the database assumes you are
able to compile PostgreSQL from source in which case make would be
installed.

>
> and this also.
>
> C:\postgresql-16.1\src\tutorial>Makefile
> 'Makefile' is not recognized as an internal or external command,
> operable program or batch file.
>

The docs don’t tell you to try and execute the plain text Makefile.

David J.

Re: CREATE ROLE inheritance details

[David G. Johnston]

On Wed, Jan 17, 2024 at 9:38 AM Bruce Momjian  wrote:

> 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.
>
>
I had a go at this.

I went with a more "bullet item" approach with my wording for
INHERIT/NOINHERIT.

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.

We consistently say what the default is for these attribute pairs, do so
here as well.

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.

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.

Patch built on top of Bruce's role.diff, not HEAD

David J.
From ce4605354f1360662ffed6f0e239b2e06d107a85 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" 
Date: Wed, 17 Jan 2024 14:19:54 -0700
Subject: [PATCH] v2

---
 doc/src/sgml/ref/create_role.sgml | 81 +--
 1 file changed, 45 insertions(+), 36 deletions(-)

diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml
index 5c2b71bf69..f134feea27 100644
--- a/doc/src/sgml/ref/create_role.sgml
+++ b/doc/src/sgml/ref/create_role.sgml
@@ -133,29 +133,29 @@ in sync when changing the above synopsis!
   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.
+This controls whether a newly established membership of this role in
+another has the INHERIT option.
+Specifically, when this role is created with an IN ROLE
+clause, is specified in the ROLE clause
+of a future CREATE ROLE command, or when this
+role is the "TO" role in a GRANT command
+that does not specify INHERIT in the
+WITH 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.
+See "GRANT on Roles" on the GRANT reference page
+for more information on what the INHERIT option
+on a membership grant does.
+   
+
+   
+If not specified, INHERIT is the default.

 

 In PostgreSQL versions before 16,
-the GRANT statement did not support
+the GRANT command 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.
@@ -293,9 +293,12 @@ 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 or to disallow SET ROLE; use a
-separate GRANT command to do that.)
+roles.  The new role

Re: Question on doc for RETURNING clause

[David G. Johnston]

On Thu, Jan 11, 2024 at 11:55 AM Russell, John  wrote:

> ```
> postgres=> insert into generatedfields (x) values (0), (10), (100) order
> by 2 desc returning id, x;
> ERROR:  ORDER BY position 2 is not in select list
> LINE 1: ...eratedfields (x) values (0), (10), (100) order by 2 desc ret...
>  ^
> ```
>
> Is the acceptance of ORDER BY documented anywhere?


VALUES, like SELECT, is an SQL Command in its own right.

https://www.postgresql.org/docs/current/sql-values.html

That is what you are ordering, before attempting insertion.  Hence why it
only sees one column.



> I didn’t see that anywhere in the INSERT syntax. Does it have any
> practical effect if there’s no RETURNING clause, e.g. do the rows get
> physically inserted in the ORDER BY order, which could have implications
> for columns like SERIAL?
>

At present, the order of rows presented to the insert does in no way compel
the insert command to act on the provided rows in order; even though in
practice it will seem to do so.

David J.

Re: initdb username doc bug

[David G. Johnston]

On Mon, Jan 8, 2024 at 2:26 PM Bruce Momjian  wrote:

> 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.
>
>
+1

Thanks!

David J.

Re: Grammar suggestion

[David G. Johnston]

On Monday, January 8, 2024, PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/15/ddl-constraints.html
> Description:
>
> Dear postgres Team,
>
> I happened to have stumbled across a tricky to read sentence in Ch. 5.45
> 'Foreign Keys' (Postgres Version 15).
>
> The original sentence: "Normally, a referencing row need not satisfy the
> foreign key constraint if any of its referencing columns are null".
>
> Here's the revised version: "Normally, a referencing row does not need to
> satisfy the foreign key constraint if any of its referencing columns are
> null."
>
> A bit restructuring in beginning of this sentence can it make it
> grammatically complete and clear for the reader.
>

I see where you are coming from but I think the word “need” is actual
problem and it has to go.  We use the phrase “escapes satisfying” in the
subsequent sentence and should use it here too.

Normally, a referencing row escapes satisfying the foreign key constraint
if any of its referencing columns are null.  [add: This is what is meant by
match simple].  If match full is …

Also, we go to the trouble of accepting “match partial”.  Maybe add a final
sentence in this paragraph nothing that we do so and explaining what
partial is defined to mean in the standard?  Also, we only note the not
implemented in the syntax parameters area, not the compatibility area…this
seems like an oversight.

David J.

Re: initdb username doc bug

[David G. Johnston]

On Saturday, January 6, 2024, David G. Johnston 
wrote:

> On Saturday, January 6, 2024, Bruce Momjian  wrote:
>
>>
>> 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.
>>
>
> So maybe a new defined term “cluster initializer”?
>

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.

David J.

Re: initdb username doc bug

[David G. Johnston]

On Saturday, January 6, 2024, Bruce Momjian  wrote:

> 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.
>

Ok, I see your point.  Given that the installation users becomes the
cluster owner by virtue of doing the only job they have, running initdb,
defining both seemed excessive but it is indeed more precise.  I would
probably avoid install user though because of the probable confusion with
the user that installed the distribution package as opposed to the one
initializing the cluster (we don’t call it installing a cluster).

So maybe a new defined term “cluster initializer”?

David J.

Re: initdb username doc bug

[David G. Johnston]

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.  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.

David J.

Re: Transition relation clarification

[David G. Johnston]

On Mon, Jan 1, 2024 at 9:35 AM Dull Bananas  wrote:

> The docs should mention that transition relations aren't automatically
> made available in functions called by the trigger function. This unknown
> behavior caused a lot of frustration for me.


Seems to fall into the "if it isn't documented it doesn't happen" category
of things that we simply choose not to clutter the documentation with.
Called functions don't inherit stuff from the caller.  It is like
documenting a variable in the calling function isn't visible to the called
function.  Admittedly at least with variables you can pass them via
arguments.  The special trigger state is indeed available in the trigger
returning function when it is the function directly invoked by the trigger.

David J.

Re: string_agg

[David G. Johnston]

On Wednesday, December 27, 2023, PG Doc comments form <
nore...@postgresql.org> wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/functions-aggregate.html
> Description:
>
> Please add the argument "distinct" to the documentation of the
> aggregate-function "string_agg". It's used to omit any duplicates in the
> result string.
>

string_agg isn’t special in this regard so instead of cluttering up every
function with distinct we instead choose to document it as a syntax feature
of aggregate functions in general.

https://www.postgresql.org/docs/16/sql-expressions.html#SYNTAX-AGGREGATES

David J.

Re: Documentation does not describes format for access privileges: =Tc/user

[David G. Johnston]

On Monday, December 25, 2023, David G. Johnston 
wrote:

> On Monday, December 25, 2023, Eugen Konkov  wrote:
>
>> No, it does not. If you refer to `An empty grantee field in an aclitem
>> stands for PUBLIC.` then "grantee field" was never described. What is
>> this?
>>
>> It would be very clear if it was described in this way:
>> The access privileges has the following format: "grantee=privileges/who
>> grants".
>>
>>
> Yes, it requires a bit of mental gymnastics to read.  The description says
> Calvin is the role being granted the privileges which makes that the
> grantee and Calvin is listed before the equal sign in the reference.
>
> “Who grants” is the “grantor”.
>
> I’ll accept that this can be improved but aside from a dictionary
> definition of grantee, which we don’t usually do, everything is shown.
>

We probably should write the syntax like we do everywhere else:

[grantee]={privilege[*]}[…]/grantor

Then define the placeholders in the subsequent paragraph.

David J.

Re: Documentation does not describes format for access privileges: =Tc/user

[David G. Johnston]

On Monday, December 25, 2023, Eugen Konkov  wrote:

> No, it does not. If you refer to `An empty grantee field in an aclitem
> stands for PUBLIC.` then "grantee field" was never described. What is
> this?
>
> It would be very clear if it was described in this way:
> The access privileges has the following format: "grantee=privileges/who
> grants".
>
>
Yes, it requires a bit of mental gymnastics to read.  The description says
Calvin is the role being granted the privileges which makes that the
grantee and Calvin is listed before the equal sign in the reference.

“Who grants” is the “grantor”.

I’ll accept that this can be improved but aside from a dictionary
definition of grantee, which we don’t usually do, everything is shown.

David J.

Re: Documentation does not describes format for access privileges: =Tc/user

[David G. Johnston]

On Friday, December 22, 2023, PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/ddl-priv.html
> Description:
>
> Hello.
> The page https://www.postgresql.org/docs/current/ddl-priv.html does not
> describe what =Tc/user means. Also I did not find a link to appropriate
> page
> which describes this.
> Specifically I do not understand how 'user=Tc/user' differs from
> '=Tc/user'.
>
> It would be nice if documentation will be extended.
>
>
The paragraph immediately following table 5.2 describes all of this.

David J.

Re: unclear wording re: spoofing prevention on network connections

[David G. Johnston]

On Saturday, December 9, 2023, Stephen Frost  wrote:

>
>
> The idea is that you can use both TLS and GSSAPI-with-encryption at the
> same time within a given cluster for connections but you wouldn’t use them
> on the same connection.  Certainly would welcome suggestions as to the best
> way to phrase that.
>

It isn’t really connection driven though - or even specific to these two
options.  The pg_hba.conf file can contain any number of different
authentication methods that are usable simultaneously (from the perspective
of the cluster).  But a given login request is only going to match a single
one of those lines; so it isn’t like the client somehow decides during each
login using the same machine and user name which way they are going to
verify who they say they are.

We don’t call out being able to use password and peer simultaneously, the
description and specification of the pg_hba.conf file itself imparts that
information.  I’m unclear why these two would warrant a special calling out.

David J.

Re: Where is using a table name as a "row value" documented?

[David G. Johnston]

On Friday, December 8, 2023, Gulyás Attila  wrote:

>
> Unfortunately, neither the row constructor docs[7] nor the pages
> referenced there mention this usage anywhere (unless I missed it somehow).
> I also found this answer[8] helpful when trying to find the relevant docs.
>

I found this fairly,quickly when looking for what can be used as a column
reference.  But I agree that this might need to be documented elsewhere as
well.

https://www.postgresql.org/docs/current/rowtypes.html#ROWTYPES-USAGE

David J.

Re: Missing "CONSTRAINT" keyword in ADD CONSTRAINT form?

[David G. Johnston]

On Monday, December 4, 2023, Tim Needham  wrote:

> Bah, scratch that, sorry again. It's because "CONSTRAINT" is implied by
> the reference to CREATE DOMAIN, isn't it?
>

Yes, we require the reader to go to the create domain page to read the
“domain_constraint” portion of the syntax.

David J.

Re: Clarification regarding CREATE TABLE LIKE and FOREIGN KEYS

[David G. Johnston]

On Fri, Dec 1, 2023 at 7:07 AM David Virebayre  wrote:

> "INCLUDING ALL" leads people to believe *everything* is copied.
>
> The fact that it doesn't seem counter intuitive, at least to me.
>
> Therefore, it doesn't seem absurd to me to warn people about what is not
> copied.
>
> I wrote the original message hoping to help improve the documentation. I
> could write a draft if I'm allowed to.
> I do believe that clarifying this point improves the documentation. If
> there's a consensus that it doesn't, my apologies for the annoyance.
>
>
You are welcome to suggest improvements.  But "all" means inclusive of
every individual item listed on that page.  You don't get stuff in "all"
that you cannot specify individually and you cannot specify references
clauses individually.

David J.

Re: Table-space documentation

[David G. Johnston]

On Sunday, November 26, 2023, PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/manage-ag-tablespaces.html
> Description:
>
> While reading the table-space documentation
> https://www.postgresql.org/docs/16/manage-ag-tablespaces.html the
> information I was looking for was not included.  The page invites feedback,
> so I though I would share.  I was specifically looking for information on
> maximum table-space size and whether there is a use-case in which you would
> create several table-spaces to split your tables / indexes logically.  I am
> from an Oracle world and it is normal for me to think about creating
> multiple table-spaces for groups of tables and placing a size on the
> table-space so that space does not extend and fill the o/s disk-space.  It
> would be nice for the documentation to include information about this
> topic.
>  Even if it is says that these factors I should not worry about as postgres
> handles them.
>

We tend to avoid documenting things that don’t exist.  So the lack of a
documented limit or any syntax to define one means no limit exists.

As for usage patterns, there is a note I thought (maybe wiki FAQ?) that
since the only thing you get with a tablespace is another root level
storage place having more than one per disk really serves no purpose.

David J.

Re: Mention that there will be no feedback.

[David G. Johnston]

On Thursday, November 23, 2023, 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:
>
> You might want to mention that createdb will give no feedback after
> creating
> database. Neither will dropdb.
>

But it does, like all commands it produces an exit code, which is 0 for
success.

David J.

Re: [DOCS] Confusing Trigger Docs.

[David G. Johnston]

On Wed, Nov 22, 2023 at 2:13 PM Bruce Momjian  wrote:

> 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.
>
>
There is too much exposition drowning out the main purpose here which is to
explain how the dual trigger situation introduced with on conflict gets
handled.  The following is a more direct approach.

If an insert command contains an on conflict do update clause, before
insert row triggers will be
applied to the proposed row before conflict detection.
If the update branch is taken, before update row triggers will also be
applied.
Either an insert or an update after row trigger will fire for each row.
Before statement triggers fire for insertions first and then for updates,
while
after statement triggers fire in the reverse order, updates and then
inserts.
Statement triggers fire regardless if any rows were actually inserted or
updated.


Tangentially, having the partition table content between this and the merge
content seems odd.  There also seems to be room to integrate this and merge
a bit better but that is beyond what I want to try right now.

David J.

Re: [DOCS] Add example about date ISO format

[David G. Johnston]

On Wed, Nov 22, 2023 at 12:26 PM Bruce Momjian  wrote:

> 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.
>
>
Most of our tables have description columns, we could add one here.  Or
I've seen us use footnote superscripts before in a table then add the
footnote text after the end of the table.

I'm against incorporating this material into the data types in Chapter 8.

David J.

Re: SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases

[David G. Johnston]

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.

Thanks!

David J.

Re: SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases

[David G. Johnston]

On Mon, Nov 20, 2023 at 7:04 PM Bruce Momjian  wrote:

> 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?
>
>
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 [...]

David J.

Re: SELECT ... FOR UPDATE OF clause documentation implies use of table_names rather than aliases

[David G. Johnston]

On Fri, Nov 17, 2023 at 3:13 PM Bruce Momjian  wrote:

> 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.
>
> The attached patch documents this.
>
>
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.

David J.

Re: Another user complaint regarding visibility of pg_catalog data

[David G. Johnston]

On Wednesday, November 8, 2023, Laurenz Albe 
wrote:

>
>
> When people ask my "why?", I tend to answer "why not?".  It is not a
> security
> problem, in my opinion.  Every user is allowed to know that I have a table
> "purchase" with a column "credit_card_nr".  As long as the permissions are
> set
> correctly, that is no problem.  Any attempt to hide that information is at
> best
> "security by obscurity".
>

The typical answer is some variant of trade secrets.  Though wanting to
store private info in a comment has some merit too.

David J.

Re: Another user complaint regarding visibility of pg_catalog data

[David G. Johnston]

On Tue, Nov 7, 2023 at 12:28 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> Is this something we just don't want to go into detail within our
> documentation, or just no one has cared enough to write something up
> (beyond my first draft back then) and form it into a patch?
>

I've gone and added an FAQ entry for this at least so I can post a link to
that in the future.

https://wiki.postgresql.org/wiki/FAQ#How_do_I_prevent_regular_users_from_seeing_my_trade_secrets.3F

I'm not married to the title or content but wanted to get something out
there while my mind was engaged on the topic.

David J.

Another user complaint regarding visibility of pg_catalog data

[David G. Johnston]

Hey,

This comes up every so often (including today on Discord) and I keep having
trouble figuring out where to point people for our official assertion and
explanation for why anyone with a login can view routine bodies, view
specifications, and comments.

Apparently I griped about this a while ago and it fell on deaf ears:

https://www.postgresql.org/message-id/1424231867994-5838367.post%40n5.nabble.com

Is this something we just don't want to go into detail within our
documentation, or just no one has cared enough to write something up
(beyond my first draft back then) and form it into a patch?

David J.

Re: Example for Unique Partial Indexes

[David G. Johnston]

On Tue, Nov 7, 2023 at 10:08 AM Tom Lane  wrote:

> "David G. Johnston"  writes:
> > On Tue, Nov 7, 2023, 09:25 PG Doc comments form 
> > wrote:
> >> Currently, the documentation does not provide information on how to
> create
> >> unique partial indexes. Unique partial indexes are valuable for
> enforcing
> >> uniqueness of a column's value over a subset of a table
>
> > 11.8 discusses this in detail including an example.  Maybe should add a
> > forward reference from 11.6 though.
>
> Yeah, AFAICS 11.8's coverage of this point is perfectly adequate.
>
> I'm disinclined to add a forward reference, because 11.8 is the
> first section that mentions partial indexes at all.  Somebody
> reading the chapter in order would have no idea what we were
> talking about.
>
>
I do understand that argument but given that uniqueness comes up
considerably more often than partial indexes for me it makes sense to
inform the reader learning about unique constraints about what else exists
in the world and not just presume they are reading the documentation
serially (or will take the time to really dive into 11.8).

"Sometimes you only care about uniqueness for data within a particular
state (say an active state) and do not care if (say, inactive) data exists
as well, possibly multiple times.  A unique index by itself cannot handle
this situation but you can combine it with a partial index for the active
state data (as described in 11.8) to achieve this limited uniqueness
requirement."

David J.

Re: Example for Unique Partial Indexes

[David G. Johnston]

On Tue, Nov 7, 2023, 09:25 PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/indexes-unique.html
> Description:
>
> I would like to request an improvement to the PostgreSQL documentation,
> specifically in the sections "11.6. Unique Indexes" and "11.8. Partial
> Indexes."
>
> Currently, the documentation does not provide information on how to create
> unique partial indexes. Unique partial indexes are valuable for enforcing
> uniqueness of a column's value over a subset of a table
>


11.8 discusses this in detail including an example.  Maybe should add a
forward reference from 11.6 though.

David J.

Re: jsonb array accessors

[David G. Johnston]

On Saturday, November 4, 2023, PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/datatype-json.html
> Description:
>
> https://dbfiddle.uk/Dr-c-nqL
>
> multiple subscripts for jsonb arrays do not behave as described on Table
> 8.25 in Postgres 16 docs / 8.14.7 jsonpath
>

That table describes jsonpath operations.  Your example doesn’t involve
jsonpath at all; it uses an SQL-scoped operation/syntax.

You will need to use an operator or function that accepts jsonpath as its
argument if you want to avail yourself of jsonpath functionality.

David J.

Re: 'pg_global' cannot be used as default_tablespace.

[David G. Johnston]

On Wednesday, November 1, 2023, Bruce Momjian  wrote:

> 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?
>

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.

David J.

Re: Full Text Search

[David G. Johnston]

On Sun, Oct 29, 2023, 13:58 PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/14/textsearch-limitations.html
> Description:
>
> Hello,
>
> In the FTS/Limitations part of the documentation, it says :
>
> "Another example — the PostgreSQL mailing list archives contained 910,989
> unique words with 57,491,343 lexemes in 461,020 messages."
>
> How could the number of lexemes be greater than unique words ?
>


https://www.postgresql.org/docs/14/textsearch-parsers.html

Note the part with the hyphenated word example.

David J.

>

Re: pg_isready --dbname option is broken. So it should not be in the manual

[David G. Johnston]

On Thursday, October 26, 2023, Daniel Gustafsson  wrote:

> > On 26 Oct 2023, at 14:20, David G. Johnston 
> wrote:
> >
> > On Thursday, October 26, 2023, PG Doc comments form <
> nore...@postgresql.org <mailto:nore...@postgresql.org>> wrote:
> > The following documentation comment has been logged on the website:
> >
> > Page: https://www.postgresql.org/docs/16/app-pg-isready.html <
> https://www.postgresql.org/docs/16/app-pg-isready.html>
> > Description:
> >
> > the --dbname option in pg_isready seems not to work propperly. the tool
> > returns 'ok' as long as the cluster itselft is running, no matter how
> wrong
> > the bdname might be.
> >
> > as this seems to be a ~10 year old misbehaviour as per the below thread I
> > think it should be removed from the manual.
> >
> > https://www.postgresql.org/message-id/flat/52840D38.
> 9070604%40agliodbs.com <https://www.postgresql.org/
> message-id/flat/52840D38.9070604%40agliodbs.com>
> >
> > Read the notes section.
>
> The notes section is pretty hidden though, I can sympathize with anyone
> missing
> it and maybe making the info a bit more visible would be good?
>

Add a “see notes” link to those two parameters.

But we put lots of stuff like this into the notes everywhere; DBAs are
expected to look for and read them on the man pages they consume.  This
doesn’t seem to fit into the description nor warrants a callout.

David J.

Re: pg_isready --dbname option is broken. So it should not be in the manual

[David G. Johnston]

On Thursday, October 26, 2023, PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/app-pg-isready.html
> Description:
>
> the --dbname option in pg_isready seems not to work propperly. the tool
> returns 'ok' as long as the cluster itselft is running, no matter how wrong
> the bdname might be.
>
> as this seems to be a ~10 year old misbehaviour as per the below thread I
> think it should be removed from the manual.
>
> https://www.postgresql.org/message-id/flat/52840D38.9070604%40agliodbs.com
>

Read the notes section.

David J.

Re: opclass. See below for details. cannot found the "below".

[David G. Johnston]

On Sunday, October 22, 2023, jian he  wrote:

> https://www.postgresql.org/docs/current/sql-createindex.html
> <<
> opclass
> The name of an operator class. See below for details.
> <<
>
> there is no "below"?
> Browser search, there are only four appearances of keyword "opclass".
>
>
The notes on that discuss this a bit further then give you two links for
more info, one being:

https://www.postgresql.org/docs/current/indexes-opclass.html

Why would you search for “opclass” and not “operator class” - the later is
used in the notes paragraph that is being referred to?

David J.

Re: docs: set role permission checking, do I read this wrong?

[David G. Johnston]

On Wednesday, October 18, 2023, Daniel Westermann <
daniel.westerm...@dbi-services.com> wrote:
>
> Thank you, this is what I see in the small example. Maybe it is my
> English, but this sentence sounds confusing.
>

How would you document that behavior? The sentence is correct; that doesn’t
mean it can’t be improved.

David J.

Re: docs: set role permission checking, do I read this wrong?

[David G. Johnston]

On Wednesday, October 18, 2023, Daniel Westermann <
daniel.westerm...@dbi-services.com> wrote:

>
> "After |SET ROLE|, permissions checking for SQL commands is carried out as
> though the named role were the one that had logged in originally."
>
> Isn't it the other way around and permission checking is done as "a", or
> do I read this wrong?
>

It is saying “a” is the current_user:

When you set role to (named role) a the system behaves as if (named role) a
had logged in originally (even though, in that example, postgres is the
role that originally logged in)

David J.

Clarify: default precision on timestamps is 6

[David G. Johnston]

On Friday, October 13, 2023, PG Doc comments form 
wrote:

> both of them round any precision
> beyond microseconds, and neither returns timestamps with greater precision
> than the value that was inserted.
>

That is precisely what a no default with maximum of six means.  If we say
the default is six that would imply storage of less precise values pads
significant zeros until there are six.

David J.

Re: 31.7.1. Initial Snapshot

[David G. Johnston]

On Wed, Oct 11, 2023 at 9:41 AM Alvaro Herrera 
wrote:

> Maybe a fix for this would be to style chapter TOCs in some way that
> makes it clear that they are TOCs -- for example, add a (subtly) visible
> bounding box, or something.  Or maybe if a chapter has a single section,
> just do not print the TOC at all.  I have no idea how to implement such
> a fix, or whether it'd be really acceptable after all.
>

Or move 4 paragraphs of introductory material into its own section so that
there are two sections and a brief sentence for an intro.

I don't see a special case for a single section to be a productive use of
time.  Improved formatting overall for the chapter ToC has merit.

David J.

Re: Wrong article SET AUTOCOMMIT

[David G. Johnston]

On Wed, Oct 11, 2023 at 9:34 AM PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/15/ecpg-sql-set-autocommit.html
> Description:
>
> This article should be removed as this parameter does not exist since a
> long
> time version 9.4
>

I suspect you are confusing the server setting for autocommit with
client-side auto-commit.  The server no longer implements auto-commit
declaratively but almost every client out there does.  In this case ECPG is
a client environment and it indeed does implement auto-commit.

David J.

Re: 31.7.1. Initial Snapshot

[David G. Johnston]

On Wed, Oct 11, 2023 at 9:32 AM PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page:
> https://www.postgresql.org/docs/16/logical-replication-architecture.html
> Description:
>
> There are dublicated section named "31.7.1. Initial Snapshot" on
> https://www.postgresql.org/docs/16/logical-replication-architecture.html


What you are seeing is the first instance of 31.7.1 is a table of contents
entry (hyperlink too) for the section.  Then you have the chapter
introductory material.  Then you have section 31.7.1 itself.

David J.

Re: Typo in PL/pgSQL trigger Example 43.4?

[David G. Johnston]

On Sat, Oct 7, 2023 at 11:11 AM Kirk Parker  wrote:

>
> INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*; -- <=
> ARGUMENT IN QUESTION
>  The emp_audit table has a column named 'userid', which in actual usage
> (next-to-last line quoted) is populated by 'user' which seems undefined in
> the context.  Was that intended to be 'current_user', or am I missing
> something?
>

user is a valid pseudo-function:

https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-SESSION

David J.

Re: It is not clear from documentation when and how I should restore base backup

[David G. Johnston]

On Thu, Oct 5, 2023, 11:11 PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/continuous-archiving.html
> Description:
>
> Hello.
>
> On page:
>
> https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-PITR-RECOVERY
> Is is not clear should I do additional actions to restore basebackup first
> and then WAL files (as described at p6)
>

You mean step 4?


> This paragraph even more confusing:
>
> https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-STANDALONE
> > ... and no special action is required to restore the backup.
> Should I configure restore_command at least?
>

It is standalone, everything needed and possible exists in the backup file
pg_basebackup created.

David J.

Re: Unclear guarantees about sort order on https://www.postgresql.org/docs/current/queries-order.html

[David G. Johnston]

On Wed, Oct 4, 2023 at 6:37 PM Erik Wienhold  wrote:

> On 2023-10-04 16:24 +0200, PG Doc comments form write:
> > The following documentation comment has been logged on the website:
> >
> > Page: https://www.postgresql.org/docs/16/queries-order.html
> > Description:
> >
> > The document only says this about unsorted queries:
> >
> > > After a query has produced an output table (after the select list has
> been
> > > processed) it can optionally be sorted. If sorting is not chosen, the
> rows
> > > will be returned in an unspecified order. The actual order in that
> case will
> > > depend on the scan and join plan types and the order on disk, but it
> must
> > > not be relied on. A particular output ordering can only be guaranteed
> if the
> > > sort step is explicitly chosen.
> >
> > It mentions "If sorting is not chosen". This sort of implies that if you
> > pick a sort the output order is predictable. However I believe that the
> only
> > actual guarantee is if the sort columns selected produce a unique value.
> >
> > For example if you do `ORDER BY name` and have two rows with the same
> name I
> > don't think the order of those rows is predictable.
>
> "The relative ordering of two rows that are not distinct with respect to
>  the  is implementation-dependent."
>

The OP is assuming a promise of a deterministic ordering of all output rows
and such a promise is only possible if the order by clause columns uniquely
identify every row in the output.  This is because all the order by
promises is that output ordering will conform to the order by
specification, and indeed if it is under-specified such that multiple rows
match a given bin, then there is no deterministic relative ordering among
those rows.

I don't feel that the wording makes any such inference regarding
determinism of row output due to the mere presence of an order by clause.
Nor doesn't such determinism in the face of an under-specific clause even
make logical sense.  I'm mostly inclined to leave the wording alone given
this single report.  My only complaints are style-istic at this point.

That said, maybe a final sentence:

Assuming every output row can be uniquely identified by some subset of the
output columns, that subset must all be listed within the order by clause
if you wish to ensure a fully deterministic ordering.

David J.

Re: This is too implicit that recovery.signal will be removed

[David G. Johnston]

On Thu, Oct 5, 2023 at 8:15 AM PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/runtime-config-wal.html
> Description:
>
> Hello.
> On the page
> https://www.postgresql.org/docs/current/runtime-config-wal.html
> Actual:
> >To start the server in targeted recovery mode, create a file called
> recovery.signal in the data directory. If both standby.signal and
> recovery.signal files are created, standby mode takes precedence. Targeted
> recovery mode ends when the archived WAL is fully replayed, or when
> recovery_target is reached. In this mode, the parameters from both this
> section and Section 20.5.6 will be used.
>
> Expected:
> >To start the server in targeted recovery mode, create a file called
> recovery.signal in the data directory. If both standby.signal and
> recovery.signal files are created, standby mode takes precedence. Targeted
> recovery mode ends and recovery.signal file is removed when the archived
> WAL
> is fully replayed, or when recovery_target is reached. In this mode, the
> parameters from both this section and Section 20.5.6 will be used. File
> 'recovery.signal' is not removed when `recovery_target_action` is
> *shutdown*.
>
>
Except that doesn't seem to be true since "pause" is the default action.

We probably should add instead:

Targeted recovery mode ends when the archived WAL is fully replayed, or
when recovery_target is reached.
The server is left in the state corresponding to the recovery_target_action
setting.

David J.

Re: Corresponding documentation page does not mention about `spread` mode

[David G. Johnston]

On Tue, Oct 3, 2023 at 2:48 PM PG Doc comments form 
wrote:

>
> On [this
> page](https://www.postgresql.org/docs/current/app-pgbasebackup.html)
> `spread` is mentioned:
>
> -c {fast|spread}
> --checkpoint={fast|spread}
>
> Sets checkpoint mode to fast (immediate) or spread (the default) (see
> Section 26.3.3).
>
> But mentioned section 26.3.3 does not describe `spread` mode:
>
> https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP
>
>
It does, though it is phrased in terms of "fast => false" meaning "spread"
whereas "fast => true" means "immediate".

But since the CLI wanted a label instead of true/false it had to call the
default "not fast" mode something and it chose "spread".

Not saying this couldn't be made more clear/better but it is accurate and
complete.

David J.

Re: missing GRANTED BY explanations

[David G. Johnston]

On Saturday, September 30, 2023, PG Doc comments form <
nore...@postgresql.org> wrote:
>
>
> In the documentation for revoke command, there is no description for
> GRANTED
> BY clause.
>
>
As the doc says read the grant page for more detailed information.
Repeating everything here was deemed redundant.

David J.

Re: missing GRANTED BY explanations

[David G. Johnston]

On Sat, Sep 30, 2023 at 1:56 PM PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/sql-revoke.html
> Description:
>
> Hi,
> In the documentation for revoke command, there is no description for
> GRANTED
> BY clause.
> please explain that what will happen when using this clause in revoke
> command.
>
>
Grants specifications include the role doing the granting.  So when you
remove a grant you have to also specify which role the grant came from.
That clause lets you specify said role explicitly.

David J.

Re: Not clear spelling about transaction

[David G. Johnston]

On Sat, Sep 30, 2023 at 1:07 PM PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/continuous-archiving.html
> Description:
>
> >No committed transactions will be lost
>
> Hello. It is not clear to me. Did you mean:
> Not committed transactions will be lost
> Or
> None of committed transactions will be lost
> ?
>
> It would be simpler if you write that explicitly:
> All committed transactions will not be lost.
>

"Committed transactions will not be lost."

The word "No" means "none", it doesn't mean "not".

David J.

Re: `pg_restore --if-exists` clarification

[David G. Johnston]

On Thursday, September 28, 2023, Gulyás Attila  wrote:

> So `--if-exists` simply suppresses any notice / warning that would occur?
>

That switch causes the drop DDL produced by pg_restore to include if exists
clauses.  The server then simply treats the DDL as a no-op if said object
being dropped cannot be found.


>
>> But "failure" is not what happens.  If you read the part regarding
>> --clean, you will see that it says:
>>
>> (Unless --if-exists is used, this might generate some *harmless error
>> messages*, if any objects were not present in the destination database.) 
>> *[emphasis
>> added]*
>> with extra emphasis on the word "harmless" --  no failure is caused; the
>> restore proceeds just fine.
>> If anything were to change in this regard, it might be better to
>> reconsider what we call the message (i.e.what language pg_restore emits in
>> this scenario.)  It's true, I suppose, in a literal sense that it's an
>> error in that pg_restore couldn't drop a table when instructed to, where no
>> such table exists.  But pragmatically it doesn't matter, so why not
>> reclassify this as a "warning" or a "notice"?
>>
>
Because pg_restore is just a client and it is repeating back what the
server tells it.  And for the server it is an error to drop an object that
doesn’t exist.

psql and pg_restore, as clients, can choose to ignore the errors they see,
regardless of what kind of error it is, but they don’t take on the added
burden of trying to reclassify errors into something else.

David J.

Re: pg_upgrade doc uses inconsistent versions within the doc.

[David G. Johnston]

On Tue, Sep 26, 2023 at 10:35 AM Bruce Momjian  wrote:

> On Mon, Sep 18, 2023 at 08:14:04PM +, PG Doc comments form wrote:
> > The following documentation comment has been logged on the website:
> >
> > Page: https://www.postgresql.org/docs/16/pgupgrade.html
> > Description:
> >
> > I feel like for readability the pg_upgrade doc should use the same
> > old_version and new_version examples when showing examples throughout the
> > doc page.
> >
> > as an exmaple I'm looking at :
> > https://www.postgresql.org/docs/current/pgupgrade.html
> >
> > in some places the example commands use v9.6 and v16. (see step 8)
> >
> > but in other places it mixes versions v9.5 and v9.6 (see step 11 sub
> step 8)
>
> Good point.  I came up with the attached patch.  It uses 9.6 and current
>

I would get rid of any mentions of our old pre-v10 versioning scheme in the
current documentation.

David J.

Ambiguous statement about order

[David G. Johnston]

On Thursday, September 14, 2023, PG Doc comments form <
nore...@postgresql.org> wrote:
>
> I observed that arrays inside JSONB preserved order of insertion. An
> explicit line about whether the array order will be preserved or not would
> be helpful.


We don’t take on responsibility for teaching people what json is.  Json
arrays are slotted composite elements where the slots are numerically
indexed.  That, by definition, makes their contents “ordered”.  The fact
that object keys are unordered and that arrays can be object keys is
immaterial to whether the contents of arrays are ordered.

David J.

Re: General Feedback

[David G. Johnston]

On Mon, Aug 28, 2023 at 7:33 AM PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/15/sql-createschema.html
> Description:
>
> "To create a schema, the invoking user must have the CREATE privilege for
> the current database. (Of course, superusers bypass this check.)" - maybe
> you can show how to check if the user has the CREATE privilege? Or is this
> omitted on purpose so readers will take their own initiative to find that
> out themselves :-)
>

In the interest of brevity it is assumed the reader is familiar with the
definitional chapter regarding privileges and can use the knowledge gained
there to perform such database inspection.

https://www.postgresql.org/docs/current/ddl-priv.html

David J.

Re: Create Role Documentation - Not Clear for new users

[David G. Johnston]

On Monday, August 21, 2023, PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/15/database-roles.html
> Description:
>
> Hi! Hope you are doing great!
>
>  I'm trying to learn about PostgreSQL, I'm totally new, and I think that
> for
> people like me, the documentation is not clear, for example, you are
> stating
> that we need to run the ` CREATE ROLE name; `, but you are not specifying
> if
> that is directly on the terminal?
>
> In my case, I'm trying to run that on my terminal and I'm getting:
> `CREATE: not found`,
>
> I think would be great if that part could be improved!
>
> I'm reading that in chapter 22 because in chapter 1.4 I'm being referenced
> to chapter 22
> Thanks for letting me share :D my thoughts!
>

All SQL is to be run within the psql program or whatever client you choose
to execute SQL from.  Pointing that out everywhere would be exceedingly
tedious.

David J.

Re: In docs there is no "Installation from Binaries" section

[David G. Johnston]

On Sun, Aug 20, 2023 at 8:56 AM PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/15/install-binaries.html
> Description:
>
> Installing software from ZIP archive is a common practice, pgsql provides
> such archive, but doesn't provide instructions on what to do with it.


Where are you seeing such a zip archive?  I'm familiar with us producing
tar gzip archives of the source code for compiling, but we don't produce
binaries that I know of.

https://www.postgresql.org/docs/current/install-getsource.html

David J.

Re: ALTER TABLE "table" ALTER COLUMN "id" SET RESTART WITH nnn raises error

[David G. Johnston]

On Sun, Aug 20, 2023 at 8:56 AM PG Doc comments form 
wrote:

> When the folloing SQL is executed:
> ALTER TABLE "table" ALTER COLUMN "id" SET RESTART WITH nnn raises error
> then an error is returned:
>

Yes, because if you take the RESTART branch in the syntax there is no word
SET preceding RESTART.

Please fix the documentation or the functionality.
>

The documentation is already correct.

David J.

Re: word incorrectly on document

[David G. Johnston]

On Tue, Aug 1, 2023 at 1:39 PM PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/15/executor.html
> Description:
>
> Document location: 52.6. Executor
> fed up -> feed up
>

My instinct says the existing wording is correct.  FWIW, ChatAI[1] agrees,
and explicitly disagrees with "feed".

I suspect the use of passive voice in the present tense is confusing
things.  "The executor feeds the data to the ModifyTable node", but its
"the ModifyTable node is fed by the executor".  See the explanation below
which matches my experience here.

David J.

[1]
The sentence "The meat is feed into the machine" is not valid English in
terms of spelling. The correct form of the verb in this context is "fed,"
not "feed." The correct sentence is: "The meat is fed into the machine."

The verb "fed" is the past participle of the verb "feed," which is used in
passive constructions to indicate that something is being given or supplied
to an object or subject. In this case, the meat is the object, and it is
receiving the action of being fed into the machine.

Re: Uniquness of ctid

[David G. Johnston]

On Wed, Jul 19, 2023 at 2:31 PM PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/15/ddl-system-columns.html
> Description:
>
> Hi,
> Looking at the explanation abour ctid, it is "The physical location of the
> row version within its table. "
> From that line, I think ctid is unique in the table.
>

Unique but not stable - if you give your actual record an ID value the
associated ctid for it may very well change over time and a given ctid can
be associated with any number of IDs

> And I also think ctid might be unique across the database since it is the
> physical location.
>

The concept doesn't even apply - the value itself only makes sense within a
given physical table. i.e., the table is implied. It's like saying "I live
at 123 Main St." to someone.  Sure in any given place there can only be a
single 123 Main St. but that really isn't useful by itself.  And to extend
back to the previous point, you may live there now but you will likely have
a different address in the future and someone else will have 123 Main St.

David J.

Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html

[David G. Johnston]

On Mon, Jul 17, 2023 at 7:45 PM Curt Kolovson  wrote:

> I’d vote for showing both (with RETURNING and without), since without it
> the second argument to SPI_exec has no effect in this example, which may
> not be obvious. That seems to be one of the subtle points illustrated by
> this example.
>

I concur:

=> SELECT execq('CREATE TABLE a (x integer)', 0); // start at 0
=> INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0)); // insert 2
=> SELECT execq('SELECT * FROM a', 0);

+ => SELECT execq('INSERT INTO a SELECT x + 2 FROM a returning x', 1); --
one more
+ -- (IIUC non-deterministically regardless of observed behavior even if
there were a limit in the SELECT)

=> SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1); // three more
=> SELECT execq('SELECT * FROM a', 10); // 6 as 10 is a limit

...[next block]
David J.

Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html

[David G. Johnston]

On Mon, Jul 17, 2023 at 6:22 PM Tom Lane  wrote:

> "David G. Johnston"  writes:
> > On Mon, Jul 17, 2023 at 4:53 PM Curt Kolovson 
> wrote:
> >> The actual results (shown below) are different than shown on this doc
> >> page.
>
> > SPI_exec sees "INSERT 0 2" as the command tag from the SQL command you
> > passed and so 2 is the output of the execq function call.
> > No INFO messages appear because you did not include a returning clause.
> > The 1 you passed to the call is immaterial if the query you supply
> doesn't
> > produce a result set.
>
> I think his point is that this example does not behave as the
> documentation claims.  Which it does not, according to my
> tests here.  I find this a bit disturbing --- did we intentionally
> change the behavior of SPI_exec somewhere along the line?
>
>
Appears to be a documentation fix oversight back in v9.0

https://github.com/postgres/postgres/commit/2ddc600f8f0252a0864e85d5cc1eeb3b9687d7e9

We fixed the wording for the API argument but not the example that
demonstrated it.

David J.

Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html

[David G. Johnston]

On Mon, Jul 17, 2023 at 4:53 PM Curt Kolovson  wrote:

> The actual results (shown below) are different than shown on this doc
> page. The reason is because the second parameter to the UDF that is
> passed to SPI_exec is the maximum number of rows to return, or 0 for
> no limit. It is not the maximum number of rows to process. In the case
> of "SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1)", it returned
> 0 rows, but it inserted (processed) 2 rows. This example should be
> corrected.
>
>
> db=# SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1);
>  execq
> ---
>  2
> (1 row)
>
>
SPI_exec sees "INSERT 0 2" as the command tag from the SQL command you
passed and so 2 is the output of the execq function call.

No INFO messages appear because you did not include a returning clause.
The 1 you passed to the call is immaterial if the query you supply doesn't
produce a result set.

David J.

Re: Confusing reference to MERGE use inside WITH

[David G. Johnston]

On Sun, Jul 16, 2023 at 5:48 AM PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/15/queries-with.html
> Description:
>
> Hi.
>
> In the queries WITH documentation
> https://www.postgresql.org/docs/15/queries-with.html it is a bit confusing
> that MERGE is referenced in several places as being something you can use
> inside with, even though it can't.
>
> If you search for MERGE on the page, you will find the 2 first results is a
> reference saying MERGE can be used inside WITH,

the same as SELECT, INSERT,
> UPDATE, or DELETE.

No, they are not, see below.

The 3rd result then correctly states that MERGE cannot be
> used inside WITH.
>
>
Yes.

The first non-intro section says that the following syntax is allowed (you
can attach WITH /TO/ MERGE)

[ WITH with_query [, ...] ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]

https://www.postgresql.org/docs/current/sql-merge.html
The second section is saying that the "with_query" above cannot contain
MERGE, i.e., you cannot put MERGE /IN/ WITH

Namely because you cannot attach a RETURNING clause to MERGE.

The intro makes it quite clear as well:

Specifically, reading the last sentence makes it clear:

Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE,
or DELETE; and the WITH clause itself is attached to a primary statement
that can be a SELECT, INSERT, UPDATE, DELETE, or MERGE.

Here the inside is called the auxiliary while the outside is called primary.

David J.

Re: group by can use alias from select list

[David G. Johnston]

On Tue, Jul 11, 2023 at 7:19 AM Laurenz Albe 
wrote:

> On Fri, 2023-07-07 at 07:36 +, PG Doc comments form wrote:
> > i have a discussion in pgsql-b...@lists.postgresql.org about this and
> it is
> > mentioned in the answers that it is documented and a "feature" of
> Postgres
> > that this can be done.
> > If this is wanted the documentation
> > (https://www.postgresql.org/docs/current/sql-select.html) that
> mentioned:
> > [execution order of SELECT that says that GROUP BY is before SELECT]
> > isn't correct because how  can 4.) be done and the alias from 5.) is
> used?
> > Here is a hint important that there is an exception for alias used in
> group
> > by
>
> I think that is already documented:
>
>
I think the complaint is that someone seeing the behavior in the wild comes
to this order-of-operations and doesn't see that the observed behavior is
documented.  Sure, they can go into the GROUP BY section and figure out
that there is a "oh, by the way" comment within there that output
columns/aliases are indeed allowed.  But I tend to agree that a mention in
the "order of operations" section that output columns from step 5 can be
seen in step 4 seems like an improvement if we really want the order of
operations to be the main reference entry point for people trying to work
out query behavior.  Something like:  "(while unadvised it is possible for
the aliases defined in the next step to be used here as well)".

David J.

Re: Further clarification in documentation: No deletion of unreferenced large objects

[David G. Johnston]

On Mon, Jun 12, 2023 at 8:32 AM PG Doc comments form 
wrote:

You may be onto something, but:

>
> Page: https://www.postgresql.org/docs/15/datatype-binary.html


This page isn't relevant to the discussion at hand as it doesn't have
anything to do with large objects.  Whether it should would be a different
complaint.


> When deleting a row that references (contains) a large object, I think that
> most users expect the DBMS to take care of the, now unreferenced, BLOB.


On what grounds?  To me this looks just like any other foreign key
situation and removing FK rows does not impact the PK.  What would lead one
to think large objects behave differently?

David J.

Re: Sequence Dependency

[David G. Johnston]

On Saturday, June 10, 2023, Umut TEKİN  wrote:

>
> it does not create any pg_depend entry for this sequence and table pair. So, 
> it is not possible to track down to find the pairs. Is there any other way to 
> find the sequence and the table pairs created using method 2?
>
>
Parse the defaults and check for matching sequence names?

You can alter a manually created sequence to be owned by a table and thus
get the dependency added even in the second case.

David J.

Re: bpchar datatype is not equal to character(1) data type

[David G. Johnston]

On Tue, Jun 6, 2023 at 7:31 AM yanliang lei  wrote:

>
> Based on the above description, there is a deduction as follows:
>  bpchar datatype is equal to character(1) data type
>

Nope, bpchar is not equal to any user-facing data type by virtue of it
being an internal implementation-only data type.  Based upon your example
I'd say it is basically a length-unconstrained character equivalent and
there is no such user-facing data type defined.

David J.

Re: Typo

[David G. Johnston]

On Tuesday, May 23, 2023, Tom Lane  wrote:

> Laurenz Albe  writes:
> > On Wed, 2023-05-24 at 07:32 +0900, Michael Paquier wrote:
> >> This is the current sentence, and it sounds kind of OK to me, FWIW:
> >> "Postgres95 code was completely ANSI C and trimmed in size by 25%.
>
> > That uses "ANSI C" as an adjective, which I think is sloppy wording
> > (even though English is somewhat relaxed about the distinction between
> > classes of words).
>
> Yeah, it's not great English, but it's not awful English either;
> just a rather telegraphic (abbreviated) style.
>
> Here's the thing: at this point, this documentation is itself a
> historical artifact.  git excavation dates the current wording to
> 8baa8fcf4 of 1999-06-21, and that was just a small adjustment of
> c8cfb0cea of 1998-03-01, and it seems likely that that was pulled
> verbatim from some older source.
>
> So I'm disinclined to change it on grounds of "I think the grammar
> is a bit shaky".  It is what it is.
>
>
>

Agreed.  Besides, after a couple of more passes it grew on me, once I
filled in the missing “compared to what” sufficiently.

David J.

Re: Typo

[David G. Johnston]

On Tue, May 23, 2023 at 3:32 PM Michael Paquier  wrote:

> On Tue, May 23, 2023 at 08:52:25PM +, PG Doc comments form wrote:
> > There appears to be a typo, here:
> >
> https://www.postgresql.org/docs/current/history.html#:~:text=Postgres95%20code%20was%20completely%20ANSI%20C
> .
> > A word or two should be added between 'completely' and 'ANSI C', such as
> > 're-written in', or 're-coded using', or some such.
>
> This is the current sentence, and it sounds kind of OK to me, FWIW:
> "Postgres95 code was completely ANSI C and trimmed in size by 25%.
>
>
I agree with the OP, that is missing something.  Maybe:

Between the 4.2 release and the release of Postgres95 the code was made to
completely adhere to ANSI C and the size was reduced by 25%.

David J.

Re: DROP INDEX - dropping index of a table in a named schema

[David G. Johnston]

On Monday, May 22, 2023, PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/15/sql-dropindex.html
> Description:
>
> I had to find out (as it is not in the given documentation) that to drop an
> index for a table in a named schema (later "schemaname.tablename") you have
> to prefix the schemaname for the index inte DROP statement.
>

The create index docs say that an index for a table always exists within
the same schema as that table.  If that schema isn’t in your search_path
then it follows you need to schema qualify references to it.

David J.

Re: I think that the transaction tutorial document (3.4) should mention transaction isolation

[David G. Johnston]

On Wed, Mar 29, 2023 at 12:56 PM Bruce Momjian  wrote:

> On Thu, Mar  2, 2023 at 01:55:31PM +, PG Doc comments form wrote:
>
> > I think that a notice saying that for absolute transaction isolation you
> > should set the default transaction isolation to serializable, and be
> > prepared to have transactions fail, and referring to the more details
> > document, would help users prevent consistency bugs.
>
> Well, the point is that the application is _not_ retrieving a value and
> setting is +100, but rather having the SQL add 100, which is kind of the
> point.


I don't think a tutorial should get into more details than that.
>

I'm on the fence, but leaning toward agreeing in principle.  The following
paragraph from that page does cover the dynamics reasonably well under the
default configuration and the tutorial should assume the reader hasn't
changed the default.

"""
So transactions must be all-or-nothing not only in terms of their permanent
effect on the database, but also in terms of their visibility as they
happen. The updates made so far by an open transaction are invisible to
other transactions until the transaction completes, whereupon all the
updates become visible simultaneously.
"""

That said, the tutorial lacks any forward reference to non-tutorial
information for the reader that wants to expand their knowledge in this
area.  A paragraph mentioning isolation levels, including stating the
default that the tutorial was operating under, and where that and the other
modes are defined/explained, should be added to the end of that page.

David J.

Re: Confusion between psql options and pg_service file config

[David G. Johnston]

On Tue, Mar 28, 2023 at 1:30 PM PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/15/libpq-connect.html
> Description:
>
I feel like I'm going in circles trying to square `psql` options (15: psql)
> with connection service file parameters (15: 34.17).  I've lost exactly
> which page I started on, but it was something like this...
>
> - I start at https://www.postgresql.org/docs/15/app-psql.html
>   - Under "Options"
> -c _command_ shows that I can run an arbitrary command on connection.
> Great!
>
> - But I'd like to specify something similar in the pg_service file, so I
> look up I look up the Connection Service File, section 34.17 -
> https://www.postgresql.org/docs/15/libpq-pgservice.html


That isn't going to work.  You use the startup script, PSQLRC, if you want
to perform some actions after the connection is established.


>
>   - So far, so good. The "parameters are connection parameters; see
> [Section
> 34.1.2](
> https://www.postgresql.org/docs/15/libpq-connect.html#LIBPQ-PARAMKEYWORDS)
> for a list."
> - Following that link, I scroll down to the key word "options" which
> "Specifies command line options" presumably like those for psql.


This is an incorrect assumption.  The command line options are precisely
those that the server recognizes since the point of this feature is to
configure the initially established session.  In effect it is an
alternative to the "SET" SQL Command.


> What I have found particularly confusing is some of the keywords for the
> pg_service file clearly duplicate the options for `psql` but some of them
> are equivalent, but not identical keywords (eg, `psql --username` vs
> pg_service.conf keyword 'user').  Are both valid, or are they restricted to
> their respective realm?  (That's a question I'm asking the documentation to
> answer, not a question I'm directly asking you.)
>

You should assume that options described within a specific context are only
valid (or the only valid spelling) in said context.


>
> There is also no particularly clear connection between psql options and the
> connection service file; it's even hard to find where the app-psql.html
> page
> indicates that using a service name in conjunction with a connection
> service
> file can make invoking psql so much easier  :- )
>
> Additionally, since the concepts are so similar to each other,


I suppose, but the pg_service.conf file is a fairly small subset of the
things that psql has to deal with.  For the overlapping subset I do agree
there is great value in using the options defined in the pg_service.conf
file and then tell psql to use the named service instead of environment
variables or command line options.  But psql can do so much more, none of
which is related to establishing the initial connection to the database.

In short, "-c" has nothing to do with making an initial connection to the
database.  "-o" is a bit of a hack, and if you use a connection pooler (at
least pg_bouncer) one that isn't actually supported.

David J.

Seeming contradiction in 22.1

[David G. Johnston]

On Thursday, March 23, 2023, maja zaloznik  wrote:

>
> My point being that if what you say is true but runs counter to what
> essentially all other instructions on the internet say, it would seem
> prudent to mention this disparity and the reasons for it in the canonical
> documentation, which would be especially valuable for noobs like me.
>

The root issue, IMO, is that all packagers have settled on the convention
of using Postgres for the superuser name, so if one only ever sees that, it
is easy to assume it is some kind of default of the software itself.  In
the face of consistent evidence, people not reading the documentation is my
supposed assumption for their ignorance of this point.  But reading the
docs, it is clear how the system really works (I.e., the name of the
bootstrap superuser comes is derived at initdb time from the environment
executing initdb).

David J.