Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite725802

Browse files
committed
pageinspect: Fix gist_page_items() with included columns
Non-leaf pages of GiST indexes contain key attributes, leaf pagescontain both key and non-key attributes, and gist_page_items() ignoredthe handling of non-key attributes. This caused a few problems whenusing 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 thevalues for included attributes.This commit fixes gist_page_items() to handle such cases in a moreappropriate way, and now displays the values of key and non-keyattributes for each item separately in a style consistent with whatruleutils.c would generate for the attribute list, depending on the pagetype dealt with. In a way similar to how a record is displayed, valueswould be double-quoted for key or non-key attributes if required.ruleutils.c did not provide a routine able to control if non-keyattributes should be displayed, so an extended() routine for indexdefinitions is added to work around the leaf and non-leaf pagedifferences.While on it, this commit fixes a third problem related to the amount ofdata reported for key attributes. The code originally relied onBuildIndexValueDescription() (used for error reports on constraints)that would not print all the data stored in the index but the indexopclass's input type, so this limited the amount of informationavailable. This switch makes gist_page_items() much cheaper as there isno need to run ACL checks for each item printed, which is not an issueanyway as superuser rights are required to execute the functions ofpageinspect. Opclasses whose data cannot be displayed can rely ongist_page_items_bytea().The documentation of this function was slightly incorrect for theoutput results generated on HEAD and v15, so adjust it on thesebranches.Author: Alexander Lakhin, Michael PaquierDiscussion:https://postgr.es/m/17884-cb8c326522977acb@postgresql.orgBackpatch-through: 14
1 parent40d465c commite725802

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
MemoryContextoldcontext;
235237
Pagepage;
238+
uint16flagbits;
239+
bits16printflags=0;
236240
OffsetNumberoffset;
237241
OffsetNumbermaxoff=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
IndexTupleitup;
299325
Datumitup_values[INDEX_MAX_KEYS];
300326
boolitup_isnull[INDEX_MAX_KEYS];
301-
char*key_desc;
327+
StringInfoDatabuf;
328+
inti;
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+
boolnq= false;
358+
359+
if (itup_isnull[i])
360+
value="null";
361+
else
362+
{
363+
Oidfoutoid;
364+
booltypisvarlena;
365+
Oidtypoid;
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+
elseif (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+
charch=*tmp;
382+
383+
if (ch=='"'||ch=='\\'||
384+
ch=='('||ch==')'||ch==','||
385+
isspace((unsignedchar)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+
charch=*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+
ALTERTABLE test_gist ADD COLUMN iint DEFAULTNULL;
59+
CREATEINDEXtest_gist_idx_incON 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 keysAS 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
DROPTABLE 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(Oidindexrelid,bits16flags)
1199+
{
1200+
boolpretty= ((flags&RULE_INDEXDEF_PRETTY)!=0);
1201+
boolkeys_only= ((flags&RULE_INDEXDEF_KEYS_ONLY)!=0);
1202+
intprettyFlags;
1203+
1204+
prettyFlags=pretty ? (PRETTYFLAG_PAREN |PRETTYFLAG_INDENT |PRETTYFLAG_SCHEMA) :PRETTYFLAG_INDENT;
1205+
1206+
returnpg_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
structPlan;/* avoid including plannodes.h here */
2121
structPlannedStmt;
2222

23+
/* Flags for pg_get_indexdef_columns_extended() */
24+
#defineRULE_INDEXDEF_PRETTY0x01
25+
#defineRULE_INDEXDEF_KEYS_ONLY0x02/* ignore included attributes */
2326

2427
externchar*pg_get_indexdef_string(Oidindexrelid);
2528
externchar*pg_get_indexdef_columns(Oidindexrelid,boolpretty);
29+
externchar*pg_get_indexdef_columns_extended(Oidindexrelid,
30+
bits16flags);
2631

2732
externchar*pg_get_partkeydef_columns(Oidrelid,boolpretty);
2833
externchar*pg_get_partconstrdef_string(OidpartitionId,char*aliasname);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp