Skip to content

Commit e725802

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 40d465c commit e725802

File tree

6 files changed

+180
-32
lines changed

6 files changed

+180
-32
lines changed

‎contrib/pageinspect/expected/gist.out

Lines changed: 39 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -31,25 +31,25 @@ 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)=((166,166))
37-
2 | (2,65535) | 40 | f | (p)=((332,332))
38-
3 | (3,65535) | 40 | f | (p)=((498,498))
39-
4 | (4,65535) | 40 | f | (p)=((664,664))
40-
5 | (5,65535) | 40 | f | (p)=((830,830))
41-
6 | (6,65535) | 40 | f | (p)=((996,996))
42-
7 | (7,65535) | 40 | f | (p)=((1000,1000))
34+
itemoffset | ctid | itemlen | dead | keys
35+
------------+-----------+---------+------+-------------------------------
36+
1 | (1,65535) | 40 | f | (p)=("(166,166),(1,1)")
37+
2 | (2,65535) | 40 | f | (p)=("(332,332),(167,167)")
38+
3 | (3,65535) | 40 | f | (p)=("(498,498),(333,333)")
39+
4 | (4,65535) | 40 | f | (p)=("(664,664),(499,499)")
40+
5 | (5,65535) | 40 | f | (p)=("(830,830),(665,665)")
41+
6 | (6,65535) | 40 | f | (p)=("(996,996),(831,831)")
42+
7 | (7,65535) | 40 | f | (p)=("(1000,1000),(997,997)")
4343
(7 rows)
4444

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

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

110110
(1 row)
111111

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

‎contrib/pageinspect/gistfuncs.c

Lines changed: 97 additions & 7 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);
@@ -233,8 +235,11 @@ gist_page_items(PG_FUNCTION_ARGS)
233235
Tuplestorestate *tupstore;
234236
MemoryContext oldcontext;
235237
Page page;
238+
uint16 flagbits;
239+
bits16 printflags = 0;
236240
OffsetNumber offset;
237241
OffsetNumber maxoff = InvalidOffsetNumber;
242+
char *index_columns;
238243

239244
if (!superuser())
240245
ereport(ERROR,
@@ -282,6 +287,27 @@ gist_page_items(PG_FUNCTION_ARGS)
282287
PG_RETURN_NULL();
283288
}
284289

290+
flagbits = GistPageGetOpaque(page)->flags;
291+
292+
/*
293+
* Included attributes are added when dealing with leaf pages, discarded
294+
* for non-leaf pages as these include only data for key attributes.
295+
*/
296+
printflags |= RULE_INDEXDEF_PRETTY;
297+
if (flagbits & F_LEAF)
298+
{
299+
tupdesc = RelationGetDescr(indexRel);
300+
}
301+
else
302+
{
303+
tupdesc = CreateTupleDescCopy(RelationGetDescr(indexRel));
304+
tupdesc->natts = IndexRelationGetNumberOfKeyAttributes(indexRel);
305+
printflags |= RULE_INDEXDEF_KEYS_ONLY;
306+
}
307+
308+
index_columns = pg_get_indexdef_columns_extended(indexRelid,
309+
printflags);
310+
285311
/* Avoid bogus PageGetMaxOffsetNumber() call with deleted pages */
286312
if (GistPageIsDeleted(page))
287313
elog(NOTICE, "page is deleted");
@@ -298,7 +324,8 @@ gist_page_items(PG_FUNCTION_ARGS)
298324
IndexTuple itup;
299325
Datum itup_values[INDEX_MAX_KEYS];
300326
bool itup_isnull[INDEX_MAX_KEYS];
301-
char *key_desc;
327+
StringInfoData buf;
328+
int i;
302329

303330
id = PageGetItemId(page, offset);
304331

@@ -307,7 +334,7 @@ gist_page_items(PG_FUNCTION_ARGS)
307334

308335
itup = (IndexTuple) PageGetItem(page, id);
309336

310-
index_deform_tuple(itup, RelationGetDescr(indexRel),
337+
index_deform_tuple(itup, tupdesc,
311338
itup_values, itup_isnull);
312339

313340
memset(nulls, 0, sizeof(nulls));
@@ -317,16 +344,79 @@ gist_page_items(PG_FUNCTION_ARGS)
317344
values[2] = Int32GetDatum((int) IndexTupleSize(itup));
318345
values[3] = BoolGetDatum(ItemIdIsDead(id));
319346

320-
key_desc = BuildIndexValueDescription(indexRel, itup_values, itup_isnull);
321-
if (key_desc)
322-
values[4] = CStringGetTextDatum(key_desc);
347+
if (index_columns)
348+
{
349+
initStringInfo(&buf);
350+
appendStringInfo(&buf, "(%s)=(", index_columns);
351+
352+
/* Most of this is copied from record_out(). */
353+
for (i = 0; i < tupdesc->natts; i++)
354+
{
355+
char *value;
356+
char *tmp;
357+
bool nq = false;
358+
359+
if (itup_isnull[i])
360+
value = "null";
361+
else
362+
{
363+
Oid foutoid;
364+
bool typisvarlena;
365+
Oid typoid;
366+
367+
typoid = tupdesc->attrs[i].atttypid;
368+
getTypeOutputInfo(typoid, &foutoid, &typisvarlena);
369+
value = OidOutputFunctionCall(foutoid, itup_values[i]);
370+
}
371+
372+
if (i == IndexRelationGetNumberOfKeyAttributes(indexRel))
373+
appendStringInfoString(&buf, ") INCLUDE (");
374+
else if (i > 0)
375+
appendStringInfoString(&buf, ", ");
376+
377+
/* Check whether we need double quotes for this value */
378+
nq = (value[0] == '\0'); /* force quotes for empty string */
379+
for (tmp = value; *tmp; tmp++)
380+
{
381+
char ch = *tmp;
382+
383+
if (ch == '"' || ch == '\\' ||
384+
ch == '(' || ch == ')' || ch == ',' ||
385+
isspace((unsigned char) ch))
386+
{
387+
nq = true;
388+
break;
389+
}
390+
}
391+
392+
/* And emit the string */
393+
if (nq)
394+
appendStringInfoCharMacro(&buf, '"');
395+
for (tmp = value; *tmp; tmp++)
396+
{
397+
char ch = *tmp;
398+
399+
if (ch == '"' || ch == '\\')
400+
appendStringInfoCharMacro(&buf, ch);
401+
appendStringInfoCharMacro(&buf, ch);
402+
}
403+
if (nq)
404+
appendStringInfoCharMacro(&buf, '"');
405+
}
406+
407+
appendStringInfoChar(&buf, ')');
408+
409+
values[4] = CStringGetTextDatum(buf.data);
410+
nulls[4] = false;
411+
}
323412
else
324413
{
325414
values[4] = (Datum) 0;
326415
nulls[4] = true;
327416
}
328417

329-
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
418+
tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
419+
values, nulls);
330420
}
331421

332422
relation_close(indexRel, AccessShareLock);

‎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 & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -714,15 +714,15 @@ test=# SELECT * FROM gist_page_opaque_info(get_raw_page('test_gist_idx', 2));
714714
the data stored in a page of a <acronym>GiST</acronym> index. For example:
715715
<screen>
716716
test=# SELECT * FROM gist_page_items(get_raw_page('test_gist_idx', 0), 'test_gist_idx');
717-
itemoffset | ctid | itemlen | dead | keys
718-
------------+-----------+---------+------+-------------------
719-
1 | (1,65535) | 40 | f | (p)=((166,166))
720-
2 | (2,65535) | 40 | f | (p)=((332,332))
721-
3 | (3,65535) | 40 | f | (p)=((498,498))
722-
4 | (4,65535) | 40 | f | (p)=((664,664))
723-
5 | (5,65535) | 40 | f | (p)=((830,830))
724-
6 | (6,65535) | 40 | f | (p)=((996,996))
725-
7 | (7,65535) | 40 | f | (p)=((1000,1000))
717+
itemoffset | ctid | itemlen | dead | keys
718+
------------+-----------+---------+------+-------------------------------
719+
1 | (1,65535) | 40 | f | (p)=("(166,166),(1,1)")
720+
2 | (2,65535) | 40 | f | (p)=("(332,332),(167,167)")
721+
3 | (3,65535) | 40 | f | (p)=("(498,498),(333,333)")
722+
4 | (4,65535) | 40 | f | (p)=("(664,664),(499,499)")
723+
5 | (5,65535) | 40 | f | (p)=("(830,830),(665,665)")
724+
6 | (6,65535) | 40 | f | (p)=("(996,996),(831,831)")
725+
7 | (7,65535) | 40 | f | (p)=("(1000,1000),(997,997)")
726726
(7 rows)
727727
</screen>
728728
</para>

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

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1193,6 +1193,22 @@ pg_get_indexdef_columns(Oid indexrelid, bool pretty)
11931193
prettyFlags, false);
11941194
}
11951195

1196+
/* Internal version, extensible with flags to control its behavior */
1197+
char *
1198+
pg_get_indexdef_columns_extended(Oid indexrelid, bits16 flags)
1199+
{
1200+
bool pretty = ((flags & RULE_INDEXDEF_PRETTY) != 0);
1201+
bool keys_only = ((flags & RULE_INDEXDEF_KEYS_ONLY) != 0);
1202+
int prettyFlags;
1203+
1204+
prettyFlags = pretty ? (PRETTYFLAG_PAREN | PRETTYFLAG_INDENT | PRETTYFLAG_SCHEMA) : PRETTYFLAG_INDENT;
1205+
1206+
return pg_get_indexdef_worker(indexrelid, 0, NULL,
1207+
true, keys_only,
1208+
false, false,
1209+
prettyFlags, false);
1210+
}
1211+
11961212
/*
11971213
* Internal workhorse to decompile an index definition.
11981214
*

‎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

2732
extern char *pg_get_partkeydef_columns(Oid relid, bool pretty);
2833
extern char *pg_get_partconstrdef_string(Oid partitionId, char *aliasname);

0 commit comments

Comments
 (0)