agora inbox for [email protected]  

help / color / mirror / Atom feed

[PATCH v4] fix HOT tuples while scanning for index builds
37+ messages / 7 participants
[nested] [flat]

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* [PATCH v4] fix HOT tuples while scanning for index builds
@ 2020-08-12 18:02 Alvaro Herrera <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Alvaro Herrera @ 2020-08-12 18:02 UTC (permalink / raw)

---
 src/backend/access/heap/heapam_handler.c | 20 ++++++++++++++++++++
 src/backend/access/heap/pruneheap.c      |  5 +++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 267a6ee25a..ba44e30035 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1324,6 +1324,12 @@ heapam_index_build_range_scan(Relation heapRelation,
 		 * buffer continuously while visiting the page, so no pruning
 		 * operation can occur either.
 		 *
+		 * In cases with only ShareUpdateExclusiveLock on the table, it's
+		 * possible for some HOT tuples to appear that we didn't know about
+		 * when we first read the page.  To handle that case, we re-obtain the
+		 * list of root offsets when a HOT tuple points to a root item that we
+		 * don't know about.
+		 *
 		 * Also, although our opinions about tuple liveness could change while
 		 * we scan the page (due to concurrent transaction commits/aborts),
 		 * the chain root locations won't, so this info doesn't need to be
@@ -1625,6 +1631,20 @@ heapam_index_build_range_scan(Relation heapRelation,
 
 			offnum = ItemPointerGetOffsetNumber(&heapTuple->t_self);
 
+			/*
+			 * If a HOT tuple points to a root that we don't know
+			 * about, obtain root items afresh.  If that still fails,
+			 * report it as corruption.
+			 */
+			if (root_offsets[offnum - 1] == InvalidOffsetNumber)
+			{
+				Page	page = BufferGetPage(hscan->rs_cbuf);
+
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
+				heap_get_root_tuples(page, root_offsets);
+				LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
+			}
+
 			if (!OffsetNumberIsValid(root_offsets[offnum - 1]))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATA_CORRUPTED),
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index 256df4de10..7e3d44dfd6 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -732,7 +732,7 @@ heap_page_prune_execute(Buffer buffer,
  * root_offsets[k - 1] = j.
  *
  * The passed-in root_offsets array must have MaxHeapTuplesPerPage entries.
- * We zero out all unused entries.
+ * Unused entries are filled with InvalidOffsetNumber (zero).
  *
  * The function must be called with at least share lock on the buffer, to
  * prevent concurrent prune operations.
@@ -747,7 +747,8 @@ heap_get_root_tuples(Page page, OffsetNumber *root_offsets)
 	OffsetNumber offnum,
 				maxoff;
 
-	MemSet(root_offsets, 0, MaxHeapTuplesPerPage * sizeof(OffsetNumber));
+	MemSet(root_offsets, InvalidOffsetNumber,
+		   MaxHeapTuplesPerPage * sizeof(OffsetNumber));
 
 	maxoff = PageGetMaxOffsetNumber(page);
 	for (offnum = FirstOffsetNumber; offnum <= maxoff; offnum = OffsetNumberNext(offnum))
-- 
2.20.1


--opJtzjQTFsWo+cga--





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* PostgreSQL 16 release announcement draft
@ 2023-08-19 19:38 Jonathan S. Katz <[email protected]>
  2023-08-20 02:18 ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  0 siblings, 1 reply; 37+ messages in thread

From: Jonathan S. Katz @ 2023-08-19 19:38 UTC (permalink / raw)
  To: PostgreSQL Hackers <[email protected]>; +Cc: [email protected]

Hi,

Attached is the first draft of the PostgreSQL 16 release announcement, 
authored by Chelsea Dole & myself.

To frame this up, the goal of the GA release announcement is to help 
folks discover the awesome new features of PostgreSQL. It's impossible 
to list out every single feature in the release and still have a 
coherent announcement, so we try to target features that have the 
broadest range of impact.

It's possible we missed or incorrectly stated something, so please 
provide feedback if we did so.

(Note I have not added in all of the links etc. to the Markdown yet, as 
I want to wait for the first pass of feedback to come through).

**Please provide feedback by August 26, 12:00 UTC**. After that point, 
we need to freeze all changes so we can begin the release announcement 
translation effort.

Thanks,

Jonathan

September 14, 2023 - The PostgreSQL Global Development Group today announced the
release of PostgreSQL 16, the latest version of the world’s most advanced open
source database.

PostgreSQL 16 raises its performance, with notable improvements to query
parallelism, bulk data loading, and logical replication. There are many features
in this release for developers and administrators alike, including more SQL/JSON
syntax, new monitoring stats for your workloads, and greater flexibility in
defining access control rules for large scale workloads.

<HOLD FOR QUOTE>

PostgreSQL, an innovative data management system known for its reliability and
robustness, benefits from over 25 years of open source development from a global
developer community and has become the preferred open source relational database
for organizations of all sizes.

### Performance Improvements

PostgreSQL 16 improves the performance of existing PostgreSQL functionality
through new query planner optimizations. In this latest release, the query
planner can parallelize  `FULL` and `RIGHT` joins, utilize incremental sorts for
`SELECT DISTINCT` queries, and execute window functions more efficiently. It
also introduces `RIGHT` and `OUTER` "anti-joins", which enable users to identify
rows not present in a joined table.

This release includes improvements for bulk loading using `COPY` in both single
and concurrent operations, with tests showing up to a 300% performance
improvement in some cases. PostgreSQL adds support for load balancing in clients
that use `libpq`, and improvements to vacuum strategy that reduce the necessity
of full-table freezes. Additionally, PostgreSQL 16 introduces CPU acceleration
using `SIMD` in both x86 and ARM architectures, resulting in performance gains
when processing ASCII and JSON strings, and performing array and subtransaction
searches.

### Logical replication 

Logical replication lets PostgreSQL users stream data to other PostgreSQL
instances or subscribers that can interpret the PostgreSQL logical replication
protocol. In PostgreSQL 16, users can perform logical decoding from a standby
instance, meaning a standby can publish logical changes to other servers. This
provides developers with new workload distribution options – for example, using
a standby rather than the busier primary to logically replicate changes to
downstream systems.

Additionally, there are several performance improvements in PostgreSQL 16 to
logical replication. Subscribers can now apply large transactions using parallel
workers. For tables that do not have a `PRIMARY KEY`, subscribers can use B-tree
indexes instead of sequential scans to find rows. Under certain conditions,
users can also speed up initial table synchronization using the binary format.

There are several access control improvements to logical replication in
PostgreSQL 16, including the new predefined role pg_create_subscription, which
grants users the ability to create a new logical subscription. Finally, this
release begins adding support for bidirectional logical replication, introducing
functionality to replicate data between two tables from different publishers.

### Developer Experience

PostgreSQL 16 adds more syntax from the SQL/JSON standard, including
constructors and predicates such as `JSON_ARRAY()`, `JSON_ARRAYAGG()`, and
`IS JSON`. This release also introduces the ability to use underscores for
thousands separators (e.g. `5_432_000`) and non-decimal integer literals, such
as `0x1538`, `0o12470`, and `0b1010100111000`.

Developers using PostgreSQL 16 will also benefit from the addition of multiple
commands to `psql` client protocol, including the `\bind` command, which allows
users to execute parameterized queries (e.g `SELECT $1 + $2`) then use `\bind`
to substitute the variables. 

PostgreSQL 16 improves general support for text collations, which provide rules
for how text is sorted. PostgreSQL 16 builds with ICU support by default,
determines the default ICU locale from the environment, and allows users to
define custom ICU collation rules.

### Monitoring

A key aspect of tuning the performance of database workloads is understanding
the impact of your I/O operations on your system. PostgreSQL 16 helps simplify
how you can analyze this data with the new pg_stat_io view, which tracks key I/O
statistics such as shared_buffer hits and I/O latency.

Additionally, this release adds a new field to the pg_stat_all_tables view,
capturing a timestamp representing when a table or index was last scanned.
PostgreSQL also makes auto_explain more readable by logging values passed into
parameterized statements, and improves accuracy of pg_stat_activity’s
normalization algorithm.

### Access Control & Security

PostgreSQL 16 provides finer-grained options for access control and enhances
other security features. The release improves management of `pg_hba.conf` and
`pg_ident.conf` files, including allowing regular expression matching for user
and database names and "include" directives for external configuration files.

This release adds several security-oriented client connection parameters,
including require_auth, which allows clients to specify which authentication
parameters they are willing to accept from a server, and `sslrootcert="system"`,
which indicates that PostgreSQL should use the trusted certificate authority
(CA) store provided by the client’s operating system. Additionally, the release
adds support for Kerberos credential delegation, allowing extensions such as
`postgres_fdw` and `dblink` to use authenticated credentials to connect to
trusted services.

### About PostgreSQL

[PostgreSQL](https://www.postgresql.org) is the world's most advanced open
source database, with a global community of thousands of users, contributors,
companies and organizations. Built on over 35 years of engineering, starting at
the University of California, Berkeley, PostgreSQL has continued with an
unmatched pace of development. PostgreSQL's mature feature set not only matches
top proprietary database systems, but exceeds them in advanced database
features, extensibility, security, and stability.

### Links

* [Download](https://www.postgresql.org/download/)
* [Release Notes](https://www.postgresql.org/docs/16/release-16.html)
* [Press Kit](https://www.postgresql.org/about/press/)
* [Security Page](https://www.postgresql.org/support/security/)
* [Versioning Policy](https://www.postgresql.org/support/versioning/)
* [Follow @postgresql](https://twitter.com/postgresql)
* [Donate](https://www.postgresql.org/about/donate/)

## More About the Features

For explanations of the above features and others, please see the following
resources:

* [Release Notes](https://www.postgresql.org/docs/16/release-16.html)
* [Feature Matrix](https://www.postgresql.org/about/featurematrix/)

## Where to Download

There are several ways you can download PostgreSQL 16, including:

* The [Official Downloads](https://www.postgresql.org/download/) page, with contains installers and tools for [Windows](https://www.postgresql.org/download/windows/), [Linux](https://www.postgresql.org/download/), [macOS](https://www.postgresql.org/download/macosx/), and more.
* [Source Code](https://www.postgresql.org/ftp/source/v16.0)

Other tools and extensions are available on the
[PostgreSQL Extension Network](http://pgxn.org/).

## Documentation

PostgreSQL 16 comes with HTML documentation as well as man pages, and you can also browse the documentation online in both [HTML](https://www.postgresql.org/docs/16/) and [PDF](https://www.postgresql.org/files/documentation/pdf/16/postgresql-16-US.pdf) formats.

## Licence

PostgreSQL uses the [PostgreSQL License](https://www.postgresql.org/about/licence/),
a BSD-like "permissive" license. This
[OSI-certified license](http://www.opensource.org/licenses/postgresql/) is
widely appreciated as flexible and business-friendly, since it does not restrict
the use of PostgreSQL with commercial and proprietary applications. Together
with multi-company support and public ownership of the code, our license makes
PostgreSQL very popular with vendors wanting to embed a database in their own
products without fear of fees, vendor lock-in, or changes in licensing terms.

## Contacts

Website

* [https://www.postgresql.org/](https://www.postgresql.org/)

Email

* [[email protected]](mailto:[email protected])

## Images and Logos

Postgres, PostgreSQL, and the Elephant Logo (Slonik) are all registered
trademarks of the [PostgreSQL Community Association of Canada](https://www.postgres.ca).
If you wish to use these marks, you must comply with the [trademark policy](https://www.postgresql.org/about/policies/trademarks/).

## Corporate Support

PostgreSQL enjoys the support of numerous companies, who sponsor developers,
provide hosting resources, and give us financial support. See our
[sponsors](https://www.postgresql.org/about/sponsors/) page for some of these
project supporters.

There is also a large community of
[companies offering PostgreSQL Support](https://www.postgresql.org/support/professional_support/),
from individual consultants to multinational companies.

If you wish to make a financial contribution to the PostgreSQL Global
Development Group or one of the recognized community non-profit organizations,
please visit our [donations](https://www.postgresql.org/about/donate/) page.


Attachments:

  [text/plain] release.en.md (9.2K, ../../[email protected]/2-release.en.md)
  download | inline:
September 14, 2023 - The PostgreSQL Global Development Group today announced the
release of PostgreSQL 16, the latest version of the world’s most advanced open
source database.

PostgreSQL 16 raises its performance, with notable improvements to query
parallelism, bulk data loading, and logical replication. There are many features
in this release for developers and administrators alike, including more SQL/JSON
syntax, new monitoring stats for your workloads, and greater flexibility in
defining access control rules for large scale workloads.

<HOLD FOR QUOTE>

PostgreSQL, an innovative data management system known for its reliability and
robustness, benefits from over 25 years of open source development from a global
developer community and has become the preferred open source relational database
for organizations of all sizes.

### Performance Improvements

PostgreSQL 16 improves the performance of existing PostgreSQL functionality
through new query planner optimizations. In this latest release, the query
planner can parallelize  `FULL` and `RIGHT` joins, utilize incremental sorts for
`SELECT DISTINCT` queries, and execute window functions more efficiently. It
also introduces `RIGHT` and `OUTER` "anti-joins", which enable users to identify
rows not present in a joined table.

This release includes improvements for bulk loading using `COPY` in both single
and concurrent operations, with tests showing up to a 300% performance
improvement in some cases. PostgreSQL adds support for load balancing in clients
that use `libpq`, and improvements to vacuum strategy that reduce the necessity
of full-table freezes. Additionally, PostgreSQL 16 introduces CPU acceleration
using `SIMD` in both x86 and ARM architectures, resulting in performance gains
when processing ASCII and JSON strings, and performing array and subtransaction
searches.

### Logical replication 

Logical replication lets PostgreSQL users stream data to other PostgreSQL
instances or subscribers that can interpret the PostgreSQL logical replication
protocol. In PostgreSQL 16, users can perform logical decoding from a standby
instance, meaning a standby can publish logical changes to other servers. This
provides developers with new workload distribution options – for example, using
a standby rather than the busier primary to logically replicate changes to
downstream systems.

Additionally, there are several performance improvements in PostgreSQL 16 to
logical replication. Subscribers can now apply large transactions using parallel
workers. For tables that do not have a `PRIMARY KEY`, subscribers can use B-tree
indexes instead of sequential scans to find rows. Under certain conditions,
users can also speed up initial table synchronization using the binary format.

There are several access control improvements to logical replication in
PostgreSQL 16, including the new predefined role pg_create_subscription, which
grants users the ability to create a new logical subscription. Finally, this
release begins adding support for bidirectional logical replication, introducing
functionality to replicate data between two tables from different publishers.

### Developer Experience

PostgreSQL 16 adds more syntax from the SQL/JSON standard, including
constructors and predicates such as `JSON_ARRAY()`, `JSON_ARRAYAGG()`, and
`IS JSON`. This release also introduces the ability to use underscores for
thousands separators (e.g. `5_432_000`) and non-decimal integer literals, such
as `0x1538`, `0o12470`, and `0b1010100111000`.

Developers using PostgreSQL 16 will also benefit from the addition of multiple
commands to `psql` client protocol, including the `\bind` command, which allows
users to execute parameterized queries (e.g `SELECT $1 + $2`) then use `\bind`
to substitute the variables. 

PostgreSQL 16 improves general support for text collations, which provide rules
for how text is sorted. PostgreSQL 16 builds with ICU support by default,
determines the default ICU locale from the environment, and allows users to
define custom ICU collation rules.

### Monitoring

A key aspect of tuning the performance of database workloads is understanding
the impact of your I/O operations on your system. PostgreSQL 16 helps simplify
how you can analyze this data with the new pg_stat_io view, which tracks key I/O
statistics such as shared_buffer hits and I/O latency.

Additionally, this release adds a new field to the pg_stat_all_tables view,
capturing a timestamp representing when a table or index was last scanned.
PostgreSQL also makes auto_explain more readable by logging values passed into
parameterized statements, and improves accuracy of pg_stat_activity’s
normalization algorithm.

### Access Control & Security

PostgreSQL 16 provides finer-grained options for access control and enhances
other security features. The release improves management of `pg_hba.conf` and
`pg_ident.conf` files, including allowing regular expression matching for user
and database names and "include" directives for external configuration files.

This release adds several security-oriented client connection parameters,
including require_auth, which allows clients to specify which authentication
parameters they are willing to accept from a server, and `sslrootcert="system"`,
which indicates that PostgreSQL should use the trusted certificate authority
(CA) store provided by the client’s operating system. Additionally, the release
adds support for Kerberos credential delegation, allowing extensions such as
`postgres_fdw` and `dblink` to use authenticated credentials to connect to
trusted services.

### About PostgreSQL

[PostgreSQL](https://www.postgresql.org) is the world's most advanced open
source database, with a global community of thousands of users, contributors,
companies and organizations. Built on over 35 years of engineering, starting at
the University of California, Berkeley, PostgreSQL has continued with an
unmatched pace of development. PostgreSQL's mature feature set not only matches
top proprietary database systems, but exceeds them in advanced database
features, extensibility, security, and stability.

### Links

* [Download](https://www.postgresql.org/download/)
* [Release Notes](https://www.postgresql.org/docs/16/release-16.html)
* [Press Kit](https://www.postgresql.org/about/press/)
* [Security Page](https://www.postgresql.org/support/security/)
* [Versioning Policy](https://www.postgresql.org/support/versioning/)
* [Follow @postgresql](https://twitter.com/postgresql)
* [Donate](https://www.postgresql.org/about/donate/)

## More About the Features

For explanations of the above features and others, please see the following
resources:

* [Release Notes](https://www.postgresql.org/docs/16/release-16.html)
* [Feature Matrix](https://www.postgresql.org/about/featurematrix/)

## Where to Download

There are several ways you can download PostgreSQL 16, including:

* The [Official Downloads](https://www.postgresql.org/download/) page, with contains installers and tools for [Windows](https://www.postgresql.org/download/windows/), [Linux](https://www.postgresql.org/download/), [macOS](https://www.postgresql.org/download/macosx/), and more.
* [Source Code](https://www.postgresql.org/ftp/source/v16.0)

Other tools and extensions are available on the
[PostgreSQL Extension Network](http://pgxn.org/).

## Documentation

PostgreSQL 16 comes with HTML documentation as well as man pages, and you can also browse the documentation online in both [HTML](https://www.postgresql.org/docs/16/) and [PDF](https://www.postgresql.org/files/documentation/pdf/16/postgresql-16-US.pdf) formats.

## Licence

PostgreSQL uses the [PostgreSQL License](https://www.postgresql.org/about/licence/),
a BSD-like "permissive" license. This
[OSI-certified license](http://www.opensource.org/licenses/postgresql/) is
widely appreciated as flexible and business-friendly, since it does not restrict
the use of PostgreSQL with commercial and proprietary applications. Together
with multi-company support and public ownership of the code, our license makes
PostgreSQL very popular with vendors wanting to embed a database in their own
products without fear of fees, vendor lock-in, or changes in licensing terms.

## Contacts

Website

* [https://www.postgresql.org/](https://www.postgresql.org/)

Email

* [[email protected]](mailto:[email protected])

## Images and Logos

Postgres, PostgreSQL, and the Elephant Logo (Slonik) are all registered
trademarks of the [PostgreSQL Community Association of Canada](https://www.postgres.ca).
If you wish to use these marks, you must comply with the [trademark policy](https://www.postgresql.org/about/policies/trademarks/).

## Corporate Support

PostgreSQL enjoys the support of numerous companies, who sponsor developers,
provide hosting resources, and give us financial support. See our
[sponsors](https://www.postgresql.org/about/sponsors/) page for some of these
project supporters.

There is also a large community of
[companies offering PostgreSQL Support](https://www.postgresql.org/support/professional_support/),
from individual consultants to multinational companies.

If you wish to make a financial contribution to the PostgreSQL Global
Development Group or one of the recognized community non-profit organizations,
please visit our [donations](https://www.postgresql.org/about/donate/) page.

  [application/pgp-signature] OpenPGP_signature (840B, ../../[email protected]/3-OpenPGP_signature)
  download

^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* Re: PostgreSQL 16 release announcement draft
  2023-08-19 19:38 PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
@ 2023-08-20 02:18 ` jian he <[email protected]>
  2023-08-23 10:20   ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  0 siblings, 1 reply; 37+ messages in thread

From: jian he @ 2023-08-20 02:18 UTC (permalink / raw)
  To: Jonathan S. Katz <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; [email protected]

>>>>>
Additionally, this release adds a new field to the pg_stat_all_tables view,
capturing a timestamp representing when a table or index was last scanned.
PostgreSQL also makes auto_explain more readable by logging values passed into
parameterized statements, and improves accuracy of pg_stat_activity’s
normalization algorithm.
>>>>>

I am not sure if it's "capturing a timestamp representing" or
"capturing the timestamp representing".
"pg_stat_activity’s normalization algorithm", I think you are
referring to "pg_stat_statements"?





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* Re: PostgreSQL 16 release announcement draft
  2023-08-19 19:38 PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-20 02:18 ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
@ 2023-08-23 10:20   ` jian he <[email protected]>
  2023-08-23 12:02     ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  0 siblings, 1 reply; 37+ messages in thread

From: jian he @ 2023-08-23 10:20 UTC (permalink / raw)
  To: Jonathan S. Katz <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; [email protected]

>>>
PostgreSQL 16 improves the performance of existing PostgreSQL functionality
through new query planner optimizations. In this latest release, the query
planner can parallelize  `FULL` and `RIGHT` joins, utilize incremental sorts for
`SELECT DISTINCT` queries, and execute window functions more efficiently. It
also introduces `RIGHT` and `OUTER` "anti-joins", which enable users to identify
rows not present in a joined table.
>>>

I think "utilize incremental sorts is for" something like select
my_avg(distinct one),my_sum(one) from (values(1),(3)) t(one);
so it's not the same as `SELECT DISTINCT` queries?
ref: https://git.postgresql.org/cgit/postgresql.git/commit/?id=1349d2790bf48a4de072931c722f39337e72055e

also
<<<< "the query planner ....., and execute window functions more efficiently."
since the query planner doesn't execute anything. probably "and
optimize window functions execution"?





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* Re: PostgreSQL 16 release announcement draft
  2023-08-19 19:38 PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-20 02:18 ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 10:20   ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
@ 2023-08-23 12:02     ` David Rowley <[email protected]>
  2023-08-23 17:55       ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  0 siblings, 1 reply; 37+ messages in thread

From: David Rowley @ 2023-08-23 12:02 UTC (permalink / raw)
  To: jian he <[email protected]>; +Cc: Jonathan S. Katz <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected]

On Wed, 23 Aug 2023 at 22:21, jian he <[email protected]> wrote:
>
> >>>
> PostgreSQL 16 improves the performance of existing PostgreSQL functionality
> through new query planner optimizations. In this latest release, the query
> planner can parallelize  `FULL` and `RIGHT` joins, utilize incremental sorts for
> `SELECT DISTINCT` queries, and execute window functions more efficiently. It
> also introduces `RIGHT` and `OUTER` "anti-joins", which enable users to identify
> rows not present in a joined table.
> >>>
>
> I think "utilize incremental sorts is for" something like select
> my_avg(distinct one),my_sum(one) from (values(1),(3)) t(one);
> so it's not the same as `SELECT DISTINCT` queries?
> ref: https://git.postgresql.org/cgit/postgresql.git/commit/?id=1349d2790bf48a4de072931c722f39337e72055e

The incremental sorts for DISTINCT will likely be a reference to
3c6fc5820, so, not the same thing as 1349d2790.  I don't see anything
there relating to 1349d2790.

> also
> <<<< "the query planner ....., and execute window functions more efficiently."
> since the query planner doesn't execute anything. probably "and
> optimize window functions execution"?

Yeah, that or "and optimize window functions so they execute more
efficiently" is likely an improvement there.

David





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* Re: PostgreSQL 16 release announcement draft
  2023-08-19 19:38 PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-20 02:18 ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 10:20   ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 12:02     ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
@ 2023-08-23 17:55       ` Jonathan S. Katz <[email protected]>
  2023-08-23 21:07         ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  0 siblings, 1 reply; 37+ messages in thread

From: Jonathan S. Katz @ 2023-08-23 17:55 UTC (permalink / raw)
  To: David Rowley <[email protected]>; jian he <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; [email protected]

On 8/23/23 8:02 AM, David Rowley wrote:
> On Wed, 23 Aug 2023 at 22:21, jian he <[email protected]> wrote:
>>
>>>>>
>> PostgreSQL 16 improves the performance of existing PostgreSQL functionality
>> through new query planner optimizations. In this latest release, the query
>> planner can parallelize  `FULL` and `RIGHT` joins, utilize incremental sorts for
>> `SELECT DISTINCT` queries, and execute window functions more efficiently. It
>> also introduces `RIGHT` and `OUTER` "anti-joins", which enable users to identify
>> rows not present in a joined table.
>>>>>
>>
>> I think "utilize incremental sorts is for" something like select
>> my_avg(distinct one),my_sum(one) from (values(1),(3)) t(one);
>> so it's not the same as `SELECT DISTINCT` queries?
>> ref: https://git.postgresql.org/cgit/postgresql.git/commit/?id=1349d2790bf48a4de072931c722f39337e72055e
> 
> The incremental sorts for DISTINCT will likely be a reference to
> 3c6fc5820, so, not the same thing as 1349d2790.  I don't see anything
> there relating to 1349d2790.

We could add something about 1349d2790 -- do you have suggested wording?

>> also
>> <<<< "the query planner ....., and execute window functions more efficiently."
>> since the query planner doesn't execute anything. probably "and
>> optimize window functions execution"?
> 
> Yeah, that or "and optimize window functions so they execute more
> efficiently" is likely an improvement there.

Modified. See updated announcement, with other incorporated changes.

Reminder that the window to submit changes closes at **August 26, 12:00 
UTC**.

Thanks,

Jonathan


September 14, 2023 - The PostgreSQL Global Development Group today announced the
release of PostgreSQL 16, the latest version of the world's most advanced open
source database.

PostgreSQL 16 raises its performance, with notable improvements to query
parallelism, bulk data loading, and logical replication. There are many features
in this release for developers and administrators alike, including more SQL/JSON
syntax, new monitoring stats for your workloads, and greater flexibility in
defining access control rules for management of policies across large fleets.

<HOLD FOR QUOTE>

PostgreSQL, an innovative data management system known for its reliability and
robustness, benefits from over 25 years of open source development from a global
developer community and has become the preferred open source relational database
for organizations of all sizes.

### Performance Improvements

PostgreSQL 16 improves the performance of existing PostgreSQL functionality
through new query planner optimizations. In this latest release, the query
planner can parallelize  `FULL` and `RIGHT` joins, utilize incremental sorts for
`SELECT DISTINCT` queries, and optimize window function executions so they
execute more efficiently. It also introduces `RIGHT` and `OUTER` "anti-joins",
which enable users to identify rows not present in a joined table.

This release includes improvements for bulk loading using `COPY` in both single
and concurrent operations, with tests showing up to a 300% performance
improvement in some cases. PostgreSQL adds support for load balancing in clients
that use `libpq`, and improvements to vacuum strategy that reduce the necessity
of full-table freezes. Additionally, PostgreSQL 16 introduces CPU acceleration
using `SIMD` in both x86 and ARM architectures, resulting in performance gains
when processing ASCII and JSON strings, and performing array and subtransaction
searches.

### Logical replication 

Logical replication lets PostgreSQL users stream data to other PostgreSQL
instances or subscribers that can interpret the PostgreSQL logical replication
protocol. In PostgreSQL 16, users can perform logical decoding from a standby
instance, meaning a standby can publish logical changes to other servers. This
provides developers with new workload distribution options – for example, using
a standby rather than the busier primary to logically replicate changes to
downstream systems.

Additionally, there are several performance improvements in PostgreSQL 16 to
logical replication. Subscribers can now apply large transactions using parallel
workers. For tables that do not have a `PRIMARY KEY`, subscribers can use B-tree
indexes instead of sequential scans to find rows. Under certain conditions,
users can also speed up initial table synchronization using the binary format.

There are several access control improvements to logical replication in
PostgreSQL 16, including the new predefined role pg_create_subscription, which
grants users the ability to create a new logical subscription. Finally, this
release begins adding support for bidirectional logical replication, introducing
functionality to replicate data between two tables from different publishers.

### Developer Experience

PostgreSQL 16 adds more syntax from the SQL/JSON standard, including
constructors and predicates such as `JSON_ARRAY()`, `JSON_ARRAYAGG()`, and
`IS JSON`. This release also introduces the ability to use underscores for
thousands separators (e.g. `5_432_000`) and non-decimal integer literals, such
as `0x1538`, `0o12470`, and `0b1010100111000`.

Developers using PostgreSQL 16 will also benefit from the addition of multiple
commands to `psql` client protocol, including the `\bind` command, which allows
users to execute parameterized queries (e.g `SELECT $1 + $2`) then use `\bind`
to substitute the variables. 

PostgreSQL 16 improves general support for text collations, which provide rules
for how text is sorted. PostgreSQL 16 builds with ICU support by default,
determines the default ICU locale from the environment, and allows users to
define custom ICU collation rules.

### Monitoring

A key aspect of tuning the performance of database workloads is understanding
the impact of your I/O operations on your system. PostgreSQL 16 helps simplify
how you can analyze this data with the new pg_stat_io view, which tracks key I/O
statistics such as shared_buffer hits and I/O latency.

Additionally, this release adds a new field to the `pg_stat_all_tables` view 
that records a timestamp representing when a table or index was last scanned.
PostgreSQL also makes auto_explain more readable by logging values passed into
parameterized statements, and improves accuracy of pg_stat_activity's
normalization algorithm.

### Access Control & Security

PostgreSQL 16 provides finer-grained options for access control and enhances
other security features. The release improves management of `pg_hba.conf` and
`pg_ident.conf` files, including allowing regular expression matching for user
and database names and "include" directives for external configuration files.

This release adds several security-oriented client connection parameters,
including require_auth, which allows clients to specify which authentication
parameters they are willing to accept from a server, and `sslrootcert="system"`,
which indicates that PostgreSQL should use the trusted certificate authority
(CA) store provided by the client's operating system. Additionally, the release
adds support for Kerberos credential delegation, allowing extensions such as
`postgres_fdw` and `dblink` to use authenticated credentials to connect to
trusted services.

### About PostgreSQL

[PostgreSQL](https://www.postgresql.org) is the world's most advanced open
source database, with a global community of thousands of users, contributors,
companies and organizations. Built on over 35 years of engineering, starting at
the University of California, Berkeley, PostgreSQL has continued with an
unmatched pace of development. PostgreSQL's mature feature set not only matches
top proprietary database systems, but exceeds them in advanced database
features, extensibility, security, and stability.

### Links

* [Download](https://www.postgresql.org/download/)
* [Release Notes](https://www.postgresql.org/docs/16/release-16.html)
* [Press Kit](https://www.postgresql.org/about/press/)
* [Security Page](https://www.postgresql.org/support/security/)
* [Versioning Policy](https://www.postgresql.org/support/versioning/)
* [Follow @postgresql](https://twitter.com/postgresql)
* [Donate](https://www.postgresql.org/about/donate/)

## More About the Features

For explanations of the above features and others, please see the following
resources:

* [Release Notes](https://www.postgresql.org/docs/16/release-16.html)
* [Feature Matrix](https://www.postgresql.org/about/featurematrix/)

## Where to Download

There are several ways you can download PostgreSQL 16, including:

* The [Official Downloads](https://www.postgresql.org/download/) page, with contains installers and tools for [Windows](https://www.postgresql.org/download/windows/), [Linux](https://www.postgresql.org/download/linux/), [macOS](https://www.postgresql.org/download/macosx/), and more.
* [Source Code](https://www.postgresql.org/ftp/source/v16.0)

Other tools and extensions are available on the
[PostgreSQL Extension Network](http://pgxn.org/).

## Documentation

PostgreSQL 16 comes with HTML documentation as well as man pages, and you can also browse the documentation online in both [HTML](https://www.postgresql.org/docs/16/) and [PDF](https://www.postgresql.org/files/documentation/pdf/16/postgresql-16-US.pdf) formats.

## Licence

PostgreSQL uses the [PostgreSQL License](https://www.postgresql.org/about/licence/),
a BSD-like "permissive" license. This
[OSI-certified license](http://www.opensource.org/licenses/postgresql/) is
widely appreciated as flexible and business-friendly, since it does not restrict
the use of PostgreSQL with commercial and proprietary applications. Together
with multi-company support and public ownership of the code, our license makes
PostgreSQL very popular with vendors wanting to embed a database in their own
products without fear of fees, vendor lock-in, or changes in licensing terms.

## Contacts

Website

* [https://www.postgresql.org/](https://www.postgresql.org/)

Email

* [[email protected]](mailto:[email protected])

## Images and Logos

Postgres, PostgreSQL, and the Elephant Logo (Slonik) are all registered
trademarks of the [PostgreSQL Community Association of Canada](https://www.postgres.ca).
If you wish to use these marks, you must comply with the [trademark policy](https://www.postgresql.org/about/policies/trademarks/).

## Corporate Support

PostgreSQL enjoys the support of numerous companies, who sponsor developers,
provide hosting resources, and give us financial support. See our
[sponsors](https://www.postgresql.org/about/sponsors/) page for some of these
project supporters.

There is also a large community of
[companies offering PostgreSQL Support](https://www.postgresql.org/support/professional_support/),
from individual consultants to multinational companies.

If you wish to make a financial contribution to the PostgreSQL Global
Development Group or one of the recognized community non-profit organizations,
please visit our [donations](https://www.postgresql.org/about/donate/) page.


Attachments:

  [text/plain] release.en.md (9.2K, ../../[email protected]/2-release.en.md)
  download | inline:
September 14, 2023 - The PostgreSQL Global Development Group today announced the
release of PostgreSQL 16, the latest version of the world's most advanced open
source database.

PostgreSQL 16 raises its performance, with notable improvements to query
parallelism, bulk data loading, and logical replication. There are many features
in this release for developers and administrators alike, including more SQL/JSON
syntax, new monitoring stats for your workloads, and greater flexibility in
defining access control rules for management of policies across large fleets.

<HOLD FOR QUOTE>

PostgreSQL, an innovative data management system known for its reliability and
robustness, benefits from over 25 years of open source development from a global
developer community and has become the preferred open source relational database
for organizations of all sizes.

### Performance Improvements

PostgreSQL 16 improves the performance of existing PostgreSQL functionality
through new query planner optimizations. In this latest release, the query
planner can parallelize  `FULL` and `RIGHT` joins, utilize incremental sorts for
`SELECT DISTINCT` queries, and optimize window function executions so they
execute more efficiently. It also introduces `RIGHT` and `OUTER` "anti-joins",
which enable users to identify rows not present in a joined table.

This release includes improvements for bulk loading using `COPY` in both single
and concurrent operations, with tests showing up to a 300% performance
improvement in some cases. PostgreSQL adds support for load balancing in clients
that use `libpq`, and improvements to vacuum strategy that reduce the necessity
of full-table freezes. Additionally, PostgreSQL 16 introduces CPU acceleration
using `SIMD` in both x86 and ARM architectures, resulting in performance gains
when processing ASCII and JSON strings, and performing array and subtransaction
searches.

### Logical replication 

Logical replication lets PostgreSQL users stream data to other PostgreSQL
instances or subscribers that can interpret the PostgreSQL logical replication
protocol. In PostgreSQL 16, users can perform logical decoding from a standby
instance, meaning a standby can publish logical changes to other servers. This
provides developers with new workload distribution options – for example, using
a standby rather than the busier primary to logically replicate changes to
downstream systems.

Additionally, there are several performance improvements in PostgreSQL 16 to
logical replication. Subscribers can now apply large transactions using parallel
workers. For tables that do not have a `PRIMARY KEY`, subscribers can use B-tree
indexes instead of sequential scans to find rows. Under certain conditions,
users can also speed up initial table synchronization using the binary format.

There are several access control improvements to logical replication in
PostgreSQL 16, including the new predefined role pg_create_subscription, which
grants users the ability to create a new logical subscription. Finally, this
release begins adding support for bidirectional logical replication, introducing
functionality to replicate data between two tables from different publishers.

### Developer Experience

PostgreSQL 16 adds more syntax from the SQL/JSON standard, including
constructors and predicates such as `JSON_ARRAY()`, `JSON_ARRAYAGG()`, and
`IS JSON`. This release also introduces the ability to use underscores for
thousands separators (e.g. `5_432_000`) and non-decimal integer literals, such
as `0x1538`, `0o12470`, and `0b1010100111000`.

Developers using PostgreSQL 16 will also benefit from the addition of multiple
commands to `psql` client protocol, including the `\bind` command, which allows
users to execute parameterized queries (e.g `SELECT $1 + $2`) then use `\bind`
to substitute the variables. 

PostgreSQL 16 improves general support for text collations, which provide rules
for how text is sorted. PostgreSQL 16 builds with ICU support by default,
determines the default ICU locale from the environment, and allows users to
define custom ICU collation rules.

### Monitoring

A key aspect of tuning the performance of database workloads is understanding
the impact of your I/O operations on your system. PostgreSQL 16 helps simplify
how you can analyze this data with the new pg_stat_io view, which tracks key I/O
statistics such as shared_buffer hits and I/O latency.

Additionally, this release adds a new field to the `pg_stat_all_tables` view 
that records a timestamp representing when a table or index was last scanned.
PostgreSQL also makes auto_explain more readable by logging values passed into
parameterized statements, and improves accuracy of pg_stat_activity's
normalization algorithm.

### Access Control & Security

PostgreSQL 16 provides finer-grained options for access control and enhances
other security features. The release improves management of `pg_hba.conf` and
`pg_ident.conf` files, including allowing regular expression matching for user
and database names and "include" directives for external configuration files.

This release adds several security-oriented client connection parameters,
including require_auth, which allows clients to specify which authentication
parameters they are willing to accept from a server, and `sslrootcert="system"`,
which indicates that PostgreSQL should use the trusted certificate authority
(CA) store provided by the client's operating system. Additionally, the release
adds support for Kerberos credential delegation, allowing extensions such as
`postgres_fdw` and `dblink` to use authenticated credentials to connect to
trusted services.

### About PostgreSQL

[PostgreSQL](https://www.postgresql.org) is the world's most advanced open
source database, with a global community of thousands of users, contributors,
companies and organizations. Built on over 35 years of engineering, starting at
the University of California, Berkeley, PostgreSQL has continued with an
unmatched pace of development. PostgreSQL's mature feature set not only matches
top proprietary database systems, but exceeds them in advanced database
features, extensibility, security, and stability.

### Links

* [Download](https://www.postgresql.org/download/)
* [Release Notes](https://www.postgresql.org/docs/16/release-16.html)
* [Press Kit](https://www.postgresql.org/about/press/)
* [Security Page](https://www.postgresql.org/support/security/)
* [Versioning Policy](https://www.postgresql.org/support/versioning/)
* [Follow @postgresql](https://twitter.com/postgresql)
* [Donate](https://www.postgresql.org/about/donate/)

## More About the Features

For explanations of the above features and others, please see the following
resources:

* [Release Notes](https://www.postgresql.org/docs/16/release-16.html)
* [Feature Matrix](https://www.postgresql.org/about/featurematrix/)

## Where to Download

There are several ways you can download PostgreSQL 16, including:

* The [Official Downloads](https://www.postgresql.org/download/) page, with contains installers and tools for [Windows](https://www.postgresql.org/download/windows/), [Linux](https://www.postgresql.org/download/linux/), [macOS](https://www.postgresql.org/download/macosx/), and more.
* [Source Code](https://www.postgresql.org/ftp/source/v16.0)

Other tools and extensions are available on the
[PostgreSQL Extension Network](http://pgxn.org/).

## Documentation

PostgreSQL 16 comes with HTML documentation as well as man pages, and you can also browse the documentation online in both [HTML](https://www.postgresql.org/docs/16/) and [PDF](https://www.postgresql.org/files/documentation/pdf/16/postgresql-16-US.pdf) formats.

## Licence

PostgreSQL uses the [PostgreSQL License](https://www.postgresql.org/about/licence/),
a BSD-like "permissive" license. This
[OSI-certified license](http://www.opensource.org/licenses/postgresql/) is
widely appreciated as flexible and business-friendly, since it does not restrict
the use of PostgreSQL with commercial and proprietary applications. Together
with multi-company support and public ownership of the code, our license makes
PostgreSQL very popular with vendors wanting to embed a database in their own
products without fear of fees, vendor lock-in, or changes in licensing terms.

## Contacts

Website

* [https://www.postgresql.org/](https://www.postgresql.org/)

Email

* [[email protected]](mailto:[email protected])

## Images and Logos

Postgres, PostgreSQL, and the Elephant Logo (Slonik) are all registered
trademarks of the [PostgreSQL Community Association of Canada](https://www.postgres.ca).
If you wish to use these marks, you must comply with the [trademark policy](https://www.postgresql.org/about/policies/trademarks/).

## Corporate Support

PostgreSQL enjoys the support of numerous companies, who sponsor developers,
provide hosting resources, and give us financial support. See our
[sponsors](https://www.postgresql.org/about/sponsors/) page for some of these
project supporters.

There is also a large community of
[companies offering PostgreSQL Support](https://www.postgresql.org/support/professional_support/),
from individual consultants to multinational companies.

If you wish to make a financial contribution to the PostgreSQL Global
Development Group or one of the recognized community non-profit organizations,
please visit our [donations](https://www.postgresql.org/about/donate/) page.

  [application/pgp-signature] OpenPGP_signature (840B, ../../[email protected]/3-OpenPGP_signature)
  download

^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* Re: PostgreSQL 16 release announcement draft
  2023-08-19 19:38 PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-20 02:18 ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 10:20   ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 12:02     ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-23 17:55       ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
@ 2023-08-23 21:07         ` David Rowley <[email protected]>
  2023-08-24 14:32           ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  0 siblings, 1 reply; 37+ messages in thread

From: David Rowley @ 2023-08-23 21:07 UTC (permalink / raw)
  To: Jonathan S. Katz <[email protected]>; +Cc: jian he <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected]

On Thu, 24 Aug 2023 at 05:55, Jonathan S. Katz <[email protected]> wrote:
> We could add something about 1349d2790 -- do you have suggested wording?

I think it's worth a mention. See the text added in square brackets below:

PostgreSQL 16 improves the performance of existing PostgreSQL functionality
through new query planner optimizations. In this latest release, the query
planner can parallelize  `FULL` and `RIGHT` joins, [generate more
optimal plans for
queries containing aggregate functions with a `DISTINCT` or `ORDER BY` clause,]
utilize incremental sorts for `SELECT DISTINCT` queries, and optimize
window function
executions so they execute more efficiently. It also introduces
`RIGHT` and `OUTER`
"anti-joins", which enable users to identify rows not present in a joined table.

Thanks

David





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* Re: PostgreSQL 16 release announcement draft
  2023-08-19 19:38 PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-20 02:18 ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 10:20   ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 12:02     ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-23 17:55       ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-23 21:07         ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
@ 2023-08-24 14:32           ` Jonathan S. Katz <[email protected]>
  2023-08-24 15:16             ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-24 15:17             ` Re: PostgreSQL 16 release announcement draft Erik Rijkers <[email protected]>
  2023-08-24 15:19             ` Re: PostgreSQL 16 release announcement draft Alvaro Herrera <[email protected]>
  0 siblings, 3 replies; 37+ messages in thread

From: Jonathan S. Katz @ 2023-08-24 14:32 UTC (permalink / raw)
  To: David Rowley <[email protected]>; +Cc: jian he <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected]

On 8/23/23 5:07 PM, David Rowley wrote:
> On Thu, 24 Aug 2023 at 05:55, Jonathan S. Katz <[email protected]> wrote:
>> We could add something about 1349d2790 -- do you have suggested wording?
> 
> I think it's worth a mention. See the text added in square brackets below:
> 
> PostgreSQL 16 improves the performance of existing PostgreSQL functionality
> through new query planner optimizations. In this latest release, the query
> planner can parallelize  `FULL` and `RIGHT` joins, [generate more
> optimal plans for
> queries containing aggregate functions with a `DISTINCT` or `ORDER BY` clause,]
> utilize incremental sorts for `SELECT DISTINCT` queries, and optimize
> window function
> executions so they execute more efficiently. It also introduces
> `RIGHT` and `OUTER`
> "anti-joins", which enable users to identify rows not present in a joined table.

I added this in mostly verbatim. I'm concerned the sentence is a bit 
long, but we could break it up into two: (1) with the new JOIN 
capabilities and (2) with the optimizations.

Jonathan

September 14, 2023 - The PostgreSQL Global Development Group today announced the
release of PostgreSQL 16, the latest version of the world's most advanced open
source database.

PostgreSQL 16 raises its performance, with notable improvements to query
parallelism, bulk data loading, and logical replication. There are many features
in this release for developers and administrators alike, including more SQL/JSON
syntax, new monitoring stats for your workloads, and greater flexibility in
defining access control rules for management of policies across large fleets.

<HOLD FOR QUOTE>

PostgreSQL, an innovative data management system known for its reliability and
robustness, benefits from over 25 years of open source development from a global
developer community and has become the preferred open source relational database
for organizations of all sizes.

### Performance Improvements

PostgreSQL 16 improves the performance of existing PostgreSQL functionality
through new query planner optimizations. In this latest release, the query
planner can parallelize  `FULL` and `RIGHT` joins, generate better optimized
plans for queries that use aggregate functions (e.g. `count`) with a `DISTINCT`
or `ORDER BY` clause, utilize incremental sorts for `SELECT DISTINCT` queries,
and optimize window function executions so they execute more efficiently.
It also introduces `RIGHT` and `OUTER` "anti-joins", which enable users to
identify rows not present in a joined table.

This release includes improvements for bulk loading using `COPY` in both single
and concurrent operations, with tests showing up to a 300% performance
improvement in some cases. PostgreSQL adds support for load balancing in clients
that use `libpq`, and improvements to vacuum strategy that reduce the necessity
of full-table freezes. Additionally, PostgreSQL 16 introduces CPU acceleration
using `SIMD` in both x86 and ARM architectures, resulting in performance gains
when processing ASCII and JSON strings, and performing array and subtransaction
searches.

### Logical replication 

Logical replication lets PostgreSQL users stream data to other PostgreSQL
instances or subscribers that can interpret the PostgreSQL logical replication
protocol. In PostgreSQL 16, users can perform logical decoding from a standby
instance, meaning a standby can publish logical changes to other servers. This
provides developers with new workload distribution options – for example, using
a standby rather than the busier primary to logically replicate changes to
downstream systems.

Additionally, there are several performance improvements in PostgreSQL 16 to
logical replication. Subscribers can now apply large transactions using parallel
workers. For tables that do not have a `PRIMARY KEY`, subscribers can use B-tree
indexes instead of sequential scans to find rows. Under certain conditions,
users can also speed up initial table synchronization using the binary format.

There are several access control improvements to logical replication in
PostgreSQL 16, including the new predefined role pg_create_subscription, which
grants users the ability to create a new logical subscription. Finally, this
release begins adding support for bidirectional logical replication, introducing
functionality to replicate data between two tables from different publishers.

### Developer Experience

PostgreSQL 16 adds more syntax from the SQL/JSON standard, including
constructors and predicates such as `JSON_ARRAY()`, `JSON_ARRAYAGG()`, and
`IS JSON`. This release also introduces the ability to use underscores for
thousands separators (e.g. `5_432_000`) and non-decimal integer literals, such
as `0x1538`, `0o12470`, and `0b1010100111000`.

Developers using PostgreSQL 16 will also benefit from the addition of multiple
commands to `psql` client protocol, including the `\bind` command, which allows
users to execute parameterized queries (e.g `SELECT $1 + $2`) then use `\bind`
to substitute the variables. 

PostgreSQL 16 improves general support for text collations, which provide rules
for how text is sorted. PostgreSQL 16 builds with ICU support by default,
determines the default ICU locale from the environment, and allows users to
define custom ICU collation rules.

### Monitoring

A key aspect of tuning the performance of database workloads is understanding
the impact of your I/O operations on your system. PostgreSQL 16 helps simplify
how you can analyze this data with the new pg_stat_io view, which tracks key I/O
statistics such as shared_buffer hits and I/O latency.

Additionally, this release adds a new field to the `pg_stat_all_tables` view 
that records a timestamp representing when a table or index was last scanned.
PostgreSQL also makes auto_explain more readable by logging values passed into
parameterized statements, and improves accuracy of pg_stat_activity's
normalization algorithm.

### Access Control & Security

PostgreSQL 16 provides finer-grained options for access control and enhances
other security features. The release improves management of `pg_hba.conf` and
`pg_ident.conf` files, including allowing regular expression matching for user
and database names and "include" directives for external configuration files.

This release adds several security-oriented client connection parameters,
including require_auth, which allows clients to specify which authentication
parameters they are willing to accept from a server, and `sslrootcert="system"`,
which indicates that PostgreSQL should use the trusted certificate authority
(CA) store provided by the client's operating system. Additionally, the release
adds support for Kerberos credential delegation, allowing extensions such as
`postgres_fdw` and `dblink` to use authenticated credentials to connect to
trusted services.

### About PostgreSQL

[PostgreSQL](https://www.postgresql.org) is the world's most advanced open
source database, with a global community of thousands of users, contributors,
companies and organizations. Built on over 35 years of engineering, starting at
the University of California, Berkeley, PostgreSQL has continued with an
unmatched pace of development. PostgreSQL's mature feature set not only matches
top proprietary database systems, but exceeds them in advanced database
features, extensibility, security, and stability.

### Links

* [Download](https://www.postgresql.org/download/)
* [Release Notes](https://www.postgresql.org/docs/16/release-16.html)
* [Press Kit](https://www.postgresql.org/about/press/)
* [Security Page](https://www.postgresql.org/support/security/)
* [Versioning Policy](https://www.postgresql.org/support/versioning/)
* [Follow @postgresql](https://twitter.com/postgresql)
* [Donate](https://www.postgresql.org/about/donate/)

## More About the Features

For explanations of the above features and others, please see the following
resources:

* [Release Notes](https://www.postgresql.org/docs/16/release-16.html)
* [Feature Matrix](https://www.postgresql.org/about/featurematrix/)

## Where to Download

There are several ways you can download PostgreSQL 16, including:

* The [Official Downloads](https://www.postgresql.org/download/) page, with contains installers and tools for [Windows](https://www.postgresql.org/download/windows/), [Linux](https://www.postgresql.org/download/linux/), [macOS](https://www.postgresql.org/download/macosx/), and more.
* [Source Code](https://www.postgresql.org/ftp/source/v16.0)

Other tools and extensions are available on the
[PostgreSQL Extension Network](http://pgxn.org/).

## Documentation

PostgreSQL 16 comes with HTML documentation as well as man pages, and you can also browse the documentation online in both [HTML](https://www.postgresql.org/docs/16/) and [PDF](https://www.postgresql.org/files/documentation/pdf/16/postgresql-16-US.pdf) formats.

## Licence

PostgreSQL uses the [PostgreSQL License](https://www.postgresql.org/about/licence/),
a BSD-like "permissive" license. This
[OSI-certified license](http://www.opensource.org/licenses/postgresql/) is
widely appreciated as flexible and business-friendly, since it does not restrict
the use of PostgreSQL with commercial and proprietary applications. Together
with multi-company support and public ownership of the code, our license makes
PostgreSQL very popular with vendors wanting to embed a database in their own
products without fear of fees, vendor lock-in, or changes in licensing terms.

## Contacts

Website

* [https://www.postgresql.org/](https://www.postgresql.org/)

Email

* [[email protected]](mailto:[email protected])

## Images and Logos

Postgres, PostgreSQL, and the Elephant Logo (Slonik) are all registered
trademarks of the [PostgreSQL Community Association of Canada](https://www.postgres.ca).
If you wish to use these marks, you must comply with the [trademark policy](https://www.postgresql.org/about/policies/trademarks/).

## Corporate Support

PostgreSQL enjoys the support of numerous companies, who sponsor developers,
provide hosting resources, and give us financial support. See our
[sponsors](https://www.postgresql.org/about/sponsors/) page for some of these
project supporters.

There is also a large community of
[companies offering PostgreSQL Support](https://www.postgresql.org/support/professional_support/),
from individual consultants to multinational companies.

If you wish to make a financial contribution to the PostgreSQL Global
Development Group or one of the recognized community non-profit organizations,
please visit our [donations](https://www.postgresql.org/about/donate/) page.


Attachments:

  [text/plain] release.en.md (9.3K, ../../[email protected]/2-release.en.md)
  download | inline:
September 14, 2023 - The PostgreSQL Global Development Group today announced the
release of PostgreSQL 16, the latest version of the world's most advanced open
source database.

PostgreSQL 16 raises its performance, with notable improvements to query
parallelism, bulk data loading, and logical replication. There are many features
in this release for developers and administrators alike, including more SQL/JSON
syntax, new monitoring stats for your workloads, and greater flexibility in
defining access control rules for management of policies across large fleets.

<HOLD FOR QUOTE>

PostgreSQL, an innovative data management system known for its reliability and
robustness, benefits from over 25 years of open source development from a global
developer community and has become the preferred open source relational database
for organizations of all sizes.

### Performance Improvements

PostgreSQL 16 improves the performance of existing PostgreSQL functionality
through new query planner optimizations. In this latest release, the query
planner can parallelize  `FULL` and `RIGHT` joins, generate better optimized
plans for queries that use aggregate functions (e.g. `count`) with a `DISTINCT`
or `ORDER BY` clause, utilize incremental sorts for `SELECT DISTINCT` queries,
and optimize window function executions so they execute more efficiently.
It also introduces `RIGHT` and `OUTER` "anti-joins", which enable users to
identify rows not present in a joined table.

This release includes improvements for bulk loading using `COPY` in both single
and concurrent operations, with tests showing up to a 300% performance
improvement in some cases. PostgreSQL adds support for load balancing in clients
that use `libpq`, and improvements to vacuum strategy that reduce the necessity
of full-table freezes. Additionally, PostgreSQL 16 introduces CPU acceleration
using `SIMD` in both x86 and ARM architectures, resulting in performance gains
when processing ASCII and JSON strings, and performing array and subtransaction
searches.

### Logical replication 

Logical replication lets PostgreSQL users stream data to other PostgreSQL
instances or subscribers that can interpret the PostgreSQL logical replication
protocol. In PostgreSQL 16, users can perform logical decoding from a standby
instance, meaning a standby can publish logical changes to other servers. This
provides developers with new workload distribution options – for example, using
a standby rather than the busier primary to logically replicate changes to
downstream systems.

Additionally, there are several performance improvements in PostgreSQL 16 to
logical replication. Subscribers can now apply large transactions using parallel
workers. For tables that do not have a `PRIMARY KEY`, subscribers can use B-tree
indexes instead of sequential scans to find rows. Under certain conditions,
users can also speed up initial table synchronization using the binary format.

There are several access control improvements to logical replication in
PostgreSQL 16, including the new predefined role pg_create_subscription, which
grants users the ability to create a new logical subscription. Finally, this
release begins adding support for bidirectional logical replication, introducing
functionality to replicate data between two tables from different publishers.

### Developer Experience

PostgreSQL 16 adds more syntax from the SQL/JSON standard, including
constructors and predicates such as `JSON_ARRAY()`, `JSON_ARRAYAGG()`, and
`IS JSON`. This release also introduces the ability to use underscores for
thousands separators (e.g. `5_432_000`) and non-decimal integer literals, such
as `0x1538`, `0o12470`, and `0b1010100111000`.

Developers using PostgreSQL 16 will also benefit from the addition of multiple
commands to `psql` client protocol, including the `\bind` command, which allows
users to execute parameterized queries (e.g `SELECT $1 + $2`) then use `\bind`
to substitute the variables. 

PostgreSQL 16 improves general support for text collations, which provide rules
for how text is sorted. PostgreSQL 16 builds with ICU support by default,
determines the default ICU locale from the environment, and allows users to
define custom ICU collation rules.

### Monitoring

A key aspect of tuning the performance of database workloads is understanding
the impact of your I/O operations on your system. PostgreSQL 16 helps simplify
how you can analyze this data with the new pg_stat_io view, which tracks key I/O
statistics such as shared_buffer hits and I/O latency.

Additionally, this release adds a new field to the `pg_stat_all_tables` view 
that records a timestamp representing when a table or index was last scanned.
PostgreSQL also makes auto_explain more readable by logging values passed into
parameterized statements, and improves accuracy of pg_stat_activity's
normalization algorithm.

### Access Control & Security

PostgreSQL 16 provides finer-grained options for access control and enhances
other security features. The release improves management of `pg_hba.conf` and
`pg_ident.conf` files, including allowing regular expression matching for user
and database names and "include" directives for external configuration files.

This release adds several security-oriented client connection parameters,
including require_auth, which allows clients to specify which authentication
parameters they are willing to accept from a server, and `sslrootcert="system"`,
which indicates that PostgreSQL should use the trusted certificate authority
(CA) store provided by the client's operating system. Additionally, the release
adds support for Kerberos credential delegation, allowing extensions such as
`postgres_fdw` and `dblink` to use authenticated credentials to connect to
trusted services.

### About PostgreSQL

[PostgreSQL](https://www.postgresql.org) is the world's most advanced open
source database, with a global community of thousands of users, contributors,
companies and organizations. Built on over 35 years of engineering, starting at
the University of California, Berkeley, PostgreSQL has continued with an
unmatched pace of development. PostgreSQL's mature feature set not only matches
top proprietary database systems, but exceeds them in advanced database
features, extensibility, security, and stability.

### Links

* [Download](https://www.postgresql.org/download/)
* [Release Notes](https://www.postgresql.org/docs/16/release-16.html)
* [Press Kit](https://www.postgresql.org/about/press/)
* [Security Page](https://www.postgresql.org/support/security/)
* [Versioning Policy](https://www.postgresql.org/support/versioning/)
* [Follow @postgresql](https://twitter.com/postgresql)
* [Donate](https://www.postgresql.org/about/donate/)

## More About the Features

For explanations of the above features and others, please see the following
resources:

* [Release Notes](https://www.postgresql.org/docs/16/release-16.html)
* [Feature Matrix](https://www.postgresql.org/about/featurematrix/)

## Where to Download

There are several ways you can download PostgreSQL 16, including:

* The [Official Downloads](https://www.postgresql.org/download/) page, with contains installers and tools for [Windows](https://www.postgresql.org/download/windows/), [Linux](https://www.postgresql.org/download/linux/), [macOS](https://www.postgresql.org/download/macosx/), and more.
* [Source Code](https://www.postgresql.org/ftp/source/v16.0)

Other tools and extensions are available on the
[PostgreSQL Extension Network](http://pgxn.org/).

## Documentation

PostgreSQL 16 comes with HTML documentation as well as man pages, and you can also browse the documentation online in both [HTML](https://www.postgresql.org/docs/16/) and [PDF](https://www.postgresql.org/files/documentation/pdf/16/postgresql-16-US.pdf) formats.

## Licence

PostgreSQL uses the [PostgreSQL License](https://www.postgresql.org/about/licence/),
a BSD-like "permissive" license. This
[OSI-certified license](http://www.opensource.org/licenses/postgresql/) is
widely appreciated as flexible and business-friendly, since it does not restrict
the use of PostgreSQL with commercial and proprietary applications. Together
with multi-company support and public ownership of the code, our license makes
PostgreSQL very popular with vendors wanting to embed a database in their own
products without fear of fees, vendor lock-in, or changes in licensing terms.

## Contacts

Website

* [https://www.postgresql.org/](https://www.postgresql.org/)

Email

* [[email protected]](mailto:[email protected])

## Images and Logos

Postgres, PostgreSQL, and the Elephant Logo (Slonik) are all registered
trademarks of the [PostgreSQL Community Association of Canada](https://www.postgres.ca).
If you wish to use these marks, you must comply with the [trademark policy](https://www.postgresql.org/about/policies/trademarks/).

## Corporate Support

PostgreSQL enjoys the support of numerous companies, who sponsor developers,
provide hosting resources, and give us financial support. See our
[sponsors](https://www.postgresql.org/about/sponsors/) page for some of these
project supporters.

There is also a large community of
[companies offering PostgreSQL Support](https://www.postgresql.org/support/professional_support/),
from individual consultants to multinational companies.

If you wish to make a financial contribution to the PostgreSQL Global
Development Group or one of the recognized community non-profit organizations,
please visit our [donations](https://www.postgresql.org/about/donate/) page.

  [application/pgp-signature] OpenPGP_signature (840B, ../../[email protected]/3-OpenPGP_signature)
  download

^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* Re: PostgreSQL 16 release announcement draft
  2023-08-19 19:38 PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-20 02:18 ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 10:20   ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 12:02     ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-23 17:55       ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-23 21:07         ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-24 14:32           ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
@ 2023-08-24 15:16             ` jian he <[email protected]>
  2023-08-24 15:23               ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2 siblings, 1 reply; 37+ messages in thread

From: jian he @ 2023-08-24 15:16 UTC (permalink / raw)
  To: Jonathan S. Katz <[email protected]>; +Cc: David Rowley <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected]

hi. Can you check my first email about "a" versus "the" and "pg_stat_activity".

also:
"including the `\bind` command, which allows
users to execute parameterized queries (e.g `SELECT $1 + $2`) then use `\bind`
to substitute the variables."

The example actually is very hard to reproduce. (it's not that super intuitive).
fail case:
test16-# SELECT $1 + $2 \bind 1 2
test16-# ;

a better example would  be (e.g `SELECT $1 , $2`).
The semicolon still needed to be in the next line.





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* Re: PostgreSQL 16 release announcement draft
  2023-08-19 19:38 PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-20 02:18 ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 10:20   ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 12:02     ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-23 17:55       ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-23 21:07         ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-24 14:32           ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-24 15:16             ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
@ 2023-08-24 15:23               ` Jonathan S. Katz <[email protected]>
  2023-08-24 15:38                 ` Re: PostgreSQL 16 release announcement draft Chapman Flack <[email protected]>
  0 siblings, 1 reply; 37+ messages in thread

From: Jonathan S. Katz @ 2023-08-24 15:23 UTC (permalink / raw)
  To: jian he <[email protected]>; +Cc: David Rowley <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected]

On 8/24/23 11:16 AM, jian he wrote:
> hi. Can you check my first email about "a" versus "the" and "pg_stat_activity".

I did when you first sent it, and did not make any changes.

> also:
> "including the `\bind` command, which allows
> users to execute parameterized queries (e.g `SELECT $1 + $2`) then use `\bind`
> to substitute the variables."
> 
> The example actually is very hard to reproduce. (it's not that super intuitive).
> fail case:
> test16-# SELECT $1 + $2 \bind 1 2
> test16-# ;
> 
> a better example would  be (e.g `SELECT $1 , $2`).
> The semicolon still needed to be in the next line.

I agree with updating the example, I'd propose:

SELECT $1::int + $2::int \bind 1 2 \g

which mirrors what's in the docs[1]

Thanks,

Jonathan

[1] 
https://www.postgresql.org/docs/16/app-psql.html#APP-PSQL-META-COMMAND-BIND


Attachments:

  [application/pgp-signature] OpenPGP_signature (840B, ../../[email protected]/2-OpenPGP_signature)
  download

^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* Re: PostgreSQL 16 release announcement draft
  2023-08-19 19:38 PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-20 02:18 ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 10:20   ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 12:02     ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-23 17:55       ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-23 21:07         ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-24 14:32           ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-24 15:16             ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-24 15:23               ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
@ 2023-08-24 15:38                 ` Chapman Flack <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Chapman Flack @ 2023-08-24 15:38 UTC (permalink / raw)
  To: Jonathan S. Katz <[email protected]>; +Cc: jian he <[email protected]>; David Rowley <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected]

On 2023-08-24 11:23, Jonathan S. Katz wrote:
> 
> SELECT $1::int + $2::int \bind 1 2 \g

One cast also works, letting type inference figure out the other.
So if I say

SELECT $1::int + $2 \gdesc

it tells me the result will be int. That made me wonder if there is
a \gdesc variant to issue the "statement variant" Describe message
and show what the parameter types have been inferred to be. If
there's not, obviously it won't be in 16, but it might be useful.

Regards,
-Chap





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* Re: PostgreSQL 16 release announcement draft
  2023-08-19 19:38 PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-20 02:18 ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 10:20   ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 12:02     ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-23 17:55       ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-23 21:07         ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-24 14:32           ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
@ 2023-08-24 15:17             ` Erik Rijkers <[email protected]>
  2023-08-26 02:51               ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2 siblings, 1 reply; 37+ messages in thread

From: Erik Rijkers @ 2023-08-24 15:17 UTC (permalink / raw)
  To: Jonathan S. Katz <[email protected]>; David Rowley <[email protected]>; +Cc: jian he <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected]

Op 8/24/23 om 16:32 schreef Jonathan S. Katz:
> On 8/23/23 5:07 PM, David Rowley wrote:
>> On Thu, 24 Aug 2023 at 05:55, Jonathan S. Katz <[email protected]> 
>> wrote:

Hi,

When v15 docs have:

"27.2.7. Cascading Replication
The cascading replication feature allows a standby server to accept 
replication connections and stream WAL records to other standbys, acting 
as a relay. This can be used to reduce the number of direct connections 
to the primary and also to minimize inter-site bandwidth overheads."

why then, in the release draft, is that capability mentioned as 
something that is new for v16?
"
In PostgreSQL 16, users can perform logical decoding from a standby
instance, meaning a standby can publish logical changes to other servers.
"

Is there a difference between the two?

Thanks,

Erik










^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* Re: PostgreSQL 16 release announcement draft
  2023-08-19 19:38 PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-20 02:18 ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 10:20   ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 12:02     ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-23 17:55       ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-23 21:07         ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-24 14:32           ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-24 15:17             ` Re: PostgreSQL 16 release announcement draft Erik Rijkers <[email protected]>
@ 2023-08-26 02:51               ` Jonathan S. Katz <[email protected]>
  2023-08-26 04:28                 ` Re: PostgreSQL 16 release announcement draft Erik Rijkers <[email protected]>
  0 siblings, 1 reply; 37+ messages in thread

From: Jonathan S. Katz @ 2023-08-26 02:51 UTC (permalink / raw)
  To: Erik Rijkers <[email protected]>; David Rowley <[email protected]>; +Cc: jian he <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected]

On 8/24/23 11:17 AM, Erik Rijkers wrote:
> Op 8/24/23 om 16:32 schreef Jonathan S. Katz:
>> On 8/23/23 5:07 PM, David Rowley wrote:
>>> On Thu, 24 Aug 2023 at 05:55, Jonathan S. Katz <[email protected]> 
>>> wrote:
> 
> Hi,
> 
> When v15 docs have:
> 
> "27.2.7. Cascading Replication
> The cascading replication feature allows a standby server to accept 
> replication connections and stream WAL records to other standbys, acting 
> as a relay. This can be used to reduce the number of direct connections 
> to the primary and also to minimize inter-site bandwidth overheads."
> 
> why then, in the release draft, is that capability mentioned as 
> something that is new for v16?
> "
> In PostgreSQL 16, users can perform logical decoding from a standby
> instance, meaning a standby can publish logical changes to other servers.
> "
> 
> Is there a difference between the two?

Yes. Those docs refer to **physical** replication, where a standby can 
continue to replicate WAL records to other standbys. In v16, standbys 
can now publish changes over **logical** replication.

Thanks,

Jonathan



Attachments:

  [application/pgp-signature] OpenPGP_signature (840B, ../../[email protected]/2-OpenPGP_signature)
  download

^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* Re: PostgreSQL 16 release announcement draft
  2023-08-19 19:38 PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-20 02:18 ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 10:20   ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 12:02     ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-23 17:55       ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-23 21:07         ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-24 14:32           ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-24 15:17             ` Re: PostgreSQL 16 release announcement draft Erik Rijkers <[email protected]>
  2023-08-26 02:51               ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
@ 2023-08-26 04:28                 ` Erik Rijkers <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Erik Rijkers @ 2023-08-26 04:28 UTC (permalink / raw)
  To: Jonathan S. Katz <[email protected]>; David Rowley <[email protected]>; +Cc: jian he <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected]

Op 8/26/23 om 04:51 schreef Jonathan S. Katz:
> On 8/24/23 11:17 AM, Erik Rijkers wrote:
>> Op 8/24/23 om 16:32 schreef Jonathan S. Katz:
>>> On 8/23/23 5:07 PM, David Rowley wrote:
>>>> On Thu, 24 Aug 2023 at 05:55, Jonathan S. Katz 
>>>> <[email protected]> wrote:
>>
>> Hi,
>>
>> When v15 docs have:
>>
>> "27.2.7. Cascading Replication
>> The cascading replication feature allows a standby server to accept 
>> replication connections and stream WAL records to other standbys, 
>> acting as a relay. This can be used to reduce the number of direct 
>> connections to the primary and also to minimize inter-site bandwidth 
>> overheads."
>>
>> why then, in the release draft, is that capability mentioned as 
>> something that is new for v16?
>> "
>> In PostgreSQL 16, users can perform logical decoding from a standby
>> instance, meaning a standby can publish logical changes to other servers.
>> "
>>
>> Is there a difference between the two?
> 
> Yes. Those docs refer to **physical** replication, where a standby can 
> continue to replicate WAL records to other standbys. In v16, standbys 
> can now publish changes over **logical** replication.

Well, I must assume you are right.

But why is the attached program, running 3 cascading v15 servers, 
showing 'logical' in the middle server's (port 6526) 
pg_replication_slots.slot_type ?  Surely that is not physical but 
logical replication?

  port |  svn   |     slot_name      | slot_type
------+--------+--------------------+-----------
  6526 | 150003 | pub_6527_from_6526 | logical   <--
(1 row)

I must be confused -- I will be thankful for enlightenment.

Erik

> Thanks,
> 
> Jonathan
> 

Attachments:

  [application/x-shellscript] logrep_cascade_15.sh (6.7K, ../../[email protected]/2-logrep_cascade_15.sh)
  download

^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* Re: PostgreSQL 16 release announcement draft
  2023-08-19 19:38 PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-20 02:18 ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 10:20   ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 12:02     ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-23 17:55       ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-23 21:07         ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-24 14:32           ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
@ 2023-08-24 15:19             ` Alvaro Herrera <[email protected]>
  2023-08-24 16:54               ` Re: PostgreSQL 16 release announcement draft Dave Cramer <[email protected]>
  2023-08-26 03:31               ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2 siblings, 2 replies; 37+ messages in thread

From: Alvaro Herrera @ 2023-08-24 15:19 UTC (permalink / raw)
  To: Jonathan S. Katz <[email protected]>; +Cc: David Rowley <[email protected]>; jian he <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected]

On 2023-Aug-24, Jonathan S. Katz wrote:

> ### Performance Improvements
> 
> PostgreSQL 16 improves the performance of existing PostgreSQL functionality
> through new query planner optimizations. In this latest release, the query
> planner can parallelize  `FULL` and `RIGHT` joins, generate better optimized
> plans for queries that use aggregate functions (e.g. `count`) with a `DISTINCT`
> or `ORDER BY` clause, utilize incremental sorts for `SELECT DISTINCT` queries,
> and optimize window function executions so they execute more efficiently.

"optimize window function executions so that they execute blah" sounds
redundant and strange. Maybe just "optimize execution of window
functions" is sufficient?  Also, using "e.g." there looks somewhat out
of place; maybe "(such as `count`)" is a good replacement?

> It also introduces `RIGHT` and `OUTER` "anti-joins", which enable users to
> identify rows not present in a joined table.

Wait.  Are you saying we didn't have those already?  Looking at
release-16.sgml I think this refers to commit 16dc2703c541, which means
this made them more efficient rather than invented them.


> This release includes improvements for bulk loading using `COPY` in both single
> and concurrent operations, with tests showing up to a 300% performance
> improvement in some cases. PostgreSQL adds support for load balancing in clients

PostgreSQL 16

> that use `libpq`, and improvements to vacuum strategy that reduce the necessity
> of full-table freezes. Additionally, PostgreSQL 16 introduces CPU acceleration
> using `SIMD` in both x86 and ARM architectures, resulting in performance gains
> when processing ASCII and JSON strings, and performing array and subtransaction
> searches.
> 
> ### Logical replication 
> 
> Logical replication lets PostgreSQL users stream data to other PostgreSQL

"L.R. in PostgreSQL lets users"?

> instances or subscribers that can interpret the PostgreSQL logical replication
> protocol. In PostgreSQL 16, users can perform logical decoding from a standby

s/decoding/replication/ ? (It seems odd to use "decoding" when the
previous sentence used "replication")

> instance, meaning a standby can publish logical changes to other servers. This
> provides developers with new workload distribution options – for example, using
> a standby rather than the busier primary to logically replicate changes to
> downstream systems.
> 
> Additionally, there are several performance improvements in PostgreSQL 16 to
> logical replication. Subscribers can now apply large transactions using parallel
> workers. For tables that do not have a `PRIMARY KEY`, subscribers can use B-tree

"a primary key", no caps.

> indexes instead of sequential scans to find rows. Under certain conditions,
> users can also speed up initial table synchronization using the binary format.
> 
> There are several access control improvements to logical replication in
> PostgreSQL 16, including the new predefined role pg_create_subscription, which
> grants users the ability to create a new logical subscription. Finally, this
> release begins adding support for bidirectional logical replication, introducing
> functionality to replicate data between two tables from different publishers.

"to create a new logical subscription" -> "to create new logical subscriptions"

> ### Developer Experience
> 
> PostgreSQL 16 adds more syntax from the SQL/JSON standard, including
> constructors and predicates such as `JSON_ARRAY()`, `JSON_ARRAYAGG()`, and
> `IS JSON`. This release also introduces the ability to use underscores for
> thousands separators (e.g. `5_432_000`) and non-decimal integer literals, such
> as `0x1538`, `0o12470`, and `0b1010100111000`.
> 
> Developers using PostgreSQL 16 will also benefit from the addition of multiple
> commands to `psql` client protocol, including the `\bind` command, which allows
> users to execute parameterized queries (e.g `SELECT $1 + $2`) then use `\bind`
> to substitute the variables. 

This paragraph sounds a bit suspicious.  What do you mean with "multiple
commands to psql client protocol"?  Also, I think "to execute parameterized
queries" should be "to prepare parameterized queries", and later "then
use \bind to execute the query substituting the variables".



> ### Monitoring
> 
> A key aspect of tuning the performance of database workloads is understanding
> the impact of your I/O operations on your system. PostgreSQL 16 helps simplify
> how you can analyze this data with the new pg_stat_io view, which tracks key I/O
> statistics such as shared_buffer hits and I/O latency.

Hmm, I think what pg_stat_io gives you is data which wasn't available
previously at all.  Maybe do something like "Pg 16 introduces
pg_stat_io, a new source of key I/O metrics that can be used for more
fine grained something something".

> Additionally, this release adds a new field to the `pg_stat_all_tables` view 
> that records a timestamp representing when a table or index was last scanned.
> PostgreSQL also makes auto_explain more readable by logging values passed into

PostgreSQL 16

> parameterized statements, and improves accuracy of pg_stat_activity's
> normalization algorithm.

I think jian already mentioned that this refers to pg_stat_statement
query fingerprinting.  I know that the query_id also appears in
pg_stat_activity, but that is much newer, and it's not permanent there
like in pss.  Maybe it should be "of the query fingerprinting algorithm
used by pg_stat_statement and pg_stat_activity".

> ## Images and Logos
> 
> Postgres, PostgreSQL, and the Elephant Logo (Slonik) are all registered
> trademarks of the [PostgreSQL Community Association of Canada](https://www.postgres.ca).

Isn't this just the "PostgreSQL Community Association", no Canada?



-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Ellos andaban todos desnudos como su madre los parió, y también las mujeres,
aunque no vi más que una, harto moza, y todos los que yo vi eran todos
mancebos, que ninguno vi de edad de más de XXX años" (Cristóbal Colón)





^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* Re: PostgreSQL 16 release announcement draft
  2023-08-19 19:38 PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-20 02:18 ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 10:20   ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 12:02     ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-23 17:55       ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-23 21:07         ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-24 14:32           ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-24 15:19             ` Re: PostgreSQL 16 release announcement draft Alvaro Herrera <[email protected]>
@ 2023-08-24 16:54               ` Dave Cramer <[email protected]>
  2023-08-26 02:50                 ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  1 sibling, 1 reply; 37+ messages in thread

From: Dave Cramer @ 2023-08-24 16:54 UTC (permalink / raw)
  To: Alvaro Herrera <[email protected]>; +Cc: Jonathan S. Katz <[email protected]>; David Rowley <[email protected]>; jian he <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected]

>
>
> > Postgres, PostgreSQL, and the Elephant Logo (Slonik) are all registered
> > trademarks of the [PostgreSQL Community Association of Canada](
> https://www.postgres.ca).
>
> Isn't this just the "PostgreSQL Community Association", no Canada?
>

Certainly confusing from the website, but in the about section is this
"PostgreSQL Community Association is a trade or business name of the PostgreSQL
Community Association of Canada."

Dave


^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* Re: PostgreSQL 16 release announcement draft
  2023-08-19 19:38 PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-20 02:18 ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 10:20   ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 12:02     ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-23 17:55       ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-23 21:07         ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-24 14:32           ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-24 15:19             ` Re: PostgreSQL 16 release announcement draft Alvaro Herrera <[email protected]>
  2023-08-24 16:54               ` Re: PostgreSQL 16 release announcement draft Dave Cramer <[email protected]>
@ 2023-08-26 02:50                 ` Jonathan S. Katz <[email protected]>
  0 siblings, 0 replies; 37+ messages in thread

From: Jonathan S. Katz @ 2023-08-26 02:50 UTC (permalink / raw)
  To: Dave Cramer <[email protected]>; Alvaro Herrera <[email protected]>; +Cc: David Rowley <[email protected]>; jian he <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected]

On 8/24/23 12:54 PM, Dave Cramer wrote:
> 
> 
> 
>      > Postgres, PostgreSQL, and the Elephant Logo (Slonik) are all
>     registered
>      > trademarks of the [PostgreSQL Community Association of
>     Canada](https://www.postgres.ca <https://www.postgres.ca;).
> 
>     Isn't this just the "PostgreSQL Community Association", no Canada?
> 
> 
> Certainly confusing from the website, but in the about section is this
> "PostgreSQL Community Association is a trade or business name of the 
> PostgreSQL Community Association of Canada."

This was something I missed when reviewing the fulltext, and went ahead 
and fixed it. Thanks,

Jonathan



Attachments:

  [application/pgp-signature] OpenPGP_signature (840B, ../../[email protected]/2-OpenPGP_signature)
  download

^ permalink  raw  reply  [nested|flat] 37+ messages in thread

* Re: PostgreSQL 16 release announcement draft
  2023-08-19 19:38 PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-20 02:18 ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 10:20   ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
  2023-08-23 12:02     ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-23 17:55       ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-23 21:07         ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
  2023-08-24 14:32           ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
  2023-08-24 15:19             ` Re: PostgreSQL 16 release announcement draft Alvaro Herrera <[email protected]>
@ 2023-08-26 03:31               ` Jonathan S. Katz <[email protected]>
  1 sibling, 0 replies; 37+ messages in thread

From: Jonathan S. Katz @ 2023-08-26 03:31 UTC (permalink / raw)
  To: Alvaro Herrera <[email protected]>; +Cc: David Rowley <[email protected]>; jian he <[email protected]>; PostgreSQL Hackers <[email protected]>; [email protected]

On 8/24/23 11:19 AM, Alvaro Herrera wrote:
> On 2023-Aug-24, Jonathan S. Katz wrote:
> 
>> ### Performance Improvements
>>
>> PostgreSQL 16 improves the performance of existing PostgreSQL functionality
>> through new query planner optimizations. In this latest release, the query
>> planner can parallelize  `FULL` and `RIGHT` joins, generate better optimized
>> plans for queries that use aggregate functions (e.g. `count`) with a `DISTINCT`
>> or `ORDER BY` clause, utilize incremental sorts for `SELECT DISTINCT` queries,
>> and optimize window function executions so they execute more efficiently.
> 
> "optimize window function executions so that they execute blah" sounds
> redundant and strange. Maybe just "optimize execution of window
> functions" is sufficient?  Also, using "e.g." there looks somewhat out
> of place; maybe "(such as `count`)" is a good replacement?
> 
>> It also introduces `RIGHT` and `OUTER` "anti-joins", which enable users to
>> identify rows not present in a joined table.
> 
> Wait.  Are you saying we didn't have those already?  Looking at
> release-16.sgml I think this refers to commit 16dc2703c541, which means
> this made them more efficient rather than invented them.
> 
> 
>> This release includes improvements for bulk loading using `COPY` in both single
>> and concurrent operations, with tests showing up to a 300% performance
>> improvement in some cases. PostgreSQL adds support for load balancing in clients
> 
> PostgreSQL 16
> 
>> that use `libpq`, and improvements to vacuum strategy that reduce the necessity
>> of full-table freezes. Additionally, PostgreSQL 16 introduces CPU acceleration
>> using `SIMD` in both x86 and ARM architectures, resulting in performance gains
>> when processing ASCII and JSON strings, and performing array and subtransaction
>> searches.
>>
>> ### Logical replication
>>
>> Logical replication lets PostgreSQL users stream data to other PostgreSQL
> 
> "L.R. in PostgreSQL lets users"?
> 
>> instances or subscribers that can interpret the PostgreSQL logical replication
>> protocol. In PostgreSQL 16, users can perform logical decoding from a standby
> 
> s/decoding/replication/ ? (It seems odd to use "decoding" when the
> previous sentence used "replication")
> 
>> instance, meaning a standby can publish logical changes to other servers. This
>> provides developers with new workload distribution options – for example, using
>> a standby rather than the busier primary to logically replicate changes to
>> downstream systems.
>>
>> Additionally, there are several performance improvements in PostgreSQL 16 to
>> logical replication. Subscribers can now apply large transactions using parallel
>> workers. For tables that do not have a `PRIMARY KEY`, subscribers can use B-tree
> 
> "a primary key", no caps.
> 
>> indexes instead of sequential scans to find rows. Under certain conditions,
>> users can also speed up initial table synchronization using the binary format.
>>
>> There are several access control improvements to logical replication in
>> PostgreSQL 16, including the new predefined role pg_create_subscription, which
>> grants users the ability to create a new logical subscription. Finally, this
>> release begins adding support for bidirectional logical replication, introducing
>> functionality to replicate data between two tables from different publishers.
> 
> "to create a new logical subscription" -> "to create new logical subscriptions"
> 
>> ### Developer Experience
>>
>> PostgreSQL 16 adds more syntax from the SQL/JSON standard, including
>> constructors and predicates such as `JSON_ARRAY()`, `JSON_ARRAYAGG()`, and
>> `IS JSON`. This release also introduces the ability to use underscores for
>> thousands separators (e.g. `5_432_000`) and non-decimal integer literals, such
>> as `0x1538`, `0o12470`, and `0b1010100111000`.
>>
>> Developers using PostgreSQL 16 will also benefit from the addition of multiple
>> commands to `psql` client protocol, including the `\bind` command, which allows
>> users to execute parameterized queries (e.g `SELECT $1 + $2`) then use `\bind`
>> to substitute the variables.
> 
> This paragraph sounds a bit suspicious.  What do you mean with "multiple
> commands to psql client protocol"?  Also, I think "to execute parameterized
> queries" should be "to prepare parameterized queries", and later "then
> use \bind to execute the query substituting the variables".
> 
> 
> 
>> ### Monitoring
>>
>> A key aspect of tuning the performance of database workloads is understanding
>> the impact of your I/O operations on your system. PostgreSQL 16 helps simplify
>> how you can analyze this data with the new pg_stat_io view, which tracks key I/O
>> statistics such as shared_buffer hits and I/O latency.
> 
> Hmm, I think what pg_stat_io gives you is data which wasn't available
> previously at all.  Maybe do something like "Pg 16 introduces
> pg_stat_io, a new source of key I/O metrics that can be used for more
> fine grained something something".
> 
>> Additionally, this release adds a new field to the `pg_stat_all_tables` view
>> that records a timestamp representing when a table or index was last scanned.
>> PostgreSQL also makes auto_explain more readable by logging values passed into
> 
> PostgreSQL 16
> 
>> parameterized statements, and improves accuracy of pg_stat_activity's
>> normalization algorithm.
> 
> I think jian already mentioned that this refers to pg_stat_statement
> query fingerprinting.  I know that the query_id also appears in
> pg_stat_activity, but that is much newer, and it's not permanent there
> like in pss.  Maybe it should be "of the query fingerprinting algorithm
> used by pg_stat_statement and pg_stat_activity".
> 
>> ## Images and Logos
>>
>> Postgres, PostgreSQL, and the Elephant Logo (Slonik) are all registered
>> trademarks of the [PostgreSQL Community Association of Canada](https://www.postgres.ca).
> 
> Isn't this just the "PostgreSQL Community Association", no Canada?

Thanks for the feedback. I accepted most of the changes. Please see 
revised text here, which also includes the URL substitutions.

Jonathan


September 14, 2023 - The PostgreSQL Global Development Group today announced the
release of PostgreSQL 16, the latest version of the world's most advanced open
source database.

[PostgreSQL 16](https://www.postgresql.org/docs/16/release-16.html) raises its
performance, with notable improvements to query parallelism, bulk data loading,
and logical replication. There are many features in this release for developers
and administrators alike, including more SQL/JSON syntax, new monitoring stats
for your workloads, and greater flexibility in defining access control rules for
management of policies across large fleets.

<HOLD FOR QUOTE>

PostgreSQL, an innovative data management system known for its reliability and
robustness, benefits from over 25 years of open source development from a global
developer community and has become the preferred open source relational database
for organizations of all sizes.

### Performance Improvements

PostgreSQL 16 improves the performance of existing PostgreSQL functionality
through new query planner optimizations. In this latest release, the
[query planner can parallelize](https://www.postgresql.org/docs/16/parallel-query.html)
`FULL` and `RIGHT`
[joins](https://www.postgresql.org/docs/16/queries-table-expressions.html#QUERIES-JOIN),
generate better optimized plans for queries that use
[aggregate functions](https://www.postgresql.org/docs/16/functions-aggregate.html)
with a `DISTINCT` or `ORDER BY` clause, utilize incremental sorts for
[`SELECT DISTINCT`](https://www.postgresql.org/docs/16/queries-select-lists.html#QUERIES-DISTINCT)
queries, and optimize
[window functions](https://www.postgresql.org/docs/16/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS)
so they execute more efficiently. It also improves `RIGHT` and `OUTER`
"anti-joins", which enables users to identify rows not present in a joined
table.

This release includes improvements for bulk loading using
[`COPY`](https://www.postgresql.org/docs/16/sql-copy.html) in both single
and concurrent operations, with tests showing up to a 300% performance
improvement in some cases. PostgreSQL 16 adds support for
[load balancing](https://www.postgresql.org/docs/16/libpq-connect.html#LIBPQ-CONNECT-LOAD-BALANCE-HOSTS)
in clients that use `libpq`, and improvements to vacuum strategy that reduce the
necessity of full-table freezes. Additionally, PostgreSQL 16 introduces CPU
acceleration using `SIMD` in both x86 and ARM architectures, resulting in
performance gains when processing ASCII and JSON strings, and performing array
and subtransaction searches.

### Logical replication 

[Logical replication](https://www.postgresql.org/docs/16/logical-replication.html)
lets users stream data to other PostgreSQL instances or subscribers that can
interpret the PostgreSQL logical replication protocol. In PostgreSQL 16, users
can perform logical replication from a standby instance, meaning a standby can
publish logical changes to other servers. This provides developers with new
workload distribution options – for example, using a standby rather than the
busier primary to logically replicate changes to downstream systems.

Additionally, there are several performance improvements in PostgreSQL 16 to
logical replication. Subscribers can now apply large transactions using parallel
workers. For tables that do not have a [primary key](https://www.postgresql.org/docs/16/ddl-constraints.html#DDL-CONSTRAINTS-PRIMARY-KEYS), subscribers can use B-tree
indexes instead of sequential scans to find rows. Under certain conditions,
users can also speed up initial table synchronization using the binary format.

There are several access control improvements to logical replication in
PostgreSQL 16, including the new
[predefined role](https://www.postgresql.org/docs/16/predefined-roles.html)
`pg_create_subscription`, which grants users the ability to create anew logical
subscriptions. Finally, this release begins adding support for bidirectional
logical replication, introducing functionality to replicate data between two
tables from different publishers.

### Developer Experience

PostgreSQL 16 adds more syntax from the
[SQL/JSON](https://www.postgresql.org/docs/16/functions-json.html) standard,
including constructors and predicates such as `JSON_ARRAY()`, `JSON_ARRAYAGG()`,
and `IS JSON`. This release also introduces the ability to use underscores for
thousands separators (e.g. `5_432_000`) and non-decimal integer literals, such
as `0x1538`, `0o12470`, and `0b1010100111000`.

Developers using PostgreSQL 16 also benefit from new commands in `psql`. This
includes
[`\bind`](https://www.postgresql.org/docs/16/app-psql.html#APP-PSQL-META-COMMAND-BIND),
which allows users to prepare parameterized queries and use `\bind` to
substitute the variables (e.g `SELECT $1::int + $2::int \bind 1 2 \g `). 

PostgreSQL 16 improves general support for
[text collations](https://www.postgresql.org/docs/16/collation.html), which
provide rules for how text is sorted. PostgreSQL 16 builds with ICU support by
default, determines the default ICU locale from the environment, and allows
users to define custom ICU collation rules.

### Monitoring

A key aspect of tuning the performance of database workloads is understanding
the impact of your I/O operations on your system. PostgreSQL 16 introduces
[`pg_stat_io`](https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW),
a new source of key I/O metrics for granular analysis of I/O access patterns.

Additionally, this release adds a new field to the
[`pg_stat_all_tables`](https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW)
view  that records a timestamp representing when a table or index was last
scanned. PostgreSQL 16 also makes
[`auto_explain`](https://www.postgresql.org/docs/16/auto-explain.html) more
readable by logging values passed into parameterized statements, and improves
the accuracy of the query tracking algorithm used by
[`pg_stat_statements`](https://www.postgresql.org/docs/16/pgstatstatements.html)
and [`pg_stat_activity`](https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW).

### Access Control & Security

PostgreSQL 16 provides finer-grained options for access control and enhances
other security features. The release improves management of
[`pg_hba.conf`](https://www.postgresql.org/docs/16/auth-pg-hba-conf.html) and
[`pg_ident.conf`](https://www.postgresql.org/docs/16/auth-username-maps.html)
files, including allowing regular expression matching for user and database
names and `include` directives for external configuration files.

This release adds several security-oriented client connection parameters,
including require_auth, which allows clients to specify which authentication
parameters they are willing to accept from a server, and
[`sslrootcert="system"`](https://www.postgresql.org/docs/16/libpq-connect.html#LIBPQ-CONNECT-SSLROOTCERT),
which indicates that PostgreSQL should use the trusted certificate authority
(CA) store provided by the client's operating system. Additionally, the release
adds support for Kerberos credential delegation, allowing extensions such as
[`postgres_fdw`](https://www.postgresql.org/docs/16/postgres-fdw.html) and
[`dblink`](https://www.postgresql.org/docs/16/dblink.html) to use authenticated
credentials to connect to trusted services.

### About PostgreSQL

[PostgreSQL](https://www.postgresql.org) is the world's most advanced open
source database, with a global community of thousands of users, contributors,
companies and organizations. Built on over 35 years of engineering, starting at
the University of California, Berkeley, PostgreSQL has continued with an
unmatched pace of development. PostgreSQL's mature feature set not only matches
top proprietary database systems, but exceeds them in advanced database
features, extensibility, security, and stability.

### Links

* [Download](https://www.postgresql.org/download/)
* [Release Notes](https://www.postgresql.org/docs/16/release-16.html)
* [Press Kit](https://www.postgresql.org/about/press/)
* [Security Page](https://www.postgresql.org/support/security/)
* [Versioning Policy](https://www.postgresql.org/support/versioning/)
* [Follow @postgresql](https://twitter.com/postgresql)
* [Donate](https://www.postgresql.org/about/donate/)

## More About the Features

For explanations of the above features and others, please see the following
resources:

* [Release Notes](https://www.postgresql.org/docs/16/release-16.html)
* [Feature Matrix](https://www.postgresql.org/about/featurematrix/)

## Where to Download

There are several ways you can download PostgreSQL 16, including:

* The [Official Downloads](https://www.postgresql.org/download/) page, with contains installers and tools for [Windows](https://www.postgresql.org/download/windows/), [Linux](https://www.postgresql.org/download/linux/), [macOS](https://www.postgresql.org/download/macosx/), and more.
* [Source Code](https://www.postgresql.org/ftp/source/v16.0)

Other tools and extensions are available on the
[PostgreSQL Extension Network](http://pgxn.org/).

## Documentation

PostgreSQL 16 comes with HTML documentation as well as man pages, and you can also browse the documentation online in both [HTML](https://www.postgresql.org/docs/16/) and [PDF](https://www.postgresql.org/files/documentation/pdf/16/postgresql-16-US.pdf) formats.

## Licence

PostgreSQL uses the [PostgreSQL License](https://www.postgresql.org/about/licence/),
a BSD-like "permissive" license. This
[OSI-certified license](http://www.opensource.org/licenses/postgresql/) is
widely appreciated as flexible and business-friendly, since it does not restrict
the use of PostgreSQL with commercial and proprietary applications. Together
with multi-company support and public ownership of the code, our license makes
PostgreSQL very popular with vendors wanting to embed a database in their own
products without fear of fees, vendor lock-in, or changes in licensing terms.

## Contacts

Website

* [https://www.postgresql.org/](https://www.postgresql.org/)

Email

* [[email protected]](mailto:[email protected])

## Images and Logos

Postgres, PostgreSQL, and the Elephant Logo (Slonik) are all registered
trademarks of the [PostgreSQL Community Association](https://www.postgres.ca).
If you wish to use these marks, you must comply with the [trademark policy](https://www.postgresql.org/about/policies/trademarks/).

## Corporate Support

PostgreSQL enjoys the support of numerous companies, who sponsor developers,
provide hosting resources, and give us financial support. See our
[sponsors](https://www.postgresql.org/about/sponsors/) page for some of these
project supporters.

There is also a large community of
[companies offering PostgreSQL Support](https://www.postgresql.org/support/professional_support/),
from individual consultants to multinational companies.

If you wish to make a financial contribution to the PostgreSQL Global
Development Group or one of the recognized community non-profit organizations,
please visit our [donations](https://www.postgresql.org/about/donate/) page.


Attachments:

  [text/plain] release.en.md (10.9K, ../../[email protected]/2-release.en.md)
  download | inline:
September 14, 2023 - The PostgreSQL Global Development Group today announced the
release of PostgreSQL 16, the latest version of the world's most advanced open
source database.

[PostgreSQL 16](https://www.postgresql.org/docs/16/release-16.html) raises its
performance, with notable improvements to query parallelism, bulk data loading,
and logical replication. There are many features in this release for developers
and administrators alike, including more SQL/JSON syntax, new monitoring stats
for your workloads, and greater flexibility in defining access control rules for
management of policies across large fleets.

<HOLD FOR QUOTE>

PostgreSQL, an innovative data management system known for its reliability and
robustness, benefits from over 25 years of open source development from a global
developer community and has become the preferred open source relational database
for organizations of all sizes.

### Performance Improvements

PostgreSQL 16 improves the performance of existing PostgreSQL functionality
through new query planner optimizations. In this latest release, the
[query planner can parallelize](https://www.postgresql.org/docs/16/parallel-query.html)
`FULL` and `RIGHT`
[joins](https://www.postgresql.org/docs/16/queries-table-expressions.html#QUERIES-JOIN),
generate better optimized plans for queries that use
[aggregate functions](https://www.postgresql.org/docs/16/functions-aggregate.html)
with a `DISTINCT` or `ORDER BY` clause, utilize incremental sorts for
[`SELECT DISTINCT`](https://www.postgresql.org/docs/16/queries-select-lists.html#QUERIES-DISTINCT)
queries, and optimize
[window functions](https://www.postgresql.org/docs/16/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS)
so they execute more efficiently. It also improves `RIGHT` and `OUTER`
"anti-joins", which enables users to identify rows not present in a joined
table.

This release includes improvements for bulk loading using
[`COPY`](https://www.postgresql.org/docs/16/sql-copy.html) in both single
and concurrent operations, with tests showing up to a 300% performance
improvement in some cases. PostgreSQL 16 adds support for
[load balancing](https://www.postgresql.org/docs/16/libpq-connect.html#LIBPQ-CONNECT-LOAD-BALANCE-HOSTS)
in clients that use `libpq`, and improvements to vacuum strategy that reduce the
necessity of full-table freezes. Additionally, PostgreSQL 16 introduces CPU
acceleration using `SIMD` in both x86 and ARM architectures, resulting in
performance gains when processing ASCII and JSON strings, and performing array
and subtransaction searches.

### Logical replication 

[Logical replication](https://www.postgresql.org/docs/16/logical-replication.html)
lets users stream data to other PostgreSQL instances or subscribers that can
interpret the PostgreSQL logical replication protocol. In PostgreSQL 16, users
can perform logical replication from a standby instance, meaning a standby can
publish logical changes to other servers. This provides developers with new
workload distribution options – for example, using a standby rather than the
busier primary to logically replicate changes to downstream systems.

Additionally, there are several performance improvements in PostgreSQL 16 to
logical replication. Subscribers can now apply large transactions using parallel
workers. For tables that do not have a [primary key](https://www.postgresql.org/docs/16/ddl-constraints.html#DDL-CONSTRAINTS-PRIMARY-KEYS), subscribers can use B-tree
indexes instead of sequential scans to find rows. Under certain conditions,
users can also speed up initial table synchronization using the binary format.

There are several access control improvements to logical replication in
PostgreSQL 16, including the new
[predefined role](https://www.postgresql.org/docs/16/predefined-roles.html)
`pg_create_subscription`, which grants users the ability to create anew logical
subscriptions. Finally, this release begins adding support for bidirectional
logical replication, introducing functionality to replicate data between two
tables from different publishers.

### Developer Experience

PostgreSQL 16 adds more syntax from the
[SQL/JSON](https://www.postgresql.org/docs/16/functions-json.html) standard,
including constructors and predicates such as `JSON_ARRAY()`, `JSON_ARRAYAGG()`,
and `IS JSON`. This release also introduces the ability to use underscores for
thousands separators (e.g. `5_432_000`) and non-decimal integer literals, such
as `0x1538`, `0o12470`, and `0b1010100111000`.

Developers using PostgreSQL 16 also benefit from new commands in `psql`. This
includes
[`\bind`](https://www.postgresql.org/docs/16/app-psql.html#APP-PSQL-META-COMMAND-BIND),
which allows users to prepare parameterized queries and use `\bind` to
substitute the variables (e.g `SELECT $1::int + $2::int \bind 1 2 \g `). 

PostgreSQL 16 improves general support for
[text collations](https://www.postgresql.org/docs/16/collation.html), which
provide rules for how text is sorted. PostgreSQL 16 builds with ICU support by
default, determines the default ICU locale from the environment, and allows
users to define custom ICU collation rules.

### Monitoring

A key aspect of tuning the performance of database workloads is understanding
the impact of your I/O operations on your system. PostgreSQL 16 introduces
[`pg_stat_io`](https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW),
a new source of key I/O metrics for granular analysis of I/O access patterns.

Additionally, this release adds a new field to the
[`pg_stat_all_tables`](https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW)
view  that records a timestamp representing when a table or index was last
scanned. PostgreSQL 16 also makes
[`auto_explain`](https://www.postgresql.org/docs/16/auto-explain.html) more
readable by logging values passed into parameterized statements, and improves
the accuracy of the query tracking algorithm used by
[`pg_stat_statements`](https://www.postgresql.org/docs/16/pgstatstatements.html)
and [`pg_stat_activity`](https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW).

### Access Control & Security

PostgreSQL 16 provides finer-grained options for access control and enhances
other security features. The release improves management of
[`pg_hba.conf`](https://www.postgresql.org/docs/16/auth-pg-hba-conf.html) and
[`pg_ident.conf`](https://www.postgresql.org/docs/16/auth-username-maps.html)
files, including allowing regular expression matching for user and database
names and `include` directives for external configuration files.

This release adds several security-oriented client connection parameters,
including require_auth, which allows clients to specify which authentication
parameters they are willing to accept from a server, and
[`sslrootcert="system"`](https://www.postgresql.org/docs/16/libpq-connect.html#LIBPQ-CONNECT-SSLROOTCERT),
which indicates that PostgreSQL should use the trusted certificate authority
(CA) store provided by the client's operating system. Additionally, the release
adds support for Kerberos credential delegation, allowing extensions such as
[`postgres_fdw`](https://www.postgresql.org/docs/16/postgres-fdw.html) and
[`dblink`](https://www.postgresql.org/docs/16/dblink.html) to use authenticated
credentials to connect to trusted services.

### About PostgreSQL

[PostgreSQL](https://www.postgresql.org) is the world's most advanced open
source database, with a global community of thousands of users, contributors,
companies and organizations. Built on over 35 years of engineering, starting at
the University of California, Berkeley, PostgreSQL has continued with an
unmatched pace of development. PostgreSQL's mature feature set not only matches
top proprietary database systems, but exceeds them in advanced database
features, extensibility, security, and stability.

### Links

* [Download](https://www.postgresql.org/download/)
* [Release Notes](https://www.postgresql.org/docs/16/release-16.html)
* [Press Kit](https://www.postgresql.org/about/press/)
* [Security Page](https://www.postgresql.org/support/security/)
* [Versioning Policy](https://www.postgresql.org/support/versioning/)
* [Follow @postgresql](https://twitter.com/postgresql)
* [Donate](https://www.postgresql.org/about/donate/)

## More About the Features

For explanations of the above features and others, please see the following
resources:

* [Release Notes](https://www.postgresql.org/docs/16/release-16.html)
* [Feature Matrix](https://www.postgresql.org/about/featurematrix/)

## Where to Download

There are several ways you can download PostgreSQL 16, including:

* The [Official Downloads](https://www.postgresql.org/download/) page, with contains installers and tools for [Windows](https://www.postgresql.org/download/windows/), [Linux](https://www.postgresql.org/download/linux/), [macOS](https://www.postgresql.org/download/macosx/), and more.
* [Source Code](https://www.postgresql.org/ftp/source/v16.0)

Other tools and extensions are available on the
[PostgreSQL Extension Network](http://pgxn.org/).

## Documentation

PostgreSQL 16 comes with HTML documentation as well as man pages, and you can also browse the documentation online in both [HTML](https://www.postgresql.org/docs/16/) and [PDF](https://www.postgresql.org/files/documentation/pdf/16/postgresql-16-US.pdf) formats.

## Licence

PostgreSQL uses the [PostgreSQL License](https://www.postgresql.org/about/licence/),
a BSD-like "permissive" license. This
[OSI-certified license](http://www.opensource.org/licenses/postgresql/) is
widely appreciated as flexible and business-friendly, since it does not restrict
the use of PostgreSQL with commercial and proprietary applications. Together
with multi-company support and public ownership of the code, our license makes
PostgreSQL very popular with vendors wanting to embed a database in their own
products without fear of fees, vendor lock-in, or changes in licensing terms.

## Contacts

Website

* [https://www.postgresql.org/](https://www.postgresql.org/)

Email

* [[email protected]](mailto:[email protected])

## Images and Logos

Postgres, PostgreSQL, and the Elephant Logo (Slonik) are all registered
trademarks of the [PostgreSQL Community Association](https://www.postgres.ca).
If you wish to use these marks, you must comply with the [trademark policy](https://www.postgresql.org/about/policies/trademarks/).

## Corporate Support

PostgreSQL enjoys the support of numerous companies, who sponsor developers,
provide hosting resources, and give us financial support. See our
[sponsors](https://www.postgresql.org/about/sponsors/) page for some of these
project supporters.

There is also a large community of
[companies offering PostgreSQL Support](https://www.postgresql.org/support/professional_support/),
from individual consultants to multinational companies.

If you wish to make a financial contribution to the PostgreSQL Global
Development Group or one of the recognized community non-profit organizations,
please visit our [donations](https://www.postgresql.org/about/donate/) page.

  [application/pgp-signature] OpenPGP_signature (840B, ../../[email protected]/3-OpenPGP_signature)
  download

^ permalink  raw  reply  [nested|flat] 37+ messages in thread

end of thread, other threads:[~2023-08-26 04:28 UTC | newest]

Thread overview: 37+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2020-08-12 18:02 [PATCH v4] fix HOT tuples while scanning for index builds Alvaro Herrera <[email protected]>
2023-08-19 19:38 PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
2023-08-20 02:18 ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
2023-08-23 10:20   ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
2023-08-23 12:02     ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
2023-08-23 17:55       ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
2023-08-23 21:07         ` Re: PostgreSQL 16 release announcement draft David Rowley <[email protected]>
2023-08-24 14:32           ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
2023-08-24 15:16             ` Re: PostgreSQL 16 release announcement draft jian he <[email protected]>
2023-08-24 15:23               ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
2023-08-24 15:38                 ` Re: PostgreSQL 16 release announcement draft Chapman Flack <[email protected]>
2023-08-24 15:17             ` Re: PostgreSQL 16 release announcement draft Erik Rijkers <[email protected]>
2023-08-26 02:51               ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
2023-08-26 04:28                 ` Re: PostgreSQL 16 release announcement draft Erik Rijkers <[email protected]>
2023-08-24 15:19             ` Re: PostgreSQL 16 release announcement draft Alvaro Herrera <[email protected]>
2023-08-24 16:54               ` Re: PostgreSQL 16 release announcement draft Dave Cramer <[email protected]>
2023-08-26 02:50                 ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>
2023-08-26 03:31               ` Re: PostgreSQL 16 release announcement draft Jonathan S. Katz <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox