Postgresql 中文操作指南

F.36. pg_visibility — visibility map information and utilities #

pg_visibility 模块提供了一种检查表的可见性地图 (VM) 和页面级别可见性信息的方法。它还提供函数来检查可见性地图的完整性并强制重建它。

The pg_visibility module provides a means for examining the visibility map (VM) and page-level visibility information of a table. It also provides functions to check the integrity of a visibility map and to force it to be rebuilt.

三个不同的位用于存储有关页面级可见性的信息。可见性地图中的 all-visible 位表示关系的对应页面中的每个元组对于当前和将来的事务都是可见的。可见性地图中的 all-frozen 位表示页面中的每个元组都被冻结；也就是说，在元组在该页面上被插入、更新、删除或锁定之前，将来的真空不需要修改该页面。页面头的 PD_ALL_VISIBLE 位与可见性地图中的 all-visible 位有相同的含义，但存储在数据页面本身中，而不是存储在单独的数据结构中。这两个位通常会一致，但有时在崩溃恢复后，页面的 all-visible 位可能会设置，而可见性地图位则清除。报告的值也可能因在 pg_visibility 检查可见性地图后但在检查数据页面之前发生的变化而不同。任何导致数据损坏的事件也会导致这些位不一致。

Three different bits are used to store information about page-level visibility. The all-visible bit in the visibility map indicates that every tuple in the corresponding page of the relation is visible to every current and future transaction. The all-frozen bit in the visibility map indicates that every tuple in the page is frozen; that is, no future vacuum will need to modify the page until such time as a tuple is inserted, updated, deleted, or locked on that page. The page header’s PD_ALL_VISIBLE bit has the same meaning as the all-visible bit in the visibility map, but is stored within the data page itself rather than in a separate data structure. These two bits will normally agree, but the page’s all-visible bit can sometimes be set while the visibility map bit is clear after a crash recovery. The reported values can also disagree because of a change that occurs after pg_visibility examines the visibility map and before it examines the data page. Any event that causes data corruption can also cause these bits to disagree.

显示有关 PD_ALL_VISIBLE 位的信息的函数比只查询可见性地图的函数要昂贵得多，因为它们必须读取关系的数据块，而不仅仅是（小得多的）可见性地图。检查关系数据块的函数也同样昂贵。

Functions that display information about PD_ALL_VISIBLE bits are much more costly than those that only consult the visibility map, because they must read the relation’s data blocks rather than only the (much smaller) visibility map. Functions that check the relation’s data blocks are similarly expensive.

F.36.1. Functions #

  • pg_visibility_map(relation regclass, blkno bigint, all_visible OUT boolean, all_frozen OUT boolean) returns record

    • Returns the all-visible and all-frozen bits in the visibility map for the given block of the given relation.

  • pg_visibility(relation regclass, blkno bigint, all_visible OUT boolean, all_frozen OUT boolean, pd_all_visible OUT boolean) returns record

    • Returns the all-visible and all-frozen bits in the visibility map for the given block of the given relation, plus the PD_ALL_VISIBLE bit of that block.

  • pg_visibility_map(relation regclass, blkno OUT bigint, all_visible OUT boolean, all_frozen OUT boolean) returns setof record

    • Returns the all-visible and all-frozen bits in the visibility map for each block of the given relation.

  • pg_visibility(relation regclass, blkno OUT bigint, all_visible OUT boolean, all_frozen OUT boolean, pd_all_visible OUT boolean) returns setof record

    • Returns the all-visible and all-frozen bits in the visibility map for each block of the given relation, plus the PD_ALL_VISIBLE bit of each block.

  • pg_visibility_map_summary(relation regclass, all_visible OUT bigint, all_frozen OUT bigint) returns record

    • Returns the number of all-visible pages and the number of all-frozen pages in the relation according to the visibility map.

  • pg_check_frozen(relation regclass, t_ctid OUT tid) returns setof tid

    • Returns the TIDs of non-frozen tuples stored in pages marked all-frozen in the visibility map. If this function returns a non-empty set of TIDs, the visibility map is corrupt.

  • pg_check_visible(relation regclass, t_ctid OUT tid) returns setof tid

    • Returns the TIDs of non-all-visible tuples stored in pages marked all-visible in the visibility map. If this function returns a non-empty set of TIDs, the visibility map is corrupt.

  • pg_truncate_visibility_map(relation regclass) returns void

    • Truncates the visibility map for the given relation. This function is useful if you believe that the visibility map for the relation is corrupt and wish to force rebuilding it. The first VACUUM executed on the given relation after this function is executed will scan every page in the relation and rebuild the visibility map. (Until that is done, queries will treat the visibility map as containing all zeroes.)

默认情况下，这些函数只能由超级用户和具有 pg_stat_scan_tables 角色权限的角色执行，但 pg_truncate_visibility_map(relation regclass) 除外，该角色只能由超级用户执行。

By default, these functions are executable only by superusers and roles with privileges of the pg_stat_scan_tables role, with the exception of pg_truncate_visibility_map(relation regclass) which can only be executed by superusers.

F.36.2. Author #

Robert Haas < link:mailto:rhaas@postgresql.org[rhaas@postgresql.org]>

Robert Haas <link:mailto:rhaas@postgresql.org[rhaas@postgresql.org]>