1 /*
2 * contrib/pgstattuple/pgstatindex.c
3 *
4 *
5 * pgstatindex
6 *
7 * Copyright (c) 2006 Satoshi Nagayasu <nagayasus@nttdata.co.jp>
8 *
9 * Permission to use, copy, modify, and distribute this software and
10 * its documentation for any purpose, without fee, and without a
11 * written agreement is hereby granted, provided that the above
12 * copyright notice and this paragraph and the following two
13 * paragraphs appear in all copies.
14 *
15 * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT,
16 * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
17 * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
18 * DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED
19 * OF THE POSSIBILITY OF SUCH DAMAGE.
20 *
21 * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT
22 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
23 * A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS
24 * IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE,
25 * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
26 */
27
28 #include "postgres.h"
29
30 #include "access/gin_private.h"
31 #include "access/heapam.h"
32 #include "access/htup_details.h"
33 #include "access/nbtree.h"
34 #include "catalog/namespace.h"
35 #include "catalog/pg_am.h"
36 #include "funcapi.h"
37 #include "miscadmin.h"
38 #include "storage/bufmgr.h"
39 #include "utils/builtins.h"
40 #include "utils/rel.h"
41
42
43 /*
44 * Because of backward-compatibility issue, we have decided to have
45 * two types of interfaces, with regclass-type input arg and text-type
46 * input arg, for each function.
47 *
48 * Those functions which have text-type input arg will be deprecated
49 * in the future release.
50 */
51 PG_FUNCTION_INFO_V1(pgstatindex);
52 PG_FUNCTION_INFO_V1(pgstatindexbyid);
53 PG_FUNCTION_INFO_V1(pg_relpages);
54 PG_FUNCTION_INFO_V1(pg_relpagesbyid);
55 PG_FUNCTION_INFO_V1(pgstatginindex);
56
57 #define IS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)
58 #define IS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID)
59 #define IS_GIN(r) ((r)->rd_rel->relam == GIN_AM_OID)
60
61 /* ------------------------------------------------
62 * A structure for a whole btree index statistics
63 * used by pgstatindex().
64 * ------------------------------------------------
65 */
66 typedef struct BTIndexStat
67 {
68 uint32 version;
69 uint32 level;
70 BlockNumber root_blkno;
71
72 uint64 internal_pages;
73 uint64 leaf_pages;
74 uint64 empty_pages;
75 uint64 deleted_pages;
76
77 uint64 max_avail;
78 uint64 free_space;
79
80 uint64 fragments;
81 } BTIndexStat;
82
83 /* ------------------------------------------------
84 * A structure for a whole GIN index statistics
85 * used by pgstatginindex().
86 * ------------------------------------------------
87 */
88 typedef struct GinIndexStat
89 {
90 int32 version;
91
92 BlockNumber pending_pages;
93 int64 pending_tuples;
94 } GinIndexStat;
95
96 static Datum pgstatindex_impl(Relation rel, FunctionCallInfo fcinfo);
97
98 /* ------------------------------------------------------
99 * pgstatindex()
100 *
101 * Usage: SELECT * FROM pgstatindex('t1_pkey');
102 * ------------------------------------------------------
103 */
104 Datum
pgstatindex(PG_FUNCTION_ARGS)105 pgstatindex(PG_FUNCTION_ARGS)
106 {
107 text *relname = PG_GETARG_TEXT_P(0);
108 Relation rel;
109 RangeVar *relrv;
110
111 if (!superuser())
112 ereport(ERROR,
113 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
114 (errmsg("must be superuser to use pgstattuple functions"))));
115
116 relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
117 rel = relation_openrv(relrv, AccessShareLock);
118
119 PG_RETURN_DATUM(pgstatindex_impl(rel, fcinfo));
120 }
121
122 Datum
pgstatindexbyid(PG_FUNCTION_ARGS)123 pgstatindexbyid(PG_FUNCTION_ARGS)
124 {
125 Oid relid = PG_GETARG_OID(0);
126 Relation rel;
127
128 if (!superuser())
129 ereport(ERROR,
130 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
131 (errmsg("must be superuser to use pgstattuple functions"))));
132
133 rel = relation_open(relid, AccessShareLock);
134
135 PG_RETURN_DATUM(pgstatindex_impl(rel, fcinfo));
136 }
137
138 static Datum
pgstatindex_impl(Relation rel,FunctionCallInfo fcinfo)139 pgstatindex_impl(Relation rel, FunctionCallInfo fcinfo)
140 {
141 Datum result;
142 BlockNumber nblocks;
143 BlockNumber blkno;
144 BTIndexStat indexStat;
145 BufferAccessStrategy bstrategy = GetAccessStrategy(BAS_BULKREAD);
146
147 if (!IS_INDEX(rel) || !IS_BTREE(rel))
148 elog(ERROR, "relation \"%s\" is not a btree index",
149 RelationGetRelationName(rel));
150
151 /*
152 * Reject attempts to read non-local temporary relations; we would be
153 * likely to get wrong data since we have no visibility into the owning
154 * session's local buffers.
155 */
156 if (RELATION_IS_OTHER_TEMP(rel))
157 ereport(ERROR,
158 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
159 errmsg("cannot access temporary tables of other sessions")));
160
161 /*
162 * Read metapage
163 */
164 {
165 Buffer buffer = ReadBufferExtended(rel, MAIN_FORKNUM, 0, RBM_NORMAL, bstrategy);
166 Page page = BufferGetPage(buffer);
167 BTMetaPageData *metad = BTPageGetMeta(page);
168
169 indexStat.version = metad->btm_version;
170 indexStat.level = metad->btm_level;
171 indexStat.root_blkno = metad->btm_root;
172
173 ReleaseBuffer(buffer);
174 }
175
176 /* -- init counters -- */
177 indexStat.internal_pages = 0;
178 indexStat.leaf_pages = 0;
179 indexStat.empty_pages = 0;
180 indexStat.deleted_pages = 0;
181
182 indexStat.max_avail = 0;
183 indexStat.free_space = 0;
184
185 indexStat.fragments = 0;
186
187 /*
188 * Scan all blocks except the metapage
189 */
190 nblocks = RelationGetNumberOfBlocks(rel);
191
192 for (blkno = 1; blkno < nblocks; blkno++)
193 {
194 Buffer buffer;
195 Page page;
196 BTPageOpaque opaque;
197
198 CHECK_FOR_INTERRUPTS();
199
200 /* Read and lock buffer */
201 buffer = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy);
202 LockBuffer(buffer, BUFFER_LOCK_SHARE);
203
204 page = BufferGetPage(buffer);
205 opaque = (BTPageOpaque) PageGetSpecialPointer(page);
206
207 /* Determine page type, and update totals */
208
209 if (P_ISDELETED(opaque))
210 indexStat.deleted_pages++;
211 else if (P_IGNORE(opaque))
212 indexStat.empty_pages++; /* this is the "half dead" state */
213 else if (P_ISLEAF(opaque))
214 {
215 int max_avail;
216
217 max_avail = BLCKSZ - (BLCKSZ - ((PageHeader) page)->pd_special + SizeOfPageHeaderData);
218 indexStat.max_avail += max_avail;
219 indexStat.free_space += PageGetFreeSpace(page);
220
221 indexStat.leaf_pages++;
222
223 /*
224 * If the next leaf is on an earlier block, it means a
225 * fragmentation.
226 */
227 if (opaque->btpo_next != P_NONE && opaque->btpo_next < blkno)
228 indexStat.fragments++;
229 }
230 else
231 indexStat.internal_pages++;
232
233 /* Unlock and release buffer */
234 LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
235 ReleaseBuffer(buffer);
236 }
237
238 relation_close(rel, AccessShareLock);
239
240 /*----------------------------
241 * Build a result tuple
242 *----------------------------
243 */
244 {
245 TupleDesc tupleDesc;
246 int j;
247 char *values[10];
248 HeapTuple tuple;
249
250 /* Build a tuple descriptor for our result type */
251 if (get_call_result_type(fcinfo, NULL, &tupleDesc) != TYPEFUNC_COMPOSITE)
252 elog(ERROR, "return type must be a row type");
253
254 j = 0;
255 values[j++] = psprintf("%d", indexStat.version);
256 values[j++] = psprintf("%d", indexStat.level);
257 values[j++] = psprintf(INT64_FORMAT,
258 (1 + /* include the metapage in index_size */
259 indexStat.leaf_pages +
260 indexStat.internal_pages +
261 indexStat.deleted_pages +
262 indexStat.empty_pages) * BLCKSZ);
263 values[j++] = psprintf("%u", indexStat.root_blkno);
264 values[j++] = psprintf(INT64_FORMAT, indexStat.internal_pages);
265 values[j++] = psprintf(INT64_FORMAT, indexStat.leaf_pages);
266 values[j++] = psprintf(INT64_FORMAT, indexStat.empty_pages);
267 values[j++] = psprintf(INT64_FORMAT, indexStat.deleted_pages);
268 if (indexStat.max_avail > 0)
269 values[j++] = psprintf("%.2f",
270 100.0 - (double) indexStat.free_space / (double) indexStat.max_avail * 100.0);
271 else
272 values[j++] = pstrdup("NaN");
273 if (indexStat.leaf_pages > 0)
274 values[j++] = psprintf("%.2f",
275 (double) indexStat.fragments / (double) indexStat.leaf_pages * 100.0);
276 else
277 values[j++] = pstrdup("NaN");
278
279 tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
280 values);
281
282 result = HeapTupleGetDatum(tuple);
283 }
284
285 return result;
286 }
287
288 /* --------------------------------------------------------
289 * pg_relpages()
290 *
291 * Get the number of pages of the table/index.
292 *
293 * Usage: SELECT pg_relpages('t1');
294 * SELECT pg_relpages('t1_pkey');
295 * --------------------------------------------------------
296 */
297 Datum
pg_relpages(PG_FUNCTION_ARGS)298 pg_relpages(PG_FUNCTION_ARGS)
299 {
300 text *relname = PG_GETARG_TEXT_P(0);
301 int64 relpages;
302 Relation rel;
303 RangeVar *relrv;
304
305 if (!superuser())
306 ereport(ERROR,
307 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
308 (errmsg("must be superuser to use pgstattuple functions"))));
309
310 relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
311 rel = relation_openrv(relrv, AccessShareLock);
312
313 /* note: this will work OK on non-local temp tables */
314
315 relpages = RelationGetNumberOfBlocks(rel);
316
317 relation_close(rel, AccessShareLock);
318
319 PG_RETURN_INT64(relpages);
320 }
321
322 Datum
pg_relpagesbyid(PG_FUNCTION_ARGS)323 pg_relpagesbyid(PG_FUNCTION_ARGS)
324 {
325 Oid relid = PG_GETARG_OID(0);
326 int64 relpages;
327 Relation rel;
328
329 if (!superuser())
330 ereport(ERROR,
331 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
332 (errmsg("must be superuser to use pgstattuple functions"))));
333
334 rel = relation_open(relid, AccessShareLock);
335
336 /* note: this will work OK on non-local temp tables */
337
338 relpages = RelationGetNumberOfBlocks(rel);
339
340 relation_close(rel, AccessShareLock);
341
342 PG_RETURN_INT64(relpages);
343 }
344
345 /* ------------------------------------------------------
346 * pgstatginindex()
347 *
348 * Usage: SELECT * FROM pgstatginindex('ginindex');
349 * ------------------------------------------------------
350 */
351 Datum
pgstatginindex(PG_FUNCTION_ARGS)352 pgstatginindex(PG_FUNCTION_ARGS)
353 {
354 Oid relid = PG_GETARG_OID(0);
355 Relation rel;
356 Buffer buffer;
357 Page page;
358 GinMetaPageData *metadata;
359 GinIndexStat stats;
360 HeapTuple tuple;
361 TupleDesc tupleDesc;
362 Datum values[3];
363 bool nulls[3] = {false, false, false};
364 Datum result;
365
366 if (!superuser())
367 ereport(ERROR,
368 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
369 (errmsg("must be superuser to use pgstattuple functions"))));
370
371 rel = relation_open(relid, AccessShareLock);
372
373 if (!IS_INDEX(rel) || !IS_GIN(rel))
374 elog(ERROR, "relation \"%s\" is not a GIN index",
375 RelationGetRelationName(rel));
376
377 /*
378 * Reject attempts to read non-local temporary relations; we would be
379 * likely to get wrong data since we have no visibility into the owning
380 * session's local buffers.
381 */
382 if (RELATION_IS_OTHER_TEMP(rel))
383 ereport(ERROR,
384 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
385 errmsg("cannot access temporary indexes of other sessions")));
386
387 /*
388 * Read metapage
389 */
390 buffer = ReadBuffer(rel, GIN_METAPAGE_BLKNO);
391 LockBuffer(buffer, GIN_SHARE);
392 page = BufferGetPage(buffer);
393 metadata = GinPageGetMeta(page);
394
395 stats.version = metadata->ginVersion;
396 stats.pending_pages = metadata->nPendingPages;
397 stats.pending_tuples = metadata->nPendingHeapTuples;
398
399 UnlockReleaseBuffer(buffer);
400 relation_close(rel, AccessShareLock);
401
402 /*
403 * Build a tuple descriptor for our result type
404 */
405 if (get_call_result_type(fcinfo, NULL, &tupleDesc) != TYPEFUNC_COMPOSITE)
406 elog(ERROR, "return type must be a row type");
407
408 values[0] = Int32GetDatum(stats.version);
409 values[1] = UInt32GetDatum(stats.pending_pages);
410 values[2] = Int64GetDatum(stats.pending_tuples);
411
412 /*
413 * Build and return the tuple
414 */
415 tuple = heap_form_tuple(tupleDesc, values, nulls);
416 result = HeapTupleGetDatum(tuple);
417
418 PG_RETURN_DATUM(result);
419 }
420