Allow pageinspect's bt_page_stats function to return a set of rows instead of a single row
Hello Hackers,
While working on one of my blogs on the B-Tree indexes
<https://www.percona.com/blog/postgresql-14-b-tree-index-reduced-bloat-with-bottom-up-deletion/>,
I needed to look at a range of B-Tree page statistics. So the goto solution
was to use pageinspect. However, reviewing stats for multiple pages meant
issuing multiple queries. I felt that there's an opportunity for
improvement in the extension by extending the API to output the statistics
for multiple pages with a single query.
That attached patch is based on the master branch. It makes the following
changes to the pageinspect contrib module:
- Updates bt_page_stats_internal function to accept 3 arguments instead of
2.
- The function now uses SRF macros to return a set rather than a single
row. The function call now requires specifying column names.
The extension version is bumped to 1.11 (PAGEINSPECT_V1_11).
To maintain backward compatibility, for versions below 1.11, the multi-call
mechanism is ended to keep the old behavior consistent.
Regression test cases for the module are updated as well as part of this
change. Here is a subset of queries that are added to the btree.sql test
case file for pageinspect.
----
CREATE TABLE test2 AS (SELECT generate_series(1, 5000) AS col1);
CREATE INDEX test2_col1_idx ON test2(col1);
SELECT * FROM bt_page_stats('test2_col1_idx', 1, 2);
SELECT * FROM bt_page_stats('test2_col1_idx', 1, 0);
SELECT * FROM bt_page_stats('test2_col1_idx', 0, 1);
SELECT * FROM bt_page_stats('test2_col1_idx', 1, -1);
DROP TABLE test2;
----
Regards.
--
Hamid Akhtar,
Percona LLC,
URL : www.percona.com
CELL:+923335449950
SKYPE: engineeredvirus
Attachments:
pageinspect_btree_pagestats_01.patchapplication/x-patch; name=pageinspect_btree_pagestats_01.patchDownload
diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile
index 5c0736564a..31d7b1c581 100644
--- a/contrib/pageinspect/Makefile
+++ b/contrib/pageinspect/Makefile
@@ -13,12 +13,12 @@ OBJS = \
rawpage.o
EXTENSION = pageinspect
-DATA = pageinspect--1.9--1.10.sql pageinspect--1.8--1.9.sql \
- pageinspect--1.7--1.8.sql pageinspect--1.6--1.7.sql \
- pageinspect--1.5.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
+DATA = pageinspect--1.10--1.11.sql pageinspect--1.9--1.10.sql \
+ pageinspect--1.8--1.9.sql pageinspect--1.7--1.8.sql \
+ pageinspect--1.6--1.7.sql pageinspect--1.5.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
PGFILEDESC = "pageinspect - functions to inspect contents of database pages"
REGRESS = page btree brin gin gist hash checksum oldextversions
diff --git a/contrib/pageinspect/btreefuncs.c b/contrib/pageinspect/btreefuncs.c
index 62f2c1b315..85127fcfdb 100644
--- a/contrib/pageinspect/btreefuncs.c
+++ b/contrib/pageinspect/btreefuncs.c
@@ -45,6 +45,7 @@ PG_FUNCTION_INFO_V1(bt_page_items_1_9);
PG_FUNCTION_INFO_V1(bt_page_items);
PG_FUNCTION_INFO_V1(bt_page_items_bytea);
PG_FUNCTION_INFO_V1(bt_page_stats_1_9);
+PG_FUNCTION_INFO_V1(bt_page_stats_1_11);
PG_FUNCTION_INFO_V1(bt_page_stats);
#define IS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)
@@ -80,6 +81,26 @@ typedef struct BTPageStat
BTCycleId btpo_cycleid;
} BTPageStat;
+/*
+ * cross-call data structure for SRF for page stats
+ */
+typedef struct ua_page_stats
+{
+ uint64 blkno;
+ uint64 blk_count;
+} ua_page_stats;
+
+/*
+ * cross-call data structure for SRF for page items
+ */
+typedef struct ua_page_items
+{
+ Page page;
+ OffsetNumber offset;
+ bool leafpage;
+ bool rightmost;
+ TupleDesc tupd;
+} ua_page_items;
/* -------------------------------------------------
* GetBTPageStatistics()
@@ -176,35 +197,17 @@ GetBTPageStatistics(BlockNumber blkno, Buffer buffer, BTPageStat *stat)
stat->avg_item_size = 0;
}
+
/* -----------------------------------------------
- * bt_page_stats()
+ * bt_index_block_validate()
*
- * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1);
+ * Validate index type is btree and block number
+ * is within a valid block number range.
* -----------------------------------------------
*/
-static Datum
-bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
+static void
+bt_index_block_validate(Relation rel, int64 blkno)
{
- text *relname = PG_GETARG_TEXT_PP(0);
- int64 blkno = (ext_version == PAGEINSPECT_V1_8 ? PG_GETARG_UINT32(1) : PG_GETARG_INT64(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))
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -232,42 +235,150 @@ bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
errmsg("block 0 is a meta page")));
CHECK_RELATION_BLOCK_RANGE(rel, blkno);
+}
- buffer = ReadBuffer(rel, blkno);
- LockBuffer(buffer, BUFFER_LOCK_SHARE);
+/* -----------------------------------------------
+ * bt_page_stats()
+ *
+ * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1, 1);
+ * Where the first argument is the name followed
+ * by block number an d number of blocks to be
+ * returned.
+ * -----------------------------------------------
+ */
+static Datum
+bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
+{
+ text *relname = PG_GETARG_TEXT_PP(0);
+ int64 blkno = (ext_version == PAGEINSPECT_V1_8 ? PG_GETARG_UINT32(1) : PG_GETARG_INT64(1));
+ int64 blk_count = (ext_version >= PAGEINSPECT_V1_11 ? PG_GETARG_INT64(2) : 1);
+ RangeVar *relrv;
+ Relation rel;
+ ua_page_stats *uargs;
+ FuncCallContext *fctx;
+ MemoryContext mctx;
- /* keep compiler quiet */
- stat.btpo_prev = stat.btpo_next = InvalidBlockNumber;
- stat.btpo_flags = stat.free_size = stat.avg_item_size = 0;
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to use pageinspect functions")));
- GetBTPageStatistics(blkno, buffer, &stat);
+ if (SRF_IS_FIRSTCALL())
+ {
+ fctx = SRF_FIRSTCALL_INIT();
- UnlockReleaseBuffer(buffer);
- relation_close(rel, AccessShareLock);
+ relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
+ rel = relation_openrv(relrv, 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");
+ /*
+ * Check if relaion is a valid btree index and block number is within
+ * the valid range.
+ */
+ bt_index_block_validate(rel, blkno);
- j = 0;
- values[j++] = psprintf("%u", stat.blkno);
- values[j++] = psprintf("%c", stat.type);
- values[j++] = psprintf("%u", stat.live_items);
- values[j++] = psprintf("%u", stat.dead_items);
- values[j++] = psprintf("%u", stat.avg_item_size);
- values[j++] = psprintf("%u", stat.page_size);
- values[j++] = psprintf("%u", stat.free_size);
- values[j++] = psprintf("%u", stat.btpo_prev);
- values[j++] = psprintf("%u", stat.btpo_next);
- values[j++] = psprintf("%u", stat.btpo_level);
- values[j++] = psprintf("%d", stat.btpo_flags);
+ relation_close(rel, AccessShareLock);
- tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
- values);
+ mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
- result = HeapTupleGetDatum(tuple);
+ uargs = palloc(sizeof(struct ua_page_stats));
- PG_RETURN_DATUM(result);
+ /*
+ * Set the start block number and the total number of blocks to ne
+ * retrieved.
+ */
+ uargs->blkno = blkno;
+ uargs->blk_count = blk_count;
+
+ fctx->user_fctx = uargs;
+
+ MemoryContextSwitchTo(mctx);
+ }
+
+ fctx = SRF_PERCALL_SETUP();
+ uargs = fctx->user_fctx;
+
+ /* We need to fetch next block statistics */
+ if (uargs->blk_count > 0)
+ {
+ Buffer buffer;
+ Datum result;
+ HeapTuple tuple;
+ int j;
+ char *values[11];
+ BTPageStat stat;
+ TupleDesc tupleDesc;
+
+ relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
+ rel = relation_openrv(relrv, AccessShareLock);
+
+ /*
+ * Since index may have changed between function calls, double check
+ * that we are still within the vaild range.
+ */
+ CHECK_RELATION_BLOCK_RANGE(rel, uargs->blkno);
+
+ buffer = ReadBuffer(rel, uargs->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(uargs->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("%u", stat.blkno);
+ values[j++] = psprintf("%c", stat.type);
+ values[j++] = psprintf("%u", stat.live_items);
+ values[j++] = psprintf("%u", stat.dead_items);
+ values[j++] = psprintf("%u", stat.avg_item_size);
+ values[j++] = psprintf("%u", stat.page_size);
+ values[j++] = psprintf("%u", stat.free_size);
+ values[j++] = psprintf("%u", stat.btpo_prev);
+ values[j++] = psprintf("%u", stat.btpo_next);
+ values[j++] = psprintf("%u", stat.btpo_level);
+ values[j++] = psprintf("%d", stat.btpo_flags);
+
+ /* Constructu tuple to be returned */
+ tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
+ values);
+
+ result = HeapTupleGetDatum(tuple);
+
+ /*
+ * Move to the next block number and decrement the number of blocks
+ * still to be fetched
+ */
+ uargs->blkno++;
+ uargs->blk_count--;
+
+ /*
+ * If the version is less than 1.11, let's end multi-function call and
+ * simply return the result.
+ */
+ if (ext_version < PAGEINSPECT_V1_11)
+ {
+ end_MultiFuncCall(fcinfo, fctx);
+ PG_RETURN_DATUM(result);
+ }
+
+ SRF_RETURN_NEXT(fctx, result);
+ }
+
+ SRF_RETURN_DONE(fctx);
+}
+
+Datum
+bt_page_stats_1_11(PG_FUNCTION_ARGS)
+{
+ return bt_page_stats_internal(fcinfo, PAGEINSPECT_V1_11);
}
Datum
@@ -283,19 +394,6 @@ bt_page_stats(PG_FUNCTION_ARGS)
return bt_page_stats_internal(fcinfo, PAGEINSPECT_V1_8);
}
-
-/*
- * cross-call data structure for SRF
- */
-struct user_args
-{
- Page page;
- OffsetNumber offset;
- bool leafpage;
- bool rightmost;
- TupleDesc tupd;
-};
-
/*-------------------------------------------------------
* bt_page_print_tuples()
*
@@ -303,7 +401,7 @@ struct user_args
* ------------------------------------------------------
*/
static Datum
-bt_page_print_tuples(struct user_args *uargs)
+bt_page_print_tuples(struct ua_page_items *uargs)
{
Page page = uargs->page;
OffsetNumber offset = uargs->offset;
@@ -457,7 +555,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
Datum result;
FuncCallContext *fctx;
MemoryContext mctx;
- struct user_args *uargs;
+ struct ua_page_items *uargs;
if (!superuser())
ereport(ERROR,
@@ -477,33 +575,11 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
rel = relation_openrv(relrv, AccessShareLock);
- if (!IS_INDEX(rel) || !IS_BTREE(rel))
- ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is not a %s index",
- RelationGetRelationName(rel), "btree")));
-
/*
- * 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.
+ * Check if relaion is a valid btree index and block number is within
+ * the valid range.
*/
- if (RELATION_IS_OTHER_TEMP(rel))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot access temporary tables of other sessions")));
-
- if (blkno < 0 || blkno > MaxBlockNumber)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("invalid block number")));
-
- if (blkno == 0)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("block 0 is a meta page")));
-
- CHECK_RELATION_BLOCK_RANGE(rel, blkno);
+ bt_index_block_validate(rel, blkno);
buffer = ReadBuffer(rel, blkno);
LockBuffer(buffer, BUFFER_LOCK_SHARE);
@@ -515,7 +591,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
*/
mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
- uargs = palloc(sizeof(struct user_args));
+ uargs = palloc(sizeof(struct ua_page_items));
uargs->page = palloc(BLCKSZ);
memcpy(uargs->page, BufferGetPage(buffer), BLCKSZ);
@@ -591,7 +667,7 @@ bt_page_items_bytea(PG_FUNCTION_ARGS)
bytea *raw_page = PG_GETARG_BYTEA_P(0);
Datum result;
FuncCallContext *fctx;
- struct user_args *uargs;
+ struct ua_page_items *uargs;
if (!superuser())
ereport(ERROR,
@@ -607,7 +683,7 @@ bt_page_items_bytea(PG_FUNCTION_ARGS)
fctx = SRF_FIRSTCALL_INIT();
mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
- uargs = palloc(sizeof(struct user_args));
+ uargs = palloc(sizeof(struct ua_page_items));
uargs->page = get_page_from_raw(raw_page);
diff --git a/contrib/pageinspect/expected/btree.out b/contrib/pageinspect/expected/btree.out
index 035a81a759..9d245485e6 100644
--- a/contrib/pageinspect/expected/btree.out
+++ b/contrib/pageinspect/expected/btree.out
@@ -34,6 +34,118 @@ btpo_flags | 3
SELECT * FROM bt_page_stats('test1_a_idx', 2);
ERROR: block number out of range
+-- bt_page_stats() function is overloaded with an option to return a set of
+-- records of page statistics.
+CREATE TABLE test2 AS (SELECT generate_series(1, 5000) AS col1);
+CREATE INDEX test2_col1_idx ON test2(col1);
+SELECT * FROM bt_page_stats('test2_col1_idx', 0, 1);
+ERROR: block 0 is a meta page
+SELECT * FROM bt_page_stats('test2_col1_idx', 1, -1);
+ERROR: block number out of range
+SELECT * FROM bt_page_stats('test2_col1_idx', 1, 0);
+(0 rows)
+
+SELECT * FROM bt_page_stats('test2_col1_idx', 1, 2);
+-[ RECORD 1 ]-+-----
+blkno | 1
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 0
+btpo_next | 2
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 2 ]-+-----
+blkno | 2
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 1
+btpo_next | 4
+btpo_level | 0
+btpo_flags | 1
+
+SELECT * FROM bt_page_stats('test2_col1_idx', 2, 6);
+-[ RECORD 1 ]-+-----
+blkno | 2
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 1
+btpo_next | 4
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 2 ]-+-----
+blkno | 3
+type | r
+live_items | 14
+dead_items | 0
+avg_item_size | 15
+page_size | 8192
+free_size | 7876
+btpo_prev | 0
+btpo_next | 0
+btpo_level | 1
+btpo_flags | 2
+-[ RECORD 3 ]-+-----
+blkno | 4
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 2
+btpo_next | 5
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 4 ]-+-----
+blkno | 5
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 4
+btpo_next | 6
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 5 ]-+-----
+blkno | 6
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 5
+btpo_next | 7
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 6 ]-+-----
+blkno | 7
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 6
+btpo_next | 8
+btpo_level | 0
+btpo_flags | 1
+
+DROP TABLE test2;
SELECT * FROM bt_page_items('test1_a_idx', -1);
ERROR: invalid block number
SELECT * FROM bt_page_items('test1_a_idx', 0);
@@ -74,7 +186,7 @@ ERROR: block number 2 is out of range for relation "test1_a_idx"
CREATE INDEX test1_a_hash ON test1 USING hash(a);
SELECT bt_metap('test1_a_hash');
ERROR: "test1_a_hash" is not a btree index
-SELECT bt_page_stats('test1_a_hash', 0);
+SELECT * FROM bt_page_stats('test1_a_hash', 0);
ERROR: "test1_a_hash" is not a btree index
SELECT bt_page_items('test1_a_hash', 0);
ERROR: "test1_a_hash" is not a btree index
diff --git a/contrib/pageinspect/pageinspect--1.10--1.11.sql b/contrib/pageinspect/pageinspect--1.10--1.11.sql
new file mode 100644
index 0000000000..bfc9266002
--- /dev/null
+++ b/contrib/pageinspect/pageinspect--1.10--1.11.sql
@@ -0,0 +1,23 @@
+/* contrib/pageinspect/pageinspect--1.10--1.11.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pageinspect UPDATE TO '1.11'" to load this file. \quit
+
+--
+-- bt_page_stats()
+--
+CREATE FUNCTION bt_page_stats(IN relname text, IN blkno int8, IN blk_count int8,
+ OUT blkno int8,
+ 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 int8,
+ OUT btpo_next int8,
+ OUT btpo_level int8,
+ OUT btpo_flags int4)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'bt_page_stats_1_11'
+LANGUAGE C STRICT PARALLEL SAFE;
diff --git a/contrib/pageinspect/pageinspect.control b/contrib/pageinspect/pageinspect.control
index 7cdf37913d..f277413dd8 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.10'
+default_version = '1.11'
module_pathname = '$libdir/pageinspect'
relocatable = true
diff --git a/contrib/pageinspect/pageinspect.h b/contrib/pageinspect/pageinspect.h
index 69b1e0a8a2..cdaa5e2c3f 100644
--- a/contrib/pageinspect/pageinspect.h
+++ b/contrib/pageinspect/pageinspect.h
@@ -22,6 +22,7 @@ enum pageinspect_version
{
PAGEINSPECT_V1_8,
PAGEINSPECT_V1_9,
+ PAGEINSPECT_V1_11,
};
/* in rawpage.c */
diff --git a/contrib/pageinspect/sql/btree.sql b/contrib/pageinspect/sql/btree.sql
index 1f554f0f67..cbb78aaeb6 100644
--- a/contrib/pageinspect/sql/btree.sql
+++ b/contrib/pageinspect/sql/btree.sql
@@ -11,6 +11,17 @@ SELECT * FROM bt_page_stats('test1_a_idx', 0);
SELECT * FROM bt_page_stats('test1_a_idx', 1);
SELECT * FROM bt_page_stats('test1_a_idx', 2);
+-- bt_page_stats() function is overloaded with an option to return a set of
+-- records of page statistics.
+CREATE TABLE test2 AS (SELECT generate_series(1, 5000) AS col1);
+CREATE INDEX test2_col1_idx ON test2(col1);
+SELECT * FROM bt_page_stats('test2_col1_idx', 0, 1);
+SELECT * FROM bt_page_stats('test2_col1_idx', 1, -1);
+SELECT * FROM bt_page_stats('test2_col1_idx', 1, 0);
+SELECT * FROM bt_page_stats('test2_col1_idx', 1, 2);
+SELECT * FROM bt_page_stats('test2_col1_idx', 2, 6);
+DROP TABLE test2;
+
SELECT * FROM bt_page_items('test1_a_idx', -1);
SELECT * FROM bt_page_items('test1_a_idx', 0);
SELECT * FROM bt_page_items('test1_a_idx', 1);
@@ -24,7 +35,7 @@ SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 2));
-- Failure when using a non-btree index.
CREATE INDEX test1_a_hash ON test1 USING hash(a);
SELECT bt_metap('test1_a_hash');
-SELECT bt_page_stats('test1_a_hash', 0);
+SELECT * FROM bt_page_stats('test1_a_hash', 0);
SELECT bt_page_items('test1_a_hash', 0);
SELECT bt_page_items(get_raw_page('test1_a_hash', 0));
CREATE INDEX test1_b_gist ON test1 USING gist(b);
Hi,
On 6/27/22 9:31 AM, Hamid Akhtar wrote:
Hello Hackers,
While working on one of my blogs on the B-Tree indexes
<https://www.percona.com/blog/postgresql-14-b-tree-index-reduced-bloat-with-bottom-up-deletion/>,
I needed to look at a range of B-Tree page statistics. So the goto
solution was to use pageinspect. However, reviewing stats for multiple
pages meant issuing multiple queries.
FWIW, I think you could also rely on generate_series()
I felt that there's an opportunity for improvement in the extension by
extending the API to output the statistics for multiple pages with a
single query.That attached patch is based on the master branch. It makes the
following changes to the pageinspect contrib module:
- Updates bt_page_stats_internal function to accept 3 arguments
instead of 2.
- The function now uses SRF macros to return a set rather than a
single row. The function call now requires specifying column names.The extension version is bumped to 1.11 (PAGEINSPECT_V1_11).
To maintain backward compatibility, for versions below 1.11, the
multi-call mechanism is ended to keep the old behavior consistent.Regression test cases for the module are updated as well as part of
this change. Here is a subset of queries that are added to the
btree.sql test case file for pageinspect.----
CREATE TABLE test2 AS (SELECT generate_series(1, 5000) AS col1);
CREATE INDEX test2_col1_idx ON test2(col1);
SELECT * FROM bt_page_stats('test2_col1_idx', 1, 2);
For example, this could be written as:
select * from
generate_series(1, 2) blkno ,
bt_page_stats('test2_col1_idx',blkno::int);
Or, if one wants to inspect to whole relation, something like:
select * from
generate_series(1, pg_relation_size('test2_col1_idx'::regclass::text) /
8192 - 1) blkno ,
bt_page_stats('test2_col1_idx',blkno::int);
Regards,
Bertrand
On Mon, Jun 27, 2022 at 1:40 PM Drouvot, Bertrand <bdrouvot@amazon.com> wrote:
Hi,
On 6/27/22 9:31 AM, Hamid Akhtar wrote:
Hello Hackers,
While working on one of my blogs on the B-Tree indexes, I needed to look at a range of B-Tree page statistics. So the goto solution was to use pageinspect. However, reviewing stats for multiple pages meant issuing multiple queries.
+1 to improve the API.
I felt that there's an opportunity for improvement in the extension by extending the API to output the statistics for multiple pages with a single query.
That attached patch is based on the master branch. It makes the following changes to the pageinspect contrib module:
- Updates bt_page_stats_internal function to accept 3 arguments instead of 2.
- The function now uses SRF macros to return a set rather than a single row. The function call now requires specifying column names.The extension version is bumped to 1.11 (PAGEINSPECT_V1_11).
To maintain backward compatibility, for versions below 1.11, the multi-call mechanism is ended to keep the old behavior consistent.Regression test cases for the module are updated as well as part of this change. Here is a subset of queries that are added to the btree.sql test case file for pageinspect.
----
CREATE TABLE test2 AS (SELECT generate_series(1, 5000) AS col1);
CREATE INDEX test2_col1_idx ON test2(col1);
SELECT * FROM bt_page_stats('test2_col1_idx', 1, 2);For example, this could be written as:
select * from
generate_series(1, 2) blkno ,
bt_page_stats('test2_col1_idx',blkno::int);Or, if one wants to inspect to whole relation, something like:
select * from
generate_series(1, pg_relation_size('test2_col1_idx'::regclass::text) / 8192 - 1) blkno ,
bt_page_stats('test2_col1_idx',blkno::int);
Good one. But not all may know the alternatives. Do we have any
difference in the execution times for the above query vs the new
function introduced in the v1 patch? If there's not much difference, I
would suggest adding an SQL function around the generate_series
approach in the pageinspect extension for better and easier usability.
Regards,
Bharath Rupireddy.
On Mon, 27 Jun 2022 at 15:52, Bharath Rupireddy <
bharath.rupireddyforpostgres@gmail.com> wrote:
On Mon, Jun 27, 2022 at 1:40 PM Drouvot, Bertrand <bdrouvot@amazon.com>
wrote:Hi,
On 6/27/22 9:31 AM, Hamid Akhtar wrote:
Hello Hackers,
While working on one of my blogs on the B-Tree indexes, I needed to look
at a range of B-Tree page statistics. So the goto solution was to use
pageinspect. However, reviewing stats for multiple pages meant issuing
multiple queries.+1 to improve the API.
I felt that there's an opportunity for improvement in the extension by
extending the API to output the statistics for multiple pages with a single
query.That attached patch is based on the master branch. It makes the
following changes to the pageinspect contrib module:
- Updates bt_page_stats_internal function to accept 3 arguments instead
of 2.
- The function now uses SRF macros to return a set rather than a single
row. The function call now requires specifying column names.
The extension version is bumped to 1.11 (PAGEINSPECT_V1_11).
To maintain backward compatibility, for versions below 1.11, themulti-call mechanism is ended to keep the old behavior consistent.
Regression test cases for the module are updated as well as part of this
change. Here is a subset of queries that are added to the btree.sql test
case file for pageinspect.----
CREATE TABLE test2 AS (SELECT generate_series(1, 5000) AS col1);
CREATE INDEX test2_col1_idx ON test2(col1);
SELECT * FROM bt_page_stats('test2_col1_idx', 1, 2);For example, this could be written as:
select * from
generate_series(1, 2) blkno ,
bt_page_stats('test2_col1_idx',blkno::int);Or, if one wants to inspect to whole relation, something like:
select * from
generate_series(1, pg_relation_size('test2_col1_idx'::regclass::text) /8192 - 1) blkno ,
bt_page_stats('test2_col1_idx',blkno::int);
Good one. But not all may know the alternatives.
+1
Do we have any
difference in the execution times for the above query vs the new
function introduced in the v1 patch? If there's not much difference, I
would suggest adding an SQL function around the generate_series
approach in the pageinspect extension for better and easier usability.
Based on some basic SQL execution time comparison of the two approaches, I
see that the API change, on average, is around 40% faster than the SQL.
CREATE TABLE test2 AS (SELECT generate_series(1, 5000000) AS col1);
CREATE INDEX test2_col1_idx ON test2(col1);
EXPLAIN ANALYZE
SELECT * FROM bt_page_stats('test2_col1_idx', 1, 5000);
EXPLAIN ANALYZE
SELECT * FROM GENERATE_SERIES(1, 5000) blkno,
bt_page_stats('test2_col1_idx',blkno::int);
For me, the API change returns back the data in around 74ms whereas the SQL
returns it in 102ms. So considering this and as you mentioned, the
alternative may not be that obvious to everyone, it is a fair improvement.
Show quoted text
Regards,
Bharath Rupireddy.
On Thu, Jun 30, 2022 at 1:54 PM Hamid Akhtar <hamid.akhtar@gmail.com> wrote:
Do we have any
difference in the execution times for the above query vs the new
function introduced in the v1 patch? If there's not much difference, I
would suggest adding an SQL function around the generate_series
approach in the pageinspect extension for better and easier usability.Based on some basic SQL execution time comparison of the two approaches, I see that the API change, on average, is around 40% faster than the SQL.
CREATE TABLE test2 AS (SELECT generate_series(1, 5000000) AS col1);
CREATE INDEX test2_col1_idx ON test2(col1);EXPLAIN ANALYZE
SELECT * FROM bt_page_stats('test2_col1_idx', 1, 5000);EXPLAIN ANALYZE
SELECT * FROM GENERATE_SERIES(1, 5000) blkno, bt_page_stats('test2_col1_idx',blkno::int);For me, the API change returns back the data in around 74ms whereas the SQL returns it in 102ms. So considering this and as you mentioned, the alternative may not be that obvious to everyone, it is a fair improvement.
I'm wondering what happens with a bit of huge data and different test
cases each test case executed, say, 2 or 3 times.
If the difference in execution times is always present, then the API
approach or changing the core function would make more sense.
Regards,
Bharath Rupireddy.
On Thu, 30 Jun 2022 at 14:27, Bharath Rupireddy <
bharath.rupireddyforpostgres@gmail.com> wrote:
On Thu, Jun 30, 2022 at 1:54 PM Hamid Akhtar <hamid.akhtar@gmail.com>
wrote:Do we have any
difference in the execution times for the above query vs the new
function introduced in the v1 patch? If there's not much difference, I
would suggest adding an SQL function around the generate_series
approach in the pageinspect extension for better and easier usability.Based on some basic SQL execution time comparison of the two approaches,
I see that the API change, on average, is around 40% faster than the SQL.
CREATE TABLE test2 AS (SELECT generate_series(1, 5000000) AS col1);
CREATE INDEX test2_col1_idx ON test2(col1);EXPLAIN ANALYZE
SELECT * FROM bt_page_stats('test2_col1_idx', 1, 5000);EXPLAIN ANALYZE
SELECT * FROM GENERATE_SERIES(1, 5000) blkno,bt_page_stats('test2_col1_idx',blkno::int);
For me, the API change returns back the data in around 74ms whereas the
SQL returns it in 102ms. So considering this and as you mentioned, the
alternative may not be that obvious to everyone, it is a fair improvement.I'm wondering what happens with a bit of huge data and different test
cases each test case executed, say, 2 or 3 times.If the difference in execution times is always present, then the API
approach or changing the core function would make more sense.
Technically, AFAIK, the performance difference will always be there.
Firstly, in the API change, there is no additional overhead of the
generate_series function. Additionally, with API change, looping over the
pages has a smaller overhead when compared with the overhead of the SQL
approach.
Show quoted text
Regards,
Bharath Rupireddy.
Hi,
On 6/30/22 10:24 AM, Hamid Akhtar wrote:
On Mon, 27 Jun 2022 at 15:52, Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:On Mon, Jun 27, 2022 at 1:40 PM Drouvot, Bertrand
<bdrouvot@amazon.com> wrote:Hi,
On 6/27/22 9:31 AM, Hamid Akhtar wrote:
Hello Hackers,
While working on one of my blogs on the B-Tree indexes, I needed
to look at a range of B-Tree page statistics. So the goto solution
was to use pageinspect. However, reviewing stats for multiple
pages meant issuing multiple queries.+1 to improve the API.
I think it makes sense too.
But what about the other pageinspect's functions that also use a single
blkno as parameter? Should not the patch also takes care of them?
Regards,
Bertrand
On Fri, 1 Jul 2022 at 13:01, Drouvot, Bertrand <bdrouvot@amazon.com> wrote:
Hi,
On 6/30/22 10:24 AM, Hamid Akhtar wrote:On Mon, 27 Jun 2022 at 15:52, Bharath Rupireddy <
bharath.rupireddyforpostgres@gmail.com> wrote:On Mon, Jun 27, 2022 at 1:40 PM Drouvot, Bertrand <bdrouvot@amazon.com>
wrote:Hi,
On 6/27/22 9:31 AM, Hamid Akhtar wrote:
Hello Hackers,
While working on one of my blogs on the B-Tree indexes, I needed to
look at a range of B-Tree page statistics. So the goto solution was to use
pageinspect. However, reviewing stats for multiple pages meant issuing
multiple queries.+1 to improve the API.
I think it makes sense too.
But what about the other pageinspect's functions that also use a single
blkno as parameter? Should not the patch also takes care of them?I've started working on that. But it's going to be a much bigger change
with a lot of code refactoring.
So, taking this one step at a time, IMHO, this patch is good to be reviewed
now.
Show quoted text
Regards,
Bertrand
Hamid Akhtar <hamid.akhtar@gmail.com> writes:
That attached patch is based on the master branch. It makes the following
changes to the pageinspect contrib module:
- Updates bt_page_stats_internal function to accept 3 arguments instead of
2.
- The function now uses SRF macros to return a set rather than a single
row. The function call now requires specifying column names.
FWIW, I think you'd be way better off changing the function name, say
to bt_multi_page_stats(). Overloading the name this way is going to
lead to great confusion, e.g. somebody who fat-fingers the number of
output arguments in a JDBC call could see confusing results due to
invoking the wrong one of the two functions. Also, I'm not quite sure
what you mean by "The function call now requires specifying column
names", but it doesn't sound like an acceptable restriction from a
compatibility standpoint. If a different name dodges that issue then
it's clearly a better way.
regards, tom lane
On Thu, 28 Jul 2022 at 00:36, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Hamid Akhtar <hamid.akhtar@gmail.com> writes:
That attached patch is based on the master branch. It makes the following
changes to the pageinspect contrib module:
- Updates bt_page_stats_internal function to accept 3 arguments insteadof
2.
- The function now uses SRF macros to return a set rather than a single
row. The function call now requires specifying column names.FWIW, I think you'd be way better off changing the function name, say
to bt_multi_page_stats(). Overloading the name this way is going to
lead to great confusion, e.g. somebody who fat-fingers the number of
output arguments in a JDBC call could see confusing results due to
invoking the wrong one of the two functions. Also, I'm not quite sure
what you mean by "The function call now requires specifying column
names", but it doesn't sound like an acceptable restriction from a
compatibility standpoint. If a different name dodges that issue then
it's clearly a better way.regards, tom lane
Attached please find the latest version of the patch;
pageinspect_btree_multipagestats_02.patch.
It no longer modifies the existing bt_page_stats function. Instead, it
introduces a new function bt_multi_page_stats as you had suggested. The
function expects three arguments where the first argument is the index name
followed by block number and number of blocks to be returned.
Please ignore this statement. It was a typo.
"The function call now requires specifying column names"
Attachments:
pageinspect_btree_multipagestats_02.patchapplication/x-patch; name=pageinspect_btree_multipagestats_02.patchDownload
diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile
index 5c07365..31d7b1c 100644
--- a/contrib/pageinspect/Makefile
+++ b/contrib/pageinspect/Makefile
@@ -13,12 +13,12 @@ OBJS = \
rawpage.o
EXTENSION = pageinspect
-DATA = pageinspect--1.9--1.10.sql pageinspect--1.8--1.9.sql \
- pageinspect--1.7--1.8.sql pageinspect--1.6--1.7.sql \
- pageinspect--1.5.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
+DATA = pageinspect--1.10--1.11.sql pageinspect--1.9--1.10.sql \
+ pageinspect--1.8--1.9.sql pageinspect--1.7--1.8.sql \
+ pageinspect--1.6--1.7.sql pageinspect--1.5.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
PGFILEDESC = "pageinspect - functions to inspect contents of database pages"
REGRESS = page btree brin gin gist hash checksum oldextversions
diff --git a/contrib/pageinspect/btreefuncs.c b/contrib/pageinspect/btreefuncs.c
index 62f2c1b..5dd216e 100644
--- a/contrib/pageinspect/btreefuncs.c
+++ b/contrib/pageinspect/btreefuncs.c
@@ -46,6 +46,7 @@ PG_FUNCTION_INFO_V1(bt_page_items);
PG_FUNCTION_INFO_V1(bt_page_items_bytea);
PG_FUNCTION_INFO_V1(bt_page_stats_1_9);
PG_FUNCTION_INFO_V1(bt_page_stats);
+PG_FUNCTION_INFO_V1(bt_multi_page_stats);
#define IS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)
#define IS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID)
@@ -80,6 +81,26 @@ typedef struct BTPageStat
BTCycleId btpo_cycleid;
} BTPageStat;
+/*
+ * cross-call data structure for SRF for page stats
+ */
+typedef struct ua_page_stats
+{
+ uint64 blkno;
+ uint64 blk_count;
+} ua_page_stats;
+
+/*
+ * cross-call data structure for SRF for page items
+ */
+typedef struct ua_page_items
+{
+ Page page;
+ OffsetNumber offset;
+ bool leafpage;
+ bool rightmost;
+ TupleDesc tupd;
+} ua_page_items;
/* -------------------------------------------------
* GetBTPageStatistics()
@@ -177,34 +198,15 @@ GetBTPageStatistics(BlockNumber blkno, Buffer buffer, BTPageStat *stat)
}
/* -----------------------------------------------
- * bt_page_stats()
+ * bt_index_block_validate()
*
- * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1);
+ * Validate index type is btree and block number
+ * is within a valid block number range.
* -----------------------------------------------
*/
-static Datum
-bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
+static void
+bt_index_block_validate(Relation rel, int64 blkno)
{
- text *relname = PG_GETARG_TEXT_PP(0);
- int64 blkno = (ext_version == PAGEINSPECT_V1_8 ? PG_GETARG_UINT32(1) : PG_GETARG_INT64(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))
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -232,6 +234,38 @@ bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
errmsg("block 0 is a meta page")));
CHECK_RELATION_BLOCK_RANGE(rel, blkno);
+}
+
+/* -----------------------------------------------
+ * bt_page_stats()
+ *
+ * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1);
+ * -----------------------------------------------
+ */
+static Datum
+bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
+{
+ text *relname = PG_GETARG_TEXT_PP(0);
+ int64 blkno = (ext_version == PAGEINSPECT_V1_8 ? PG_GETARG_UINT32(1) : PG_GETARG_INT64(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);
+
+ bt_index_block_validate(rel, blkno);
buffer = ReadBuffer(rel, blkno);
LockBuffer(buffer, BUFFER_LOCK_SHARE);
@@ -270,6 +304,134 @@ bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
PG_RETURN_DATUM(result);
}
+/* -----------------------------------------------
+ * bt_multi_page_stats()
+ *
+ * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1, 1);
+ * Where the first argument is the name followed
+ * by block number and number of blocks to be
+ * returned.
+ * -----------------------------------------------
+ */
+Datum
+bt_multi_page_stats(PG_FUNCTION_ARGS)
+{
+ text *relname = PG_GETARG_TEXT_PP(0);
+ int64 blkno = PG_GETARG_INT64(1);
+ int64 blk_count = PG_GETARG_INT64(2);
+ RangeVar *relrv;
+ Relation rel;
+ ua_page_stats *uargs;
+ FuncCallContext *fctx;
+ MemoryContext mctx;
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to use pageinspect functions")));
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ fctx = SRF_FIRSTCALL_INIT();
+
+ relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
+ rel = relation_openrv(relrv, AccessShareLock);
+
+ /*
+ * Check if relaion is a valid btree index and block number is within
+ * the valid range.
+ */
+ bt_index_block_validate(rel, blkno);
+
+ relation_close(rel, AccessShareLock);
+
+ mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
+
+ uargs = palloc(sizeof(struct ua_page_stats));
+
+ /*
+ * Set the start block number and the total number of blocks to ne
+ * retrieved.
+ */
+ uargs->blkno = blkno;
+ uargs->blk_count = blk_count;
+
+ fctx->user_fctx = uargs;
+
+ MemoryContextSwitchTo(mctx);
+ }
+
+ fctx = SRF_PERCALL_SETUP();
+ uargs = fctx->user_fctx;
+
+ /* We need to fetch next block statistics */
+ if (uargs->blk_count > 0)
+ {
+ Buffer buffer;
+ Datum result;
+ HeapTuple tuple;
+ int j;
+ char *values[11];
+ BTPageStat stat;
+ TupleDesc tupleDesc;
+
+ relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
+ rel = relation_openrv(relrv, AccessShareLock);
+
+ /*
+ * Since index may have changed between function calls, double check
+ * that we are still within the vaild range.
+ */
+ CHECK_RELATION_BLOCK_RANGE(rel, uargs->blkno);
+
+ buffer = ReadBuffer(rel, uargs->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(uargs->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("%u", stat.blkno);
+ values[j++] = psprintf("%c", stat.type);
+ values[j++] = psprintf("%u", stat.live_items);
+ values[j++] = psprintf("%u", stat.dead_items);
+ values[j++] = psprintf("%u", stat.avg_item_size);
+ values[j++] = psprintf("%u", stat.page_size);
+ values[j++] = psprintf("%u", stat.free_size);
+ values[j++] = psprintf("%u", stat.btpo_prev);
+ values[j++] = psprintf("%u", stat.btpo_next);
+ values[j++] = psprintf("%u", stat.btpo_level);
+ values[j++] = psprintf("%d", stat.btpo_flags);
+
+ /* Constructu tuple to be returned */
+ tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
+ values);
+
+ result = HeapTupleGetDatum(tuple);
+
+ /*
+ * Move to the next block number and decrement the number of blocks
+ * still to be fetched
+ */
+ uargs->blkno++;
+ uargs->blk_count--;
+
+ SRF_RETURN_NEXT(fctx, result);
+ }
+
+ SRF_RETURN_DONE(fctx);
+}
+
Datum
bt_page_stats_1_9(PG_FUNCTION_ARGS)
{
@@ -283,19 +445,6 @@ bt_page_stats(PG_FUNCTION_ARGS)
return bt_page_stats_internal(fcinfo, PAGEINSPECT_V1_8);
}
-
-/*
- * cross-call data structure for SRF
- */
-struct user_args
-{
- Page page;
- OffsetNumber offset;
- bool leafpage;
- bool rightmost;
- TupleDesc tupd;
-};
-
/*-------------------------------------------------------
* bt_page_print_tuples()
*
@@ -303,7 +452,7 @@ struct user_args
* ------------------------------------------------------
*/
static Datum
-bt_page_print_tuples(struct user_args *uargs)
+bt_page_print_tuples(struct ua_page_items *uargs)
{
Page page = uargs->page;
OffsetNumber offset = uargs->offset;
@@ -457,7 +606,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
Datum result;
FuncCallContext *fctx;
MemoryContext mctx;
- struct user_args *uargs;
+ struct ua_page_items *uargs;
if (!superuser())
ereport(ERROR,
@@ -477,33 +626,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
rel = relation_openrv(relrv, AccessShareLock);
- if (!IS_INDEX(rel) || !IS_BTREE(rel))
- ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is not a %s index",
- RelationGetRelationName(rel), "btree")));
-
- /*
- * 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 || blkno > MaxBlockNumber)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("invalid block number")));
-
- if (blkno == 0)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("block 0 is a meta page")));
-
- CHECK_RELATION_BLOCK_RANGE(rel, blkno);
+ bt_index_block_validate(rel, blkno);
buffer = ReadBuffer(rel, blkno);
LockBuffer(buffer, BUFFER_LOCK_SHARE);
@@ -515,7 +638,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
*/
mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
- uargs = palloc(sizeof(struct user_args));
+ uargs = palloc(sizeof(struct ua_page_items));
uargs->page = palloc(BLCKSZ);
memcpy(uargs->page, BufferGetPage(buffer), BLCKSZ);
@@ -591,7 +714,7 @@ bt_page_items_bytea(PG_FUNCTION_ARGS)
bytea *raw_page = PG_GETARG_BYTEA_P(0);
Datum result;
FuncCallContext *fctx;
- struct user_args *uargs;
+ struct ua_page_items *uargs;
if (!superuser())
ereport(ERROR,
@@ -607,7 +730,7 @@ bt_page_items_bytea(PG_FUNCTION_ARGS)
fctx = SRF_FIRSTCALL_INIT();
mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
- uargs = palloc(sizeof(struct user_args));
+ uargs = palloc(sizeof(struct ua_page_items));
uargs->page = get_page_from_raw(raw_page);
diff --git a/contrib/pageinspect/expected/btree.out b/contrib/pageinspect/expected/btree.out
index 035a81a..383b9be 100644
--- a/contrib/pageinspect/expected/btree.out
+++ b/contrib/pageinspect/expected/btree.out
@@ -34,6 +34,117 @@ btpo_flags | 3
SELECT * FROM bt_page_stats('test1_a_idx', 2);
ERROR: block number out of range
+-- bt_multi_page_stats() function returns a set of records of page statistics.
+CREATE TABLE test2 AS (SELECT generate_series(1, 5000) AS col1);
+CREATE INDEX test2_col1_idx ON test2(col1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 0, 1);
+ERROR: block 0 is a meta page
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, -1);
+ERROR: block number out of range
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 0);
+(0 rows)
+
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 2);
+-[ RECORD 1 ]-+-----
+blkno | 1
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 0
+btpo_next | 2
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 2 ]-+-----
+blkno | 2
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 1
+btpo_next | 4
+btpo_level | 0
+btpo_flags | 1
+
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 2, 6);
+-[ RECORD 1 ]-+-----
+blkno | 2
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 1
+btpo_next | 4
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 2 ]-+-----
+blkno | 3
+type | r
+live_items | 14
+dead_items | 0
+avg_item_size | 15
+page_size | 8192
+free_size | 7876
+btpo_prev | 0
+btpo_next | 0
+btpo_level | 1
+btpo_flags | 2
+-[ RECORD 3 ]-+-----
+blkno | 4
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 2
+btpo_next | 5
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 4 ]-+-----
+blkno | 5
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 4
+btpo_next | 6
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 5 ]-+-----
+blkno | 6
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 5
+btpo_next | 7
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 6 ]-+-----
+blkno | 7
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 6
+btpo_next | 8
+btpo_level | 0
+btpo_flags | 1
+
+DROP TABLE test2;
SELECT * FROM bt_page_items('test1_a_idx', -1);
ERROR: invalid block number
SELECT * FROM bt_page_items('test1_a_idx', 0);
diff --git a/contrib/pageinspect/pageinspect--1.10--1.11.sql b/contrib/pageinspect/pageinspect--1.10--1.11.sql
index e69de29..976d029 100644
--- a/contrib/pageinspect/pageinspect--1.10--1.11.sql
+++ b/contrib/pageinspect/pageinspect--1.10--1.11.sql
@@ -0,0 +1,23 @@
+/* contrib/pageinspect/pageinspect--1.10--1.11.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pageinspect UPDATE TO '1.11'" to load this file. \quit
+
+--
+-- bt_multi_page_stats()
+--
+CREATE FUNCTION bt_multi_page_stats(IN relname text, IN blkno int8, IN blk_count int8,
+ OUT blkno int8,
+ 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 int8,
+ OUT btpo_next int8,
+ OUT btpo_level int8,
+ OUT btpo_flags int4)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'bt_multi_page_stats'
+LANGUAGE C STRICT PARALLEL SAFE;
diff --git a/contrib/pageinspect/pageinspect.control b/contrib/pageinspect/pageinspect.control
index 7cdf379..f277413 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.10'
+default_version = '1.11'
module_pathname = '$libdir/pageinspect'
relocatable = true
diff --git a/contrib/pageinspect/sql/btree.sql b/contrib/pageinspect/sql/btree.sql
index 1f554f0..9a95914 100644
--- a/contrib/pageinspect/sql/btree.sql
+++ b/contrib/pageinspect/sql/btree.sql
@@ -11,6 +11,16 @@ SELECT * FROM bt_page_stats('test1_a_idx', 0);
SELECT * FROM bt_page_stats('test1_a_idx', 1);
SELECT * FROM bt_page_stats('test1_a_idx', 2);
+-- bt_multi_page_stats() function returns a set of records of page statistics.
+CREATE TABLE test2 AS (SELECT generate_series(1, 5000) AS col1);
+CREATE INDEX test2_col1_idx ON test2(col1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 0, 1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, -1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 0);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 2);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 2, 6);
+DROP TABLE test2;
+
SELECT * FROM bt_page_items('test1_a_idx', -1);
SELECT * FROM bt_page_items('test1_a_idx', 0);
SELECT * FROM bt_page_items('test1_a_idx', 1);
On Sun, 31 Jul 2022 at 18:00, Hamid Akhtar <hamid.akhtar@gmail.com> wrote:
On Thu, 28 Jul 2022 at 00:36, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Hamid Akhtar <hamid.akhtar@gmail.com> writes:
That attached patch is based on the master branch. It makes the
following
changes to the pageinspect contrib module:
- Updates bt_page_stats_internal function to accept 3 arguments insteadof
2.
- The function now uses SRF macros to return a set rather than a single
row. The function call now requires specifying column names.FWIW, I think you'd be way better off changing the function name, say
to bt_multi_page_stats(). Overloading the name this way is going to
lead to great confusion, e.g. somebody who fat-fingers the number of
output arguments in a JDBC call could see confusing results due to
invoking the wrong one of the two functions. Also, I'm not quite sure
what you mean by "The function call now requires specifying column
names", but it doesn't sound like an acceptable restriction from a
compatibility standpoint. If a different name dodges that issue then
it's clearly a better way.regards, tom lane
Attached please find the latest version of the patch;
pageinspect_btree_multipagestats_02.patch.It no longer modifies the existing bt_page_stats function. Instead, it
introduces a new function bt_multi_page_stats as you had suggested. The
function expects three arguments where the first argument is the index name
followed by block number and number of blocks to be returned.Please ignore this statement. It was a typo.
"The function call now requires specifying column names"
Attached is the rebased version of the patch
(pageinspect_btree_multipagestats_03.patch) for the master branch.
Show quoted text
Attachments:
pageinspect_btree_multipagestats_03.patchapplication/x-patch; name=pageinspect_btree_multipagestats_03.patchDownload
diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile
index 5c07365..31d7b1c 100644
--- a/contrib/pageinspect/Makefile
+++ b/contrib/pageinspect/Makefile
@@ -13,12 +13,12 @@ OBJS = \
rawpage.o
EXTENSION = pageinspect
-DATA = pageinspect--1.9--1.10.sql pageinspect--1.8--1.9.sql \
- pageinspect--1.7--1.8.sql pageinspect--1.6--1.7.sql \
- pageinspect--1.5.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
+DATA = pageinspect--1.10--1.11.sql pageinspect--1.9--1.10.sql \
+ pageinspect--1.8--1.9.sql pageinspect--1.7--1.8.sql \
+ pageinspect--1.6--1.7.sql pageinspect--1.5.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
PGFILEDESC = "pageinspect - functions to inspect contents of database pages"
REGRESS = page btree brin gin gist hash checksum oldextversions
diff --git a/contrib/pageinspect/btreefuncs.c b/contrib/pageinspect/btreefuncs.c
index 9375d55..f7ec95c 100644
--- a/contrib/pageinspect/btreefuncs.c
+++ b/contrib/pageinspect/btreefuncs.c
@@ -46,6 +46,7 @@ PG_FUNCTION_INFO_V1(bt_page_items);
PG_FUNCTION_INFO_V1(bt_page_items_bytea);
PG_FUNCTION_INFO_V1(bt_page_stats_1_9);
PG_FUNCTION_INFO_V1(bt_page_stats);
+PG_FUNCTION_INFO_V1(bt_multi_page_stats);
#define IS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)
#define IS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID)
@@ -80,6 +81,26 @@ typedef struct BTPageStat
BTCycleId btpo_cycleid;
} BTPageStat;
+/*
+ * cross-call data structure for SRF for page stats
+ */
+typedef struct ua_page_stats
+{
+ uint64 blkno;
+ uint64 blk_count;
+} ua_page_stats;
+
+/*
+ * cross-call data structure for SRF for page items
+ */
+typedef struct ua_page_items
+{
+ Page page;
+ OffsetNumber offset;
+ bool leafpage;
+ bool rightmost;
+ TupleDesc tupd;
+} ua_page_items;
/* -------------------------------------------------
* GetBTPageStatistics()
@@ -177,34 +198,15 @@ GetBTPageStatistics(BlockNumber blkno, Buffer buffer, BTPageStat *stat)
}
/* -----------------------------------------------
- * bt_page_stats()
+ * bt_index_block_validate()
*
- * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1);
+ * Validate index type is btree and block number
+ * is within a valid block number range.
* -----------------------------------------------
*/
-static Datum
-bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
+static void
+bt_index_block_validate(Relation rel, int64 blkno)
{
- text *relname = PG_GETARG_TEXT_PP(0);
- int64 blkno = (ext_version == PAGEINSPECT_V1_8 ? PG_GETARG_UINT32(1) : PG_GETARG_INT64(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))
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -232,6 +234,38 @@ bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
errmsg("block 0 is a meta page")));
CHECK_RELATION_BLOCK_RANGE(rel, blkno);
+}
+
+/* -----------------------------------------------
+ * bt_page_stats()
+ *
+ * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1);
+ * -----------------------------------------------
+ */
+static Datum
+bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
+{
+ text *relname = PG_GETARG_TEXT_PP(0);
+ int64 blkno = (ext_version == PAGEINSPECT_V1_8 ? PG_GETARG_UINT32(1) : PG_GETARG_INT64(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);
+
+ bt_index_block_validate(rel, blkno);
buffer = ReadBuffer(rel, blkno);
LockBuffer(buffer, BUFFER_LOCK_SHARE);
@@ -270,6 +304,134 @@ bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
PG_RETURN_DATUM(result);
}
+/* -----------------------------------------------
+ * bt_multi_page_stats()
+ *
+ * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1, 1);
+ * Where the first argument is the name followed
+ * by block number and number of blocks to be
+ * returned.
+ * -----------------------------------------------
+ */
+Datum
+bt_multi_page_stats(PG_FUNCTION_ARGS)
+{
+ text *relname = PG_GETARG_TEXT_PP(0);
+ int64 blkno = PG_GETARG_INT64(1);
+ int64 blk_count = PG_GETARG_INT64(2);
+ RangeVar *relrv;
+ Relation rel;
+ ua_page_stats *uargs;
+ FuncCallContext *fctx;
+ MemoryContext mctx;
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to use pageinspect functions")));
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ fctx = SRF_FIRSTCALL_INIT();
+
+ relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
+ rel = relation_openrv(relrv, AccessShareLock);
+
+ /*
+ * Check if relaion is a valid btree index and block number is within
+ * the valid range.
+ */
+ bt_index_block_validate(rel, blkno);
+
+ relation_close(rel, AccessShareLock);
+
+ mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
+
+ uargs = palloc(sizeof(struct ua_page_stats));
+
+ /*
+ * Set the start block number and the total number of blocks to ne
+ * retrieved.
+ */
+ uargs->blkno = blkno;
+ uargs->blk_count = blk_count;
+
+ fctx->user_fctx = uargs;
+
+ MemoryContextSwitchTo(mctx);
+ }
+
+ fctx = SRF_PERCALL_SETUP();
+ uargs = fctx->user_fctx;
+
+ /* We need to fetch next block statistics */
+ if (uargs->blk_count > 0)
+ {
+ Buffer buffer;
+ Datum result;
+ HeapTuple tuple;
+ int j;
+ char *values[11];
+ BTPageStat stat;
+ TupleDesc tupleDesc;
+
+ relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
+ rel = relation_openrv(relrv, AccessShareLock);
+
+ /*
+ * Since index may have changed between function calls, double check
+ * that we are still within the vaild range.
+ */
+ CHECK_RELATION_BLOCK_RANGE(rel, uargs->blkno);
+
+ buffer = ReadBuffer(rel, uargs->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(uargs->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("%u", stat.blkno);
+ values[j++] = psprintf("%c", stat.type);
+ values[j++] = psprintf("%u", stat.live_items);
+ values[j++] = psprintf("%u", stat.dead_items);
+ values[j++] = psprintf("%u", stat.avg_item_size);
+ values[j++] = psprintf("%u", stat.page_size);
+ values[j++] = psprintf("%u", stat.free_size);
+ values[j++] = psprintf("%u", stat.btpo_prev);
+ values[j++] = psprintf("%u", stat.btpo_next);
+ values[j++] = psprintf("%u", stat.btpo_level);
+ values[j++] = psprintf("%d", stat.btpo_flags);
+
+ /* Constructu tuple to be returned */
+ tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
+ values);
+
+ result = HeapTupleGetDatum(tuple);
+
+ /*
+ * Move to the next block number and decrement the number of blocks
+ * still to be fetched
+ */
+ uargs->blkno++;
+ uargs->blk_count--;
+
+ SRF_RETURN_NEXT(fctx, result);
+ }
+
+ SRF_RETURN_DONE(fctx);
+}
+
Datum
bt_page_stats_1_9(PG_FUNCTION_ARGS)
{
@@ -283,19 +445,6 @@ bt_page_stats(PG_FUNCTION_ARGS)
return bt_page_stats_internal(fcinfo, PAGEINSPECT_V1_8);
}
-
-/*
- * cross-call data structure for SRF
- */
-struct user_args
-{
- Page page;
- OffsetNumber offset;
- bool leafpage;
- bool rightmost;
- TupleDesc tupd;
-};
-
/*-------------------------------------------------------
* bt_page_print_tuples()
*
@@ -303,7 +452,7 @@ struct user_args
* ------------------------------------------------------
*/
static Datum
-bt_page_print_tuples(struct user_args *uargs)
+bt_page_print_tuples(struct ua_page_items *uargs)
{
Page page = uargs->page;
OffsetNumber offset = uargs->offset;
@@ -453,7 +602,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
Datum result;
FuncCallContext *fctx;
MemoryContext mctx;
- struct user_args *uargs;
+ struct ua_page_items *uargs;
if (!superuser())
ereport(ERROR,
@@ -473,33 +622,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
rel = relation_openrv(relrv, AccessShareLock);
- if (!IS_INDEX(rel) || !IS_BTREE(rel))
- ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is not a %s index",
- RelationGetRelationName(rel), "btree")));
-
- /*
- * 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 || blkno > MaxBlockNumber)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("invalid block number")));
-
- if (blkno == 0)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("block 0 is a meta page")));
-
- CHECK_RELATION_BLOCK_RANGE(rel, blkno);
+ bt_index_block_validate(rel, blkno);
buffer = ReadBuffer(rel, blkno);
LockBuffer(buffer, BUFFER_LOCK_SHARE);
@@ -511,7 +634,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
*/
mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
- uargs = palloc(sizeof(struct user_args));
+ uargs = palloc(sizeof(struct ua_page_items));
uargs->page = palloc(BLCKSZ);
memcpy(uargs->page, BufferGetPage(buffer), BLCKSZ);
@@ -587,7 +710,7 @@ bt_page_items_bytea(PG_FUNCTION_ARGS)
bytea *raw_page = PG_GETARG_BYTEA_P(0);
Datum result;
FuncCallContext *fctx;
- struct user_args *uargs;
+ struct ua_page_items *uargs;
if (!superuser())
ereport(ERROR,
@@ -603,7 +726,7 @@ bt_page_items_bytea(PG_FUNCTION_ARGS)
fctx = SRF_FIRSTCALL_INIT();
mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
- uargs = palloc(sizeof(struct user_args));
+ uargs = palloc(sizeof(struct ua_page_items));
uargs->page = get_page_from_raw(raw_page);
diff --git a/contrib/pageinspect/expected/btree.out b/contrib/pageinspect/expected/btree.out
index 035a81a..383b9be 100644
--- a/contrib/pageinspect/expected/btree.out
+++ b/contrib/pageinspect/expected/btree.out
@@ -34,6 +34,117 @@ btpo_flags | 3
SELECT * FROM bt_page_stats('test1_a_idx', 2);
ERROR: block number out of range
+-- bt_multi_page_stats() function returns a set of records of page statistics.
+CREATE TABLE test2 AS (SELECT generate_series(1, 5000) AS col1);
+CREATE INDEX test2_col1_idx ON test2(col1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 0, 1);
+ERROR: block 0 is a meta page
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, -1);
+ERROR: block number out of range
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 0);
+(0 rows)
+
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 2);
+-[ RECORD 1 ]-+-----
+blkno | 1
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 0
+btpo_next | 2
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 2 ]-+-----
+blkno | 2
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 1
+btpo_next | 4
+btpo_level | 0
+btpo_flags | 1
+
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 2, 6);
+-[ RECORD 1 ]-+-----
+blkno | 2
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 1
+btpo_next | 4
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 2 ]-+-----
+blkno | 3
+type | r
+live_items | 14
+dead_items | 0
+avg_item_size | 15
+page_size | 8192
+free_size | 7876
+btpo_prev | 0
+btpo_next | 0
+btpo_level | 1
+btpo_flags | 2
+-[ RECORD 3 ]-+-----
+blkno | 4
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 2
+btpo_next | 5
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 4 ]-+-----
+blkno | 5
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 4
+btpo_next | 6
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 5 ]-+-----
+blkno | 6
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 5
+btpo_next | 7
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 6 ]-+-----
+blkno | 7
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 6
+btpo_next | 8
+btpo_level | 0
+btpo_flags | 1
+
+DROP TABLE test2;
SELECT * FROM bt_page_items('test1_a_idx', -1);
ERROR: invalid block number
SELECT * FROM bt_page_items('test1_a_idx', 0);
diff --git a/contrib/pageinspect/pageinspect--1.10--1.11.sql b/contrib/pageinspect/pageinspect--1.10--1.11.sql
index e69de29..976d029 100644
--- a/contrib/pageinspect/pageinspect--1.10--1.11.sql
+++ b/contrib/pageinspect/pageinspect--1.10--1.11.sql
@@ -0,0 +1,23 @@
+/* contrib/pageinspect/pageinspect--1.10--1.11.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pageinspect UPDATE TO '1.11'" to load this file. \quit
+
+--
+-- bt_multi_page_stats()
+--
+CREATE FUNCTION bt_multi_page_stats(IN relname text, IN blkno int8, IN blk_count int8,
+ OUT blkno int8,
+ 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 int8,
+ OUT btpo_next int8,
+ OUT btpo_level int8,
+ OUT btpo_flags int4)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'bt_multi_page_stats'
+LANGUAGE C STRICT PARALLEL SAFE;
diff --git a/contrib/pageinspect/pageinspect.control b/contrib/pageinspect/pageinspect.control
index 7cdf379..f277413 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.10'
+default_version = '1.11'
module_pathname = '$libdir/pageinspect'
relocatable = true
diff --git a/contrib/pageinspect/sql/btree.sql b/contrib/pageinspect/sql/btree.sql
index 1f554f0..9a95914 100644
--- a/contrib/pageinspect/sql/btree.sql
+++ b/contrib/pageinspect/sql/btree.sql
@@ -11,6 +11,16 @@ SELECT * FROM bt_page_stats('test1_a_idx', 0);
SELECT * FROM bt_page_stats('test1_a_idx', 1);
SELECT * FROM bt_page_stats('test1_a_idx', 2);
+-- bt_multi_page_stats() function returns a set of records of page statistics.
+CREATE TABLE test2 AS (SELECT generate_series(1, 5000) AS col1);
+CREATE INDEX test2_col1_idx ON test2(col1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 0, 1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, -1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 0);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 2);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 2, 6);
+DROP TABLE test2;
+
SELECT * FROM bt_page_items('test1_a_idx', -1);
SELECT * FROM bt_page_items('test1_a_idx', 0);
SELECT * FROM bt_page_items('test1_a_idx', 1);
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: not tested
Documentation: not tested
Looks good to me.
The new status of this patch is: Ready for Committer
On Mon, Aug 1, 2022 at 11:29 PM Naeem Akhter <naeem.akhter@percona.com>
wrote:
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: not tested
Documentation: not testedLooks good to me.
The new status of this patch is: Ready for Committer
The patch has a compilation error on the latest code base, please rebase
your patch.
[03:08:46.087] /tmp/cirrus-ci-build/contrib/cube/cubeparse.h:77:27: error:
‘result’ was not declared in this scope
[03:08:46.087] 77 | int cube_yyparse (NDBOX **result, Size scanbuflen);
[03:08:46.087] | ^~~~~~
[03:08:46.087] /tmp/cirrus-ci-build/contrib/cube/cubeparse.h:77:40: error:
expected primary-expression before ‘scanbuflen’
[03:08:46.087] 77 | int cube_yyparse (NDBOX **result, Size scanbuflen);
...
--
Ibrar Ahmed
On Tue, 6 Sept 2022 at 11:25, Ibrar Ahmed <ibrar.ahmad@gmail.com> wrote:
On Mon, Aug 1, 2022 at 11:29 PM Naeem Akhter <naeem.akhter@percona.com>
wrote:The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: not tested
Documentation: not testedLooks good to me.
The new status of this patch is: Ready for Committer
The patch has a compilation error on the latest code base, please rebase
your patch.[03:08:46.087] /tmp/cirrus-ci-build/contrib/cube/cubeparse.h:77:27: error:
‘result’ was not declared in this scope
[03:08:46.087] 77 | int cube_yyparse (NDBOX **result, Size scanbuflen);
[03:08:46.087] | ^~~~~~
[03:08:46.087] /tmp/cirrus-ci-build/contrib/cube/cubeparse.h:77:40: error:
expected primary-expression before ‘scanbuflen’
[03:08:46.087] 77 | int cube_yyparse (NDBOX **result, Size scanbuflen);
...--
Ibrar Ahmed
The compilation and regression are working fine. I have verified it against
the tip of the master branch [commit: 57796a0f].
Hamid Akhtar <hamid.akhtar@gmail.com> writes:
Attached is the rebased version of the patch
(pageinspect_btree_multipagestats_03.patch) for the master branch.
I looked through this and cleaned up the code a little (attached).
There are still some issues before it could be considered committable:
1. Where's the documentation update?
2. As this stands, there's no nice way to ask for "all the pages".
If you specify a page count that's even one too large, you get an
error. I think there's room for an easier-to-use way to do that.
We could say that the thing just silently stops at the last page,
so that you just need to write a large page count. Or maybe it'd
be better to define a zero or negative page count as "all the rest",
while still insisting that a positive count refer to real pages.
3. I think it's highly likely that the new test case is not portable.
In particular a machine with MAXALIGN 4 would be likely to put a
different number of tuples per page, or do the page split differently
so that the page with fewer index tuples isn't page 3. Unfortunately
I don't seem to have a working setup like that right at the moment
to verify; but I'd counsel trying this inside a VM or something to
see if it's actually likely to survive on the buildfarm. I'm not
sure, but making the indexed column be int8 instead of int4 might
reduce the risks here.
regards, tom lane
Attachments:
pageinspect_btree_multipagestats-v4.patchtext/x-diff; charset=us-ascii; name=pageinspect_btree_multipagestats-v4.patchDownload
diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile
index 5c0736564a..069683caf6 100644
--- a/contrib/pageinspect/Makefile
+++ b/contrib/pageinspect/Makefile
@@ -13,7 +13,9 @@ OBJS = \
rawpage.o
EXTENSION = pageinspect
-DATA = pageinspect--1.9--1.10.sql pageinspect--1.8--1.9.sql \
+DATA = \
+ pageinspect--1.10--1.11.sql \
+ pageinspect--1.9--1.10.sql pageinspect--1.8--1.9.sql \
pageinspect--1.7--1.8.sql pageinspect--1.6--1.7.sql \
pageinspect--1.5.sql pageinspect--1.5--1.6.sql \
pageinspect--1.4--1.5.sql pageinspect--1.3--1.4.sql \
diff --git a/contrib/pageinspect/btreefuncs.c b/contrib/pageinspect/btreefuncs.c
index 9375d55e14..f52a6ac87e 100644
--- a/contrib/pageinspect/btreefuncs.c
+++ b/contrib/pageinspect/btreefuncs.c
@@ -46,6 +46,7 @@ PG_FUNCTION_INFO_V1(bt_page_items);
PG_FUNCTION_INFO_V1(bt_page_items_bytea);
PG_FUNCTION_INFO_V1(bt_page_stats_1_9);
PG_FUNCTION_INFO_V1(bt_page_stats);
+PG_FUNCTION_INFO_V1(bt_multi_page_stats);
#define IS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)
#define IS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID)
@@ -80,6 +81,28 @@ typedef struct BTPageStat
BTCycleId btpo_cycleid;
} BTPageStat;
+/*
+ * cross-call data structure for SRF for page stats
+ */
+typedef struct ua_page_stats
+{
+ Oid relid;
+ int64 blkno;
+ int64 blk_count;
+} ua_page_stats;
+
+/*
+ * cross-call data structure for SRF for page items
+ */
+typedef struct ua_page_items
+{
+ Page page;
+ OffsetNumber offset;
+ bool leafpage;
+ bool rightmost;
+ TupleDesc tupd;
+} ua_page_items;
+
/* -------------------------------------------------
* GetBTPageStatistics()
@@ -177,34 +200,15 @@ GetBTPageStatistics(BlockNumber blkno, Buffer buffer, BTPageStat *stat)
}
/* -----------------------------------------------
- * bt_page_stats()
+ * bt_index_block_validate()
*
- * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1);
+ * Validate index type is btree and block number
+ * is within a valid block number range.
* -----------------------------------------------
*/
-static Datum
-bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
+static void
+bt_index_block_validate(Relation rel, int64 blkno)
{
- text *relname = PG_GETARG_TEXT_PP(0);
- int64 blkno = (ext_version == PAGEINSPECT_V1_8 ? PG_GETARG_UINT32(1) : PG_GETARG_INT64(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))
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -232,6 +236,39 @@ bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
errmsg("block 0 is a meta page")));
CHECK_RELATION_BLOCK_RANGE(rel, blkno);
+}
+
+/* -----------------------------------------------
+ * bt_page_stats()
+ *
+ * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1);
+ * Arguments are index relation name and block number
+ * -----------------------------------------------
+ */
+static Datum
+bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
+{
+ text *relname = PG_GETARG_TEXT_PP(0);
+ int64 blkno = (ext_version == PAGEINSPECT_V1_8 ? PG_GETARG_UINT32(1) : PG_GETARG_INT64(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);
+
+ bt_index_block_validate(rel, blkno);
buffer = ReadBuffer(rel, blkno);
LockBuffer(buffer, BUFFER_LOCK_SHARE);
@@ -284,17 +321,134 @@ bt_page_stats(PG_FUNCTION_ARGS)
}
-/*
- * cross-call data structure for SRF
+/* -----------------------------------------------
+ * bt_multi_page_stats()
+ *
+ * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1, 2);
+ * Arguments are index relation name, first block number, number of blocks
+ * -----------------------------------------------
*/
-struct user_args
+Datum
+bt_multi_page_stats(PG_FUNCTION_ARGS)
{
- Page page;
- OffsetNumber offset;
- bool leafpage;
- bool rightmost;
- TupleDesc tupd;
-};
+ Relation rel;
+ ua_page_stats *uargs;
+ FuncCallContext *fctx;
+ MemoryContext mctx;
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to use pageinspect functions")));
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ text *relname = PG_GETARG_TEXT_PP(0);
+ int64 blkno = PG_GETARG_INT64(1);
+ int64 blk_count = PG_GETARG_INT64(2);
+ RangeVar *relrv;
+
+ fctx = SRF_FIRSTCALL_INIT();
+
+ relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
+ rel = relation_openrv(relrv, AccessShareLock);
+
+ /* Check that rel is a valid btree index and 1st block number is OK */
+ bt_index_block_validate(rel, blkno);
+
+ /* Save arguments for reuse */
+ mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
+
+ uargs = palloc(sizeof(ua_page_stats));
+
+ uargs->relid = RelationGetRelid(rel);
+ uargs->blkno = blkno;
+ uargs->blk_count = blk_count;
+
+ fctx->user_fctx = uargs;
+
+ MemoryContextSwitchTo(mctx);
+
+ /*
+ * To avoid possibly leaking a relcache reference if the SRF isn't run
+ * to completion, we close and re-open the index rel each time
+ * through, using the index's OID for re-opens to ensure we get the
+ * same rel. Keep the AccessShareLock though, to ensure it doesn't go
+ * away underneath us.
+ */
+ relation_close(rel, NoLock);
+ }
+
+ fctx = SRF_PERCALL_SETUP();
+ uargs = fctx->user_fctx;
+
+ /* We need to fetch next block statistics */
+ if (uargs->blk_count > 0)
+ {
+ Buffer buffer;
+ Datum result;
+ HeapTuple tuple;
+ int j;
+ char *values[11];
+ BTPageStat stat;
+ TupleDesc tupleDesc;
+
+ /* We should have lock already */
+ rel = relation_open(uargs->relid, NoLock);
+
+ /*
+ * Check that the next block number is valid --- we could have stepped
+ * off the end, or index could have gotten shorter.
+ */
+ CHECK_RELATION_BLOCK_RANGE(rel, uargs->blkno);
+
+ buffer = ReadBuffer(rel, uargs->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(uargs->blkno, buffer, &stat);
+
+ UnlockReleaseBuffer(buffer);
+ relation_close(rel, NoLock);
+
+ /* 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("%u", stat.blkno);
+ values[j++] = psprintf("%c", stat.type);
+ values[j++] = psprintf("%u", stat.live_items);
+ values[j++] = psprintf("%u", stat.dead_items);
+ values[j++] = psprintf("%u", stat.avg_item_size);
+ values[j++] = psprintf("%u", stat.page_size);
+ values[j++] = psprintf("%u", stat.free_size);
+ values[j++] = psprintf("%u", stat.btpo_prev);
+ values[j++] = psprintf("%u", stat.btpo_next);
+ values[j++] = psprintf("%u", stat.btpo_level);
+ values[j++] = psprintf("%d", stat.btpo_flags);
+
+ /* Construct tuple to be returned */
+ tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
+ values);
+
+ result = HeapTupleGetDatum(tuple);
+
+ /*
+ * Move to the next block number and decrement the number of blocks
+ * still to be fetched
+ */
+ uargs->blkno++;
+ uargs->blk_count--;
+
+ SRF_RETURN_NEXT(fctx, result);
+ }
+
+ SRF_RETURN_DONE(fctx);
+}
/*-------------------------------------------------------
* bt_page_print_tuples()
@@ -303,7 +457,7 @@ struct user_args
* ------------------------------------------------------
*/
static Datum
-bt_page_print_tuples(struct user_args *uargs)
+bt_page_print_tuples(ua_page_items *uargs)
{
Page page = uargs->page;
OffsetNumber offset = uargs->offset;
@@ -453,7 +607,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
Datum result;
FuncCallContext *fctx;
MemoryContext mctx;
- struct user_args *uargs;
+ ua_page_items *uargs;
if (!superuser())
ereport(ERROR,
@@ -473,33 +627,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
rel = relation_openrv(relrv, AccessShareLock);
- if (!IS_INDEX(rel) || !IS_BTREE(rel))
- ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is not a %s index",
- RelationGetRelationName(rel), "btree")));
-
- /*
- * 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 || blkno > MaxBlockNumber)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("invalid block number")));
-
- if (blkno == 0)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("block 0 is a meta page")));
-
- CHECK_RELATION_BLOCK_RANGE(rel, blkno);
+ bt_index_block_validate(rel, blkno);
buffer = ReadBuffer(rel, blkno);
LockBuffer(buffer, BUFFER_LOCK_SHARE);
@@ -511,7 +639,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
*/
mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
- uargs = palloc(sizeof(struct user_args));
+ uargs = palloc(sizeof(ua_page_items));
uargs->page = palloc(BLCKSZ);
memcpy(uargs->page, BufferGetPage(buffer), BLCKSZ);
@@ -587,7 +715,7 @@ bt_page_items_bytea(PG_FUNCTION_ARGS)
bytea *raw_page = PG_GETARG_BYTEA_P(0);
Datum result;
FuncCallContext *fctx;
- struct user_args *uargs;
+ ua_page_items *uargs;
if (!superuser())
ereport(ERROR,
@@ -603,7 +731,7 @@ bt_page_items_bytea(PG_FUNCTION_ARGS)
fctx = SRF_FIRSTCALL_INIT();
mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
- uargs = palloc(sizeof(struct user_args));
+ uargs = palloc(sizeof(ua_page_items));
uargs->page = get_page_from_raw(raw_page);
diff --git a/contrib/pageinspect/expected/btree.out b/contrib/pageinspect/expected/btree.out
index 035a81a759..de747f1751 100644
--- a/contrib/pageinspect/expected/btree.out
+++ b/contrib/pageinspect/expected/btree.out
@@ -34,6 +34,118 @@ btpo_flags | 3
SELECT * FROM bt_page_stats('test1_a_idx', 2);
ERROR: block number out of range
+-- bt_multi_page_stats() function returns a set of records of page statistics.
+CREATE TABLE test2 AS (SELECT generate_series(1, 5000) AS col1);
+CREATE INDEX test2_col1_idx ON test2(col1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 0, 1);
+ERROR: block 0 is a meta page
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, -1);
+(0 rows)
+
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 0);
+(0 rows)
+
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 2);
+-[ RECORD 1 ]-+-----
+blkno | 1
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 0
+btpo_next | 2
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 2 ]-+-----
+blkno | 2
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 1
+btpo_next | 4
+btpo_level | 0
+btpo_flags | 1
+
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 2, 6);
+-[ RECORD 1 ]-+-----
+blkno | 2
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 1
+btpo_next | 4
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 2 ]-+-----
+blkno | 3
+type | r
+live_items | 14
+dead_items | 0
+avg_item_size | 15
+page_size | 8192
+free_size | 7876
+btpo_prev | 0
+btpo_next | 0
+btpo_level | 1
+btpo_flags | 2
+-[ RECORD 3 ]-+-----
+blkno | 4
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 2
+btpo_next | 5
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 4 ]-+-----
+blkno | 5
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 4
+btpo_next | 6
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 5 ]-+-----
+blkno | 6
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 5
+btpo_next | 7
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 6 ]-+-----
+blkno | 7
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 6
+btpo_next | 8
+btpo_level | 0
+btpo_flags | 1
+
+DROP TABLE test2;
SELECT * FROM bt_page_items('test1_a_idx', -1);
ERROR: invalid block number
SELECT * FROM bt_page_items('test1_a_idx', 0);
diff --git a/contrib/pageinspect/pageinspect--1.10--1.11.sql b/contrib/pageinspect/pageinspect--1.10--1.11.sql
new file mode 100644
index 0000000000..976d029de9
--- /dev/null
+++ b/contrib/pageinspect/pageinspect--1.10--1.11.sql
@@ -0,0 +1,23 @@
+/* contrib/pageinspect/pageinspect--1.10--1.11.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pageinspect UPDATE TO '1.11'" to load this file. \quit
+
+--
+-- bt_multi_page_stats()
+--
+CREATE FUNCTION bt_multi_page_stats(IN relname text, IN blkno int8, IN blk_count int8,
+ OUT blkno int8,
+ 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 int8,
+ OUT btpo_next int8,
+ OUT btpo_level int8,
+ OUT btpo_flags int4)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'bt_multi_page_stats'
+LANGUAGE C STRICT PARALLEL SAFE;
diff --git a/contrib/pageinspect/pageinspect.control b/contrib/pageinspect/pageinspect.control
index 7cdf37913d..f277413dd8 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.10'
+default_version = '1.11'
module_pathname = '$libdir/pageinspect'
relocatable = true
diff --git a/contrib/pageinspect/sql/btree.sql b/contrib/pageinspect/sql/btree.sql
index 1f554f0f67..9a959142cd 100644
--- a/contrib/pageinspect/sql/btree.sql
+++ b/contrib/pageinspect/sql/btree.sql
@@ -11,6 +11,16 @@ SELECT * FROM bt_page_stats('test1_a_idx', 0);
SELECT * FROM bt_page_stats('test1_a_idx', 1);
SELECT * FROM bt_page_stats('test1_a_idx', 2);
+-- bt_multi_page_stats() function returns a set of records of page statistics.
+CREATE TABLE test2 AS (SELECT generate_series(1, 5000) AS col1);
+CREATE INDEX test2_col1_idx ON test2(col1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 0, 1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, -1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 0);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 2);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 2, 6);
+DROP TABLE test2;
+
SELECT * FROM bt_page_items('test1_a_idx', -1);
SELECT * FROM bt_page_items('test1_a_idx', 0);
SELECT * FROM bt_page_items('test1_a_idx', 1);
I wrote:
3. I think it's highly likely that the new test case is not portable.
In particular a machine with MAXALIGN 4 would be likely to put a
different number of tuples per page, or do the page split differently
so that the page with fewer index tuples isn't page 3. Unfortunately
I don't seem to have a working setup like that right at the moment
to verify; but I'd counsel trying this inside a VM or something to
see if it's actually likely to survive on the buildfarm.
I spun up a 32-bit VM, since that had been on my to-do list anyway,
and it looks like I was right:
diff -U3 /usr/home/tgl/pgsql/contrib/pageinspect/expected/btree.out /usr/home/tgl/pgsql/contrib/pageinspect/results/btree.out
--- /usr/home/tgl/pgsql/contrib/pageinspect/expected/btree.out 2022-09-12 15:15:40.432135000 -0400
+++ /usr/home/tgl/pgsql/contrib/pageinspect/results/btree.out 2022-09-12 15:15:54.481549000 -0400
@@ -49,11 +49,11 @@
-[ RECORD 1 ]-+-----
blkno | 1
type | l
-live_items | 367
+live_items | 458
dead_items | 0
-avg_item_size | 16
+avg_item_size | 12
page_size | 8192
-free_size | 808
+free_size | 820
btpo_prev | 0
btpo_next | 2
btpo_level | 0
@@ -61,11 +61,11 @@
... etc etc ...
regards, tom lane
On Mon, Sep 12, 2022 at 03:18:59PM -0400, Tom Lane wrote:
I spun up a 32-bit VM, since that had been on my to-do list anyway,
and it looks like I was right:
This feedback has not been addressed and the thread is idle four
weeks, so I have marked this CF entry as RwF. Please feel free to
resubmit once a new version of the patch is available.
--
Michael
On Wed, 12 Oct 2022 at 10:51, Michael Paquier <michael@paquier.xyz> wrote:
On Mon, Sep 12, 2022 at 03:18:59PM -0400, Tom Lane wrote:
I spun up a 32-bit VM, since that had been on my to-do list anyway,
and it looks like I was right:This feedback has not been addressed and the thread is idle four
weeks, so I have marked this CF entry as RwF. Please feel free to
resubmit once a new version of the patch is available.
Attaching the version 5 of the patch that addresses all 3 points raised by
Tom Lane earlier in the thread.
(1) Documentation is added.
(2) Passing "-1" for the number of blocks required now returns all the
remaining index pages after the starting block.
(3) The newly added test cases work for both 32-bit and 64-bit systems.
Show quoted text
--
Michael
Attachments:
pageinspect_btree_multipagestats-v5.patchapplication/octet-stream; name=pageinspect_btree_multipagestats-v5.patchDownload
diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile
index 5c0736564a..069683caf6 100644
--- a/contrib/pageinspect/Makefile
+++ b/contrib/pageinspect/Makefile
@@ -13,7 +13,9 @@ OBJS = \
rawpage.o
EXTENSION = pageinspect
-DATA = pageinspect--1.9--1.10.sql pageinspect--1.8--1.9.sql \
+DATA = \
+ pageinspect--1.10--1.11.sql \
+ pageinspect--1.9--1.10.sql pageinspect--1.8--1.9.sql \
pageinspect--1.7--1.8.sql pageinspect--1.6--1.7.sql \
pageinspect--1.5.sql pageinspect--1.5--1.6.sql \
pageinspect--1.4--1.5.sql pageinspect--1.3--1.4.sql \
diff --git a/contrib/pageinspect/btreefuncs.c b/contrib/pageinspect/btreefuncs.c
index 9375d55e14..53e5f5921f 100644
--- a/contrib/pageinspect/btreefuncs.c
+++ b/contrib/pageinspect/btreefuncs.c
@@ -46,6 +46,7 @@ PG_FUNCTION_INFO_V1(bt_page_items);
PG_FUNCTION_INFO_V1(bt_page_items_bytea);
PG_FUNCTION_INFO_V1(bt_page_stats_1_9);
PG_FUNCTION_INFO_V1(bt_page_stats);
+PG_FUNCTION_INFO_V1(bt_multi_page_stats);
#define IS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)
#define IS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID)
@@ -55,7 +56,7 @@ PG_FUNCTION_INFO_V1(bt_page_stats);
/* 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"); }
+ elog(ERROR, "block number " INT64_FORMAT " is out of range", blkno); }
/* ------------------------------------------------
* structure for single btree page statistics
@@ -80,6 +81,29 @@ typedef struct BTPageStat
BTCycleId btpo_cycleid;
} BTPageStat;
+/*
+ * cross-call data structure for SRF for page stats
+ */
+typedef struct ua_page_stats
+{
+ Oid relid;
+ int64 blkno;
+ int64 blk_count;
+ bool allpages;
+} ua_page_stats;
+
+/*
+ * cross-call data structure for SRF for page items
+ */
+typedef struct ua_page_items
+{
+ Page page;
+ OffsetNumber offset;
+ bool leafpage;
+ bool rightmost;
+ TupleDesc tupd;
+} ua_page_items;
+
/* -------------------------------------------------
* GetBTPageStatistics()
@@ -177,34 +201,15 @@ GetBTPageStatistics(BlockNumber blkno, Buffer buffer, BTPageStat *stat)
}
/* -----------------------------------------------
- * bt_page_stats()
+ * bt_index_block_validate()
*
- * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1);
+ * Validate index type is btree and block number
+ * is within a valid block number range.
* -----------------------------------------------
*/
-static Datum
-bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
+static void
+bt_index_block_validate(Relation rel, int64 blkno)
{
- text *relname = PG_GETARG_TEXT_PP(0);
- int64 blkno = (ext_version == PAGEINSPECT_V1_8 ? PG_GETARG_UINT32(1) : PG_GETARG_INT64(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))
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -232,6 +237,39 @@ bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
errmsg("block 0 is a meta page")));
CHECK_RELATION_BLOCK_RANGE(rel, blkno);
+}
+
+/* -----------------------------------------------
+ * bt_page_stats()
+ *
+ * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1);
+ * Arguments are index relation name and block number
+ * -----------------------------------------------
+ */
+static Datum
+bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
+{
+ text *relname = PG_GETARG_TEXT_PP(0);
+ int64 blkno = (ext_version == PAGEINSPECT_V1_8 ? PG_GETARG_UINT32(1) : PG_GETARG_INT64(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);
+
+ bt_index_block_validate(rel, blkno);
buffer = ReadBuffer(rel, blkno);
LockBuffer(buffer, BUFFER_LOCK_SHARE);
@@ -284,17 +322,145 @@ bt_page_stats(PG_FUNCTION_ARGS)
}
-/*
- * cross-call data structure for SRF
+/* -----------------------------------------------
+ * bt_multi_page_stats()
+ *
+ * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1, 2);
+ * Arguments are index relation name, first block number, number of blocks
+ * -----------------------------------------------
*/
-struct user_args
+Datum
+bt_multi_page_stats(PG_FUNCTION_ARGS)
{
- Page page;
- OffsetNumber offset;
- bool leafpage;
- bool rightmost;
- TupleDesc tupd;
-};
+ Relation rel;
+ ua_page_stats *uargs;
+ FuncCallContext *fctx;
+ MemoryContext mctx;
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to use pageinspect functions")));
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ text *relname = PG_GETARG_TEXT_PP(0);
+ int64 blkno = PG_GETARG_INT64(1);
+ int64 blk_count = PG_GETARG_INT64(2);
+ RangeVar *relrv;
+
+ fctx = SRF_FIRSTCALL_INIT();
+
+ relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
+ rel = relation_openrv(relrv, AccessShareLock);
+
+ /* Check that rel is a valid btree index and 1st block number is OK */
+ bt_index_block_validate(rel, blkno);
+
+ /*
+ * Check if upperbound of the specified range is valid. If only one
+ * page is requested, skip as we've already validated the page.
+ */
+ if (blk_count > 1)
+ CHECK_RELATION_BLOCK_RANGE(rel, blkno + blk_count - 1);
+
+ /* Save arguments for reuse */
+ mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
+
+ uargs = palloc(sizeof(ua_page_stats));
+
+ uargs->relid = RelationGetRelid(rel);
+ uargs->blkno = blkno;
+ uargs->allpages = (blk_count < 0);
+
+ if (uargs->allpages)
+ uargs->blk_count = RelationGetNumberOfBlocks(rel) - uargs->blkno;
+ else
+ uargs->blk_count = blk_count;
+
+ fctx->user_fctx = uargs;
+
+ MemoryContextSwitchTo(mctx);
+
+ /*
+ * To avoid possibly leaking a relcache reference if the SRF isn't run
+ * to completion, we close and re-open the index rel each time
+ * through, using the index's OID for re-opens to ensure we get the
+ * same rel. Keep the AccessShareLock though, to ensure it doesn't go
+ * away underneath us.
+ */
+ relation_close(rel, NoLock);
+ }
+
+ fctx = SRF_PERCALL_SETUP();
+ uargs = fctx->user_fctx;
+
+
+ /* We should have lock already */
+ rel = relation_open(uargs->relid, NoLock);
+
+ if (uargs->allpages)
+ uargs->blk_count = RelationGetNumberOfBlocks(rel) - uargs->blkno;
+
+ /* We need to fetch next block statistics */
+ if (uargs->blk_count > 0)
+ {
+ Buffer buffer;
+ Datum result;
+ HeapTuple tuple;
+ int j;
+ char *values[11];
+ BTPageStat stat;
+ TupleDesc tupleDesc;
+
+ buffer = ReadBuffer(rel, uargs->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(uargs->blkno, buffer, &stat);
+
+ UnlockReleaseBuffer(buffer);
+ relation_close(rel, NoLock);
+
+ /* 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("%u", stat.blkno);
+ values[j++] = psprintf("%c", stat.type);
+ values[j++] = psprintf("%u", stat.live_items);
+ values[j++] = psprintf("%u", stat.dead_items);
+ values[j++] = psprintf("%u", stat.avg_item_size);
+ values[j++] = psprintf("%u", stat.page_size);
+ values[j++] = psprintf("%u", stat.free_size);
+ values[j++] = psprintf("%u", stat.btpo_prev);
+ values[j++] = psprintf("%u", stat.btpo_next);
+ values[j++] = psprintf("%u", stat.btpo_level);
+ values[j++] = psprintf("%d", stat.btpo_flags);
+
+ /* Construct tuple to be returned */
+ tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
+ values);
+
+ result = HeapTupleGetDatum(tuple);
+
+ /*
+ * Move to the next block number and decrement the number of blocks
+ * still to be fetched
+ */
+ uargs->blkno++;
+ uargs->blk_count--;
+
+ SRF_RETURN_NEXT(fctx, result);
+ }
+
+ relation_close(rel, NoLock);
+ SRF_RETURN_DONE(fctx);
+}
/*-------------------------------------------------------
* bt_page_print_tuples()
@@ -303,7 +469,7 @@ struct user_args
* ------------------------------------------------------
*/
static Datum
-bt_page_print_tuples(struct user_args *uargs)
+bt_page_print_tuples(ua_page_items *uargs)
{
Page page = uargs->page;
OffsetNumber offset = uargs->offset;
@@ -453,7 +619,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
Datum result;
FuncCallContext *fctx;
MemoryContext mctx;
- struct user_args *uargs;
+ ua_page_items *uargs;
if (!superuser())
ereport(ERROR,
@@ -473,33 +639,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
rel = relation_openrv(relrv, AccessShareLock);
- if (!IS_INDEX(rel) || !IS_BTREE(rel))
- ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is not a %s index",
- RelationGetRelationName(rel), "btree")));
-
- /*
- * 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 || blkno > MaxBlockNumber)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("invalid block number")));
-
- if (blkno == 0)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("block 0 is a meta page")));
-
- CHECK_RELATION_BLOCK_RANGE(rel, blkno);
+ bt_index_block_validate(rel, blkno);
buffer = ReadBuffer(rel, blkno);
LockBuffer(buffer, BUFFER_LOCK_SHARE);
@@ -511,7 +651,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
*/
mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
- uargs = palloc(sizeof(struct user_args));
+ uargs = palloc(sizeof(ua_page_items));
uargs->page = palloc(BLCKSZ);
memcpy(uargs->page, BufferGetPage(buffer), BLCKSZ);
@@ -587,7 +727,7 @@ bt_page_items_bytea(PG_FUNCTION_ARGS)
bytea *raw_page = PG_GETARG_BYTEA_P(0);
Datum result;
FuncCallContext *fctx;
- struct user_args *uargs;
+ ua_page_items *uargs;
if (!superuser())
ereport(ERROR,
@@ -603,7 +743,7 @@ bt_page_items_bytea(PG_FUNCTION_ARGS)
fctx = SRF_FIRSTCALL_INIT();
mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
- uargs = palloc(sizeof(struct user_args));
+ uargs = palloc(sizeof(ua_page_items));
uargs->page = get_page_from_raw(raw_page);
diff --git a/contrib/pageinspect/expected/btree.out b/contrib/pageinspect/expected/btree.out
index 035a81a759..97f2f4e2fa 100644
--- a/contrib/pageinspect/expected/btree.out
+++ b/contrib/pageinspect/expected/btree.out
@@ -33,7 +33,120 @@ btpo_level | 0
btpo_flags | 3
SELECT * FROM bt_page_stats('test1_a_idx', 2);
-ERROR: block number out of range
+ERROR: block number 2 is out of range
+-- bt_multi_page_stats() function returns a set of records of page statistics.
+CREATE TABLE test2 AS (SELECT generate_series(1, 1000)::int8 AS col1);
+CREATE INDEX test2_col1_idx ON test2(col1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 0, 1);
+ERROR: block 0 is a meta page
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, -1);
+-[ RECORD 1 ]-+-----
+blkno | 1
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 0
+btpo_next | 2
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 2 ]-+-----
+blkno | 2
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 1
+btpo_next | 4
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 3 ]-+-----
+blkno | 3
+type | r
+live_items | 3
+dead_items | 0
+avg_item_size | 13
+page_size | 8192
+free_size | 8096
+btpo_prev | 0
+btpo_next | 0
+btpo_level | 1
+btpo_flags | 2
+-[ RECORD 4 ]-+-----
+blkno | 4
+type | l
+live_items | 268
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 2788
+btpo_prev | 2
+btpo_next | 0
+btpo_level | 0
+btpo_flags | 1
+
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 0);
+(0 rows)
+
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 2);
+-[ RECORD 1 ]-+-----
+blkno | 1
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 0
+btpo_next | 2
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 2 ]-+-----
+blkno | 2
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 1
+btpo_next | 4
+btpo_level | 0
+btpo_flags | 1
+
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 3, 2);
+-[ RECORD 1 ]-+-----
+blkno | 3
+type | r
+live_items | 3
+dead_items | 0
+avg_item_size | 13
+page_size | 8192
+free_size | 8096
+btpo_prev | 0
+btpo_next | 0
+btpo_level | 1
+btpo_flags | 2
+-[ RECORD 2 ]-+-----
+blkno | 4
+type | l
+live_items | 268
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 2788
+btpo_prev | 2
+btpo_next | 0
+btpo_level | 0
+btpo_flags | 1
+
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 7, 2);
+ERROR: block number 7 is out of range
+DROP TABLE test2;
SELECT * FROM bt_page_items('test1_a_idx', -1);
ERROR: invalid block number
SELECT * FROM bt_page_items('test1_a_idx', 0);
@@ -51,7 +164,7 @@ htid | (0,1)
tids |
SELECT * FROM bt_page_items('test1_a_idx', 2);
-ERROR: block number out of range
+ERROR: block number 2 is out of range
SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', -1));
ERROR: invalid block number
SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 0));
diff --git a/contrib/pageinspect/pageinspect--1.10--1.11.sql b/contrib/pageinspect/pageinspect--1.10--1.11.sql
new file mode 100644
index 0000000000..976d029de9
--- /dev/null
+++ b/contrib/pageinspect/pageinspect--1.10--1.11.sql
@@ -0,0 +1,23 @@
+/* contrib/pageinspect/pageinspect--1.10--1.11.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pageinspect UPDATE TO '1.11'" to load this file. \quit
+
+--
+-- bt_multi_page_stats()
+--
+CREATE FUNCTION bt_multi_page_stats(IN relname text, IN blkno int8, IN blk_count int8,
+ OUT blkno int8,
+ 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 int8,
+ OUT btpo_next int8,
+ OUT btpo_level int8,
+ OUT btpo_flags int4)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'bt_multi_page_stats'
+LANGUAGE C STRICT PARALLEL SAFE;
diff --git a/contrib/pageinspect/pageinspect.control b/contrib/pageinspect/pageinspect.control
index 7cdf37913d..f277413dd8 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.10'
+default_version = '1.11'
module_pathname = '$libdir/pageinspect'
relocatable = true
diff --git a/contrib/pageinspect/sql/btree.sql b/contrib/pageinspect/sql/btree.sql
index 1f554f0f67..102ebdefe3 100644
--- a/contrib/pageinspect/sql/btree.sql
+++ b/contrib/pageinspect/sql/btree.sql
@@ -11,6 +11,17 @@ SELECT * FROM bt_page_stats('test1_a_idx', 0);
SELECT * FROM bt_page_stats('test1_a_idx', 1);
SELECT * FROM bt_page_stats('test1_a_idx', 2);
+-- bt_multi_page_stats() function returns a set of records of page statistics.
+CREATE TABLE test2 AS (SELECT generate_series(1, 1000)::int8 AS col1);
+CREATE INDEX test2_col1_idx ON test2(col1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 0, 1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, -1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 0);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 2);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 3, 2);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 7, 2);
+DROP TABLE test2;
+
SELECT * FROM bt_page_items('test1_a_idx', -1);
SELECT * FROM bt_page_items('test1_a_idx', 0);
SELECT * FROM bt_page_items('test1_a_idx', 1);
diff --git a/doc/src/sgml/pageinspect.sgml b/doc/src/sgml/pageinspect.sgml
index d4ee34ee0f..cf267424e9 100644
--- a/doc/src/sgml/pageinspect.sgml
+++ b/doc/src/sgml/pageinspect.sgml
@@ -346,6 +346,53 @@ btpo_flags | 3
</listitem>
</varlistentry>
+ <varlistentry>
+ <term>
+ <function>bt_multi_page_stats(relname text, blkno bigint, blk_count bigint) returns setof record</function>
+ <indexterm>
+ <primary>bt_multi_page_stats</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ <function>bt_multi_page_stats</function> returns summary information
+ about the <structfield>blk_count</structfield> number of B-tree index
+ pages starting from <structfield>blkno</structfield> page. A value of
+ <literal>-1</literal> for <structfield>blk_count</structfield> returns
+ summary information for all index pages starting from
+ <structfield>blkno</structfield> page. For example:
+<screen>
+test=# SELECT * FROM bt_multi_page_stats('pg_proc_oid_index', 5, 2);
+-[ RECORD 1 ]-+-----
+blkno | 5
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 4
+btpo_next | 6
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 2 ]-+-----
+blkno | 6
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 5
+btpo_next | 7
+btpo_level | 0
+btpo_flags | 1
+</screen>
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term>
<function>bt_page_items(relname text, blkno bigint) returns setof record</function>
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: not tested
Documentation: not tested
i've tested and verified the documentation.
On Mon, 21 Nov 2022 at 17:34, Naeem Akhter <naeem.akhter@percona.com> wrote:
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: not tested
Documentation: not testedi've tested and verified the documentation.
Rebasing the patch to the tip of the master branch.
Attachments:
pageinspect_btree_multipagestats-v6.patchapplication/octet-stream; name=pageinspect_btree_multipagestats-v6.patchDownload
diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile
index ad5a3ac511..95e030b396 100644
--- a/contrib/pageinspect/Makefile
+++ b/contrib/pageinspect/Makefile
@@ -13,7 +13,7 @@ OBJS = \
rawpage.o
EXTENSION = pageinspect
-DATA = pageinspect--1.10--1.11.sql \
+DATA = pageinspect--1.11--1.12.sql pageinspect--1.10--1.11.sql \
pageinspect--1.9--1.10.sql pageinspect--1.8--1.9.sql \
pageinspect--1.7--1.8.sql pageinspect--1.6--1.7.sql \
pageinspect--1.5.sql pageinspect--1.5--1.6.sql \
diff --git a/contrib/pageinspect/btreefuncs.c b/contrib/pageinspect/btreefuncs.c
index 9375d55e14..e2cbed7377 100644
--- a/contrib/pageinspect/btreefuncs.c
+++ b/contrib/pageinspect/btreefuncs.c
@@ -46,6 +46,7 @@ PG_FUNCTION_INFO_V1(bt_page_items);
PG_FUNCTION_INFO_V1(bt_page_items_bytea);
PG_FUNCTION_INFO_V1(bt_page_stats_1_9);
PG_FUNCTION_INFO_V1(bt_page_stats);
+PG_FUNCTION_INFO_V1(bt_multi_page_stats);
#define IS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)
#define IS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID)
@@ -55,7 +56,7 @@ PG_FUNCTION_INFO_V1(bt_page_stats);
/* 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"); }
+ elog(ERROR, "block number " INT64_FORMAT " is out of range", blkno); }
/* ------------------------------------------------
* structure for single btree page statistics
@@ -80,6 +81,29 @@ typedef struct BTPageStat
BTCycleId btpo_cycleid;
} BTPageStat;
+/*
+ * cross-call data structure for SRF for page stats
+ */
+typedef struct ua_page_stats
+{
+ Oid relid;
+ int64 blkno;
+ int64 blk_count;
+ bool allpages;
+} ua_page_stats;
+
+/*
+ * cross-call data structure for SRF for page items
+ */
+typedef struct ua_page_items
+{
+ Page page;
+ OffsetNumber offset;
+ bool leafpage;
+ bool rightmost;
+ TupleDesc tupd;
+} ua_page_items;
+
/* -------------------------------------------------
* GetBTPageStatistics()
@@ -177,34 +201,15 @@ GetBTPageStatistics(BlockNumber blkno, Buffer buffer, BTPageStat *stat)
}
/* -----------------------------------------------
- * bt_page_stats()
+ * bt_index_block_validate()
*
- * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1);
+ * Validate index type is btree and block number
+ * is within a valid block number range.
* -----------------------------------------------
*/
-static Datum
-bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
+static void
+bt_index_block_validate(Relation rel, int64 blkno)
{
- text *relname = PG_GETARG_TEXT_PP(0);
- int64 blkno = (ext_version == PAGEINSPECT_V1_8 ? PG_GETARG_UINT32(1) : PG_GETARG_INT64(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))
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -232,6 +237,39 @@ bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
errmsg("block 0 is a meta page")));
CHECK_RELATION_BLOCK_RANGE(rel, blkno);
+}
+
+/* -----------------------------------------------
+ * bt_page_stats()
+ *
+ * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1);
+ * Arguments are index relation name and block number
+ * -----------------------------------------------
+ */
+static Datum
+bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
+{
+ text *relname = PG_GETARG_TEXT_PP(0);
+ int64 blkno = (ext_version == PAGEINSPECT_V1_8 ? PG_GETARG_UINT32(1) : PG_GETARG_INT64(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);
+
+ bt_index_block_validate(rel, blkno);
buffer = ReadBuffer(rel, blkno);
LockBuffer(buffer, BUFFER_LOCK_SHARE);
@@ -284,17 +322,145 @@ bt_page_stats(PG_FUNCTION_ARGS)
}
-/*
- * cross-call data structure for SRF
+/* -----------------------------------------------
+ * bt_multi_page_stats()
+ *
+ * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1, 2);
+ * Arguments are index relation name, first block number, number of blocks
+ * -----------------------------------------------
*/
-struct user_args
+Datum
+bt_multi_page_stats(PG_FUNCTION_ARGS)
{
- Page page;
- OffsetNumber offset;
- bool leafpage;
- bool rightmost;
- TupleDesc tupd;
-};
+ Relation rel;
+ ua_page_stats *uargs;
+ FuncCallContext *fctx;
+ MemoryContext mctx;
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to use pageinspect functions")));
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ text *relname = PG_GETARG_TEXT_PP(0);
+ int64 blkno = PG_GETARG_INT64(1);
+ int64 blk_count = PG_GETARG_INT64(2);
+ RangeVar *relrv;
+
+ fctx = SRF_FIRSTCALL_INIT();
+
+ relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
+ rel = relation_openrv(relrv, AccessShareLock);
+
+ /* Check that rel is a valid btree index and 1st block number is OK */
+ bt_index_block_validate(rel, blkno);
+
+ /*
+ * Check if upperbound of the specified range is valid. If only one
+ * page is requested, skip as we've already validated the page.
+ */
+ if (blk_count > 1)
+ CHECK_RELATION_BLOCK_RANGE(rel, blkno + blk_count - 1);
+
+ /* Save arguments for reuse */
+ mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
+
+ uargs = palloc(sizeof(ua_page_stats));
+
+ uargs->relid = RelationGetRelid(rel);
+ uargs->blkno = blkno;
+ uargs->allpages = (blk_count < 0);
+
+ if (uargs->allpages)
+ uargs->blk_count = RelationGetNumberOfBlocks(rel) - uargs->blkno;
+ else
+ uargs->blk_count = blk_count;
+
+ fctx->user_fctx = uargs;
+
+ MemoryContextSwitchTo(mctx);
+
+ /*
+ * To avoid possibly leaking a relcache reference if the SRF isn't run
+ * to completion, we close and re-open the index rel each time
+ * through, using the index's OID for re-opens to ensure we get the
+ * same rel. Keep the AccessShareLock though, to ensure it doesn't go
+ * away underneath us.
+ */
+ relation_close(rel, NoLock);
+ }
+
+ fctx = SRF_PERCALL_SETUP();
+ uargs = fctx->user_fctx;
+
+
+ /* We should have lock already */
+ rel = relation_open(uargs->relid, NoLock);
+
+ if (uargs->allpages)
+ uargs->blk_count = RelationGetNumberOfBlocks(rel) - uargs->blkno;
+
+ /* We need to fetch next block statistics */
+ if (uargs->blk_count > 0)
+ {
+ Buffer buffer;
+ Datum result;
+ HeapTuple tuple;
+ int j;
+ char *values[11];
+ BTPageStat stat;
+ TupleDesc tupleDesc;
+
+ buffer = ReadBuffer(rel, uargs->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(uargs->blkno, buffer, &stat);
+
+ UnlockReleaseBuffer(buffer);
+ relation_close(rel, NoLock);
+
+ /* 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("%u", stat.blkno);
+ values[j++] = psprintf("%c", stat.type);
+ values[j++] = psprintf("%u", stat.live_items);
+ values[j++] = psprintf("%u", stat.dead_items);
+ values[j++] = psprintf("%u", stat.avg_item_size);
+ values[j++] = psprintf("%u", stat.page_size);
+ values[j++] = psprintf("%u", stat.free_size);
+ values[j++] = psprintf("%u", stat.btpo_prev);
+ values[j++] = psprintf("%u", stat.btpo_next);
+ values[j++] = psprintf("%u", stat.btpo_level);
+ values[j++] = psprintf("%d", stat.btpo_flags);
+
+ /* Construct tuple to be returned */
+ tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
+ values);
+
+ result = HeapTupleGetDatum(tuple);
+
+ /*
+ * Move to the next block number and decrement the number of blocks
+ * still to be fetched
+ */
+ uargs->blkno++;
+ uargs->blk_count--;
+
+ SRF_RETURN_NEXT(fctx, result);
+ }
+
+ relation_close(rel, NoLock);
+ SRF_RETURN_DONE(fctx);
+}
/*-------------------------------------------------------
* bt_page_print_tuples()
@@ -303,7 +469,7 @@ struct user_args
* ------------------------------------------------------
*/
static Datum
-bt_page_print_tuples(struct user_args *uargs)
+bt_page_print_tuples(ua_page_items *uargs)
{
Page page = uargs->page;
OffsetNumber offset = uargs->offset;
@@ -453,7 +619,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
Datum result;
FuncCallContext *fctx;
MemoryContext mctx;
- struct user_args *uargs;
+ ua_page_items *uargs;
if (!superuser())
ereport(ERROR,
@@ -473,33 +639,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
rel = relation_openrv(relrv, AccessShareLock);
- if (!IS_INDEX(rel) || !IS_BTREE(rel))
- ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is not a %s index",
- RelationGetRelationName(rel), "btree")));
-
- /*
- * 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 || blkno > MaxBlockNumber)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("invalid block number")));
-
- if (blkno == 0)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("block 0 is a meta page")));
-
- CHECK_RELATION_BLOCK_RANGE(rel, blkno);
+ bt_index_block_validate(rel, blkno);
buffer = ReadBuffer(rel, blkno);
LockBuffer(buffer, BUFFER_LOCK_SHARE);
@@ -511,7 +651,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
*/
mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
- uargs = palloc(sizeof(struct user_args));
+ uargs = palloc(sizeof(ua_page_items));
uargs->page = palloc(BLCKSZ);
memcpy(uargs->page, BufferGetPage(buffer), BLCKSZ);
@@ -587,7 +727,7 @@ bt_page_items_bytea(PG_FUNCTION_ARGS)
bytea *raw_page = PG_GETARG_BYTEA_P(0);
Datum result;
FuncCallContext *fctx;
- struct user_args *uargs;
+ ua_page_items *uargs;
if (!superuser())
ereport(ERROR,
@@ -603,7 +743,7 @@ bt_page_items_bytea(PG_FUNCTION_ARGS)
fctx = SRF_FIRSTCALL_INIT();
mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
- uargs = palloc(sizeof(struct user_args));
+ uargs = palloc(sizeof(ua_page_items));
uargs->page = get_page_from_raw(raw_page);
diff --git a/contrib/pageinspect/expected/btree.out b/contrib/pageinspect/expected/btree.out
index 035a81a759..97f2f4e2fa 100644
--- a/contrib/pageinspect/expected/btree.out
+++ b/contrib/pageinspect/expected/btree.out
@@ -33,7 +33,120 @@ btpo_level | 0
btpo_flags | 3
SELECT * FROM bt_page_stats('test1_a_idx', 2);
-ERROR: block number out of range
+ERROR: block number 2 is out of range
+-- bt_multi_page_stats() function returns a set of records of page statistics.
+CREATE TABLE test2 AS (SELECT generate_series(1, 1000)::int8 AS col1);
+CREATE INDEX test2_col1_idx ON test2(col1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 0, 1);
+ERROR: block 0 is a meta page
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, -1);
+-[ RECORD 1 ]-+-----
+blkno | 1
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 0
+btpo_next | 2
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 2 ]-+-----
+blkno | 2
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 1
+btpo_next | 4
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 3 ]-+-----
+blkno | 3
+type | r
+live_items | 3
+dead_items | 0
+avg_item_size | 13
+page_size | 8192
+free_size | 8096
+btpo_prev | 0
+btpo_next | 0
+btpo_level | 1
+btpo_flags | 2
+-[ RECORD 4 ]-+-----
+blkno | 4
+type | l
+live_items | 268
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 2788
+btpo_prev | 2
+btpo_next | 0
+btpo_level | 0
+btpo_flags | 1
+
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 0);
+(0 rows)
+
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 2);
+-[ RECORD 1 ]-+-----
+blkno | 1
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 0
+btpo_next | 2
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 2 ]-+-----
+blkno | 2
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 1
+btpo_next | 4
+btpo_level | 0
+btpo_flags | 1
+
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 3, 2);
+-[ RECORD 1 ]-+-----
+blkno | 3
+type | r
+live_items | 3
+dead_items | 0
+avg_item_size | 13
+page_size | 8192
+free_size | 8096
+btpo_prev | 0
+btpo_next | 0
+btpo_level | 1
+btpo_flags | 2
+-[ RECORD 2 ]-+-----
+blkno | 4
+type | l
+live_items | 268
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 2788
+btpo_prev | 2
+btpo_next | 0
+btpo_level | 0
+btpo_flags | 1
+
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 7, 2);
+ERROR: block number 7 is out of range
+DROP TABLE test2;
SELECT * FROM bt_page_items('test1_a_idx', -1);
ERROR: invalid block number
SELECT * FROM bt_page_items('test1_a_idx', 0);
@@ -51,7 +164,7 @@ htid | (0,1)
tids |
SELECT * FROM bt_page_items('test1_a_idx', 2);
-ERROR: block number out of range
+ERROR: block number 2 is out of range
SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', -1));
ERROR: invalid block number
SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 0));
diff --git a/contrib/pageinspect/pageinspect--1.11--1.12.sql b/contrib/pageinspect/pageinspect--1.11--1.12.sql
new file mode 100644
index 0000000000..f1166c5234
--- /dev/null
+++ b/contrib/pageinspect/pageinspect--1.11--1.12.sql
@@ -0,0 +1,23 @@
+/* contrib/pageinspect/pageinspect--1.11--1.12.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pageinspect UPDATE TO '1.12'" to load this file. \quit
+
+--
+-- bt_multi_page_stats()
+--
+CREATE FUNCTION bt_multi_page_stats(IN relname text, IN blkno int8, IN blk_count int8,
+ OUT blkno int8,
+ 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 int8,
+ OUT btpo_next int8,
+ OUT btpo_level int8,
+ OUT btpo_flags int4)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'bt_multi_page_stats'
+LANGUAGE C STRICT PARALLEL SAFE;
diff --git a/contrib/pageinspect/pageinspect.control b/contrib/pageinspect/pageinspect.control
index f277413dd8..b2804e9b12 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.11'
+default_version = '1.12'
module_pathname = '$libdir/pageinspect'
relocatable = true
diff --git a/contrib/pageinspect/sql/btree.sql b/contrib/pageinspect/sql/btree.sql
index 1f554f0f67..102ebdefe3 100644
--- a/contrib/pageinspect/sql/btree.sql
+++ b/contrib/pageinspect/sql/btree.sql
@@ -11,6 +11,17 @@ SELECT * FROM bt_page_stats('test1_a_idx', 0);
SELECT * FROM bt_page_stats('test1_a_idx', 1);
SELECT * FROM bt_page_stats('test1_a_idx', 2);
+-- bt_multi_page_stats() function returns a set of records of page statistics.
+CREATE TABLE test2 AS (SELECT generate_series(1, 1000)::int8 AS col1);
+CREATE INDEX test2_col1_idx ON test2(col1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 0, 1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, -1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 0);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 2);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 3, 2);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 7, 2);
+DROP TABLE test2;
+
SELECT * FROM bt_page_items('test1_a_idx', -1);
SELECT * FROM bt_page_items('test1_a_idx', 0);
SELECT * FROM bt_page_items('test1_a_idx', 1);
diff --git a/doc/src/sgml/pageinspect.sgml b/doc/src/sgml/pageinspect.sgml
index d4ee34ee0f..8ad10b60c7 100644
--- a/doc/src/sgml/pageinspect.sgml
+++ b/doc/src/sgml/pageinspect.sgml
@@ -346,6 +346,53 @@ btpo_flags | 3
</listitem>
</varlistentry>
+ <varlistentry>
+ <term>
+ <function>bt_multi_page_stats(relname text, blkno bigint, blk_count bigint) returns setof record</function>
+ <indexterm>
+ <primary>bt_multi_page_stats</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ <function>bt_multi_page_stats</function> returns summary information
+ about the <structfield>blk_count</structfield> number of B-tree index
+ pages starting from <structfield>blkno</structfield> page. A value of
+ <literal>-1</literal> for <structfield>blk_count</structfield> returns
+ summary information for all index pages starting from
+ <structfield>blkno</structfield> page. For example:
+<screen>
+test=# SELECT * FROM bt_multi_page_stats('pg_proc_oid_index', 5, 2);
+-[ RECORD 1 ]-+-----
+blkno | 5
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 4
+btpo_next | 6
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 2 ]-+-----
+blkno | 6
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 5
+btpo_next | 7
+btpo_level | 0
+btpo_flags | 1
+</screen>
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term>
<function>bt_page_items(relname text, blkno bigint) returns setof record</function>
Hi,
On 2022-11-25 02:45:01 +0500, Hamid Akhtar wrote:
Rebasing the patch to the tip of the master branch.
This doesn't pass tests on cfbot. Looks like possibly some files are missing?
diff -U3 /tmp/cirrus-ci-build/contrib/pageinspect/expected/page.out /tmp/cirrus-ci-build/build/testrun/pageinspect/regress/results/page.out
--- /tmp/cirrus-ci-build/contrib/pageinspect/expected/page.out 2022-12-06 20:07:47.691479000 +0000
+++ /tmp/cirrus-ci-build/build/testrun/pageinspect/regress/results/page.out 2022-12-06 20:11:42.955606000 +0000
@@ -1,4 +1,5 @@
CREATE EXTENSION pageinspect;
+ERROR: extension "pageinspect" has no installation script nor update path for version "1.12"
-- Use a temp table so that effects of VACUUM are predictable
CREATE TEMP TABLE test1 (a int, b int);
INSERT INTO test1 VALUES (16777217, 131584);
@@ -6,236 +7,203 @@
Greetings,
Andres Freund
On 2022-Dec-06, Andres Freund wrote:
Hi,
On 2022-11-25 02:45:01 +0500, Hamid Akhtar wrote:
Rebasing the patch to the tip of the master branch.
This doesn't pass tests on cfbot. Looks like possibly some files are missing?
The .sql file is there all right, but meson.build is not altered to be
aware of it.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
On Wed, 7 Dec 2022 at 13:01, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
On 2022-Dec-06, Andres Freund wrote:
Hi,
On 2022-11-25 02:45:01 +0500, Hamid Akhtar wrote:
Rebasing the patch to the tip of the master branch.
This doesn't pass tests on cfbot. Looks like possibly some files are
missing?
The .sql file is there all right, but meson.build is not altered to be
aware of it.
I wasn't aware of the meson.build file. Attached is the latest version of
the patch that contains the updated meson.build.
--
Hamid Akhtar,
Percona LLC, www.percona.com
Attachments:
pageinspect_btree_multipagestats-v7.patchapplication/octet-stream; name=pageinspect_btree_multipagestats-v7.patchDownload
diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile
index ad5a3ac511..95e030b396 100644
--- a/contrib/pageinspect/Makefile
+++ b/contrib/pageinspect/Makefile
@@ -13,7 +13,7 @@ OBJS = \
rawpage.o
EXTENSION = pageinspect
-DATA = pageinspect--1.10--1.11.sql \
+DATA = pageinspect--1.11--1.12.sql pageinspect--1.10--1.11.sql \
pageinspect--1.9--1.10.sql pageinspect--1.8--1.9.sql \
pageinspect--1.7--1.8.sql pageinspect--1.6--1.7.sql \
pageinspect--1.5.sql pageinspect--1.5--1.6.sql \
diff --git a/contrib/pageinspect/btreefuncs.c b/contrib/pageinspect/btreefuncs.c
index 9375d55e14..e2cbed7377 100644
--- a/contrib/pageinspect/btreefuncs.c
+++ b/contrib/pageinspect/btreefuncs.c
@@ -46,6 +46,7 @@ PG_FUNCTION_INFO_V1(bt_page_items);
PG_FUNCTION_INFO_V1(bt_page_items_bytea);
PG_FUNCTION_INFO_V1(bt_page_stats_1_9);
PG_FUNCTION_INFO_V1(bt_page_stats);
+PG_FUNCTION_INFO_V1(bt_multi_page_stats);
#define IS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)
#define IS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID)
@@ -55,7 +56,7 @@ PG_FUNCTION_INFO_V1(bt_page_stats);
/* 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"); }
+ elog(ERROR, "block number " INT64_FORMAT " is out of range", blkno); }
/* ------------------------------------------------
* structure for single btree page statistics
@@ -80,6 +81,29 @@ typedef struct BTPageStat
BTCycleId btpo_cycleid;
} BTPageStat;
+/*
+ * cross-call data structure for SRF for page stats
+ */
+typedef struct ua_page_stats
+{
+ Oid relid;
+ int64 blkno;
+ int64 blk_count;
+ bool allpages;
+} ua_page_stats;
+
+/*
+ * cross-call data structure for SRF for page items
+ */
+typedef struct ua_page_items
+{
+ Page page;
+ OffsetNumber offset;
+ bool leafpage;
+ bool rightmost;
+ TupleDesc tupd;
+} ua_page_items;
+
/* -------------------------------------------------
* GetBTPageStatistics()
@@ -177,34 +201,15 @@ GetBTPageStatistics(BlockNumber blkno, Buffer buffer, BTPageStat *stat)
}
/* -----------------------------------------------
- * bt_page_stats()
+ * bt_index_block_validate()
*
- * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1);
+ * Validate index type is btree and block number
+ * is within a valid block number range.
* -----------------------------------------------
*/
-static Datum
-bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
+static void
+bt_index_block_validate(Relation rel, int64 blkno)
{
- text *relname = PG_GETARG_TEXT_PP(0);
- int64 blkno = (ext_version == PAGEINSPECT_V1_8 ? PG_GETARG_UINT32(1) : PG_GETARG_INT64(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))
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -232,6 +237,39 @@ bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
errmsg("block 0 is a meta page")));
CHECK_RELATION_BLOCK_RANGE(rel, blkno);
+}
+
+/* -----------------------------------------------
+ * bt_page_stats()
+ *
+ * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1);
+ * Arguments are index relation name and block number
+ * -----------------------------------------------
+ */
+static Datum
+bt_page_stats_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
+{
+ text *relname = PG_GETARG_TEXT_PP(0);
+ int64 blkno = (ext_version == PAGEINSPECT_V1_8 ? PG_GETARG_UINT32(1) : PG_GETARG_INT64(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);
+
+ bt_index_block_validate(rel, blkno);
buffer = ReadBuffer(rel, blkno);
LockBuffer(buffer, BUFFER_LOCK_SHARE);
@@ -284,17 +322,145 @@ bt_page_stats(PG_FUNCTION_ARGS)
}
-/*
- * cross-call data structure for SRF
+/* -----------------------------------------------
+ * bt_multi_page_stats()
+ *
+ * Usage: SELECT * FROM bt_page_stats('t1_pkey', 1, 2);
+ * Arguments are index relation name, first block number, number of blocks
+ * -----------------------------------------------
*/
-struct user_args
+Datum
+bt_multi_page_stats(PG_FUNCTION_ARGS)
{
- Page page;
- OffsetNumber offset;
- bool leafpage;
- bool rightmost;
- TupleDesc tupd;
-};
+ Relation rel;
+ ua_page_stats *uargs;
+ FuncCallContext *fctx;
+ MemoryContext mctx;
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to use pageinspect functions")));
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ text *relname = PG_GETARG_TEXT_PP(0);
+ int64 blkno = PG_GETARG_INT64(1);
+ int64 blk_count = PG_GETARG_INT64(2);
+ RangeVar *relrv;
+
+ fctx = SRF_FIRSTCALL_INIT();
+
+ relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
+ rel = relation_openrv(relrv, AccessShareLock);
+
+ /* Check that rel is a valid btree index and 1st block number is OK */
+ bt_index_block_validate(rel, blkno);
+
+ /*
+ * Check if upperbound of the specified range is valid. If only one
+ * page is requested, skip as we've already validated the page.
+ */
+ if (blk_count > 1)
+ CHECK_RELATION_BLOCK_RANGE(rel, blkno + blk_count - 1);
+
+ /* Save arguments for reuse */
+ mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
+
+ uargs = palloc(sizeof(ua_page_stats));
+
+ uargs->relid = RelationGetRelid(rel);
+ uargs->blkno = blkno;
+ uargs->allpages = (blk_count < 0);
+
+ if (uargs->allpages)
+ uargs->blk_count = RelationGetNumberOfBlocks(rel) - uargs->blkno;
+ else
+ uargs->blk_count = blk_count;
+
+ fctx->user_fctx = uargs;
+
+ MemoryContextSwitchTo(mctx);
+
+ /*
+ * To avoid possibly leaking a relcache reference if the SRF isn't run
+ * to completion, we close and re-open the index rel each time
+ * through, using the index's OID for re-opens to ensure we get the
+ * same rel. Keep the AccessShareLock though, to ensure it doesn't go
+ * away underneath us.
+ */
+ relation_close(rel, NoLock);
+ }
+
+ fctx = SRF_PERCALL_SETUP();
+ uargs = fctx->user_fctx;
+
+
+ /* We should have lock already */
+ rel = relation_open(uargs->relid, NoLock);
+
+ if (uargs->allpages)
+ uargs->blk_count = RelationGetNumberOfBlocks(rel) - uargs->blkno;
+
+ /* We need to fetch next block statistics */
+ if (uargs->blk_count > 0)
+ {
+ Buffer buffer;
+ Datum result;
+ HeapTuple tuple;
+ int j;
+ char *values[11];
+ BTPageStat stat;
+ TupleDesc tupleDesc;
+
+ buffer = ReadBuffer(rel, uargs->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(uargs->blkno, buffer, &stat);
+
+ UnlockReleaseBuffer(buffer);
+ relation_close(rel, NoLock);
+
+ /* 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("%u", stat.blkno);
+ values[j++] = psprintf("%c", stat.type);
+ values[j++] = psprintf("%u", stat.live_items);
+ values[j++] = psprintf("%u", stat.dead_items);
+ values[j++] = psprintf("%u", stat.avg_item_size);
+ values[j++] = psprintf("%u", stat.page_size);
+ values[j++] = psprintf("%u", stat.free_size);
+ values[j++] = psprintf("%u", stat.btpo_prev);
+ values[j++] = psprintf("%u", stat.btpo_next);
+ values[j++] = psprintf("%u", stat.btpo_level);
+ values[j++] = psprintf("%d", stat.btpo_flags);
+
+ /* Construct tuple to be returned */
+ tuple = BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
+ values);
+
+ result = HeapTupleGetDatum(tuple);
+
+ /*
+ * Move to the next block number and decrement the number of blocks
+ * still to be fetched
+ */
+ uargs->blkno++;
+ uargs->blk_count--;
+
+ SRF_RETURN_NEXT(fctx, result);
+ }
+
+ relation_close(rel, NoLock);
+ SRF_RETURN_DONE(fctx);
+}
/*-------------------------------------------------------
* bt_page_print_tuples()
@@ -303,7 +469,7 @@ struct user_args
* ------------------------------------------------------
*/
static Datum
-bt_page_print_tuples(struct user_args *uargs)
+bt_page_print_tuples(ua_page_items *uargs)
{
Page page = uargs->page;
OffsetNumber offset = uargs->offset;
@@ -453,7 +619,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
Datum result;
FuncCallContext *fctx;
MemoryContext mctx;
- struct user_args *uargs;
+ ua_page_items *uargs;
if (!superuser())
ereport(ERROR,
@@ -473,33 +639,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
rel = relation_openrv(relrv, AccessShareLock);
- if (!IS_INDEX(rel) || !IS_BTREE(rel))
- ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is not a %s index",
- RelationGetRelationName(rel), "btree")));
-
- /*
- * 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 || blkno > MaxBlockNumber)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("invalid block number")));
-
- if (blkno == 0)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("block 0 is a meta page")));
-
- CHECK_RELATION_BLOCK_RANGE(rel, blkno);
+ bt_index_block_validate(rel, blkno);
buffer = ReadBuffer(rel, blkno);
LockBuffer(buffer, BUFFER_LOCK_SHARE);
@@ -511,7 +651,7 @@ bt_page_items_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
*/
mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
- uargs = palloc(sizeof(struct user_args));
+ uargs = palloc(sizeof(ua_page_items));
uargs->page = palloc(BLCKSZ);
memcpy(uargs->page, BufferGetPage(buffer), BLCKSZ);
@@ -587,7 +727,7 @@ bt_page_items_bytea(PG_FUNCTION_ARGS)
bytea *raw_page = PG_GETARG_BYTEA_P(0);
Datum result;
FuncCallContext *fctx;
- struct user_args *uargs;
+ ua_page_items *uargs;
if (!superuser())
ereport(ERROR,
@@ -603,7 +743,7 @@ bt_page_items_bytea(PG_FUNCTION_ARGS)
fctx = SRF_FIRSTCALL_INIT();
mctx = MemoryContextSwitchTo(fctx->multi_call_memory_ctx);
- uargs = palloc(sizeof(struct user_args));
+ uargs = palloc(sizeof(ua_page_items));
uargs->page = get_page_from_raw(raw_page);
diff --git a/contrib/pageinspect/expected/btree.out b/contrib/pageinspect/expected/btree.out
index 035a81a759..97f2f4e2fa 100644
--- a/contrib/pageinspect/expected/btree.out
+++ b/contrib/pageinspect/expected/btree.out
@@ -33,7 +33,120 @@ btpo_level | 0
btpo_flags | 3
SELECT * FROM bt_page_stats('test1_a_idx', 2);
-ERROR: block number out of range
+ERROR: block number 2 is out of range
+-- bt_multi_page_stats() function returns a set of records of page statistics.
+CREATE TABLE test2 AS (SELECT generate_series(1, 1000)::int8 AS col1);
+CREATE INDEX test2_col1_idx ON test2(col1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 0, 1);
+ERROR: block 0 is a meta page
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, -1);
+-[ RECORD 1 ]-+-----
+blkno | 1
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 0
+btpo_next | 2
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 2 ]-+-----
+blkno | 2
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 1
+btpo_next | 4
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 3 ]-+-----
+blkno | 3
+type | r
+live_items | 3
+dead_items | 0
+avg_item_size | 13
+page_size | 8192
+free_size | 8096
+btpo_prev | 0
+btpo_next | 0
+btpo_level | 1
+btpo_flags | 2
+-[ RECORD 4 ]-+-----
+blkno | 4
+type | l
+live_items | 268
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 2788
+btpo_prev | 2
+btpo_next | 0
+btpo_level | 0
+btpo_flags | 1
+
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 0);
+(0 rows)
+
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 2);
+-[ RECORD 1 ]-+-----
+blkno | 1
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 0
+btpo_next | 2
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 2 ]-+-----
+blkno | 2
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 1
+btpo_next | 4
+btpo_level | 0
+btpo_flags | 1
+
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 3, 2);
+-[ RECORD 1 ]-+-----
+blkno | 3
+type | r
+live_items | 3
+dead_items | 0
+avg_item_size | 13
+page_size | 8192
+free_size | 8096
+btpo_prev | 0
+btpo_next | 0
+btpo_level | 1
+btpo_flags | 2
+-[ RECORD 2 ]-+-----
+blkno | 4
+type | l
+live_items | 268
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 2788
+btpo_prev | 2
+btpo_next | 0
+btpo_level | 0
+btpo_flags | 1
+
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 7, 2);
+ERROR: block number 7 is out of range
+DROP TABLE test2;
SELECT * FROM bt_page_items('test1_a_idx', -1);
ERROR: invalid block number
SELECT * FROM bt_page_items('test1_a_idx', 0);
@@ -51,7 +164,7 @@ htid | (0,1)
tids |
SELECT * FROM bt_page_items('test1_a_idx', 2);
-ERROR: block number out of range
+ERROR: block number 2 is out of range
SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', -1));
ERROR: invalid block number
SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 0));
diff --git a/contrib/pageinspect/meson.build b/contrib/pageinspect/meson.build
index 25fa7dc20c..3318260e86 100644
--- a/contrib/pageinspect/meson.build
+++ b/contrib/pageinspect/meson.build
@@ -34,6 +34,7 @@ install_data(
'pageinspect--1.8--1.9.sql',
'pageinspect--1.9--1.10.sql',
'pageinspect--1.10--1.11.sql',
+ 'pageinspect--1.11--1.12.sql',
'pageinspect.control',
kwargs: contrib_data_args,
)
diff --git a/contrib/pageinspect/pageinspect--1.11--1.12.sql b/contrib/pageinspect/pageinspect--1.11--1.12.sql
new file mode 100644
index 0000000000..f1166c5234
--- /dev/null
+++ b/contrib/pageinspect/pageinspect--1.11--1.12.sql
@@ -0,0 +1,23 @@
+/* contrib/pageinspect/pageinspect--1.11--1.12.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pageinspect UPDATE TO '1.12'" to load this file. \quit
+
+--
+-- bt_multi_page_stats()
+--
+CREATE FUNCTION bt_multi_page_stats(IN relname text, IN blkno int8, IN blk_count int8,
+ OUT blkno int8,
+ 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 int8,
+ OUT btpo_next int8,
+ OUT btpo_level int8,
+ OUT btpo_flags int4)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'bt_multi_page_stats'
+LANGUAGE C STRICT PARALLEL SAFE;
diff --git a/contrib/pageinspect/pageinspect.control b/contrib/pageinspect/pageinspect.control
index f277413dd8..b2804e9b12 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.11'
+default_version = '1.12'
module_pathname = '$libdir/pageinspect'
relocatable = true
diff --git a/contrib/pageinspect/sql/btree.sql b/contrib/pageinspect/sql/btree.sql
index 1f554f0f67..102ebdefe3 100644
--- a/contrib/pageinspect/sql/btree.sql
+++ b/contrib/pageinspect/sql/btree.sql
@@ -11,6 +11,17 @@ SELECT * FROM bt_page_stats('test1_a_idx', 0);
SELECT * FROM bt_page_stats('test1_a_idx', 1);
SELECT * FROM bt_page_stats('test1_a_idx', 2);
+-- bt_multi_page_stats() function returns a set of records of page statistics.
+CREATE TABLE test2 AS (SELECT generate_series(1, 1000)::int8 AS col1);
+CREATE INDEX test2_col1_idx ON test2(col1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 0, 1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, -1);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 0);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 1, 2);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 3, 2);
+SELECT * FROM bt_multi_page_stats('test2_col1_idx', 7, 2);
+DROP TABLE test2;
+
SELECT * FROM bt_page_items('test1_a_idx', -1);
SELECT * FROM bt_page_items('test1_a_idx', 0);
SELECT * FROM bt_page_items('test1_a_idx', 1);
diff --git a/doc/src/sgml/pageinspect.sgml b/doc/src/sgml/pageinspect.sgml
index d4ee34ee0f..8ad10b60c7 100644
--- a/doc/src/sgml/pageinspect.sgml
+++ b/doc/src/sgml/pageinspect.sgml
@@ -346,6 +346,53 @@ btpo_flags | 3
</listitem>
</varlistentry>
+ <varlistentry>
+ <term>
+ <function>bt_multi_page_stats(relname text, blkno bigint, blk_count bigint) returns setof record</function>
+ <indexterm>
+ <primary>bt_multi_page_stats</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ <function>bt_multi_page_stats</function> returns summary information
+ about the <structfield>blk_count</structfield> number of B-tree index
+ pages starting from <structfield>blkno</structfield> page. A value of
+ <literal>-1</literal> for <structfield>blk_count</structfield> returns
+ summary information for all index pages starting from
+ <structfield>blkno</structfield> page. For example:
+<screen>
+test=# SELECT * FROM bt_multi_page_stats('pg_proc_oid_index', 5, 2);
+-[ RECORD 1 ]-+-----
+blkno | 5
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 4
+btpo_next | 6
+btpo_level | 0
+btpo_flags | 1
+-[ RECORD 2 ]-+-----
+blkno | 6
+type | l
+live_items | 367
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 808
+btpo_prev | 5
+btpo_next | 7
+btpo_level | 0
+btpo_flags | 1
+</screen>
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term>
<function>bt_page_items(relname text, blkno bigint) returns setof record</function>
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation: not tested
Looks good to me
The new status of this patch is: Ready for Committer
Hamid Akhtar <hamid.akhtar@percona.com> writes:
I wasn't aware of the meson.build file. Attached is the latest version of
the patch that contains the updated meson.build.
Pushed with minor corrections, plus one major one: you missed the
point of aeaaf520f, that pageinspect functions that touch relations
need to be parallel-restricted not parallel-safe.
regards, tom lane