v1-0001-Bug-fix-do-not-check-temp-tables-from-pg_amcheck.patch

From fc2e4a14113f7b3d556707f900f1c6cf2a61c601 Mon Sep 17 00:00:00 2001
From: Mark Dilger <mark.dilger@enterprisedb.com>
Date: Sun, 3 Oct 2021 09:52:22 -0700
Subject: [PATCH v1] Bug fix: do not check temp tables from pg_amcheck

Fix BUG #17212: pg_amcheck fails on checking temporary relations

Since pg_amcheck does not create temporary tables of its own, all
temporary tables it encounters must necessarily belong to other
sessions.  Check neither temporary tables nor indexes over such
tables.
---
 src/bin/pg_amcheck/pg_amcheck.c   | 15 +++++++-------
 src/bin/pg_amcheck/t/003_check.pl | 33 +++++++++++++++++++++++++++++++
 2 files changed, 41 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_amcheck/pg_amcheck.c b/src/bin/pg_amcheck/pg_amcheck.c
index ec04b977de..33a9965a60 100644
--- a/src/bin/pg_amcheck/pg_amcheck.c
+++ b/src/bin/pg_amcheck/pg_amcheck.c
@@ -1888,11 +1888,9 @@ compile_relation_list_one_db(PGconn *conn, SimplePtrList *relations,
 						  "\nAND (c.relam = %u OR NOT ep.heap_only OR ep.rel_regex IS NULL)"
 						  "\nAND (c.relam = %u OR NOT ep.btree_only OR ep.rel_regex IS NULL)",
 						  HEAP_TABLE_AM_OID, BTREE_AM_OID);
-
+	appendPQExpBufferStr(&sql, "\nWHERE c.relpersistence != 't'");
 	if (opts.excludetbl || opts.excludeidx || opts.excludensp)
-		appendPQExpBufferStr(&sql, "\nWHERE ep.pattern_id IS NULL");
-	else
-		appendPQExpBufferStr(&sql, "\nWHERE true");
+		appendPQExpBufferStr(&sql, "\nAND ep.pattern_id IS NULL");
 
 	/*
 	 * We need to be careful not to break the --no-dependent-toast and
@@ -1944,7 +1942,8 @@ compile_relation_list_one_db(PGconn *conn, SimplePtrList *relations,
 								 "\nON ('pg_toast' ~ ep.nsp_regex OR ep.nsp_regex IS NULL)"
 								 "\nAND (t.relname ~ ep.rel_regex OR ep.rel_regex IS NULL)"
 								 "\nAND ep.heap_only"
-								 "\nWHERE ep.pattern_id IS NULL");
+								 "\nWHERE ep.pattern_id IS NULL"
+								 "\nAND t.relpersistence != 't'");
 		appendPQExpBufferStr(&sql,
 							 "\n)");
 	}
@@ -1962,7 +1961,8 @@ compile_relation_list_one_db(PGconn *conn, SimplePtrList *relations,
 						  "\nINNER JOIN pg_catalog.pg_index i "
 						  "ON r.oid = i.indrelid "
 						  "INNER JOIN pg_catalog.pg_class c "
-						  "ON i.indexrelid = c.oid");
+						  "ON i.indexrelid = c.oid "
+						  "AND c.relpersistence != 't'");
 		if (opts.excludeidx || opts.excludensp)
 			appendPQExpBufferStr(&sql,
 								 "\nINNER JOIN pg_catalog.pg_namespace n "
@@ -2000,7 +2000,8 @@ compile_relation_list_one_db(PGconn *conn, SimplePtrList *relations,
 						  "INNER JOIN pg_catalog.pg_index i "
 						  "ON t.oid = i.indrelid"
 						  "\nINNER JOIN pg_catalog.pg_class c "
-						  "ON i.indexrelid = c.oid");
+						  "ON i.indexrelid = c.oid "
+						  "AND c.relpersistence != 't'");
 		if (opts.excludeidx)
 			appendPQExpBufferStr(&sql,
 								 "\nLEFT OUTER JOIN exclude_pat ep "
diff --git a/src/bin/pg_amcheck/t/003_check.pl b/src/bin/pg_amcheck/t/003_check.pl
index c26e5eda67..de7814089e 100644
--- a/src/bin/pg_amcheck/t/003_check.pl
+++ b/src/bin/pg_amcheck/t/003_check.pl
@@ -337,6 +337,32 @@ $node->command_checks_all([ @cmd, '-d', 'db1', '-d', 'db2', '-d', 'db3' ],
 #
 perform_all_corruptions();
 
+# Hold open an unrelated session with temporary tables defined, and indexes
+# over them defined, to verify that pg_amcheck does not fail attempting to
+# check them.  Check both temporary objects whose creation has been committed
+# and not.
+#
+# BUG #17212: pg_amcheck fails on checking temporary relations
+#
+my ($sql_1, $sql_2) = map { qq(
+CREATE TEMPORARY TABLE empty_tmp$_ (i INTEGER) ON COMMIT PRESERVE ROWS;
+CREATE TEMPORARY TABLE data_tmp$_ (i INTEGER[], b BOX) ON COMMIT PRESERVE ROWS;
+INSERT INTO data_tmp$_ (i, b) VALUES (ARRAY[1,2,3]::INTEGER[], '((1,2),(3,4))'::BOX);
+CREATE INDEX brin_tmp$_ ON data_tmp$_ USING BRIN (b);
+CREATE INDEX btree_tmp$_ ON data_tmp$_ USING BTREE (i);
+CREATE INDEX gin_tmp$_ ON data_tmp$_ USING GIN (i);
+CREATE INDEX gist_tmp$_ ON data_tmp$_ USING GIST (b);
+CREATE INDEX hash_tmp$_ ON data_tmp$_ USING HASH (i);
+) } (1, 2);
+my $in = '';
+my $out = '';
+my $timer = IPC::Run::timeout(180);
+my $h = $node->background_psql('db1', \$in, \$out, $timer);
+$in .= qq{
+BEGIN; $sql_1; COMMIT; -- other sessions see these in the catalogs
+BEGIN; $sql_2; -- no commit, so these objects invisible to other sessions
+};
+$h->pump_nb;
 
 # Checking databases with amcheck installed and corrupt relations, pg_amcheck
 # command itself should return exit status = 2, because tables and indexes are
@@ -517,3 +543,10 @@ $node->command_checks_all(
 	[ @cmd, '-d', 'db1', '-d', 'db2', '-d', 'db3', '-S', 's*' ],
 	0, [$no_output_re], [$no_output_re],
 	'pg_amcheck excluding all corrupt schemas');
+
+# Finish the unrelated session.  We could let the test cleanup do this for us,
+# but we'd like to verify that nothing pg_amcheck did above breaks this other
+# session.
+$in = 'COMMIT;';
+$h->pump_nb;
+$h->finish;
-- 
2.21.1 (Apple Git-122.3)