pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Jesper Pedersen <jesper.pedersen@redhat.com>
To: Michael Paquier <michael.paquier@gmail.com>
Cc: Ashutosh Sharma <ashu.coek88@gmail.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: pageinspect: Hash index support
Date: Thu, 29 Sep 2016 15:38:07 -0400
Message-ID: <bcf8c21b-702e-20a7-a4b0-236ed2363d84@2ndquadrant.com> (raw )
In-Reply-To: <80a00b5c-56d3-dffb-8dba-edf5d2167b12@2ndquadrant.com >
References: <e2ac6c58-b93f-9dd9-f4e6-d6d30add7fdf@redhat.com >
<20160830170633.GA508711@alvherre.pgsql >
<CAMkU=1wghadVOxkphJTQUqukgNkE7ETPci0g55Y+fqXO4Q43PQ@mail.gmail.com >
<0f99b6b0-0e54-0f6e-9502-f8cb6f194c17@redhat.com >
<CAB7nPqSLVbe-wXCd77CMx6vHKfRd6thUwv7nzP54Lv9k4Q6hww@mail.gmail.com >
<3493caf5-6d50-8915-7612-bd830972f513@redhat.com >
<CAMkU=1weMVE+Re7NQyAtXT5g6E5Fziw9PVU9UDapb0AG5MH=YQ@mail.gmail.com >
<CAE9k0Pk4dC9VrzV7Vm6em7JFzRMGfzx2=eYu0Yb_Psga-W47Ww@mail.gmail.com >
<e1bda3f7-dc99-c610-4d48-58815f5b7515@redhat.com >
<CAE9k0P=OQSEj0x9z91tvE9P1f-Ht7x7z8X+eDWQGTJNSFbn9yg@mail.gmail.com >
<5d64a536-cc90-ce96-b68f-5b6bd3206476@redhat.com >
<CAB7nPqTT3ZEm8QKsYYxfUnWL+P3p2mAbud7AFiZpK_JKx3eADA@mail.gmail.com >
<d235dcd6-146a-1269-73a0-da9a55bfeddc@redhat.com >
<80a00b5c-56d3-dffb-8dba-edf5d2167b12@2ndquadrant.com >
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
I wrote some tests for pageinspect, attached here (hash stuff in a
separate patch). I think all the output numbers ought to be
deterministic (I excluded everything that might contain xids). Please test.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Attachments: download all (2 files, .tar.gz)
0001-Add-tests-for-pageinspect.patch (invalid/octet-stream, 11.7K) download
From b9c649159bc3291785dfe7a56baf4443db70062a Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Thu, 29 Sep 2016 12:00:00 -0400
Subject: [PATCH 1/2] Add tests for pageinspect
---
contrib/pageinspect/.gitignore | 4 ++
contrib/pageinspect/Makefile | 2 +
contrib/pageinspect/expected/brin.out | 51 ++++++++++++++++++++++++
contrib/pageinspect/expected/btree.out | 45 +++++++++++++++++++++
contrib/pageinspect/expected/gin.out | 30 ++++++++++++++
contrib/pageinspect/expected/page.out | 73 ++++++++++++++++++++++++++++++++++
contrib/pageinspect/sql/brin.sql | 18 +++++++++
contrib/pageinspect/sql/btree.sql | 17 ++++++++
contrib/pageinspect/sql/gin.sql | 14 +++++++
contrib/pageinspect/sql/page.sql | 30 ++++++++++++++
10 files changed, 284 insertions(+)
create mode 100644 contrib/pageinspect/.gitignore
create mode 100644 contrib/pageinspect/expected/brin.out
create mode 100644 contrib/pageinspect/expected/btree.out
create mode 100644 contrib/pageinspect/expected/gin.out
create mode 100644 contrib/pageinspect/expected/page.out
create mode 100644 contrib/pageinspect/sql/brin.sql
create mode 100644 contrib/pageinspect/sql/btree.sql
create mode 100644 contrib/pageinspect/sql/gin.sql
create mode 100644 contrib/pageinspect/sql/page.sql
diff --git a/contrib/pageinspect/.gitignore b/contrib/pageinspect/.gitignore
new file mode 100644
index 0000000..5dcb3ff
--- /dev/null
+++ b/contrib/pageinspect/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile
index c73d728..ecf0df0 100644
--- a/contrib/pageinspect/Makefile
+++ b/contrib/pageinspect/Makefile
@@ -11,6 +11,8 @@ DATA = pageinspect--1.6.sql pageinspect--1.5--1.6.sql \
pageinspect--1.0--1.1.sql pageinspect--unpackaged--1.0.sql
PGFILEDESC = "pageinspect - functions to inspect contents of database pages"
+REGRESS = page btree brin gin
+
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
diff --git a/contrib/pageinspect/expected/brin.out b/contrib/pageinspect/expected/brin.out
new file mode 100644
index 0000000..71eb190
--- /dev/null
+++ b/contrib/pageinspect/expected/brin.out
@@ -0,0 +1,51 @@
+CREATE TABLE test1 (a int, b text);
+INSERT INTO test1 VALUES (1, 'one');
+CREATE INDEX test1_a_idx ON test1 USING brin (a);
+SELECT brin_page_type(get_raw_page('test1_a_idx', 0));
+ brin_page_type
+----------------
+ meta
+(1 row)
+
+SELECT brin_page_type(get_raw_page('test1_a_idx', 1));
+ brin_page_type
+----------------
+ revmap
+(1 row)
+
+SELECT brin_page_type(get_raw_page('test1_a_idx', 2));
+ brin_page_type
+----------------
+ regular
+(1 row)
+
+SELECT * FROM brin_metapage_info(get_raw_page('test1_a_idx', 0));
+ magic | version | pagesperrange | lastrevmappage
+------------+---------+---------------+----------------
+ 0xA8109CFA | 1 | 128 | 1
+(1 row)
+
+SELECT * FROM brin_metapage_info(get_raw_page('test1_a_idx', 1));
+ERROR: page is not a BRIN page of type "metapage"
+DETAIL: Expected special type 0000f091, got 0000f092.
+SELECT * FROM brin_revmap_data(get_raw_page('test1_a_idx', 0)) LIMIT 5;
+ERROR: page is not a BRIN page of type "revmap"
+DETAIL: Expected special type 0000f092, got 0000f091.
+SELECT * FROM brin_revmap_data(get_raw_page('test1_a_idx', 1)) LIMIT 5;
+ pages
+-------
+ (2,1)
+ (0,0)
+ (0,0)
+ (0,0)
+ (0,0)
+(5 rows)
+
+SELECT * FROM brin_page_items(get_raw_page('test1_a_idx', 2), 'test1_a_idx')
+ ORDER BY blknum, attnum LIMIT 5;
+ itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value
+------------+--------+--------+----------+----------+-------------+----------
+ 1 | 0 | 1 | f | f | f | {1 .. 1}
+(1 row)
+
+DROP TABLE test1;
diff --git a/contrib/pageinspect/expected/btree.out b/contrib/pageinspect/expected/btree.out
new file mode 100644
index 0000000..ef7f86e
--- /dev/null
+++ b/contrib/pageinspect/expected/btree.out
@@ -0,0 +1,45 @@
+CREATE TABLE test1 (a int, b text);
+INSERT INTO test1 VALUES (1, 'one');
+CREATE INDEX test1_a_idx ON test1 USING btree (a);
+\x
+SELECT * FROM bt_metap('test1_a_idx');
+-[ RECORD 1 ]-----
+magic | 340322
+version | 2
+root | 1
+level | 0
+fastroot | 1
+fastlevel | 0
+
+SELECT * FROM bt_page_stats('test1_a_idx', 0);
+ERROR: block 0 is a meta page
+SELECT * FROM bt_page_stats('test1_a_idx', 1);
+-[ RECORD 1 ]-+-----
+blkno | 1
+type | l
+live_items | 1
+dead_items | 0
+avg_item_size | 16
+page_size | 8192
+free_size | 8128
+btpo_prev | 0
+btpo_next | 0
+btpo | 0
+btpo_flags | 3
+
+SELECT * FROM bt_page_stats('test1_a_idx', 2);
+ERROR: block number out of range
+SELECT * FROM bt_page_items('test1_a_idx', 0);
+ERROR: block 0 is a meta page
+SELECT * FROM bt_page_items('test1_a_idx', 1);
+-[ RECORD 1 ]-----------------------
+itemoffset | 1
+ctid | (0,1)
+itemlen | 16
+nulls | f
+vars | f
+data | 01 00 00 00 00 00 00 00
+
+SELECT * FROM bt_page_items('test1_a_idx', 2);
+ERROR: block number out of range
+DROP TABLE test1;
diff --git a/contrib/pageinspect/expected/gin.out b/contrib/pageinspect/expected/gin.out
new file mode 100644
index 0000000..4f341e1
--- /dev/null
+++ b/contrib/pageinspect/expected/gin.out
@@ -0,0 +1,30 @@
+CREATE TABLE test1 (x int, y int[]);
+INSERT INTO test1 VALUES (1, ARRAY[11, 111]);
+CREATE INDEX test1_y_idx ON test1 USING gin (y);
+\x
+SELECT * FROM gin_metapage_info(get_raw_page('test1_y_idx', 0));
+-[ RECORD 1 ]----+-----------
+pending_head | 4294967295
+pending_tail | 4294967295
+tail_free_size | 0
+n_pending_pages | 0
+n_pending_tuples | 0
+n_total_pages | 2
+n_entry_pages | 1
+n_data_pages | 0
+n_entries | 2
+version | 2
+
+SELECT * FROM gin_metapage_info(get_raw_page('test1_y_idx', 1));
+ERROR: input page is not a GIN metapage
+DETAIL: Flags 0002, expected 0008
+SELECT * FROM gin_page_opaque_info(get_raw_page('test1_y_idx', 1));
+-[ RECORD 1 ]---------
+rightlink | 4294967295
+maxoff | 0
+flags | {leaf}
+
+SELECT * FROM gin_leafpage_items(get_raw_page('test1_y_idx', 1));
+ERROR: input page is not a compressed GIN data leaf page
+DETAIL: Flags 0002, expected 0083
+DROP TABLE test1;
diff --git a/contrib/pageinspect/expected/page.out b/contrib/pageinspect/expected/page.out
new file mode 100644
index 0000000..673a852
--- /dev/null
+++ b/contrib/pageinspect/expected/page.out
@@ -0,0 +1,73 @@
+CREATE EXTENSION pageinspect;
+CREATE TABLE test1 (a int, b text);
+INSERT INTO test1 VALUES (1, 'one');
+VACUUM test1; -- set up FSM
+-- The page contents can vary, so just test that it can be read
+-- successfully, but don't keep the output.
+SELECT octet_length(get_raw_page('test1', 'main', 0)) AS main_0;
+ main_0
+--------
+ 8192
+(1 row)
+
+SELECT octet_length(get_raw_page('test1', 'main', 1)) AS main_1;
+ERROR: block number 1 is out of range for relation "test1"
+SELECT octet_length(get_raw_page('test1', 'fsm', 0)) AS fsm_0;
+ fsm_0
+-------
+ 8192
+(1 row)
+
+SELECT octet_length(get_raw_page('test1', 'fsm', 1)) AS fsm_1;
+ fsm_1
+-------
+ 8192
+(1 row)
+
+SELECT octet_length(get_raw_page('test1', 'vm', 0)) AS vm_0;
+ vm_0
+------
+ 8192
+(1 row)
+
+SELECT octet_length(get_raw_page('test1', 'vm', 1)) AS vm_1;
+ERROR: block number 1 is out of range for relation "test1"
+SELECT octet_length(get_raw_page('xxx', 'main', 0));
+ERROR: relation "xxx" does not exist
+SELECT octet_length(get_raw_page('test1', 'xxx', 0));
+ERROR: invalid fork name
+HINT: Valid fork names are "main", "fsm", "vm", and "init".
+SELECT get_raw_page('test1', 0) = get_raw_page('test1', 'main', 0);
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT tuple_data_split('test1'::regclass, t_data, t_infomask, t_infomask2, t_bits)
+ FROM heap_page_items(get_raw_page('test1', 0));
+ tuple_data_split
+-------------------------------
+ {"\\x01000000","\\x096f6e65"}
+(1 row)
+
+SELECT * FROM fsm_page_contents(get_raw_page('test1', 'fsm', 0));
+ fsm_page_contents
+-------------------
+ 0: 254 +
+ 1: 254 +
+ 3: 254 +
+ 7: 254 +
+ 15: 254 +
+ 31: 254 +
+ 63: 254 +
+ 127: 254 +
+ 255: 254 +
+ 511: 254 +
+ 1023: 254 +
+ 2047: 254 +
+ 4095: 254 +
+ fp_next_slot: 0 +
+
+(1 row)
+
+DROP TABLE test1;
diff --git a/contrib/pageinspect/sql/brin.sql b/contrib/pageinspect/sql/brin.sql
new file mode 100644
index 0000000..735bc3b
--- /dev/null
+++ b/contrib/pageinspect/sql/brin.sql
@@ -0,0 +1,18 @@
+CREATE TABLE test1 (a int, b text);
+INSERT INTO test1 VALUES (1, 'one');
+CREATE INDEX test1_a_idx ON test1 USING brin (a);
+
+SELECT brin_page_type(get_raw_page('test1_a_idx', 0));
+SELECT brin_page_type(get_raw_page('test1_a_idx', 1));
+SELECT brin_page_type(get_raw_page('test1_a_idx', 2));
+
+SELECT * FROM brin_metapage_info(get_raw_page('test1_a_idx', 0));
+SELECT * FROM brin_metapage_info(get_raw_page('test1_a_idx', 1));
+
+SELECT * FROM brin_revmap_data(get_raw_page('test1_a_idx', 0)) LIMIT 5;
+SELECT * FROM brin_revmap_data(get_raw_page('test1_a_idx', 1)) LIMIT 5;
+
+SELECT * FROM brin_page_items(get_raw_page('test1_a_idx', 2), 'test1_a_idx')
+ ORDER BY blknum, attnum LIMIT 5;
+
+DROP TABLE test1;
diff --git a/contrib/pageinspect/sql/btree.sql b/contrib/pageinspect/sql/btree.sql
new file mode 100644
index 0000000..4fa3416
--- /dev/null
+++ b/contrib/pageinspect/sql/btree.sql
@@ -0,0 +1,17 @@
+CREATE TABLE test1 (a int, b text);
+INSERT INTO test1 VALUES (1, 'one');
+CREATE INDEX test1_a_idx ON test1 USING btree (a);
+
+\x
+
+SELECT * FROM bt_metap('test1_a_idx');
+
+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);
+
+SELECT * FROM bt_page_items('test1_a_idx', 0);
+SELECT * FROM bt_page_items('test1_a_idx', 1);
+SELECT * FROM bt_page_items('test1_a_idx', 2);
+
+DROP TABLE test1;
diff --git a/contrib/pageinspect/sql/gin.sql b/contrib/pageinspect/sql/gin.sql
new file mode 100644
index 0000000..ba79ff2
--- /dev/null
+++ b/contrib/pageinspect/sql/gin.sql
@@ -0,0 +1,14 @@
+CREATE TABLE test1 (x int, y int[]);
+INSERT INTO test1 VALUES (1, ARRAY[11, 111]);
+CREATE INDEX test1_y_idx ON test1 USING gin (y);
+
+\x
+
+SELECT * FROM gin_metapage_info(get_raw_page('test1_y_idx', 0));
+SELECT * FROM gin_metapage_info(get_raw_page('test1_y_idx', 1));
+
+SELECT * FROM gin_page_opaque_info(get_raw_page('test1_y_idx', 1));
+
+SELECT * FROM gin_leafpage_items(get_raw_page('test1_y_idx', 1));
+
+DROP TABLE test1;
diff --git a/contrib/pageinspect/sql/page.sql b/contrib/pageinspect/sql/page.sql
new file mode 100644
index 0000000..a304151
--- /dev/null
+++ b/contrib/pageinspect/sql/page.sql
@@ -0,0 +1,30 @@
+CREATE EXTENSION pageinspect;
+
+CREATE TABLE test1 (a int, b text);
+INSERT INTO test1 VALUES (1, 'one');
+
+VACUUM test1; -- set up FSM
+
+-- The page contents can vary, so just test that it can be read
+-- successfully, but don't keep the output.
+
+SELECT octet_length(get_raw_page('test1', 'main', 0)) AS main_0;
+SELECT octet_length(get_raw_page('test1', 'main', 1)) AS main_1;
+
+SELECT octet_length(get_raw_page('test1', 'fsm', 0)) AS fsm_0;
+SELECT octet_length(get_raw_page('test1', 'fsm', 1)) AS fsm_1;
+
+SELECT octet_length(get_raw_page('test1', 'vm', 0)) AS vm_0;
+SELECT octet_length(get_raw_page('test1', 'vm', 1)) AS vm_1;
+
+SELECT octet_length(get_raw_page('xxx', 'main', 0));
+SELECT octet_length(get_raw_page('test1', 'xxx', 0));
+
+SELECT get_raw_page('test1', 0) = get_raw_page('test1', 'main', 0);
+
+SELECT tuple_data_split('test1'::regclass, t_data, t_infomask, t_infomask2, t_bits)
+ FROM heap_page_items(get_raw_page('test1', 0));
+
+SELECT * FROM fsm_page_contents(get_raw_page('test1', 'fsm', 0));
+
+DROP TABLE test1;
--
2.10.0
0002-Add-tests-for-pageinspect-hash.patch (invalid/octet-stream, 7.9K) download
From a690c18221dd9c05cc7c9ae42b5046c866dae5a7 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Thu, 29 Sep 2016 12:00:00 -0400
Subject: [PATCH 2/2] Add tests for pageinspect hash
---
contrib/pageinspect/Makefile | 2 +-
contrib/pageinspect/expected/hash.out | 131 ++++++++++++++++++++++++++++++++++
contrib/pageinspect/sql/hash.sql | 29 ++++++++
3 files changed, 161 insertions(+), 1 deletion(-)
create mode 100644 contrib/pageinspect/expected/hash.out
create mode 100644 contrib/pageinspect/sql/hash.sql
diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile
index ecf0df0..d566c35 100644
--- a/contrib/pageinspect/Makefile
+++ b/contrib/pageinspect/Makefile
@@ -11,7 +11,7 @@ DATA = pageinspect--1.6.sql pageinspect--1.5--1.6.sql \
pageinspect--1.0--1.1.sql pageinspect--unpackaged--1.0.sql
PGFILEDESC = "pageinspect - functions to inspect contents of database pages"
-REGRESS = page btree brin gin
+REGRESS = page btree brin gin hash
ifdef USE_PGXS
PG_CONFIG = pg_config
diff --git a/contrib/pageinspect/expected/hash.out b/contrib/pageinspect/expected/hash.out
new file mode 100644
index 0000000..c605297
--- /dev/null
+++ b/contrib/pageinspect/expected/hash.out
@@ -0,0 +1,131 @@
+CREATE TABLE test1 (a int, b text);
+INSERT INTO test1 VALUES (1, 'one');
+CREATE INDEX test1_a_idx ON test1 USING hash (a);
+WARNING: hash indexes are not WAL-logged and their use is discouraged
+\x
+SELECT * FROM hash_metapage_info(get_raw_page('test1_a_idx', 0));
+-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+magic | 0x06440640
+version | 2
+ntuples | 1
+ffactor | 307
+bsize | 8152
+bmsize | 4096
+bmshift | 15
+maxbucket | 3
+highmask | 7
+lowmask | 3
+ovflpoint | 2
+firstfree | 0
+nmaps | 1
+procid | 450
+spares | 0000 0000 0001 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000
+mapp | 0005 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000
+
+SELECT * FROM hash_metapage_info(get_raw_page('test1_a_idx', 1));
+ERROR: page is not a metadata page
+DETAIL: Expected 00000008, got 00000002.
+SELECT * FROM hash_metapage_info(get_raw_page('test1_a_idx', 2));
+ERROR: page is not a metadata page
+DETAIL: Expected 00000008, got 00000002.
+SELECT * FROM hash_metapage_info(get_raw_page('test1_a_idx', 3));
+ERROR: page is not a metadata page
+DETAIL: Expected 00000008, got 00000002.
+SELECT * FROM hash_metapage_info(get_raw_page('test1_a_idx', 4));
+ERROR: page is not a metadata page
+DETAIL: Expected 00000008, got 00000002.
+SELECT * FROM hash_metapage_info(get_raw_page('test1_a_idx', 5));
+ERROR: page is not a metadata page
+DETAIL: Expected 00000008, got 00000004.
+SELECT * FROM hash_metapage_info(get_raw_page('test1_a_idx', 6));
+ERROR: block number 6 is out of range for relation "test1_a_idx"
+SELECT * FROM hash_page_stats(get_raw_page('test1_a_idx', 0));
+ERROR: page is a metadata page
+DETAIL: Flags 00000008.
+SELECT * FROM hash_page_stats(get_raw_page('test1_a_idx', 1));
+-[ RECORD 1 ]---+-------
+type | bucket
+live_items | 0
+dead_items | 0
+page_size | 8192
+free_size | 8148
+hasho_prevblkno | -1
+hasho_nextblkno | -1
+hasho_bucket | 0
+hasho_flag | 2
+hasho_page_id | 65408
+
+SELECT * FROM hash_page_stats(get_raw_page('test1_a_idx', 2));
+-[ RECORD 1 ]---+-------
+type | bucket
+live_items | 0
+dead_items | 0
+page_size | 8192
+free_size | 8148
+hasho_prevblkno | -1
+hasho_nextblkno | -1
+hasho_bucket | 1
+hasho_flag | 2
+hasho_page_id | 65408
+
+SELECT * FROM hash_page_stats(get_raw_page('test1_a_idx', 3));
+-[ RECORD 1 ]---+-------
+type | bucket
+live_items | 1
+dead_items | 0
+page_size | 8192
+free_size | 8128
+hasho_prevblkno | -1
+hasho_nextblkno | -1
+hasho_bucket | 2
+hasho_flag | 2
+hasho_page_id | 65408
+
+SELECT * FROM hash_page_stats(get_raw_page('test1_a_idx', 4));
+-[ RECORD 1 ]---+-------
+type | bucket
+live_items | 0
+dead_items | 0
+page_size | 8192
+free_size | 8148
+hasho_prevblkno | -1
+hasho_nextblkno | -1
+hasho_bucket | 3
+hasho_flag | 2
+hasho_page_id | 65408
+
+SELECT * FROM hash_page_stats(get_raw_page('test1_a_idx', 5));
+-[ RECORD 1 ]---+-------
+type | bitmap
+live_items | 0
+dead_items | 0
+page_size | 8192
+free_size | 8148
+hasho_prevblkno | -1
+hasho_nextblkno | -1
+hasho_bucket | -1
+hasho_flag | 4
+hasho_page_id | 65408
+
+SELECT * FROM hash_page_items(get_raw_page('test1_a_idx', 0));
+ERROR: page is a metadata page
+DETAIL: Flags 00000008.
+SELECT * FROM hash_page_items(get_raw_page('test1_a_idx', 1));
+(0 rows)
+
+SELECT * FROM hash_page_items(get_raw_page('test1_a_idx', 2));
+(0 rows)
+
+SELECT * FROM hash_page_items(get_raw_page('test1_a_idx', 3));
+-[ RECORD 1 ]-----------------------
+itemoffset | 1
+ctid | (2359296,12328)
+data | 46 17 73 8e 00 00 00 00
+
+SELECT * FROM hash_page_items(get_raw_page('test1_a_idx', 4));
+(0 rows)
+
+SELECT * FROM hash_page_items(get_raw_page('test1_a_idx', 5));
+(0 rows)
+
+DROP TABLE test1;
diff --git a/contrib/pageinspect/sql/hash.sql b/contrib/pageinspect/sql/hash.sql
new file mode 100644
index 0000000..5f5f6f8
--- /dev/null
+++ b/contrib/pageinspect/sql/hash.sql
@@ -0,0 +1,29 @@
+CREATE TABLE test1 (a int, b text);
+INSERT INTO test1 VALUES (1, 'one');
+CREATE INDEX test1_a_idx ON test1 USING hash (a);
+
+\x
+
+SELECT * FROM hash_metapage_info(get_raw_page('test1_a_idx', 0));
+SELECT * FROM hash_metapage_info(get_raw_page('test1_a_idx', 1));
+SELECT * FROM hash_metapage_info(get_raw_page('test1_a_idx', 2));
+SELECT * FROM hash_metapage_info(get_raw_page('test1_a_idx', 3));
+SELECT * FROM hash_metapage_info(get_raw_page('test1_a_idx', 4));
+SELECT * FROM hash_metapage_info(get_raw_page('test1_a_idx', 5));
+SELECT * FROM hash_metapage_info(get_raw_page('test1_a_idx', 6));
+
+SELECT * FROM hash_page_stats(get_raw_page('test1_a_idx', 0));
+SELECT * FROM hash_page_stats(get_raw_page('test1_a_idx', 1));
+SELECT * FROM hash_page_stats(get_raw_page('test1_a_idx', 2));
+SELECT * FROM hash_page_stats(get_raw_page('test1_a_idx', 3));
+SELECT * FROM hash_page_stats(get_raw_page('test1_a_idx', 4));
+SELECT * FROM hash_page_stats(get_raw_page('test1_a_idx', 5));
+
+SELECT * FROM hash_page_items(get_raw_page('test1_a_idx', 0));
+SELECT * FROM hash_page_items(get_raw_page('test1_a_idx', 1));
+SELECT * FROM hash_page_items(get_raw_page('test1_a_idx', 2));
+SELECT * FROM hash_page_items(get_raw_page('test1_a_idx', 3));
+SELECT * FROM hash_page_items(get_raw_page('test1_a_idx', 4));
+SELECT * FROM hash_page_items(get_raw_page('test1_a_idx', 5));
+
+DROP TABLE test1;
--
2.10.0
view thread (110+ messages) latest in thread
Message-ID: <bcf8c21b-702e-20a7-a4b0-236ed2363d84@2ndquadrant.com>
Permalink: ../bcf8c21b-702e-20a7-a4b0-236ed2363d84@2ndquadrant.com/
Also on: postgresql.org/message-id/bcf8c21b-702e-20a7-a4b0-236ed2363d84@2ndquadrant.com
copy link · copy postgr.es · copy git am
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: peter.eisentraut@2ndquadrant.com, jesper.pedersen@redhat.com, michael.paquier@gmail.com, ashu.coek88@gmail.com, jeff.janes@gmail.com, alvherre@2ndquadrant.com
Subject: Re: pageinspect: Hash index support
In-Reply-To: <bcf8c21b-702e-20a7-a4b0-236ed2363d84@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox