| 1 |
<!-- doc/src/sgml/pgvisibility.sgml --> |
| 2 |
|
| 3 |
<sect1 id="pgvisibility" xreflabel="pg_visibility"> |
| 4 |
<title>pg_visibility — visibility map information and utilities</title> |
| 5 |
|
| 6 |
<indexterm zone="pgvisibility"> |
| 7 |
<primary>pg_visibility</primary> |
| 8 |
</indexterm> |
| 9 |
|
| 10 |
<para> |
| 11 |
The <filename>pg_visibility</filename> module provides a means for examining the |
| 12 |
visibility map (VM) and page-level visibility information of a table. |
| 13 |
It also provides functions to check the integrity of a visibility map and to |
| 14 |
force it to be rebuilt. |
| 15 |
</para> |
| 16 |
|
| 17 |
<para> |
| 18 |
Three different bits are used to store information about page-level |
| 19 |
visibility. The all-visible bit in the visibility map indicates that every |
| 20 |
tuple in the corresponding page of the relation is visible to every current |
| 21 |
and future transaction. The all-frozen bit in the visibility map indicates |
| 22 |
that every tuple in the page is frozen; that is, no future vacuum will need |
| 23 |
to modify the page until such time as a tuple is inserted, updated, deleted, |
| 24 |
or locked on that page. |
| 25 |
The page header's <literal>PD_ALL_VISIBLE</literal> bit has the |
| 26 |
same meaning as the all-visible bit in the visibility map, but is stored |
| 27 |
within the data page itself rather than in a separate data structure. |
| 28 |
These two bits will normally agree, but the page's all-visible bit can |
| 29 |
sometimes be set while the visibility map bit is clear after a crash |
| 30 |
recovery. The reported values can also disagree because of a change that |
| 31 |
occurs after <literal>pg_visibility</literal> examines the visibility map and |
| 32 |
before it examines the data page. Any event that causes data corruption |
| 33 |
can also cause these bits to disagree. |
| 34 |
</para> |
| 35 |
|
| 36 |
<para> |
| 37 |
Functions that display information about <literal>PD_ALL_VISIBLE</literal> bits |
| 38 |
are much more costly than those that only consult the visibility map, |
| 39 |
because they must read the relation's data blocks rather than only the |
| 40 |
(much smaller) visibility map. Functions that check the relation's |
| 41 |
data blocks are similarly expensive. |
| 42 |
</para> |
| 43 |
|
| 44 |
<sect2 id="pgvisibility-funcs"> |
| 45 |
<title>Functions</title> |
| 46 |
|
| 47 |
<variablelist> |
| 48 |
<varlistentry> |
| 49 |
<term><function>pg_visibility_map(relation regclass, blkno bigint, all_visible OUT boolean, all_frozen OUT boolean) returns record</function></term> |
| 50 |
<listitem> |
| 51 |
<para> |
| 52 |
Returns the all-visible and all-frozen bits in the visibility map for |
| 53 |
the given block of the given relation. |
| 54 |
</para> |
| 55 |
</listitem> |
| 56 |
</varlistentry> |
| 57 |
|
| 58 |
<varlistentry> |
| 59 |
<term><function>pg_visibility(relation regclass, blkno bigint, all_visible OUT boolean, all_frozen OUT boolean, pd_all_visible OUT boolean) returns record</function></term> |
| 60 |
<listitem> |
| 61 |
<para> |
| 62 |
Returns the all-visible and all-frozen bits in the visibility map for |
| 63 |
the given block of the given relation, plus the |
| 64 |
<literal>PD_ALL_VISIBLE</literal> bit of that block. |
| 65 |
</para> |
| 66 |
</listitem> |
| 67 |
</varlistentry> |
| 68 |
|
| 69 |
<varlistentry> |
| 70 |
<term><function>pg_visibility_map(relation regclass, blkno OUT bigint, all_visible OUT boolean, all_frozen OUT boolean) returns setof record</function></term> |
| 71 |
<listitem> |
| 72 |
<para> |
| 73 |
Returns the all-visible and all-frozen bits in the visibility map for |
| 74 |
each block of the given relation. |
| 75 |
</para> |
| 76 |
</listitem> |
| 77 |
</varlistentry> |
| 78 |
|
| 79 |
<varlistentry> |
| 80 |
<term><function>pg_visibility(relation regclass, blkno OUT bigint, all_visible OUT boolean, all_frozen OUT boolean, pd_all_visible OUT boolean) returns setof record</function></term> |
| 81 |
|
| 82 |
<listitem> |
| 83 |
<para> |
| 84 |
Returns the all-visible and all-frozen bits in the visibility map for |
| 85 |
each block of the given relation, plus the <literal>PD_ALL_VISIBLE</literal> |
| 86 |
bit of each block. |
| 87 |
</para> |
| 88 |
</listitem> |
| 89 |
</varlistentry> |
| 90 |
|
| 91 |
<varlistentry> |
| 92 |
<term><function>pg_visibility_map_summary(relation regclass, all_visible OUT bigint, all_frozen OUT bigint) returns record</function></term> |
| 93 |
|
| 94 |
<listitem> |
| 95 |
<para> |
| 96 |
Returns the number of all-visible pages and the number of all-frozen |
| 97 |
pages in the relation according to the visibility map. |
| 98 |
</para> |
| 99 |
</listitem> |
| 100 |
</varlistentry> |
| 101 |
|
| 102 |
<varlistentry> |
| 103 |
<term><function>pg_check_frozen(relation regclass, t_ctid OUT tid) returns setof tid</function></term> |
| 104 |
|
| 105 |
<listitem> |
| 106 |
<para> |
| 107 |
Returns the TIDs of non-frozen tuples stored in pages marked all-frozen |
| 108 |
in the visibility map. If this function returns a non-empty set of |
| 109 |
TIDs, the visibility map is corrupt. |
| 110 |
</para> |
| 111 |
</listitem> |
| 112 |
</varlistentry> |
| 113 |
|
| 114 |
<varlistentry> |
| 115 |
<term><function>pg_check_visible(relation regclass, t_ctid OUT tid) returns setof tid</function></term> |
| 116 |
|
| 117 |
<listitem> |
| 118 |
<para> |
| 119 |
Returns the TIDs of non-all-visible tuples stored in pages marked |
| 120 |
all-visible in the visibility map. If this function returns a non-empty |
| 121 |
set of TIDs, the visibility map is corrupt. |
| 122 |
</para> |
| 123 |
</listitem> |
| 124 |
</varlistentry> |
| 125 |
|
| 126 |
<varlistentry> |
| 127 |
<term><function>pg_truncate_visibility_map(relation regclass) returns void</function></term> |
| 128 |
|
| 129 |
<listitem> |
| 130 |
<para> |
| 131 |
Truncates the visibility map for the given relation. This function is |
| 132 |
useful if you believe that the visibility map for the relation is |
| 133 |
corrupt and wish to force rebuilding it. The first <command>VACUUM</command> |
| 134 |
executed on the given relation after this function is executed will scan |
| 135 |
every page in the relation and rebuild the visibility map. (Until that |
| 136 |
is done, queries will treat the visibility map as containing all zeroes.) |
| 137 |
</para> |
| 138 |
</listitem> |
| 139 |
</varlistentry> |
| 140 |
</variablelist> |
| 141 |
|
| 142 |
<para> |
| 143 |
By default, these functions are executable only by superusers and roles with privileges |
| 144 |
of the <literal>pg_stat_scan_tables</literal> role, with the exception of |
| 145 |
<function>pg_truncate_visibility_map(relation regclass)</function> which can only |
| 146 |
be executed by superusers. |
| 147 |
</para> |
| 148 |
</sect2> |
| 149 |
|
| 150 |
<sect2 id="pgvisibility-author"> |
| 151 |
<title>Author</title> |
| 152 |
|
| 153 |
<para> |
| 154 |
Robert Haas <email>[email protected]</email> |
| 155 |
</para> |
| 156 |
</sect2> |
| 157 |
|
| 158 |
</sect1> |