From: Jesper Pedersen <jesper.pedersen@redhat.com>
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: pageinspect: Hash index support
Date: Tue, 30 Aug 2016 10:49:45 -0400
Message-ID: <e2ac6c58-b93f-9dd9-f4e6-d6d30add7fdf@redhat.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Hi,
Attached is a patch that adds support for hash indexes in pageinspect.
The functionality (hash_metap, hash_page_stats and hash_page_items)
follows the B-tree functions.
This patch will need an update once Amit's and Mithun's work on
Concurrent Hash Indexes is committed to account for the new meta-page
constants.
I'll create a CommitFest entry for this submission.
Feedback is most welcome.
Best regards,
Jesper
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackersAttachments:0001-pageinspect-Hash-index-support_v1.patch (text/x-patch, 34.9K) download
From 55262d5fa3822afbae94f4989627dd65e91fe098 Mon Sep 17 00:00:00 2001
From: jesperpedersen <jesper.pedersen@redhat.com>
Date: Fri, 5 Aug 2016 10:16:32 -0400
Subject: [PATCH] pageinspect: Hash index support
---
contrib/pageinspect/Makefile | 10 +-
contrib/pageinspect/hashfuncs.c | 472 ++++++++++++++++++++++++++
contrib/pageinspect/pageinspect--1.5--1.6.sql | 64 ++++
contrib/pageinspect/pageinspect--1.5.sql | 279 ---------------
contrib/pageinspect/pageinspect--1.6.sql | 339 ++++++++++++++++++
contrib/pageinspect/pageinspect.control | 2 +-
doc/src/sgml/pageinspect.sgml | 100 ++++++
7 files changed, 981 insertions(+), 285 deletions(-)
create mode 100644 contrib/pageinspect/hashfuncs.c
create mode 100644 contrib/pageinspect/pageinspect--1.5--1.6.sql
delete mode 100644 contrib/pageinspect/pageinspect--1.5.sql
create mode 100644 contrib/pageinspect/pageinspect--1.6.sql
diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefileindex a98237e..c73d728 100644--- a/contrib/pageinspect/Makefile+++ b/contrib/pageinspect/Makefile@@ -2,13 +2,13 @@
MODULE_big = pageinspect
OBJS = rawpage.o heapfuncs.o btreefuncs.o fsmfuncs.o \
- brinfuncs.o ginfuncs.o $(WIN32RES)+ brinfuncs.o ginfuncs.o hashfuncs.o $(WIN32RES)
EXTENSION = pageinspect
-DATA = pageinspect--1.5.sql pageinspect--1.4--1.5.sql \- pageinspect--1.3--1.4.sql pageinspect--1.2--1.3.sql \- pageinspect--1.1--1.2.sql pageinspect--1.0--1.1.sql \- pageinspect--unpackaged--1.0.sql+DATA = pageinspect--1.6.sql pageinspect--1.5--1.6.sql \+ pageinspect--1.4--1.5.sql pageinspect--1.3--1.4.sql \+ pageinspect--1.2--1.3.sql pageinspect--1.1--1.2.sql \+ pageinspect--1.0--1.1.sql pageinspect--unpackaged--1.0.sql
PGFILEDESC = "pageinspect - functions to inspect contents of database pages"
ifdef USE_PGXS
diff --git a/contrib/pageinspect/hashfuncs.c b/contrib/pageinspect/hashfuncs.cnew file mode 100644index 0000000..598b7b2--- /dev/null+++ b/contrib/pageinspect/hashfuncs.c@@ -0,0 +1,472 @@+/*+ * hashfuncs.c+ * Functions to investigate the content of HASH indexes+ *+ * Copyright (c) 2016, PostgreSQL Global Development Group+ *+ * IDENTIFICATION+ * contrib/pageinspect/hashfuncs.c+ */++#include "postgres.h"++#include "access/hash.h"+#include "access/htup_details.h"+#include "catalog/namespace.h"+#include "catalog/pg_am.h"+#include "funcapi.h"+#include "miscadmin.h"+#include "utils/builtins.h"+#include "utils/rel.h"+++PG_FUNCTION_INFO_V1(hash_metap);+PG_FUNCTION_INFO_V1(hash_page_items);+PG_FUNCTION_INFO_V1(hash_page_stats);++#define IS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)+#define IS_HASH(r) ((r)->rd_rel->relam == HASH_AM_OID)++/* note: BlockNumber is unsigned, hence can't be negative */+#define CHECK_RELATION_BLOCK_RANGE(rel, blkno) { \+ if ( RelationGetNumberOfBlocks(rel) <= (BlockNumber) (blkno) ) \+ elog(ERROR, "block number out of range"); }++/* ------------------------------------------------+ * structure for single hash page statistics+ * ------------------------------------------------+ */+typedef struct HashPageStat+{+ uint32 blkno;+ uint32 live_items;+ uint32 dead_items;+ uint32 page_size;+ uint32 max_avail;+ uint32 free_size;+ uint32 avg_item_size;+ char type;++ /* opaque data */+ BlockNumber hasho_prevblkno;+ BlockNumber hasho_nextblkno;+ Bucket hasho_bucket;+ uint16 hasho_flag;+ uint16 hasho_page_id;+} HashPageStat;+++/* -------------------------------------------------+ * GetHashPageStatistics()+ *+ * Collect statistics of single hash page+ * -------------------------------------------------+ */+static void+GetHashPageStatistics(BlockNumber blkno, Buffer buffer, HashPageStat *stat)+{+ Page page = BufferGetPage(buffer);+ PageHeader phdr = (PageHeader) page;+ OffsetNumber maxoff = PageGetMaxOffsetNumber(page);+ HashPageOpaque opaque = (HashPageOpaque) 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->page_size = PageGetPageSize(page);++ /* page type (flags) */+ if (opaque->hasho_flag & LH_UNUSED_PAGE)+ stat->type = 'u';+ else if (opaque->hasho_flag & LH_OVERFLOW_PAGE)+ stat->type = 'v';+ else if (opaque->hasho_flag & LH_BUCKET_PAGE)+ stat->type = 'b';+ else if (opaque->hasho_flag & LH_BITMAP_PAGE)+ stat->type = 'i';+ else+ stat->type = 'm';++ /* hash page opaque data */+ stat->hasho_prevblkno = opaque->hasho_prevblkno;+ stat->hasho_nextblkno = opaque->hasho_nextblkno;+ stat->hasho_bucket = opaque->hasho_bucket;+ stat->hasho_flag = opaque->hasho_flag;+ stat->hasho_page_id = opaque->hasho_page_id;++ /* 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 (!ItemIdIsDead(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;+}++/* ---------------------------------------------------+ * hash_page_stats()+ *+ * Usage: SELECT * FROM hash_page_stats('t1_hash', 1);+ * ---------------------------------------------------+ */+Datum+hash_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;+ HeapTuple tuple;+ TupleDesc tupleDesc;+ int j;+ char *values[12];+ HashPageStat 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_HASH(rel))+ elog(ERROR, "relation \"%s\" is not a hash 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.hasho_prevblkno = stat.hasho_nextblkno = InvalidBlockNumber;+ stat.hasho_flag = stat.hasho_page_id = stat.free_size = stat.avg_item_size = 0;++ GetHashPageStatistics(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.hasho_prevblkno);+ values[j++] = psprintf("%d", stat.hasho_nextblkno);+ values[j++] = psprintf("%d", stat.hasho_bucket);+ values[j++] = psprintf("%d", stat.hasho_flag);+ values[j++] = psprintf("%d", stat.hasho_page_id);++ tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),+ values);++ result = HeapTupleGetDatum(tuple);++ PG_RETURN_DATUM(result);+}++/*-------------------------------------------------------+ * hash_page_items()+ *+ * Get IndexTupleData set in a hash page+ *+ * Usage: SELECT * FROM hash_page_items('t1_hash', 1);+ *-------------------------------------------------------+ */++/*+ * cross-call data structure for SRF+ */+struct user_args+{+ Page page;+ OffsetNumber offset;+};++Datum+hash_page_items(PG_FUNCTION_ARGS)+{+ text *relname = PG_GETARG_TEXT_P(0);+ uint32 blkno = PG_GETARG_UINT32(1);+ Datum result;+ char *values[6];+ HeapTuple tuple;+ FuncCallContext *fctx;+ MemoryContext mctx;+ struct user_args *uargs;++ if (!superuser())+ ereport(ERROR,+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),+ (errmsg("must be superuser to use pageinspect functions"))));++ if (SRF_IS_FIRSTCALL())+ {+ RangeVar *relrv;+ Relation rel;+ Buffer buffer;+ TupleDesc tupleDesc;++ fctx = SRF_FIRSTCALL_INIT();++ relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));+ rel = relation_openrv(relrv, AccessShareLock);++ if (!IS_INDEX(rel) || !IS_HASH(rel))+ elog(ERROR, "relation \"%s\" is not a hash 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);++ /*+ * We copy the page into local storage to avoid holding pin on the+ * buffer longer than we must, and possibly failing to release it at+ * all if the calling query doesn't fetch all rows.+ */+ mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);++ uargs = palloc(sizeof(struct user_args));++ uargs->page = palloc(BLCKSZ);+ memcpy(uargs->page, BufferGetPage(buffer), BLCKSZ);++ UnlockReleaseBuffer(buffer);+ relation_close(rel, AccessShareLock);++ uargs->offset = FirstOffsetNumber;++ fctx->max_calls = PageGetMaxOffsetNumber(uargs->page);++ /* 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");++ fctx->attinmeta = TupleDescGetAttInMetadata(tupleDesc);++ fctx->user_fctx = uargs;++ MemoryContextSwitchTo(mctx);+ }++ fctx = SRF_PERCALL_SETUP();+ uargs = fctx->user_fctx;++ if (fctx->call_cntr < fctx->max_calls)+ {+ ItemId id;+ IndexTuple itup;+ int j;+ int off;+ int dlen;+ char *dump;+ char *ptr;++ id = PageGetItemId(uargs->page, uargs->offset);++ if (!ItemIdIsValid(id))+ elog(ERROR, "invalid ItemId");++ itup = (IndexTuple) PageGetItem(uargs->page, id);++ j = 0;+ values[j++] = psprintf("%d", uargs->offset);+ values[j++] = psprintf("(%u,%u)",+ BlockIdGetBlockNumber(&(itup->t_tid.ip_blkid)),+ itup->t_tid.ip_posid);+ values[j++] = psprintf("%d", (int) IndexTupleSize(itup));+ values[j++] = psprintf("%c", IndexTupleHasNulls(itup) ? 't' : 'f');+ values[j++] = psprintf("%c", IndexTupleHasVarwidths(itup) ? 't' : 'f');++ ptr = (char *) itup + IndexInfoFindDataOffset(itup->t_info);+ dlen = IndexTupleSize(itup) - IndexInfoFindDataOffset(itup->t_info);+ dump = palloc0(dlen * 3 + 1);+ values[j] = dump;+ for (off = 0; off < dlen; off++)+ {+ if (off > 0)+ *dump++ = ' ';+ sprintf(dump, "%02x", *(ptr + off) & 0xff);+ dump += 2;+ }++ tuple = BuildTupleFromCStrings(fctx->attinmeta, values);+ result = HeapTupleGetDatum(tuple);++ uargs->offset = uargs->offset + 1;++ SRF_RETURN_NEXT(fctx, result);+ }+ else+ {+ pfree(uargs->page);+ pfree(uargs);+ SRF_RETURN_DONE(fctx);+ }+}+++/* ------------------------------------------------+ * hash_metap()+ *+ * Get a hash's meta-page information+ *+ * Usage: SELECT * FROM hash_metap('t1_hash')+ * ------------------------------------------------+ */+Datum+hash_metap(PG_FUNCTION_ARGS)+{+ text *relname = PG_GETARG_TEXT_P(0);+ Datum result;+ Relation rel;+ RangeVar *relrv;+ HashMetaPageData *metad;+ TupleDesc tupleDesc;+ int i,j;+ char *values[16];+ char *spares;+ char *mapp;+ Buffer buffer;+ Page page;+ HeapTuple tuple;++ 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_HASH(rel))+ elog(ERROR, "relation \"%s\" is not a hash 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")));++ buffer = ReadBuffer(rel, 0);+ LockBuffer(buffer, BUFFER_LOCK_SHARE);++ page = BufferGetPage(buffer);+ metad = HashPageGetMeta(page);++ /* 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", metad->hashm_magic);+ values[j++] = psprintf("%d", metad->hashm_version);+ values[j++] = psprintf("%f", metad->hashm_ntuples);+ values[j++] = psprintf("%d", metad->hashm_ffactor);+ values[j++] = psprintf("%d", metad->hashm_bsize);+ values[j++] = psprintf("%d", metad->hashm_bmsize);+ values[j++] = psprintf("%d", metad->hashm_bmshift);+ values[j++] = psprintf("%d", metad->hashm_maxbucket);+ values[j++] = psprintf("%d", metad->hashm_highmask);+ values[j++] = psprintf("%d", metad->hashm_lowmask);+ values[j++] = psprintf("%d", metad->hashm_ovflpoint);+ values[j++] = psprintf("%d", metad->hashm_firstfree);+ values[j++] = psprintf("%d", metad->hashm_nmaps);+ values[j++] = psprintf("%d", metad->hashm_procid);++ spares = palloc0(HASH_MAX_SPLITPOINTS * 5 + 1);+ values[j++] = spares;+ for (i = 0; i < HASH_MAX_SPLITPOINTS; i++)+ {+ if (i > 0)+ *spares++ = ' ';++ sprintf(spares, "%04x", metad->hashm_spares[i]);+ spares += 4;+ }++ mapp = palloc0(HASH_MAX_BITMAPS * 5 + 1);+ values[j++] = mapp;+ for (i = 0; i < HASH_MAX_BITMAPS; i++)+ {+ if (i > 0)+ *mapp++ = ' ';++ sprintf(mapp, "%04x", metad->hashm_mapp[i]);+ mapp += 4;+ }++ tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),+ values);++ result = HeapTupleGetDatum(tuple);++ UnlockReleaseBuffer(buffer);+ relation_close(rel, AccessShareLock);++ PG_RETURN_DATUM(result);+}diff --git a/contrib/pageinspect/pageinspect--1.5--1.6.sql b/contrib/pageinspect/pageinspect--1.5--1.6.sqlnew file mode 100644index 0000000..cb59749--- /dev/null+++ b/contrib/pageinspect/pageinspect--1.5--1.6.sql@@ -0,0 +1,64 @@+/* contrib/pageinspect/pageinspect--1.5--1.6.sql */++-- complain if script is sourced in psql, rather than via ALTER EXTENSION+\echo Use "ALTER EXTENSION pageinspect UPDATE TO '1.6'" to load this file. \quit++--+-- HASH functions+--++--+-- hash_metap()+--+CREATE FUNCTION hash_metap(IN relname text,+ OUT magic int4,+ OUT version int4,+ OUT ntuples double precision,+ OUT ffactor int2,+ OUT bsize int2,+ OUT bmsize int2,+ OUT bmshift int2,+ OUT maxbucket int4,+ OUT highmask int4,+ OUT lowmask int4,+ OUT ovflpoint int4,+ OUT firstfree int4,+ OUT nmaps int4,+ OUT procid int4,+ OUT spares text,+ OUT mapp text)+AS 'MODULE_PATHNAME', 'hash_metap'+LANGUAGE C STRICT PARALLEL SAFE;++--+-- hash_page_stats()+--+CREATE FUNCTION hash_page_stats(IN relname text, IN blkno int4,+ OUT blkno int4,+ OUT type "char",+ OUT live_items int4,+ OUT dead_items int4,+ OUT avg_item_size int4,+ OUT page_size int4,+ OUT free_size int4,+ OUT hasho_prevblkno int4,+ OUT hasho_nextblkno int4,+ OUT hasho_bucket int4,+ OUT hasho_flag int4,+ OUT hasho_page_id int4)+AS 'MODULE_PATHNAME', 'hash_page_stats'+LANGUAGE C STRICT PARALLEL SAFE;++--+-- hash_page_items()+--+CREATE FUNCTION hash_page_items(IN relname text, IN blkno int4,+ OUT itemoffset smallint,+ OUT ctid tid,+ OUT itemlen smallint,+ OUT nulls bool,+ OUT vars bool,+ OUT data text)+RETURNS SETOF record+AS 'MODULE_PATHNAME', 'hash_page_items'+LANGUAGE C STRICT PARALLEL SAFE;diff --git a/contrib/pageinspect/pageinspect--1.5.sql b/contrib/pageinspect/pageinspect--1.5.sqldeleted file mode 100644index 1e40c3c..0000000--- a/contrib/pageinspect/pageinspect--1.5.sql+++ /dev/null@@ -1,279 +0,0 @@-/* contrib/pageinspect/pageinspect--1.5.sql */---- complain if script is sourced in psql, rather than via CREATE EXTENSION-\echo Use "CREATE EXTENSION pageinspect" to load this file. \quit------- get_raw_page()----CREATE FUNCTION get_raw_page(text, int4)-RETURNS bytea-AS 'MODULE_PATHNAME', 'get_raw_page'-LANGUAGE C STRICT PARALLEL SAFE;--CREATE FUNCTION get_raw_page(text, text, int4)-RETURNS bytea-AS 'MODULE_PATHNAME', 'get_raw_page_fork'-LANGUAGE C STRICT PARALLEL SAFE;------- page_header()----CREATE FUNCTION page_header(IN page bytea,- OUT lsn pg_lsn,- OUT checksum smallint,- OUT flags smallint,- OUT lower smallint,- OUT upper smallint,- OUT special smallint,- OUT pagesize smallint,- OUT version smallint,- OUT prune_xid xid)-AS 'MODULE_PATHNAME', 'page_header'-LANGUAGE C STRICT PARALLEL SAFE;------- heap_page_items()----CREATE FUNCTION heap_page_items(IN page bytea,- OUT lp smallint,- OUT lp_off smallint,- OUT lp_flags smallint,- OUT lp_len smallint,- OUT t_xmin xid,- OUT t_xmax xid,- OUT t_field3 int4,- OUT t_ctid tid,- OUT t_infomask2 integer,- OUT t_infomask integer,- OUT t_hoff smallint,- OUT t_bits text,- OUT t_oid oid,- OUT t_data bytea)-RETURNS SETOF record-AS 'MODULE_PATHNAME', 'heap_page_items'-LANGUAGE C STRICT PARALLEL SAFE;------- tuple_data_split()----CREATE FUNCTION tuple_data_split(rel_oid oid,- t_data bytea,- t_infomask integer,- t_infomask2 integer,- t_bits text)-RETURNS bytea[]-AS 'MODULE_PATHNAME','tuple_data_split'-LANGUAGE C PARALLEL SAFE;--CREATE FUNCTION tuple_data_split(rel_oid oid,- t_data bytea,- t_infomask integer,- t_infomask2 integer,- t_bits text,- do_detoast bool)-RETURNS bytea[]-AS 'MODULE_PATHNAME','tuple_data_split'-LANGUAGE C PARALLEL SAFE;------- heap_page_item_attrs()----CREATE FUNCTION heap_page_item_attrs(- IN page bytea,- IN rel_oid regclass,- IN do_detoast bool,- OUT lp smallint,- OUT lp_off smallint,- OUT lp_flags smallint,- OUT lp_len smallint,- OUT t_xmin xid,- OUT t_xmax xid,- OUT t_field3 int4,- OUT t_ctid tid,- OUT t_infomask2 integer,- OUT t_infomask integer,- OUT t_hoff smallint,- OUT t_bits text,- OUT t_oid oid,- OUT t_attrs bytea[]- )-RETURNS SETOF record AS $$-SELECT lp,- lp_off,- lp_flags,- lp_len,- t_xmin,- t_xmax,- t_field3,- t_ctid,- t_infomask2,- t_infomask,- t_hoff,- t_bits,- t_oid,- tuple_data_split(- rel_oid,- t_data,- t_infomask,- t_infomask2,- t_bits,- do_detoast)- AS t_attrs- FROM heap_page_items(page);-$$ LANGUAGE SQL PARALLEL SAFE;--CREATE FUNCTION heap_page_item_attrs(IN page bytea, IN rel_oid regclass,- OUT lp smallint,- OUT lp_off smallint,- OUT lp_flags smallint,- OUT lp_len smallint,- OUT t_xmin xid,- OUT t_xmax xid,- OUT t_field3 int4,- OUT t_ctid tid,- OUT t_infomask2 integer,- OUT t_infomask integer,- OUT t_hoff smallint,- OUT t_bits text,- OUT t_oid oid,- OUT t_attrs bytea[]- )-RETURNS SETOF record AS $$-SELECT * from heap_page_item_attrs(page, rel_oid, false);-$$ LANGUAGE SQL PARALLEL SAFE;------- bt_metap()----CREATE FUNCTION bt_metap(IN relname text,- OUT magic int4,- OUT version int4,- OUT root int4,- OUT level int4,- OUT fastroot int4,- OUT fastlevel int4)-AS 'MODULE_PATHNAME', 'bt_metap'-LANGUAGE C STRICT PARALLEL SAFE;------- bt_page_stats()----CREATE FUNCTION bt_page_stats(IN relname text, IN blkno int4,- OUT blkno int4,- OUT type "char",- OUT live_items int4,- OUT dead_items int4,- OUT avg_item_size int4,- OUT page_size int4,- OUT free_size int4,- OUT btpo_prev int4,- OUT btpo_next int4,- OUT btpo int4,- OUT btpo_flags int4)-AS 'MODULE_PATHNAME', 'bt_page_stats'-LANGUAGE C STRICT PARALLEL SAFE;------- bt_page_items()----CREATE FUNCTION bt_page_items(IN relname text, IN blkno int4,- OUT itemoffset smallint,- OUT ctid tid,- OUT itemlen smallint,- OUT nulls bool,- OUT vars bool,- OUT data text)-RETURNS SETOF record-AS 'MODULE_PATHNAME', 'bt_page_items'-LANGUAGE C STRICT PARALLEL SAFE;------- brin_page_type()----CREATE FUNCTION brin_page_type(IN page bytea)-RETURNS text-AS 'MODULE_PATHNAME', 'brin_page_type'-LANGUAGE C STRICT PARALLEL SAFE;------- brin_metapage_info()----CREATE FUNCTION brin_metapage_info(IN page bytea, OUT magic text,- OUT version integer, OUT pagesperrange integer, OUT lastrevmappage bigint)-AS 'MODULE_PATHNAME', 'brin_metapage_info'-LANGUAGE C STRICT PARALLEL SAFE;------- brin_revmap_data()----CREATE FUNCTION brin_revmap_data(IN page bytea,- OUT pages tid)-RETURNS SETOF tid-AS 'MODULE_PATHNAME', 'brin_revmap_data'-LANGUAGE C STRICT PARALLEL SAFE;------- brin_page_items()----CREATE FUNCTION brin_page_items(IN page bytea, IN index_oid regclass,- OUT itemoffset int,- OUT blknum int,- OUT attnum int,- OUT allnulls bool,- OUT hasnulls bool,- OUT placeholder bool,- OUT value text)-RETURNS SETOF record-AS 'MODULE_PATHNAME', 'brin_page_items'-LANGUAGE C STRICT PARALLEL SAFE;------- fsm_page_contents()----CREATE FUNCTION fsm_page_contents(IN page bytea)-RETURNS text-AS 'MODULE_PATHNAME', 'fsm_page_contents'-LANGUAGE C STRICT PARALLEL SAFE;------- GIN functions---------- gin_metapage_info()----CREATE FUNCTION gin_metapage_info(IN page bytea,- OUT pending_head bigint,- OUT pending_tail bigint,- OUT tail_free_size int4,- OUT n_pending_pages bigint,- OUT n_pending_tuples bigint,- OUT n_total_pages bigint,- OUT n_entry_pages bigint,- OUT n_data_pages bigint,- OUT n_entries bigint,- OUT version int4)-AS 'MODULE_PATHNAME', 'gin_metapage_info'-LANGUAGE C STRICT PARALLEL SAFE;------- gin_page_opaque_info()----CREATE FUNCTION gin_page_opaque_info(IN page bytea,- OUT rightlink bigint,- OUT maxoff int4,- OUT flags text[])-AS 'MODULE_PATHNAME', 'gin_page_opaque_info'-LANGUAGE C STRICT PARALLEL SAFE;------- gin_leafpage_items()----CREATE FUNCTION gin_leafpage_items(IN page bytea,- OUT first_tid tid,- OUT nbytes int2,- OUT tids tid[])-RETURNS SETOF record-AS 'MODULE_PATHNAME', 'gin_leafpage_items'-LANGUAGE C STRICT PARALLEL SAFE;diff --git a/contrib/pageinspect/pageinspect--1.6.sql b/contrib/pageinspect/pageinspect--1.6.sqlnew file mode 100644index 0000000..76833f0--- /dev/null+++ b/contrib/pageinspect/pageinspect--1.6.sql@@ -0,0 +1,339 @@+/* contrib/pageinspect/pageinspect--1.6.sql */++-- complain if script is sourced in psql, rather than via CREATE EXTENSION+\echo Use "CREATE EXTENSION pageinspect" to load this file. \quit++--+-- get_raw_page()+--+CREATE FUNCTION get_raw_page(text, int4)+RETURNS bytea+AS 'MODULE_PATHNAME', 'get_raw_page'+LANGUAGE C STRICT PARALLEL SAFE;++CREATE FUNCTION get_raw_page(text, text, int4)+RETURNS bytea+AS 'MODULE_PATHNAME', 'get_raw_page_fork'+LANGUAGE C STRICT PARALLEL SAFE;++--+-- page_header()+--+CREATE FUNCTION page_header(IN page bytea,+ OUT lsn pg_lsn,+ OUT checksum smallint,+ OUT flags smallint,+ OUT lower smallint,+ OUT upper smallint,+ OUT special smallint,+ OUT pagesize smallint,+ OUT version smallint,+ OUT prune_xid xid)+AS 'MODULE_PATHNAME', 'page_header'+LANGUAGE C STRICT PARALLEL SAFE;++--+-- heap_page_items()+--+CREATE FUNCTION heap_page_items(IN page bytea,+ OUT lp smallint,+ OUT lp_off smallint,+ OUT lp_flags smallint,+ OUT lp_len smallint,+ OUT t_xmin xid,+ OUT t_xmax xid,+ OUT t_field3 int4,+ OUT t_ctid tid,+ OUT t_infomask2 integer,+ OUT t_infomask integer,+ OUT t_hoff smallint,+ OUT t_bits text,+ OUT t_oid oid,+ OUT t_data bytea)+RETURNS SETOF record+AS 'MODULE_PATHNAME', 'heap_page_items'+LANGUAGE C STRICT PARALLEL SAFE;++--+-- tuple_data_split()+--+CREATE FUNCTION tuple_data_split(rel_oid oid,+ t_data bytea,+ t_infomask integer,+ t_infomask2 integer,+ t_bits text)+RETURNS bytea[]+AS 'MODULE_PATHNAME','tuple_data_split'+LANGUAGE C PARALLEL SAFE;++CREATE FUNCTION tuple_data_split(rel_oid oid,+ t_data bytea,+ t_infomask integer,+ t_infomask2 integer,+ t_bits text,+ do_detoast bool)+RETURNS bytea[]+AS 'MODULE_PATHNAME','tuple_data_split'+LANGUAGE C PARALLEL SAFE;++--+-- heap_page_item_attrs()+--+CREATE FUNCTION heap_page_item_attrs(+ IN page bytea,+ IN rel_oid regclass,+ IN do_detoast bool,+ OUT lp smallint,+ OUT lp_off smallint,+ OUT lp_flags smallint,+ OUT lp_len smallint,+ OUT t_xmin xid,+ OUT t_xmax xid,+ OUT t_field3 int4,+ OUT t_ctid tid,+ OUT t_infomask2 integer,+ OUT t_infomask integer,+ OUT t_hoff smallint,+ OUT t_bits text,+ OUT t_oid oid,+ OUT t_attrs bytea[]+ )+RETURNS SETOF record AS $$+SELECT lp,+ lp_off,+ lp_flags,+ lp_len,+ t_xmin,+ t_xmax,+ t_field3,+ t_ctid,+ t_infomask2,+ t_infomask,+ t_hoff,+ t_bits,+ t_oid,+ tuple_data_split(+ rel_oid,+ t_data,+ t_infomask,+ t_infomask2,+ t_bits,+ do_detoast)+ AS t_attrs+ FROM heap_page_items(page);+$$ LANGUAGE SQL PARALLEL SAFE;++CREATE FUNCTION heap_page_item_attrs(IN page bytea, IN rel_oid regclass,+ OUT lp smallint,+ OUT lp_off smallint,+ OUT lp_flags smallint,+ OUT lp_len smallint,+ OUT t_xmin xid,+ OUT t_xmax xid,+ OUT t_field3 int4,+ OUT t_ctid tid,+ OUT t_infomask2 integer,+ OUT t_infomask integer,+ OUT t_hoff smallint,+ OUT t_bits text,+ OUT t_oid oid,+ OUT t_attrs bytea[]+ )+RETURNS SETOF record AS $$+SELECT * from heap_page_item_attrs(page, rel_oid, false);+$$ LANGUAGE SQL PARALLEL SAFE;++--+-- bt_metap()+--+CREATE FUNCTION bt_metap(IN relname text,+ OUT magic int4,+ OUT version int4,+ OUT root int4,+ OUT level int4,+ OUT fastroot int4,+ OUT fastlevel int4)+AS 'MODULE_PATHNAME', 'bt_metap'+LANGUAGE C STRICT PARALLEL SAFE;++--+-- bt_page_stats()+--+CREATE FUNCTION bt_page_stats(IN relname text, IN blkno int4,+ OUT blkno int4,+ OUT type "char",+ OUT live_items int4,+ OUT dead_items int4,+ OUT avg_item_size int4,+ OUT page_size int4,+ OUT free_size int4,+ OUT btpo_prev int4,+ OUT btpo_next int4,+ OUT btpo int4,+ OUT btpo_flags int4)+AS 'MODULE_PATHNAME', 'bt_page_stats'+LANGUAGE C STRICT PARALLEL SAFE;++--+-- bt_page_items()+--+CREATE FUNCTION bt_page_items(IN relname text, IN blkno int4,+ OUT itemoffset smallint,+ OUT ctid tid,+ OUT itemlen smallint,+ OUT nulls bool,+ OUT vars bool,+ OUT data text)+RETURNS SETOF record+AS 'MODULE_PATHNAME', 'bt_page_items'+LANGUAGE C STRICT PARALLEL SAFE;++--+-- brin_page_type()+--+CREATE FUNCTION brin_page_type(IN page bytea)+RETURNS text+AS 'MODULE_PATHNAME', 'brin_page_type'+LANGUAGE C STRICT PARALLEL SAFE;++--+-- brin_metapage_info()+--+CREATE FUNCTION brin_metapage_info(IN page bytea, OUT magic text,+ OUT version integer, OUT pagesperrange integer, OUT lastrevmappage bigint)+AS 'MODULE_PATHNAME', 'brin_metapage_info'+LANGUAGE C STRICT PARALLEL SAFE;++--+-- brin_revmap_data()+--+CREATE FUNCTION brin_revmap_data(IN page bytea,+ OUT pages tid)+RETURNS SETOF tid+AS 'MODULE_PATHNAME', 'brin_revmap_data'+LANGUAGE C STRICT PARALLEL SAFE;++--+-- brin_page_items()+--+CREATE FUNCTION brin_page_items(IN page bytea, IN index_oid regclass,+ OUT itemoffset int,+ OUT blknum int,+ OUT attnum int,+ OUT allnulls bool,+ OUT hasnulls bool,+ OUT placeholder bool,+ OUT value text)+RETURNS SETOF record+AS 'MODULE_PATHNAME', 'brin_page_items'+LANGUAGE C STRICT PARALLEL SAFE;++--+-- fsm_page_contents()+--+CREATE FUNCTION fsm_page_contents(IN page bytea)+RETURNS text+AS 'MODULE_PATHNAME', 'fsm_page_contents'+LANGUAGE C STRICT PARALLEL SAFE;++--+-- GIN functions+--++--+-- gin_metapage_info()+--+CREATE FUNCTION gin_metapage_info(IN page bytea,+ OUT pending_head bigint,+ OUT pending_tail bigint,+ OUT tail_free_size int4,+ OUT n_pending_pages bigint,+ OUT n_pending_tuples bigint,+ OUT n_total_pages bigint,+ OUT n_entry_pages bigint,+ OUT n_data_pages bigint,+ OUT n_entries bigint,+ OUT version int4)+AS 'MODULE_PATHNAME', 'gin_metapage_info'+LANGUAGE C STRICT PARALLEL SAFE;++--+-- gin_page_opaque_info()+--+CREATE FUNCTION gin_page_opaque_info(IN page bytea,+ OUT rightlink bigint,+ OUT maxoff int4,+ OUT flags text[])+AS 'MODULE_PATHNAME', 'gin_page_opaque_info'+LANGUAGE C STRICT PARALLEL SAFE;++--+-- gin_leafpage_items()+--+CREATE FUNCTION gin_leafpage_items(IN page bytea,+ OUT first_tid tid,+ OUT nbytes int2,+ OUT tids tid[])+RETURNS SETOF record+AS 'MODULE_PATHNAME', 'gin_leafpage_items'+LANGUAGE C STRICT PARALLEL SAFE;++--+-- HASH functions+--++--+-- hash_metap()+--+CREATE FUNCTION hash_metap(IN relname text,+ OUT magic int4,+ OUT version int4,+ OUT ntuples double precision,+ OUT ffactor int2,+ OUT bsize int2,+ OUT bmsize int2,+ OUT bmshift int2,+ OUT maxbucket int4,+ OUT highmask int4,+ OUT lowmask int4,+ OUT ovflpoint int4,+ OUT firstfree int4,+ OUT nmaps int4,+ OUT procid int4,+ OUT spares text,+ OUT mapp text)+AS 'MODULE_PATHNAME', 'hash_metap'+LANGUAGE C STRICT PARALLEL SAFE;++--+-- hash_page_stats()+--+CREATE FUNCTION hash_page_stats(IN relname text, IN blkno int4,+ OUT blkno int4,+ OUT type "char",+ OUT live_items int4,+ OUT dead_items int4,+ OUT avg_item_size int4,+ OUT page_size int4,+ OUT free_size int4,+ OUT hasho_prevblkno int4,+ OUT hasho_nextblkno int4,+ OUT hasho_bucket int4,+ OUT hasho_flag int4,+ OUT hasho_page_id int4)+AS 'MODULE_PATHNAME', 'hash_page_stats'+LANGUAGE C STRICT PARALLEL SAFE;++--+-- hash_page_items()+--+CREATE FUNCTION hash_page_items(IN relname text, IN blkno int4,+ OUT itemoffset smallint,+ OUT ctid tid,+ OUT itemlen smallint,+ OUT nulls bool,+ OUT vars bool,+ OUT data text)+RETURNS SETOF record+AS 'MODULE_PATHNAME', 'hash_page_items'+LANGUAGE C STRICT PARALLEL SAFE;diff --git a/contrib/pageinspect/pageinspect.control b/contrib/pageinspect/pageinspect.controlindex 23c8eff..1a61c9f 100644--- a/contrib/pageinspect/pageinspect.control+++ b/contrib/pageinspect/pageinspect.control@@ -1,5 +1,5 @@
# pageinspect extension
comment = 'inspect the contents of database pages at a low level'
-default_version = '1.5'+default_version = '1.6'
module_pathname = '$libdir/pageinspect'
relocatable = true
diff --git a/doc/src/sgml/pageinspect.sgml b/doc/src/sgml/pageinspect.sgmlindex 5d187ed..836b19e 100644--- a/doc/src/sgml/pageinspect.sgml+++ b/doc/src/sgml/pageinspect.sgml@@ -452,6 +452,106 @@ test=# SELECT first_tid, nbytes, tids[0:5] as some_tids
<varlistentry>
<term>
+ <function>hash_metap(relname text) returns record</function>+ <indexterm>+ <primary>hash_metap</primary>+ </indexterm>+ </term>++ <listitem>+ <para>+ <function>hash_metap</function> returns information about a hash+ index's metapage. For example:+<screen>+test=# SELECT * FROM hash_metap('mytab_index');+-[ RECORD 1 ]-----+magic | 105121344+version | 2+ntuples | 1000000+ffactor | 307+bsize | 8152+bmsize | 4096+bmshift | 15+maxbucket | 4095+highmask | 8191+lowmask | 4095+ovflpoint | 12+firstfree | 0+nmaps | 1+procid | 400+spares | 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0001 0000 ...+mapp | 1001 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 ...+</screen>+ </para>+ </listitem>+ </varlistentry>++ <varlistentry>+ <term>+ <function>hash_page_stats(relname text, blkno int) returns record</function>+ <indexterm>+ <primary>hash_page_stats</primary>+ </indexterm>+ </term>++ <listitem>+ <para>+ <function>hash_page_stats</function> returns summary information about+ single pages of hash indexes. For example:+<screen>+test=# SELECT * FROM hash_page_stats('mytab_index', 1);+-[ RECORD 1 ]-+-----+blkno | 1+type | b+live_items | 211+dead_items | 0+avg_item_size | 16+page_size | 8192+free_size | 3928+hasho_prevblkno | -1+hasho_nextblkno | -1+hasho_bucket | 0+hasho_flag | 2+hasho_page_id | 65408+</screen>+ </para>+ </listitem>+ </varlistentry>++ <varlistentry>+ <term>+ <function>hash_page_items(relname text, blkno int) returns setof record</function>+ <indexterm>+ <primary>hash_page_items</primary>+ </indexterm>+ </term>++ <listitem>+ <para>+ <function>hash_page_items</function> returns detailed information about+ all of the items on a hash index page. For example:+<screen>+test=# SELECT * FROM hash_page_items('mytab_index', 1); + itemoffset | ctid | itemlen | nulls | vars | data +------------+------------+---------+-------+------+-------------------------+ 1 | (2057,114) | 16 | f | f | 00 a0 25 02 00 00 00 00+ 2 | (4033,38) | 16 | f | f | 00 20 8d 02 00 00 00 00+ 3 | (4223,130) | 16 | f | f | 00 80 f7 02 00 00 00 00+ 4 | (1524,126) | 16 | f | f | 00 70 de 04 00 00 00 00+ 5 | (5850,86) | 16 | f | f | 00 70 1a 07 00 00 00 00+...+ 210 | (5541,140) | 16 | f | f | 00 90 a9 ff 00 00 00 00+ 211 | (4654,93) | 16 | f | f | 00 d0 fb ff 00 00 00 00++</screen>+ The <structfield>ctid</> column points to a heap tuple + (BlockIdData,OffsetNumber).+ </para>+ </listitem>+ </varlistentry>++ <varlistentry>+ <term>
<function>fsm_page_contents(page bytea) returns text</function>
<indexterm>
<primary>fsm_page_contents</primary>
--
2.7.4
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: jesper.pedersen@redhat.com
Subject: Re: pageinspect: Hash index support
In-Reply-To: <e2ac6c58-b93f-9dd9-f4e6-d6d30add7fdf@redhat.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox