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

Commite7bff46

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 parent613a7ec commite7bff46

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
OidindexRelid=PG_GETARG_OID(1);
199201
ReturnSetInfo*rsinfo= (ReturnSetInfo*)fcinfo->resultinfo;
200202
RelationindexRel;
203+
TupleDesctupdesc;
201204
Pagepage;
205+
uint16flagbits;
206+
bits16printflags=0;
202207
OffsetNumberoffset;
203208
OffsetNumbermaxoff=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
IndexTupleitup;
243270
Datumitup_values[INDEX_MAX_KEYS];
244271
boolitup_isnull[INDEX_MAX_KEYS];
245-
char*key_desc;
272+
StringInfoDatabuf;
273+
inti;
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+
boolnq= false;
303+
304+
if (itup_isnull[i])
305+
value="null";
306+
else
307+
{
308+
Oidfoutoid;
309+
booltypisvarlena;
310+
Oidtypoid;
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+
elseif (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+
charch=*tmp;
327+
328+
if (ch=='"'||ch=='\\'||
329+
ch=='('||ch==')'||ch==','||
330+
isspace((unsignedchar)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+
charch=*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+
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 & 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(Oidindexrelid,bits16flags)
1221+
{
1222+
boolpretty= ((flags&RULE_INDEXDEF_PRETTY)!=0);
1223+
boolkeys_only= ((flags&RULE_INDEXDEF_KEYS_ONLY)!=0);
1224+
intprettyFlags;
1225+
1226+
prettyFlags=GET_PRETTY_FLAGS(pretty);
1227+
1228+
returnpg_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
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
externchar*pg_get_querydef(Query*query,boolpretty);
2732

2833
externchar*pg_get_partkeydef_columns(Oidrelid,boolpretty);

0 commit comments

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