granicus.if.org Git - postgresql/blob - doc/src/sgml/pageinspect.sgml

   1 <!-- doc/src/sgml/pageinspect.sgml -->
   2
   3 <sect1 id="pageinspect" xreflabel="pageinspect">
   4  <title>pageinspect</title>
   5
   6  <indexterm zone="pageinspect">
   7   <primary>pageinspect</primary>
   8  </indexterm>
   9
  10  <para>
  11   The <filename>pageinspect</> module provides functions that allow you to
  12   inspect the contents of database pages at a low level, which is useful for
  13   debugging purposes.  All of these functions may be used only by superusers.
  14  </para>
  15
  16  <sect2>
  17   <title>Functions</title>
  18
  19   <variablelist>
  20    <varlistentry>
  21     <term>
  22      <function>get_raw_page(relname text, fork text, blkno int) returns bytea</function>
  23      <indexterm>
  24       <primary>get_raw_page</primary>
  25      </indexterm>
  26     </term>
  27
  28     <listitem>
  29      <para>
  30       <function>get_raw_page</function> reads the specified block of the named
  31       relation and returns a copy as a <type>bytea</> value.  This allows a
  32       single time-consistent copy of the block to be obtained.
  33       <replaceable>fork</replaceable> should be <literal>'main'</literal> for
  34       the main data fork, <literal>'fsm'</literal> for the free space map,
  35       <literal>'vm'</literal> for the visibility map, or <literal>'init'</literal>
  36       for the initialization fork.
  37      </para>
  38     </listitem>
  39    </varlistentry>
  40
  41    <varlistentry>
  42     <term>
  43      <function>get_raw_page(relname text, blkno int) returns bytea</function>
  44     </term>
  45
  46     <listitem>
  47      <para>
  48       A shorthand version of <function>get_raw_page</function>, for reading
  49       from the main fork.  Equivalent to
  50       <literal>get_raw_page(relname, 'main', blkno)</literal>
  51      </para>
  52     </listitem>
  53    </varlistentry>
  54
  55    <varlistentry>
  56     <term>
  57      <function>page_header(page bytea) returns record</function>
  58      <indexterm>
  59       <primary>page_header</primary>
  60      </indexterm>
  61     </term>
  62
  63     <listitem>
  64      <para>
  65       <function>page_header</function> shows fields that are common to all
  66       <productname>PostgreSQL</> heap and index pages.
  67      </para>
  68
  69      <para>
  70       A page image obtained with <function>get_raw_page</function> should be
  71       passed as argument.  For example:
  72 <screen>
  73 test=# SELECT * FROM page_header(get_raw_page('pg_class', 0));
  74     lsn    | checksum | flags  | lower | upper | special | pagesize | version | prune_xid
  75 -----------+----------+--------+-------+-------+---------+----------+---------+-----------
  76  0/24A1B50 |        1 |      1 |   232 |   368 |    8192 |     8192 |       4 |         0
  77 </screen>
  78       The returned columns correspond to the fields in the
  79       <structname>PageHeaderData</> struct.
  80       See <filename>src/include/storage/bufpage.h</> for details.
  81     </para>
  82     </listitem>
  83    </varlistentry>
  84
  85    <varlistentry>
  86     <term>
  87      <function>heap_page_items(page bytea) returns setof record</function>
  88      <indexterm>
  89       <primary>heap_page_items</primary>
  90      </indexterm>
  91     </term>
  92
  93     <listitem>
  94      <para>
  95       <function>heap_page_items</function> shows all line pointers on a heap
  96       page.  For those line pointers that are in use, tuple headers as well
  97       as tuple raw data are also shown. All tuples are shown, whether or not
  98       the tuples were visible to an MVCC snapshot at the time the raw page
  99       was copied.
 100      </para>
 101      <para>
 102       A heap page image obtained with <function>get_raw_page</function> should
 103       be passed as argument.  For example:
 104 <screen>
 105 test=# SELECT * FROM heap_page_items(get_raw_page('pg_class', 0));
 106 </screen>
 107       See <filename>src/include/storage/itemid.h</> and
 108       <filename>src/include/access/htup_details.h</> for explanations of the fields
 109       returned.
 110      </para>
 111     </listitem>
 112    </varlistentry>
 113
 114    <varlistentry>
 115     <term>
 116      <function>tuple_data_split(rel_oid, t_data bytea, t_infomask integer, t_infomask2 integer, t_bits text [, do_detoast bool]) returns bytea[]</function>
 117      <indexterm>
 118       <primary>tuple_data_split</primary>
 119      </indexterm>
 120     </term>
 121     <listitem>
 122      <para>
 123       <function>tuple_data_split</function> splits tuple data into attributes
 124       in the same way as backend internals.
 125 <screen>
 126 test=# SELECT tuple_data_split('pg_class'::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page('pg_class', 0));
 127 </screen>
 128       This function should be called with the same arguments as the return
 129       attributes of <function>heap_page_items</function>.
 130      </para>
 131      <para>
 132       If <parameter>do_detoast</parameter> is <literal>true</literal>,
 133       attribute that will be detoasted as needed. Default value is
 134       <literal>false</literal>.
 135      </para>
 136     </listitem>
 137    </varlistentry>
 138
 139    <varlistentry>
 140     <term>
 141      <function>heap_page_item_attrs(rel_oid, t_data bytea, [, do_detoast bool]) returns bytea[]</function>
 142      <indexterm>
 143       <primary>heap_page_item_attrs</primary>
 144      </indexterm>
 145     </term>
 146     <listitem>
 147      <para>
 148       <function>heap_page_item_attrs</function> is equivalent to
 149       <function>heap_page_items</function> except that it returns
 150       tuple raw data as an array of attributes that can optionally
 151       be detoasted by <parameter>do_detoast</parameter> which is
 152       <literal>false</literal> by default.
 153      </para>
 154      <para>
 155       A heap page image obtained with <function>get_raw_page</function> should
 156       be passed as argument.  For example:
 157 <screen>
 158 test=# SELECT * FROM heap_page_item_attrs(get_raw_page('pg_class', 0), 'pg_class'::regclass);
 159 </screen>
 160      </para>
 161     </listitem>
 162    </varlistentry>
 163
 164    <varlistentry>
 165     <term>
 166      <function>bt_metap(relname text) returns record</function>
 167      <indexterm>
 168       <primary>bt_metap</primary>
 169      </indexterm>
 170     </term>
 171
 172     <listitem>
 173      <para>
 174       <function>bt_metap</function> returns information about a B-tree
 175       index's metapage.  For example:
 176 <screen>
 177 test=# SELECT * FROM bt_metap('pg_cast_oid_index');
 178 -[ RECORD 1 ]-----
 179 magic     | 340322
 180 version   | 2
 181 root      | 1
 182 level     | 0
 183 fastroot  | 1
 184 fastlevel | 0
 185 </screen>
 186      </para>
 187     </listitem>
 188    </varlistentry>
 189
 190    <varlistentry>
 191     <term>
 192      <function>bt_page_stats(relname text, blkno int) returns record</function>
 193      <indexterm>
 194       <primary>bt_page_stats</primary>
 195      </indexterm>
 196     </term>
 197
 198     <listitem>
 199      <para>
 200       <function>bt_page_stats</function> returns summary information about
 201       single pages of B-tree indexes.  For example:
 202 <screen>
 203 test=# SELECT * FROM bt_page_stats('pg_cast_oid_index', 1);
 204 -[ RECORD 1 ]-+-----
 205 blkno         | 1
 206 type          | l
 207 live_items    | 256
 208 dead_items    | 0
 209 avg_item_size | 12
 210 page_size     | 8192
 211 free_size     | 4056
 212 btpo_prev     | 0
 213 btpo_next     | 0
 214 btpo          | 0
 215 btpo_flags    | 3
 216 </screen>
 217      </para>
 218     </listitem>
 219    </varlistentry>
 220
 221    <varlistentry>
 222     <term>
 223      <function>bt_page_items(relname text, blkno int) returns setof record</function>
 224      <indexterm>
 225       <primary>bt_page_items</primary>
 226      </indexterm>
 227     </term>
 228
 229     <listitem>
 230      <para>
 231       <function>bt_page_items</function> returns detailed information about
 232       all of the items on a B-tree index page.  For example:
 233 <screen>
 234 test=# SELECT * FROM bt_page_items('pg_cast_oid_index', 1);
 235  itemoffset |  ctid   | itemlen | nulls | vars |    data
 236 ------------+---------+---------+-------+------+-------------
 237           1 | (0,1)   |      12 | f     | f    | 23 27 00 00
 238           2 | (0,2)   |      12 | f     | f    | 24 27 00 00
 239           3 | (0,3)   |      12 | f     | f    | 25 27 00 00
 240           4 | (0,4)   |      12 | f     | f    | 26 27 00 00
 241           5 | (0,5)   |      12 | f     | f    | 27 27 00 00
 242           6 | (0,6)   |      12 | f     | f    | 28 27 00 00
 243           7 | (0,7)   |      12 | f     | f    | 29 27 00 00
 244           8 | (0,8)   |      12 | f     | f    | 2a 27 00 00
 245 </screen>
 246       In a B-tree leaf page, <structfield>ctid</> points to a heap tuple.
 247       In an internal page, the block number part of <structfield>ctid</>
 248       points to another page in the index itself, while the offset part
 249       (the second number) is ignored and is usually 1.
 250      </para>
 251      <para>
 252       Note that the first item on any non-rightmost page (any page with
 253       a non-zero value in the <structfield>btpo_next</> field) is the
 254       page's <quote>high key</quote>, meaning its <structfield>data</>
 255       serves as an upper bound on all items appearing on the page, while
 256       its <structfield>ctid</> field is meaningless.  Also, on non-leaf
 257       pages, the first real data item (the first item that is not a high
 258       key) is a <quote>minus infinity</quote> item, with no actual value
 259       in its <structfield>data</> field.  Such an item does have a valid
 260       downlink in its <structfield>ctid</> field, however.
 261      </para>
 262     </listitem>
 263    </varlistentry>
 264
 265    <varlistentry>
 266     <term>
 267      <function>brin_page_type(page bytea) returns text</function>
 268      <indexterm>
 269       <primary>brin_page_type</primary>
 270      </indexterm>
 271     </term>
 272
 273     <listitem>
 274      <para>
 275       <function>brin_page_type</function> returns the page type of the given
 276       <acronym>BRIN</acronym> index page, or throws an error if the page is
 277       not a valid <acronym>BRIN</acronym> page.  For example:
 278 <screen>
 279 test=# SELECT brin_page_type(get_raw_page('brinidx', 0));
 280  brin_page_type
 281 ----------------
 282  meta
 283 </screen>
 284      </para>
 285     </listitem>
 286    </varlistentry>
 287
 288    <varlistentry>
 289     <term>
 290      <function>brin_metapage_info(page bytea) returns record</function>
 291      <indexterm>
 292       <primary>brin_metapage_info</primary>
 293      </indexterm>
 294     </term>
 295
 296     <listitem>
 297      <para>
 298       <function>brin_metapage_info</function> returns assorted information
 299       about a <acronym>BRIN</acronym> index metapage.  For example:
 300 <screen>
 301 test=# SELECT * FROM brin_metapage_info(get_raw_page('brinidx', 0));
 302    magic    | version | pagesperrange | lastrevmappage
 303 ------------+---------+---------------+----------------
 304  0xA8109CFA |       1 |             4 |              2
 305 </screen>
 306      </para>
 307     </listitem>
 308    </varlistentry>
 309
 310    <varlistentry>
 311     <term>
 312      <function>brin_revmap_data(page bytea) returns setof tid</function>
 313      <indexterm>
 314       <primary>brin_revmap_data</primary>
 315      </indexterm>
 316     </term>
 317
 318     <listitem>
 319      <para>
 320       <function>brin_revmap_data</function> returns the list of tuple
 321       identifiers in a <acronym>BRIN</acronym> index range map page.
 322       For example:
 323 <screen>
 324 test=# SELECT * FROM brin_revmap_data(get_raw_page('brinidx', 2)) limit 5;
 325   pages
 326 ---------
 327  (6,137)
 328  (6,138)
 329  (6,139)
 330  (6,140)
 331  (6,141)
 332 </screen>
 333      </para>
 334     </listitem>
 335    </varlistentry>
 336
 337    <varlistentry>
 338     <term>
 339      <function>brin_page_items(page bytea, index oid) returns setof record</function>
 340      <indexterm>
 341       <primary>brin_page_items</primary>
 342      </indexterm>
 343     </term>
 344
 345     <listitem>
 346      <para>
 347       <function>brin_page_items</function> returns the data stored in the
 348       <acronym>BRIN</acronym> data page.  For example:
 349 <screen>
 350 test=# SELECT * FROM brin_page_items(get_raw_page('brinidx', 5),
 351                                      'brinidx')
 352        ORDER BY blknum, attnum LIMIT 6;
 353  itemoffset | blknum | attnum | allnulls | hasnulls | placeholder |    value
 354 ------------+--------+--------+----------+----------+-------------+--------------
 355         137 |      0 |      1 | t        | f        | f           |
 356         137 |      0 |      2 | f        | f        | f           | {1 .. 88}
 357         138 |      4 |      1 | t        | f        | f           |
 358         138 |      4 |      2 | f        | f        | f           | {89 .. 176}
 359         139 |      8 |      1 | t        | f        | f           |
 360         139 |      8 |      2 | f        | f        | f           | {177 .. 264}
 361 </screen>
 362       The returned columns correspond to the fields in the
 363       <structname>BrinMemTuple</> and <structname>BrinValues</> structs.
 364       See <filename>src/include/access/brin_tuple.h</> for details.
 365      </para>
 366     </listitem>
 367    </varlistentry>
 368
 369    <varlistentry>
 370     <term>
 371      <function>gin_metapage_info(page bytea) returns record</function>
 372      <indexterm>
 373       <primary>gin_metapage_info</primary>
 374      </indexterm>
 375     </term>
 376
 377     <listitem>
 378      <para>
 379       <function>gin_metapage_info</function> returns information about
 380       a <acronym>GIN</acronym> index metapage.  For example:
 381 <screen>
 382 test=# SELECT * FROM gin_metapage_info(get_raw_page('gin_index', 0));
 383 -[ RECORD 1 ]----+-----------
 384 pending_head     | 4294967295
 385 pending_tail     | 4294967295
 386 tail_free_size   | 0
 387 n_pending_pages  | 0
 388 n_pending_tuples | 0
 389 n_total_pages    | 7
 390 n_entry_pages    | 6
 391 n_data_pages     | 0
 392 n_entries        | 693
 393 version          | 2
 394 </screen>
 395      </para>
 396     </listitem>
 397    </varlistentry>
 398
 399    <varlistentry>
 400     <term>
 401      <function>gin_page_opaque_info(page bytea) returns record</function>
 402      <indexterm>
 403       <primary>gin_page_opaque_info</primary>
 404      </indexterm>
 405     </term>
 406
 407     <listitem>
 408      <para>
 409       <function>gin_page_opaque_info</function> returns information about
 410       a <acronym>GIN</acronym> index opaque area, like the page type.
 411       For example:
 412 <screen>
 413 test=# SELECT * FROM gin_page_opaque_info(get_raw_page('gin_index', 2));
 414  rightlink | maxoff |         flags
 415 -----------+--------+------------------------
 416          5 |      0 | {data,leaf,compressed}
 417 (1 row)
 418 </screen>
 419      </para>
 420     </listitem>
 421    </varlistentry>
 422
 423    <varlistentry>
 424     <term>
 425      <function>gin_leafpage_items(page bytea) returns setof record</function>
 426      <indexterm>
 427       <primary>gin_leafpage_items</primary>
 428      </indexterm>
 429     </term>
 430
 431     <listitem>
 432      <para>
 433       <function>gin_leafpage_items</function> returns information about
 434       the data stored in a <acronym>GIN</acronym> leaf page.  For example:
 435 <screen>
 436 test=# SELECT first_tid, nbytes, tids[0:5] as some_tids
 437         FROM gin_leafpage_items(get_raw_page('gin_test_idx', 2));
 438  first_tid | nbytes |                        some_tids
 439 -----------+--------+----------------------------------------------------------
 440  (8,41)    |    244 | {"(8,41)","(8,43)","(8,44)","(8,45)","(8,46)"}
 441  (10,45)   |    248 | {"(10,45)","(10,46)","(10,47)","(10,48)","(10,49)"}
 442  (12,52)   |    248 | {"(12,52)","(12,53)","(12,54)","(12,55)","(12,56)"}
 443  (14,59)   |    320 | {"(14,59)","(14,60)","(14,61)","(14,62)","(14,63)"}
 444  (167,16)  |    376 | {"(167,16)","(167,17)","(167,18)","(167,19)","(167,20)"}
 445  (170,30)  |    376 | {"(170,30)","(170,31)","(170,32)","(170,33)","(170,34)"}
 446  (173,44)  |    197 | {"(173,44)","(173,45)","(173,46)","(173,47)","(173,48)"}
 447 (7 rows)
 448 </screen>
 449      </para>
 450     </listitem>
 451    </varlistentry>
 452
 453    <varlistentry>
 454     <term>
 455      <function>fsm_page_contents(page bytea) returns text</function>
 456      <indexterm>
 457       <primary>fsm_page_contents</primary>
 458      </indexterm>
 459     </term>
 460
 461     <listitem>
 462      <para>
 463       <function>fsm_page_contents</function> shows the internal node structure
 464       of a FSM page. The output is a multiline string, with one line per
 465       node in the binary tree within the page. Only those nodes that are not
 466       zero are printed. The so-called "next" pointer, which points to the
 467       next slot to be returned from the page, is also printed.
 468      </para>
 469      <para>
 470       See <filename>src/backend/storage/freespace/README</> for more
 471       information on the structure of an FSM page.
 472      </para>
 473     </listitem>
 474    </varlistentry>
 475   </variablelist>
 476  </sect2>
 477
 478 </sect1>