Add database page inspection /contrib module.

Simon and Heikki

1 files changed, 38 insertions, 476 deletions

diff --git a/contrib/pgstattuple/pgstatindex.c b/contrib/pgstattuple/pgstatindex.c
index 2982ceaf506..838fd9e5250 100644
--- a/contrib/pgstattuple/pgstatindex.c
+++ b/contrib/pgstattuple/pgstatindex.c
@@ -36,30 +36,14 @@
 #include "utils/inval.h"
 
 PG_FUNCTION_INFO_V1(pgstatindex);
-PG_FUNCTION_INFO_V1(bt_metap);
-PG_FUNCTION_INFO_V1(bt_page_items);
-PG_FUNCTION_INFO_V1(bt_page_stats);
 PG_FUNCTION_INFO_V1(pg_relpages);
 
 extern Datum pgstatindex(PG_FUNCTION_ARGS);
-extern Datum bt_metap(PG_FUNCTION_ARGS);
-extern Datum bt_page_items(PG_FUNCTION_ARGS);
-extern Datum bt_page_stats(PG_FUNCTION_ARGS);
 extern Datum pg_relpages(PG_FUNCTION_ARGS);
 
 #define PGSTATINDEX_TYPE "public.pgstatindex_type"
 #define PGSTATINDEX_NCOLUMNS 10
 
-#define BTMETAP_TYPE "public.bt_metap_type"
-#define BTMETAP_NCOLUMNS 6
-
-#define BTPAGEITEMS_TYPE "public.bt_page_items_type"
-#define BTPAGEITEMS_NCOLUMNS 6
-
-#define BTPAGESTATS_TYPE "public.bt_page_stats_type"
-#define BTPAGESTATS_NCOLUMNS 11
-
-
 #define IS_INDEX(r) ((r)->rd_rel->relkind == 'i')
 #define IS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID)
 
@@ -73,150 +57,28 @@ extern Datum pg_relpages(PG_FUNCTION_ARGS);
 			 elog(ERROR, "Block number out of range."); }
 
 /* ------------------------------------------------
- * structure for single btree page statistics
- * ------------------------------------------------
- */
-typedef struct BTPageStat
-{
-	uint32		blkno;
-	uint32		live_items;
-	uint32		dead_items;
-	uint32		page_size;
-	uint32		max_avail;
-	uint32		free_size;
-	uint32		avg_item_size;
-	uint32		fragments;
-	char		type;
-
-	/* opaque data */
-	BlockNumber btpo_prev;
-	BlockNumber btpo_next;
-	union
-	{
-		uint32		level;
-		TransactionId xact;
-	}			btpo;
-	uint16		btpo_flags;
-	BTCycleId	btpo_cycleid;
-}	BTPageStat;
-
-/* ------------------------------------------------
  * A structure for a whole btree index statistics
  * used by pgstatindex().
  * ------------------------------------------------
  */
 typedef struct BTIndexStat
 {
-	uint32		magic;
 	uint32		version;
 	BlockNumber root_blkno;
 	uint32		level;
 
-	BlockNumber fastroot;
-	uint32		fastlevel;
-
-	uint32		live_items;
-	uint32		dead_items;
-
 	uint32		root_pages;
 	uint32		internal_pages;
 	uint32		leaf_pages;
 	uint32		empty_pages;
 	uint32		deleted_pages;
 
-	uint32		page_size;
-	uint32		avg_item_size;
-
 	uint32		max_avail;
 	uint32		free_space;
 
 	uint32		fragments;
 }	BTIndexStat;
 
-/* -------------------------------------------------
- * GetBTPageStatistics()
- *
- * Collect statistics of single b-tree leaf page
- * -------------------------------------------------
- */
-static void
-GetBTPageStatistics(BlockNumber blkno, Buffer buffer, BTPageStat * stat)
-{
-	Page		page = BufferGetPage(buffer);
-	PageHeader	phdr = (PageHeader) page;
-	OffsetNumber maxoff = PageGetMaxOffsetNumber(page);
-	BTPageOpaque opaque = (BTPageOpaque) PageGetSpecialPointer(page);
-	int			item_size = 0;
-	int			off;
-
-	stat->blkno = blkno;
-
-	stat->max_avail = BLCKSZ - (BLCKSZ - phdr->pd_special + SizeOfPageHeaderData);
-
-	stat->dead_items = stat->live_items = 0;
-	stat->fragments = 0;
-
-	stat->page_size = PageGetPageSize(page);
-
-	/* page type (flags) */
-	if (P_ISDELETED(opaque))
-	{
-		stat->type = 'd';
-		stat->btpo.xact = opaque->btpo.xact;
-		return;
-	}
-	else if (P_IGNORE(opaque))
-		stat->type = 'e';
-	else if (P_ISLEAF(opaque))
-		stat->type = 'l';
-	else if (P_ISROOT(opaque))
-		stat->type = 'r';
-	else
-		stat->type = 'i';
-
-	/* btpage opaque data */
-	stat->btpo_prev = opaque->btpo_prev;
-	stat->btpo_next = opaque->btpo_next;
-	stat->btpo.level = opaque->btpo.level;
-	stat->btpo_flags = opaque->btpo_flags;
-	stat->btpo_cycleid = opaque->btpo_cycleid;
-
-	/*----------------------------------------------
-	 * If a next leaf is on the previous block,
-	 * it means a fragmentation.
-	 *----------------------------------------------
-	 */
-	if (stat->type == 'l')
-	{
-		if (opaque->btpo_next != P_NONE && opaque->btpo_next < blkno)
-			stat->fragments++;
-	}
-
-	/* count live and dead tuples, and free space */
-	for (off = FirstOffsetNumber; off <= maxoff; off++)
-	{
-		IndexTuple	itup;
-
-		ItemId		id = PageGetItemId(page, off);
-
-		itup = (IndexTuple) PageGetItem(page, id);
-
-		item_size += IndexTupleSize(itup);
-
-		if (!ItemIdDeleted(id))
-			stat->live_items++;
-		else
-			stat->dead_items++;
-	}
-	stat->free_size = PageGetFreeSpace(page);
-
-	if ((stat->live_items + stat->dead_items) > 0)
-		stat->avg_item_size = item_size / (stat->live_items + stat->dead_items);
-	else
-		stat->avg_item_size = 0;
-}
-
-
 /* ------------------------------------------------------
  * pgstatindex()
  *
@@ -249,12 +111,9 @@ pgstatindex(PG_FUNCTION_ARGS)
 		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);
 	}
@@ -279,47 +138,49 @@ pgstatindex(PG_FUNCTION_ARGS)
 	 */
 	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.");
-		}
+		Buffer		buffer;
+		Page		page;
+		BTPageOpaque opaque;
+
+		/* Read and lock buffer */
+		buffer = ReadBuffer(rel, blkno);
+		LockBuffer(buffer, BUFFER_LOCK_SHARE);
 
-		/* -- leaf fragmentation -- */
-		indexStat.fragments += stat.fragments;
+		page = BufferGetPage(buffer);
+		opaque = (BTPageOpaque) PageGetSpecialPointer(page);
 
-		if (stat.type == 'l')
+		/* Determine page type, and update totals */
+
+		if (P_ISDELETED(opaque))
+			indexStat.deleted_pages++;
+
+		else if (P_IGNORE(opaque))
+			indexStat.empty_pages++;
+
+		else if (P_ISLEAF(opaque))
 		{
-			indexStat.max_avail += stat.max_avail;
-			indexStat.free_space += stat.free_size;
+			int max_avail;
+			max_avail = BLCKSZ - (BLCKSZ - ((PageHeader)page)->pd_special + SizeOfPageHeaderData);
+			indexStat.max_avail += max_avail;
+			indexStat.free_space += PageGetFreeSpace(page);
+
+			indexStat.leaf_pages++;
+
+			/*
+			 * If the next leaf is on an earlier block, it
+			 * means a fragmentation.
+			 */
+			if (opaque->btpo_next != P_NONE && opaque->btpo_next < blkno)
+				indexStat.fragments++;
 		}
+		else if (P_ISROOT(opaque))
+			indexStat.root_pages++;
+
+		else
+			indexStat.internal_pages++;
 
+		/* Unlock and release buffer */
+		LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
 		ReleaseBuffer(buffer);
 	}
 
@@ -373,305 +234,6 @@ pgstatindex(PG_FUNCTION_ARGS)
 	PG_RETURN_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;
-
-	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_items()
- *
- * Get IndexTupleData set in a leaf page
- *
- * Usage: SELECT * FROM bt_page_items('t1_pkey', 0);
- *-------------------------------------------------------
- */
-/* ---------------------------------------------------
- * data structure for SRF to hold a scan information
- * ---------------------------------------------------
- */
-struct user_args
-{
-	TupleDesc	tupd;
-	Relation	rel;
-	Buffer		buffer;
-	Page		page;
-	uint16		offset;
-};
-
-Datum
-bt_page_items(PG_FUNCTION_ARGS)
-{
-	text	   *relname = PG_GETARG_TEXT_P(0);
-	uint32		blkno = PG_GETARG_UINT32(1);
-
-	RangeVar   *relrv;
-	Datum		result;
-	char	   *values[BTPAGEITEMS_NCOLUMNS];
-	BTPageOpaque opaque;
-	HeapTuple	tuple;
-	ItemId		id;
-
-	FuncCallContext *fctx;
-	MemoryContext mctx;
-	struct user_args *uargs = NULL;
-
-	if (blkno == 0)
-		elog(ERROR, "Block 0 is a meta page.");
-
-	if (SRF_IS_FIRSTCALL())
-	{
-		fctx = SRF_FIRSTCALL_INIT();
-		mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
-
-		uargs = palloc(sizeof(struct user_args));
-
-		uargs->tupd = RelationNameGetTupleDesc(BTPAGEITEMS_TYPE);
-		uargs->offset = FirstOffsetNumber;
-
-		relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
-		uargs->rel = relation_openrv(relrv, AccessShareLock);
-
-		CHECK_RELATION_BLOCK_RANGE(uargs->rel, blkno);
-
-		uargs->buffer = ReadBuffer(uargs->rel, blkno);
-
-		if (!IS_INDEX(uargs->rel) || !IS_BTREE(uargs->rel))
-			elog(ERROR, "bt_page_items() can be used only on b-tree index.");
-
-		uargs->page = BufferGetPage(uargs->buffer);
-
-		opaque = (BTPageOpaque) PageGetSpecialPointer(uargs->page);
-
-		if (P_ISDELETED(opaque))
-			elog(NOTICE, "bt_page_items(): this page is deleted.");
-
-		fctx->max_calls = PageGetMaxOffsetNumber(uargs->page);
-		fctx->user_fctx = uargs;
-
-		MemoryContextSwitchTo(mctx);
-	}
-
-	fctx = SRF_PERCALL_SETUP();
-	uargs = fctx->user_fctx;
-
-	if (fctx->call_cntr < fctx->max_calls)
-	{
-		IndexTuple	itup;
-
-		id = PageGetItemId(uargs->page, uargs->offset);
-
-		if (!ItemIdIsValid(id))
-			elog(ERROR, "Invalid ItemId.");
-
-		itup = (IndexTuple) PageGetItem(uargs->page, id);
-
-		{
-			int			j = 0;
-
-			BlockNumber blkno = BlockIdGetBlockNumber(&(itup->t_tid.ip_blkid));
-
-			values[j] = palloc(32);
-			snprintf(values[j++], 32, "%d", uargs->offset);
-			values[j] = palloc(32);
-			snprintf(values[j++], 32, "(%u,%u)", blkno, itup->t_tid.ip_posid);
-			values[j] = palloc(32);
-			snprintf(values[j++], 32, "%d", (int) IndexTupleSize(itup));
-			values[j] = palloc(32);
-			snprintf(values[j++], 32, "%c", IndexTupleHasNulls(itup) ? 't' : 'f');
-			values[j] = palloc(32);
-			snprintf(values[j++], 32, "%c", IndexTupleHasVarwidths(itup) ? 't' : 'f');
-
-			{
-				int			off;
-				char	   *dump;
-				char	   *ptr = (char *) itup + IndexInfoFindDataOffset(itup->t_info);
-
-				dump = palloc(IndexTupleSize(itup) * 3);
-				memset(dump, 0, IndexTupleSize(itup) * 3);
-
-				for (off = 0;
-					 off < IndexTupleSize(itup) - IndexInfoFindDataOffset(itup->t_info);
-					 off++)
-				{
-					if (dump[0] == '\0')
-						sprintf(dump, "%02x", *(ptr + off) & 0xff);
-					else
-					{
-						char		buf[4];
-
-						sprintf(buf, " %02x", *(ptr + off) & 0xff);
-						strcat(dump, buf);
-					}
-				}
-				values[j] = dump;
-			}
-
-			tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(uargs->tupd), values);
-			result = TupleGetDatum(TupleDescGetSlot(uargs->tupd), tuple);
-		}
-
-		uargs->offset = uargs->offset + 1;
-
-		SRF_RETURN_NEXT(fctx, result);
-	}
-	else
-	{
-		ReleaseBuffer(uargs->buffer);
-		relation_close(uargs->rel, AccessShareLock);
-
-		SRF_RETURN_DONE(fctx);
-	}
-}
-
-
-/* ------------------------------------------------
- * bt_metap()
- *
- * Get a btree meta-page information
- *
- * Usage: SELECT * FROM bt_metap('t1_pkey')
- * ------------------------------------------------
- */
-Datum
-bt_metap(PG_FUNCTION_ARGS)
-{
-	text	   *relname = PG_GETARG_TEXT_P(0);
-	Buffer		buffer;
-
-	Relation	rel;
-	RangeVar   *relrv;
-	Datum		result;
-
-	relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
-	rel = relation_openrv(relrv, AccessShareLock);
-
-	if (!IS_INDEX(rel) || !IS_BTREE(rel))
-		elog(ERROR, "bt_metap() can be used only on b-tree index.");
-
-	buffer = ReadBuffer(rel, 0);
-
-	{
-		BTMetaPageData *metad;
-
-		TupleDesc	tupleDesc;
-		int			j;
-		char	   *values[BTMETAP_NCOLUMNS];
-		HeapTuple	tuple;
-
-		Page		page = BufferGetPage(buffer);
-
-		metad = BTPageGetMeta(page);
-
-		tupleDesc = RelationNameGetTupleDesc(BTMETAP_TYPE);
-
-		j = 0;
-		values[j] = palloc(32);
-		snprintf(values[j++], 32, "%d", metad->btm_magic);
-		values[j] = palloc(32);
-		snprintf(values[j++], 32, "%d", metad->btm_version);
-		values[j] = palloc(32);
-		snprintf(values[j++], 32, "%d", metad->btm_root);
-		values[j] = palloc(32);
-		snprintf(values[j++], 32, "%d", metad->btm_level);
-		values[j] = palloc(32);
-		snprintf(values[j++], 32, "%d", metad->btm_fastroot);
-		values[j] = palloc(32);
-		snprintf(values[j++], 32, "%d", metad->btm_fastlevel);
-
-		tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
-									   values);
-
-		result = TupleGetDatum(TupleDescGetSlot(tupleDesc), tuple);
-	}
-
-	ReleaseBuffer(buffer);
-
-	relation_close(rel, AccessShareLock);
-
-	PG_RETURN_DATUM(result);
-}
-
 /* --------------------------------------------------------
  * pg_relpages()
  *