Skip to content

Commit e7bff46

Browse files
michaelpqmichaelpq
committed
pageinspect: Fix gist_page_items() with included columns
Non-leaf pages of GiST indexes contain key attributes, leaf pages contain both key and non-key attributes, and gist_page_items() ignored the handling of non-key attributes. This caused a few problems when using gist_page_items() on a GiST index with INCLUDE: - On a non-leaf page, the function would crash. - On a leaf page, the function would work, but miss to display all the values for included attributes. This commit fixes gist_page_items() to handle such cases in a more appropriate way, and now displays the values of key and non-key attributes for each item separately in a style consistent with what ruleutils.c would generate for the attribute list, depending on the page type dealt with. In a way similar to how a record is displayed, values would be double-quoted for key or non-key attributes if required. ruleutils.c did not provide a routine able to control if non-key attributes should be displayed, so an extended() routine for index definitions is added to work around the leaf and non-leaf page differences. While on it, this commit fixes a third problem related to the amount of data reported for key attributes. The code originally relied on BuildIndexValueDescription() (used for error reports on constraints) that would not print all the data stored in the index but the index opclass's input type, so this limited the amount of information available. This switch makes gist_page_items() much cheaper as there is no need to run ACL checks for each item printed, which is not an issue anyway as superuser rights are required to execute the functions of pageinspect. Opclasses whose data cannot be displayed can rely on gist_page_items_bytea(). The documentation of this function was slightly incorrect for the output results generated on HEAD and v15, so adjust it on these branches. Author: Alexander Lakhin, Michael Paquier Discussion: https://postgr.es/m/17884-cb8c326522977acb@postgresql.org Backpatch-through: 14
1 parent 613a7ec commit e7bff46

File tree

6 files changed

+178
-31
lines changed

6 files changed

+178
-31
lines changed

‎contrib/pageinspect/expected/gist.out

Lines changed: 38 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -31,24 +31,24 @@ SELECT * FROM gist_page_opaque_info(get_raw_page('test_gist_idx', 2));
3131

3232
COMMIT;
3333
SELECT * FROM gist_page_items(get_raw_page('test_gist_idx', 0), 'test_gist_idx');
34-
itemoffset | ctid | itemlen | dead | keys
35-
------------+-----------+---------+------+-------------------
36-
1 | (1,65535) | 40 | f | (p)=((185,185))
37-
2 | (2,65535) | 40 | f | (p)=((370,370))
38-
3 | (3,65535) | 40 | f | (p)=((555,555))
39-
4 | (4,65535) | 40 | f | (p)=((740,740))
40-
5 | (5,65535) | 40 | f | (p)=((870,870))
41-
6 | (6,65535) | 40 | f | (p)=((1000,1000))
34+
itemoffset | ctid | itemlen | dead | keys
35+
------------+-----------+---------+------+-------------------------------
36+
1 | (1,65535) | 40 | f | (p)=("(185,185),(1,1)")
37+
2 | (2,65535) | 40 | f | (p)=("(370,370),(186,186)")
38+
3 | (3,65535) | 40 | f | (p)=("(555,555),(371,371)")
39+
4 | (4,65535) | 40 | f | (p)=("(740,740),(556,556)")
40+
5 | (5,65535) | 40 | f | (p)=("(870,870),(741,741)")
41+
6 | (6,65535) | 40 | f | (p)=("(1000,1000),(871,871)")
4242
(6 rows)
4343

4444
SELECT * FROM gist_page_items(get_raw_page('test_gist_idx', 1), 'test_gist_idx') LIMIT 5;
45-
itemoffset | ctid | itemlen | dead | keys
46-
------------+-------+---------+------+-------------
47-
1 | (0,1) | 40 | f | (p)=((1,1))
48-
2 | (0,2) | 40 | f | (p)=((2,2))
49-
3 | (0,3) | 40 | f | (p)=((3,3))
50-
4 | (0,4) | 40 | f | (p)=((4,4))
51-
5 | (0,5) | 40 | f | (p)=((5,5))
45+
itemoffset | ctid | itemlen | dead | keys
46+
------------+-------+---------+------+---------------------
47+
1 | (0,1) | 40 | f | (p)=("(1,1),(1,1)")
48+
2 | (0,2) | 40 | f | (p)=("(2,2),(2,2)")
49+
3 | (0,3) | 40 | f | (p)=("(3,3),(3,3)")
50+
4 | (0,4) | 40 | f | (p)=("(4,4),(4,4)")
51+
5 | (0,5) | 40 | f | (p)=("(5,5),(5,5)")
5252
(5 rows)
5353

5454
-- gist_page_items_bytea prints the raw key data as a bytea. The output of that is
@@ -107,4 +107,27 @@ SELECT gist_page_opaque_info(decode(repeat('00', :block_size), 'hex'));
107107

108108
(1 row)
109109

110+
-- Test gist_page_items with included columns.
111+
-- Non-leaf pages contain only the key attributes, and leaf pages contain
112+
-- the included attributes.
113+
ALTER TABLE test_gist ADD COLUMN i int DEFAULT NULL;
114+
CREATE INDEX test_gist_idx_inc ON test_gist
115+
USING gist (p) INCLUDE (t, i);
116+
-- Mask the value of the key attribute to avoid alignment issues.
117+
SELECT regexp_replace(keys, '\(p\)=\("(.*?)"\)', '(p)=("<val>")') AS keys_nonleaf_1
118+
FROM gist_page_items(get_raw_page('test_gist_idx_inc', 0), 'test_gist_idx_inc')
119+
WHERE itemoffset = 1;
120+
keys_nonleaf_1
121+
----------------
122+
(p)=("<val>")
123+
(1 row)
124+
125+
SELECT keys AS keys_leaf_1
126+
FROM gist_page_items(get_raw_page('test_gist_idx_inc', 1), 'test_gist_idx_inc')
127+
WHERE itemoffset = 1;
128+
keys_leaf_1
129+
------------------------------------------------------
130+
(p) INCLUDE (t, i)=("(1,1),(1,1)") INCLUDE (1, null)
131+
(1 row)
132+
110133
DROP TABLE test_gist;

‎contrib/pageinspect/gistfuncs.c

Lines changed: 96 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -21,8 +21,10 @@
2121
#include "storage/itemptr.h"
2222
#include "utils/array.h"
2323
#include "utils/builtins.h"
24-
#include "utils/rel.h"
2524
#include "utils/pg_lsn.h"
25+
#include "utils/lsyscache.h"
26+
#include "utils/rel.h"
27+
#include "utils/ruleutils.h"
2628
#include "utils/varlena.h"
2729

2830
PG_FUNCTION_INFO_V1(gist_page_opaque_info);
@@ -198,9 +200,13 @@ gist_page_items(PG_FUNCTION_ARGS)
198200
Oid indexRelid = PG_GETARG_OID(1);
199201
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
200202
Relation indexRel;
203+
TupleDesc tupdesc;
201204
Page page;
205+
uint16 flagbits;
206+
bits16 printflags = 0;
202207
OffsetNumber offset;
203208
OffsetNumber maxoff = InvalidOffsetNumber;
209+
char *index_columns;
204210

205211
if (!superuser())
206212
ereport(ERROR,
@@ -226,6 +232,27 @@ gist_page_items(PG_FUNCTION_ARGS)
226232
PG_RETURN_NULL();
227233
}
228234

235+
flagbits = GistPageGetOpaque(page)->flags;
236+
237+
/*
238+
* Included attributes are added when dealing with leaf pages, discarded
239+
* for non-leaf pages as these include only data for key attributes.
240+
*/
241+
printflags |= RULE_INDEXDEF_PRETTY;
242+
if (flagbits & F_LEAF)
243+
{
244+
tupdesc = RelationGetDescr(indexRel);
245+
}
246+
else
247+
{
248+
tupdesc = CreateTupleDescCopy(RelationGetDescr(indexRel));
249+
tupdesc->natts = IndexRelationGetNumberOfKeyAttributes(indexRel);
250+
printflags |= RULE_INDEXDEF_KEYS_ONLY;
251+
}
252+
253+
index_columns = pg_get_indexdef_columns_extended(indexRelid,
254+
printflags);
255+
229256
/* Avoid bogus PageGetMaxOffsetNumber() call with deleted pages */
230257
if (GistPageIsDeleted(page))
231258
elog(NOTICE, "page is deleted");
@@ -242,7 +269,8 @@ gist_page_items(PG_FUNCTION_ARGS)
242269
IndexTuple itup;
243270
Datum itup_values[INDEX_MAX_KEYS];
244271
bool itup_isnull[INDEX_MAX_KEYS];
245-
char *key_desc;
272+
StringInfoData buf;
273+
int i;
246274

247275
id = PageGetItemId(page, offset);
248276

@@ -251,7 +279,7 @@ gist_page_items(PG_FUNCTION_ARGS)
251279

252280
itup = (IndexTuple) PageGetItem(page, id);
253281

254-
index_deform_tuple(itup, RelationGetDescr(indexRel),
282+
index_deform_tuple(itup, tupdesc,
255283
itup_values, itup_isnull);
256284

257285
memset(nulls, 0, sizeof(nulls));
@@ -261,9 +289,71 @@ gist_page_items(PG_FUNCTION_ARGS)
261289
values[2] = Int32GetDatum((int) IndexTupleSize(itup));
262290
values[3] = BoolGetDatum(ItemIdIsDead(id));
263291

264-
key_desc = BuildIndexValueDescription(indexRel, itup_values, itup_isnull);
265-
if (key_desc)
266-
values[4] = CStringGetTextDatum(key_desc);
292+
if (index_columns)
293+
{
294+
initStringInfo(&buf);
295+
appendStringInfo(&buf, "(%s)=(", index_columns);
296+
297+
/* Most of this is copied from record_out(). */
298+
for (i = 0; i < tupdesc->natts; i++)
299+
{
300+
char *value;
301+
char *tmp;
302+
bool nq = false;
303+
304+
if (itup_isnull[i])
305+
value = "null";
306+
else
307+
{
308+
Oid foutoid;
309+
bool typisvarlena;
310+
Oid typoid;
311+
312+
typoid = tupdesc->attrs[i].atttypid;
313+
getTypeOutputInfo(typoid, &foutoid, &typisvarlena);
314+
value = OidOutputFunctionCall(foutoid, itup_values[i]);
315+
}
316+
317+
if (i == IndexRelationGetNumberOfKeyAttributes(indexRel))
318+
appendStringInfoString(&buf, ") INCLUDE (");
319+
else if (i > 0)
320+
appendStringInfoString(&buf, ", ");
321+
322+
/* Check whether we need double quotes for this value */
323+
nq = (value[0] == '\0'); /* force quotes for empty string */
324+
for (tmp = value; *tmp; tmp++)
325+
{
326+
char ch = *tmp;
327+
328+
if (ch == '"' || ch == '\\' ||
329+
ch == '(' || ch == ')' || ch == ',' ||
330+
isspace((unsigned char) ch))
331+
{
332+
nq = true;
333+
break;
334+
}
335+
}
336+
337+
/* And emit the string */
338+
if (nq)
339+
appendStringInfoCharMacro(&buf, '"');
340+
for (tmp = value; *tmp; tmp++)
341+
{
342+
char ch = *tmp;
343+
344+
if (ch == '"' || ch == '\\')
345+
appendStringInfoCharMacro(&buf, ch);
346+
appendStringInfoCharMacro(&buf, ch);
347+
}
348+
if (nq)
349+
appendStringInfoCharMacro(&buf, '"');
350+
}
351+
352+
appendStringInfoChar(&buf, ')');
353+
354+
values[4] = CStringGetTextDatum(buf.data);
355+
nulls[4] = false;
356+
}
267357
else
268358
{
269359
values[4] = (Datum) 0;

‎contrib/pageinspect/sql/gist.sql

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -52,4 +52,18 @@ SELECT gist_page_items_bytea(decode(repeat('00', :block_size), 'hex'));
5252
SELECT gist_page_items(decode(repeat('00', :block_size), 'hex'), 'test_gist_idx'::regclass);
5353
SELECT gist_page_opaque_info(decode(repeat('00', :block_size), 'hex'));
5454

55+
-- Test gist_page_items with included columns.
56+
-- Non-leaf pages contain only the key attributes, and leaf pages contain
57+
-- the included attributes.
58+
ALTER TABLE test_gist ADD COLUMN i int DEFAULT NULL;
59+
CREATE INDEX test_gist_idx_inc ON test_gist
60+
USING gist (p) INCLUDE (t, i);
61+
-- Mask the value of the key attribute to avoid alignment issues.
62+
SELECT regexp_replace(keys, '\(p\)=\("(.*?)"\)', '(p)=("<val>")') AS keys_nonleaf_1
63+
FROM gist_page_items(get_raw_page('test_gist_idx_inc', 0), 'test_gist_idx_inc')
64+
WHERE itemoffset = 1;
65+
SELECT keys AS keys_leaf_1
66+
FROM gist_page_items(get_raw_page('test_gist_idx_inc', 1), 'test_gist_idx_inc')
67+
WHERE itemoffset = 1;
68+
5569
DROP TABLE test_gist;

‎doc/src/sgml/pageinspect.sgml

Lines changed: 9 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -764,16 +764,15 @@ test=# SELECT * FROM gist_page_opaque_info(get_raw_page('test_gist_idx', 2));
764764
the data stored in a page of a <acronym>GiST</acronym> index. For example:
765765
<screen>
766766
test=# SELECT * FROM gist_page_items(get_raw_page('test_gist_idx', 0), 'test_gist_idx');
767-
itemoffset | ctid | itemlen | dead | keys
768-
------------+-----------+---------+------+-------------------
769-
1 | (1,65535) | 40 | f | (p)=((166,166))
770-
2 | (2,65535) | 40 | f | (p)=((332,332))
771-
3 | (3,65535) | 40 | f | (p)=((498,498))
772-
4 | (4,65535) | 40 | f | (p)=((664,664))
773-
5 | (5,65535) | 40 | f | (p)=((830,830))
774-
6 | (6,65535) | 40 | f | (p)=((996,996))
775-
7 | (7,65535) | 40 | f | (p)=((1000,1000))
776-
(7 rows)
767+
itemoffset | ctid | itemlen | dead | keys
768+
------------+-----------+---------+------+-------------------------------
769+
1 | (1,65535) | 40 | f | (p)=("(185,185),(1,1)")
770+
2 | (2,65535) | 40 | f | (p)=("(370,370),(186,186)")
771+
3 | (3,65535) | 40 | f | (p)=("(555,555),(371,371)")
772+
4 | (4,65535) | 40 | f | (p)=("(740,740),(556,556)")
773+
5 | (5,65535) | 40 | f | (p)=("(870,870),(741,741)")
774+
6 | (6,65535) | 40 | f | (p)=("(1000,1000),(871,871)")
775+
(6 rows)
777776
</screen>
778777
</para>
779778
</listitem>

‎src/backend/utils/adt/ruleutils.c

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1215,6 +1215,22 @@ pg_get_indexdef_columns(Oid indexrelid, bool pretty)
12151215
prettyFlags, false);
12161216
}
12171217

1218+
/* Internal version, extensible with flags to control its behavior */
1219+
char *
1220+
pg_get_indexdef_columns_extended(Oid indexrelid, bits16 flags)
1221+
{
1222+
bool pretty = ((flags & RULE_INDEXDEF_PRETTY) != 0);
1223+
bool keys_only = ((flags & RULE_INDEXDEF_KEYS_ONLY) != 0);
1224+
int prettyFlags;
1225+
1226+
prettyFlags = GET_PRETTY_FLAGS(pretty);
1227+
1228+
return pg_get_indexdef_worker(indexrelid, 0, NULL,
1229+
true, keys_only,
1230+
false, false,
1231+
prettyFlags, false);
1232+
}
1233+
12181234
/*
12191235
* Internal workhorse to decompile an index definition.
12201236
*

‎src/include/utils/ruleutils.h

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,9 +20,14 @@
2020
struct Plan; /* avoid including plannodes.h here */
2121
struct PlannedStmt;
2222

23+
/* Flags for pg_get_indexdef_columns_extended() */
24+
#define RULE_INDEXDEF_PRETTY 0x01
25+
#define RULE_INDEXDEF_KEYS_ONLY 0x02 /* ignore included attributes */
2326

2427
extern char *pg_get_indexdef_string(Oid indexrelid);
2528
extern char *pg_get_indexdef_columns(Oid indexrelid, bool pretty);
29+
extern char *pg_get_indexdef_columns_extended(Oid indexrelid,
30+
bits16 flags);
2631
extern char *pg_get_querydef(Query *query, bool pretty);
2732

2833
extern char *pg_get_partkeydef_columns(Oid relid, bool pretty);

0 commit comments

Comments
 (0)