/* ----------------------------------------------- * bt_page() * * Usage: SELECT * FROM bt_page('t1_pkey', 1); * ----------------------------------------------- */ datum_t bt_page_stats(PG_FUNC_ARGS) { text *relname = ARG_TEXT_P(0); uint32 blkno = ARG_UINT32(1); buf_id_t buffer; struct relation * rel; range_var_n *relrv; datum_t result; struct heap_tuple * tuple; struct tuple *tupleDesc; int j; char *values[11]; BTPageStat stat; if (!superuser()) ereport(ERROR, (errcode(E_INSUFFICIENT_PRIVILEGE), (errmsg("must be superuser to use pageinspect functions")))); relrv = nl_to_range_var(textToQualifiedNameList(relname)); rel = relation_openrv(relrv, ACCESS_SHR_LOCK); if (!IS_INDEX(rel) || !IS_BTREE(rel)) elog(ERROR, "relation \"%s\" is not a btree index", REL_NAME(rel)); /* * Reject attempts to read non-local temporary relations; we would be * likely to get wrong data since we have no visibility into the owning * session's local buffers. */ if (REL_IS_OTHER_TMP(rel)) ereport(ERROR, (errcode(E_FEATURE_NOT_SUPPORTED), errmsg("cannot access temporary tables of other sessions"))); if (blkno == 0) elog(ERROR, "block 0 is a meta page"); CHECK_RELATION_BLOCK_RANGE(rel, blkno); buffer = read_buf(rel, blkno); /* keep compiler quiet */ stat.btpo_prev = stat.btpo_next = INVALID_BLK_NR; stat.btpo_flags = stat.free_size = stat.avg_item_size = 0; GetBTPageStatistics(blkno, buffer, &stat); /* Build a tuple descriptor for our result type */ if (get_call_result_type(fcinfo, NULL, &tupleDesc) != TYPEFUNC_COMPOSITE) elog(ERROR, "return type must be a row type"); j = 0; values[j] = palloc(32); snprintf(values[j++], 32, "%d", stat.blkno); values[j] = palloc(32); snprintf(values[j++], 32, "%c", stat.type); values[j] = palloc(32); snprintf(values[j++], 32, "%d", stat.live_items); values[j] = palloc(32); snprintf(values[j++], 32, "%d", stat.dead_items); values[j] = palloc(32); snprintf(values[j++], 32, "%d", stat.avg_item_size); values[j] = palloc(32); snprintf(values[j++], 32, "%d", stat.page_size); values[j] = palloc(32); snprintf(values[j++], 32, "%d", stat.free_size); values[j] = palloc(32); snprintf(values[j++], 32, "%d", stat.btpo_prev); values[j] = palloc(32); snprintf(values[j++], 32, "%d", stat.btpo_next); values[j] = palloc(32); if (stat.type == 'd') snprintf(values[j++], 32, "%d", stat.btpo.xact); else snprintf(values[j++], 32, "%d", stat.btpo.level); values[j] = palloc(32); snprintf(values[j++], 32, "%d", stat.btpo_flags); tuple = build_tuple_from_cstrings(TupleDescGetAttInMetadata(tupleDesc), values); result = HeapTupleGetDatum(tuple); release_buf(buffer); relation_close(rel, ACCESS_SHR_LOCK); RET_DATUM(result); }
/* ----------------------------------------------- * bt_page() * * Usage: SELECT * FROM bt_page('t1_pkey', 0); * ----------------------------------------------- */ Datum bt_page_stats(PG_FUNCTION_ARGS) { text *relname = PG_GETARG_TEXT_P(0); uint32 blkno = PG_GETARG_UINT32(1); Buffer buffer; Relation rel; RangeVar *relrv; Datum result; if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), (errmsg("must be superuser to use pgstattuple functions")))); relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname)); rel = relation_openrv(relrv, AccessShareLock); CHECK_RELATION_BLOCK_RANGE(rel, blkno); buffer = ReadBuffer(rel, blkno); if (!IS_INDEX(rel) || !IS_BTREE(rel)) elog(ERROR, "bt_page_stats() can be used only on b-tree index."); if (blkno == 0) elog(ERROR, "Block 0 is a meta page."); { HeapTuple tuple; TupleDesc tupleDesc; int j; char *values[BTPAGESTATS_NCOLUMNS]; BTPageStat stat; GetBTPageStatistics(blkno, buffer, &stat); tupleDesc = RelationNameGetTupleDesc(BTPAGESTATS_TYPE); j = 0; values[j] = palloc(32); snprintf(values[j++], 32, "%d", stat.blkno); values[j] = palloc(32); snprintf(values[j++], 32, "%c", stat.type); values[j] = palloc(32); snprintf(values[j++], 32, "%d", stat.live_items); values[j] = palloc(32); snprintf(values[j++], 32, "%d", stat.dead_items); values[j] = palloc(32); snprintf(values[j++], 32, "%d", stat.avg_item_size); values[j] = palloc(32); snprintf(values[j++], 32, "%d", stat.page_size); values[j] = palloc(32); snprintf(values[j++], 32, "%d", stat.free_size); values[j] = palloc(32); snprintf(values[j++], 32, "%d", stat.btpo_prev); values[j] = palloc(32); snprintf(values[j++], 32, "%d", stat.btpo_next); values[j] = palloc(32); if (stat.type == 'd') snprintf(values[j++], 32, "%d", stat.btpo.xact); else snprintf(values[j++], 32, "%d", stat.btpo.level); values[j] = palloc(32); snprintf(values[j++], 32, "%d", stat.btpo_flags); tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc), values); result = TupleGetDatum(TupleDescGetSlot(tupleDesc), tuple); } ReleaseBuffer(buffer); relation_close(rel, AccessShareLock); PG_RETURN_DATUM(result); }
/* ----------------------------------------------- * bt_page_stats() * * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1); * ----------------------------------------------- */ Datum bt_page_stats(PG_FUNCTION_ARGS) { text *relname = PG_GETARG_TEXT_PP(0); uint32 blkno = PG_GETARG_UINT32(1); Buffer buffer; Relation rel; RangeVar *relrv; Datum result; HeapTuple tuple; TupleDesc tupleDesc; int j; char *values[11]; BTPageStat stat; if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), (errmsg("must be superuser to use pageinspect functions")))); relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname)); rel = relation_openrv(relrv, AccessShareLock); if (!IS_INDEX(rel) || !IS_BTREE(rel)) elog(ERROR, "relation \"%s\" is not a btree index", RelationGetRelationName(rel)); /* * Reject attempts to read non-local temporary relations; we would be * likely to get wrong data since we have no visibility into the owning * session's local buffers. */ if (RELATION_IS_OTHER_TEMP(rel)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot access temporary tables of other sessions"))); if (blkno == 0) elog(ERROR, "block 0 is a meta page"); CHECK_RELATION_BLOCK_RANGE(rel, blkno); buffer = ReadBuffer(rel, blkno); LockBuffer(buffer, BUFFER_LOCK_SHARE); /* keep compiler quiet */ stat.btpo_prev = stat.btpo_next = InvalidBlockNumber; stat.btpo_flags = stat.free_size = stat.avg_item_size = 0; GetBTPageStatistics(blkno, buffer, &stat); UnlockReleaseBuffer(buffer); relation_close(rel, AccessShareLock); /* Build a tuple descriptor for our result type */ if (get_call_result_type(fcinfo, NULL, &tupleDesc) != TYPEFUNC_COMPOSITE) elog(ERROR, "return type must be a row type"); j = 0; values[j++] = psprintf("%d", stat.blkno); values[j++] = psprintf("%c", stat.type); values[j++] = psprintf("%d", stat.live_items); values[j++] = psprintf("%d", stat.dead_items); values[j++] = psprintf("%d", stat.avg_item_size); values[j++] = psprintf("%d", stat.page_size); values[j++] = psprintf("%d", stat.free_size); values[j++] = psprintf("%d", stat.btpo_prev); values[j++] = psprintf("%d", stat.btpo_next); values[j++] = psprintf("%d", (stat.type == 'd') ? stat.btpo.xact : stat.btpo.level); values[j++] = psprintf("%d", stat.btpo_flags); tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc), values); result = HeapTupleGetDatum(tuple); PG_RETURN_DATUM(result); }
/* ------------------------------------------------------ * pgstatindex() * * Usage: SELECT * FROM pgstatindex('t1_pkey'); * ------------------------------------------------------ */ Datum pgstatindex(PG_FUNCTION_ARGS) { text *relname = PG_GETARG_TEXT_P(0); Relation rel; RangeVar *relrv; Datum result; uint32 nblocks; uint32 blkno; BTIndexStat indexStat; if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), (errmsg("must be superuser to use pgstattuple functions")))); relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname)); rel = relation_openrv(relrv, AccessShareLock); if (!IS_INDEX(rel) || !IS_BTREE(rel)) elog(ERROR, "pgstatindex() can be used only on b-tree index."); /*------------------- * Read a metapage *------------------- */ { Buffer buffer = ReadBuffer(rel, 0); Page page = BufferGetPage(buffer); BTMetaPageData *metad = BTPageGetMeta(page); indexStat.magic = metad->btm_magic; indexStat.version = metad->btm_version; indexStat.root_blkno = metad->btm_root; indexStat.level = metad->btm_level; indexStat.fastroot = metad->btm_fastroot; indexStat.fastlevel = metad->btm_fastlevel; ReleaseBuffer(buffer); } nblocks = RelationGetNumberOfBlocks(rel); /* -- init stat -- */ indexStat.fragments = 0; indexStat.root_pages = 0; indexStat.leaf_pages = 0; indexStat.internal_pages = 0; indexStat.empty_pages = 0; indexStat.deleted_pages = 0; indexStat.max_avail = 0; indexStat.free_space = 0; /*----------------------- * Scan all blocks *----------------------- */ for (blkno = 1; blkno < nblocks; blkno++) { Buffer buffer = ReadBuffer(rel, blkno); BTPageStat stat; /* scan one page */ stat.blkno = blkno; GetBTPageStatistics(blkno, buffer, &stat); /*--------------------- * page status (type) *--------------------- */ switch (stat.type) { case 'd': indexStat.deleted_pages++; break; case 'l': indexStat.leaf_pages++; break; case 'i': indexStat.internal_pages++; break; case 'e': indexStat.empty_pages++; break; case 'r': indexStat.root_pages++; break; default: elog(ERROR, "unknown page status."); } /* -- leaf fragmentation -- */ indexStat.fragments += stat.fragments; if (stat.type == 'l') { indexStat.max_avail += stat.max_avail; indexStat.free_space += stat.free_size; } ReleaseBuffer(buffer); } relation_close(rel, AccessShareLock); /*---------------------------- * Build a result tuple *---------------------------- */ { TupleDesc tupleDesc; int j; char *values[PGSTATINDEX_NCOLUMNS]; HeapTupleData tupleData; HeapTuple tuple = &tupleData; tupleDesc = RelationNameGetTupleDesc(PGSTATINDEX_TYPE); j = 0; values[j] = palloc(32); snprintf(values[j++], 32, "%d", indexStat.version); values[j] = palloc(32); snprintf(values[j++], 32, "%d", indexStat.level); values[j] = palloc(32); snprintf(values[j++], 32, "%d", (indexStat.root_pages + indexStat.leaf_pages + indexStat.internal_pages + indexStat.deleted_pages + indexStat.empty_pages) * BLCKSZ); values[j] = palloc(32); snprintf(values[j++], 32, "%d", indexStat.root_blkno); values[j] = palloc(32); snprintf(values[j++], 32, "%d", indexStat.internal_pages); values[j] = palloc(32); snprintf(values[j++], 32, "%d", indexStat.leaf_pages); values[j] = palloc(32); snprintf(values[j++], 32, "%d", indexStat.empty_pages); values[j] = palloc(32); snprintf(values[j++], 32, "%d", indexStat.deleted_pages); values[j] = palloc(32); snprintf(values[j++], 32, "%.2f", 100.0 - (float) indexStat.free_space / (float) indexStat.max_avail * 100.0); values[j] = palloc(32); snprintf(values[j++], 32, "%.2f", (float) indexStat.fragments / (float) indexStat.leaf_pages * 100.0); tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc), values); result = TupleGetDatum(TupleDescGetSlot(tupleDesc), tuple); } PG_RETURN_DATUM(result); }