v5-01-fix-gist_page_items-for-index-with-INCLUDE-and-diff-storage.patch

From 0680c5c6fc2134b6ba86daa4f5e0e27843acb42b Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Thu, 18 May 2023 10:48:50 +0900
Subject: [PATCH v5] pageinspect: Fix gist_page_items with included columns

Blah.

Author: Alexander Lakhin
Discussion: https://postgr.es/m/17884-cb8c326522977acb@postgresql.org
Backpatch-through: 14
---
 src/include/utils/ruleutils.h         |  5 ++
 src/backend/utils/adt/ruleutils.c     | 16 +++++
 contrib/pageinspect/expected/gist.out | 89 ++++++++++++++++++++++-----
 contrib/pageinspect/gistfuncs.c       | 81 ++++++++++++++++++++++--
 contrib/pageinspect/sql/gist.sql      | 30 +++++++++
 5 files changed, 200 insertions(+), 21 deletions(-)

diff --git a/src/include/utils/ruleutils.h b/src/include/utils/ruleutils.h
index 1a42d9f39b..b006d9d475 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -20,9 +20,14 @@
 struct Plan;					/* avoid including plannodes.h here */
 struct PlannedStmt;
 
+/* Flags for pg_get_indexdef_columns_extended() */
+#define RULE_INDEXDEF_PRETTY		0x01
+#define RULE_INDEXDEF_KEYS_ONLY		0x02	/* ignore included attributes */
 
 extern char *pg_get_indexdef_string(Oid indexrelid);
 extern char *pg_get_indexdef_columns(Oid indexrelid, bool pretty);
+extern char *pg_get_indexdef_columns_extended(Oid indexrelid,
+											  bits16 flags);
 extern char *pg_get_querydef(Query *query, bool pretty);
 
 extern char *pg_get_partkeydef_columns(Oid relid, bool pretty);
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index e93d66a7ec..6d673493cb 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1215,6 +1215,22 @@ pg_get_indexdef_columns(Oid indexrelid, bool pretty)
 								  prettyFlags, false);
 }
 
+/* Internal version, extensible with flags to control its behavior */
+char *
+pg_get_indexdef_columns_extended(Oid indexrelid, bits16 flags)
+{
+	bool		pretty = ((flags & RULE_INDEXDEF_PRETTY) != 0);
+	bool		keys_only = ((flags & RULE_INDEXDEF_KEYS_ONLY) != 0);
+	int			prettyFlags;
+
+	prettyFlags = GET_PRETTY_FLAGS(pretty);
+
+	return pg_get_indexdef_worker(indexrelid, 0, NULL,
+								  true, keys_only,
+								  false, false,
+								  prettyFlags, false);
+}
+
 /*
  * Internal workhorse to decompile an index definition.
  *
diff --git a/contrib/pageinspect/expected/gist.out b/contrib/pageinspect/expected/gist.out
index 460bef3037..c240e51415 100644
--- a/contrib/pageinspect/expected/gist.out
+++ b/contrib/pageinspect/expected/gist.out
@@ -31,24 +31,24 @@ SELECT * FROM gist_page_opaque_info(get_raw_page('test_gist_idx', 2));
 
 COMMIT;
 SELECT * FROM gist_page_items(get_raw_page('test_gist_idx', 0), 'test_gist_idx');
- itemoffset |   ctid    | itemlen | dead |       keys        
-------------+-----------+---------+------+-------------------
-          1 | (1,65535) |      40 | f    | (p)=((185,185))
-          2 | (2,65535) |      40 | f    | (p)=((370,370))
-          3 | (3,65535) |      40 | f    | (p)=((555,555))
-          4 | (4,65535) |      40 | f    | (p)=((740,740))
-          5 | (5,65535) |      40 | f    | (p)=((870,870))
-          6 | (6,65535) |      40 | f    | (p)=((1000,1000))
+ itemoffset |   ctid    | itemlen | dead |             keys              
+------------+-----------+---------+------+-------------------------------
+          1 | (1,65535) |      40 | f    | (p)=(((185,185),(1,1)))
+          2 | (2,65535) |      40 | f    | (p)=(((370,370),(186,186)))
+          3 | (3,65535) |      40 | f    | (p)=(((555,555),(371,371)))
+          4 | (4,65535) |      40 | f    | (p)=(((740,740),(556,556)))
+          5 | (5,65535) |      40 | f    | (p)=(((870,870),(741,741)))
+          6 | (6,65535) |      40 | f    | (p)=(((1000,1000),(871,871)))
 (6 rows)
 
 SELECT * FROM gist_page_items(get_raw_page('test_gist_idx', 1), 'test_gist_idx') LIMIT 5;
- itemoffset | ctid  | itemlen | dead |    keys     
-------------+-------+---------+------+-------------
-          1 | (0,1) |      40 | f    | (p)=((1,1))
-          2 | (0,2) |      40 | f    | (p)=((2,2))
-          3 | (0,3) |      40 | f    | (p)=((3,3))
-          4 | (0,4) |      40 | f    | (p)=((4,4))
-          5 | (0,5) |      40 | f    | (p)=((5,5))
+ itemoffset | ctid  | itemlen | dead |        keys         
+------------+-------+---------+------+---------------------
+          1 | (0,1) |      40 | f    | (p)=(((1,1),(1,1)))
+          2 | (0,2) |      40 | f    | (p)=(((2,2),(2,2)))
+          3 | (0,3) |      40 | f    | (p)=(((3,3),(3,3)))
+          4 | (0,4) |      40 | f    | (p)=(((4,4),(4,4)))
+          5 | (0,5) |      40 | f    | (p)=(((5,5),(5,5)))
 (5 rows)
 
 -- gist_page_items_bytea prints the raw key data as a bytea. The output of that is
@@ -107,4 +107,63 @@ SELECT gist_page_opaque_info(decode(repeat('00', :block_size), 'hex'));
  
 (1 row)
 
+-- Test gist_page_items with included columns.
+-- Non-leaf pages have only the key attributes, and leaf pages have
+-- the included attributes.
+ALTER TABLE test_gist ADD COLUMN i1 int DEFAULT NULL;
+ALTER TABLE test_gist ADD COLUMN i2 int DEFAULT 1;
+CREATE INDEX test_gist_idx_inc_1  ON test_gist
+  USING gist (p, point(i1, i1)) INCLUDE (p, i1);
+CREATE INDEX test_gist_idx_inc_12 ON test_gist
+  USING gist (p, point(i1, i2)) INCLUDE (t, i1, i2);
+CREATE INDEX test_gist_idx_inc_2  ON test_gist
+  USING gist (p, point(i2, i2)) INCLUDE (p, t, i2);
+SELECT keys AS keys_nonleaf_1
+  FROM gist_page_items(get_raw_page('test_gist_idx_inc_1', 0), 'test_gist_idx_inc_1')
+  WHERE itemoffset = 1;
+                                  keys_nonleaf_1                                  
+----------------------------------------------------------------------------------
+ (p, point(i1::double precision, i1::double precision))=(((119,119),(1,1)), null)
+(1 row)
+
+SELECT keys AS keys_leaf_1
+  FROM gist_page_items(get_raw_page('test_gist_idx_inc_1', 1), 'test_gist_idx_inc_1')
+  WHERE itemoffset = 1;
+                                                    keys_leaf_1                                                     
+--------------------------------------------------------------------------------------------------------------------
+ (p, point(i1::double precision, i1::double precision)) INCLUDE (p, i1)=(((1,1),(1,1)), null) INCLUDE ((1,1), null)
+(1 row)
+
+SELECT keys AS keys_nonleaf_12
+  FROM gist_page_items(get_raw_page('test_gist_idx_inc_12', 0), 'test_gist_idx_inc_12')
+  WHERE itemoffset = 1;
+                                 keys_nonleaf_12                                  
+----------------------------------------------------------------------------------
+ (p, point(i1::double precision, i2::double precision))=(((135,135),(1,1)), null)
+(1 row)
+
+SELECT keys AS keys_leaf_12
+  FROM gist_page_items(get_raw_page('test_gist_idx_inc_12', 1), 'test_gist_idx_inc_12')
+  WHERE itemoffset = 1;
+                                                     keys_leaf_12                                                      
+-----------------------------------------------------------------------------------------------------------------------
+ (p, point(i1::double precision, i2::double precision)) INCLUDE (t, i1, i2)=(((1,1),(1,1)), null) INCLUDE (1, null, 1)
+(1 row)
+
+SELECT keys AS keys_nonleaf_2
+  FROM gist_page_items(get_raw_page('test_gist_idx_inc_2', 0), 'test_gist_idx_inc_2')
+  WHERE itemoffset = 1;
+                                     keys_nonleaf_2                                      
+-----------------------------------------------------------------------------------------
+ (p, point(i2::double precision, i2::double precision))=(((81,81),(1,1)), ((1,1),(1,1)))
+(1 row)
+
+SELECT keys AS keys_leaf_2
+  FROM gist_page_items(get_raw_page('test_gist_idx_inc_2', 1), 'test_gist_idx_inc_2')
+  WHERE itemoffset = 1;
+                                                          keys_leaf_2                                                           
+--------------------------------------------------------------------------------------------------------------------------------
+ (p, point(i2::double precision, i2::double precision)) INCLUDE (p, t, i2)=(((1,1),(1,1)), ((1,1),(1,1))) INCLUDE ((1,1), 1, 1)
+(1 row)
+
 DROP TABLE test_gist;
diff --git a/contrib/pageinspect/gistfuncs.c b/contrib/pageinspect/gistfuncs.c
index 3dca7f1318..c11d2915dc 100644
--- a/contrib/pageinspect/gistfuncs.c
+++ b/contrib/pageinspect/gistfuncs.c
@@ -21,8 +21,10 @@
 #include "storage/itemptr.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
-#include "utils/rel.h"
 #include "utils/pg_lsn.h"
+#include "utils/lsyscache.h"
+#include "utils/rel.h"
+#include "utils/ruleutils.h"
 #include "utils/varlena.h"
 
 PG_FUNCTION_INFO_V1(gist_page_opaque_info);
@@ -198,9 +200,13 @@ gist_page_items(PG_FUNCTION_ARGS)
 	Oid			indexRelid = PG_GETARG_OID(1);
 	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
 	Relation	indexRel;
+	TupleDesc	tupdesc;
 	Page		page;
+	uint16		flagbits;
+	bits16		printflags = 0;
 	OffsetNumber offset;
 	OffsetNumber maxoff = InvalidOffsetNumber;
+	char	   *index_columns;
 
 	if (!superuser())
 		ereport(ERROR,
@@ -226,6 +232,27 @@ gist_page_items(PG_FUNCTION_ARGS)
 		PG_RETURN_NULL();
 	}
 
+	flagbits = GistPageGetOpaque(page)->flags;
+
+	/*
+	 * Included attributes are added when dealing with leaf pages, discarded
+	 * for non-leaf pages as these include only data for key attributes.
+	 */
+	printflags |= RULE_INDEXDEF_PRETTY;
+	if (flagbits & F_LEAF)
+	{
+		tupdesc = RelationGetDescr(indexRel);
+	}
+	else
+	{
+		tupdesc = CreateTupleDescCopy(RelationGetDescr(indexRel));
+		tupdesc->natts = IndexRelationGetNumberOfKeyAttributes(indexRel);
+		printflags |= RULE_INDEXDEF_KEYS_ONLY;
+	}
+
+	index_columns = pg_get_indexdef_columns_extended(indexRelid,
+													 printflags);
+
 	/* Avoid bogus PageGetMaxOffsetNumber() call with deleted pages */
 	if (GistPageIsDeleted(page))
 		elog(NOTICE, "page is deleted");
@@ -242,7 +269,8 @@ gist_page_items(PG_FUNCTION_ARGS)
 		IndexTuple	itup;
 		Datum		itup_values[INDEX_MAX_KEYS];
 		bool		itup_isnull[INDEX_MAX_KEYS];
-		char	   *key_desc;
+		StringInfoData buf;
+		int			i;
 
 		id = PageGetItemId(page, offset);
 
@@ -251,7 +279,7 @@ gist_page_items(PG_FUNCTION_ARGS)
 
 		itup = (IndexTuple) PageGetItem(page, id);
 
-		index_deform_tuple(itup, RelationGetDescr(indexRel),
+		index_deform_tuple(itup, tupdesc,
 						   itup_values, itup_isnull);
 
 		memset(nulls, 0, sizeof(nulls));
@@ -261,9 +289,50 @@ gist_page_items(PG_FUNCTION_ARGS)
 		values[2] = Int32GetDatum((int) IndexTupleSize(itup));
 		values[3] = BoolGetDatum(ItemIdIsDead(id));
 
-		key_desc = BuildIndexValueDescription(indexRel, itup_values, itup_isnull);
-		if (key_desc)
-			values[4] = CStringGetTextDatum(key_desc);
+		if (index_columns)
+		{
+			initStringInfo(&buf);
+			appendStringInfo(&buf, "(%s)=(", index_columns);
+
+			for (i = 0; i < tupdesc->natts; i++)
+			{
+				char	   *val;
+
+				if (itup_isnull[i])
+					val = "null";
+				else
+				{
+					Oid			foutoid;
+					bool		typisvarlena;
+					Oid			typoid;
+
+					typoid = tupdesc->attrs[i].atttypid;
+					getTypeOutputInfo(typoid, &foutoid, &typisvarlena);
+					val = OidOutputFunctionCall(foutoid, itup_values[i]);
+				}
+
+				if (i == IndexRelationGetNumberOfKeyAttributes(indexRel))
+					appendStringInfoString(&buf, ") INCLUDE (");
+				else if (i > 0)
+					appendStringInfoString(&buf, ", ");
+
+				/*
+				 * NULL values are printed as-is, and included attributes
+				 * don't require more parenthesis for clarity in the output.
+				 */
+				if (itup_isnull[i])
+					appendStringInfoString(&buf, val);
+				else if (i >= IndexRelationGetNumberOfKeyAttributes(indexRel))
+					appendStringInfo(&buf, "%s", val);
+				else
+					appendStringInfo(&buf, "(%s)", val);
+			}
+
+			appendStringInfoChar(&buf, ')');
+
+			values[4] = CStringGetTextDatum(buf.data);
+			nulls[4] = false;
+		}
 		else
 		{
 			values[4] = (Datum) 0;
diff --git a/contrib/pageinspect/sql/gist.sql b/contrib/pageinspect/sql/gist.sql
index 4787b784a4..5d9384907e 100644
--- a/contrib/pageinspect/sql/gist.sql
+++ b/contrib/pageinspect/sql/gist.sql
@@ -52,4 +52,34 @@ SELECT gist_page_items_bytea(decode(repeat('00', :block_size), 'hex'));
 SELECT gist_page_items(decode(repeat('00', :block_size), 'hex'), 'test_gist_idx'::regclass);
 SELECT gist_page_opaque_info(decode(repeat('00', :block_size), 'hex'));
 
+-- Test gist_page_items with included columns.
+-- Non-leaf pages have only the key attributes, and leaf pages have
+-- the included attributes.
+ALTER TABLE test_gist ADD COLUMN i1 int DEFAULT NULL;
+ALTER TABLE test_gist ADD COLUMN i2 int DEFAULT 1;
+CREATE INDEX test_gist_idx_inc_1  ON test_gist
+  USING gist (p, point(i1, i1)) INCLUDE (p, i1);
+CREATE INDEX test_gist_idx_inc_12 ON test_gist
+  USING gist (p, point(i1, i2)) INCLUDE (t, i1, i2);
+CREATE INDEX test_gist_idx_inc_2  ON test_gist
+  USING gist (p, point(i2, i2)) INCLUDE (p, t, i2);
+SELECT keys AS keys_nonleaf_1
+  FROM gist_page_items(get_raw_page('test_gist_idx_inc_1', 0), 'test_gist_idx_inc_1')
+  WHERE itemoffset = 1;
+SELECT keys AS keys_leaf_1
+  FROM gist_page_items(get_raw_page('test_gist_idx_inc_1', 1), 'test_gist_idx_inc_1')
+  WHERE itemoffset = 1;
+SELECT keys AS keys_nonleaf_12
+  FROM gist_page_items(get_raw_page('test_gist_idx_inc_12', 0), 'test_gist_idx_inc_12')
+  WHERE itemoffset = 1;
+SELECT keys AS keys_leaf_12
+  FROM gist_page_items(get_raw_page('test_gist_idx_inc_12', 1), 'test_gist_idx_inc_12')
+  WHERE itemoffset = 1;
+SELECT keys AS keys_nonleaf_2
+  FROM gist_page_items(get_raw_page('test_gist_idx_inc_2', 0), 'test_gist_idx_inc_2')
+  WHERE itemoffset = 1;
+SELECT keys AS keys_leaf_2
+  FROM gist_page_items(get_raw_page('test_gist_idx_inc_2', 1), 'test_gist_idx_inc_2')
+  WHERE itemoffset = 1;
+
 DROP TABLE test_gist;
-- 
2.40.1