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

Commit2dd7782

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 parente187693 commit2dd7782

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);
@@ -202,9 +204,13 @@ gist_page_items(PG_FUNCTION_ARGS)
202204
OidindexRelid=PG_GETARG_OID(1);
203205
ReturnSetInfo*rsinfo= (ReturnSetInfo*)fcinfo->resultinfo;
204206
RelationindexRel;
207+
TupleDesctupdesc;
205208
Pagepage;
209+
uint16flagbits;
210+
bits16printflags=0;
206211
OffsetNumberoffset;
207212
OffsetNumbermaxoff=InvalidOffsetNumber;
213+
char*index_columns;
208214

209215
if (!superuser())
210216
ereport(ERROR,
@@ -230,6 +236,27 @@ gist_page_items(PG_FUNCTION_ARGS)
230236
PG_RETURN_NULL();
231237
}
232238

239+
flagbits=GistPageGetOpaque(page)->flags;
240+
241+
/*
242+
* Included attributes are added when dealing with leaf pages, discarded
243+
* for non-leaf pages as these include only data for key attributes.
244+
*/
245+
printflags |=RULE_INDEXDEF_PRETTY;
246+
if (flagbits&F_LEAF)
247+
{
248+
tupdesc=RelationGetDescr(indexRel);
249+
}
250+
else
251+
{
252+
tupdesc=CreateTupleDescCopy(RelationGetDescr(indexRel));
253+
tupdesc->natts=IndexRelationGetNumberOfKeyAttributes(indexRel);
254+
printflags |=RULE_INDEXDEF_KEYS_ONLY;
255+
}
256+
257+
index_columns=pg_get_indexdef_columns_extended(indexRelid,
258+
printflags);
259+
233260
/* Avoid bogus PageGetMaxOffsetNumber() call with deleted pages */
234261
if (GistPageIsDeleted(page))
235262
elog(NOTICE,"page is deleted");
@@ -246,7 +273,8 @@ gist_page_items(PG_FUNCTION_ARGS)
246273
IndexTupleitup;
247274
Datumitup_values[INDEX_MAX_KEYS];
248275
boolitup_isnull[INDEX_MAX_KEYS];
249-
char*key_desc;
276+
StringInfoDatabuf;
277+
inti;
250278

251279
id=PageGetItemId(page,offset);
252280

@@ -255,7 +283,7 @@ gist_page_items(PG_FUNCTION_ARGS)
255283

256284
itup= (IndexTuple)PageGetItem(page,id);
257285

258-
index_deform_tuple(itup,RelationGetDescr(indexRel),
286+
index_deform_tuple(itup,tupdesc,
259287
itup_values,itup_isnull);
260288

261289
memset(nulls,0,sizeof(nulls));
@@ -265,9 +293,71 @@ gist_page_items(PG_FUNCTION_ARGS)
265293
values[2]=Int32GetDatum((int)IndexTupleSize(itup));
266294
values[3]=BoolGetDatum(ItemIdIsDead(id));
267295

268-
key_desc=BuildIndexValueDescription(indexRel,itup_values,itup_isnull);
269-
if (key_desc)
270-
values[4]=CStringGetTextDatum(key_desc);
296+
if (index_columns)
297+
{
298+
initStringInfo(&buf);
299+
appendStringInfo(&buf,"(%s)=(",index_columns);
300+
301+
/* Most of this is copied from record_out(). */
302+
for (i=0;i<tupdesc->natts;i++)
303+
{
304+
char*value;
305+
char*tmp;
306+
boolnq= false;
307+
308+
if (itup_isnull[i])
309+
value="null";
310+
else
311+
{
312+
Oidfoutoid;
313+
booltypisvarlena;
314+
Oidtypoid;
315+
316+
typoid=tupdesc->attrs[i].atttypid;
317+
getTypeOutputInfo(typoid,&foutoid,&typisvarlena);
318+
value=OidOutputFunctionCall(foutoid,itup_values[i]);
319+
}
320+
321+
if (i==IndexRelationGetNumberOfKeyAttributes(indexRel))
322+
appendStringInfoString(&buf,") INCLUDE (");
323+
elseif (i>0)
324+
appendStringInfoString(&buf,", ");
325+
326+
/* Check whether we need double quotes for this value */
327+
nq= (value[0]=='\0');/* force quotes for empty string */
328+
for (tmp=value;*tmp;tmp++)
329+
{
330+
charch=*tmp;
331+
332+
if (ch=='"'||ch=='\\'||
333+
ch=='('||ch==')'||ch==','||
334+
isspace((unsignedchar)ch))
335+
{
336+
nq= true;
337+
break;
338+
}
339+
}
340+
341+
/* And emit the string */
342+
if (nq)
343+
appendStringInfoCharMacro(&buf,'"');
344+
for (tmp=value;*tmp;tmp++)
345+
{
346+
charch=*tmp;
347+
348+
if (ch=='"'||ch=='\\')
349+
appendStringInfoCharMacro(&buf,ch);
350+
appendStringInfoCharMacro(&buf,ch);
351+
}
352+
if (nq)
353+
appendStringInfoCharMacro(&buf,'"');
354+
}
355+
356+
appendStringInfoChar(&buf,')');
357+
358+
values[4]=CStringGetTextDatum(buf.data);
359+
nulls[4]= false;
360+
}
271361
else
272362
{
273363
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
@@ -716,16 +716,15 @@ test=# SELECT * FROM gist_page_opaque_info(get_raw_page('test_gist_idx', 2));
716716
the data stored in a page of a <acronym>GiST</acronym> index. For example:
717717
<screen>
718718
test=# SELECT * FROM gist_page_items(get_raw_page('test_gist_idx', 0), 'test_gist_idx');
719-
itemoffset | ctid | itemlen | dead | keys
720-
------------+-----------+---------+------+-------------------
721-
1 | (1,65535) | 40 | f | (p)=((166,166))
722-
2 | (2,65535) | 40 | f | (p)=((332,332))
723-
3 | (3,65535) | 40 | f | (p)=((498,498))
724-
4 | (4,65535) | 40 | f | (p)=((664,664))
725-
5 | (5,65535) | 40 | f | (p)=((830,830))
726-
6 | (6,65535) | 40 | f | (p)=((996,996))
727-
7 | (7,65535) | 40 | f | (p)=((1000,1000))
728-
(7 rows)
719+
itemoffset | ctid | itemlen | dead | keys
720+
------------+-----------+---------+------+-------------------------------
721+
1 | (1,65535) | 40 | f | (p)=("(185,185),(1,1)")
722+
2 | (2,65535) | 40 | f | (p)=("(370,370),(186,186)")
723+
3 | (3,65535) | 40 | f | (p)=("(555,555),(371,371)")
724+
4 | (4,65535) | 40 | f | (p)=("(740,740),(556,556)")
725+
5 | (5,65535) | 40 | f | (p)=("(870,870),(741,741)")
726+
6 | (6,65535) | 40 | f | (p)=("(1000,1000),(871,871)")
727+
(6 rows)
729728
</screen>
730729
</para>
731730
</listitem>

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

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1200,6 +1200,22 @@ pg_get_indexdef_columns(Oid indexrelid, bool pretty)
12001200
prettyFlags, false);
12011201
}
12021202

1203+
/* Internal version, extensible with flags to control its behavior */
1204+
char*
1205+
pg_get_indexdef_columns_extended(Oidindexrelid,bits16flags)
1206+
{
1207+
boolpretty= ((flags&RULE_INDEXDEF_PRETTY)!=0);
1208+
boolkeys_only= ((flags&RULE_INDEXDEF_KEYS_ONLY)!=0);
1209+
intprettyFlags;
1210+
1211+
prettyFlags=GET_PRETTY_FLAGS(pretty);
1212+
1213+
returnpg_get_indexdef_worker(indexrelid,0,NULL,
1214+
true,keys_only,
1215+
false, false,
1216+
prettyFlags, false);
1217+
}
1218+
12031219
/*
12041220
* Internal workhorse to decompile an index definition.
12051221
*

‎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