Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bph9r-0000nN-HD for pgsql-hackers@arkaria.postgresql.org; Thu, 29 Sep 2016 19:38:15 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bph9q-0005Yj-SD for pgsql-hackers@arkaria.postgresql.org; Thu, 29 Sep 2016 19:38:14 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bph9o-0005XO-A4 for pgsql-hackers@postgresql.org; Thu, 29 Sep 2016 19:38:12 +0000 Received: from out2-smtp.messagingengine.com ([66.111.4.26]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bph9l-0002SJ-K1 for pgsql-hackers@postgresql.org; Thu, 29 Sep 2016 19:38:11 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.42]) by mailout.nyi.internal (Postfix) with ESMTP id CB3AD2061E; Thu, 29 Sep 2016 15:38:07 -0400 (EDT) Received: from frontend2 ([10.202.2.161]) by compute2.internal (MEProxy); Thu, 29 Sep 2016 15:38:07 -0400 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-sasl-enc :x-sasl-enc; s=smtpout; bh=HPZMk/7JN3AZX0rpchekb00E5rc=; b=rG6ri bO1AMimBypbNXFzdlOmGnMoYAX184VYDnRQazXtmfyGh1SxOuXA4XdHhes1Cdu1p 9H0+Su2skLpsJ0vmmYsZgSVl83xm5LTxqslppnpdkGE2tXFpIOwkwM7ygQGCDJzP oho+4cbWXE1u0lkDkg7HK2xftBMbOkG5gh2FmE= X-Sasl-enc: tutXo9LRdElacyImqa5gIwdMrhXezFaeQIEtXeio2lqs 1475177887 Received: from april.local (c-73-13-66-39.hsd1.pa.comcast.net [73.13.66.39]) by mail.messagingengine.com (Postfix) with ESMTPA id 5061DCC08D; Thu, 29 Sep 2016 15:38:07 -0400 (EDT) Subject: Re: pageinspect: Hash index support To: Jesper Pedersen , Michael Paquier References: <20160830170633.GA508711@alvherre.pgsql> <0f99b6b0-0e54-0f6e-9502-f8cb6f194c17@redhat.com> <3493caf5-6d50-8915-7612-bd830972f513@redhat.com> <5d64a536-cc90-ce96-b68f-5b6bd3206476@redhat.com> <80a00b5c-56d3-dffb-8dba-edf5d2167b12@2ndquadrant.com> Cc: Ashutosh Sharma , Jeff Janes , Alvaro Herrera , PostgreSQL Hackers From: Peter Eisentraut Organization: 2ndQuadrant Message-ID: Date: Thu, 29 Sep 2016 15:38:07 -0400 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0) Gecko/20100101 Thunderbird/45.3.0 MIME-Version: 1.0 In-Reply-To: <80a00b5c-56d3-dffb-8dba-edf5d2167b12@2ndquadrant.com> Content-Type: multipart/mixed; boundary="------------778173F9C17E4DDC9595C553" X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org This is a multi-part message in MIME format. --------------778173F9C17E4DDC9595C553 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit 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 --------------778173F9C17E4DDC9595C553 Content-Type: invalid/octet-stream; name="0001-Add-tests-for-pageinspect.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0001-Add-tests-for-pageinspect.patch" From b9c649159bc3291785dfe7a56baf4443db70062a Mon Sep 17 00:00:00 2001 From: Peter Eisentraut 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 --------------778173F9C17E4DDC9595C553 Content-Type: invalid/octet-stream; name="0002-Add-tests-for-pageinspect-hash.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0002-Add-tests-for-pageinspect-hash.patch" From a690c18221dd9c05cc7c9ae42b5046c866dae5a7 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut 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 --------------778173F9C17E4DDC9595C553 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers --------------778173F9C17E4DDC9595C553--