Thread: PG 18 release notes draft committed
I have committd the first draft of the PG 18 release notes. The item
count looks strong:
release-7.4: 263
release-8.0: 230
release-8.1: 174
release-8.2: 215
release-8.3: 214
release-8.4: 314
release-9.0: 237
release-9.1: 203
release-9.2: 238
release-9.3: 177
release-9.4: 211
release-9.5: 193
release-9.6: 214
release-10: 189
release-11: 170
release-12: 180
release-13: 178
release-14: 220
release-15: 184
release-16: 206
release-17: 182
release-18: 209
I will continue improving it until beta 1, and until the final release.
I will probably add markup in 1-3 weeks. Let the feedback begin. ;-)
You can see the most current HTML-built version here:
https://momjian.us/pgsql_docs/release-18.html
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
Hi Bruce, On Fri, May 2, 2025 at 11:44 AM Bruce Momjian <bruce@momjian.us> wrote: > I have committd the first draft of the PG 18 release notes. > > I will continue improving it until beta 1, and until the final release. > I will probably add markup in 1-3 weeks. Let the feedback begin. ;-) Thanks as always for working on this. I noticed the release notes currently combine several partition-related commits under one item: +<!-- +Author: Amit Langote <amitlan@postgresql.org> +2025-01-30 [bb3ec16e1] Move PartitionPruneInfo out of plan nodes into PlannedSt +Author: Amit Langote <amitlan@postgresql.org> +2025-01-31 [d47cbf474] Perform runtime initial pruning outside ExecInitNode() +Author: Amit Langote <amitlan@postgresql.org> +2025-02-07 [cbc127917] Track unpruned relids to avoid processing pruned relatio +Author: Amit Langote <amitlan@postgresql.org> +2025-02-20 [525392d57] Don't lock partitions pruned by initial pruning +Author: Amit Langote <amitlan@postgresql.org> +2025-04-04 [88f55bc97] Make derived clause lookup in EquivalenceClass more effi +Author: David Rowley <drowley@postgresql.org> +2025-04-08 [d69d45a5a] Speedup child EquivalenceMember lookup in planner +--> +<listitem> +<para> +Allow partitions to be pruned earlier and quicker, and skipped in more places (Amit Langote, Ashutosh Bapat, Yuya Watari, David Rowley) +<ulink url="&commit_baseurl;bb3ec16e1">§</ulink> +<ulink url="&commit_baseurl;d47cbf474">§</ulink> +<ulink url="&commit_baseurl;cbc127917">§</ulink> +<ulink url="&commit_baseurl;525392d57">§</ulink> +<ulink url="&commit_baseurl;88f55bc97">§</ulink> +<ulink url="&commit_baseurl;d69d45a5a">§</ulink> +</para> +</listitem> But I think these really fall into three separate improvements: 1. Speed up execution of cached plans by deferring locks on partitions subject to pruning (Amit Langote) (bb3ec16e1, d47cbf474, cbc127917, 525392d57) 2. Speed up child EquivalenceMember lookup in planner (Yuya Watari, David Rowley) (d69d45a5a) 3. Speed up derived clause lookup in EquivalenceClass (Ashutosh Bapat) (88f55bc97) Alternatively, 2 and 3 can be combined as: 2. Speed up partition planning by improving EquivalenceClass lookups (Yuya Watari, David Rowley, Ashutosh Bapat) I think 1 should go under Partitioning, which I see is currently missing. Any thoughts, David? Can work on a patch if you'd like. -- Thanks, Amit Langote
On Thu, May 1, 2025 at 7:44 PM Bruce Momjian <bruce@momjian.us> wrote: > I will probably add markup in 1-3 weeks. Let the feedback begin. ;-) Thanks! > <para> > Version 18 contains a number of changes that may affect compatibility > with previous releases. Observe the following incompatibilities: > </para> > [...] > Rename server variable ssl_ecdh_curve to ssl_groups and allow multiple colon-separated ECDH curves to be specified (EricaZhang, Daniel Gustafsson) The previous setting name should continue to function correctly, since it's mapped as an alias, so this can probably be moved into the "standard" config features rather than a compatibility change. --Jacob
On Fri, May 2, 2025 at 08:24:42AM -0700, Jacob Champion wrote: > On Thu, May 1, 2025 at 7:44 PM Bruce Momjian <bruce@momjian.us> wrote: > > I will probably add markup in 1-3 weeks. Let the feedback begin. ;-) > > Thanks! > > > <para> > > Version 18 contains a number of changes that may affect compatibility > > with previous releases. Observe the following incompatibilities: > > </para> > > [...] > > Rename server variable ssl_ecdh_curve to ssl_groups and allow multiple colon-separated ECDH curves to be specified (EricaZhang, Daniel Gustafsson) > > The previous setting name should continue to function correctly, since > it's mapped as an alias, so this can probably be moved into the > "standard" config features rather than a compatibility change. Thanks, done. The commit message didn't indicate the old name would still work, and I didn't review the patch for that. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
On Fri, May 2, 2025 at 01:00:57PM +0900, Amit Langote wrote: > 1. Speed up execution of cached plans by deferring locks on partitions > subject to pruning (Amit Langote) > (bb3ec16e1, d47cbf474, cbc127917, 525392d57) > > 2. Speed up child EquivalenceMember lookup in planner (Yuya Watari, > David Rowley) > (d69d45a5a) > > 3. Speed up derived clause lookup in EquivalenceClass (Ashutosh Bapat) > (88f55bc97) > > Alternatively, 2 and 3 can be combined as: > > 2. Speed up partition planning by improving EquivalenceClass lookups > (Yuya Watari, David Rowley, Ashutosh Bapat) > > I think 1 should go under Partitioning, which I see is currently missing. > > Any thoughts, David? > > Can work on a patch if you'd like. So, a few things. First, these set of commits was in a group of 10 that I added since there have been complaints in the past that optimizer improvements were not listed and therefore patch authors were not given sufficient credit. That means the 209 item count for PG 18 is 10 higher than my normal filtering would produce. Second, looking at the items, these are a case of "X is faster", which we don't normally mention in the release notes. We normally mention "faster" when it is so much faster that use cases which were not possible before might be possible now, so it is recommended to retest. That is what I saw this grouped item as, whereas I don't think the individual items meet that criteria. Also, I didn't see enough partition items to warrant a separate partition section, and we didn't have one in PG 17 either. We could pull all the partition items from the sections they are already in, but they seem more natural in the sections they are in. I don't think most people would know what EquivalenceMember is, and even if they did, would they be able to connect it to an SQL query? Finally, I see the big increases in this release as being the optimizer, monitoring, and constraints. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
On Fri, May 2, 2025 at 12:18:06PM -0400, Bruce Momjian wrote: > Finally, I see the big increases in this release as being the optimizer, > monitoring, and constraints. Also, and I am loving the chapter markers linking to gitweb. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
On Fri, 2 May 2025 at 04:45, Bruce Momjian <bruce@momjian.us> wrote: > > I have committd the first draft of the PG 18 release notes. The item > count looks strong: Thanks for all the work. Some notes: 1. There's currently no mention that protocol version 3.2 was introduced in this release. I'm not sure where/how this should be mentioned, but I definitely think it should be somewhere. It's a pretty major change. One option is to replace/amend the "Make cancel request keys 256 bits" item. Maybe replace that with something like: "Postgres 18 introduces protocol version 3.2. This is the first new protocol version since 3.0, which was introduced in Postgres 7.4. This new protocol version 3.2 allows a server to use longer cancel request keys. When the client advertises support for protocol version 3.2 (or higher) Postgres 18 will use a cancel key size of 256 bits." 2. Obviously biased since it's my contribution, but I think d38bab5 might deserve a mention. 3. The "Add PQtrace() output..." commitlist should also contain 7adec2d5fc
On Sat, May 3, 2025 at 01:46:29AM +0200, Jelte Fennema-Nio wrote: > On Fri, 2 May 2025 at 04:45, Bruce Momjian <bruce@momjian.us> wrote: > > > > I have committd the first draft of the PG 18 release notes. The item > > count looks strong: > > Thanks for all the work. Some notes: > > 1. There's currently no mention that protocol version 3.2 was > introduced in this release. I'm not sure where/how this should be > mentioned, but I definitely think it should be somewhere. It's a > pretty major change. One option is to replace/amend the "Make cancel > request keys 256 bits" item. Maybe replace that with something like: > "Postgres 18 introduces protocol version 3.2. This is the first new > protocol version since 3.0, which was introduced in Postgres 7.4. This > new protocol version 3.2 allows a server to use longer cancel request > keys. When the client advertises support for protocol version 3.2 (or > higher) Postgres 18 will use a cancel key size of 256 bits." Okay, I added a mention next to the libpq version function entries. > 2. Obviously biased since it's my contribution, but I think d38bab5 > might deserve a mention. I disagree. pgbench limits like this are not something we give much detail around error avoidance to in the release notes. > 3. The "Add PQtrace() output..." commitlist should also contain 7adec2d5fc Added. Patch attached. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
Attachment
On Sat, 3 May 2025 at 02:06, Bruce Momjian <bruce@momjian.us> wrote: > > On Sat, May 3, 2025 at 01:46:29AM +0200, Jelte Fennema-Nio wrote: > > On Fri, 2 May 2025 at 04:45, Bruce Momjian <bruce@momjian.us> wrote: > > > > > > I have committd the first draft of the PG 18 release notes. The item > > > count looks strong: > > > > Thanks for all the work. Some notes: > > > > 1. There's currently no mention that protocol version 3.2 was > > introduced in this release. I'm not sure where/how this should be > > mentioned, but I definitely think it should be somewhere. It's a > > pretty major change. One option is to replace/amend the "Make cancel > > request keys 256 bits" item. Maybe replace that with something like: > > "Postgres 18 introduces protocol version 3.2. This is the first new > > protocol version since 3.0, which was introduced in Postgres 7.4. This > > new protocol version 3.2 allows a server to use longer cancel request > > keys. When the client advertises support for protocol version 3.2 (or > > higher) Postgres 18 will use a cancel key size of 256 bits." > > Okay, I added a mention next to the libpq version function entries. I think it should be mentioned in the server section. How about we replace: "This is enabled when the libpq client and server are Postgres 18 or later. ", with the following: This is only enabled when the client supports the wire protocol version 3.2 (or up), Wire protocol version 3.2 is introduced in this release. For libpq based clients that means that libpq needs to be of version 18 or higher. That also makes it clear that for non-libpq based clients people should check if their client supports it.
On Fri, May 2, 2025 at 10:44 AM Bruce Momjian <bruce@momjian.us> wrote: > > I have committd the first draft of the PG 18 release notes. The item > count looks strong: > release-17: 182 > release-18: 209 > > I will continue improving it until beta 1, and until the final release. > I will probably add markup in 1-3 weeks. Let the feedback begin. ;-) > > You can see the most current HTML-built version here: > > https://momjian.us/pgsql_docs/release-18.html > seems you missed this ([1]): Detect redundant GROUP BY columns using UNIQUE indexes d4c3a156c added support that when the GROUP BY contained all of the columns belonging to a relation's PRIMARY KEY, all other columns belonging to that relation would be removed from the GROUP BY clause. That's possible because all other columns are functionally dependent on the PRIMARY KEY and those columns alone ensure the groups are distinct. [1] https://git.postgresql.org/cgit/postgresql.git/commit/?id=bd10ec529796a13670645e6acd640c6f290df020
On Sat, May 3, 2025 at 01:16:24PM +0200, Jelte Fennema-Nio wrote: > On Sat, 3 May 2025 at 02:06, Bruce Momjian <bruce@momjian.us> wrote: > > > > On Sat, May 3, 2025 at 01:46:29AM +0200, Jelte Fennema-Nio wrote: > > > On Fri, 2 May 2025 at 04:45, Bruce Momjian <bruce@momjian.us> wrote: > > > > > > > > I have committd the first draft of the PG 18 release notes. The item > > > > count looks strong: > > > > > > Thanks for all the work. Some notes: > > > > > > 1. There's currently no mention that protocol version 3.2 was > > > introduced in this release. I'm not sure where/how this should be > > > mentioned, but I definitely think it should be somewhere. It's a > > > pretty major change. One option is to replace/amend the "Make cancel > > > request keys 256 bits" item. Maybe replace that with something like: > > > "Postgres 18 introduces protocol version 3.2. This is the first new > > > protocol version since 3.0, which was introduced in Postgres 7.4. This > > > new protocol version 3.2 allows a server to use longer cancel request > > > keys. When the client advertises support for protocol version 3.2 (or > > > higher) Postgres 18 will use a cancel key size of 256 bits." > > > > Okay, I added a mention next to the libpq version function entries. > > I think it should be mentioned in the server section. How about we > replace: "This is enabled when the libpq client and server are > Postgres 18 or later. ", with the following: > > This is only enabled when the client supports the wire protocol > version 3.2 (or up), Wire protocol version 3.2 is introduced in this > release. For libpq based clients that means that libpq needs to be of > version 18 or higher. > > That also makes it clear that for non-libpq based clients people > should check if their client supports it. I moved the item and added some text, patch attached. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
Attachment
On Sat, May 3, 2025 at 11:04:45PM +0800, jian he wrote: > On Fri, May 2, 2025 at 10:44 AM Bruce Momjian <bruce@momjian.us> wrote: > > > > I have committd the first draft of the PG 18 release notes. The item > > count looks strong: > > release-17: 182 > > release-18: 209 > > > > I will continue improving it until beta 1, and until the final release. > > I will probably add markup in 1-3 weeks. Let the feedback begin. ;-) > > > > You can see the most current HTML-built version here: > > > > https://momjian.us/pgsql_docs/release-18.html > > > > seems you missed this ([1]): > > Detect redundant GROUP BY columns using UNIQUE indexes > > d4c3a156c added support that when the GROUP BY contained all of the columns > belonging to a relation's PRIMARY KEY, all other columns belonging to that > relation would be removed from the GROUP BY clause. That's possible because all > other columns are functionally dependent on the PRIMARY KEY and those columns > alone ensure the groups are distinct. > > [1] https://git.postgresql.org/cgit/postgresql.git/commit/?id=bd10ec529796a13670645e6acd640c6f290df020 We added this item to the PG 9.6 release notes: <!-- 2016-02-11 [d4c3a156c] Remove GROUP BY columns that are functionally dependent --> <para> Ignore <literal>GROUP BY</> columns that are functionally dependent on other columns (David Rowley) </para> <para> If a <literal>GROUP BY</> clause includes all columns of a non-deferred primary key, as well as other columns of the same table, those other columns are redundant and can be dropped from the grouping. This saves computation in many common cases. </para> </listitem> Interestingly, the first paragraph suggests this optimization already works for unique indexes, but the text below it states it only works for primary keys. As a nod to PG 9.6, which was released in 2016, I duplicated that item and reworded it for this commit. :-) Patch attached. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
Attachment
On Sat, 3 May 2025 at 18:19, Bruce Momjian <bruce@momjian.us> wrote: > I moved the item and added some text, patch attached. LGTM, apart from the typo in the word "client' (it's spelled as "cliient" in the diff). Noticed a few other small things when rereading: 1. "Add libpq functions and environment..." should be "Add libpq connection parameters and environment 2. "Allow the specification of non-overlapping PRIMARY KEY and UNIQUE constraints" has no commit attached to it. I noticed this because it wasn't fully clear to me what this feature entailed, so I wanted to look at it in more detail. 3. "Report search_path changes to the client." is currently in the libpq section, but this is a server-only change. So that seems a bit strange. Not sure where to put it though.
Hi hackers, > I will continue improving it until beta 1, and until the final release. > I will probably add markup in 1-3 weeks. Let the feedback begin. ;-) > > You can see the most current HTML-built version here: > > https://momjian.us/pgsql_docs/release-18.html > I'm not sure, but I'll ask. I have significantly improved the handling of Unicode Case in PostgreSQL. The improvements affect important functions such as lower(), upper(), casefold(). Specifically, the patch has significantly reduced the size of Unicode Case tables (and consequently the size of the object file). We got a significant speed gain: ASCII by ≈10% Cyrillic by ≈80% Unicode in general by ≈30% But, unfortunately, I didn't see any mention of this improvement in the release notes. Hence the question to the community - are such improvements worth mentioning? I'm just new to the community, and want to understand. Commit: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=27bdec06841d1bb004ca7627eac97808b08a7ac7 I am now actively working on a major improvement to Unicode Normalization Forms. Thanks! -- Regards, Alexander Borisov
On Sun, May 4, 2025 at 01:31:51AM +0300, Alexander Borisov wrote: > Hi hackers, > > > I will continue improving it until beta 1, and until the final release. > > I will probably add markup in 1-3 weeks. Let the feedback begin. ;-) > > > > You can see the most current HTML-built version here: > > > > https://momjian.us/pgsql_docs/release-18.html > > > > > I'm not sure, but I'll ask. > > I have significantly improved the handling of Unicode Case in > PostgreSQL. > The improvements affect important functions such as lower(), upper(), > casefold(). > Specifically, the patch has significantly reduced the size of Unicode > Case tables (and consequently the size of the object file). > We got a significant speed gain: > ASCII by ≈10% > Cyrillic by ≈80% > Unicode in general by ≈30% > > But, unfortunately, I didn't see any mention of this improvement in the > release notes. > Hence the question to the community - are such improvements worth > mentioning? > > I'm just new to the community, and want to understand. > > Commit: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=27bdec06841d1bb004ca7627eac97808b08a7ac7 > > I am now actively working on a major improvement to Unicode > Normalization Forms. Given the performance numbers above, which were not in the commit, maybe I should add it to the case folding item, and add his name as a co-author. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
On Sat, May 3, 2025 at 09:40:47PM +0200, Jelte Fennema-Nio wrote: > On Sat, 3 May 2025 at 18:19, Bruce Momjian <bruce@momjian.us> wrote: > > I moved the item and added some text, patch attached. > > LGTM, apart from the typo in the word "client' (it's spelled as > "cliient" in the diff). Thanks, fixed. > Noticed a few other small things when rereading: > > 1. "Add libpq functions and environment..." should be "Add libpq > connection parameters and environment Fixed. > 2. "Allow the specification of non-overlapping PRIMARY KEY and UNIQUE > constraints" has no commit attached to it. I noticed this because it > wasn't fully clear to me what this feature entailed, so I wanted to > look at it in more detail. Fixed. > 3. "Report search_path changes to the client." is currently in the > libpq section, but this is a server-only change. So that seems a bit > strange. Not sure where to put it though. Not sure. The only idea I had was server configuration, which doesn't match well. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
04.05.2025 01:47, Bruce Momjian wrote: [...] > Given the performance numbers above, which were not in the commit, maybe > I should add it to the case folding item, and add his name as a > co-author. > I'm not a co-author, I'm the author of my own algorithm that significantly improves PostgreSQL code. The author of casefold() is Jeff Davis (and in general, as I understand, Jeff is responsible for Unicode in Postgres). I only suggested how to significantly improve the storage of Unicode Case data and provide fast access to this data. In other words, I improved, accelerated the algorithms. Because of which the functions lower(), upper(), casefold() got a significant boost. -- Regards, Alexander Borisov
On Sun, May 4, 2025 at 02:24:16AM +0300, Alexander Borisov wrote: > 04.05.2025 01:47, Bruce Momjian wrote: > > [...] > > > Given the performance numbers above, which were not in the commit, maybe > > I should add it to the case folding item, and add his name as a > > co-author. > > > > I'm not a co-author, I'm the author of my own algorithm that > significantly improves PostgreSQL code. > The author of casefold() is Jeff Davis (and in general, as I understand, > Jeff is responsible for Unicode in Postgres). > I only suggested how to significantly improve the storage of Unicode > Case data and provide fast access to this data. > In other words, I improved, accelerated the algorithms. > > Because of which the functions lower(), upper(), casefold() got a > significant boost. It doesn't warrant its own item because it is not user-facing work. The best we can do is add the commit to an existing item and add you as a co-author on an existing item. You will see several items that are that way already. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
04.05.2025 02:28, Bruce Momjian wrote: > It doesn't warrant its own item because it is not user-facing work. The > best we can do is add the commit to an existing item and add you as a > co-author on an existing item. You will see several items that are that > way already. > Thank you for clarifying! Users are not interested in performance gains. Then it's not worth considering. Sorry to interrupt. -- Regards, Alexander Borisov
On Sun, May 4, 2025 at 02:48:31AM +0300, Alexander Borisov wrote:
> 04.05.2025 02:28, Bruce Momjian wrote:
>
>
> > It doesn't warrant its own item because it is not user-facing work. The
> > best we can do is add the commit to an existing item and add you as a
> > co-author on an existing item. You will see several items that are that
> > way already.
> >
>
> Thank you for clarifying!
> Users are not interested in performance gains.
> Then it's not worth considering. Sorry to interrupt.
So the logic is something I posted to this thread already:
So, a few things. First, these set of commits was in a group of 10 that
I added since there have been complaints in the past that optimizer
improvements were not listed and therefore patch authors were not given
sufficient credit. That means the 209 item count for PG 18 is 10 higher
than my normal filtering would produce.
Second, looking at the items, these are a case of "X is faster", which
we don't normally mention in the release notes. We normally mention
"faster" when it is so much faster that use cases which were not
possible before might be possible now, so it is recommended to retest.
That is what I saw this grouped item as, whereas I don't think the
individual items meet that criteria.
So, users are interested in performance in the sense it makes use cases
possible, and if your commit is making the case folding useful, we
should mention it in the release notes. I don't think making it
separate would fit though.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
On Sun, 4 May 2025 at 03:21, Bruce Momjian <bruce@momjian.us> wrote: > So the logic is something I posted to this thread already: > > So, a few things. First, these set of commits was in a group of 10 that > I added since there have been complaints in the past that optimizer > improvements were not listed and therefore patch authors were not given > sufficient credit. That means the 209 item count for PG 18 is 10 higher > than my normal filtering would produce. > > Second, looking at the items, these are a case of "X is faster", which > we don't normally mention in the release notes. We normally mention > "faster" when it is so much faster that use cases which were not > possible before might be possible now, so it is recommended to retest. > That is what I saw this grouped item as, whereas I don't think the > individual items meet that criteria. Let me start off the yearly thread of people saying they disagree with this filtering logic. I think there's an important utility of the Release Notes that these logic is not covering well: Many people read the release notes to see if upgrading is worth the hassle & risk for them specifically. The aggregate of some small performance improvements that apply to their queries could very well push them over the edge. These performance improvements don't need to "allow any new use cases" for that to be the case. The filtering that you currently do makes the release notes much less useful for people using the release notes for this purpose. Users might very well care more about ~10% perf improvement for a feature they use heavily, than all of the newly added SQL syntax combined. > So, users are interested in performance in the sense it makes use cases > possible, and if your commit is making the case folding useful, we > should mention it in the release notes. I don't think making it > separate would fit though. For this specific commit, I think if it had only changed the performance of casefold(), then I'd agree that it should be grouped with the casefold addition in the release notes. My reasoning would be that there's no "diff" in performance since the previous release, because the function did not exist in the previous release. So the perf improvements are simply part of the "initial implementation" of casefold from a user perspective. However since this commit also impacts the very commonly used lower() and upper() functions, I think that it would make sense if it got its own entry. It's neither clear for me from the commit message nor the skimming the original thread, whether the perf improvement numbers listed by Alexander also apply to lower() and upper(), or if they only apply to casefold(): On Sun, 4 May 2025 at 00:32, Alexander Borisov <lex.borisov@gmail.com> wrote: > ASCII by ≈10% > Cyrillic by ≈80% > Unicode in general by ≈30% If they apply the lower() and upper() I definitely think this patch deserves a place in "General Performance".
On Fri, 2 May 2025 at 04:45, Bruce Momjian <bruce@momjian.us> wrote: > I have committd the first draft of the PG 18 release notes. Some suggestions for additional commits to list for the items in the changelog: 1. I think 5070349102af12832c8528651c8ed18b16346323 should be listed as a commit for "Add libpq connection parameters and environment variables...". This commit contains a major part of the change that allows supporting multiple protocol versions client side. 2. I think 9d9b9d46f3c509c722ebbf2a1e7dc6296a6c711d & 09be39112654c3f158098fdb5f820143c0330763 should be listed as a commits for "Make cancel request keys 256 bits". These commits contain crucial parts of that change. This would also put me in there as one of the co-authors for this item.
Regarding these items * Allow ALTER TABLE to set the NOT VALID attribute of NOT NULL constraints (Rushabh Lathia, Jian He) * Allow NOT VALID foreign key constraints on partitioned tables (Amul Sul) * Allow modification of the inheritability of constraints (Suraj Kharage, Álvaro Herrera) The syntax is ALTER TABLE ... ALTER CONSTRAINT ... [NO] INHERIT. * Store column NOT NULL specifications in pg_constraint (Álvaro Herrera, Bernd Helmle) This allows names to be specified for NOT NULL constraint. This also adds NOT NULL constraints to foreign tables and NOT NULL inheritance control to local tables. I think the wording and order of them is a bit unclear. I would put the last item first, immediately followed by the other two; alternatively we could merge them all into a single one: * Store NOT NULL constraints in pg_constraint for better preservability (Álvaro, Bernd, Suraj, Rushabh, Jian) The constraint names are well defined and are preserved across dump/restore. Also, the NOT VALID and NO INHERIT properties work as expected and can be modified by users via ALTER TABLE. Or something along those lines. This one in particular: Allow modification of the inheritability of constraints (Suraj Kharage, Álvaro Herrera) The syntax is ALTER TABLE ... ALTER CONSTRAINT ... [NO] INHERIT. only works with not-null constraints, which is why I suggest to merge together with the above item. Ideally, on the next release somebody would work to make that feature more general (work with other types of constraints). I think this item Allow CHECK and foreign key constraints to be specified as NOT ENFORCED (Amul Sul) This also adds column pg_constraint.conenforced. should come second or maybe even first in that section, as it is I think the most user-visible. These two items Allow NOT VALID foreign key constraints on partitioned tables (Amul Sul) Allow dropping of constraints ONLY on partitioned tables (Álvaro Herrera) This was previously erroneously prohibited. can mostly be considered bug-fixes, so they should be last in the section; they aren't new features, just making existing features work correctly. The one I have second is I think of lesser importance. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ "I'm impressed how quickly you are fixing this obscure issue. I came from MS SQL and it would be hard for me to put into words how much of a better job you all are doing on [PostgreSQL]." Steve Midgley, http://archives.postgresql.org/pgsql-sql/2008-08/msg00000.php
Hi, On Fri, May 2, 2025 at 11:44 AM Bruce Momjian <bruce@momjian.us> wrote: > > I have committd the first draft of the PG 18 release notes. The item > count looks strong: > > release-7.4: 263 > release-8.0: 230 > release-8.1: 174 > release-8.2: 215 > release-8.3: 214 > release-8.4: 314 > release-9.0: 237 > release-9.1: 203 > release-9.2: 238 > release-9.3: 177 > release-9.4: 211 > release-9.5: 193 > release-9.6: 214 > release-10: 189 > release-11: 170 > release-12: 180 > release-13: 178 > release-14: 220 > release-15: 184 > release-16: 206 > release-17: 182 > release-18: 209 > > I will continue improving it until beta 1, and until the final release. > I will probably add markup in 1-3 weeks. Let the feedback begin. ;-) Thanks for your work! > Add REJECT_LIMIT to control the number of invalid rows COPY IN can ignore (Atsushi Torikoshi) Since REJECT_LIMIT cannot be used with COPY IN but can be used with COPY FROM, I think "IN" should be "FROM". =# COPY t1 TO '/tmp/a' WITH (REJECT_LIMIT 3); ERROR: COPY REJECT_LIMIT requires ON_ERROR to be set to IGNORE =# COPY t1 TO '/tmp/a' WITH ( ON_ERROR ignore, REJECT_LIMIT 3); ERROR: COPY ON_ERROR cannot be used with COPY TO LINE 1: COPY t1 to '/tmp/a' WITH (ON_ERROR ignore, REJECT_LIMIT 3); > This is active when ON_ERROR = 'ignore'. As a non-native English speaker, I may be misunderstanding this, but the word "active" might suggest that REJECT_LIMIT always takes effect by default, causing the COPY operation to stop after a certain number of errors. However, in reality, REJECT_LIMIT does not have any effect by default — unless explicitly set, there is no limit on the number of rows that can be skipped when ON_ERROR is set to ignore. To avoid this potential confusion, a phrasing like: This option must be used with ON_ERROR ignore. might be clearer. Also, in the v17 release notes, COPY option values are not enclosed in single quotes but written in <literal> tag. For consistency, it might be better to follow the same style in the v18 notes as well. -- https://www.postgresql.org/docs/current/release-17.html Add new COPY option ON_ERROR ignore to discard error rows The default behavior is ON_ERROR stop > Add COPY log_verbosity level "silent" to suppress all log output (Atsushi Torikoshi) Similarly, in the v17 release notes, the log_verbosity option was written in uppercase (LOG_VERBOSITY). For consistency, it may be preferable to use the same case formatting in this entry as well. -- https://www.postgresql.org/docs/current/release-17.html Add new COPY option LOG_VERBOSITY which reports COPY FROM ignored error rows Also, the phrase "suppress all log output" may be slightly misleading. Even with log_verbosity = 'silent', COPY still outputs logs — it only suppresses log messages related to skipped rows when ON_ERROR ignore is used. It might help to clarify this nuance to avoid confusion. For example, how about "Add COPY LOG_VERBOSITY silent to suppress logs related to skipped rows"? > Add on_error and log_verbosity options to file_fdw (Atsushi Torikoshi) > Add REJECT_LIMIT to control the number of invalid rows file_fdw can ignore (Atsushi Torikoshi) > This is active when ON_ERROR = 'ignore'. The case of option names for file_fdw is inconsistent — some are lowercase (on_error, log_verbosity), while others use uppercase (REJECT_LIMIT, ON_ERROR). For consistency, it might be better to standardize the option names throughout the release notes. Since the file_fdw documentation consistently uses lowercase for all options, using lowercase in the release notes as well might feel more natural. -- Regards, Atsushi Torikoshi
04.05.2025 12:19, Jelte Fennema-Nio wrote: > On Sun, 4 May 2025 at 03:21, Bruce Momjian <bruce@momjian.us> wrote: >> So the logic is something I posted to this thread already: >> >> So, a few things. First, these set of commits was in a group of 10 that >> I added since there have been complaints in the past that optimizer >> improvements were not listed and therefore patch authors were not given >> sufficient credit. That means the 209 item count for PG 18 is 10 higher >> than my normal filtering would produce. >> >> Second, looking at the items, these are a case of "X is faster", which >> we don't normally mention in the release notes. We normally mention >> "faster" when it is so much faster that use cases which were not >> possible before might be possible now, so it is recommended to retest. >> That is what I saw this grouped item as, whereas I don't think the >> individual items meet that criteria. > > Let me start off the yearly thread of people saying they disagree with > this filtering logic. I think there's an important utility of the > Release Notes that these logic is not covering well: > > Many people read the release notes to see if upgrading is worth the > hassle & risk for them specifically. The aggregate of some small > performance improvements that apply to their queries could very well > push them over the edge. These performance improvements don't need to > "allow any new use cases" for that to be the case. > > The filtering that you currently do makes the release notes much less > useful for people using the release notes for this purpose. Users > might very well care more about ~10% perf improvement for a feature > they use heavily, than all of the newly added SQL syntax combined. > >> So, users are interested in performance in the sense it makes use cases >> possible, and if your commit is making the case folding useful, we >> should mention it in the release notes. I don't think making it >> separate would fit though. > > For this specific commit, I think if it had only changed the > performance of casefold(), then I'd agree that it should be grouped > with the casefold addition in the release notes. My reasoning would be > that there's no "diff" in performance since the previous release, > because the function did not exist in the previous release. So the > perf improvements are simply part of the "initial implementation" of > casefold from a user perspective. > > However since this commit also impacts the very commonly used lower() > and upper() functions, I think that it would make sense if it got its > own entry. It's neither clear for me from the commit message nor the > skimming the original thread, whether the perf improvement numbers > listed by Alexander also apply to lower() and upper(), or if they only > apply to casefold(): > > On Sun, 4 May 2025 at 00:32, Alexander Borisov <lex.borisov@gmail.com> wrote: >> ASCII by ≈10% >> Cyrillic by ≈80% >> Unicode in general by ≈30% > > If they apply the lower() and upper() I definitely think this patch > deserves a place in "General Performance". I'm actually a bit confused, and didn't expect such a heated discussion about creating an entry about my patch in Release Notes. I thought I had made significant Unicode improvements to Postgres. Namely, significantly reduce the object file size for Unicode Case, and most importantly increased performance. Thanks to my approach/algorithm, all Unicode Case related functions got a significant boost. Namely: lower(), upper(), casefold(). I have already given the figures. Why casefold() is the one that got caught here is not clear to me, I have nothing to do with the implementation of this function. I improved the overall Unicode Case algorithm, which caused a boost in all of the listed functions. I thought it would be useful for users to know that Postgres is improving in the performance direction, especially in such functions that are very often used by users to compare text by bringing it to a certain case. The discussion made me realize that it's not that important. I'm not insisting. If it really doesn't matter to users, then it's not worth discussing. Thank you for your attention. I will continue to improve Postgres. -- Regards, Alexander Borisov
On 5/3/25 7:48 PM, Alexander Borisov wrote: > 04.05.2025 02:28, Bruce Momjian wrote: > > >> It doesn't warrant its own item because it is not user-facing work. The >> best we can do is add the commit to an existing item and add you as a >> co-author on an existing item. You will see several items that are that >> way already. >> > > Thank you for clarifying! > Users are not interested in performance gains. > Then it's not worth considering. Sorry to interrupt. I do know that users are interested in performance gains - and it is definitely user facing - and as such I try to include these things in the release announcement (which I'm currently drafting) as part of making the release available. Similarly, we do need to use discretion with how we discuss performance enhancements in the release announcement to ensure we make directionally accurate claims and look towards broad impact. I've noted the claims on upper/lower; we'll see if it makes the cut in the release announcement. Thanks, Jonathan
On Sun, 4 May 2025 at 22:28, Alexander Borisov <lex.borisov@gmail.com> wrote: > I'm actually a bit confused, and didn't expect such a heated discussion > about creating an entry about my patch in Release Notes. I definitely understand this. And to make my own opinion on this matter extremely clear: I *do* think it's important to users, and it should be included in the release notes. I think there are a few things at play here why that did not happen in Bruce his initial draft: 1. I personally think the requirement that Bruce uses for perf improvements to make it into the changelog is too strict (see my previous email for details) 2. Bruce is only a single person, and as such cannot read all emails on pgsql-hackers, so he relies only on commit messages to determine impact for release notes. The commit message for your change did not include any details on the perf improvements that could be expected. 3. After skimming the email thread[1], it's hard for me to understand where these perf numbers came from. And the first few results only mention casefold performance i.e. they call the results: "casefold() test." So, it's unclear what perf gains are expected for the other functions mentioned in the email subject. As for how to improve these: 1 is discussed/complained about basically every year whenever release notes are created. I don't think we can do any better than having those discussions. Unless someone else wants to start owning writing the release notes, or we somehow share the burden, e.g. by having the person that commits also write a release note entry. 2 can be improved by people including perf numbers in their commit messages. The second way to improve is by sending feedback on the release notes if things are missed, like you did. 3 is something you could help with I think. It would have been helpful if you had shared the script/commands you used to get these performance numbers. That way I could reproduce them myself. Also if you had included some perf numbers for lower() and upper() that would have been great too, as those are (currently) much more commonly used than casefold(). NOTE: I might have missed the script or be wrong about this some other way, since Jeff did not require this for committing it. If so, please disregard. [1]: https://www.postgresql.org/message-id/flat/7cac7e66-9a3b-4e3f-a997-42aa0c401f80%40gmail.com > I will continue to improve Postgres. Please do, your work is very much appreciated!
On Sun, May 4, 2025 at 11:49:47AM +0200, Jelte Fennema-Nio wrote:
> On Fri, 2 May 2025 at 04:45, Bruce Momjian <bruce@momjian.us> wrote:
> > I have committd the first draft of the PG 18 release notes.
>
> Some suggestions for additional commits to list for the items in the changelog:
> 1. I think 5070349102af12832c8528651c8ed18b16346323 should be listed
> as a commit for "Add libpq connection parameters and environment
> variables...". This commit contains a major part of the change that
> allows supporting multiple protocol versions client side.
Added.
> 2. I think 9d9b9d46f3c509c722ebbf2a1e7dc6296a6c711d &
> 09be39112654c3f158098fdb5f820143c0330763 should be listed as a commits
> for "Make cancel request keys 256 bits". These commits contain crucial
> parts of that change. This would also put me in there as one of the
> co-authors for this item.
I added the first commit but the second one is:
commit 09be3911265
Author: Heikki Linnakangas <heikki.linnakangas@iki.fi>
Date: Wed Apr 2 15:32:40 2025 +0300
Add timingsafe_bcmp(), for constant-time memory comparison
timingsafe_bcmp() should be used instead of memcmp() or a naive
for-loop, when comparing passwords or secret tokens, to avoid leaking
information about the secret token by timing. This commit just
introduces the function but does not change any existing code to use
it yet.
Co-authored-by: Jelte Fennema-Nio <github-tech@jeltef.nl>
Discussion: https://www.postgresql.org/message-id/7b86da3b-9356-4e50-aa1b-56570825e234@iki.fi
which is either the wrong commit hash or too far away from the item
description to be added.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
On Sun, May 4, 2025 at 01:35:30PM +0200, Álvaro Herrera wrote: > Regarding these items > > * Allow ALTER TABLE to set the NOT VALID attribute of NOT NULL constraints > (Rushabh Lathia, Jian He) > > * Allow NOT VALID foreign key constraints on partitioned tables (Amul Sul) > > * Allow modification of the inheritability of constraints (Suraj Kharage, Álvaro Herrera) > > The syntax is ALTER TABLE ... ALTER CONSTRAINT ... [NO] INHERIT. > > * Store column NOT NULL specifications in pg_constraint (Álvaro Herrera, Bernd Helmle) > > This allows names to be specified for NOT NULL constraint. This also adds NOT > NULL constraints to foreign tables and NOT NULL inheritance control to local > tables. > > > I think the wording and order of them is a bit unclear. I would put the > last item first, immediately followed by the other two; alternatively we > could merge them all into a single one: I moved the last item to first in the group. > * Store NOT NULL constraints in pg_constraint for better preservability > (Álvaro, Bernd, Suraj, Rushabh, Jian) > > The constraint names are well defined and are preserved across > dump/restore. Also, the NOT VALID and NO INHERIT properties work > as expected and can be modified by users via ALTER TABLE. > > Or something along those lines. I think the merged text is too confusing. > This one in particular: > Allow modification of the inheritability of constraints (Suraj Kharage, Álvaro Herrera) > > The syntax is ALTER TABLE ... ALTER CONSTRAINT ... [NO] INHERIT. > > only works with not-null constraints, which is why I suggest to merge > together with the above item. Ideally, on the next release somebody > would work to make that feature more general (work with other types of > constraints). Yes, I see that detail in the docs of the first commit, and in the second commit text. I modified to say NOT NULL. > > I think this item > Allow CHECK and foreign key constraints to be specified as NOT ENFORCED > (Amul Sul) > > This also adds column pg_constraint.conenforced. > > should come second or maybe even first in that section, as it is I think > the most user-visible. I made it second. > These two items > Allow NOT VALID foreign key constraints on partitioned tables (Amul Sul) > > Allow dropping of constraints ONLY on partitioned tables (Álvaro Herrera) > > This was previously erroneously prohibited. > > can mostly be considered bug-fixes, so they should be last in the > section; they aren't new features, just making existing features work > correctly. The one I have second is I think of lesser importance. Agreed, moved as you suggested. I have trouble figuring out the importance sometimes, and this section is very large. Patch attached. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
Attachment
On Sun, May 4, 2025 at 10:41:30PM +0900, Atsushi Torikoshi wrote:
> > I will continue improving it until beta 1, and until the final release.
> > I will probably add markup in 1-3 weeks. Let the feedback begin. ;-)
>
> Thanks for your work!
>
> > Add REJECT_LIMIT to control the number of invalid rows COPY IN can ignore (Atsushi Torikoshi)
>
> Since REJECT_LIMIT cannot be used with COPY IN but can be used with
> COPY FROM, I think "IN" should be "FROM".
>
> =# COPY t1 TO '/tmp/a' WITH (REJECT_LIMIT 3);
> ERROR: COPY REJECT_LIMIT requires ON_ERROR to be set to IGNORE
> =# COPY t1 TO '/tmp/a' WITH ( ON_ERROR ignore, REJECT_LIMIT 3);
> ERROR: COPY ON_ERROR cannot be used with COPY TO
> LINE 1: COPY t1 to '/tmp/a' WITH (ON_ERROR ignore, REJECT_LIMIT 3);
Agreed. A PG 18 commit had "COPY IN" and I ended up using that, even
though we have no COPY IN but only COPY FROM. Fixed in all placed.
> > This is active when ON_ERROR = 'ignore'.
>
> As a non-native English speaker, I may be misunderstanding this, but
> the word "active" might suggest that REJECT_LIMIT always takes effect
> by default, causing the COPY operation to stop after a certain number
> of errors.
> However, in reality, REJECT_LIMIT does not have any effect by default
> — unless explicitly set, there is no limit on the number of rows that
> can be skipped when ON_ERROR is set to ignore.
> To avoid this potential confusion, a phrasing like:
>
> This option must be used with ON_ERROR ignore.
>
> might be clearer.
Uh, that might suggest you have to use REJECT_LIMIT with ON_ERROR, which
is untrue. I used:
This is available when ON_ERROR = 'ignore'.
> Also, in the v17 release notes, COPY option values are not enclosed in
> single quotes but written in <literal> tag.
> For consistency, it might be better to follow the same style in the
> v18 notes as well.
>
> -- https://www.postgresql.org/docs/current/release-17.html
> Add new COPY option ON_ERROR ignore to discard error rows
> The default behavior is ON_ERROR stop
The quotes will be removed when I add XML markup in 1-3 weeks.
> > Add COPY log_verbosity level "silent" to suppress all log output (Atsushi Torikoshi)
>
> Similarly, in the v17 release notes, the log_verbosity option was
> written in uppercase (LOG_VERBOSITY).
>
> For consistency, it may be preferable to use the same case formatting
> in this entry as well.
>
> -- https://www.postgresql.org/docs/current/release-17.html
> Add new COPY option LOG_VERBOSITY which reports COPY FROM ignored error rows
Same.
> Also, the phrase "suppress all log output" may be slightly misleading.
> Even with log_verbosity = 'silent', COPY still outputs logs — it only
> suppresses log messages related to skipped rows when ON_ERROR ignore
> is used.
> It might help to clarify this nuance to avoid confusion.
> For example, how about "Add COPY LOG_VERBOSITY silent to suppress logs
> related to skipped rows"?
I went with:
Add COPY LOG_VERBOSITY level "silent" to suppress log output of ignored rows
because the docs call them "ignored" rows rather than "skipped" rows.
> > Add on_error and log_verbosity options to file_fdw (Atsushi Torikoshi)
> > Add REJECT_LIMIT to control the number of invalid rows file_fdw can ignore (Atsushi Torikoshi)
> > This is active when ON_ERROR = 'ignore'.
>
> The case of option names for file_fdw is inconsistent — some are
> lowercase (on_error, log_verbosity), while others use uppercase
> (REJECT_LIMIT, ON_ERROR).
> For consistency, it might be better to standardize the option names
> throughout the release notes.
> Since the file_fdw documentation consistently uses lowercase for all
> options, using lowercase in the release notes as well might feel more
> natural.
Case changed. Patch attached.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
Attachment
On Fri, 2 May 2025 at 16:01, Amit Langote <amitlangote09@gmail.com> wrote: > +Allow partitions to be pruned earlier and quicker, and skipped in > more places (Amit Langote, Ashutosh Bapat, Yuya Watari, David Rowley) > > Alternatively, 2 and 3 can be combined as: > 2. Speed up partition planning by improving EquivalenceClass lookups > (Yuya Watari, David Rowley, Ashutosh Bapat) > Any thoughts, David? I agree that 88f55bc97 and d69d45a5a should be in their own item. Likely no need to go into detail about the speed up being about "EquivalenceClass lookups". I imagine something like "Reduce planner overheads when planning queries to partitioned and inheritance parent tables" Then for bb3ec16e1, d47cbf474, cbc127917 and 525392d57, something like "Defer locking of partitions during execution until after partition elimination". The release notes for 11.0 called it "partition elimination", so I went with that naming. David
On Fri, 2 May 2025 at 14:44, Bruce Momjian <bruce@momjian.us> wrote: > You can see the most current HTML-built version here: > > https://momjian.us/pgsql_docs/release-18.html Thanks for working on these. For "Improve the performance of hash joins (David Rowley)", 0f5738202 did the same thing for GROUP BY and hashed subplans too. It might be worth adjusting this to some more generic text which covers all of these. Something like "Speed up hash value generation in Hash Join, GROUP BY, hashed Subplan and hashed set operations</p><p>This change also allows JIT compilation for obtaining hash values for these operations". The set operations I likely should have mentioned in the commit message. There's also Jeff's work in cc721c459, 4d143509c, a0942f441, 626df47ad which does work to reduce the memory overheads of hashed GROUP BY, hashed Subplans and hashed set operations. I think Jeff might have understated the possible performance gains from these commits. I very much think this is worth something like "Reduce memory overheads for hashed GROUP BY, subplans and set operation processing (Jeff Davis)". A quick test with: explain analyze select a from generate_series(1,1000000) a group by a; v17: Batches: 1 Memory Usage: 90145kB v18: Batches: 1 Memory Usage: 57385kB A 37% reduction for this case. Not insignificant. David
Hi, On Thu, May 01, 2025 at 10:44:50PM -0400, Bruce Momjian wrote: > I have committd the first draft of the PG 18 release notes. Thanks for working on this! > You can see the most current HTML-built version here: > > https://momjian.us/pgsql_docs/release-18.html I have one comment related to "per backend" statistics. We have this: " Add per-backend I/O statistics reporting (Bertrand Drouvot) The statistics are accessed via pg_stat_get_backend_io(). Per-backend statistics can be cleared via pg_stat_reset_backend_stats(). " and " Add function pg_stat_get_backend_wal() to return per-backend WAL statistics (Bertrand Drouvot) " I think that we could mention pg_stat_reset_backend_stats() in both case, something like: A. "Per-backend I/O statistics can be cleared via pg_stat_reset_backend_stats()" and "Per-backend WAL statistics can be cleared via pg_stat_reset_backend_stats()" for consitency. Or: B. mention pg_stat_reset_backend_stats() "separately" just saying: " Per-backend statistics can be cleared via pg_stat_reset_backend_stats() " and get rid of the mention in "per-backend I/O statistics". I'd be tempted to vote for B (so that pg_stat_reset_backend_stats() is introduced separately): thoughts? Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
On Thu, May 01, 2025 at 10:44:50PM -0400, Bruce Momjian wrote:
> I have committd the first draft of the PG 18 release notes.
> I have committd the first draft of the PG 18 release notes.
I don't think pg_buffercache_numa does exist.
Regards
Daniel
>On Thu, May 01, 2025 at 10:44:50PM -0400, Bruce Momjian wrote:
>> I have committd the first draft of the PG 18 release notes.
>> I have committd the first draft of the PG 18 release notes.
>I don't think pg_buffercache_numa does exist.
Please ignore
Regards
Daniel
On Mon, May 5, 2025 at 09:42:10PM +1200, David Rowley wrote: > On Fri, 2 May 2025 at 16:01, Amit Langote <amitlangote09@gmail.com> wrote: > > +Allow partitions to be pruned earlier and quicker, and skipped in > > more places (Amit Langote, Ashutosh Bapat, Yuya Watari, David Rowley) > > > > Alternatively, 2 and 3 can be combined as: > > > 2. Speed up partition planning by improving EquivalenceClass lookups > > (Yuya Watari, David Rowley, Ashutosh Bapat) > > > Any thoughts, David? > > I agree that 88f55bc97 and d69d45a5a should be in their own item. > Likely no need to go into detail about the speed up being about > "EquivalenceClass lookups". I imagine something like "Reduce planner > overheads when planning queries to partitioned and inheritance parent > tables" > > Then for bb3ec16e1, d47cbf474, cbc127917 and 525392d57, something like > "Defer locking of partitions during execution until after partition > elimination". The release notes for 11.0 called it "partition > elimination", so I went with that naming. Okay, I split them up and went with the attached patch. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
Attachment
On Mon, May 5, 2025 at 10:14:22PM +1200, David Rowley wrote: > On Fri, 2 May 2025 at 14:44, Bruce Momjian <bruce@momjian.us> wrote: > > You can see the most current HTML-built version here: > > > > https://momjian.us/pgsql_docs/release-18.html > > Thanks for working on these. > > For "Improve the performance of hash joins (David Rowley)", 0f5738202 > did the same thing for GROUP BY and hashed subplans too. It might be > worth adjusting this to some more generic text which covers all of > these. Something like "Speed up hash value generation in Hash Join, > GROUP BY, hashed Subplan and hashed set operations</p><p>This change > also allows JIT compilation for obtaining hash values for these > operations". The set operations I likely should have mentioned in the > commit message. Okay, text added. > There's also Jeff's work in cc721c459, 4d143509c, a0942f441, 626df47ad > which does work to reduce the memory overheads of hashed GROUP BY, > hashed Subplans and hashed set operations. I think Jeff might have > understated the possible performance gains from these commits. I very > much think this is worth something like "Reduce memory overheads for > hashed GROUP BY, subplans and set operation processing (Jeff Davis)". > > A quick test with: explain analyze select a from > generate_series(1,1000000) a group by a; > > v17: Batches: 1 Memory Usage: 90145kB > v18: Batches: 1 Memory Usage: 57385kB > > A 37% reduction for this case. Not insignificant. Commits added and Jeff's name added, patch attached. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
Attachment
On Mon, May 5, 2025 at 02:09:26PM +0000, Bertrand Drouvot wrote: > A. > > "Per-backend I/O statistics can be cleared via pg_stat_reset_backend_stats()" > > and > > "Per-backend WAL statistics can be cleared via pg_stat_reset_backend_stats()" > > for consitency. > > Or: I went with the A option, patch attached. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
Attachment
On Thu, May 1, 2025 at 7:44 PM Bruce Momjian <bruce@momjian.us> wrote: > https://momjian.us/pgsql_docs/release-18.html > +Add support for the "oauth" authentication (Jacob Champion, Daniel Gustafsson, Thomas Munro) Should be either 'support for "oauth" authentication' or 'support for the "oauth" authentication method', I think. > +This adds an "oauth" authentication method to pg_hba.conf, a server variable oauth_validator_libraries to specify OAUTHlibraries, a configure flag --with-libcurl to add the required > +compile-time libraries, and libpq OAUTH options. Maybe the description of oauth_validator_libraries could be something like "to load token validation modules"? Also, "OAUTH" should just be "OAuth". We should probably lock in the capitalization: - "OAuth" is the name of the framework we're using - "oauth" is the HBA method name in the configs - "OAUTHBEARER" is the internal name of the SASL method, which most users don't care about and should only rarely appear in the docs Thanks! --Jacob
On Mon, May 5, 2025 at 12:20:15PM -0700, Jacob Champion wrote: > On Thu, May 1, 2025 at 7:44 PM Bruce Momjian <bruce@momjian.us> wrote: > > https://momjian.us/pgsql_docs/release-18.html > > > +Add support for the "oauth" authentication (Jacob Champion, Daniel Gustafsson, Thomas Munro) > > Should be either 'support for "oauth" authentication' or 'support for > the "oauth" authentication method', I think. Okay. > > +This adds an "oauth" authentication method to pg_hba.conf, a server variable oauth_validator_libraries to specify OAUTHlibraries, a configure flag --with-libcurl to add the required > > +compile-time libraries, and libpq OAUTH options. > > Maybe the description of oauth_validator_libraries could be something > like "to load token validation modules"? Done. > Also, "OAUTH" should just be "OAuth". We should probably lock in the > capitalization: > > - "OAuth" is the name of the framework we're using > - "oauth" is the HBA method name in the configs > - "OAUTHBEARER" is the internal name of the SASL method, which most > users don't care about and should only rarely appear in the docs Capitalization changed, patch attached. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
Attachment
05.05.2025 03:22, Jelte Fennema-Nio wrote: [...] > > I think there are a few things at play here why that did not happen in > Bruce his initial draft: > 1. I personally think the requirement that Bruce uses for perf > improvements to make it into the changelog is too strict (see my > previous email for details) > 2. Bruce is only a single person, and as such cannot read all emails > on pgsql-hackers, so he relies only on commit messages to determine > impact for release notes. The commit message for your change did not > include any details on the perf improvements that could be expected. > 3. After skimming the email thread[1], it's hard for me to understand > where these perf numbers came from. And the first few results only > mention casefold performance i.e. they call the results: "casefold() > test." So, it's unclear what perf gains are expected for the other > functions mentioned in the email subject. I totally agree with you, it's hard to keep track of everything. It's also a lot of work to read every commit and understand its essence. I have no complaints, I'm just trying to understand the rules of getting into Release Notes. The rules, as it turns out, are not simple. But they are rules, even though I don't agree with them, I accept them. > > As for how to improve these: > 1 is discussed/complained about basically every year whenever release > notes are created. I don't think we can do any better than having > those discussions. Unless someone else wants to start owning writing > the release notes, or we somehow share the burden, e.g. by having the > person that commits also write a release note entry. > 2 can be improved by people including perf numbers in their commit > messages. The second way to improve is by sending feedback on the > release notes if things are missed, like you did. > 3 is something you could help with I think. It would have been helpful > if you had shared the script/commands you used to get these > performance numbers. That way I could reproduce them myself. Also if > you had included some perf numbers for lower() and upper() that would > have been great too, as those are (currently) much more commonly used > than casefold(). NOTE: I might have missed the script or be wrong > about this some other way, since Jeff did not require this for > committing it. If so, please disregard. > > [1]: https://www.postgresql.org/message-id/flat/7cac7e66-9a3b-4e3f-a997-42aa0c401f80%40gmail.com A bit about what those numbers are, in the discussion for the patch I described how I got those numbers. The point is that functions lower(), upper(), casefold() have one common algorithm, the difference is in what table for mapping we pass to this algorithm. Therefore, there is no sense to measure the performance of each function separately. Any of these functions will show the performance of the algorithm of getting codepoints from tables in the same way. Therefore, we can take lower() or upper() or casefold() and get the result of Unicode table mapping algorithm (that's where I changed the code, the algorithm). I can measure everything, but there is no sense in it. Here are the measurements made at the moment of patch discussion: For each test, a sql file was created for pgbench. The data description is present. casefold() test. ASCII: Repeated characters (700kb) in the range from 0x20 to 0x7E. Patch: tps = 278.449809 Without: tps = 266.526168 Cyrillic: Repeated characters (1MB) in the range from 0x0410 to 0x042F. Patch: tps = 86.740680 Without: tps = 49.373695 Unicode: A query consisting of all Unicode characters from 0xA0 to 0x2FA1D (excluding 0xD800..0xDFFF). Patch: tps = 102.221092 Without: tps = 92.477798 * Ubuntu 24.04.1 (Intel(R) Xeon(R) Gold 6140) (gcc version 13.3.0) ASCII: Repeated characters (700kb) in the range from 0x20 to 0x7E. Patch: tps = 146.712371 Without: tps = 120.794307 Cyrillic: Repeated characters (1MB) in the range from 0x0410 to 0x042F. Patch: tps = 44.499567 Without: tps = 24.237999 Unicode: A query consisting of all Unicode characters from 0xA0 to 0x2FA1D (excluding 0xD800..0xDFFF). Patch: tps = 54.354833 Without: tps = 46.556531 > >> I will continue to improve Postgres. > > Please do, your work is very much appreciated! I thought it was worthy of a separate line in the Release Notes. As I think, it is not so easy to increase the performance for Unicode. So many users use lower() and upper(), and it would be nice to know that work is being done to improve performance in this area. But again, I'm new to the Postgres community and I'm getting to know what's going on here and how it works. Thank you for paying attention to it! -- Regards, Alexander Borisov
Bruce Momjian <bruce@momjian.us> writes:
> Capitalization changed, patch attached.
I need to start wrapping the tarballs soon ... are you done
with the release notes for today?
regards, tom lane
On Mon, May 5, 2025 at 04:12:16PM -0400, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Capitalization changed, patch attached. > > I need to start wrapping the tarballs soon ... are you done > with the release notes for today? Yes, I am, thanks for asking. I was waiting for the case folding thread to conclude, but I think it is still being discussed so let's not wait for it. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
On Tue, 6 May 2025 at 03:59, Bruce Momjian <bruce@momjian.us> wrote: > > On Mon, May 5, 2025 at 09:42:10PM +1200, David Rowley wrote: > > I agree that 88f55bc97 and d69d45a5a should be in their own item. > > Likely no need to go into detail about the speed up being about > > "EquivalenceClass lookups". I imagine something like "Reduce planner > > overheads when planning queries to partitioned and inheritance parent > > tables" > > > > Then for bb3ec16e1, d47cbf474, cbc127917 and 525392d57, something like > > "Defer locking of partitions during execution until after partition > > elimination". The release notes for 11.0 called it "partition > > elimination", so I went with that naming. > > Okay, I split them up and went with the attached patch. > +Allow partitions to be pruned more efficienty (Ashutosh Bapat, Yuya Watari, David Rowley) I think you've misunderstood what's been changed here. Unfortunately, it's not even true with a bit of eye squinting as these changes have nothing to do with partition pruning. I think it would be much more informative to state it as I suggested. Also, the spelling of "efficiently" needs adjusted. > +Avoid the locking of pruned partitions during planning (Amit Langote) At the very least, you'd need to swap "planning" for "execution" as the above statement isn't true. David
On Sat, May 3, 2025 at 1:18 AM Bruce Momjian <bruce@momjian.us> wrote: > On Fri, May 2, 2025 at 01:00:57PM +0900, Amit Langote wrote: > > 1. Speed up execution of cached plans by deferring locks on partitions > > subject to pruning (Amit Langote) > > (bb3ec16e1, d47cbf474, cbc127917, 525392d57) > > > > 2. Speed up child EquivalenceMember lookup in planner (Yuya Watari, > > David Rowley) > > (d69d45a5a) > > > > 3. Speed up derived clause lookup in EquivalenceClass (Ashutosh Bapat) > > (88f55bc97) > > > > Alternatively, 2 and 3 can be combined as: > > > > 2. Speed up partition planning by improving EquivalenceClass lookups > > (Yuya Watari, David Rowley, Ashutosh Bapat) > > > > I think 1 should go under Partitioning, which I see is currently missing. > > > > Any thoughts, David? > > > > Can work on a patch if you'd like. > > So, a few things. First, these set of commits was in a group of 10 that > I added since there have been complaints in the past that optimizer > improvements were not listed and therefore patch authors were not given > sufficient credit. That means the 209 item count for PG 18 is 10 higher > than my normal filtering would produce. > > Second, looking at the items, these are a case of "X is faster", which > we don't normally mention in the release notes. We normally mention > "faster" when it is so much faster that use cases which were not > possible before might be possible now, so it is recommended to retest. > That is what I saw this grouped item as, whereas I don't think the > individual items meet that criteria. > > Also, I didn't see enough partition items to warrant a separate > partition section, and we didn't have one in PG 17 either. We could > pull all the partition items from the sections they are already in, but > they seem more natural in the sections they are in. > > I don't think most people would know what EquivalenceMember is, and even > if they did, would they be able to connect it to an SQL query? Thanks for splitting these (cf847d634). I think the text for the locking item should mention “during execution,” as David also suggested. Again, I don’t think this change belongs under Optimizer since it doesn’t really affect the planner -- it’s mainly an executor improvement. Maybe the General Performance section is a better fit. Also, just to clarify why the individual items are meaningful performance improvements: * Locking change: Executing cached plans involving hundreds or thousands of partitions was bottlenecked by locking; with this change, execution is now roughly 20x faster with 1000 partitions. * Planning time improvements: Planning certain commonly used queries against partitioned tables when they don’t use partition pruning is now roughly 20x faster with 1000 partitions. -- Thanks, Amit Langote
On Fri, May 2, 2025 at 10:44 AM Bruce Momjian <bruce@momjian.us> wrote: > > release-16: 206 > release-17: 182 > release-18: 209 > > I will continue improving it until beta 1, and until the final release. > I will probably add markup in 1-3 weeks. Let the feedback begin. ;-) > > You can see the most current HTML-built version here: > > https://momjian.us/pgsql_docs/release-18.html > ``` Add pg_dump options --with-schema, --with-data, and --with_statistics (Jeff Davis) § The negative versions of these options already existed. Add pg_dump option --sequence-data to dump sequence data that would normally be excluded (Nathan Bossart) § Add pg_dump, pg_dumpall, and pg_restore options --statistics-only, --no-statistics, --no-data, and --no-schema (Corey Huinker, Jeff Davis) § ```` in pg17, we only have "--schema-only", "--data-only", so description "The negative versions of these options already existed." is wrong? you can also see the above third item conflict with it. ``--with_statistics`` should be ``--with-statistics``. Add option --no-policies to pg_dump, pg_dumpall, pg_restore to avoid policy specification (Nikolay Samokhvalov) § This is useful for migrating to systems with different policies. generally, we should say "row level security policy" instead of "policy"? I think this sentence ( Add --no-policies option to control row level security policy handling in dump and restore operations.) in the commit message is good. maybe we can change it to ( Add --no-policies option to control row level security policy handling in pg_dump, pg_dumpall, pg_restore) Allow jsonb NULL values to be cast to scalar types as NULL (Tom Lane) § Previously such casts generated an error. here should be "jsonb null values", since we can not do ``select 'NULL'::jsonb;``
Allow partitions to be pruned more efficienty (Ashutosh Bapat, Yuya Watari, David Rowley) § § typo, "efficienty" should be "efficiently"?
On Tue, May 6, 2025 at 03:14:56PM +1200, David Rowley wrote: > On Tue, 6 May 2025 at 03:59, Bruce Momjian <bruce@momjian.us> wrote: > > > > On Mon, May 5, 2025 at 09:42:10PM +1200, David Rowley wrote: > > > I agree that 88f55bc97 and d69d45a5a should be in their own item. > > > Likely no need to go into detail about the speed up being about > > > "EquivalenceClass lookups". I imagine something like "Reduce planner > > > overheads when planning queries to partitioned and inheritance parent > > > tables" > > > > > > Then for bb3ec16e1, d47cbf474, cbc127917 and 525392d57, something like > > > "Defer locking of partitions during execution until after partition > > > elimination". The release notes for 11.0 called it "partition > > > elimination", so I went with that naming. > > > > Okay, I split them up and went with the attached patch. > > > > +Allow partitions to be pruned more efficienty (Ashutosh Bapat, Yuya Watari, David Rowley) > > I think you've misunderstood what's been changed here. Unfortunately, > it's not even true with a bit of eye squinting as these changes have > nothing to do with partition pruning. I think it would be much more I think what you are saying is that this has to do with partition processing of joins, but not the pruning process. I don't think a non-partition joins are likely to hit 32 EquivalenceClasses. > informative to state it as I suggested. Also, the spelling of > "efficiently" needs adjusted. Fixed. My spell check filter was wrong. > > +Avoid the locking of pruned partitions during planning (Amit Langote) > > At the very least, you'd need to swap "planning" for "execution" as > the above statement isn't true. I went with the attached patch. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
Attachment
On Tue, May 6, 2025 at 03:08:13PM +0900, Amit Langote wrote: > > I don't think most people would know what EquivalenceMember is, and even > > if they did, would they be able to connect it to an SQL query? > > Thanks for splitting these (cf847d634). I think the text for the > locking item should mention “during execution,” as David also > suggested. Again, I don’t think this change belongs under Optimizer > since it doesn’t really affect the planner -- it’s mainly an executor > improvement. Maybe the General Performance section is a better fit. Yes, good idea, done. > Also, just to clarify why the individual items are meaningful > performance improvements: > > * Locking change: Executing cached plans involving hundreds or > thousands of partitions was bottlenecked by locking; with this change, > execution is now roughly 20x faster with 1000 partitions. > > * Planning time improvements: Planning certain commonly used queries > against partitioned tables when they don’t use partition pruning is > now roughly 20x faster with 1000 partitions. That's impressive! -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
On Tue, May 6, 2025 at 10:18:27PM +0800, jian he wrote: > Allow partitions to be pruned more efficienty (Ashutosh Bapat, Yuya > Watari, David Rowley) § § > typo, "efficienty" should be "efficiently"? Yes, fixed from other email report. My spellcheck filter was broken. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
On Tue, May 6, 2025 at 10:13:36PM +0800, jian he wrote: > Add pg_dump options --with-schema, --with-data, and --with_statistics > (Jeff Davis) § > The negative versions of these options already existed. > > Add pg_dump option --sequence-data to dump sequence data that would > normally be excluded (Nathan Bossart) § > > Add pg_dump, pg_dumpall, and pg_restore options --statistics-only, > --no-statistics, --no-data, and --no-schema (Corey Huinker, Jeff > Davis) § > ```` > > in pg17, we only have "--schema-only", "--data-only", > so description "The negative versions of these options already > existed." is wrong? > you can also see the above third item conflict with it. > > ``--with_statistics`` should be ``--with-statistics``. > > > > Add option --no-policies to pg_dump, pg_dumpall, pg_restore to avoid > policy specification (Nikolay Samokhvalov) § > This is useful for migrating to systems with different policies. > > generally, we should say "row level security policy" instead of "policy"? > I think this sentence ( Add --no-policies option to control row level > security policy handling > in dump and restore operations.) in the commit message is good. > maybe we can change it to > ( Add --no-policies option to control row level security policy > handling in pg_dump, pg_dumpall, pg_restore) > > > > Allow jsonb NULL values to be cast to scalar types as NULL (Tom Lane) § > Previously such casts generated an error. > > here should be "jsonb null values", since we can not do ``select > 'NULL'::jsonb;`` All fixed in the attached applied patch. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
Attachment
On Wed, 7 May 2025 at 07:44, Bruce Momjian <bruce@momjian.us> wrote: > I think what you are saying is that this has to do with partition > processing of joins, but not the pruning process. I don't think a > non-partition joins are likely to hit 32 EquivalenceClasses. A query such as: SELECT * FROM table_with_lots_of_partitions ORDER BY col; will plan much faster now. No joins there. David
On Wed, May 7, 2025 at 09:27:14AM +1200, David Rowley wrote: > On Wed, 7 May 2025 at 07:44, Bruce Momjian <bruce@momjian.us> wrote: > > I think what you are saying is that this has to do with partition > > processing of joins, but not the pruning process. I don't think a > > non-partition joins are likely to hit 32 EquivalenceClasses. > > A query such as: SELECT * FROM table_with_lots_of_partitions ORDER BY > col; will plan much faster now. No joins there. Ah, interesting. I am guessing the ORDER BY requires the EquivalenceClasses items. Applied patch attached. I don't think we can make more precise wording for this item. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
Attachment
On Wed, 7 May 2025 at 13:17, Bruce Momjian <bruce@momjian.us> wrote: > > On Wed, May 7, 2025 at 09:27:14AM +1200, David Rowley wrote: > > On Wed, 7 May 2025 at 07:44, Bruce Momjian <bruce@momjian.us> wrote: > > A query such as: SELECT * FROM table_with_lots_of_partitions ORDER BY > > col; will plan much faster now. No joins there. > > Ah, interesting. I am guessing the ORDER BY requires the > EquivalenceClasses items. Applied patch attached. I don't think we can > make more precise wording for this item. The new wording looks good to me. David
On Fri, May 2, 2025 at 11:44 AM Bruce Momjian <bruce@momjian.us> wrote: > > I have committd the first draft of the PG 18 release notes. > I will continue improving it until beta 1, and until the final release. > I will probably add markup in 1-3 weeks. Let the feedback begin. ;-) Thanks for working on these. I'm wondering if we should consider mentioning that several long-standing issues related to grouping sets have been fixed starting from PostgreSQL v18. I understand that we typically don't include bug fixes in the release notes, but these particular issues have been present since grouping sets were first introduced, and currently they still exist in the back branches (yeah, for some reason we do not have back-branch fixes; we only fix them in v18 and going forward). There have been complaints from users about grouping sets giving incorrect results in certain cases, which has made them hesitant to use PostgreSQL for analytical workloads. Since these issues are fixed in v18, it might be worthwhile to point this out — if only to reassure users that these issues are no longer a concern going forward. (FWIW, the related commits are 247dea89f, f5050f795, and cc5d98525). Thanks Richard
On Thu, 1 May 2025 22:44:50 -0400 Bruce Momjian <bruce@momjian.us> wrote: > I have committd the first draft of the PG 18 release notes. The item > count looks strong: Thank you for working on this. > Have pgbench report the number of failed transactions (Yugo Nagata) I think that should be "Have pgbench report the number of failed, retried, or skipped transactions in per-script reports" because the number of failed, retried, or skip transactions was already reported in the main report, but these are now reported in per-script reports. Regards, Yugo Nagata -- Yugo Nagata <nagata@sraoss.co.jp>
On Wed, May 7, 2025 at 07:59:41PM +0900, Yugo Nagata wrote: > On Thu, 1 May 2025 22:44:50 -0400 > Bruce Momjian <bruce@momjian.us> wrote: > > > I have committd the first draft of the PG 18 release notes. The item > > count looks strong: > > Thank you for working on this. > > > Have pgbench report the number of failed transactions (Yugo Nagata) > > I think that should be > > "Have pgbench report the number of failed, retried, or skipped transactions > in per-script reports" > > because the number of failed, retried, or skip transactions was already reported > in the main report, but these are now reported in per-script reports. Good point, applied patch attached. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
Attachment
On Fri, 2 May 2025 at 14:44, Bruce Momjian <bruce@momjian.us> wrote: > You can see the most current HTML-built version here: > > https://momjian.us/pgsql_docs/release-18.html You might have left it out on purpose as the output isn't likely to be read by a machine, but for "Change pg_backend_memory_contexts.level to be one-based", d9e03864b applied the same change to pg_log_backend_memory_contexts(). David
On Thu, May 8, 2025 at 12:47:22PM +1200, David Rowley wrote: > On Thu, 8 May 2025 at 12:36, Bruce Momjian <bruce@momjian.us> wrote: > > +Change pg_backend_memory_contexts.level and pg_log_backend_memory_contexts()() to be one-based (Melih Mutlu, Fujii Masao) > > There's an extra set of parentheses on that function name. There's > also an inconsistency between the commit link you added and the author > you added. You've put the author of 706cbed3510 but the commit link > contains d9e03864b. Ah, I see those now. It was two commits. For some reason the edits are hard for me; applied patch attached. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
Attachment
On Fri, May 2, 2025 at 10:44 AM Bruce Momjian <bruce@momjian.us> wrote: > > release-18: 209 > > I will continue improving it until beta 1, and until the final release. > I will probably add markup in 1-3 weeks. Let the feedback begin. ;-) > > You can see the most current HTML-built version here: > > https://momjian.us/pgsql_docs/release-18.html > hi. Add OLD/NEW support to RETURNING in DML queries (Dean Rasheed) § Previously RETURNING only returned new values for INSERT and UPDATE, old values for DELETE; MERGE would return the appropriate value for the internal query executed. This new syntax allows INSERT with an ON CONFLICT action to return old values, UPDATE to return old values, and DELETE to return new values if the query assigned to an ON DELETE row would return new values. New syntax allows changeable relation aliases "old" and "new" to specify which values should be returned. -------------------------------------------------------- I am not sure I understand the last sentence, especially "changeable relation aliases". looking at the commit message, the following is what i come up with: Previously RETURNING only returned new values for INSERT and UPDATE, old values for DELETE; MERGE would return the appropriate value for the internal query executed. This allows the RETURNING list of INSERT/UPDATE/DELETE/MERGE queries to explicitly return old and new values by using the special aliases "old" and "new". There are no restrictions on the use of "old" and "new" in any DML queries.
On Thu, May 8, 2025 at 5:41 AM Bruce Momjian <bruce@momjian.us> wrote: > On Wed, May 7, 2025 at 03:03:32PM +0900, Richard Guo wrote: > > I'm wondering if we should consider mentioning that several > > long-standing issues related to grouping sets have been fixed starting > > from PostgreSQL v18. I understand that we typically don't include bug > > fixes in the release notes, but these particular issues have been > > present since grouping sets were first introduced, and currently they > > still exist in the back branches (yeah, for some reason we do not have > > back-branch fixes; we only fix them in v18 and going forward). > > > > There have been complaints from users about grouping sets giving > > incorrect results in certain cases, which has made them hesitant to > > use PostgreSQL for analytical workloads. Since these issues are fixed > > in v18, it might be worthwhile to point this out — if only to reassure > > users that these issues are no longer a concern going forward. > > > > (FWIW, the related commits are 247dea89f, f5050f795, and cc5d98525). > > Agreed, any fix that commonly used to return wrong results should be > mentioned. Applied patch attached. I'm not sure whether it's appropriate to put the incorrect-result fixes under the item "Allow some HAVING clauses on GROUPING SETS to be pushed to WHERE clauses", since that item is an optimization and does not fix these incorrect-result issues. But I'm also unsure where else they should go. Would it make sense to list them as a new item instead? Thanks Richard
On Thu, May 8, 2025 at 03:45:00PM +0800, jian he wrote: > On Fri, May 2, 2025 at 10:44 AM Bruce Momjian <bruce@momjian.us> wrote: > > > > release-18: 209 > > > > I will continue improving it until beta 1, and until the final release. > > I will probably add markup in 1-3 weeks. Let the feedback begin. ;-) > > > > You can see the most current HTML-built version here: > > > > https://momjian.us/pgsql_docs/release-18.html > > > > hi. > > Add OLD/NEW support to RETURNING in DML queries (Dean Rasheed) § > > Previously RETURNING only returned new values for INSERT and UPDATE, old values > for DELETE; MERGE would return the appropriate value for the internal query > executed. This new syntax allows INSERT with an ON CONFLICT action to return old > values, UPDATE to return old values, and DELETE to return new values if the > query assigned to an ON DELETE row would return new values. New syntax allows > changeable relation aliases "old" and "new" to specify which values should be > returned. > -------------------------------------------------------- > I am not sure I understand the last sentence, especially "changeable > relation aliases". > looking at the commit message, the following is what i come up with: > > Previously RETURNING only returned new values for INSERT and UPDATE, old values > for DELETE; MERGE would return the appropriate value for the internal query > executed. This allows the RETURNING list of INSERT/UPDATE/DELETE/MERGE queries > to explicitly return old and new values by using the special aliases "old" and > "new". There are no restrictions on the use of "old" and "new" in any DML > queries. Yes, I like your new text better than mine. I retained "This new syntax" so "This" is clear, and adjusted the last sentence to connect the alias with the ability to rename to avoid conflicts. Applied patch attached. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
Attachment
On Thu, May 8, 2025 at 07:46:11PM +0900, Richard Guo wrote: > On Thu, May 8, 2025 at 5:41 AM Bruce Momjian <bruce@momjian.us> wrote: > > On Wed, May 7, 2025 at 03:03:32PM +0900, Richard Guo wrote: > > > I'm wondering if we should consider mentioning that several > > > long-standing issues related to grouping sets have been fixed starting > > > from PostgreSQL v18. I understand that we typically don't include bug > > > fixes in the release notes, but these particular issues have been > > > present since grouping sets were first introduced, and currently they > > > still exist in the back branches (yeah, for some reason we do not have > > > back-branch fixes; we only fix them in v18 and going forward). > > > > > > There have been complaints from users about grouping sets giving > > > incorrect results in certain cases, which has made them hesitant to > > > use PostgreSQL for analytical workloads. Since these issues are fixed > > > in v18, it might be worthwhile to point this out — if only to reassure > > > users that these issues are no longer a concern going forward. > > > > > > (FWIW, the related commits are 247dea89f, f5050f795, and cc5d98525). > > > > Agreed, any fix that commonly used to return wrong results should be > > mentioned. Applied patch attached. > > I'm not sure whether it's appropriate to put the incorrect-result > fixes under the item "Allow some HAVING clauses on GROUPING SETS to > be pushed to WHERE clauses", since that item is an optimization and > does not fix these incorrect-result issues. > > But I'm also unsure where else they should go. Would it make sense to > list them as a new item instead? I put it there because that is the only mention of GROUPING SETS. If we create a separate item, would the text just be "This release fixes some GROUPING SETS queries that used to return incorrect results." Is there any pattern to the fix? Seems NULLs or subqueries were involved. Is this an incompatibility? Could people be relying on the old behavior? -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
Hi, Bruce, I have one comment, in E.1.3.4. Functions, crc32c also needs bracket. "Add functions crc32() and crc32c to compute CRC values" --> "Add functions crc32() and crc32c() to compute CRC values" Regards, Steven 在 2025/5/2 10:44, Bruce Momjian 写道: > I have committd the first draft of the PG 18 release notes. The item > count looks strong: > > release-7.4: 263 > release-8.0: 230 > release-8.1: 174 > release-8.2: 215 > release-8.3: 214 > release-8.4: 314 > release-9.0: 237 > release-9.1: 203 > release-9.2: 238 > release-9.3: 177 > release-9.4: 211 > release-9.5: 193 > release-9.6: 214 > release-10: 189 > release-11: 170 > release-12: 180 > release-13: 178 > release-14: 220 > release-15: 184 > release-16: 206 > release-17: 182 > release-18: 209 > > I will continue improving it until beta 1, and until the final release. > I will probably add markup in 1-3 weeks. Let the feedback begin. ;-) > > You can see the most current HTML-built version here: > > https://momjian.us/pgsql_docs/release-18.html >
On Fri, May 9, 2025 at 04:51:03PM +0800, Steven Niu wrote: > Hi, Bruce, > > I have one comment, in E.1.3.4. Functions, crc32c also needs bracket. > "Add functions crc32() and crc32c to compute CRC values" --> > "Add functions crc32() and crc32c() to compute CRC values" Thanks, fixed. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
On Thursday, May 1, 2025, Bruce Momjian <bruce@momjian.us> wrote:
I will continue improving it until beta 1, and until the final release.
I will probably add markup in 1-3 weeks. Let the feedback begin. ;-)
Should all columns removed from system views and/or catalogs be listed in “Migration” or is there some filtering criteria? There are at minimum quite a few statistics related ones we’ve dropped that only appear in the Changes section (e.g., pg_stat_io, pg_stat_wal).
David J.
On Mon, 2025-05-12 at 20:27 -0700, David G. Johnston wrote: > On Thursday, May 1, 2025, Bruce Momjian <bruce@momjian.us> wrote: > > I will continue improving it until beta 1, and until the final release. > > I will probably add markup in 1-3 weeks. Let the feedback begin. ;-) > > > Should all columns removed from system views and/or catalogs be listed in > “Migration” or is there some filtering criteria? There are at minimum quite > a few statistics related ones we’ve dropped that only appear in the Changes > section (e.g., pg_stat_io, pg_stat_wal). I am not sure. On the one hand, the catalogs don't promise to be a stable API, so there would be no need to enumerate such changes as compatibility breaks. The "Migration" section also doesn't list changes to the exported PostgreSQL functins, which has bitten me as extension developer several times. On the other hand, the catalogs are described in the documentation, which gives them more exposure, and it doesn't seem unreasonable to document breaking changes as well. Do you have an idea how many changes there are? If there are not too many, and somebody is willing to do the work, I wouldn't be against it. Yours, Laurenz Albe
On 2025-May-13, Laurenz Albe wrote: > On the one hand, the catalogs don't promise to be a stable API, so there > would be no need to enumerate such changes as compatibility breaks. I think it might be useful to distinguish changes to views, which are intended to be user-facing, from changes to "internal" catalogs, which aren't. I would think that it's reasonable to list changes to system views in the incompatibility section, but not changes to the system catalog tables. As related anecdote, the rename of columns in pg_stat_activity a few years back caused some pg_upgrade pain, which IMO was understandable; it's reasonable to call that kind of thing out as a possible incompatibility, at least IMO. > The "Migration" section also doesn't list changes to the exported > PostgreSQL functins, which has bitten me as extension developer several > times. Hmm. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "No me acuerdo, pero no es cierto. No es cierto, y si fuera cierto, no me acuerdo." (Augusto Pinochet a una corte de justicia)
On Thu, May 15, 2025 at 02:02:02PM -0400, Álvaro Herrera wrote: > As related anecdote, the rename of columns in pg_stat_activity a few > years back caused some pg_upgrade pain, which IMO was understandable; > it's reasonable to call that kind of thing out as a possible > incompatibility, at least IMO. > > > The "Migration" section also doesn't list changes to the exported > > PostgreSQL functins, which has bitten me as extension developer several > > times. > > Hmm. I have thought about such issues, and I feel there are so many items that would be interesting to extension developers that adding just a few to the release notes would not be very helpful, and if we add all of them it would be distracting for the majority of users. It might be helpful for someone to write a wiki page specifically for that audience. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
On Tue, May 13, 2025 at 08:05:24AM +0200, Laurenz Albe wrote: > On Mon, 2025-05-12 at 20:27 -0700, David G. Johnston wrote: > > Should all columns removed from system views and/or catalogs be listed in > > “Migration” or is there some filtering criteria? There are at minimum quite > > a few statistics related ones we’ve dropped that only appear in the Changes > > section (e.g., pg_stat_io, pg_stat_wal). > > I am not sure. > > On the one hand, the catalogs don't promise to be a stable API, so there > would be no need to enumerate such changes as compatibility breaks. > The "Migration" section also doesn't list changes to the exported > PostgreSQL functins, which has bitten me as extension developer several > times. > > On the other hand, the catalogs are described in the documentation, which > gives them more exposure, and it doesn't seem unreasonable to document > breaking changes as well. > > Do you have an idea how many changes there are? If there are not too many, > and somebody is willing to do the work, I wouldn't be against it. First, I apologize for the delay in my replying --- I was on vacation last week. Second, let me explain the criteria I use for table changes, and then we can discuss if the criteria is correct, and whether I followed the criteria accurately for PG 18. So, there are system views and system tables. Most system views are important to users, because we created them mostly for user consumption, while system tables might or might not hold useful information for users. Second, we have three possible changes --- column addition, column renaming, and column removal. And third, we can list the changes in the incompatibility section, or in the main release notes. So, for column additions, I would never list them in the incompatibility section, though it could break SELECT *. For renames and deletes, they would normally appear in the incompatibility section, unless they are system tables that do not normally hold user-helpful information, in which case I might list it in the main section, or not at all. I believe I followed that criteria for PG 18. There might be a few cases in PG 18 where columns used for monitoring were renamed or deleted because they were replaced, and I felt it was too complex to list them in the incompatibility section because there were new features mixed into the process so I listed them in the main section. I thought that was the proper balance. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
Hi, Thanks for working on this! On Fri, 2 May 2025 at 05:44, Bruce Momjian <bruce@momjian.us> wrote: > > I will continue improving it until beta 1, and until the final release. > I will probably add markup in 1-3 weeks. Let the feedback begin. ;-) +<listitem> +<para> +Add server variable file_copy_method to control the file copying method (Nazir Bilal Yavuz) +<ulink url="&commit_baseurl;f78ca6f3e">§</ulink> +</para> A minor fix; I co-authored this with Thomas Munro, he is the actual author. -- Regards, Nazir Bilal Yavuz Microsoft
Hi, On Tue, 20 May 2025 at 16:52, Bruce Momjian <bruce@momjian.us> wrote: > > On Tue, May 20, 2025 at 03:46:44PM +0300, Nazir Bilal Yavuz wrote: > > Hi, > > > > Thanks for working on this! > > > > On Fri, 2 May 2025 at 05:44, Bruce Momjian <bruce@momjian.us> wrote: > > > > > > I will continue improving it until beta 1, and until the final release. > > > I will probably add markup in 1-3 weeks. Let the feedback begin. ;-) > > > > +<listitem> > > +<para> > > +Add server variable file_copy_method to control the file copying > > method (Nazir Bilal Yavuz) > > +<ulink url="&commit_baseurl;f78ca6f3e">§</ulink> > > +</para> > > Uh, the commit is: > > commit f78ca6f3ebb > Author: Thomas Munro <tmunro@postgresql.org> > Date: Tue Apr 8 20:52:47 2025 +1200 > > Introduce file_copy_method setting. > > It can be set to either COPY (the default) or CLONE if the system > supports it. CLONE causes callers of copydir(), currently CREATE > DATABASE ... STRATEGY=FILE_COPY and ALTER DATABASE ... SET TABLESPACE = > ..., to use copy_file_range (Linux, FreeBSD) or copyfile (macOS) to copy > files instead of a read-write loop over the contents. > > CLONE gives the kernel the opportunity to share block ranges on > copy-on-write file systems and push copying down to storage on others, > depending on configuration. On some systems CLONE can be used to clone > large databases quickly with CREATE DATABASE ... TEMPLATE=source > STRATEGY=FILE_COPY. > > Other operating systems could be supported; patches welcome. > > Co-authored-by: Nazir Bilal Yavuz <byavuz81@gmail.com> > Reviewed-by: Robert Haas <robertmhaas@gmail.com> > Reviewed-by: Ranier Vilela <ranier.vf@gmail.com> > Discussion: https://postgr.es/m/CA%2BhUKGLM%2Bt%2BSwBU-cHeMUXJCOgBxSHLGZutV5zCwY4qrCcE02w%40mail.gmail.com > > As of the date of the commit, "Co-authored-by:" is listed as: > > https://wiki.postgresql.org/wiki/Commit_Message_Guidance > > "Co-authored-by:" is used by committers when they want to give full credit > to the named individuals, but also indicate that they made significant > changes. > > > A minor fix; I co-authored this with Thomas Munro, he is the actual author. > > Uh, does this mean I should add Thomas Munro before or after your name, > or remove your name and list only Thomas Munro? Sorry for taking your time, I did not know that. Then, I am okay with how it is right now. -- Regards, Nazir Bilal Yavuz Microsoft
On Fri, 2 May 2025 at 08:14, Bruce Momjian <bruce@momjian.us> wrote: > > I have committd the first draft of the PG 18 release notes. The item > count looks strong: > > I will continue improving it until beta 1, and until the final release. > I will probably add markup in 1-3 weeks. Let the feedback begin. ;-) Regarding the following: Change the default CREATE SUBSCRIPTION streaming option from "off" to "parallel" (Hayato Kuroda, Masahiko Sawada, Peter Smith, Amit Kapila) The author name was incorrectly listed in the commit; it should be "Vignesh C". This correction is noted in the follow-up to the commit email at [1]. [1] - https://www.postgresql.org/message-id/CAA4eK1K_M6%3DTB7sMKhBS5e819ePknB1_bT3a8LAnzV2dv64wjA%40mail.gmail.com Regards, Vignesh
On Fri, May 23, 2025 at 08:19:15AM +0530, vignesh C wrote: > On Fri, 2 May 2025 at 08:14, Bruce Momjian <bruce@momjian.us> wrote: > > > > I have committd the first draft of the PG 18 release notes. The item > > count looks strong: > > > > I will continue improving it until beta 1, and until the final release. > > I will probably add markup in 1-3 weeks. Let the feedback begin. ;-) > > Regarding the following: > Change the default CREATE SUBSCRIPTION streaming option from "off" to > "parallel" (Hayato Kuroda, Masahiko Sawada, Peter Smith, Amit Kapila) > > The author name was incorrectly listed in the commit; it should be > "Vignesh C". This correction is noted in the follow-up to the commit > email at [1]. > [1] - https://www.postgresql.org/message-id/CAA4eK1K_M6%3DTB7sMKhBS5e819ePknB1_bT3a8LAnzV2dv64wjA%40mail.gmail.com Ah, I see, fixed with the attached patch. It might be nice if we had a more organized way of recording such commit corrections. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
Attachment
On 2025-May-20, Bruce Momjian wrote: > On Tue, May 20, 2025 at 05:15:54PM +0300, Nazir Bilal Yavuz wrote: > > > As of the date of the commit, "Co-authored-by:" is listed as: > > > > > > https://wiki.postgresql.org/wiki/Commit_Message_Guidance > No problem. The "Co-authored-by:" guidance was only written down in > January of this year. I assume Thomas Munro was following that guidance > when he wrote the commit message. FWIW I have not taken to following that page yet, particularly because the chosen page title is pretty random, there are no links to it, and I couldn't find it when I searched for it; I wouldn't assume anyone is using it as if it were already gospel. Anyway, in order to contribute I added a few links to it, and also fixed the markup some. TBH I don't agree with everything the page says. For instance, I don't think we should care about what gitweb does with the commit title. gitweb is a dying interface and we'd do good to get rid of it, relying instead on cgit only. cgit shows more than 50 chars of the commit title, so I don't think there's a reason to limit oneself to 50 chars there. I also think that showing an XML-ish format of a commit message is unhelpful, not to mention hard to read. Showing a few actual examples would be better. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ ¡Ay, ay, ay! Con lo mucho que yo lo quería (bis) se fue de mi vera ... se fue para siempre, pa toíta ... pa toíta la vida ¡Ay Camarón! ¡Ay Camarón! (Paco de Lucía)
On Fri, May 23, 2025 at 10:46:09AM +0200, Álvaro Herrera wrote:
> On 2025-May-22, Bruce Momjian wrote:
>
> > Ah, I see, fixed with the attached patch. It might be nice if we had a
> > more organized way of recording such commit corrections.
>
> `git notes` can do that. They can also be used to indicate things such
> as commits being reverted.
That seems useful. This web page explains how it can be used:
https://stackoverflow.com/questions/20324417/git-get-all-notes-of-a-branch
I suppose I would get all of the notes that apply to the commits I am
reviewing.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
On Fri, May 23, 2025 at 09:54:54AM +0200, Álvaro Herrera wrote: > On 2025-May-20, Bruce Momjian wrote: > > > On Tue, May 20, 2025 at 05:15:54PM +0300, Nazir Bilal Yavuz wrote: > > > > As of the date of the commit, "Co-authored-by:" is listed as: > > > > > > > > https://wiki.postgresql.org/wiki/Commit_Message_Guidance > > > No problem. The "Co-authored-by:" guidance was only written down in > > January of this year. I assume Thomas Munro was following that guidance > > when he wrote the commit message. > > FWIW I have not taken to following that page yet, particularly because > the chosen page title is pretty random, there are no links to it, and I > couldn't find it when I searched for it; I wouldn't assume anyone is > using it as if it were already gospel. > > Anyway, in order to contribute I added a few links to it, and also fixed > the markup some. Great, thanks. > TBH I don't agree with everything the page says. For instance, I don't > think we should care about what gitweb does with the commit title. > gitweb is a dying interface and we'd do good to get rid of it, relying > instead on cgit only. cgit shows more than 50 chars of the commit > title, so I don't think there's a reason to limit oneself to 50 chars > there. Agreed, gitweb limit removed. > I also think that showing an XML-ish format of a commit message is > unhelpful, not to mention hard to read. Showing a few actual examples > would be better. Agreed, but the XML came from Joe Conway so I am hesitant to remove it myself without feedback from him. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
On Thu, May 22, 2025 at 10:24 PM Bruce Momjian <bruce@momjian.us> wrote: > I see your point that we are not defining what this does. I went with > the attached text. You propose the wording is "This allows muti-column btree indexes to be used by queries that only reference the second or later indexed columns". I think that your wording is a big improvement. I personally would have emphasized the absence of a "=" condition, rather than the presence of another condition on a later column, since there are cases where the first column is referenced but skip scan can still be used (e.g., when there one or more inequalities on the first column, plus a "=" condition on the second column). I can live with this wording, though. Thanks -- Peter Geoghegan
Hi, There seems to be some unexpected ">" here: "E.1.3.7.3. Logical Replication Applications>" ====== Kind Regards, Peter Smith. Fujitsu Australia
For the "Deprecate MD5 password authentication" item, IMHO we should
emphasize that support for MD5 passwords will be removed in a future major
release, as we did for the 18beta1 announcement. Here's an attempt:
Deprecate MD5 password authentication (Nathan Bossart)
Support for MD5 passwords will be removed in a future major version
release. CREATE ROLE and ALTER ROLE now emit deprecation warnings when
setting MD5 passwords. These warnings can be disabled by setting the
md5_password_warnings parameter to "off".
--
nathan
On Tue, May 27, 2025 at 09:26:41AM -0500, Nathan Bossart wrote: > For the "Deprecate MD5 password authentication" item, IMHO we should > emphasize that support for MD5 passwords will be removed in a future major > release, as we did for the 18beta1 announcement. Here's an attempt: > > Deprecate MD5 password authentication (Nathan Bossart) > > Support for MD5 passwords will be removed in a future major version > release. CREATE ROLE and ALTER ROLE now emit deprecation warnings when > setting MD5 passwords. These warnings can be disabled by setting the > md5_password_warnings parameter to "off". Agree, I have replaced the item text with your text. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
On Thu, 1 May 2025 22:44:50 -0400 Bruce Momjian <bruce@momjian.us> wrote: > I have committd the first draft of the PG 18 release notes. The item > count looks strong: Some items in the "EXPLAIN" section are actually not about the EXPLAIN but the ANALYZE command. The attached patch is move them to the "Utility Commands" section with a little edit of wordings. Regards, Yugo Nagata -- Yugo Nagata <nagata@sraoss.co.jp>
Attachment
On Wed, May 28, 2025 at 10:55:50AM +0100, Dean Rasheed wrote: > For "Improve the speed of multiplication", I think it should say > "numeric multiplication" rather than simply "multiplication", and I > think it's worth also linking to commits ca481d3c9ab and c4e44224cf6 > which were part of the same work. > > I think it's also worth mentioning 9428c001f67, which sped up numeric > division. That can be included in the same item, as in the attached > patch, unless you think it's worth listing it as a separate item. Great, patch applied. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
On 5/27/25 17:27, Bruce Momjian wrote: > On Mon, May 26, 2025 at 10:20:08AM -0400, Joe Conway wrote: >> On 5/23/25 09:47, Bruce Momjian wrote: >> > On Fri, May 23, 2025 at 09:54:54AM +0200, Álvaro Herrera wrote: >> > > I also think that showing an XML-ish format of a commit message is >> > > unhelpful, not to mention hard to read. Showing a few actual examples >> > > would be better. >> > >> > Agreed, but the XML came from Joe Conway so I am hesitant to remove it >> > myself without feedback from him. >> >> >> I am not married to it, but I would say that I find pure examples >> confusing/ambiguous. To me at least the XML-ish specification is easier to >> understand. >> >> Perhaps both the specification as-is and one or two examples added? > > Yeah, I think some people like syntax, others like examples. What do you think about providing links into the archives for good representative log messages rather than making up a contrived example and/or copy/pasting them into the wiki page itself? Also, looking at the wiki page, my inclination would be to add an "Examples" section at the bottom of the wiki page -- does that work or do you think it ought to go just under the "General layout" syntax section? -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
On Wed, May 28, 2025 at 02:54:29PM -0400, Joe Conway wrote:
> On 5/27/25 17:27, Bruce Momjian wrote:
> > On Mon, May 26, 2025 at 10:20:08AM -0400, Joe Conway wrote:
> > > On 5/23/25 09:47, Bruce Momjian wrote:
> > > > On Fri, May 23, 2025 at 09:54:54AM +0200, Álvaro Herrera wrote:
> > > > > I also think that showing an XML-ish format of a commit message is
> > > > > unhelpful, not to mention hard to read. Showing a few actual examples
> > > > > would be better.
> > > > > Agreed, but the XML came from Joe Conway so I am hesitant to
> > > remove it
> > > > myself without feedback from him.
> > >
> > >
> > > I am not married to it, but I would say that I find pure examples
> > > confusing/ambiguous. To me at least the XML-ish specification is easier to
> > > understand.
> > >
> > > Perhaps both the specification as-is and one or two examples added?
> >
> > Yeah, I think some people like syntax, others like examples.
>
> What do you think about providing links into the archives for good
> representative log messages rather than making up a contrived example and/or
> copy/pasting them into the wiki page itself?
Sure, we can just link to:
https://www.postgresql.org/list/pgsql-committers/
and they can choose any recent commit.
> Also, looking at the wiki page, my inclination would be to add an "Examples"
> section at the bottom of the wiki page -- does that work or do you think it
> ought to go just under the "General layout" syntax section?
Sure, makes sense.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
On Thu, May 1, 2025 at 10:44 PM Bruce Momjian <bruce@momjian.us> wrote: > > I will continue improving it until beta 1, and until the final release. Hi Bruce, Thanks so much for putting these together. For the item: "Increase the logging granularity of server variable log_connections" I noticed that you cite the commit 9219093cab2 that actually does modularize the GUC but you also cite a separate following commit 18cd15e706ac which adds a new option that logs the duration of various parts of connection establishment and backend setup. That is, it is a separate feature. 9219093cab2 made it so we could add options and have them be individually enabled or disabled in logging. But 18cd15e706ac is only related insomuch as we probably wouldn't have added it if log_connections had been just a boolean and it was enabled by default. Anyway, it might be worth separately calling out that now you can configure log_connections to log the durations of various parts of connection establishment and backend setup -- which is a distinct feature from modularization. For the item: "Add an asynchronous I/O subsystem" I notice we don't call out any of the operations where users could expect to see asynchronous IO be used. Some were enabled in 17 (like sequential scans, analyze, and pg_prewarm), but most of the read stream users went in this release: d9c7911e1a5, 043799fa08c, e215166c9c8, 69273b818b1, c5c239e26e3, 2b73a8cd33b, 9256822608f, c3e775e608f, 8720a15e9ab12, 65c310b310a I have had users ask me already which operations they can expect to use asynchronous I/O. The most commonly encountered AIO operations are probably be vacuum, bitmap heap scan, and sequential scans, but it might be worth having a list somewhere of what uses AIO. I expect we'll get the question quite often. And finally, for the item: "Allow specification of the fixed number of dead tuples that will trigger an autovacuum" We also added a kind of corollary for insert-triggered vacuums in 06eae9e6218ab2a which attempts to deal with a similar problem of big tables not being autovacuumed enough but for insert-mostly tables. Perhaps because there is no exposed configuration it is not worth mentioning, but I thought I would bring it up since their purposes are related. - Melanie
On Wed, May 28, 2025 at 10:49 PM Bruce Momjian <bruce@momjian.us> wrote: > > On Wed, May 28, 2025 at 08:07:20PM -0400, Melanie Plageman wrote: > > Yes, I can now see it is two items so I have split it into two in the > attached, applied patch. In a separate commit I adjusted the docs for > log_connections to more clearly explain the new "setup_durations" > output. Cool, thanks! > > "Add an asynchronous I/O subsystem" > > > > I notice we don't call out any of the operations where users could > > expect to see asynchronous IO be used. Some were enabled in 17 (like > > sequential scans, analyze, and pg_prewarm), but most of the read > > stream users went in this release: > > > > d9c7911e1a5, 043799fa08c, e215166c9c8, 69273b818b1, c5c239e26e3, > > 2b73a8cd33b, 9256822608f, c3e775e608f, 8720a15e9ab12, 65c310b310a > > > > I have had users ask me already which operations they can expect to > > use asynchronous I/O. The most commonly encountered AIO operations are > > probably be vacuum, bitmap heap scan, and sequential scans, but it > > might be worth having a list somewhere of what uses AIO. I expect > > we'll get the question quite often. > > Yes, I knew I needed more detail on this. I have added text in this > commit to try to improve that. Maybe it is worth saying something at the end like "amongst other operations" to clarify it isn't just those. I noticed in the PG 17 release notes [1] we did include the shas of each of the commits for the read stream users. Should we do that here as well? Those are what enable those operations to use AIO. > > "Allow specification of the fixed number of dead tuples that will > > trigger an autovacuum" > > > > We also added a kind of corollary for insert-triggered vacuums in > > 06eae9e6218ab2a which attempts to deal with a similar problem of big > > tables not being autovacuumed enough but for insert-mostly tables. > > Perhaps because there is no exposed configuration it is not worth > > mentioning, but I thought I would bring it up since their purposes are > > related. > > I studied this and I can't figure out how to clearly explain it in a > useful way. I am now thinking it is more of a bug or behavior fix or > that would not be usually mentioned. Makes sense - Melanie [1] https://www.postgresql.org/docs/current/release-17.html
On Thu, May 01, 2025 at 10:44:50PM -0400, Bruce Momjian wrote: > I have committd the first draft of the PG 18 release notes. When a commit changes the user that runs a function in existing queries, I think that almost always needs a release notes entry. It would follow that commit 01463e1 needs an entry. I recommend text "Run each deferred trigger as the role that caused the trigger to fire."
On Tue, Jun 3, 2025 at 10:21:23AM -0700, Noah Misch wrote:
> On Thu, May 01, 2025 at 10:44:50PM -0400, Bruce Momjian wrote:
> > I have committd the first draft of the PG 18 release notes.
>
> When a commit changes the user that runs a function in existing queries, I
> think that almost always needs a release notes entry. It would follow that
> commit 01463e1 needs an entry. I recommend text "Run each deferred trigger as
> the role that caused the trigger to fire."
Okay, let's look at the commit:
commit 01463e1cccd
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thu Jan 23 12:25:45 2025 -0500
Ensure that AFTER triggers run as the instigating user.
With deferred triggers, it is possible that the current role changes
between the time when the trigger is queued and the time it is
executed (for example, the triggering data modification could have
been executed in a SECURITY DEFINER function).
Up to now, deferred trigger functions would run with the current role
set to whatever was active at commit time. That does not matter for
foreign-key constraints, whose correctness doesn't depend on the
current role. But for user-written triggers, the current role
certainly can matter.
Hence, fix things so that AFTER triggers are fired under the role
that was active when they were queued, matching the behavior of
BEFORE triggers which would have actually fired at that time.
(If the trigger function is marked SECURITY DEFINER, that of course
overrides this, as it always has.)
This does not create any new security exposure: if you do DML on a
table owned by a hostile user, that user has always had various ways
to exploit your permissions, such as the aforementioned BEFORE
triggers, default expressions, etc. It might remove some security
exposure, because the old behavior could potentially expose some
other role besides the one directly modifying the table.
There was discussion of making a larger change, such as running as
the trigger's owner. However, that would break the common idiom of
capturing the value of CURRENT_USER in a trigger for auditing/logging
purposes. This change will make no difference in the typical scenario
where the current role doesn't change before commit.
Arguably this is a bug fix, but it seems too big a semantic change
to consider for back-patching.
Author: Laurenz Albe <laurenz.albe@cybertec.at>
Reviewed-by: Joseph Koshakow <koshy44@gmail.com>
Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com>
Discussion: https://postgr.es/m/77ee784cf248e842f74588418f55c2931e47bd78.camel@cybertec.at
There are two questions --- should it be mentioned in the release notes,
and should it be listed in the incompatibility section.
It is called a bug fix, which I think means it is just implementing a
behavior that users already expected. (Yes, there is a doc addition to
clarify this.) I thought it was an edge case that didn't warrant
mention in the release notes, and the rare cases would be caught in
application testing.
Now, if we do want to mention it, it should be done in a way that makes
it clear to readers whether they are affected by this change. We can
try text like:
Execute non-SECURITY-DEFINER AFTER triggers as the role that was
active at the time the trigger was fired
Previously such triggers were run as the role that was active at
commit time.
Seems like this would be in the incompatibility section, if we want to
add it.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
On Wed, Jun 4, 2025 at 1:45 PM Bruce Momjian <bruce@momjian.us> wrote:
Now, if we do want to mention it, it should be done in a way that makes
it clear to readers whether they are affected by this change. We can
try text like:
Execute non-SECURITY-DEFINER AFTER triggers as the role that was
active at the time the trigger was fired
Previously such triggers were run as the role that was active at
commit time.
"Deferred constraint triggers now run as the role active when the trigger was fired: previously they used the role active when execution began."
The timing is not only at commit, and it makes more sense to me to focus on "deferred constraint" instead of the more general "after" trigger.
The trigger doesn't have a security definer clause - the function does and would of course take effect during execution. Not strongly opposed to keeping the note.
David J.
On Wed, Jun 04, 2025 at 04:45:18PM -0400, Bruce Momjian wrote: > On Tue, Jun 3, 2025 at 10:21:23AM -0700, Noah Misch wrote: > > When a commit changes the user that runs a function in existing queries, I > > think that almost always needs a release notes entry. It would follow that > > commit 01463e1 needs an entry. I recommend text "Run each deferred trigger as > > the role that caused the trigger to fire." > There are two questions --- should it be mentioned in the release notes, > and should it be listed in the incompatibility section. > > It is called a bug fix, which I think means it is just implementing a > behavior that users already expected. (Yes, there is a doc addition to > clarify this.) I thought it was an edge case that didn't warrant > mention in the release notes, and the rare cases would be caught in > application testing. > > Now, if we do want to mention it, it should be done in a way that makes > it clear to readers whether they are affected by this change. We can > try text like: > > Execute non-SECURITY-DEFINER AFTER triggers as the role that was > active at the time the trigger was fired > > Previously such triggers were run as the role that was active at > commit time. I agree with David G. Johnston's feedback on this. My draft didn't mention SECURITY DEFINER, because I consider it redundant from a user's perspective. If a function is SECURITY DEFINER, that always overrides other sources of user identity. No need to mention it each time. That said, I'm not too picky about the exact wording. The way you have it wouldn't bother me. > Seems like this would be in the incompatibility section, if we want to > add it. Works for me.
On Wed, Jun 4, 2025 at 02:02:38PM -0700, David G. Johnston wrote:
> On Wed, Jun 4, 2025 at 1:45 PM Bruce Momjian <bruce@momjian.us> wrote:
>
> Now, if we do want to mention it, it should be done in a way that makes
> it clear to readers whether they are affected by this change. We can
> try text like:
>
> Execute non-SECURITY-DEFINER AFTER triggers as the role that was
> active at the time the trigger was fired
>
> Previously such triggers were run as the role that was active at
> commit time.
>
>
> "Deferred constraint triggers now run as the role active when the trigger was
> fired: previously they used the role active when execution began."
>
> The timing is not only at commit, and it makes more sense to me to focus on
> "deferred constraint" instead of the more general "after" trigger.
Ah, yes, I see your point --- it is really only deferred after triggers,
not before triggers, and deferrability is only available for constraint
triggers.
> The trigger doesn't have a security definer clause - the function does and
> would of course take effect during execution. Not strongly opposed to keeping
> the note.
I think we have to keep the non-SECURITY-DEFINER designation to keep the
text accurate, but you are right it is part of the function, not the
trigger:
Execute deferred constraint triggers attached to
non-SECURITY-DEFINER functions as the role that was active at
the time the trigger was fired
Previously such triggers were run as the role that was active at
commit/execution time.
As you can see, this is going to be hard to read, and I don't know if a
sufficient number of people will care.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
On Wed, Jun 4, 2025 at 02:29:46PM -0700, Noah Misch wrote: > I agree with David G. Johnston's feedback on this. My draft didn't mention > SECURITY DEFINER, because I consider it redundant from a user's perspective. > If a function is SECURITY DEFINER, that always overrides other sources of user > identity. No need to mention it each time. Well, if it is a SECURITY DEFINER function, it is not going to be run as the user who is active at commit/execution time, so I think we have to specify that. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
Bruce Momjian <bruce@momjian.us> writes:
> I think we have to keep the non-SECURITY-DEFINER designation to keep the
> text accurate, but you are right it is part of the function, not the
> trigger:
> Execute deferred constraint triggers attached to
> non-SECURITY-DEFINER functions as the role that was active at
> the time the trigger was fired
> Previously such triggers were run as the role that was active at
> commit/execution time.
> As you can see, this is going to be hard to read, and I don't know if a
> sufficient number of people will care.
It's still inaccurate -- to my mind, a "deferred" trigger is one that
runs later than the end of the triggering statement. I think you
should use "after trigger". Also, "fired" is a fairly confusing
choice of word here; I think most people would take that as meaning
the act of running the trigger. How about
Execute AFTER triggers as the role that was active at the
moment the trigger event was queued
Previously such triggers were run as the role that is active
when it is time to execute the trigger (e.g., at COMMIT).
I concur that this needs to be called out as an incompatibility.
regards, tom lane
On Wed, Jun 04, 2025 at 06:10:38PM -0400, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > I think we have to keep the non-SECURITY-DEFINER designation to keep the > > text accurate, I don't see it that way, because there's currently no experiment the user can perform to distinguish between the following alternatives: 1. We switch to the user that fired the trigger, then call the SECURITY DEFINER function. As always, the first step of executing a SECURITY DEFINER function is to switch to its owner. 2. We know it's a SECURITY DEFINER function, so we don't switch. We just call the SECURITY DEFINER function. As always, the first step of executing a SECURITY DEFINER function is to switch to its owner. The actual implementation is (1), for what it's worth. The src/backend part of the commit didn't special-case SECURITY DEFINER. > but you are right it is part of the function, not the > > trigger: > > > Execute deferred constraint triggers attached to > > non-SECURITY-DEFINER functions as the role that was active at > > the time the trigger was fired > > > Previously such triggers were run as the role that was active at > > commit/execution time. > It's still inaccurate -- to my mind, a "deferred" trigger is one that > runs later than the end of the triggering statement. I think you > should use "after trigger". Also, "fired" is a fairly confusing > choice of word here; I think most people would take that as meaning > the act of running the trigger. How about > > Execute AFTER triggers as the role that was active at the > moment the trigger event was queued > > Previously such triggers were run as the role that is active > when it is time to execute the trigger (e.g., at COMMIT). I like that.
On Wed, Jun 4, 2025 at 3:17 PM Noah Misch <noah@leadboat.com> wrote:
On Wed, Jun 04, 2025 at 06:10:38PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> but you are right it is part of the function, not the
> > trigger:
>
> > Execute deferred constraint triggers attached to
> > non-SECURITY-DEFINER functions as the role that was active at
> > the time the trigger was fired
>
> > Previously such triggers were run as the role that was active at
> > commit/execution time.
> It's still inaccurate -- to my mind, a "deferred" trigger is one that
> runs later than the end of the triggering statement. I think you
> should use "after trigger".
Is this covering the case of executing at the end of an outer SQL command (thus not deferred) that contains volatile DML functions that temporarily change current_user within the function?
Also, "fired" is a fairly confusing
> choice of word here; I think most people would take that as meaning
> the act of running the trigger.
Agreed
How about
>
> Execute AFTER triggers as the role that was active at the
> moment the trigger event was queued
>
> Previously such triggers were run as the role that is active
> when it is time to execute the trigger (e.g., at COMMIT).
I like that.
I do as well.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Is this covering the case of executing at the end of an outer SQL command
> (thus not deferred) that contains volatile DML functions that temporarily
> change current_user within the function?
Not quite. I think that a non-deferred AFTER trigger would ordinarily
run as the same user that was active when we queued the event, earlier
in the same statement --- but it's possible that some function that
runs in between would change the active role in a non-temporary way.
Doing that will now have different effects than it did before.
regards, tom lane
On Wed, Jun 4, 2025 at 06:37:57PM -0400, Tom Lane wrote: > "David G. Johnston" <david.g.johnston@gmail.com> writes: > > Is this covering the case of executing at the end of an outer SQL command > > (thus not deferred) that contains volatile DML functions that temporarily > > change current_user within the function? > > Not quite. I think that a non-deferred AFTER trigger would ordinarily > run as the same user that was active when we queued the event, earlier > in the same statement --- but it's possible that some function that > runs in between would change the active role in a non-temporary way. > Doing that will now have different effects than it did before. Yes, I wonder if we need to work that angle into the description. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
On Wed, Jun 4, 2025 at 05:53:38PM -0400, Bruce Momjian wrote:
> On Wed, Jun 4, 2025 at 02:29:46PM -0700, Noah Misch wrote:
> > I agree with David G. Johnston's feedback on this. My draft didn't mention
> > SECURITY DEFINER, because I consider it redundant from a user's perspective.
> > If a function is SECURITY DEFINER, that always overrides other sources of user
> > identity. No need to mention it each time.
>
> Well, if it is a SECURITY DEFINER function, it is not going to be run as
> the user who is active at commit/execution time, so I think we have to
> specify that.
I came up with this text:
Execute AFTER triggers as the role that was active when trigger
events were queued
Previously such triggers were run as the role that was active at
trigger execution time (e.g., at COMMIT). This is significant
for cases where the role is changed between queue time and
transaction commit.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
On Thu, Jun 5, 2025 at 08:32:44PM -0400, Bruce Momjian wrote: > On Wed, Jun 4, 2025 at 05:53:38PM -0400, Bruce Momjian wrote: > > On Wed, Jun 4, 2025 at 02:29:46PM -0700, Noah Misch wrote: > > > I agree with David G. Johnston's feedback on this. My draft didn't mention > > > SECURITY DEFINER, because I consider it redundant from a user's perspective. > > > If a function is SECURITY DEFINER, that always overrides other sources of user > > > identity. No need to mention it each time. > > > > Well, if it is a SECURITY DEFINER function, it is not going to be run as > > the user who is active at commit/execution time, so I think we have to > > specify that. > > I came up with this text: > > Execute AFTER triggers as the role that was active when trigger > events were queued > > Previously such triggers were run as the role that was active at > trigger execution time (e.g., at COMMIT). This is significant > for cases where the role is changed between queue time and > transaction commit. Item added to the incompatibilities section of the release notes. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
On Thu, May 29, 2025 at 12:41 PM Bruce Momjian <bruce@momjian.us> wrote: > > On Thu, May 29, 2025 at 09:42:30AM -0400, Melanie Plageman wrote: > > > > "Add an asynchronous I/O subsystem" > > I noticed in the PG 17 release notes [1] we did include the shas of > > each of the commits for the read stream users. Should we do that here > > as well? Those are what enable those operations to use AIO. > > So, I added the read stream item to PG 17 since that was a new > infrastructure feature, but for PG 18, we are just improving that > internal infrastructure, so didn't mention it. If you think we should > add those commits, I can do it. I wouldn't consider it as improving internal infrastructure exactly. My understanding is that usually when we add new features, even if they use an existing API, we still include them, as relevant, in the release notes. For example, next release we hope to have index prefetching -- but it may be implemented using the read stream API. Does that mean we wouldn't mention it in the release notes? - Melanie