PG 16 draft release notes ready

On 5/18/23 4:49 PM, Bruce Momjian wrote:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

Thanks for going through this. The release announcement draft will
follow shortly after in a different thread.

I learned a few things creating it this time:

* I can get confused over C function names and SQL function names in
commit messages.

* The sections and ordering of the entries can greatly clarify the
items.

* The feature count is slightly higher than recent releases:

release-10: 189
release-11: 170
release-12: 180
release-13: 178
release-14: 220
release-15: 184
--> release-16: 200

This definitely feels like a very full release. Personally I'm very excited.

Thanks,

Jonathan

On 5/18/23 4:49 PM, Bruce Momjian wrote:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

Still reading, but saw this:

Allow incremental sorts in more cases, including DISTINCT (David
Rowley)window

I didn't realize we had a DISTINCT (David Rowley) window, but it sounds
like an awesome feature ;)

Thanks,

Jonathan

On Thu, May 18, 2023 at 05:39:08PM -0400, Jonathan Katz wrote:

On 5/18/23 4:49 PM, Bruce Momjian wrote:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

Still reading, but saw this:

Allow incremental sorts in more cases, including DISTINCT (David
Rowley)window

I didn't realize we had a DISTINCT (David Rowley) window, but it sounds like
an awesome feature ;)

I have fixed this and several others. The URL will show the new text.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Thu, May 18, 2023 at 1:49 PM Bruce Momjian <bruce@momjian.us> wrote:

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

I learned a few things creating it this time:

* I can get confused over C function names and SQL function names in
commit messages.

The commit history covering pg_walinspect was complicated. Some of the
newly added stuff was revised multiple times, by multiple authors due
to changing ideas about the best UI. Here is some concrete feedback
about that:

* Two functions that were in 15 that each end in *_till_end_of_wal()
were removed for 16, since the same functionality is now provided
through a more intuitive UI: we now tolerate invalid end_lsn values
"from the future", per a new "Tip" in the pg_walinspect documentation
for 16.

In my opinion this should (at most) be covered as a compatibility
item. It's not really new functionality.

* There is one truly new pg_walinspect function added to 16:
pg_get_wal_block_info(). Its main purpose is to see how each
individual block changed -- it's far easier to track how blocks
changed over time using the new function. The original "record
orientated" functions made that very difficult (regex hacks were
required).

pg_get_wal_block_info first appeared under another name, and had
somewhat narrower functionality to the final version, all of which
shouldn't matter to users -- since they never saw a stable release
with any of that. There is no point in telling users about the commits
that changed the name/functionality of pg_get_wal_block_info that came
only a couple of months after the earliest version was commited -- to
users, it is simply a new function with new functionality.

I also suggest merging the pg_waldump items with the section you've
added covering pg_walinspect. A "pg_walinspect and pg_waldump" section
seems natural to me. Some of the enhancements in this area benefit
pg_walinspect and pg_waldump in exactly the same way, since
pg_walinspect is essentially a backend/SQL interface equivalent of
pg_waldump's frontend/CLI interface. Melanie's work on improving the
descriptions output for WAL records like Heap's PRUNE and VACUUM
records is a great example of this -- it does exactly the same thing
for pg_walinspect and pg_waldump, without directly targeting either
(it also affects the wal_debug developer option).

It might also make sense to say that the enhanced WAL record
descriptions from Melanie generally apply to records used by VACUUM
only.

Note also that the item "Add pg_waldump option --save-fullpage to dump
full page images (David Christensen)" is tangentially related to
pg_get_wal_block_info(), since you can also get FPIs using
pg_get_wal_block_info() (in fact, that was originally its main
purpose). I'm not saying that you necessarily need to connect them
together in any way, but you might consider it.

--
Peter Geoghegan

On Thu, May 18, 2023 at 02:53:25PM -0700, Peter Geoghegan wrote:

On Thu, May 18, 2023 at 1:49 PM Bruce Momjian <bruce@momjian.us> wrote:

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

I learned a few things creating it this time:

* I can get confused over C function names and SQL function names in
commit messages.

The commit history covering pg_walinspect was complicated. Some of the
newly added stuff was revised multiple times, by multiple authors due
to changing ideas about the best UI. Here is some concrete feedback
about that:

* Two functions that were in 15 that each end in *_till_end_of_wal()
were removed for 16, since the same functionality is now provided
through a more intuitive UI: we now tolerate invalid end_lsn values
"from the future", per a new "Tip" in the pg_walinspect documentation
for 16.

In my opinion this should (at most) be covered as a compatibility
item. It's not really new functionality.

So, I looked at this and the problem is that this is best as a single
release note entry because we are removing and adding, and if I moved it
to compatibility, I am concerned the new feature will be missed. Since
WAL inspection is a utility operation, inn general, I think having it in
the pg_walinspect section makes the most sense.

* There is one truly new pg_walinspect function added to 16:
pg_get_wal_block_info(). Its main purpose is to see how each
individual block changed -- it's far easier to track how blocks
changed over time using the new function. The original "record
orientated" functions made that very difficult (regex hacks were
required).

pg_get_wal_block_info first appeared under another name, and had
somewhat narrower functionality to the final version, all of which
shouldn't matter to users -- since they never saw a stable release
with any of that. There is no point in telling users about the commits
that changed the name/functionality of pg_get_wal_block_info that came
only a couple of months after the earliest version was commited -- to
users, it is simply a new function with new functionality.

Right.

I also suggest merging the pg_waldump items with the section you've
added covering pg_walinspect. A "pg_walinspect and pg_waldump" section
seems natural to me. Some of the enhancements in this area benefit
pg_walinspect and pg_waldump in exactly the same way, since
pg_walinspect is essentially a backend/SQL interface equivalent of
pg_waldump's frontend/CLI interface. Melanie's work on improving the
descriptions output for WAL records like Heap's PRUNE and VACUUM
records is a great example of this -- it does exactly the same thing
for pg_walinspect and pg_waldump, without directly targeting either
(it also affects the wal_debug developer option).

Well, pg_waldump is an installed binary while pg_walinspect is an
extension, so I am not sure where I would put a merged section.

It might also make sense to say that the enhanced WAL record
descriptions from Melanie generally apply to records used by VACUUM
only.

Okay, I went with:

Improve descriptions of pg_walinspect WAL record descriptions
(Melanie Plageman, Peter Geoghegan)

Note also that the item "Add pg_waldump option --save-fullpage to dump
full page images (David Christensen)" is tangentially related to
pg_get_wal_block_info(), since you can also get FPIs using
pg_get_wal_block_info() (in fact, that was originally its main
purpose). I'm not saying that you necessarily need to connect them
together in any way, but you might consider it.

Well, there is so much _new_ in that tool that listing everything new
seems confusing.

FYI, I have just added an item about more aggressing freezing:

<!--
Author: Peter Geoghegan <pg@bowt.ie>
2022-09-08 [d977ffd92] Instrument freezing in autovacuum log reports.
Author: Peter Geoghegan <pg@bowt.ie>
2022-11-15 [9e5405993] Deduplicate freeze plans in freeze WAL records.
Author: Peter Geoghegan <pg@bowt.ie>
2022-12-28 [1de58df4f] Add page-level freezing to VACUUM.
-->

<listitem>
<para>
During non-freeze operations, perform page freezing where appropriate
Peter Geoghegan)
</para>

<para>
This makes full-table freeze vacuums less necessary.
</para>
</listitem>

All changes committed.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Thu, May 18, 2023 at 3:52 PM Bruce Momjian <bruce@momjian.us> wrote:

So, I looked at this and the problem is that this is best as a single
release note entry because we are removing and adding, and if I moved it
to compatibility, I am concerned the new feature will be missed. Since
WAL inspection is a utility operation, inn general, I think having it in
the pg_walinspect section makes the most sense.

I don't understand what you mean by that. The changes to
*_till_end_of_wal() (the way that those duplicative functions were
removed, and more permissive end_lsn behavior was added) is unrelated
to all of the other changes. Plus it's just not very important.

Okay, I went with:

Improve descriptions of pg_walinspect WAL record descriptions
(Melanie Plageman, Peter Geoghegan)

Note also that the item "Add pg_waldump option --save-fullpage to dump
full page images (David Christensen)" is tangentially related to
pg_get_wal_block_info(), since you can also get FPIs using
pg_get_wal_block_info() (in fact, that was originally its main
purpose). I'm not saying that you necessarily need to connect them
together in any way, but you might consider it.

Well, there is so much _new_ in that tool that listing everything new
seems confusing.

There is pretty much one truly new piece of functionality added to
pg_walinspect (the function called pg_get_wal_block_info was added) --
since the enhancement to rmgr description output applies equally to
pg_waldump, no matter where you place it in the release notes. So not
sure what you mean.

All changes committed.

Even after these changes, the release notes still refer to a function
called "pg_get_wal_block". There is no such function, though -- not in
Postgres 16, and not in any other major version.

As I said, there is a new function called "pg_get_wal_block_info". It
should simply be presented as a whole new function that offers novel
new functionality compared to what was available in Postgres 15 --
without any further elaboration. (It happens to be true that
pg_get_wal_block_info only reached its final form following multiple
rounds of work in multiple commits, but that is of no consequence to
users -- even the earliest form of the function appeared in a commit
in the Postgres 16 cycle.)

--
Peter Geoghegan

On Thu, 18 May 2023 at 22:49, Bruce Momjian <bruce@momjian.us> wrote:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

I'm not sure if bugfixes like these are considered for release notes,
but I'm putting it up here just in case:

As of 8fcb32db (new, only in PG16) we now enforce limits on the size
of WAL records during construction, where previously we hoped that the
WAL records didn't exceed those limits.
This change is immediately user-visible through a change in behaviour
of `pg_logical_emit_message(true, repeat('_', 2^30 - 10), repeat('_',
2^30 - 10))`, and extensions that implement their own rmgrs could also
see a change in behavior from this change.

Kind regards,

Matthias van de Meent
Neon, Inc.

On Thu, May 18, 2023 at 04:12:26PM -0700, Peter Geoghegan wrote:

On Thu, May 18, 2023 at 3:52 PM Bruce Momjian <bruce@momjian.us> wrote:

So, I looked at this and the problem is that this is best as a single
release note entry because we are removing and adding, and if I moved it
to compatibility, I am concerned the new feature will be missed. Since
WAL inspection is a utility operation, inn general, I think having it in
the pg_walinspect section makes the most sense.

I don't understand what you mean by that. The changes to
*_till_end_of_wal() (the way that those duplicative functions were
removed, and more permissive end_lsn behavior was added) is unrelated
to all of the other changes. Plus it's just not very important.

I see what you mean now. I have moved the function removal to the
incompatibilities section and kept the existing entry but remove the
text about the removed functions.

Okay, I went with:

Improve descriptions of pg_walinspect WAL record descriptions
(Melanie Plageman, Peter Geoghegan)

Note also that the item "Add pg_waldump option --save-fullpage to dump
full page images (David Christensen)" is tangentially related to
pg_get_wal_block_info(), since you can also get FPIs using
pg_get_wal_block_info() (in fact, that was originally its main
purpose). I'm not saying that you necessarily need to connect them
together in any way, but you might consider it.

Well, there is so much _new_ in that tool that listing everything new
seems confusing.

There is pretty much one truly new piece of functionality added to
pg_walinspect (the function called pg_get_wal_block_info was added) --
since the enhancement to rmgr description output applies equally to
pg_waldump, no matter where you place it in the release notes. So not
sure what you mean.

I see what you mean now. I have removed the mention of
pg_get_wal_block_info() and moved the three items back into the
extension section since there are only three pg_walinspect items now.

All changes committed.

Even after these changes, the release notes still refer to a function
called "pg_get_wal_block". There is no such function, though -- not in
Postgres 16, and not in any other major version.

Oh

As I said, there is a new function called "pg_get_wal_block_info". It
should simply be presented as a whole new function that offers novel
new functionality compared to what was available in Postgres 15 --
without any further elaboration. (It happens to be true that
pg_get_wal_block_info only reached its final form following multiple
rounds of work in multiple commits, but that is of no consequence to
users -- even the earliest form of the function appeared in a commit
in the Postgres 16 cycle.)

Done. Please see the URL for the updated text, diff attached.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Fri, May 19, 2023 at 01:33:17AM +0200, Matthias van de Meent wrote:

On Thu, 18 May 2023 at 22:49, Bruce Momjian <bruce@momjian.us> wrote:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

I'm not sure if bugfixes like these are considered for release notes,
but I'm putting it up here just in case:

As of 8fcb32db (new, only in PG16) we now enforce limits on the size
of WAL records during construction, where previously we hoped that the
WAL records didn't exceed those limits.
This change is immediately user-visible through a change in behaviour
of `pg_logical_emit_message(true, repeat('_', 2^30 - 10), repeat('_',
2^30 - 10))`, and extensions that implement their own rmgrs could also
see a change in behavior from this change.

I saw that commit but I considered it sufficiently rare and sufficiently
internal that I did not include it.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Thu, May 18, 2023 at 6:44 PM Bruce Momjian <bruce@momjian.us> wrote:

I don't understand what you mean by that. The changes to
*_till_end_of_wal() (the way that those duplicative functions were
removed, and more permissive end_lsn behavior was added) is unrelated
to all of the other changes. Plus it's just not very important.

I see what you mean now. I have moved the function removal to the
incompatibilities section and kept the existing entry but remove the
text about the removed functions.

Your patch now has two separate items for "[5c1b66280] Rework design
of functions in pg_walinspect", but even one item is arguably one too
many. The "ending LSN" item (the second item for this same commit)
should probably be removed altogether. If you're going to keep the
sentences that appear under that second item, then it should at least
be consolidated with the first item, in order that commit 5c1b66280
isn't listed twice.

Note also that the patch doesn't remove a remaining reference to an
update in how pg_get_wal_block_info() works, which (as I've said) is a
brand new function as far as users are concerned. Users don't need to
hear that it has been updated, since these release notes will also be
the first time they've been presented with any information about
pg_get_wal_block_info(). (This isn't very important; again, I suggest
that you avoid saying anything about any specific function, even if
you feel strongly that the "ending LSN" issue must be spelled out like
this.)

There is pretty much one truly new piece of functionality added to
pg_walinspect (the function called pg_get_wal_block_info was added) --
since the enhancement to rmgr description output applies equally to
pg_waldump, no matter where you place it in the release notes. So not
sure what you mean.

I see what you mean now. I have removed the mention of
pg_get_wal_block_info() and moved the three items back into the
extension section since there are only three pg_walinspect items now.

The wording for this item as it appears in the patch is: "Improve
descriptions of pg_walinspect WAL record descriptions". I suggest the
following wording be used instead: "Provide more detailed descriptions
of certain WAL records in the output of pg_walinspect and pg_waldump".

--
Peter Geoghegan

On Fri, May 19, 2023 at 4:49 AM Bruce Momjian <bruce@momjian.us> wrote:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

I learned a few things creating it this time:

* I can get confused over C function names and SQL function names in
commit messages.

* The sections and ordering of the entries can greatly clarify the
items.

* The feature count is slightly higher than recent releases:

release-10: 189
release-11: 170
release-12: 180
release-13: 178
release-14: 220
release-15: 184
--> release-16: 200

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

* When granting role membership, require the granted-by role to be a role

that has appropriate permissions (Robert Haas)
This is a requirement even when the superuser is granting role membership.

an exception would be the granted-by is the bootstrap superuser.

On Thu, May 18, 2023 at 07:15:26PM -0700, Peter Geoghegan wrote:

On Thu, May 18, 2023 at 6:44 PM Bruce Momjian <bruce@momjian.us> wrote:

I don't understand what you mean by that. The changes to
*_till_end_of_wal() (the way that those duplicative functions were
removed, and more permissive end_lsn behavior was added) is unrelated
to all of the other changes. Plus it's just not very important.

I see what you mean now. I have moved the function removal to the
incompatibilities section and kept the existing entry but remove the
text about the removed functions.

Your patch now has two separate items for "[5c1b66280] Rework design
of functions in pg_walinspect", but even one item is arguably one too
many. The "ending LSN" item (the second item for this same commit)

I see your point. pg_get_wal_block_info() doesn't exist in pre-PG 16,
so I have removed that text from the release note item, but kept the
other two functions.

should probably be removed altogether. If you're going to keep the
sentences that appear under that second item, then it should at least
be consolidated with the first item, in order that commit 5c1b66280
isn't listed twice.

We can list items twice if they have different focuses.

Note also that the patch doesn't remove a remaining reference to an
update in how pg_get_wal_block_info() works, which (as I've said) is a
brand new function as far as users are concerned. Users don't need to
hear that it has been updated, since these release notes will also be
the first time they've been presented with any information about
pg_get_wal_block_info(). (This isn't very important; again, I suggest
that you avoid saying anything about any specific function, even if
you feel strongly that the "ending LSN" issue must be spelled out like
this.)

Agreed.

There is pretty much one truly new piece of functionality added to
pg_walinspect (the function called pg_get_wal_block_info was added) --
since the enhancement to rmgr description output applies equally to
pg_waldump, no matter where you place it in the release notes. So not
sure what you mean.

I see what you mean now. I have removed the mention of
pg_get_wal_block_info() and moved the three items back into the
extension section since there are only three pg_walinspect items now.

The wording for this item as it appears in the patch is: "Improve
descriptions of pg_walinspect WAL record descriptions". I suggest the
following wording be used instead: "Provide more detailed descriptions
of certain WAL records in the output of pg_walinspect and pg_waldump".

I went with, "Add detailed descriptions of WAL records in pg_walinspect
and pg_waldump (Melanie Plageman, Peter Geoghegan)".

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Fri, May 19, 2023 at 10:41:59AM +0800, jian he wrote:

On Fri, May 19, 2023 at 4:49 AM Bruce Momjian <bruce@momjian.us> wrote:
* When granting role membership, require the granted-by role to be a role
that has appropriate permissions (Robert Haas)
This is a requirement even when the superuser is granting role membership.

an exception would be the granted-by is the bootstrap superuser.

Okay, updated text is:

<listitem>
<para>
When granting role membership, require the granted-by role to be a role that has appropriate permissions (Robert Haas)
</para>

<para>
This is a requirement even when a non-bootstrap superuser is granting role membership.
</para>
</listitem>

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

Hi,

On 5/18/23 10:49 PM, Bruce Momjian wrote:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

Thanks!

"
This adds the function pg_log_standby_snapshot(). TEXT?:
"

My proposal:

This adds the function pg_log_standby_snapshot() to log details of the current snapshot
to WAL. If the primary is idle, the slot creation on a standby can take a while.
This function can be used on the primary to speed up the logical slot creation on
the standby.

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

On Fri, May 19, 2023 at 09:49:18AM +0200, Drouvot, Bertrand wrote:

Thanks!

"
This adds the function pg_log_standby_snapshot(). TEXT?:
"

My proposal:

This adds the function pg_log_standby_snapshot() to log details of the current snapshot
to WAL. If the primary is idle, the slot creation on a standby can take a while.
This function can be used on the primary to speed up the logical slot creation on
the standby.

Yes, I got this concept from the commit message, but I am unclear on
what is actually happening so I can clearly explain it. Slot creation
on the standby needs a snapshot, and that is only created when there is
activity, or happens periodically, and this forces it to happen, or
something? And what snapshot is this? The current session's?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Thu, May 18, 2023 at 04:49:47PM -0400, Bruce Momjian wrote:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

Thanks!

Allow GRANT to give vacuum and analyze permission to users beyond the
table owner or superusers (Nathan Bossart)

This one was effectively reverted in favor of the MAINTAIN privilege.

Create a predefined role with permission to perform maintenance
operations (Nathan Bossart)

IMO this should also mention the grantable MAINTAIN privilege.
Alternatively, the item above about granting vacuum/analyze privileges
could be adjusted.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

The intro in "E.1.3. Changes" says "... between PostgreSQL 15 and the
previous major release".

That should be "... between PostgreSQL __16__ ..." right?

Regards,
-- Sehrope Sarkuni
Founder & CEO | JackDB, Inc. | https://www.jackdb.com/

On Fri, May 19, 2023 at 07:31:40AM -0700, Nathan Bossart wrote:

On Thu, May 18, 2023 at 04:49:47PM -0400, Bruce Momjian wrote:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

Thanks!

Allow GRANT to give vacuum and analyze permission to users beyond the
table owner or superusers (Nathan Bossart)

This one was effectively reverted in favor of the MAINTAIN privilege.

Okay, removed.

Create a predefined role with permission to perform maintenance
operations (Nathan Bossart)

IMO this should also mention the grantable MAINTAIN privilege.
Alternatively, the item above about granting vacuum/analyze privileges
could be adjusted.

Very good point --- here is the new text:

<!--
Author: Jeff Davis <jdavis@postgresql.org>
2022-12-13 [60684dd83] Add grantable MAINTAIN privilege and pg_maintain role.
Author: Andrew Dunstan <andrew@dunslane.net>
2022-11-28 [4441fc704] Provide non-superuser predefined roles for vacuum and an
Author: Jeff Davis <jdavis@postgresql.org>
2023-01-14 [ff9618e82] Fix MAINTAIN privileges for toast tables and partitions.
-->

<listitem>
<para>
Create a predefined role and grantable privilege with permission to perform maintenance operations (Nathan Bossart)
</para>

<para>
The predefined role is is called pg_maintain.
</para>
</listitem>

I will commit this change now.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Fri, May 19, 2023 at 11:07:29AM -0400, Sehrope Sarkuni wrote:

The intro in "E.1.3. Changes" says "... between PostgreSQL 15 and the previous
major release".

That should be "... between PostgreSQL __16__ ..." right?

Oh, I thought I had changed all those --- fixed now, thanks!

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Fri, May 19, 2023 at 4:49 AM Bruce Momjian <bruce@momjian.us> wrote:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

I learned a few things creating it this time:

* I can get confused over C function names and SQL function names in
commit messages.

* The sections and ordering of the entries can greatly clarify the
items.

* The feature count is slightly higher than recent releases:

release-10: 189
release-11: 170
release-12: 180
release-13: 178
release-14: 220
release-15: 184
--> release-16: 200

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

Add function pg_dissect_walfile_name() to report the segment and timeline

values of WAL file names (Bharath Rupireddy)

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=13e0d7a603852b8b05c03b45228daabffa0cced2
the function rename to pg_split_walfile_name.

seems didn't mention pg_input_is_valid,pg_input_error_info?
https://www.postgresql.org/docs/devel/functions-info.html#FUNCTIONS-INFO-VALIDITY-TABLE

The v16 release notes are problematic in a PDF docs build:

[WARN] FOUserAgent - Glyph "?" (0x142, lslash) not available in font "Times-Roman".

This is evidently from

Add functions to add, subtract, and generate timestamptz values in a specified time zone (Przemysław Sztoch, Gurjeet Singh)

Change date_trunc(unit, timestamptz, time_zone) to be an immutable function (Przemysław Sztoch)

since "ł" doesn't exist in ISO8859-1. I'd suggest dumbing these
down to plain "l".

regards, tom lane

Sorry for changing the subject line.....

these two commits seems not mentioned.
Fix ts_headline() edge cases for empty query and empty search text.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=029dea882a7aa34f46732473eed7c917505e6481

Simplify the implementations of the to_reg* functions.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3ea7329c9a79ade27b5d3742d1a41ce6d0d9aca8

On Fri, 19 May 2023 at 22:59, jian he <jian.universality@gmail.com> wrote:

Sorry for changing the subject line.....

these two commits seems not mentioned.

On a similar topic, should every committed item from the commitfest be
mentioned, or only ones that are significant enough?

I’m wondering because I had a role in this very small item, yet I don’t see
it listed in the psql section:

https://commitfest.postgresql.org/42/4133/

It’s OK if we don’t mention every single change, I just want to make sure I
understand.

Hi,

On 5/19/23 2:29 PM, Bruce Momjian wrote:

On Fri, May 19, 2023 at 09:49:18AM +0200, Drouvot, Bertrand wrote:

Thanks!

"
This adds the function pg_log_standby_snapshot(). TEXT?:
"

My proposal:

This adds the function pg_log_standby_snapshot() to log details of the current snapshot
to WAL. If the primary is idle, the slot creation on a standby can take a while.
This function can be used on the primary to speed up the logical slot creation on
the standby.

Yes, I got this concept from the commit message, but I am unclear on
what is actually happening so I can clearly explain it. Slot creation
on the standby needs a snapshot, and that is only created when there is
activity, or happens periodically, and this forces it to happen, or
something? And what snapshot is this? The current session's?

It's the snapshot of running transactions (aka the xl_running_xacts WAL record) that is used during the
logical slot creation to determine if the logical decoding find a consistent state to start with.

On a primary this WAL record is being emitted during the logical slot creation, but on a standby
we can't write WAL records (so we are waiting for the primary to emit it).

Outside of logical slot creation, this WAL record is also emitted during checkpoint or periodically
by the bgwriter.

What about?

This adds the function pg_log_standby_snapshot() to emit the WAL record that contains the list
of running transactions.

If the primary is idle, the logical slot creation on a standby can take a while (waiting for this WAL record
to be replayed to determine if the logical decoding find a consistent state to start with).

In that case, this new function can be used on the primary to speed up the logical slot
creation on the standby.

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

On Fri, May 19, 2023 at 07:05:12PM -0400, Tom Lane wrote:

The v16 release notes are problematic in a PDF docs build:

[WARN] FOUserAgent - Glyph "?" (0x142, lslash) not available in font "Times-Roman".

This is evidently from

Add functions to add, subtract, and generate timestamptz values in a specified time zone (Przemysław Sztoch, Gurjeet Singh)

Change date_trunc(unit, timestamptz, time_zone) to be an immutable function (Przemysław Sztoch)

since "ł" doesn't exist in ISO8859-1. I'd suggest dumbing these
down to plain "l".

Done. I know we used to be limited to Latin-1 but when my build of HTML
worked, I thought that had changed. :-(

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

Bruce Momjian <bruce@momjian.us> writes:

On Fri, May 19, 2023 at 07:05:12PM -0400, Tom Lane wrote:

... "ł" doesn't exist in ISO8859-1. I'd suggest dumbing these
down to plain "l".

Done. I know we used to be limited to Latin-1 but when my build of HTML
worked, I thought that had changed. :-(

Yeah, I think the HTML toolchain is better than it used to be on
this score. But PDF is still limited.

regards, tom lane

On Sat, May 20, 2023 at 06:07:08PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

On Fri, May 19, 2023 at 07:05:12PM -0400, Tom Lane wrote:

... "ł" doesn't exist in ISO8859-1. I'd suggest dumbing these
down to plain "l".

Done. I know we used to be limited to Latin-1 but when my build of HTML
worked, I thought that had changed. :-(

Yeah, I think the HTML toolchain is better than it used to be on
this score. But PDF is still limited.

Ah, makes sense. I will need to test the PDF build next time.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Sat, May 20, 2023 at 12:59:35AM +0800, jian he wrote:

Add function pg_dissect_walfile_name() to report the segment and timeline
values of WAL file names (Bharath Rupireddy)

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=
13e0d7a603852b8b05c03b45228daabffa0cced2
the function rename to pg_split_walfile_name.

Fixed. I copied the commit that did the rename, but forgot to actually
update the release note text to match.

seems didn't mention pg_input_is_valid,pg_input_error_info? 
https://www.postgresql.org/docs/devel/functions-info.html#
FUNCTIONS-INFO-VALIDITY-TABLE

Good point. I incorrectly interpreted the commit text as part of our
test infrastuture and not the addition of two SQL functions:

Add test scaffolding for soft error reporting from input functions.

pg_input_is_valid() returns boolean, while pg_input_error_message()
returns the primary error message if the input is bad, or NULL
if the input is OK. The main reason for having two functions is
so that we can test both the details-wanted and the no-details-wanted
code paths.

I have added this release note item:

<!--
Author: Tom Lane <tgl@sss.pgh.pa.us>
2022-12-09 [1939d2628] Add test scaffolding for soft error reporting from input
-->

<listitem>
<para>
Add functions pg_input_is_valid() and pg_input_error_message() to check for type conversion errors (Tom Lane)
</para>
</listitem>

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Sat, May 20, 2023 at 10:59:20AM +0800, jian he wrote:

Sorry for changing the subject line..... 

these two commits seems not mentioned.
Fix ts_headline() edge cases for empty query and empty search text.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=
029dea882a7aa34f46732473eed7c917505e6481

I usually don't cover bug fixes for rare cases that used to generate
errors. However, the bigger issue is that this commit did not appear in
my output of git_changelog because it was backpatched, as indicated in
the commit text.

Simplify the implementations of the to_reg* functions.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=

The commit for this is:

Author: Tom Lane <tgl@sss.pgh.pa.us>
2022-12-27 [3ea7329c9] Simplify the implementations of the to_reg*
functions.

Simplify the implementations of the to_reg* functions.

Given the soft-input-error feature, we can reduce these functions
to be just thin wrappers around a soft-error call of the
corresponding datatype input function. This means less code and
more certainty that the to_reg* functions match the normal input
behavior.

--> Notably, it also means that they will accept numeric OID input,
--> which they didn't before. It's not clear to me if that omission
had more than laziness behind it, but it doesn't seem like
something we need to work hard to preserve.

Discussion: /messages/by-id/3910031.1672095600@sss.pgh.pa.us

The change is that to_reg* functions can now accept OIDs, which I didn't
notice when I read the commit message. I have added this release note
item:

<!--
Author: Tom Lane <tgl@sss.pgh.pa.us>
2022-12-27 [3ea7329c9] Simplify the implementations of the to_reg* functions.
-->

<listitem>
<para>
Allow to_reg* functions to accept OIDs parameters (Tom Lane)
</para>
</listitem>

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Fri, May 19, 2023 at 11:08:18PM -0400, Isaac Morland wrote:

On Fri, 19 May 2023 at 22:59, jian he <jian.universality@gmail.com> wrote:

Sorry for changing the subject line..... 

these two commits seems not mentioned.

On a similar topic, should every committed item from the commitfest be
mentioned, or only ones that are significant enough?

I’m wondering because I had a role in this very small item, yet I don’t see it
listed in the psql section:

https://commitfest.postgresql.org/42/4133/

It’s OK if we don’t mention every single change, I just want to make sure I
understand.

I have never considered the presence of an item in the commitfest as an
indicator of importance to be in the release notes. The major release
notes, for me, is a balance of listing the most visible changes without
going into unmanageable detail.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

Bruce Momjian <bruce@momjian.us> writes:

I have added this release note item:

Add functions pg_input_is_valid() and pg_input_error_message() to check for type conversion errors (Tom Lane)

pg_input_error_message got renamed to pg_input_error_info later,
cf b8da37b3a (which maybe should be included in the comment
for this entry).

regards, tom lane

On Sat, May 20, 2023 at 10:37:58AM +0200, Drouvot, Bertrand wrote:

It's the snapshot of running transactions (aka the xl_running_xacts WAL record) that is used during the
logical slot creation to determine if the logical decoding find a consistent state to start with.

On a primary this WAL record is being emitted during the logical slot creation, but on a standby
we can't write WAL records (so we are waiting for the primary to emit it).

Outside of logical slot creation, this WAL record is also emitted during checkpoint or periodically
by the bgwriter.

What about?

This adds the function pg_log_standby_snapshot() to emit the WAL record that contains the list
of running transactions.

If the primary is idle, the logical slot creation on a standby can take a while (waiting for this WAL record
to be replayed to determine if the logical decoding find a consistent state to start with).

In that case, this new function can be used on the primary to speed up the logical slot
creation on the standby.

Okay, this helps. I split the entry into two with this text:

<!--
Author: Andres Freund <andres@anarazel.de>
2023-04-08 [0fdab27ad] Allow logical decoding on standbys
-->

<listitem>
<para>
Allow logical decoding on standbys (Bertrand Drouvot, Andres Freund, Amit Khandekar)
</para>
</listitem>

<!--
Author: Andres Freund <andres@anarazel.de>
2023-04-08 [0fdab27ad] Allow logical decoding on standbys
-->

<listitem>
<para>
Add function pg_log_standby_snapshot() to force creation of a WAL snapshot (Bertrand Drouvot)
</para>

<para>
WAL snapshots are required for logical slot creation so this function speeds their creation on standbys.
</para>
</listitem>

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Sat, May 20, 2023 at 08:51:21PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

I have added this release note item:

Add functions pg_input_is_valid() and pg_input_error_message() to check for type conversion errors (Tom Lane)

pg_input_error_message got renamed to pg_input_error_info later,
cf b8da37b3a (which maybe should be included in the comment
for this entry).

Oh, I skipped the original entry so I skipped that one too. I have
adjusted the release note item and added the commit to the comment:

<!--
Author: Tom Lane <tgl@sss.pgh.pa.us>
2022-12-09 [1939d2628] Add test scaffolding for soft error reporting from input
Author: Michael Paquier <michael@paquier.xyz>
2023-02-28 [b8da37b3a] Rework pg_input_error_message(), now renamed pg_input_er
-->

<listitem>
<para>
--> Add functions pg_input_is_valid() and pg_input_error_info() to check for type conversion errors (Tom Lane)
</para>
</listitem>

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

2023年5月19日(金) 5:49 Bruce Momjian <bruce@momjian.us>:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

Hi

Below are a few commits which are not referenced in the current iteration
of the release notes, but which seem worthy of inclusion.
Apologies if they have been previously discussed, or I'm overlooking something
obvious.

d09dbeb9b Speedup hash index builds by skipping needless binary searches
"Testing has shown that this can improve hash index build speeds by 5-15%
with a unique set of integer values."

e09d7a126 Improve speed of hash index build.
"This seems to be good for overall
speedup of 5%-9%, depending on the incoming data."

594f8d377 Allow batching of inserts during cross-partition updates.
seems reasonable to mention this as it's related to 97da48246, which
is mentioned in the notes

1349d2790 Improve performance of ORDER BY / DISTINCT aggregates
This is the basis for da5800d5, which is mentioned in the notes, but AFAICS
the latter is an implementation fix for the former (haven't looked
into either
in detail though).

The following are probably not headline features, but are the kind of
behavioural
changes I'd expect to find in the release notes (when, at some point
in the far and
distant future, trying to work out when they were introduced when considering
application compatibility etc.):

13a185f54 Allow publications with schema and table of the same schema.
2ceea5adb Accept "+infinity" in date and timestamp[tz] input.
d540a02a7 Display the leader apply worker's PID for parallel apply workers.

Regards

Ian Barwick

On Sun, May 21, 2023 at 09:30:01PM +0900, Ian Lawrence Barwick wrote:

2023年5月19日(金) 5:49 Bruce Momjian <bruce@momjian.us>:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

Hi

Below are a few commits which are not referenced in the current iteration
of the release notes, but which seem worthy of inclusion.
Apologies if they have been previously discussed, or I'm overlooking something
obvious.

d09dbeb9b Speedup hash index builds by skipping needless binary searches
"Testing has shown that this can improve hash index build speeds by 5-15%
with a unique set of integer values."

e09d7a126 Improve speed of hash index build.
"This seems to be good for overall
speedup of 5%-9%, depending on the incoming data."

For the above two items, I mention items that would change user behavior
like new features or changes that are significant enough that they would
change user behavior. For example, if a new join method increases
performance by 5x, that could change user behavior. Based on the quoted
numbers above, I didn't think "hash now faster" would be appropriate to
mention. Right?

594f8d377 Allow batching of inserts during cross-partition updates.
seems reasonable to mention this as it's related to 97da48246, which
is mentioned in the notes

I wasn't sure if that was significant, based on the above logic, but
97da48246 has a user API to control it so I mentioned that one.

1349d2790 Improve performance of ORDER BY / DISTINCT aggregates
This is the basis for da5800d5, which is mentioned in the notes, but AFAICS
the latter is an implementation fix for the former (haven't looked
into either
in detail though).

I have added this commit to the existing entry, thanks.

The following are probably not headline features, but are the kind of
behavioural
changes I'd expect to find in the release notes (when, at some point
in the far and
distant future, trying to work out when they were introduced when considering
application compatibility etc.):

13a185f54 Allow publications with schema and table of the same schema.

This seemed like a rare enough case that I did not add it.

2ceea5adb Accept "+infinity" in date and timestamp[tz] input.

I have this but didn't add that commit, added.

d540a02a7 Display the leader apply worker's PID for parallel apply workers.

Parallelism of apply is a new feature and I don't normally mention
output _additions_ that are related to new features.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

Bruce Momjian <bruce@momjian.us> writes:

On Sun, May 21, 2023 at 09:30:01PM +0900, Ian Lawrence Barwick wrote:

2ceea5adb Accept "+infinity" in date and timestamp[tz] input.

I have this but didn't add that commit, added.

That's really not related to the commit you added it to...

I don't have time today to read through all the relnotes, but I went
through those that have my name on them. Suggested wording modifications
attached.

regards, tom lane

Hi,

Thanks for the release notes!

<!--
Author: Andres Freund <andres@anarazel.de>
2023-04-06 [00d1e02be] hio: Use ExtendBufferedRelBy() to extend tables more eff
Author: Andres Freund <andres@anarazel.de>
2023-04-06 [26158b852] Use ExtendBufferedRelTo() in XLogReadBufferExtended()
-->

<listitem>
<para>
Allow more efficient addition of multiple heap and index pages (Andres Freund)
</para>
</listitem>

While the case of extending by multiple pages improved the most, even
extending by a single page at a time got a good bit more scalable. Maybe just
"Improve efficiency of extending relations"?

I think:

<!--
Author: Andres Freund <andres@anarazel.de>
2023-04-08 [0fdab27ad] Allow logical decoding on standbys
-->

<listitem>
<para>
Allow logical decoding on standbys (Bertrand Drouvot, Andres Freund, Amit Khandekar)
</para>
</listitem>

pretty much includes:

<!--
Author: Andres Freund <andres@anarazel.de>
2023-04-07 [be87200ef] Support invalidating replication slots due to horizon an
Author: Andres Freund <andres@anarazel.de>
2023-04-08 [26669757b] Handle logical slot conflicts on standby
-->

<listitem>
<para>
Allow invalidation of replication slots due to row removal, wal_level, and conflicts (Bertrand Drouvot, Andres Freund, Amit Khandekar)
</para>

as it is a prerequisite.

I'd probably also merge

<!--
Author: Andres Freund <andres@anarazel.de>
2023-04-08 [0fdab27ad] Allow logical decoding on standbys
-->

<listitem>
<para>
Add function pg_log_standby_snapshot() to force creation of a WAL snapshot (Bertrand Drouvot)
</para>

<para>
WAL snapshots are required for logical slot creation so this function speeds their creation on standbys.
</para>
</listitem>

As there really isn't a use case outside of logical decoding on a standby.

<!--
Author: Andres Freund <andres@anarazel.de>
2022-07-17 [089480c07] Default to hidden visibility for extension libraries whe
Author: Andres Freund <andres@anarazel.de>
2022-07-17 [8cf64d35e] Mark all symbols exported from extension libraries PGDLL
-->

<listitem>
<para>
Prevent extension libraries from export their symbols by default (Andres Freund, Tom Lane)
</para>
</listitem>

s/export/exporting/?

Looking through the release notes, I didn't see an entry for

commit c6e0fe1f2a08505544c410f613839664eea9eb21
Author: David Rowley <drowley@postgresql.org>
Date: 2022-08-29 17:15:00 +1200

Improve performance of and reduce overheads of memory management

even though I think that's one of the more impactful improvements. What was
the reason for leaving that out?

Greetings,

Andres Freund

On 5/21/23 1:13 PM, Andres Freund wrote:

Looking through the release notes, I didn't see an entry for

commit c6e0fe1f2a08505544c410f613839664eea9eb21
Author: David Rowley <drowley@postgresql.org>
Date: 2022-08-29 17:15:00 +1200

Improve performance of and reduce overheads of memory management

even though I think that's one of the more impactful improvements. What was
the reason for leaving that out?

IIUC in[1]/messages/by-id/CAApHDvpjauCRXcgcaL6+e3eqecEHoeRm9D-kcbuvBitgPnW=vw@mail.gmail.com, would this "just speed up" read-heavy workloads?

Thanks,

Jonathan

[1]: /messages/by-id/CAApHDvpjauCRXcgcaL6+e3eqecEHoeRm9D-kcbuvBitgPnW=vw@mail.gmail.com
/messages/by-id/CAApHDvpjauCRXcgcaL6+e3eqecEHoeRm9D-kcbuvBitgPnW=vw@mail.gmail.com

On 5/18/23 4:49 PM, Bruce Momjian wrote:

I have completed the first draft of the PG 16 release notes.

One thing that we could attempt for this beta is to include a
prospective list of "major features + enhancements." Of course it can
change before the GA, but it'll give readers some idea of things to test.

I'd propose the following (in no particular order):

* General performance improvements for read-heavy workloads (looking for
clarification for that in[1]/messages/by-id/CAApHDvpjauCRXcgcaL6+e3eqecEHoeRm9D-kcbuvBitgPnW=vw@mail.gmail.com)

* Parallel execution of queries that use `FULL` and `OUTER` joins

* Logical replication allowed from read-only standbys

* Logical replication subscribers can apply large transactions in parallel

* Monitoring of I/O statistics through the `pg_stat_io` view

* Addition of SQL/JSON constructors and identity functions

* Optimizations to the vacuum freezing strategy

* Support for regular expressions for matching usernames and databases
names in `pg_hba.conf`, and user names in `pg_ident.conf`

The above is tossing items at the wall, and I'm OK with any or all being
modified or replaced.

Thanks,

Jonathan

[1]: /messages/by-id/CAApHDvpjauCRXcgcaL6+e3eqecEHoeRm9D-kcbuvBitgPnW=vw@mail.gmail.com
/messages/by-id/CAApHDvpjauCRXcgcaL6+e3eqecEHoeRm9D-kcbuvBitgPnW=vw@mail.gmail.com

Hi,

On May 21, 2023 11:46:56 AM PDT, "Jonathan S. Katz" <jkatz@postgresql.org> wrote:

On 5/21/23 1:13 PM, Andres Freund wrote:

Looking through the release notes, I didn't see an entry for

commit c6e0fe1f2a08505544c410f613839664eea9eb21
Author: David Rowley <drowley@postgresql.org>
Date: 2022-08-29 17:15:00 +1200
Improve performance of and reduce overheads of memory management

even though I think that's one of the more impactful improvements. What was
the reason for leaving that out?

IIUC in[1], would this "just speed up" read-heavy workloads?

I don't think so. It can speed up write workloads as well. But more importantly it can noticeably reduce memory usage, including for things like the relcache.

Andres
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

On Sun, May 21, 2023 at 01:11:05PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

On Sun, May 21, 2023 at 09:30:01PM +0900, Ian Lawrence Barwick wrote:

2ceea5adb Accept "+infinity" in date and timestamp[tz] input.

I have this but didn't add that commit, added.

That's really not related to the commit you added it to...

I don't have time today to read through all the relnotes, but I went
through those that have my name on them. Suggested wording modifications
attached.

These were all good, patch applied, thanks.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On 5/21/23 3:24 PM, Andres Freund wrote:

Hi,

On May 21, 2023 11:46:56 AM PDT, "Jonathan S. Katz" <jkatz@postgresql.org> wrote:

On 5/21/23 1:13 PM, Andres Freund wrote:

Looking through the release notes, I didn't see an entry for

commit c6e0fe1f2a08505544c410f613839664eea9eb21
Author: David Rowley <drowley@postgresql.org>
Date: 2022-08-29 17:15:00 +1200
Improve performance of and reduce overheads of memory management

even though I think that's one of the more impactful improvements. What was
the reason for leaving that out?

IIUC in[1], would this "just speed up" read-heavy workloads?

I don't think so. It can speed up write workloads as well. But more importantly it can noticeably reduce memory usage, including for things like the relcache.

Cool! I'll dive more into the thread later to learn more.

Jonathan

On 5/21/23 3:04 PM, Jonathan S. Katz wrote:

On 5/18/23 4:49 PM, Bruce Momjian wrote:

I have completed the first draft of the PG 16 release notes.

One thing that we could attempt for this beta is to include a
prospective list of "major features + enhancements." Of course it can
change before the GA, but it'll give readers some idea of things to test.

I'd propose the following (in no particular order):

* General performance improvements for read-heavy workloads (looking for
clarification for that in[1])

Per [1]/messages/by-id/5749E807-A5B7-4CC7-8282-84F6F0D4D1D0@anarazel.de this sounds like it should be:

* Optimization to reduce overall memory usage, including general
performance improvements.

We can get more specific for the GA.

Thanks,

Jonathan

[1]: /messages/by-id/5749E807-A5B7-4CC7-8282-84F6F0D4D1D0@anarazel.de
/messages/by-id/5749E807-A5B7-4CC7-8282-84F6F0D4D1D0@anarazel.de

In E.1.2. Migration to Version 16, probably need mention, some
privilege command cannot restore.
if new cluster bootstrap superuser name is not the same as old one. "GRANT
x TO y GRANTED BY no_bootstrap_superuser; " will have error.

---pg15 dump content.
CREATE ROLE jian;
ALTER ROLE jian WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN
REPLICATION BYPASSRLS;
CREATE ROLE regress_priv_user1;
ALTER ROLE regress_priv_user1 WITH NOSUPERUSER INHERIT NOCREATEROLE
NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE regress_priv_user2;
ALTER ROLE regress_priv_user2 WITH NOSUPERUSER INHERIT NOCREATEROLE
NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE su1;
ALTER ROLE su1 WITH SUPERUSER INHERIT CREATEROLE NOCREATEDB LOGIN
NOREPLICATION NOBYPASSRLS;
GRANT regress_priv_user1 TO regress_priv_user2 GRANTED BY su1;

-----------restore in pg16
\i /home/jian/Desktop/dumpall_schema.sql
2023-05-22 08:46:00.170 CST [456584] ERROR: permission denied to grant
privileges as role "su1"
2023-05-22 08:46:00.170 CST [456584] DETAIL: The grantor must have the
ADMIN option on role "regress_priv_user1".
2023-05-22 08:46:00.170 CST [456584] STATEMENT: GRANT regress_priv_user1
TO regress_priv_user2 GRANTED BY su1;
psql:/home/jian/Desktop/dumpall_schema.sql:32: ERROR: permission denied to
grant privileges as role "su1"
DETAIL: The grantor must have the ADMIN option on role
"regress_priv_user1".

On Sun, May 21, 2023 at 10:13:41AM -0700, Andres Freund wrote:

Hi,

Thanks for the release notes!

<!--
Author: Andres Freund <andres@anarazel.de>
2023-04-06 [00d1e02be] hio: Use ExtendBufferedRelBy() to extend tables more eff
Author: Andres Freund <andres@anarazel.de>
2023-04-06 [26158b852] Use ExtendBufferedRelTo() in XLogReadBufferExtended()
-->

<listitem>
<para>
Allow more efficient addition of multiple heap and index pages (Andres Freund)
</para>
</listitem>

While the case of extending by multiple pages improved the most, even
extending by a single page at a time got a good bit more scalable. Maybe just
"Improve efficiency of extending relations"?

Okay, I made this change:

	-Allow more efficient addition of multiple heap and index pages (Andres Freund)
	+Allow more efficient addition of heap and index pages (Andres Freund)

I think:

<!--
Author: Andres Freund <andres@anarazel.de>
2023-04-08 [0fdab27ad] Allow logical decoding on standbys
-->

<listitem>
<para>
Allow logical decoding on standbys (Bertrand Drouvot, Andres Freund, Amit Khandekar)
</para>
</listitem>

pretty much includes:

<!--
Author: Andres Freund <andres@anarazel.de>
2023-04-07 [be87200ef] Support invalidating replication slots due to horizon an
Author: Andres Freund <andres@anarazel.de>
2023-04-08 [26669757b] Handle logical slot conflicts on standby
-->

<listitem>
<para>
Allow invalidation of replication slots due to row removal, wal_level, and conflicts (Bertrand Drouvot, Andres Freund, Amit Khandekar)
</para>

as it is a prerequisite.

Okay, I merged the commit entries and the authors, and removed the item.

I'd probably also merge

<!--
Author: Andres Freund <andres@anarazel.de>
2023-04-08 [0fdab27ad] Allow logical decoding on standbys
-->

<listitem>
<para>
Add function pg_log_standby_snapshot() to force creation of a WAL snapshot (Bertrand Drouvot)
</para>

<para>
WAL snapshots are required for logical slot creation so this function speeds their creation on standbys.
</para>
</listitem>

As there really isn't a use case outside of logical decoding on a standby.

Okay new merged item is:

<!--
Author: Andres Freund <andres@anarazel.de>
2023-04-08 [0fdab27ad] Allow logical decoding on standbys
Author: Andres Freund <andres@anarazel.de>
2023-04-07 [be87200ef] Support invalidating replication slots due to horizon an
Author: Andres Freund <andres@anarazel.de>
2023-04-08 [26669757b] Handle logical slot conflicts on standby
Author: Andres Freund <andres@anarazel.de>
2023-04-08 [0fdab27ad] Allow logical decoding on standbys
-->

<listitem>
<para>
Allow logical decoding on standbys (Bertrand Drouvot, Andres Freund, Amit Khandekar, Bertrand Drouvot)
</para>
</listitem>

<listitem>
<para>
New function pg_log_standby_snapshot() forces creation of WAL snapshots.
Snapshots are required for logical slot creation so this function speeds their creation on standbys.
</para>
</listitem>

<!--
Author: Andres Freund <andres@anarazel.de>
2022-07-17 [089480c07] Default to hidden visibility for extension libraries whe
Author: Andres Freund <andres@anarazel.de>
2022-07-17 [8cf64d35e] Mark all symbols exported from extension libraries PGDLL
-->

<listitem>
<para>
Prevent extension libraries from export their symbols by default (Andres Freund, Tom Lane)
</para>
</listitem>

s/export/exporting/?

Seems Tom's patch already fixed that.

Looking through the release notes, I didn't see an entry for

commit c6e0fe1f2a08505544c410f613839664eea9eb21
Author: David Rowley <drowley@postgresql.org>
Date: 2022-08-29 17:15:00 +1200

Improve performance of and reduce overheads of memory management

even though I think that's one of the more impactful improvements. What was
the reason for leaving that out?

If you read my previous email:

For the above two items, I mention items that would change user
like new features or changes that are significant enough that they would
change user behavior. For example, if a new join method increases
performance by 5x, that could change user behavior. Based on the quoted
numbers above, I didn't think "hash now faster" would be appropriate to
mention. Right?

I can see this item as a big win, but I don't know how to describe it in a way
that is helpful for the user to know.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Mon, May 22, 2023 at 09:03:11AM +0800, jian he wrote:

In E.1.2. Migration to Version 16, probably need mention, some
privilege command cannot restore.
if new cluster bootstrap superuser name is not the same as old one. "GRANT x TO
y GRANTED BY no_bootstrap_superuser; " will have error.

---pg15 dump content.
CREATE ROLE jian;
ALTER ROLE jian WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION
BYPASSRLS;
CREATE ROLE regress_priv_user1;
ALTER ROLE regress_priv_user1 WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB
LOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE regress_priv_user2;
ALTER ROLE regress_priv_user2 WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB
LOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE su1;
ALTER ROLE su1 WITH SUPERUSER INHERIT CREATEROLE NOCREATEDB LOGIN NOREPLICATION
NOBYPASSRLS;
GRANT regress_priv_user1 TO regress_priv_user2 GRANTED BY su1;

-----------restore in pg16
\i /home/jian/Desktop/dumpall_schema.sql
2023-05-22 08:46:00.170 CST [456584] ERROR:  permission denied to grant
privileges as role "su1"
2023-05-22 08:46:00.170 CST [456584] DETAIL:  The grantor must have the ADMIN
option on role "regress_priv_user1".
2023-05-22 08:46:00.170 CST [456584] STATEMENT:  GRANT regress_priv_user1 TO
regress_priv_user2 GRANTED BY su1;
psql:/home/jian/Desktop/dumpall_schema.sql:32: ERROR:  permission denied to
grant privileges as role "su1"
DETAIL:  The grantor must have the ADMIN option on role "regress_priv_user1".

Agreed, new text:

<!--
Author: Robert Haas <rhaas@postgresql.org>
2022-07-26 [e530be2c5] Do not allow removal of superuser privileges from bootst
-->

<listitem>
<para>
Prevent removal of superuser privileges for the bootstrap user (Robert Haas)
</para>

<para>
--> Restoring such users could lead to errors.
</para>
</listitem>

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Sun, May 21, 2023 at 10:13:41AM -0700, Andres Freund wrote:

Hi,

Thanks for the release notes!

<!--
Author: Andres Freund <andres@anarazel.de>
2023-04-06 [00d1e02be] hio: Use ExtendBufferedRelBy() to extend tables more eff
Author: Andres Freund <andres@anarazel.de>
2023-04-06 [26158b852] Use ExtendBufferedRelTo() in XLogReadBufferExtended()
-->

<listitem>
<para>
Allow more efficient addition of multiple heap and index pages (Andres Freund)
</para>
</listitem>

While the case of extending by multiple pages improved the most, even
extending by a single page at a time got a good bit more scalable. Maybe just
"Improve efficiency of extending relations"?

Do average users know heap and index files are both relations? That
seems too abstract so I spelled out heap and index pages.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

Hi,

On 2023-05-21 22:46:58 -0400, Bruce Momjian wrote:

Looking through the release notes, I didn't see an entry for

commit c6e0fe1f2a08505544c410f613839664eea9eb21
Author: David Rowley <drowley@postgresql.org>
Date: 2022-08-29 17:15:00 +1200

Improve performance of and reduce overheads of memory management

even though I think that's one of the more impactful improvements. What was
the reason for leaving that out?

If you read my previous email:

For the above two items, I mention items that would change user
like new features or changes that are significant enough that they would
change user behavior. For example, if a new join method increases
performance by 5x, that could change user behavior. Based on the quoted
numbers above, I didn't think "hash now faster" would be appropriate to
mention. Right?

I continue, as in past releases, to think that this is a bad policy. For
existing workloads performance improvements are commonly a more convincing
reason to upgrade than new features - they allow users to scale the workload
further, without needing application changes.

Of course there are performance improvement that are too miniscule to be worth
mentioning, but it's not a common case.

And here it's not just performance, but also memory usage, including steady
state memory usage.

I can see this item as a big win, but I don't know how to describe it in a way
that is helpful for the user to know.

In doubt the subject of the commit would just work IMO.

Greetings,

Andres Freund

I have added the major features to the release notes with the attached
patch.

---------------------------------------------------------------------------

On Sun, May 21, 2023 at 07:53:38PM -0400, Jonathan Katz wrote:

On 5/21/23 3:04 PM, Jonathan S. Katz wrote:

On 5/18/23 4:49 PM, Bruce Momjian wrote:

I have completed the first draft of the PG 16 release notes.

One thing that we could attempt for this beta is to include a
prospective list of "major features + enhancements." Of course it can
change before the GA, but it'll give readers some idea of things to
test.

I'd propose the following (in no particular order):

* General performance improvements for read-heavy workloads (looking for
clarification for that in[1])

Per [1] this sounds like it should be:

* Optimization to reduce overall memory usage, including general performance
improvements.

We can get more specific for the GA.

Thanks,

Jonathan

[1] /messages/by-id/5749E807-A5B7-4CC7-8282-84F6F0D4D1D0@anarazel.de

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

Hi,

On 2023-05-21 22:52:09 -0400, Bruce Momjian wrote:

On Sun, May 21, 2023 at 10:13:41AM -0700, Andres Freund wrote:

Hi,

Thanks for the release notes!

<!--
Author: Andres Freund <andres@anarazel.de>
2023-04-06 [00d1e02be] hio: Use ExtendBufferedRelBy() to extend tables more eff
Author: Andres Freund <andres@anarazel.de>
2023-04-06 [26158b852] Use ExtendBufferedRelTo() in XLogReadBufferExtended()
-->

<listitem>
<para>
Allow more efficient addition of multiple heap and index pages (Andres Freund)
</para>
</listitem>

While the case of extending by multiple pages improved the most, even
extending by a single page at a time got a good bit more scalable. Maybe just
"Improve efficiency of extending relations"?

Do average users know heap and index files are both relations? That
seems too abstract so I spelled out heap and index pages.

I don't know about average users - but I think users that read the release
notes do know.

I am a bit on the fence about "addition" vs "extending" - for me it's not
clear what "adding pages" really means, but I might be too deep into this.

Greetings,

Andres Freund

On Mon, May 22, 2023 at 10:59:36AM -0700, Andres Freund wrote:

On 2023-05-21 22:46:58 -0400, Bruce Momjian wrote:

For the above two items, I mention items that would change user
like new features or changes that are significant enough that they would
change user behavior. For example, if a new join method increases
performance by 5x, that could change user behavior. Based on the quoted
numbers above, I didn't think "hash now faster" would be appropriate to
mention. Right?

I continue, as in past releases, to think that this is a bad policy. For
existing workloads performance improvements are commonly a more convincing
reason to upgrade than new features - they allow users to scale the workload
further, without needing application changes.

Of course there are performance improvement that are too miniscule to be worth
mentioning, but it's not a common case.

And here it's not just performance, but also memory usage, including steady
state memory usage.

Understood. I continue to need help determining which items to include.
Can you suggest some text? This?

Improve efficiency of memory usage to allow for better scaling

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Mon, May 22, 2023 at 11:03:31AM -0700, Andres Freund wrote:

On 2023-05-21 22:52:09 -0400, Bruce Momjian wrote:

Do average users know heap and index files are both relations? That
seems too abstract so I spelled out heap and index pages.

I don't know about average users - but I think users that read the release
notes do know.

I am a bit on the fence about "addition" vs "extending" - for me it's not
clear what "adding pages" really means, but I might be too deep into this.

I am worried "extending" and "extensions" might be too close a wording
since we often mention extensions. I tried "increase the file eize" but
that seemed wordy. Ideas?

Personally, while I consider heap and indexes to be both relations at
the SQL level, at the file system level I tend to think of them as
different, but perhaps that is just me.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Sun, May 21, 2023 at 3:05 PM Jonathan S. Katz <jkatz@postgresql.org> wrote:

* Support for regular expressions for matching usernames and databases
names in `pg_hba.conf`, and user names in `pg_ident.conf`

I suggest that this is not a major feature.

Perhaps the work that I did to improve CREATEROLE could be considered
for inclusion in the major features list. In previous releases,
someone with CREATEROLE can hack the PG OS account. Now they can't. In
previous releases, someone with CREATEROLE can manage all
non-superuser roles, but now they can manage the roles they create (or
ones they are given explicit authority to manage). You can even
control whether or not such users automatically inherit the privileges
of roles they create, as superusers inherit all privileges. There is
certainly some argument that this is not a sufficiently significant
set of changes to justify a major feature mention, and even if it is,
it's not clear to me exactly how it would be best worded. And yet I
feel like it's very likely that if we look back on this release in 3
years, those changes will have had a significant impact on many
PostgreSQL deployments, above all in the cloud, whereas I think it
likely that the ability to have regular expressions in pg_hba.conf and
pg_ident.conf will have had very little effect by comparison.

Of course, there is always a possibility that I'm over-estimating the
impact of my own work.

--
Robert Haas
EDB: http://www.enterprisedb.com

Hi Bruce,

Add support for SSE2 (Streaming SIMD Extensions 2) vector operations on

x86-64 architectures (John Naylor)

Add support for Advanced SIMD (Single Instruction Multiple Data) (NEON)

instructions on ARM architectures (Nathan Bossart)

Nit: It's a bit odd that SIMD is spelled out in only the Arm entry, and
perhaps expanding the abbreviations can be left out.

Allow arrays searches to use vector operations on x86-64 architectures

(John Naylor)

We can leave out the architecture here (see below). Typo: "array searches"

All the above seem appropriate for the "source code" section, but the
following entries might be better in the "performance" section:

Allow ASCII string detection to use vector operations on x86-64

architectures (John Naylor)

Allow JSON string processing to use vector operations on x86-64

architectures (John Naylor)

ARM?

Arm as well. For anything using 16-byte vectors the two architectures are
equivalently supported. For all the applications, I would just say "vector"
or "SIMD".

And here maybe /processing/parsing/.

Allow xid/subxid searches to use vector operations on x86-64

architectures (Nathan Bossart)

When moved to the performance section, it would be something like "improve
scalability when a large number of write transactions are in progress".

--
John Naylor
EDB: http://www.enterprisedb.com

On Tue, May 23, 2023 at 09:58:30AM +0700, John Naylor wrote:

Hi Bruce,

Add support for SSE2 (Streaming SIMD Extensions 2) vector operations on

x86-64 architectures (John Naylor)

Add support for Advanced SIMD (Single Instruction Multiple Data) (NEON)

instructions on ARM architectures (Nathan Bossart)

Nit: It's a bit odd that SIMD is spelled out in only the Arm entry, and perhaps
expanding the abbreviations can be left out.

The issue is that x86-64's SSE2 uses an embedded acronym:

SSE2 (Streaming SIMD Extensions 2)

so technically it is:

SSE2 (Streaming (Single Instruction Multiple Data) Extensions 2

but embedded acronyms is something I wanted to avoid. ;-)

Allow arrays searches to use vector operations on x86-64 architectures (John

Naylor)

We can leave out the architecture here (see below). Typo: "array searches"

Both fixed.

All the above seem appropriate for the "source code" section, but the following
entries might be better in the "performance" section:

Allow ASCII string detection to use vector operations on x86-64 architectures

(John Naylor)

Allow JSON string processing to use vector operations on x86-64 architectures

(John Naylor)

ARM?

Arm as well. For anything using 16-byte vectors the two architectures are
equivalently supported. For all the applications, I would just say "vector" or
"SIMD".

Okay, I kept "vector". I don't think moving them into performance makes
sense because there I don't think this would impact user behavior or
choice, and it can't be controlled.

And here maybe /processing/parsing/.

Done.

Allow xid/subxid searches to use vector operations on x86-64 architectures

(Nathan Bossart)

When moved to the performance section, it would be something like "improve
scalability when a large number of write transactions are in progress".

Uh, again, see above, this does not impact user behavior or choices. I
assume this is x86-64-only.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Tue, May 23, 2023 at 11:26 AM Bruce Momjian <bruce@momjian.us> wrote:

On Tue, May 23, 2023 at 09:58:30AM +0700, John Naylor wrote:

Allow ASCII string detection to use vector operations on x86-64

architectures

(John Naylor)

Allow JSON string processing to use vector operations on x86-64

architectures

(John Naylor)

ARM?

Arm as well. For anything using 16-byte vectors the two architectures

are

equivalently supported. For all the applications, I would just say

"vector" or

"SIMD".

Okay, I kept "vector". I don't think moving them into performance makes
sense because there I don't think this would impact user behavior or
choice, and it can't be controlled.

Well, these two items were only committed because of measurable speed
increases, and have zero effect on how developers work with "source code",
so that's a category error.

Whether they rise to the significance of warranting inclusion in release
notes is debatable.

Allow xid/subxid searches to use vector operations on x86-64

architectures

(Nathan Bossart)

When moved to the performance section, it would be something like

"improve

scalability when a large number of write transactions are in progress".

Uh, again, see above, this does not impact user behavior or choices.

So that turns a scalability improvement into "source code"?

I assume this is x86-64-only.

Au contraire, I said "For anything using 16-byte vectors the two
architectures are equivalently supported". It's not clear from looking at
individual commit messages, that's why I piped in to help.

--
John Naylor
EDB: http://www.enterprisedb.com

On Mon, 22 May 2023 at 07:05, Jonathan S. Katz <jkatz@postgresql.org> wrote:

* Parallel execution of queries that use `FULL` and `OUTER` joins

I think this should be `RIGHT` joins rather than `OUTER` joins.

LEFT joins have been parallelizable I think for a long time now.

David

On Tue, 23 May 2023 at 06:04, Bruce Momjian <bruce@momjian.us> wrote:

On Mon, May 22, 2023 at 10:59:36AM -0700, Andres Freund wrote:

And here it's not just performance, but also memory usage, including steady
state memory usage.

Understood. I continue to need help determining which items to include.
Can you suggest some text? This?

Improve efficiency of memory usage to allow for better scaling

Maybe something like:

* Reduce palloc() memory overhead for all memory allocations down to 8
bytes on all platforms. (Andres Freund, David Rowley)

This allows more efficient use of memory and is especially useful in
queries which perform operations (such as sorting or hashing) that
require more than work_mem.

David

On 5/23/23 4:37 PM, David Rowley wrote:

On Mon, 22 May 2023 at 07:05, Jonathan S. Katz <jkatz@postgresql.org> wrote:

* Parallel execution of queries that use `FULL` and `OUTER` joins

I think this should be `RIGHT` joins rather than `OUTER` joins.

LEFT joins have been parallelizable I think for a long time now.

I had grabbed it from this line:

Allow outer and full joins to be performed in parallel (Melanie
Plageman, Thomas Munro)

If we want to be specific on RIGHT joins, I can update it in the release
announcement, but it may be too late for the release notes (at least for
beta 1).

Thanks,

Jonathan

On 5/22/23 4:18 PM, Robert Haas wrote:

On Sun, May 21, 2023 at 3:05 PM Jonathan S. Katz <jkatz@postgresql.org> wrote:

* Support for regular expressions for matching usernames and databases
names in `pg_hba.conf`, and user names in `pg_ident.conf`

I suggest that this is not a major feature.

Perhaps the work that I did to improve CREATEROLE could be considered
for inclusion in the major features list. In previous releases,
someone with CREATEROLE can hack the PG OS account. Now they can't. In
previous releases, someone with CREATEROLE can manage all
non-superuser roles, but now they can manage the roles they create (or
ones they are given explicit authority to manage). You can even
control whether or not such users automatically inherit the privileges
of roles they create, as superusers inherit all privileges. There is
certainly some argument that this is not a sufficiently significant
set of changes to justify a major feature mention, and even if it is,
it's not clear to me exactly how it would be best worded. And yet I
feel like it's very likely that if we look back on this release in 3
years, those changes will have had a significant impact on many
PostgreSQL deployments, above all in the cloud, whereas I think it
likely that the ability to have regular expressions in pg_hba.conf and
pg_ident.conf will have had very little effect by comparison.

Of course, there is always a possibility that I'm over-estimating the
impact of my own work.

In general, I'm completely fine with people advocating for their own
features during this process, in case there's something that I missed.

For this case, while I think this work is very impactful, but I don't
know if I'd call it a major feature vs. modifying an unintended
behavior. Additionally, folks have likely put mitigations in place for
this through the years. I'm happy to be convinced otherwise.

The regular expressions in the files adds an ability that both we didn't
have before, and has been a request I've heard from users with very
large deployments. For them, it'll help simplify a lot of their
configurations/automations for setting this up en masse. Again, I'm
happy to be convinced otherwise.

I wanted to use the beta release to allow for us to see 1/ how people
ultimately test these things and 2/ help better sift out what will be
called a major feature. We could end up shuffling items in the list or
completely rewriting it, so it's not set in stone.

Thanks,

Jonathan

On Wed, May 24, 2023 at 08:37:45AM +1200, David Rowley wrote:

On Mon, 22 May 2023 at 07:05, Jonathan S. Katz <jkatz@postgresql.org> wrote:

* Parallel execution of queries that use `FULL` and `OUTER` joins

I think this should be `RIGHT` joins rather than `OUTER` joins.

LEFT joins have been parallelizable I think for a long time now.

Well, since we can swap left/right easily, why would we not have just
have swappted the tables and done the join in the past? I think there
are two things missing in my description.

First, I need to mention parallel _hash_ join. Second, I think this
item is saying that the _inner_ side of a parallel hash join can be an
OUTER or FULL join. How about?

Allow hash joins to be parallelized where the inner side is
processed as an OUTER or FULL join (Melanie Plageman, Thomas Munro)

In this case, the inner side is the hashed side.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Tue, May 23, 2023 at 06:27:23PM -0400, Jonathan Katz wrote:

On 5/23/23 4:37 PM, David Rowley wrote:

On Mon, 22 May 2023 at 07:05, Jonathan S. Katz <jkatz@postgresql.org> wrote:

* Parallel execution of queries that use `FULL` and `OUTER` joins

I think this should be `RIGHT` joins rather than `OUTER` joins.

LEFT joins have been parallelizable I think for a long time now.

I had grabbed it from this line:

Allow outer and full joins to be performed in parallel (Melanie Plageman,
Thomas Munro)

If we want to be specific on RIGHT joins, I can update it in the release
announcement, but it may be too late for the release notes (at least for
beta 1).

We will have many more edits before final so I would not worry about
adjusting the beta1 wording.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Tue, May 23, 2023 at 12:14:04PM +0700, John Naylor wrote:

On Tue, May 23, 2023 at 11:26 AM Bruce Momjian <bruce@momjian.us> wrote:

Allow xid/subxid searches to use vector operations on x86-64

architectures

(Nathan Bossart)

When moved to the performance section, it would be something like "improve
scalability when a large number of write transactions are in progress".

Uh, again, see above, this does not impact user behavior or choices.  

So that turns a scalability improvement into "source code"?

I assume this is x86-64-only.

Au contraire, I said "For anything using 16-byte vectors the two architectures
are equivalently supported". It's not clear from looking at individual commit
messages, that's why I piped in to help.

Okay, updated text:

Allow xid/subxid searches to use vector operations (Nathan Bossart)

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Wed, 24 May 2023 at 15:54, Bruce Momjian <bruce@momjian.us> wrote:

On Wed, May 24, 2023 at 08:37:45AM +1200, David Rowley wrote:

On Mon, 22 May 2023 at 07:05, Jonathan S. Katz <jkatz@postgresql.org> wrote:

* Parallel execution of queries that use `FULL` and `OUTER` joins

I think this should be `RIGHT` joins rather than `OUTER` joins.

LEFT joins have been parallelizable I think for a long time now.

Well, since we can swap left/right easily, why would we not have just
have swappted the tables and done the join in the past? I think there
are two things missing in my description.

First, I need to mention parallel _hash_ join. Second, I think this
item is saying that the _inner_ side of a parallel hash join can be an
OUTER or FULL join. How about?

Allow hash joins to be parallelized where the inner side is
processed as an OUTER or FULL join (Melanie Plageman, Thomas Munro)

In this case, the inner side is the hashed side.

I think Jonathan's text is safe to swap OUTER to RIGHT as it mentions
"execution". For the release notes, maybe the mention of it can be
moved away from "E.1.3.1.1. Optimizer" and put under "E.1.3.1.2.
General Performance" and ensure we mention that we're talking about
the executor?

I'm thinking it might be confusing if we claim that this is something
that we switched on in the planner. It was a limitation with the
executor which the planner was just onboard with not producing plans
for.

David

On Tue, May 23, 2023 at 12:14:04PM +0700, John Naylor wrote:

On Tue, May 23, 2023 at 11:26 AM Bruce Momjian <bruce@momjian.us> wrote:

On Tue, May 23, 2023 at 09:58:30AM +0700, John Naylor wrote:

Allow ASCII string detection to use vector operations on x86-64

architectures

(John Naylor)

Allow JSON string processing to use vector operations on x86-64

architectures

(John Naylor)

ARM?

Arm as well. For anything using 16-byte vectors the two architectures are
equivalently supported. For all the applications, I would just say "vector"

or

"SIMD".

Okay, I kept "vector".  I don't think moving them into performance makes
sense because there I don't think this would impact user behavior or
choice, and it can't be controlled.

Well, these two items were only committed because of measurable speed
increases, and have zero effect on how developers work with "source code", so
that's a category error.

Whether they rise to the significance of warranting inclusion in release notes
is debatable.

Okay, let's dissect this. First, I am excited about these features
because I think they show innovation, particularly for high scaling, so
I want to highlight this.

Second, you might be correct that the section is wrong. I thought of
CPU instructions as something tied to the compiler, so part of the build
process or source code, but the point we should be make is that we have
these acceleration, not how it is implemented. We can move the entire
group to the "General Performance" section, or we can split it out:

Keep in source code:

Add support for SSE2 (Streaming SIMD Extensions 2) vector operations on
x86-64 architectures (John Naylor)

Add support for Advanced SIMD (Single Instruction Multiple Data) (NEON)
instructions on ARM architectures (Nathan Bossart)

move to General Performance:

Allow xid/subxid searches to use vector operations (Nathan Bossart)

Allow ASCII string detection to use vector operations (John Naylor)

and add these to data types:

Allow JSON string parsing to use vector operations (John Naylor)

Allow array searches to use vector operations (John Naylor)

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Wed, May 24, 2023 at 11:19 AM Bruce Momjian <bruce@momjian.us> wrote:

Second, you might be correct that the section is wrong. I thought of
CPU instructions as something tied to the compiler, so part of the build
process or source code, but the point we should be make is that we have
these acceleration, not how it is implemented. We can move the entire
group to the "General Performance" section, or we can split it out:

Splitting out like that seems like a good idea to me.

Keep in source code:

Add support for SSE2 (Streaming SIMD Extensions 2) vector

operations on

x86-64 architectures (John Naylor)

Add support for Advanced SIMD (Single Instruction Multiple Data)

(NEON)

instructions on ARM architectures (Nathan Bossart)

move to General Performance:

Allow xid/subxid searches to use vector operations (Nathan

Bossart)

Allow ASCII string detection to use vector operations (John

Naylor)

(The ASCII part is most relevant for COPY FROM, just in case that matters.)

and add these to data types:

Allow JSON string parsing to use vector operations (John Naylor)

Allow array searches to use vector operations (John Naylor)

The last one refers to new internal functions, so it could stay in source
code. (Either way, we don't want to imply that arrays of SQL types are
accelerated this way, it's so far only for internal arrays.)

--
John Naylor
EDB: http://www.enterprisedb.com

On Wed, May 24, 2023 at 12:23:02PM +0700, John Naylor wrote:

On Wed, May 24, 2023 at 11:19 AM Bruce Momjian <bruce@momjian.us> wrote:

Second, you might be correct that the section is wrong.  I thought of
CPU instructions as something tied to the compiler, so part of the build
process or source code, but the point we should be make is that we have
these acceleration, not how it is implemented.  We can move the entire
group to the "General Performance" section, or we can split it out:

Splitting out like that seems like a good idea to me. 

Okay, items split into sections and several merged. I left the
CPU-specific parts in Source Code, and moved the rest into a merged item
in General Performance, but moved the JSON item to Data Types.

Patch attached, and you can see the results at:

https://momjian.us/pgsql_docs/release-16.html

The last one refers to new internal functions, so it could stay in source code.
(Either way, we don't want to imply that arrays of SQL types are accelerated
this way, it's so far only for internal arrays.)

Good point. I called them "C arrays" but it it into the General
Performance item.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

Op 5/24/23 om 15:58 schreef Bruce Momjian:

On Wed, May 24, 2023 at 12:23:02PM +0700, John Naylor wrote:

On Wed, May 24, 2023 at 11:19 AM Bruce Momjian <bruce@momjian.us> wrote:

Typos:

'from standbys servers' should be
'from standby servers'

'reindexedb' should be
'reindexdb'
(2x: the next line mentions, erroneously, 'reindexedb --system')

'created only created' should be
'only created'
(I think)

'could could' should be
'could'

'are now require the role' should be
'now require the role'

'values is' should be
'value is'

'to marked' should be
'to be marked'

thanks,
Erik

On Wed, May 24, 2023 at 04:57:59PM +0200, Erik Rijkers wrote:

Op 5/24/23 om 15:58 schreef Bruce Momjian:

On Wed, May 24, 2023 at 12:23:02PM +0700, John Naylor wrote:

On Wed, May 24, 2023 at 11:19 AM Bruce Momjian <bruce@momjian.us> wrote:

Typos:

'from standbys servers' should be
'from standby servers'

'reindexedb' should be
'reindexdb'
(2x: the next line mentions, erroneously, 'reindexedb --system')

'created only created' should be
'only created'
(I think)

'could could' should be
'could'

'are now require the role' should be
'now require the role'

'values is' should be
'value is'

'to marked' should be
'to be marked'

All good, patch attached and applied. Updated docs are at:

https://momjian.us/pgsql_docs/release-16.html

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On 5/24/23 12:13 AM, David Rowley wrote:

On Wed, 24 May 2023 at 15:54, Bruce Momjian <bruce@momjian.us> wrote:

On Wed, May 24, 2023 at 08:37:45AM +1200, David Rowley wrote:

On Mon, 22 May 2023 at 07:05, Jonathan S. Katz <jkatz@postgresql.org> wrote:

* Parallel execution of queries that use `FULL` and `OUTER` joins

I think this should be `RIGHT` joins rather than `OUTER` joins.

LEFT joins have been parallelizable I think for a long time now.

Well, since we can swap left/right easily, why would we not have just
have swappted the tables and done the join in the past? I think there
are two things missing in my description.

First, I need to mention parallel _hash_ join. Second, I think this
item is saying that the _inner_ side of a parallel hash join can be an
OUTER or FULL join. How about?

Allow hash joins to be parallelized where the inner side is
processed as an OUTER or FULL join (Melanie Plageman, Thomas Munro)

In this case, the inner side is the hashed side.

I think Jonathan's text is safe to swap OUTER to RIGHT as it mentions
"execution".

I made this swap in the release announcement. Thanks!

Jonathan

On Wed, May 24, 2023 at 08:48:56AM +1200, David Rowley wrote:

On Tue, 23 May 2023 at 06:04, Bruce Momjian <bruce@momjian.us> wrote:

On Mon, May 22, 2023 at 10:59:36AM -0700, Andres Freund wrote:

And here it's not just performance, but also memory usage, including steady
state memory usage.

Understood. I continue to need help determining which items to include.
Can you suggest some text? This?

Improve efficiency of memory usage to allow for better scaling

Maybe something like:

* Reduce palloc() memory overhead for all memory allocations down to 8
bytes on all platforms. (Andres Freund, David Rowley)

This allows more efficient use of memory and is especially useful in
queries which perform operations (such as sorting or hashing) that
require more than work_mem.

Well, this would go in the source code section, but it seems too
internal and global to mention.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Wed, May 24, 2023 at 01:43:50PM -0400, Bruce Momjian wrote:

* Reduce palloc() memory overhead for all memory allocations down to 8
bytes on all platforms. (Andres Freund, David Rowley)

This allows more efficient use of memory and is especially useful in
queries which perform operations (such as sorting or hashing) that
require more than work_mem.

Well, this would go in the source code section, but it seems too
internal and global to mention.

What was the previous memory allocation overhead?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Wed, May 24, 2023 at 8:58 PM Bruce Momjian <bruce@momjian.us> wrote:

Okay, items split into sections and several merged. I left the
CPU-specific parts in Source Code, and moved the rest into a merged item
in General Performance, but moved the JSON item to Data Types.

It looks like it got moved to Functions actually?

The last one refers to new internal functions, so it could stay in

source code.

(Either way, we don't want to imply that arrays of SQL types are

accelerated

this way, it's so far only for internal arrays.)

Good point. I called them "C arrays" but it it into the General
Performance item.

Looks good to me, although...

Allow xid/subxid searches and ASCII string detection to use vector

operations (Nathan Bossart)

Nathan wrote the former, I did the latter.

Thanks for working on this!

--
John Naylor
EDB: http://www.enterprisedb.com

On Thu, May 25, 2023 at 08:31:29AM +0700, John Naylor wrote:

On Wed, May 24, 2023 at 8:58 PM Bruce Momjian <bruce@momjian.us> wrote:

Okay, items split into sections and several merged.  I left the
CPU-specific parts in Source Code, and moved the rest into a merged item
in General Performance, but moved the JSON item to Data Types.

It looks like it got moved to Functions actually?

The last one refers to new internal functions, so it could stay in source

code.

(Either way, we don't want to imply that arrays of SQL types are

accelerated

this way, it's so far only for internal arrays.)

Good point.  I called them "C arrays" but it it into the General
Performance item.

Looks good to me, although...

Allow xid/subxid searches and ASCII string detection to use vector operations

(Nathan Bossart)

Nathan wrote the former, I did the latter.

Thanks for working on this!

Ugh, I have to remember to merge authors when I merge items --- fixed.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Thu, 25 May 2023 at 05:45, Bruce Momjian <bruce@momjian.us> wrote:

On Wed, May 24, 2023 at 01:43:50PM -0400, Bruce Momjian wrote:

* Reduce palloc() memory overhead for all memory allocations down to 8
bytes on all platforms. (Andres Freund, David Rowley)

This allows more efficient use of memory and is especially useful in
queries which perform operations (such as sorting or hashing) that
require more than work_mem.

Well, this would go in the source code section, but it seems too
internal and global to mention.

What was the previous memory allocation overhead?

On 64-bit builds, it was 16 bytes for AllocSet contexts, 24 bytes for
generation contexts and 16 bytes for slab contexts.

David

Bruce Momjian <bruce@momjian.us> writes:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

The bit about auto_explain and query parameters says:

Allow auto_explain to log query parameters used in executing prepared
statements (Dagfinn Ilmari Mannsåker)

This is controlled by auto_explain.log_parameter_max_length, and by
default query parameters will be logged with no length
restriction. SHOULD THIS BE MORE CLEARLY IDENTIFIED AS CONTROLLING THE
EXECUTION OF PREPARED STATEMENTS?

This is wrong, the logging applies to all query parameters, not just for
prepared statements (and has nothing to do with controlling the
execution thereof). That was just the only way to test it when it was
written, because psql's \bind command exist yet then.

Should we perhaps add some tests for that, like the attached?

- ilmari

On Thu, 2023-05-18 at 16:49 -0400, Bruce Momjian wrote:

I have completed the first draft of the PG 16 release notes.

I found two typos.

Yours,
Laurenz Albe

On 2023-May-25, Laurenz Albe wrote:

@@ -1335,7 +1335,7 @@ Author: Peter Eisentraut <peter@eisentraut.org>

<listitem>
<para>
-Add Windows process the system collations (Jose Santamaria Flecha)
+Add Windows to process the system collations (Jose Santamaria Flecha)
ADD THIS?
</para>
</listitem>

Hmm, not sure this describes the change properly. Maybe something like
"On Windows, system locales are now imported automatically. Previously,
only ICU locales were imported automatically on Windows."

Maybe the Windows improvements should be listed together in a separate
section.

Also, "Juan José Santamaría Flecha" is the spelling Juan José uses for
his name.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/

On Thu, May 25, 2023 at 09:20:11PM +0100, Dagfinn Ilmari Mannsåker wrote:

Bruce Momjian <bruce@momjian.us> writes:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

The bit about auto_explain and query parameters says:

Allow auto_explain to log query parameters used in executing prepared
statements (Dagfinn Ilmari Mannsåker)

This is controlled by auto_explain.log_parameter_max_length, and by
default query parameters will be logged with no length
restriction. SHOULD THIS BE MORE CLEARLY IDENTIFIED AS CONTROLLING THE
EXECUTION OF PREPARED STATEMENTS?

This is wrong, the logging applies to all query parameters, not just for
prepared statements (and has nothing to do with controlling the
execution thereof). That was just the only way to test it when it was
written, because psql's \bind command exist yet then.

I see your point. How is this?

Allow auto_explain to log query parameters used by parameterized
statements (Dagfinn Ilmari Mannsåker)

This affects queries using server-side PRAPARE/EXECUTE
and client-side parse/bind. Logging is controlled by
auto_explain.log_parameter_max_length; by default query
parameters will be logged with no length restriction.

Should we perhaps add some tests for that, like the attached?

Sorry, I don't know the answer.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Thu, May 25, 2023 at 11:51:24PM +0200, Laurenz Albe wrote:

On Thu, 2023-05-18 at 16:49 -0400, Bruce Momjian wrote:

I have completed the first draft of the PG 16 release notes.

I found two typos.

diff --git a/doc/src/sgml/release-16.sgml b/doc/src/sgml/release-16.sgml
index faecae7c42..7dad0b8550 100644
--- a/doc/src/sgml/release-16.sgml
+++ b/doc/src/sgml/release-16.sgml
@@ -1294,7 +1294,7 @@ Determine the ICU default locale from the environment (Jeff Davis)
</para>

<para>
-However, ICU doesn't support the C local so UTF-8 is used in such cases.  Previously the default was always UTF-8.
+However, ICU doesn't support the C locale so UTF-8 is used in such cases.  Previously the default was always UTF-8.
</para>
</listitem>

I have made this change.

@@ -1335,7 +1335,7 @@ Author: Peter Eisentraut <peter@eisentraut.org>

<listitem>
<para>
-Add Windows process the system collations (Jose Santamaria Flecha)
+Add Windows to process the system collations (Jose Santamaria Flecha)
ADD THIS?
</para>
</listitem>

I will deal with this item in the email from Álvaro Herrera.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Fri, May 26, 2023 at 12:21:23PM +0200, Álvaro Herrera wrote:

On 2023-May-25, Laurenz Albe wrote:

@@ -1335,7 +1335,7 @@ Author: Peter Eisentraut <peter@eisentraut.org>

<listitem>
<para>
-Add Windows process the system collations (Jose Santamaria Flecha)
+Add Windows to process the system collations (Jose Santamaria Flecha)
ADD THIS?
</para>
</listitem>

Hmm, not sure this describes the change properly. Maybe something like
"On Windows, system locales are now imported automatically. Previously,
only ICU locales were imported automatically on Windows."

Maybe the Windows improvements should be listed together in a separate
section.

Also, "Juan José Santamaría Flecha" is the spelling Juan José uses for
his name.

Okay, I reword this and fixed Juan's name, attached, and applied.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Sun, May 21, 2023 at 10:47 PM Bruce Momjian <bruce@momjian.us> wrote:

Okay new merged item is:

<!--
Author: Andres Freund <andres@anarazel.de>
2023-04-08 [0fdab27ad] Allow logical decoding on standbys
Author: Andres Freund <andres@anarazel.de>
2023-04-07 [be87200ef] Support invalidating replication slots due to horizon an
Author: Andres Freund <andres@anarazel.de>
2023-04-08 [26669757b] Handle logical slot conflicts on standby
Author: Andres Freund <andres@anarazel.de>
2023-04-08 [0fdab27ad] Allow logical decoding on standbys
-->

<listitem>
<para>
Allow logical decoding on standbys (Bertrand Drouvot, Andres Freund, Amit Khandekar, Bertrand Drouvot)
</para>
</listitem>

<listitem>
<para>
New function pg_log_standby_snapshot() forces creation of WAL snapshots.
Snapshots are required for logical slot creation so this function speeds their creation on standbys.
</para>
</listitem>

Bertrand Drouvot is mentioned two times in this item and commit
0fdab27ad is listed two times. Is it intentional?

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

On Mon, May 29, 2023 at 10:08:41AM -0400, Masahiko Sawada wrote:

On Sun, May 21, 2023 at 10:47 PM Bruce Momjian <bruce@momjian.us> wrote:

Okay new merged item is:

<!--
Author: Andres Freund <andres@anarazel.de>
2023-04-08 [0fdab27ad] Allow logical decoding on standbys
Author: Andres Freund <andres@anarazel.de>
2023-04-07 [be87200ef] Support invalidating replication slots due to horizon an
Author: Andres Freund <andres@anarazel.de>
2023-04-08 [26669757b] Handle logical slot conflicts on standby
Author: Andres Freund <andres@anarazel.de>
2023-04-08 [0fdab27ad] Allow logical decoding on standbys
-->

<listitem>
<para>
Allow logical decoding on standbys (Bertrand Drouvot, Andres Freund, Amit Khandekar, Bertrand Drouvot)
</para>
</listitem>

<listitem>
<para>
New function pg_log_standby_snapshot() forces creation of WAL snapshots.
Snapshots are required for logical slot creation so this function speeds their creation on standbys.
</para>
</listitem>

Bertrand Drouvot is mentioned two times in this item and commit
0fdab27ad is listed two times. Is it intentional?

Thanks, fixed.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Tue, May 23, 2023 at 11:54:30PM -0400, Bruce Momjian wrote:

On Wed, May 24, 2023 at 08:37:45AM +1200, David Rowley wrote:

On Mon, 22 May 2023 at 07:05, Jonathan S. Katz <jkatz@postgresql.org> wrote:

* Parallel execution of queries that use `FULL` and `OUTER` joins

I think this should be `RIGHT` joins rather than `OUTER` joins.

LEFT joins have been parallelizable I think for a long time now.

Well, since we can swap left/right easily, why would we not have just
have swappted the tables and done the join in the past? I think there
are two things missing in my description.

First, I need to mention parallel _hash_ join. Second, I think this
item is saying that the _inner_ side of a parallel hash join can be an
OUTER or FULL join. How about?

Allow hash joins to be parallelized where the inner side is
processed as an OUTER or FULL join (Melanie Plageman, Thomas Munro)

In this case, the inner side is the hashed side.

I went with this text:

Allow parallelization of FULL and internal right OUTER hash joins
(Melanie Plageman, Thomas Munro)

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Wed, May 24, 2023 at 04:13:14PM +1200, David Rowley wrote:

On Wed, 24 May 2023 at 15:54, Bruce Momjian <bruce@momjian.us> wrote:

First, I need to mention parallel _hash_ join. Second, I think this
item is saying that the _inner_ side of a parallel hash join can be an
OUTER or FULL join. How about?

Allow hash joins to be parallelized where the inner side is
processed as an OUTER or FULL join (Melanie Plageman, Thomas Munro)

In this case, the inner side is the hashed side.

I think Jonathan's text is safe to swap OUTER to RIGHT as it mentions
"execution". For the release notes, maybe the mention of it can be
moved away from "E.1.3.1.1. Optimizer" and put under "E.1.3.1.2.
General Performance" and ensure we mention that we're talking about
the executor?

I'm thinking it might be confusing if we claim that this is something
that we switched on in the planner. It was a limitation with the
executor which the planner was just onboard with not producing plans
for.

Well, I try to keep plan changes in the optimizer section because that
is where the decisions are made, and how people think of plans since
EXPLAIN makes them visible. I agree it is an executor change but I
think that distinction will be more confusing than helpful.

Frankly, almost all the optimizer items are really executor changes.
Maybe the "Optimizer" title needs to be changed, but I do think it is
good to group plan changes together.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Sat, May 27, 2023 at 09:34:37PM -0400, Bruce Momjian wrote:

This is controlled by auto_explain.log_parameter_max_length, and by
default query parameters will be logged with no length
restriction. SHOULD THIS BE MORE CLEARLY IDENTIFIED AS CONTROLLING THE
EXECUTION OF PREPARED STATEMENTS?

This is wrong, the logging applies to all query parameters, not just for
prepared statements (and has nothing to do with controlling the
execution thereof). That was just the only way to test it when it was
written, because psql's \bind command exist yet then.

I see your point. How is this?

Allow auto_explain to log query parameters used by parameterized
statements (Dagfinn Ilmari Mannsåker)

This affects queries using server-side PRAPARE/EXECUTE
and client-side parse/bind. Logging is controlled by
auto_explain.log_parameter_max_length; by default query
parameters will be logged with no length restriction.

Done, attached patch applied.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

Bruce Momjian <bruce@momjian.us> writes:

On Sat, May 27, 2023 at 09:34:37PM -0400, Bruce Momjian wrote:

This is controlled by auto_explain.log_parameter_max_length, and by
default query parameters will be logged with no length
restriction. SHOULD THIS BE MORE CLEARLY IDENTIFIED AS CONTROLLING THE
EXECUTION OF PREPARED STATEMENTS?

This is wrong, the logging applies to all query parameters, not just for
prepared statements (and has nothing to do with controlling the
execution thereof). That was just the only way to test it when it was
written, because psql's \bind command exist yet then.

I see your point. How is this?

Allow auto_explain to log query parameters used by parameterized
statements (Dagfinn Ilmari Mannsåker)

This affects queries using server-side PRAPARE/EXECUTE
and client-side parse/bind. Logging is controlled by
auto_explain.log_parameter_max_length; by default query
parameters will be logged with no length restriction.

Done, attached patch applied.

That works for me. Thanks!

- ilmari

Hi,

On Thu, May 18, 2023 at 4:49 PM Bruce Momjian <bruce@momjian.us> wrote:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

I have one suggestion on this item:

<!--
Author: Amit Kapila <akapila@postgresql.org>
2022-07-21 [366283961] Allow users to skip logical replication of data having o
Author: Amit Kapila <akapila@postgresql.org>
2022-09-08 [875693019] Raise a warning if there is a possibility of data from m
-->

<listitem>
<para>
Allow logical replication subscribers to process only changes that
have no origin (Vignesh C, Amit Kapila)
</para>

<para>
This can be used to avoid replication loops.
</para>
</listitem>

I think it's better to mention the new 'origin' option as other new
subscription options are mentioned. For example,

<para>
This can be used to avoid replication loops. This can be controlled by
the subscription "origin" option.
</para>

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

On Tue, May 30, 2023 at 06:33:09AM -0400, Masahiko Sawada wrote:

Hi,

On Thu, May 18, 2023 at 4:49 PM Bruce Momjian <bruce@momjian.us> wrote:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

I have one suggestion on this item:

<!--
Author: Amit Kapila <akapila@postgresql.org>
2022-07-21 [366283961] Allow users to skip logical replication of data having o
Author: Amit Kapila <akapila@postgresql.org>
2022-09-08 [875693019] Raise a warning if there is a possibility of data from m
-->

<listitem>
<para>
Allow logical replication subscribers to process only changes that
have no origin (Vignesh C, Amit Kapila)
</para>

<para>
This can be used to avoid replication loops.
</para>
</listitem>

I think it's better to mention the new 'origin' option as other new
subscription options are mentioned. For example,

<para>
This can be used to avoid replication loops. This can be controlled by
the subscription "origin" option.
</para>

Great, new text is:

This can be used to avoid replication loops. This is controlled
by the new CREATE SUBSCRIPTION "origin" option.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Thu, May 25, 2023 at 05:57:25PM +1200, David Rowley wrote:

On Thu, 25 May 2023 at 05:45, Bruce Momjian <bruce@momjian.us> wrote:

On Wed, May 24, 2023 at 01:43:50PM -0400, Bruce Momjian wrote:

* Reduce palloc() memory overhead for all memory allocations down to 8
bytes on all platforms. (Andres Freund, David Rowley)

This allows more efficient use of memory and is especially useful in
queries which perform operations (such as sorting or hashing) that
require more than work_mem.

Well, this would go in the source code section, but it seems too
internal and global to mention.

What was the previous memory allocation overhead?

On 64-bit builds, it was 16 bytes for AllocSet contexts, 24 bytes for
generation contexts and 16 bytes for slab contexts.

Okay, item added to Source Code:

<!--
Author: David Rowley <drowley@postgresql.org>
2022-08-29 [c6e0fe1f2] Improve performance of and reduce overheads of memory ma
-->

<listitem>
<para>
Reduce overhead of memory allocations (Andres Freund, David Rowley)
</para>
</listitem>

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Wed, 31 May 2023 at 11:32, Bruce Momjian <bruce@momjian.us> wrote:

On Thu, May 25, 2023 at 05:57:25PM +1200, David Rowley wrote:

On 64-bit builds, it was 16 bytes for AllocSet contexts, 24 bytes for
generation contexts and 16 bytes for slab contexts.

Okay, item added to Source Code:

I don't think this should go under "E.1.3.11. Source Code". The patch
was entirely aimed to increase performance, not just of allocations
themselves, but of any operations which uses palloc'd memory. This is
due to the patch increasing the density of memory allocation on blocks
malloc'd by our memory context code so that fewer CPU cache lines need
to be touched in the entire backend process for *all* memory that's
allocated with palloc. The performance increase here can be fairly
significant for small-sized palloc requests when CPU cache pressure is
high. Since CPU caches aren't that big, it does not take much of a
query to put the cache pressure up. Hashing or sorting a few million
rows is going to do that.

The patch here was born out of the regression report I made in [1]/messages/by-id/CAApHDvqXpLzav6dUeR5vO_RBh_feHrHMLhigVQXw9jHCyKP9PA@mail.gmail.com,
which I mention in [2]/messages/by-id/CAApHDvowHNSVLhMc0cnovg8PfnYQZxit-gP_bn3xkT4rZX3G0w@mail.gmail.com about the prototype patch Andres wrote to fix
the performance regression.

I think "E.1.3.1.2. General Performance" might be a better location.
Having it under "Source Code" makes it sound like it was some
refactoring work. That's certainly not the case.

A bit more detail:

Here's a small histogram of the number of allocations in various size
buckets from running make check with some debug output in
AllocSetAlloc and GenerationAlloc to record the size of the
allocation:

bucket | number_of_allocations | percent_of_total_allocations
----------------+-----------------------+---------
up to 16 bytes | 8,881,106 | 31.39
up to 32 bytes | 4,579,608 | 16.18
up to 64 bytes | 6,574,107 | 23.23
above 64 bytes | 8,260,714 | 29.19

So quite a large portion of our allocations (at least in our test
suite) are small. Halving the 16-byte chunk header down 8 bytes on a
16-byte allocation means a 25% memory saving.

David

[1]: /messages/by-id/CAApHDvqXpLzav6dUeR5vO_RBh_feHrHMLhigVQXw9jHCyKP9PA@mail.gmail.com
[2]: /messages/by-id/CAApHDvowHNSVLhMc0cnovg8PfnYQZxit-gP_bn3xkT4rZX3G0w@mail.gmail.com

On Wed, May 31, 2023 at 06:03:01PM +1200, David Rowley wrote:

I don't think this should go under "E.1.3.11. Source Code". The patch
was entirely aimed to increase performance, not just of allocations
themselves, but of any operations which uses palloc'd memory. This is
due to the patch increasing the density of memory allocation on blocks
malloc'd by our memory context code so that fewer CPU cache lines need
to be touched in the entire backend process for *all* memory that's
allocated with palloc. The performance increase here can be fairly
significant for small-sized palloc requests when CPU cache pressure is
high. Since CPU caches aren't that big, it does not take much of a
query to put the cache pressure up. Hashing or sorting a few million
rows is going to do that.

The patch here was born out of the regression report I made in [1],
which I mention in [2] about the prototype patch Andres wrote to fix
the performance regression.

I think "E.1.3.1.2. General Performance" might be a better location.
Having it under "Source Code" makes it sound like it was some
refactoring work. That's certainly not the case.

Okay, moved.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

Hello,

On Thu, 18 May 2023 16:49:47 -0400
Bruce Momjian <bruce@momjian.us> wrote:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

Thanks for the release notes.

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

I didn't find the following in the release note. This might be
considered as a bug fix, but the change in this commit can potentially
impact applications. Is it worth including it in the release note?

commit 43351557d0d2b9c5e20298b5fee2849abef86aff
Make materialized views participate in predicate locking

Regards,
Yugo Nagata

I learned a few things creating it this time:

* I can get confused over C function names and SQL function names in
commit messages.

* The sections and ordering of the entries can greatly clarify the
items.

* The feature count is slightly higher than recent releases:

release-10: 189
release-11: 170
release-12: 180
release-13: 178
release-14: 220
release-15: 184
--> release-16: 200

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

--
Yugo NAGATA <nagata@sraoss.co.jp>

On Mon, Jun 5, 2023 at 05:33:51PM +0900, Yugo NAGATA wrote:

Hello,

On Thu, 18 May 2023 16:49:47 -0400
Bruce Momjian <bruce@momjian.us> wrote:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

Thanks for the release notes.

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

I didn't find the following in the release note. This might be
considered as a bug fix, but the change in this commit can potentially
impact applications. Is it worth including it in the release note?

commit 43351557d0d2b9c5e20298b5fee2849abef86aff
Make materialized views participate in predicate locking

I did look at this commit and decided, thought it is a behavior change,
that it is probably something that would be caught during upgrade
testing. I thought it was rare enough, and so hard to describe about
how to adjust for it, that is should not be mentioned. If we find that
users do hit this issue, or others, during beta, we can add it.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

Hi,

On Fri, May 19, 2023 at 5:49 AM Bruce Momjian <bruce@momjian.us> wrote:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

<!--
Author: Michael Paquier <michael@paquier.xyz>
2023-03-14 [5c1b66280] Rework design of functions in pg_walinspect
-->

<listitem>
<para>
Remove pg_walinspect functions
pg_get_wal_records_info_till_end_of_wal() and
pg_get_wal_stats_till_end_of_wal().
</para>
</listitem>

I found that this item misses the author, Bharath Rupireddy. Please
add his name.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

On Mon, 5 Jun 2023 11:42:43 -0400
Bruce Momjian <bruce@momjian.us> wrote:

On Mon, Jun 5, 2023 at 05:33:51PM +0900, Yugo NAGATA wrote:

Hello,

On Thu, 18 May 2023 16:49:47 -0400
Bruce Momjian <bruce@momjian.us> wrote:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

Thanks for the release notes.

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

I didn't find the following in the release note. This might be
considered as a bug fix, but the change in this commit can potentially
impact applications. Is it worth including it in the release note?

commit 43351557d0d2b9c5e20298b5fee2849abef86aff
Make materialized views participate in predicate locking

I did look at this commit and decided, thought it is a behavior change,
that it is probably something that would be caught during upgrade
testing. I thought it was rare enough, and so hard to describe about
how to adjust for it, that is should not be mentioned. If we find that
users do hit this issue, or others, during beta, we can add it.

Thank you for replying. I understood.

Regards,
Yugo Nagata

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

--
Yugo NAGATA <nagata@sraoss.co.jp>

On Thu, Jun 8, 2023 at 02:23:33PM +0900, Masahiko Sawada wrote:

Hi,

On Fri, May 19, 2023 at 5:49 AM Bruce Momjian <bruce@momjian.us> wrote:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

<!--
Author: Michael Paquier <michael@paquier.xyz>
2023-03-14 [5c1b66280] Rework design of functions in pg_walinspect
-->

<listitem>
<para>
Remove pg_walinspect functions
pg_get_wal_records_info_till_end_of_wal() and
pg_get_wal_stats_till_end_of_wal().
</para>
</listitem>

I found that this item misses the author, Bharath Rupireddy. Please
add his name.

Thanks, fixed.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

Adding to this thread as suggested by jkatz for consideration of
adding to release notes...

In [1]/messages/by-id/CAKz==bLzGb-9O294AoZHqEWpAi2Ki58yCr4gaqg1HnZyh3L1uA@mail.gmail.com I mention the omission of ldap_password_hook and a suggested paragraph.

Roberto

[1]: /messages/by-id/CAKz==bLzGb-9O294AoZHqEWpAi2Ki58yCr4gaqg1HnZyh3L1uA@mail.gmail.com

On Tue, Jun 27, 2023 at 03:49:44PM -0600, Roberto Mello wrote:

Adding to this thread as suggested by jkatz for consideration of
adding to release notes...

In [1] I mention the omission of ldap_password_hook and a suggested paragraph.

Roberto

[1] /messages/by-id/CAKz==bLzGb-9O294AoZHqEWpAi2Ki58yCr4gaqg1HnZyh3L1uA@mail.gmail.com

I did see that commit:

commit 419a8dd814
Author: Andrew Dunstan <andrew@dunslane.net>
Date: Wed Mar 15 16:37:28 2023 -0400

Add a hook for modifying the ldapbind password

The hook can be installed by a shared_preload library.

A similar mechanism could be used for radius paswords, for example, and
the type name auth_password_hook_typ has been shosen with that in mind.

John Naylor and Andrew Dunstan

Discussion: /messages/by-id/469b06ed-69de-ba59-c13a-91d2372e52a9@dunslane.net

However, there is no user documentation of this hook, so it didn't seem
like something to add to the release notes.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

Hi,

Thanks for making the release notes. I found the release note of
PG16 beta2 mentions a reverted following feature.

```
<!--
Author: Jeff Davis <jdavis@postgresql.org>
2023-03-09 [27b62377b] Use ICU by default at initdb time.
-->

<listitem>
<para>
Have initdb use ICU by default if ICU is enabled in the binary (Jeff
Davis)
</para>

<para>
Option --locale-provider=libc can be used to disable ICU.
</para>
</listitem>
```

Unfortunately, the feature is reverted with the commit.
*
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2535c74b1a6190cc42e13f6b6b55d94bff4b7dd6

Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION

On Fri, Jun 30, 2023 at 05:29:17PM +0900, Masahiro Ikeda wrote:

Hi,

Thanks for making the release notes. I found the release note of
PG16 beta2 mentions a reverted following feature.

```
<!--
Author: Jeff Davis <jdavis@postgresql.org>
2023-03-09 [27b62377b] Use ICU by default at initdb time.
-->

<listitem>
<para>
Have initdb use ICU by default if ICU is enabled in the binary (Jeff Davis)
</para>

<para>
Option --locale-provider=libc can be used to disable ICU.
</para>
</listitem>
```

Unfortunately, the feature is reverted with the commit.
* https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2535c74b1a6190cc42e13f6b6b55d94bff4b7dd6

Oh, I didn't notice the revert --- item removed.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Thu, May 18, 2023 at 04:49:47PM -0400, Bruce Momjian wrote:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

Sawada-san has mentioned on twitter that fdd8937 is not mentioned in
the release notes, and it seems to me that he is right. This is
described as a bug in the commit log, but it did not get backpatched
because of the lack of complaints. Also, because we've removed
support for anything older than Windows 10 in PG16, this change very
easy to do.
--
Michael

On Tue, Jul 4, 2023 at 03:31:05PM +0900, Michael Paquier wrote:

On Thu, May 18, 2023 at 04:49:47PM -0400, Bruce Momjian wrote:

I have completed the first draft of the PG 16 release notes. You can
see the output here:

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

Sawada-san has mentioned on twitter that fdd8937 is not mentioned in
the release notes, and it seems to me that he is right. This is
described as a bug in the commit log, but it did not get backpatched
because of the lack of complaints. Also, because we've removed
support for anything older than Windows 10 in PG16, this change very
easy to do.

I did review this and wasn't sure exactly what I would describe. It is
saying huge pages will now work on some versions of Windows 10 but
didn't before?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Thu, 2023-05-18 at 16:49 -0400, Bruce Momjian wrote:

I have completed the first draft of the PG 16 release notes.

The release notes say:

- Prevent \df+ from showing function source code (Isaac Morland)

Function bodies are more easily viewed with \ev and \ef.

That should be \sf, not \ev or \ef, right?

Yours,
Laurenz Albe

On Thu, 2023-05-18 at 16:49 -0400, Bruce Momjian wrote:

I have completed the first draft of the PG 16 release notes.

The release notes still have:

- Have initdb use ICU by default if ICU is enabled in the binary (Jeff Davis)

Option --locale-provider=libc can be used to disable ICU.

But this was reverted in 2535c74b1a6190cc42e13f6b6b55d94bff4b7dd6.

Yours,
Laurenz Albe

Op 7/4/23 om 23:32 schreef Bruce Momjian:

https://momjian.us/pgsql_docs/release-16.html

I noticed these:

'new new RULES' should be
'new RULES'

'Perform apply of large transactions' should be
'Performs apply of large transactions'
(I think)

'SQL JSON paths' should be
'SQL/JSON paths'

Erik Rijkers

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

Create a predefined role and grantable privilege with permission to perform maintenance operations (Nathan Bossart)
The predefined role is is called pg_maintain.

this feature was also reverted.
https://git.postgresql.org/cgit/postgresql.git/commit/?id=151c22deee66a3390ca9a1c3675e29de54ae73fc

Please consider to add item to the psql section:

Add psql \drg command to display role grants and remove the "Member of"
column from \du & \dg altogether (d65ddaca)

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com

On Tue, Jul 04, 2023 at 05:32:07PM -0400, Bruce Momjian wrote:

On Tue, Jul 4, 2023 at 03:31:05PM +0900, Michael Paquier wrote:

On Thu, May 18, 2023 at 04:49:47PM -0400, Bruce Momjian wrote:
Sawada-san has mentioned on twitter that fdd8937 is not mentioned in
the release notes, and it seems to me that he is right. This is
described as a bug in the commit log, but it did not get backpatched
because of the lack of complaints. Also, because we've removed
support for anything older than Windows 10 in PG16, this change very
easy to do.

I did review this and wasn't sure exactly what I would describe. It is
saying huge pages will now work on some versions of Windows 10 but
didn't before?

Windows 10 has always used a forced automated rolling upgrade process,
so there are not many versions older than 1703, I suppose. I don't
know if large pages were working before 1703 where
FILE_MAP_LARGE_PAGES has been introduced, and I have never been able
to test that. Honestly, I don't think that we need to be picky about
the version mentioned, as per the forced upgrade process done by
Microsoft.

So, my preference would be to keep it simple and add an item like "Fix
huge pages on Windows 10 and newer versions", with as potential
subnote "The backend sets a flag named FILE_MAP_LARGE_PAGES to allow
huge pages", though this is not really mandatory to go down to this
level of internals, either.
--
Michael

On Thu, May 18, 2023 at 04:49:47PM -0400, Bruce Momjian wrote:

https://momjian.us/pgsql_docs/release-16.html

<!--
Author: Robert Haas <rhaas@postgresql.org>
2023-01-10 [cf5eb37c5] Restrict the privileges of CREATEROLE users.
-->

<listitem>
<para>
Restrict the privileges of CREATEROLE roles (Robert Haas)
</para>

<para>
Previously roles with CREATEROLE privileges could change many aspects of any non-superuser role. Such changes, including adding members, now require the role requesting the change to have ADMIN OPTION
permission.
</para>
</listitem>

<!--
Author: Robert Haas <rhaas@postgresql.org>
2023-01-24 [f1358ca52] Adjust interaction of CREATEROLE with role properties.
-->

<listitem>
<para>
Improve logic of CREATEROLE roles ability to control other roles (Robert Haas)
</para>

<para>
For example, they can change the CREATEDB, REPLICATION, and BYPASSRLS properties only if they also have those permissions.
</para>
</listitem>

CREATEROLE is a radically different feature in v16. In v15-, it was an
almost-superuser. In v16, informally speaking, it can create and administer
its own collection of roles, but it can't administer roles outside its
collection or grant memberships or permissions not offered to itself. Hence,
let's move these two into the incompatibilities section. Let's also merge
them, since f1358ca52 is just doing to clauses like CREATEDB what cf5eb37c5
did to role memberships.

<!--
Author: Robert Haas <rhaas@postgresql.org>
2022-08-25 [e3ce2de09] Allow grant-level control of role inheritance behavior.
-->

<listitem>
<para>
Allow GRANT to control role inheritance behavior (Robert Haas)
</para>

<para>
By default, role inheritance is controlled by the inheritance status of the member role. The new GRANT clauses WITH INHERIT and WITH ADMIN can now override this.
</para>
</listitem>

<!--
Author: Robert Haas <rhaas@postgresql.org>
2023-01-10 [e5b8a4c09] Add new GUC createrole_self_grant.
Author: Daniel Gustafsson <dgustafsson@postgresql.org>
2023-02-22 [e00bc6c92] doc: Add default value of createrole_self_grant
-->

<listitem>
<para>
Allow roles that create other roles to automatically inherit the new role's rights or SET ROLE to the new role (Robert Haas, Shi Yu)
</para>

<para>
This is controlled by server variable createrole_self_grant.
</para>
</listitem>

Similarly, v16 radically changes the CREATE ROLE ... WITH INHERIT clause. The
clause used to "change the behavior of already-existing grants." Let's merge
these two and move the combination to the incompatibilities section.

Remove libpq support for SCM credential authentication (Michael Paquier)

Since the point of removing it is the deep unlikelihood of anyone using it, I
wouldn't list this in "incompatibilities".

Deprecate createuser option --role (Nathan Bossart)

This is indeed a deprecation, not a removal. By the definition of
"deprecate", it's not an incompatibility.

On Fri, Jul 14, 2023 at 08:16:38PM +0200, Laurenz Albe wrote:

On Thu, 2023-05-18 at 16:49 -0400, Bruce Momjian wrote:

I have completed the first draft of the PG 16 release notes.

The release notes say:

- Prevent \df+ from showing function source code (Isaac Morland)

Function bodies are more easily viewed with \ev and \ef.

That should be \sf, not \ev or \ef, right?

Agreed, fixed. I am not sure why I put \ev and \ef there.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Fri, Jul 14, 2023 at 08:20:59PM +0200, Laurenz Albe wrote:

On Thu, 2023-05-18 at 16:49 -0400, Bruce Momjian wrote:

I have completed the first draft of the PG 16 release notes.

The release notes still have:

- Have initdb use ICU by default if ICU is enabled in the binary (Jeff Davis)

Option --locale-provider=libc can be used to disable ICU.

But this was reverted in 2535c74b1a6190cc42e13f6b6b55d94bff4b7dd6.

FYI, this was corrected in this commit:

commit c729642bd7
Author: Bruce Momjian <bruce@momjian.us>
Date: Fri Jun 30 17:35:47 2023 -0400

doc: PG 16 relnotes, remove "Have initdb use ICU by default"

Item reverted.

Backpatch-through: 16 only

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Fri, Jul 14, 2023 at 09:29:42PM +0200, Erik Rijkers wrote:

Op 7/4/23 om 23:32 schreef Bruce Momjian:

https://momjian.us/pgsql_docs/release-16.html

I noticed these:

'new new RULES' should be
'new RULES'

'Perform apply of large transactions' should be
'Performs apply of large transactions'
(I think)

'SQL JSON paths' should be
'SQL/JSON paths'

Fixed with the attached patch.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Sun, Jul 23, 2023 at 12:45:17PM +0800, jian he wrote:

https://momjian.us/pgsql_docs/release-16.html

I will adjust it to the feedback I receive; that URL will quickly show
all updates.

Create a predefined role and grantable privilege with permission to perform maintenance operations (Nathan Bossart)
The predefined role is is called pg_maintain.

this feature was also reverted.
https://git.postgresql.org/cgit/postgresql.git/commit/?id=151c22deee66a3390ca9a1c3675e29de54ae73fc

Thenks, fixed based on earlier report by Laurenz Albe.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Sun, Jul 23, 2023 at 02:09:17PM +0300, Pavel Luzanov wrote:

Please consider to add item to the psql section:

Add psql \drg command to display role grants and remove the "Member of"
column from \du & \dg altogether (d65ddaca)

The release notes are only current as of 2023-06-26 and I will consider
this when I updated them next week, thanks.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Sun, Jul 23, 2023 at 08:19:55PM +0900, Michael Paquier wrote:

On Tue, Jul 04, 2023 at 05:32:07PM -0400, Bruce Momjian wrote:

On Tue, Jul 4, 2023 at 03:31:05PM +0900, Michael Paquier wrote:

On Thu, May 18, 2023 at 04:49:47PM -0400, Bruce Momjian wrote:
Sawada-san has mentioned on twitter that fdd8937 is not mentioned in
the release notes, and it seems to me that he is right. This is
described as a bug in the commit log, but it did not get backpatched
because of the lack of complaints. Also, because we've removed
support for anything older than Windows 10 in PG16, this change very
easy to do.

I did review this and wasn't sure exactly what I would describe. It is
saying huge pages will now work on some versions of Windows 10 but
didn't before?

Windows 10 has always used a forced automated rolling upgrade process,
so there are not many versions older than 1703, I suppose. I don't
know if large pages were working before 1703 where
FILE_MAP_LARGE_PAGES has been introduced, and I have never been able
to test that. Honestly, I don't think that we need to be picky about
the version mentioned, as per the forced upgrade process done by
Microsoft.

So, my preference would be to keep it simple and add an item like "Fix
huge pages on Windows 10 and newer versions", with as potential
subnote "The backend sets a flag named FILE_MAP_LARGE_PAGES to allow
huge pages", though this is not really mandatory to go down to this
level of internals, either.

That is very helpful. I added this to the release notes Server
Configuration section:

<!--
Author: Michael Paquier <michael@paquier.xyz>
2022-09-17 [fdd8937c0] Fix huge_pages on Windows
-->

<listitem>
<para>
Allow huge pages to work on newer versions of Windows 10 (Thomas Munro)
</para>

<para>
This adds the special handling required to enable huge pages on newer
versions of Windows 10.
</para>
</listitem>

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Sat, Aug 5, 2023 at 04:08:47PM -0700, Noah Misch wrote:

On Thu, May 18, 2023 at 04:49:47PM -0400, Bruce Momjian wrote:

https://momjian.us/pgsql_docs/release-16.html

<!--
Author: Robert Haas <rhaas@postgresql.org>
2023-01-10 [cf5eb37c5] Restrict the privileges of CREATEROLE users.
-->

<listitem>
<para>
Restrict the privileges of CREATEROLE roles (Robert Haas)
</para>

<para>
Previously roles with CREATEROLE privileges could change many aspects of any non-superuser role. Such changes, including adding members, now require the role requesting the change to have ADMIN OPTION
permission.
</para>
</listitem>

<!--
Author: Robert Haas <rhaas@postgresql.org>
2023-01-24 [f1358ca52] Adjust interaction of CREATEROLE with role properties.
-->

<listitem>
<para>
Improve logic of CREATEROLE roles ability to control other roles (Robert Haas)
</para>

<para>
For example, they can change the CREATEDB, REPLICATION, and BYPASSRLS properties only if they also have those permissions.
</para>
</listitem>

CREATEROLE is a radically different feature in v16. In v15-, it was an
almost-superuser. In v16, informally speaking, it can create and administer
its own collection of roles, but it can't administer roles outside its
collection or grant memberships or permissions not offered to itself. Hence,
let's move these two into the incompatibilities section. Let's also merge
them, since f1358ca52 is just doing to clauses like CREATEDB what cf5eb37c5
did to role memberships.

Good point. I have adjusted this item with the attached patch.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Wed, Aug 09, 2023 at 05:45:27PM -0400, Bruce Momjian wrote:

That is very helpful. I added this to the release notes Server
Configuration section:

<listitem>
<para>
Allow huge pages to work on newer versions of Windows 10 (Thomas Munro)
</para>

<para>
This adds the special handling required to enable huge pages on newer
versions of Windows 10.
</para>
</listitem>

Looks good to me, thanks for updating the notes!
--
Michael

On Sat, Aug 5, 2023 at 04:08:47PM -0700, Noah Misch wrote:

Author: Robert Haas <rhaas@postgresql.org>
2022-08-25 [e3ce2de09] Allow grant-level control of role inheritance behavior.
-->

<listitem>
<para>
Allow GRANT to control role inheritance behavior (Robert Haas)
</para>

<para>
By default, role inheritance is controlled by the inheritance status of the member role. The new GRANT clauses WITH INHERIT and WITH ADMIN can now override this.
</para>
</listitem>

<!--
Author: Robert Haas <rhaas@postgresql.org>
2023-01-10 [e5b8a4c09] Add new GUC createrole_self_grant.
Author: Daniel Gustafsson <dgustafsson@postgresql.org>
2023-02-22 [e00bc6c92] doc: Add default value of createrole_self_grant
-->

<listitem>
<para>
Allow roles that create other roles to automatically inherit the new role's rights or SET ROLE to the new role (Robert Haas, Shi Yu)
</para>

<para>
This is controlled by server variable createrole_self_grant.
</para>
</listitem>

Similarly, v16 radically changes the CREATE ROLE ... WITH INHERIT clause. The
clause used to "change the behavior of already-existing grants." Let's merge
these two and move the combination to the incompatibilities section.

I need help with this. I don't understand how they can be combined, and
I don't understand the incompatibility text in commit e3ce2de09d:

If a GRANT does not specify WITH INHERIT, the behavior based on
whether the member role is marked INHERIT or NOINHERIT. This means
that if all roles are marked INHERIT or NOINHERIT before any role
grants are performed, the behavior is identical to what we had before;
otherwise, it's different, because ALTER ROLE [NO]INHERIT now only
changes the default behavior of future grants, and has no effect on
existing ones.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Sat, Aug 5, 2023 at 04:08:47PM -0700, Noah Misch wrote:

Remove libpq support for SCM credential authentication (Michael Paquier)

Since the point of removing it is the deep unlikelihood of anyone using it, I
wouldn't list this in "incompatibilities".

I moved this to the Source Code section.

Deprecate createuser option --role (Nathan Bossart)

This is indeed a deprecation, not a removal. By the definition of
"deprecate", it's not an incompatibility.

I moved this to the Server Applications section.

Thanks.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

FYI, the current PG 16 release notes are available at:

https://momjian.us/pgsql_docs/release-16.html

I plan to add markup next week. I am sorry I was away most of July so
could not update this until now.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On 09.08.2023 21:06, Bruce Momjian wrote:

On Sun, Jul 23, 2023 at 02:09:17PM +0300, Pavel Luzanov wrote:

Please consider to add item to the psql section:

Add psql \drg command to display role grants and remove the "Member of"
column from \du & \dg altogether (d65ddaca)

The release notes are only current as of 2023-06-26 and I will consider
this when I updated them next week, thanks.

This item is a part of Beta 3 scheduled for August 10, 2023 (today). [1]
It might be worth updating the release notes before the release.
But I'm not familiar with the release process in detail, so I could be
wrong.

1.
/messages/by-id/93c00ac3-08b3-33ba-5d77-6ceb6ab20254@postgresql.org

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com

On Thu, Aug 10, 2023 at 07:56:12AM +0300, Pavel Luzanov wrote:

On 09.08.2023 21:06, Bruce Momjian wrote:

On Sun, Jul 23, 2023 at 02:09:17PM +0300, Pavel Luzanov wrote:

Please consider to add item to the psql section:

Add psql \drg command to display role grants and remove the "Member of"
column from \du & \dg altogether (d65ddaca)

The release notes are only current as of 2023-06-26 and I will consider
this when I updated them next week, thanks.

This item is a part of Beta 3 scheduled for August 10, 2023 (today). [1]
It might be worth updating the release notes before the release.
But I'm not familiar with the release process in detail, so I could be
wrong.

1. /messages/by-id/93c00ac3-08b3-33ba-5d77-6ceb6ab20254@postgresql.org

The next text is:

<listitem>
<para>
Allow psql's access privilege commands to show system objects (Nathan Bossart, Pavel Luzanov)
</para>

<para>
--> The options are \dpS, \zS, and \drg.
</para>
</listitem>

The current release notes are at:

https://momjian.us/pgsql_docs/release-16.html

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

You can view the Postgres 16 release notes, with markup and links to our
docs, here:

https://momjian.us/pgsql_docs/release-16.html

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Wed, Aug 9, 2023 at 08:35:21PM -0400, Bruce Momjian wrote:

On Sat, Aug 5, 2023 at 04:08:47PM -0700, Noah Misch wrote:

Author: Robert Haas <rhaas@postgresql.org>
2022-08-25 [e3ce2de09] Allow grant-level control of role inheritance behavior.
-->

<listitem>
<para>
Allow GRANT to control role inheritance behavior (Robert Haas)
</para>

<para>
By default, role inheritance is controlled by the inheritance status of the member role. The new GRANT clauses WITH INHERIT and WITH ADMIN can now override this.
</para>
</listitem>

<!--
Author: Robert Haas <rhaas@postgresql.org>
2023-01-10 [e5b8a4c09] Add new GUC createrole_self_grant.
Author: Daniel Gustafsson <dgustafsson@postgresql.org>
2023-02-22 [e00bc6c92] doc: Add default value of createrole_self_grant
-->

<listitem>
<para>
Allow roles that create other roles to automatically inherit the new role's rights or SET ROLE to the new role (Robert Haas, Shi Yu)
</para>

<para>
This is controlled by server variable createrole_self_grant.
</para>
</listitem>

Similarly, v16 radically changes the CREATE ROLE ... WITH INHERIT clause. The
clause used to "change the behavior of already-existing grants." Let's merge
these two and move the combination to the incompatibilities section.

I need help with this. I don't understand how they can be combined, and
I don't understand the incompatibility text in commit e3ce2de09d:

If a GRANT does not specify WITH INHERIT, the behavior based on
whether the member role is marked INHERIT or NOINHERIT. This means
that if all roles are marked INHERIT or NOINHERIT before any role
grants are performed, the behavior is identical to what we had before;
otherwise, it's different, because ALTER ROLE [NO]INHERIT now only
changes the default behavior of future grants, and has no effect on
existing ones.

I am waiting for an answer to this question, or can I assume the release
notes are acceptable?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On 17.08.2023 05:36, Bruce Momjian wrote:

On Wed, Aug 9, 2023 at 08:35:21PM -0400, Bruce Momjian wrote:

On Sat, Aug 5, 2023 at 04:08:47PM -0700, Noah Misch wrote:

Author: Robert Haas <rhaas@postgresql.org>
2022-08-25 [e3ce2de09] Allow grant-level control of role inheritance behavior.
-->

<listitem>
<para>
Allow GRANT to control role inheritance behavior (Robert Haas)
</para>

<para>
By default, role inheritance is controlled by the inheritance status of the member role. The new GRANT clauses WITH INHERIT and WITH ADMIN can now override this.
</para>
</listitem>

<!--
Author: Robert Haas <rhaas@postgresql.org>
2023-01-10 [e5b8a4c09] Add new GUC createrole_self_grant.
Author: Daniel Gustafsson <dgustafsson@postgresql.org>
2023-02-22 [e00bc6c92] doc: Add default value of createrole_self_grant
-->

<listitem>
<para>
Allow roles that create other roles to automatically inherit the new role's rights or SET ROLE to the new role (Robert Haas, Shi Yu)
</para>

<para>
This is controlled by server variable createrole_self_grant.
</para>
</listitem>

Similarly, v16 radically changes the CREATE ROLE ... WITH INHERIT clause. The
clause used to "change the behavior of already-existing grants." Let's merge
these two and move the combination to the incompatibilities section.

I need help with this. I don't understand how they can be combined, and
I don't understand the incompatibility text in commit e3ce2de09d:

If a GRANT does not specify WITH INHERIT, the behavior based on
whether the member role is marked INHERIT or NOINHERIT. This means
that if all roles are marked INHERIT or NOINHERIT before any role
grants are performed, the behavior is identical to what we had before;
otherwise, it's different, because ALTER ROLE [NO]INHERIT now only
changes the default behavior of future grants, and has no effect on
existing ones.

I am waiting for an answer to this question, or can I assume the release
notes are acceptable?

I can try to explain how I understand it myself.

In v15 and early, inheritance of granted to role privileges depends on
INHERIT attribute of a role:

create user alice;
grant pg_read_all_settings to alice;

By default privileges inherited:
\c - alice
show data_directory;
       data_directory
-----------------------------
 /var/lib/postgresql/15/main
(1 row)

After disabling the INHERIT attribute, privileges are not inherited:

\c - postgres
alter role alice noinherit;

\c - alice
show data_directory;
ERROR:  must be superuser or have privileges of pg_read_all_settings to
examine "data_directory"

In v16 changing INHERIT attribute on alice role doesn't change
inheritance behavior of already granted roles.
If we repeat the example, Alice still inherits pg_read_all_settings
privileges after disabling the INHERIT attribute for the role.

Information for making decisions about role inheritance has been moved
from the role attribute to GRANT role TO role [WITH INHERIT|NOINHERIT]
command and can be viewed by the new \drg command:

\drg
                    List of role grants
 Role name |      Member of       |   Options    | Grantor
-----------+----------------------+--------------+----------
 alice     | pg_read_all_settings | INHERIT, SET | postgres
(1 row)

Changing the INHERIT attribute for a role now will affect (as the
default value) only future GRANT commands without an INHERIT clause.

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com

On Wed, Aug 16, 2023 at 10:36:05PM -0400, Bruce Momjian wrote:

You can view the Postgres 16 release notes, with markup and links to our
docs, here:

https://momjian.us/pgsql_docs/release-16.html

FYI, thanks to this patch:

commit 78ee60ed84
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Mon Jan 9 15:08:24 2023 -0500

Doc: add XML ID attributes to <sectN> and <varlistentry> tags.

This doesn't have any external effect at the moment, but it
will allow adding useful link-discoverability features later.

Brar Piening, reviewed by Karl Pinc.

Discussion: /messages/by-id/CAB8KJ=jpuQU9QJe4+RgWENrK5g9jhoysMw2nvTN_esoOU0=a_w@mail.gmail.com

I was able to add more links to the docs, and the links were more
precise. I used to be frustrated I couldn't find nearby links to
content, but I had no such troubles this year. I think the additional
and more precise links will help people digest the release notes more
efficiently.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

I posted to pgsql-docs first, but was kindly redirected here by Jonathan:

The release notes for Postgres 16 says here:
https://www.postgresql.org/docs/16/release-16.html#RELEASE-16-PERFORMANCE

Same as here:
https://momjian.us/pgsql_docs/release-16.html#RELEASE-16-PERFORMANCE

Allow window functions to use ROWS mode internally when RANGE mode is
specified but unnecessary (David Rowley)

But the improvement (fix to some degree) also applies to the much more
common case where no mode has been specified, RANGE unfortunately being the
default.
That includes the most common use case "row_number() OVER (ORDER BY col)",
where RANGE mode should not be applied to begin with, according to SQL
specs. This is what made me investigate, test and eventually propose a fix
in the first place. See:

/messages/by-id/CAGHENJ7LBBszxS+SkWWFVnBmOT2oVsBhDMB1DFrgerCeYa_DyA@mail.gmail.com
/messages/by-id/CAApHDvohAKEtTXxq7Pc-ic2dKT8oZfbRKeEJP64M0B6+S88z+A@mail.gmail.com

Also, I was hoping to be mentioned in the release note for working this out:

Allow window functions to use the faster ROWS mode internally when
RANGE mode is specified or would be default, but unnecessary (David Rowley,
Erwin Brandstetter)

Thanks,
Erwin

On Sat, 19 Aug 2023 at 04:02, Bruce Momjian <bruce@momjian.us> wrote:

On Thu, Aug 17, 2023 at 08:37:28AM +0300, Pavel Luzanov wrote:

On 17.08.2023 05:36, Bruce Momjian wrote:

On Wed, Aug 9, 2023 at 08:35:21PM -0400, Bruce Momjian wrote:

On Sat, Aug 5, 2023 at 04:08:47PM -0700, Noah Misch wrote:

Author: Robert Haas <rhaas@postgresql.org>
2022-08-25 [e3ce2de09] Allow grant-level control of role inheritance behavior.
-->

<listitem>
<para>
Allow GRANT to control role inheritance behavior (Robert Haas)
</para>

<para>
By default, role inheritance is controlled by the inheritance status of the member role. The new GRANT clauses WITH INHERIT and WITH ADMIN can now override this.
</para>
</listitem>

<!--
Author: Robert Haas <rhaas@postgresql.org>
2023-01-10 [e5b8a4c09] Add new GUC createrole_self_grant.
Author: Daniel Gustafsson <dgustafsson@postgresql.org>
2023-02-22 [e00bc6c92] doc: Add default value of createrole_self_grant
-->

<listitem>
<para>
Allow roles that create other roles to automatically inherit the new role's rights or SET ROLE to the new role (Robert Haas, Shi Yu)
</para>

<para>
This is controlled by server variable createrole_self_grant.
</para>
</listitem>

Similarly, v16 radically changes the CREATE ROLE ... WITH INHERIT clause. The
clause used to "change the behavior of already-existing grants." Let's merge
these two and move the combination to the incompatibilities section.

I need help with this. I don't understand how they can be combined, and
I don't understand the incompatibility text in commit e3ce2de09d:

If a GRANT does not specify WITH INHERIT, the behavior based on
whether the member role is marked INHERIT or NOINHERIT. This means
that if all roles are marked INHERIT or NOINHERIT before any role
grants are performed, the behavior is identical to what we had before;
otherwise, it's different, because ALTER ROLE [NO]INHERIT now only
changes the default behavior of future grants, and has no effect on
existing ones.

I am waiting for an answer to this question, or can I assume the release
notes are acceptable?

I can try to explain how I understand it myself.

In v15 and early, inheritance of granted to role privileges depends on
INHERIT attribute of a role:

create user alice;
grant pg_read_all_settings to alice;

By default privileges inherited:
\c - alice
show data_directory;
       data_directory
-----------------------------
 /var/lib/postgresql/15/main
(1 row)

After disabling the INHERIT attribute, privileges are not inherited:

\c - postgres
alter role alice noinherit;

\c - alice
show data_directory;
ERROR:  must be superuser or have privileges of pg_read_all_settings to
examine "data_directory"

In v16 changing INHERIT attribute on alice role doesn't change inheritance
behavior of already granted roles.
If we repeat the example, Alice still inherits pg_read_all_settings
privileges after disabling the INHERIT attribute for the role.

Information for making decisions about role inheritance has been moved from
the role attribute to GRANT role TO role [WITH INHERIT|NOINHERIT] command
and can be viewed by the new \drg command:

\drg
                    List of role grants
 Role name |      Member of       |   Options    | Grantor
-----------+----------------------+--------------+----------
 alice     | pg_read_all_settings | INHERIT, SET | postgres
(1 row)

Changing the INHERIT attribute for a role now will affect (as the default
value) only future GRANT commands without an INHERIT clause.

I was able to create this simple example to illustrate it:

CREATE ROLE a1;
CREATE ROLE a2;
CREATE ROLE a3;
CREATE ROLE a4;
CREATE ROLE b INHERIT;

GRANT a1 TO b WITH INHERIT TRUE;
GRANT a2 TO b WITH INHERIT FALSE;

GRANT a3 TO b;
ALTER USER b NOINHERIT;
GRANT a4 TO b;

\drg
List of role grants
Role name | Member of | Options | Grantor
-----------+-----------+--------------+----------
b | a1 | INHERIT, SET | postgres
b | a2 | SET | postgres
b | a3 | INHERIT, SET | postgres
b | a4 | SET | postgres

I will work on the relase notes adjustments for this and reply in a few
days.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Sat, Aug 19, 2023 at 12:59:47PM -0400, Bruce Momjian wrote:

On Thu, Aug 17, 2023 at 08:37:28AM +0300, Pavel Luzanov wrote:

I can try to explain how I understand it myself.

In v15 and early, inheritance of granted to role privileges depends on
INHERIT attribute of a role:

create user alice;
grant pg_read_all_settings to alice;

By default privileges inherited:
\c - alice
show data_directory;
       data_directory
-----------------------------
 /var/lib/postgresql/15/main
(1 row)

After disabling the INHERIT attribute, privileges are not inherited:

\c - postgres
alter role alice noinherit;

\c - alice
show data_directory;
ERROR:  must be superuser or have privileges of pg_read_all_settings to
examine "data_directory"

In v16 changing INHERIT attribute on alice role doesn't change inheritance
behavior of already granted roles.
If we repeat the example, Alice still inherits pg_read_all_settings
privileges after disabling the INHERIT attribute for the role.

Information for making decisions about role inheritance has been moved from
the role attribute to GRANT role TO role [WITH INHERIT|NOINHERIT] command
and can be viewed by the new \drg command:

\drg
                    List of role grants
 Role name |      Member of       |   Options    | Grantor
-----------+----------------------+--------------+----------
 alice     | pg_read_all_settings | INHERIT, SET | postgres
(1 row)

Changing the INHERIT attribute for a role now will affect (as the default
value) only future GRANT commands without an INHERIT clause.

I was able to create this simple example to illustrate it:

CREATE ROLE a1;
CREATE ROLE a2;
CREATE ROLE a3;
CREATE ROLE a4;
CREATE ROLE b INHERIT;

GRANT a1 TO b WITH INHERIT TRUE;
GRANT a2 TO b WITH INHERIT FALSE;

GRANT a3 TO b;
ALTER USER b NOINHERIT;
GRANT a4 TO b;

\drg
List of role grants
Role name | Member of | Options | Grantor
-----------+-----------+--------------+----------
b | a1 | INHERIT, SET | postgres
b | a2 | SET | postgres
b | a3 | INHERIT, SET | postgres
b | a4 | SET | postgres

I will work on the relase notes adjustments for this and reply in a few
days.

Attached is an applied patch that moves the inherit item into
incompatibilities. clarifies it, and splits out the ADMIN syntax item.

Please let me know if I need any other changes. Thanks.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Sat, Aug 19, 2023 at 04:24:48AM +0200, Erwin Brandstetter wrote:

I posted to pgsql-docs first, but was kindly redirected here by Jonathan:

The release notes for Postgres 16 says here:
https://www.postgresql.org/docs/16/release-16.html#RELEASE-16-PERFORMANCE

Same as here:
https://momjian.us/pgsql_docs/release-16.html#RELEASE-16-PERFORMANCE

    Allow window functions to use ROWS mode internally when RANGE mode is
specified but unnecessary (David Rowley)

Yes, I didn't like "specified" myself but never returned to improve it.
I am now using:

Allow window functions to use the faster <link
linkend="syntax-window-functions"><literal>ROWS</literal></link> mode
internally when <literal>RANGE</literal> mode is active but unnecessary
------
(David Rowley)

Can that be improved?

But the improvement (fix to some degree) also applies to the much more common
case where no mode has been specified, RANGE unfortunately being the default.
That includes the most common use case "row_number() OVER (ORDER BY col)",
where RANGE mode should not be applied to begin with, according to SQL specs.
This is what made me investigate, test and eventually propose a fix in the
first place. See:

Yes, very true.

Also, I was hoping to be mentioned in the release note for working this out:

    Allow window functions to use the faster ROWS mode internally when RANGE
mode is specified or would be default, but unnecessary (David Rowley, Erwin
Brandstetter)

Uh, I have CC'ed David Rowley because that is unclear based on the
commit message. I don't normally mention reviewers.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Tue, 22 Aug 2023 at 10:08, Bruce Momjian <bruce@momjian.us> wrote:

On Sat, Aug 19, 2023 at 04:24:48AM +0200, Erwin Brandstetter wrote:

I posted to pgsql-docs first, but was kindly redirected here by Jonathan:

The release notes for Postgres 16 says here:
https://www.postgresql.org/docs/16/release-16.html#RELEASE-16-PERFORMANCE

Same as here:
https://momjian.us/pgsql_docs/release-16.html#RELEASE-16-PERFORMANCE

Allow window functions to use ROWS mode internally when RANGE mode is
specified but unnecessary (David Rowley)

Yes, I didn't like "specified" myself but never returned to improve it.
I am now using:

Allow window functions to use the faster <link
linkend="syntax-window-functions"><literal>ROWS</literal></link> mode
internally when <literal>RANGE</literal> mode is active but unnecessary
------
(David Rowley)

Can that be improved?

Looks good to me.

Also, I was hoping to be mentioned in the release note for working this out:

Allow window functions to use the faster ROWS mode internally when RANGE
mode is specified or would be default, but unnecessary (David Rowley, Erwin
Brandstetter)

Uh, I have CC'ed David Rowley because that is unclear based on the
commit message. I don't normally mention reviewers.

I confirm that Erwin reported in [1]/messages/by-id/CAGHENJ7LBBszxS+SkWWFVnBmOT2oVsBhDMB1DFrgerCeYa_DyA@mail.gmail.com that row_number() is not affected
by the ROWS/RANGE option and that ROWS performs better due to the
executor having less work to do. I am the author of the patch which
implemented that plus a few other window functions that also can
benefit from the same optimisation. Based on this, I don't see any
problems with the credits for this item as they are currently in the
release notes.

David

[1]: /messages/by-id/CAGHENJ7LBBszxS+SkWWFVnBmOT2oVsBhDMB1DFrgerCeYa_DyA@mail.gmail.com

On 22.08.2023 00:58, Bruce Momjian wrote:

Attached is an applied patch that moves the inherit item into
incompatibilities. clarifies it, and splits out the ADMIN syntax item.

The role's default inheritance behavior can be overridden with the

new <command>GRANT ... WITH INHERIT</command> clause.

The only question about "can be". Why not "will be"? The inheritance
behavior will be changed anyway.

Please let me know if I need any other changes. Thanks.

* Allow psql's access privilege commands to show system objects (Nathan
Bossart, Pavel Luzanov)
    The options are \dpS, \zS, and \drg.

I think that this description correct only for the \dpS and \zS commands.
(By the way, unfortunately after reverting MAINTAIN privilege this
commands are not much useful in v16.)

But the \drg command is a different thing. This is a full featured
replacement for "Member of" column of the \du, \dg commands.
It shows not only members, but granted options (admin, inherit, set) and
grantor.
This is important information for membership usage and administration.
IMO, removing the "Member of" column from the \du & \dg commands also
requires attention in release notes.

So, I suggest new item in the psql section for \drg. Something like this:

* Add psql \drg command to display role grants and remove the "Member
of" column from \du & \dg altogether.

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com

On Tue, Aug 22, 2023 at 10:02:16AM +0300, Pavel Luzanov wrote:

On 22.08.2023 00:58, Bruce Momjian wrote:

Attached is an applied patch that moves the inherit item into
incompatibilities. clarifies it, and splits out the ADMIN syntax item.

The role's default inheritance behavior can be overridden with the new

<command>GRANT ... WITH INHERIT</command> clause.

The only question about "can be". Why not "will be"? The inheritance
behavior will be changed anyway.

I used "can be" to highlight you "can" override it, but don't need to.