From 5ae2087202af9fd804c8b8d76954bdd8bdf31e51 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Sat, 28 Oct 2023 00:21:23 +0300
Subject: Teach contrib/amcheck to check the unique constraint violation

Add the 'checkunique' argument to bt_index_check() and bt_index_parent_check().
When the flag is specified the procedures will check the unique constraint
violation for unique indexes.  Only one heap entry for all equal keys in
the index should be visible (including posting list entries).  Report an error
otherwise.

pg_amcheck called with the --checkunique option will do the same check for all
the indexes it checks.

Author: Anastasia Lubennikova <lubennikovaav@gmail.com>
Author: Pavel Borisov <pashkin.elfe@gmail.com>
Author: Maxim Orlov <orlovmg@gmail.com>
Reviewed-by: Mark Dilger <mark.dilger@enterprisedb.com>
Reviewed-by: Zhihong Yu <zyu@yugabyte.com>
Reviewed-by: Peter Geoghegan <pg@bowt.ie>
Reviewed-by: Aleksander Alekseev <aleksander@timescale.com>
Discussion: https://postgr.es/m/CALT9ZEHRn5xAM5boga0qnrCmPV52bScEK2QnQ1HmUZDD301JEg%40mail.gmail.com
---
 doc/src/sgml/amcheck.sgml        | 14 ++++++++++----
 doc/src/sgml/ref/pg_amcheck.sgml | 11 +++++++++++
 2 files changed, 21 insertions(+), 4 deletions(-)

(limited to 'doc/src')

diff --git a/doc/src/sgml/amcheck.sgml b/doc/src/sgml/amcheck.sgml
index 2b9c1a9205f..780fd05a73b 100644
--- a/doc/src/sgml/amcheck.sgml
+++ b/doc/src/sgml/amcheck.sgml
@@ -58,7 +58,7 @@
   <variablelist>
    <varlistentry>
     <term>
-     <function>bt_index_check(index regclass, heapallindexed boolean) returns void</function>
+     <function>bt_index_check(index regclass, heapallindexed boolean, checkunique boolean) returns void</function>
      <indexterm>
       <primary>bt_index_check</primary>
      </indexterm>
@@ -115,7 +115,10 @@ ORDER BY c.relpages DESC LIMIT 10;
       that span child/parent relationships, but will verify the
       presence of all heap tuples as index tuples within the index
       when <parameter>heapallindexed</parameter> is
-      <literal>true</literal>.  When a routine, lightweight test for
+      <literal>true</literal>.  When <parameter>checkunique</parameter>
+      is <literal>true</literal> <function>bt_index_check</function> will
+      check that no more than one among duplicate entries in unique
+      index is visible.  When a routine, lightweight test for
       corruption is required in a live production environment, using
       <function>bt_index_check</function> often provides the best
       trade-off between thoroughness of verification and limiting the
@@ -126,7 +129,7 @@ ORDER BY c.relpages DESC LIMIT 10;
 
    <varlistentry>
     <term>
-     <function>bt_index_parent_check(index regclass, heapallindexed boolean, rootdescend boolean) returns void</function>
+     <function>bt_index_parent_check(index regclass, heapallindexed boolean, rootdescend boolean, checkunique boolean) returns void</function>
      <indexterm>
       <primary>bt_index_parent_check</primary>
      </indexterm>
@@ -139,7 +142,10 @@ ORDER BY c.relpages DESC LIMIT 10;
       Optionally, when the <parameter>heapallindexed</parameter>
       argument is <literal>true</literal>, the function verifies the
       presence of all heap tuples that should be found within the
-      index.  When the optional <parameter>rootdescend</parameter>
+      index.  When <parameter>checkunique</parameter>
+      is <literal>true</literal> <function>bt_index_parent_check</function> will
+      check that no more than one among duplicate entries in unique
+      index is visible.  When the optional <parameter>rootdescend</parameter>
       argument is <literal>true</literal>, verification re-finds
       tuples on the leaf level by performing a new search from the
       root page for each tuple.  The checks that can be performed by
diff --git a/doc/src/sgml/ref/pg_amcheck.sgml b/doc/src/sgml/ref/pg_amcheck.sgml
index 20c2897accb..067c806b46d 100644
--- a/doc/src/sgml/ref/pg_amcheck.sgml
+++ b/doc/src/sgml/ref/pg_amcheck.sgml
@@ -432,6 +432,17 @@ PostgreSQL documentation
       </para>
      </listitem>
     </varlistentry>
+
+    <varlistentry>
+     <term><option>--checkunique</option></term>
+     <listitem>
+      <para>
+       For each index with unique constraint checked, verify that no more than
+       one among duplicate entries is visible in the index using <xref linkend="amcheck"/>'s
+       <option>checkunique</option> option.
+      </para>
+     </listitem>
+    </varlistentry>
    </variablelist>
   </para>
 
-- 
cgit v1.2.3