pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Jesper Pedersen <jesper.pedersen@redhat.com>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: Michael Paquier <michael.paquier@gmail.com>
Cc: Ashutosh Sharma <ashu.coek88@gmail.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: pageinspect: Hash index support
Date: Thu, 29 Sep 2016 12:27:44 -0400
Message-ID: <62ee2eba-3a5b-06d3-e224-cfe00b395be7@redhat.com> (raw )
In-Reply-To: <8a751498-6ee9-b943-abf3-b5641851d7de@2ndquadrant.com >
References: <e2ac6c58-b93f-9dd9-f4e6-d6d30add7fdf@redhat.com >
<CAMkU=1wghadVOxkphJTQUqukgNkE7ETPci0g55Y+fqXO4Q43PQ@mail.gmail.com >
<0f99b6b0-0e54-0f6e-9502-f8cb6f194c17@redhat.com >
<CAB7nPqSLVbe-wXCd77CMx6vHKfRd6thUwv7nzP54Lv9k4Q6hww@mail.gmail.com >
<3493caf5-6d50-8915-7612-bd830972f513@redhat.com >
<CAMkU=1weMVE+Re7NQyAtXT5g6E5Fziw9PVU9UDapb0AG5MH=YQ@mail.gmail.com >
<CAE9k0Pk4dC9VrzV7Vm6em7JFzRMGfzx2=eYu0Yb_Psga-W47Ww@mail.gmail.com >
<e1bda3f7-dc99-c610-4d48-58815f5b7515@redhat.com >
<CAE9k0P=OQSEj0x9z91tvE9P1f-Ht7x7z8X+eDWQGTJNSFbn9yg@mail.gmail.com >
<5d64a536-cc90-ce96-b68f-5b6bd3206476@redhat.com >
<CAB7nPqTT3ZEm8QKsYYxfUnWL+P3p2mAbud7AFiZpK_JKx3eADA@mail.gmail.com >
<d235dcd6-146a-1269-73a0-da9a55bfeddc@redhat.com >
<80a00b5c-56d3-dffb-8dba-edf5d2167b12@2ndquadrant.com >
<16d89e7e-cb1a-cb5d-e944-6f10c324a312@redhat.com >
<c982b01c-06bc-485b-5dde-057ee244261a@2ndquadrant.com >
<836487ee-8d4e-cd49-54ac-f4793ddba5ae@redhat.com >
<8a751498-6ee9-b943-abf3-b5641851d7de@2ndquadrant.com >
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 09/29/2016 11:58 AM, Peter Eisentraut wrote:
> On 9/27/16 10:10 AM, Jesper Pedersen wrote:
>> contrib/pageinspect/pageinspect--1.5--1.6.sql | 59 ++++
>> contrib/pageinspect/pageinspect--1.5.sql | 279 ------------------
>> contrib/pageinspect/pageinspect--1.6.sql | 346 ++++++++++++++++++++++
>
> I think there is still a misunderstanding about these extension files.
> You only need to supply pageinspect--1.5--1.6.sql and leave all the
> other ones alone.
>
Ok.
Left the 'DATA' section in the Makefile, and the control file as is.
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-hackers
Attachments:
0001-pageinspect-Hash-index-support_v8.patch (text/x-patch, 20.3K) download
From b48623beda6aad43116d46ff44de55bdc7547325 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 | 408 ++++++++++++++++++++++++++
contrib/pageinspect/pageinspect--1.5--1.6.sql | 59 ++++
contrib/pageinspect/pageinspect.control | 2 +-
doc/src/sgml/pageinspect.sgml | 146 ++++++++-
5 files changed, 609 insertions(+), 16 deletions(-)
create mode 100644 contrib/pageinspect/hashfuncs.c
create mode 100644 contrib/pageinspect/pageinspect--1.5--1.6.sql
diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile
index 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.c
new file mode 100644
index 0000000..a76b683
--- /dev/null
+++ b/contrib/pageinspect/hashfuncs.c
@@ -0,0 +1,408 @@
+/*
+ * 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 "funcapi.h"
+#include "miscadmin.h"
+#include "utils/builtins.h"
+
+PG_FUNCTION_INFO_V1(hash_metapage_info);
+PG_FUNCTION_INFO_V1(hash_page_items);
+PG_FUNCTION_INFO_V1(hash_page_stats);
+
+/* ------------------------------------------------
+ * structure for single hash page statistics
+ * ------------------------------------------------
+ */
+typedef struct HashPageStat
+{
+ uint32 live_items;
+ uint32 dead_items;
+ uint32 page_size;
+ uint32 free_size;
+ char *type;
+
+ /* opaque data */
+ BlockNumber hasho_prevblkno;
+ BlockNumber hasho_nextblkno;
+ Bucket hasho_bucket;
+ uint16 hasho_flag;
+ uint16 hasho_page_id;
+} HashPageStat;
+
+
+/*
+ * Verify that the given bytea contains a HASH page, or die in the attempt.
+ * A pointer to the page is returned.
+ */
+static Page
+verify_hash_page(bytea *raw_page, bool metap)
+{
+ Page page;
+ int raw_page_size;
+ HashPageOpaque pageopaque;
+
+ raw_page_size = VARSIZE(raw_page) - VARHDRSZ;
+
+ if (raw_page_size != BLCKSZ)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("input page too small"),
+ errdetail("Expected size %d, got %d",
+ BLCKSZ, raw_page_size)));
+
+ page = VARDATA(raw_page);
+ pageopaque = (HashPageOpaque) PageGetSpecialPointer(page);
+ if (pageopaque->hasho_page_id != HASHO_PAGE_ID)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("page is not a HASH page"),
+ errdetail("Expected %08x, got %08x.",
+ HASHO_PAGE_ID, pageopaque->hasho_page_id)));
+
+ if (metap)
+ {
+ if (pageopaque->hasho_flag != LH_META_PAGE)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("page is not a metadata page"),
+ errdetail("Expected %08x, got %08x.",
+ LH_META_PAGE, pageopaque->hasho_flag)));
+ }
+ else
+ {
+ if (pageopaque->hasho_flag == LH_META_PAGE)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("page is a metadata page"),
+ errdetail("Flags %08x.",
+ pageopaque->hasho_flag)));
+ }
+
+ return page;
+}
+
+/* -------------------------------------------------
+ * GetHashPageStatistics()
+ *
+ * Collect statistics of single hash page
+ * -------------------------------------------------
+ */
+static void
+GetHashPageStatistics(Page page, HashPageStat *stat)
+{
+ OffsetNumber maxoff = PageGetMaxOffsetNumber(page);
+ HashPageOpaque opaque = (HashPageOpaque) PageGetSpecialPointer(page);
+ int off;
+
+ stat->dead_items = stat->live_items = 0;
+ stat->page_size = PageGetPageSize(page);
+
+ /* page type (flags) */
+ if (opaque->hasho_flag & LH_META_PAGE)
+ stat->type = "metapage";
+ else if (opaque->hasho_flag & LH_OVERFLOW_PAGE)
+ stat->type = "overflow";
+ else if (opaque->hasho_flag & LH_BUCKET_PAGE)
+ stat->type = "bucket";
+ else if (opaque->hasho_flag & LH_BITMAP_PAGE)
+ stat->type = "bitmap";
+ else
+ stat->type = "unused";
+
+ /* 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++)
+ {
+ ItemId id = PageGetItemId(page, off);
+
+ if (!ItemIdIsDead(id))
+ stat->live_items++;
+ else
+ stat->dead_items++;
+ }
+ stat->free_size = PageGetFreeSpace(page);
+}
+
+/* ---------------------------------------------------
+ * hash_page_stats()
+ *
+ * Usage: SELECT * FROM hash_page_stats(get_raw_page('t1_hash', 1));
+ * ---------------------------------------------------
+ */
+Datum
+hash_page_stats(PG_FUNCTION_ARGS)
+{
+ bytea *raw_page = PG_GETARG_BYTEA_P(0);
+ Page page;
+ int j;
+ Datum values[10];
+ bool nulls[10];
+ HashPageStat stat;
+ HeapTuple tuple;
+ TupleDesc tupleDesc;
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ (errmsg("must be superuser to use pageinspect functions"))));
+
+ page = verify_hash_page(raw_page, false);
+
+ /* keep compiler quiet */
+ stat.hasho_prevblkno = stat.hasho_nextblkno = InvalidBlockNumber;
+ stat.hasho_flag = stat.hasho_page_id = stat.free_size = 0;
+
+ GetHashPageStatistics(page, &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");
+ tupleDesc = BlessTupleDesc(tupleDesc);
+
+ MemSet(nulls, 0, sizeof(nulls));
+
+ j = 0;
+ values[j++] = CStringGetTextDatum(stat.type);
+ values[j++] = UInt32GetDatum(stat.live_items);
+ values[j++] = UInt32GetDatum(stat.dead_items);
+ values[j++] = UInt32GetDatum(stat.page_size);
+ values[j++] = UInt32GetDatum(stat.free_size);
+ values[j++] = UInt32GetDatum(stat.hasho_prevblkno);
+ values[j++] = UInt32GetDatum(stat.hasho_nextblkno);
+ values[j++] = UInt32GetDatum(stat.hasho_bucket);
+ values[j++] = UInt16GetDatum(stat.hasho_flag);
+ values[j++] = UInt16GetDatum(stat.hasho_page_id);
+
+ tuple = heap_form_tuple(tupleDesc, values, nulls);
+
+ PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
+}
+
+/*
+ * cross-call data structure for SRF
+ */
+struct user_args
+{
+ Page page;
+ OffsetNumber offset;
+};
+
+/*-------------------------------------------------------
+ * hash_page_items()
+ *
+ * Get IndexTupleData set in a hash page
+ *
+ * Usage: SELECT * FROM hash_page_items(get_raw_page('t1_hash', 1));
+ *-------------------------------------------------------
+ */
+Datum
+hash_page_items(PG_FUNCTION_ARGS)
+{
+ bytea *raw_page = PG_GETARG_BYTEA_P(0);
+ Page page;
+ Datum result;
+ Datum values[3];
+ bool nulls[3];
+ 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())
+ {
+ TupleDesc tupleDesc;
+
+ fctx = SRF_FIRSTCALL_INIT();
+
+ page = verify_hash_page(raw_page, false);
+
+ /*
+ * 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, page, BLCKSZ);
+
+ 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");
+ tupleDesc = BlessTupleDesc(tupleDesc);
+
+ 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 *s;
+ char *ptr;
+
+ id = PageGetItemId(uargs->page, uargs->offset);
+
+ if (!ItemIdIsValid(id))
+ elog(ERROR, "invalid ItemId");
+
+ itup = (IndexTuple) PageGetItem(uargs->page, id);
+
+ MemSet(nulls, 0, sizeof(nulls));
+
+ j = 0;
+ values[j++] = UInt16GetDatum(uargs->offset);
+ values[j++] = CStringGetTextDatum(psprintf("(%u,%u)",
+ BlockIdGetBlockNumber(&(itup->t_tid.ip_blkid)),
+ itup->t_tid.ip_posid));
+
+ ptr = (char *) itup + IndexInfoFindDataOffset(itup->t_info);
+ dlen = IndexTupleSize(itup) - IndexInfoFindDataOffset(itup->t_info);
+ dump = palloc0(dlen * 3 + 1);
+ s = dump;
+ for (off = 0; off < dlen; off++)
+ {
+ if (off > 0)
+ *dump++ = ' ';
+ sprintf(dump, "%02x", *(ptr + off) & 0xff);
+ dump += 2;
+ }
+ values[j] = CStringGetTextDatum(s);
+
+ tuple = heap_form_tuple(fctx->attinmeta->tupdesc, values, nulls);
+ result = HeapTupleGetDatum(tuple);
+
+ uargs->offset = uargs->offset + 1;
+
+ SRF_RETURN_NEXT(fctx, result);
+ }
+ else
+ {
+ pfree(uargs->page);
+ pfree(uargs);
+ SRF_RETURN_DONE(fctx);
+ }
+}
+
+/* ------------------------------------------------
+ * hash_metapage_info()
+ *
+ * Get a hash's meta-page information
+ *
+ * Usage: SELECT * FROM hash_metapage_info(get_raw_page('t1_hash', 0))
+ * ------------------------------------------------
+ */
+Datum
+hash_metapage_info(PG_FUNCTION_ARGS)
+{
+ bytea *raw_page = PG_GETARG_BYTEA_P(0);
+ Page page;
+ HashMetaPageData *metad;
+ TupleDesc tupleDesc;
+ HeapTuple tuple;
+ int i,j;
+ Datum values[16];
+ bool nulls[16];
+ char *spares;
+ char *mapp;
+ char *s;
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ (errmsg("must be superuser to use pageinspect functions"))));
+
+ page = verify_hash_page(raw_page, true);
+
+ /* 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");
+ tupleDesc = BlessTupleDesc(tupleDesc);
+
+ metad = HashPageGetMeta(page);
+
+ MemSet(nulls, 0, sizeof(nulls));
+
+ j = 0;
+ values[j++] = CStringGetTextDatum(psprintf("0x%08X", metad->hashm_magic));
+ values[j++] = UInt32GetDatum(metad->hashm_version);
+ values[j++] = Float8GetDatum(metad->hashm_ntuples);
+ values[j++] = UInt16GetDatum(metad->hashm_ffactor);
+ values[j++] = UInt16GetDatum(metad->hashm_bsize);
+ values[j++] = UInt16GetDatum(metad->hashm_bmsize);
+ values[j++] = UInt16GetDatum(metad->hashm_bmshift);
+ values[j++] = UInt32GetDatum(metad->hashm_maxbucket);
+ values[j++] = UInt32GetDatum(metad->hashm_highmask);
+ values[j++] = UInt32GetDatum(metad->hashm_lowmask);
+ values[j++] = UInt32GetDatum(metad->hashm_ovflpoint);
+ values[j++] = UInt32GetDatum(metad->hashm_firstfree);
+ values[j++] = UInt32GetDatum(metad->hashm_nmaps);
+ values[j++] = UInt16GetDatum(metad->hashm_procid);
+
+ spares = palloc0(HASH_MAX_SPLITPOINTS * 5 + 1);
+ s = spares;
+ for (i = 0; i < HASH_MAX_SPLITPOINTS; i++)
+ {
+ if (i > 0)
+ *spares++ = ' ';
+
+ sprintf(spares, "%04x", metad->hashm_spares[i]);
+ spares += 4;
+ }
+ values[j++] = CStringGetTextDatum(s);
+
+ mapp = palloc0(HASH_MAX_BITMAPS * 5 + 1);
+ s = mapp;
+ for (i = 0; i < HASH_MAX_BITMAPS; i++)
+ {
+ if (i > 0)
+ *mapp++ = ' ';
+
+ sprintf(mapp, "%04x", metad->hashm_mapp[i]);
+ mapp += 4;
+ }
+ values[j++] = CStringGetTextDatum(s);
+
+ tuple = heap_form_tuple(tupleDesc, values, nulls);
+
+ PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
+}
diff --git a/contrib/pageinspect/pageinspect--1.5--1.6.sql b/contrib/pageinspect/pageinspect--1.5--1.6.sql
new file mode 100644
index 0000000..7f8c2d2
--- /dev/null
+++ b/contrib/pageinspect/pageinspect--1.5--1.6.sql
@@ -0,0 +1,59 @@
+/* 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_metapage_info()
+--
+CREATE FUNCTION hash_metapage_info(IN page bytea,
+ OUT magic text,
+ 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_metapage_info'
+LANGUAGE C STRICT PARALLEL SAFE;
+
+--
+-- hash_page_stats()
+--
+CREATE FUNCTION hash_page_stats(IN page bytea,
+ OUT type text,
+ OUT live_items int4,
+ OUT dead_items 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 page bytea,
+ OUT itemoffset smallint,
+ OUT ctid tid,
+ 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.control
index 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.sgml
index 5d187ed..626feba 100644
--- a/doc/src/sgml/pageinspect.sgml
+++ b/doc/src/sgml/pageinspect.sgml
@@ -14,7 +14,7 @@
</para>
<sect2>
- <title>Functions</title>
+ <title>General functions</title>
<variablelist>
<varlistentry>
@@ -160,7 +160,36 @@ test=# SELECT * FROM heap_page_item_attrs(get_raw_page('pg_class', 0), 'pg_class
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term>
+ <function>fsm_page_contents(page bytea) returns text</function>
+ <indexterm>
+ <primary>fsm_page_contents</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ <function>fsm_page_contents</function> shows the internal node structure
+ of a FSM page. The output is a multiline string, with one line per
+ node in the binary tree within the page. Only those nodes that are not
+ zero are printed. The so-called "next" pointer, which points to the
+ next slot to be returned from the page, is also printed.
+ </para>
+ <para>
+ See <filename>src/backend/storage/freespace/README</> for more
+ information on the structure of an FSM page.
+ </para>
+ </listitem>
+ </varlistentry>
+</variablelist>
+</sect2>
+ <sect2>
+ <title>B-tree functions</title>
+
+ <variablelist>
<varlistentry>
<term>
<function>bt_metap(relname text) returns record</function>
@@ -261,7 +290,13 @@ test=# SELECT * FROM bt_page_items('pg_cast_oid_index', 1);
</para>
</listitem>
</varlistentry>
+ </variablelist>
+ </sect2>
+
+ <sect2>
+ <title>BRIN functions</title>
+ <variablelist>
<varlistentry>
<term>
<function>brin_page_type(page bytea) returns text</function>
@@ -365,7 +400,13 @@ test=# SELECT * FROM brin_page_items(get_raw_page('brinidx', 5),
</para>
</listitem>
</varlistentry>
+ </variablelist>
+ </sect2>
+
+ <sect2>
+ <title>GIN functions</title>
+ <variablelist>
<varlistentry>
<term>
<function>gin_metapage_info(page bytea) returns record</function>
@@ -449,26 +490,111 @@ test=# SELECT first_tid, nbytes, tids[0:5] as some_tids
</para>
</listitem>
</varlistentry>
+ </variablelist>
+ </sect2>
+
+ <sect2>
+ <title>Hash functions</title>
+ <para>
+ The hash index data structures are defined in <filename>src/include/access/hash.h</>.
+ </para>
+
+ <variablelist>
<varlistentry>
<term>
- <function>fsm_page_contents(page bytea) returns text</function>
+ <function>hash_metapage_info(page bytes) returns record</function>
<indexterm>
- <primary>fsm_page_contents</primary>
+ <primary>hash_metapage_info</primary>
</indexterm>
</term>
<listitem>
<para>
- <function>fsm_page_contents</function> shows the internal node structure
- of a FSM page. The output is a multiline string, with one line per
- node in the binary tree within the page. Only those nodes that are not
- zero are printed. The so-called "next" pointer, which points to the
- next slot to be returned from the page, is also printed.
+ <function>hash_metapage_info</function> returns information about a hash
+ index's metapage. For example:
+<screen>
+test=# SELECT * FROM hash_metapage_info(get_raw_page('mytab_index', 0));
+-[ RECORD 1 ]-----
+magic | 0x06440640
+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(page bytea) returns record</function>
+ <indexterm>
+ <primary>hash_page_stats</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
<para>
- See <filename>src/backend/storage/freespace/README</> for more
- information on the structure of an FSM page.
+ <function>hash_page_stats</function> returns summary information about
+ single pages of hash indexes. For example:
+<screen>
+test=# SELECT * FROM hash_page_stats(get_raw_page('mytab_index', 1));
+-[ RECORD 1 ]---+-----
+type | bucket
+live_items | 211
+dead_items | 0
+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(page bytea) 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(get_raw_page('mytab_index', 1));
+ itemoffset | ctid | data
+------------+------------+-------------------------
+ 1 | (2057,114) | 00 a0 25 02 00 00 00 00
+ 2 | (4033,38) | 00 20 8d 02 00 00 00 00
+ 3 | (4223,130) | 00 80 f7 02 00 00 00 00
+ 4 | (1524,126) | 00 70 de 04 00 00 00 00
+ 5 | (5850,86) | 00 70 1a 07 00 00 00 00
+...
+ 210 | (5541,140) | 00 90 a9 ff 00 00 00 00
+ 211 | (4654,93) | 00 d0 fb ff 00 00 00 00
+
+</screen>
+ The <structfield>ctid</> column points to a heap tuple
+ (BlockIdData,OffsetNumber).
</para>
</listitem>
</varlistentry>
--
2.7.4
view thread (110+ messages) latest in thread
Message-ID: <62ee2eba-3a5b-06d3-e224-cfe00b395be7@redhat.com>
Permalink: ../62ee2eba-3a5b-06d3-e224-cfe00b395be7@redhat.com/
Also on: postgresql.org/message-id/62ee2eba-3a5b-06d3-e224-cfe00b395be7@redhat.com
copy link · copy postgr.es · copy git am
reply 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, peter.eisentraut@2ndquadrant.com, michael.paquier@gmail.com, ashu.coek88@gmail.com, jeff.janes@gmail.com, alvherre@2ndquadrant.com
Subject: Re: pageinspect: Hash index support
In-Reply-To: <62ee2eba-3a5b-06d3-e224-cfe00b395be7@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