PostgreSQL-8中的MVCC。冷冻

最新推荐文章于 2022-06-13 16:26:41 发布

cullen2012

最新推荐文章于 2022-06-13 16:26:41 发布

阅读量342

点赞数

文章标签：数据库 java python mysql linux

原文链接：https://habr.com/en/company/postgrespro/blog/487590/

版权

isolation, made a digression about 隔离相关的问题入手，对 low-level data structure, discussed 低级数据结构进行了论述，详细讨论了 row versions in detail and observed how 行版本，并观察了如何从行版本中获取 data snapshots are obtained from row versions. 数据快照。

Then we covered different vacuuming techniques: in-page vacuum (along with HOT updates), vacuum and autovacuum.

然后，我们覆盖不同的吸尘技术：在页面的真空 (热更新一起)，真空和自动清理。

Now we've reached the last topic of this series. We will talk on the transaction id wraparound and freezing.

现在，我们已经达到了本系列的最后一个主题。我们将讨论事务ID的环绕和冻结。

交易ID环绕 (Transaction ID wraparound)

PostgreSQL uses 32-bit transaction IDs. This is a pretty large number (about 4 billion), but with intensive work of the server, this number is not unlikely to get exhausted. For example: with the workload of 1000 transactions a second, this will happen as early as in one month and a half of continuous work.

PostgreSQL使用32位事务ID。这是一个相当大的数字(大约40亿)，但是随着服务器的大量工作，这个数字不太可能被耗尽。例如：每秒处理1000次事务，这种情况最早要在一个半月的连续工作中发生。

But we've mentioned that multiversion concurrency control relies on the sequential numbering, which means that of two transactions the one with a smaller number can be considered to have started earlier. Therefore, it is clear that it is not an option to just reset the counter and start the numbering from scratch.

但是我们已经提到，多版本并发控制依赖于顺序编号，这意味着在两个事务中，数目较小的事务可以被认为是较早开始的。因此，很明显，重置计数器并从头开始编号不是一个选择。

But why not use 64-bit transaction IDs — wouldn't it completely eliminate the issue? The thing is that the header of each tuple (as discussed earlier) stores two transaction IDs: xmin and xmax. The header is pretty large as it is — 23 bytes at a minimum, and the increase of the bit size would entail the increase of the header by extra 8 bytes. And this is out of all reason.

但是，为什么不使用64位事务处理ID-不能完全消除问题呢？关键是每个元组的标头( 如前所述 )存储两个事务ID： xmin和xmax 。标头相当大-至少23个字节，并且位大小的增加将使标头增加额外的8个字节。这是完全没有道理的。

xmin and xmin和 xmax are still 32-bit, and a page header stores the «beginning of the epoch», which is common for the whole page. xmax仍然是32位，并且页眉存储着“纪元的开始”，这对于整个页面。

So what's to be done? Instead of ordering transaction IDs sequentially (as numbers), imagine a circle or a clock dial. Transaction IDs are compared in the same sense as clock readings are compared. That is, for each transaction, the «counterclockwise» half of transaction IDs is regarded to pertain to the past, while the «clockwise» part is regarded to pertain to the future.

那该怎么办呢？与其按顺序(按数字)顺序排列事务标识，不如想象一个圆圈或一个钟盘。以与比较时钟读数相同的方式比较事务ID。也就是说，对于每笔交易，交易ID的“逆时针”部分被认为与过去有关，而“顺时针”部分被视为与未来有关。

The age of the transaction is defined as the number of transactions that ran since the time when the transaction occurred in the system (regardless of the transaction ID wraparound). To figure out whether one transaction is older than the other, we compare their ages rather than IDs. (By the way, it's for this reason that the operations «greater» and «less» are not defined for the xid data type.)

事务的期限定义为自系统中发生事务以来(不考虑事务ID绕行)开始运行的事务数。为了确定一笔交易是否比另一笔交易更早，我们比较了它们的年龄而不是ID。 (顺便说一下，正是由于这个原因，未为xid数据类型定义“更大”和“更少”操作。)

But this looped arrangement is troublesome. A transaction that was in the distant past (transaction 1 in the figure), some time later will get into the half of the circle pertinent to the future. This, certainly, breaks visibility rules and would cause issues: changes done by the transaction 1 would just fall out of sight.

但是这种循环安排是麻烦的。一段遥远的过去的交易(图中的交易1)将在一段时间后进入与未来有关的圈子的一半。当然，这会破坏可见性规则，并会引发问题：事务1所做的更改将不可见。

元组冻结和可见性规则 (Tuple freezing and visibility rules)

To prevent such «travels» from the past to future, vacuuming does one more task (in addition to freeing page space). It finds pretty old and «cold» tuples (which are visible in all snapshots and are unlikely to change) and marks them in a special way, that is, «freezes» them. A frozen tuple is considered older than any normal data and is always visible in all snapshots. And it is no longer needed to look at the xmin transaction number, and this number can be safely reused. So, frozen tuples always remain in the past.

为了防止这种“旅行”在过去到将来发生，清理操作还需要执行另一项任务(除了释放页面空间之外)。它会找到相当旧的和“冷的”元组(在所有快照中可见，并且不太可能更改)，并以特殊的方式标记它们，即“冻结”它们。冻结元组被认为比任何普通数据都旧，并且在所有快照中始终可见。并且不再需要查看xmin事务编号，并且可以安全地重用该编号。因此，冷冻元组始终保留在过去。

To track the xmin transaction as frozen, both hint bits are set: committed and aborted.

为了跟踪冻结的xmin事务，两个提示位都设置为： committed和aborted 。

Note that the xmax transaction does not need freezing. Its existence indicates that the tuple is not live anymore. When it gets no longer visible in data snapshots, this tuple will be vacuumed away.

请注意， xmax事务不需要冻结。它的存在表明该元组不再存在。当它不再在数据快照中可见时，该元组将被清除。

Let's create a table for experiments. And let's specify the minimum fillfactor for it such that only two rows fit on each page — this makes it more convenient for us to watch what is happening. Let's also turn autovacuum off to control the vacuuming time on our own.

让我们为实验创建一个表。让我们为其指定最小填充因子，以使每页仅容纳两行-这使我们更方便地观察正在发生的事情。让我们也关闭自动真空以自行控制吸尘时间。

=> CREATE TABLE tfreeze(
  id integer,
  s char(300)
) WITH (fillfactor = 10, autovacuum_enabled = off);

We've already created a few variants of the function that uses the «pageinspect» extension to show the tuples located on a page. We will now create one more variant of this function: it will show several pages at once and output the age of the xmin transaction (using the age system function):

我们已经创建了一些函数的变体，这些变体使用«pageinspect»扩展名来显示页面上的元组。现在，我们将创建此函数的另一个变体：它将一次显示多个页面，并输出xmin事务的使用xmin (使用age系统功能)：

=> CREATE FUNCTION heap_page(relname text, pageno_from integer, pageno_to integer)
RETURNS TABLE(ctid tid, state text, xmin text, xmin_age integer, xmax text, t_ctid tid)
AS $$
SELECT (pageno,lp)::text::tid AS ctid,
       CASE lp_flags
         WHEN 0 THEN 'unused'
         WHEN 1 THEN 'normal'
         WHEN 2 THEN 'redirect to '||lp_off
         WHEN 3 THEN 'dead'
       END AS state,
       t_xmin || CASE
         WHEN (t_infomask & 256+512) = 256+512 THEN ' (f)'
         WHEN (t_infomask & 256) > 0 THEN ' (c)'
         WHEN (t_infomask & 512) > 0 THEN ' (a)'
         ELSE ''
       END AS xmin,
      age(t_xmin) xmin_age,
       t_xmax || CASE
         WHEN (t_infomask & 1024) > 0 THEN ' (c)'
         WHEN (t_infomask & 2048) > 0 THEN ' (a)'
         ELSE ''
       END AS xmax,
       t_ctid
FROM generate_series(pageno_from, pageno_to) p(pageno),
     heap_page_items(get_raw_page(relname, pageno))
ORDER BY pageno, lp;
$$ LANGUAGE SQL;

Note that both committed and aborted hint bits set indicate freezing (which we denote with a parenthesized «f»). Multiple sources (including the documentation) mention a specialized ID to indicate frozen transactions: FrozenTransactionId = 2. This method was in place in PostgreSQL versions earlier than 9.4, and now it is replaced with hint bits. This permits to retain the initial transaction number in a tuple, which is convenient for maintenance and debugging. However, you can still come across transactions with ID = 2 in old systems, even upgraded to latest versions.

请注意，已committed和已aborted提示位都设置为冻结(我们用括号“ f”表示)。多个来源(包括文档)都提到一个专门的ID来指示冻结的事务：FrozenTransactionId =2。此方法在9.4之前的PostgreSQL版本中已经存在，现在已由提示位代替。这允许将初始事务编号保留在元组中，这便于维护和调试。但是，您仍然可以在旧系统中遇到ID = 2的事务，甚至可以升级到最新版本。

We will also need the «pg_visibility» extension, which enables us to look into the visibility map:

我们还将需要«pg_visibility»扩展名，它使我们能够查看可见性图：

=> CREATE EXTENSION pg_visibility;

In PostgreSQL versions earlier than 9.6, the visibility map contained one bit per page; the map tracked only pages with «pretty old» row versions, which are visible in all data snapshots for sure. The idea behind this is that if a page is tracked in the visibility map, the visibility rules for its tuples do not need to be checked.

在9.6之前的PostgreSQL版本中，可见性映射每页只包含一位。该地图仅跟踪具有“相当旧”行版本的页面，这些页面肯定会在所有数据快照中可见。其背后的想法是，如果在可见性地图中跟踪页面，则无需检查其元组的可见性规则。

Starting with version 9.6, the all-frozen bit for each page was added to the visibility map. The all-frozen bit tracks pages where all tuples are frozen.

从9.6版开始，每页的全冻结位已添加到可见性地图中。全冻结位跟踪所有元组都冻结的页面。

Let's insert a few rows into the table and immediately do vacuuming for the visibility map to be created:

让我们在表中插入几行，并立即进行吸尘以创建可见性图：

=> INSERT INTO tfreeze(id, s)
  SELECT g.id, 'FOO' FROM generate_series(1,100) g(id);
=> VACUUM tfreeze;

And we can see that both pages are known to be all-visible, but not to be all-frozen:

而且我们可以看到这两个页面都是可见的，但并不是全部冻结的：

=> SELECT * FROM generate_series(0,1) g(blkno), pg_visibility_map('tfreeze',g.blkno)
ORDER BY g.blkno;

blkno | all_visible | all_frozen 
-------+-------------+------------
     0 | t           | f
     1 | t           | f
(2 rows)

The age of the transaction that created the rows (xmin_age) equals one — this is the last transaction performed in the system:

创建行的事务的年龄( xmin_age )等于一-这是系统中最后执行的事务：

=> SELECT * FROM heap_page('tfreeze',0,1);

ctid  | state  |  xmin   | xmin_age | xmax  | t_ctid 
-------+--------+---------+----------+-------+--------
 (0,1) | normal | 697 (c) |        1 | 0 (a) | (0,1)
 (0,2) | normal | 697 (c) |        1 | 0 (a) | (0,2)
 (1,1) | normal | 697 (c) |        1 | 0 (a) | (1,1)
 (1,2) | normal | 697 (c) |        1 | 0 (a) | (1,2)
(4 rows)

最低冷冻年龄 (Minimum age for freezing)

Three main parameters control freezing, and we will discuss them one after another.

三个主要参数控制冻结，我们将逐一讨论它们。

Let's start with vacuum_freeze_min_age, which defines the minimum age of the xmin transaction for which a tuple can be frozen. The smaller this value, the more extra overhead there may be: if we deal with «hot», intensively changing, data, freezing of new and newer tuples will go down the drain. In this case, it's better to wait.

让我们从vacuum_freeze_min_age开始，它定义了可以冻结元组的xmin事务的最小期限。该值越小，可能会有更多的额外开销：如果我们处理“热”，密集变化的数据，冻结新的和更新的元组，将会耗费大量精力。在这种情况下，最好等待。

The default value of this parameter specifies that a transaction starts getting frozen when 50 million of other transactions ran since the time it occurred:

此参数的默认值指定自发生时间起，当运行了5000万其他交易时，交易开始冻结：

=> SHOW vacuum_freeze_min_age;

vacuum_freeze_min_age 
-----------------------
 50000000
(1 row)

To watch freezing, let's reduce the value of this parameter to one.

为了观察冻结，让我们将此参数的值减小为一。

=> ALTER SYSTEM SET vacuum_freeze_min_age = 1;
=> SELECT pg_reload_conf();

And let's update one row on the zero page. The new version will get onto the same page because of the small fillfactor.

让我们更新零页上的一行。由于fillfactor小，新版本将进入同一页面。

=> UPDATE tfreeze SET s = 'BAR' WHERE id = 1;

This is what we now see on the data pages:

现在，我们在数据页面上看到以下内容：

=> SELECT * FROM heap_page('tfreeze',0,1);

ctid  | state  |  xmin   | xmin_age | xmax  | t_ctid 
-------+--------+---------+----------+-------+--------
 (0,1) | normal | 697 (c) |        2 | 698   | (0,3)
 (0,2) | normal | 697 (c) |        2 | 0 (a) | (0,2)
 (0,3) | normal | 698     |        1 | 0 (a) | (0,3)
 (1,1) | normal | 697 (c) |        2 | 0 (a) | (1,1)
 (1,2) | normal | 697 (c) |        2 | 0 (a) | (1,2)
(5 rows)

Now the rows older than vacuum_freeze_min_age = 1 are to be frozen. But note that the zero row is not tracked in the visibility map (the UPDATE command that changed the page reset the bit), while the first one is still tracked:

现在，要冻结早于vacuum_freeze_min_age = 1的行。但是请注意，在可见性图中未跟踪零行(更改页面的UPDATE命令将重置该位)，而仍跟踪第一个：

=> SELECT * FROM generate_series(0,1) g(blkno), pg_visibility_map('tfreeze',g.blkno)
ORDER BY g.blkno;

blkno | all_visible | all_frozen 
-------+-------------+------------
     0 | f           | f
     1 | t           | f
(2 rows)

We've already discussed that vacuuming looks only through pages not tracked in the visibility map. And this is the case:

我们已经讨论过，吸尘仅通过可见性图中未跟踪的页面进行。就是这种情况：

=> VACUUM tfreeze;
=> SELECT * FROM heap_page('tfreeze',0,1);

ctid  |     state     |  xmin   | xmin_age | xmax  | t_ctid 
-------+---------------+---------+----------+-------+--------
 (0,1) | redirect to 3 |         |          |       | 
 (0,2) | normal        | 697 (f) |        2 | 0 (a) | (0,2)
 (0,3) | normal        | 698 (c) |        1 | 0 (a) | (0,3)
 (1,1) | normal        | 697 (c) |        2 | 0 (a) | (1,1)
 (1,2) | normal        | 697 (c) |        2 | 0 (a) | (1,2)
(5 rows)

On the zero page, one version is frozen, but vacuuming did not look into the first page at all. So, if only live tuples are left on a page, vacuuming will not access this page and will not freeze them.

在零页上，一个版本被冻结，但是吸尘根本没有进入第一页。因此，如果在页面上仅保留活动元组，则清理将不会访问该页面，也不会冻结它们。

=> SELECT * FROM generate_series(0,1) g(blkno), pg_visibility_map('tfreeze',g.blkno)
ORDER BY g.blkno;

blkno | all_visible | all_frozen 
-------+-------------+------------
     0 | t           | f
     1 | t           | f
(2 rows)

冻结整个表的年龄 (Age to freeze the entire table)

To freeze the tuples left on pages where vacuuming does not normally look, the second parameter is provided: vacuum_freeze_table_age. It defines the age of the transaction for which vacuuming ignores the visibility map and looks through all the table pages in order to do freezing.

要冻结通常看起来没有吸尘的页面上留下的元组，请提供第二个参数： vacuum_freeze_table_age 。它定义了事务处理的年龄，对于该事务处理，清理将忽略可见性图，并浏览所有表页面以进行冻结。

Each page stores the transaction ID for which all the older transactions are known to be frozen for sure (pg_class.relfrozenxid). And this is the age of this stored transaction that the value of the vacuum_freeze_table_age parameter is compared to.

每个页面存储的事务ID(肯定会冻结所有旧事务)( pg_class.relfrozenxid )。这是此存储事务的使用期限，将其与vacuum_freeze_table_age参数的值进行比较。

=> SELECT relfrozenxid, age(relfrozenxid) FROM pg_class WHERE relname = 'tfreeze';

relfrozenxid | age 
--------------+-----
          694 |   5
(1 row)

Before PostgreSQL 9.6, each time vacuuming did full scan of a table in order to visit all the pages for sure. For large-size tables this operation was long and sad. The matter was even worse because if vacuuming did not complete (for example, an impatient admin interrupted the command), the process had to be started from the very beginning.

在PostgreSQL 9.6之前，每次清理都会对表进行完全扫描，以确保能够访问所有页面。对于大型桌子，此操作很长且令人难过。事情变得更糟，因为如果无法完成清理工作(例如，急躁的管理员打断了该命令)，则该过程必须从头开始。

Since version 9.6, thanks to the all-frozen bit (which we can see in the all_frozen column in the pg_visibility_map output), vacuuming goes only through pages for which the bit is not set yet. This ensures not only a considerably smaller amount of work, but also interrupt tolerance: if a vacuum process is stopped and restarted, it will not have to look again into the pages for which it already set the all-frozen bit last time.

从9.6版开始，由于使用了全冻结位(我们可以在pg_visibility_map输出的all_frozen列中pg_visibility_map )，清理仅通过尚未设置该位的页面进行。这样不仅可以确保工作量少得多，而且可以确保中断容忍度：如果真空过程停止并重新启动，则不必再次查看其上次已设置全冻结位的页面。

Anyway, all table pages get frozen once every (vacuum_freeze_table_age − vacuum_freeze_min_age) transactions. With the default values, this happens once a million transactions:

无论如何，所有表页面每( transaction_freeze_table_age - vacuum_freeze_min_age )都会冻结一次。使用默认值时，这种情况发生在一百万次交易中：

=> SHOW vacuum_freeze_table_age;

vacuum_freeze_table_age 
-------------------------
 150000000
(1 row)

So it is clear that a too large value of vacuum_freeze_min_age is not an option either since this will start increasing the overhead rather than reduce it.

因此很明显，也不大可能选择vacuum_freeze_min_age值，因为这将开始增加开销而不是减少开销。

Let's see how freezing of an entire table is done, and to this end, we'll reduce vacuum_freeze_table_age to 5, so that the freezing condition is met.

让我们看看如何冻结整个表，为此，我们将vacuum_freeze_table_age减小为5，以便满足冻结条件。

=> ALTER SYSTEM SET vacuum_freeze_table_age = 5;
=> SELECT pg_reload_conf();

Let's do freezing:

让我们冻结：

=> VACUUM tfreeze;

Now, since the entire table was checked for sure, the ID of the frozen transaction can be increased because we are certain that no older unfrozen transaction is left on the pages.

现在，由于确定要检查整个表，因此可以增加冻结交易的ID，因为我们可以确定页面上没有剩余较旧的未冻结交易。

=> SELECT relfrozenxid, age(relfrozenxid) FROM pg_class WHERE relname = 'tfreeze';

relfrozenxid | age 
--------------+-----
          698 |   1
(1 row)

Now all the tuples on the first page are frozen:

现在，第一页上的所有元组都被冻结：

=> SELECT * FROM heap_page('tfreeze',0,1);

ctid  |     state     |  xmin   | xmin_age | xmax  | t_ctid 
-------+---------------+---------+----------+-------+--------
 (0,1) | redirect to 3 |         |          |       | 
 (0,2) | normal        | 697 (f) |        2 | 0 (a) | (0,2)
 (0,3) | normal        | 698 (c) |        1 | 0 (a) | (0,3)
 (1,1) | normal        | 697 (f) |        2 | 0 (a) | (1,1)
 (1,2) | normal        | 697 (f) |        2 | 0 (a) | (1,2)
(5 rows)

Besides, the first page is known to be all-frozen:

此外，第一页是全冻结的：

=> SELECT * FROM generate_series(0,1) g(blkno), pg_visibility_map('tfreeze',g.blkno)
ORDER BY g.blkno;

blkno | all_visible | all_frozen 
-------+-------------+------------
     0 | t           | f
     1 | t           | t
(2 rows)

“激进”冻结的年龄 (Age for «aggressive» freezing)

Timely freezing of tuples is essential. If a transaction that is not frozen yet faces a risk to get to the future, PostgreSQL will shutdown in order to prevent possible issues.

及时冻结元组至关重要。如果未冻结的事务面临未来的风险，PostgreSQL将关闭以防止可能出现的问题。

Why can this happen? There are various reasons.

为什么会发生这种情况？原因有很多。

Autovacuum may be turned off, and VACUUM is not launched either. We've already mentioned that you should not do so, but this is technically possible.
自动真空可能已关闭，VACUUM也未启动。我们已经提到您不应该这样做，但这在技术上是可行的。
Even if autovacuum is turned on, it does not reach unused databases (remember the track_counts parameter and «template0» database).
即使打开了autovacuum，它也不会到达未使用的数据库(请记住track_counts参数和«template0»数据库)。
As we observed last time, vacuuming skips tables where data is only added, but not deleted or changed.
正如我们上次观察到的，清理操作会跳过仅添加数据但不删除或更改数据的表。

To respond to these, «aggressive» freezing is provided, which is controlled by the autovacuum_freeze_max_age parameter. If a table in some database may have an unfrozen transaction that is older that the age specified in the age parameter, forced autovacuuming is launched (even if it is turned off) and the processes will sooner or later reach the problematic table (regardless of usual criteria).

为了响应这些问题，提供了“激进”冻结，这由autovacuum_freeze_max_age参数控制。如果某个数据库中的表的未冻结事务早于age参数中指定的年龄，则将启动强制自动清理(即使已关闭)，并且进程迟早会到达有问题的表(无论通常如何)条件)。

The default value is pretty conservative:

默认值非常保守：

=> SHOW autovacuum_freeze_max_age;

autovacuum_freeze_max_age 
---------------------------
 200000000
(1 row)

The limitation for autovacuum_freeze_max_age is 2 billion transactions, and the value used is 10 times smaller. And this makes sense: by increasing the value we also increase the risk for autovacuuming to be unable to freeze all the necessary rows during the time interval left.

autovacuum_freeze_max_age的限制为20亿个事务，并且使用的值小10倍。这是有道理的：通过增加该值，我们还增加了自动抽真空的风险，因为自动抽真空在剩余的时间间隔内无法冻结所有必要的行。

Besides, the value of this parameter determines the size of the XACT structure: since the system must not retain older transactions that may require the status to be found out, autovacuuming frees space by deleting unneeded segment files of XACT.

此外，此参数的值确定XACT结构的大小：由于系统不得保留可能需要了解其状态的较旧事务，因此，自动清空将通过删除不需要的XACT段文件来释放空间。

Let's look at how vacuuming handles append-only tables by example of «tfreeze». Autovacuum is turned off for this table, but even this won't hinder.

让我们看一下“ tfreeze”示例如何用吸尘器处理仅追加表。该表的自动真空功能已关闭，但即使这样也不会受到阻碍。

The change of the autovacuum_freeze_max_age parameter requires the server to restart. But you can also set all the above parameters at the level of separate tables by means of storage parameters. This usually makes sense to do only in special situations when the table does require special handling.

更改autovacuum_freeze_max_age参数要求服务器重新启动。但是，您也可以通过存储参数在单独的表级别设置所有上述参数。通常只有在表格确实需要特殊处理的情况下，才有意义。

So, we'll set autovacuum_freeze_max_age at the table level (and revert to the normal fillfactor at the same time). Unfortunately the minimum possible value is 100 000:

因此，我们将在表级别设置autovacuum_freeze_max_age (并同时恢复为正常的fillfactor)。不幸的是，最小可能值为100 000：

=> ALTER TABLE tfreeze SET (autovacuum_freeze_max_age = 100000, fillfactor = 100);

Unfortunately — because we will have to perform 100 000 transactions to reproduce the situation of interest. But for practical use this is, certainly, an extremely low value.

不幸的是，因为我们必须执行10万笔交易才能重现您所关注的情况。但是，对于实际使用，这无疑是一个极低的值。

Since we are going to add data, let's insert 100 000 rows into the table, each in its own transaction. And again, note that you should avoid doing so in a real-case scenario. But we are only researching, so we are permitted.

由于我们要添加数据，因此让我们将10万行插入到表中，每个行都有自己的事务。再次提醒您，在实际情况下应避免这样做。但是我们只是在研究，所以我们被允许。

=> CREATE PROCEDURE foo(id integer) AS $$
BEGIN
  INSERT INTO tfreeze VALUES (id, 'FOO');
  COMMIT;
END;
$$ LANGUAGE plpgsql;

=> DO $$
BEGIN
  FOR i IN 101 .. 100100 LOOP
    CALL foo(i);
  END LOOP;
END;
$$;

As we can see, the age of the last frozen transaction in the table exceeded the threshold:

我们可以看到，表中最后冻结的事务的寿命超过了阈值：

=> SELECT relfrozenxid, age(relfrozenxid) FROM pg_class WHERE relname = 'tfreeze';

relfrozenxid |  age   
--------------+--------
          698 | 100006
(1 row)

But now if we wait for a while, a record will appear in the message log on automatic aggressive vacuum of table "test.public.tfreeze", the number of the frozen transaction will change and its age will no longer be beyond the bounds of decency:

但是现在，如果我们等待一会儿，一条记录将出现在automatic aggressive vacuum of table "test.public.tfreeze"的消息日志中，被冻结的事务数将发生变化，并且其年龄将不再超出体面：

=> SELECT relfrozenxid, age(relfrozenxid) FROM pg_class WHERE relname = 'tfreeze';

relfrozenxid | age 
--------------+-----
       100703 |   3
(1 row)

手动冻结 (Freezing manually)

Sometimes it appears convenient to control freezing manually rather than rely on autovacuuming.

有时，手动控制冻结而不是依靠自动抽真空似乎很方便。

You can manually launch freezing by means the VACUUM FREEZE command. It will freeze all the tuples regardless of the age of transactions (as if the autovacuum_freeze_min_age parameter were equal to zero). When a table is rewritten using the VACUUM FULL or CLUSTER command, all the rows also get frozen.

您可以通过VACUUM FREEZE命令手动启动冻结。它将冻结所有元组，而与事务的年龄无关(就像autovacuum_freeze_min_age参数等于零)。使用VACUUM FULL或CLUSTER命令重写表时，所有行也将被冻结。

To freeze all the databases, you can use the utility:

要冻结所有数据库，可以使用该实用程序：

vacuumdb --all --freeze

The data can also be frozen when it is initially loaded by the COPY command if the FREEZE parameter is specified. To this end, the table must be created (or emptied with the TRUNCATE command) in the same transaction as COPY.

如果指定了FREEZE参数，则在最初由COPY命令加载数据时也可以将其冻结。为此，必须在与COPY相同的事务中创建表(或用TRUNCATE命令清空)。

Since there is an exception for frozen rows in visibility rules, such rows will be visible in the snapshots of other transactions, which violates normal isolation rules (this relates to transactions with the Repeatable Read or Serializable level).

由于可见性规则中冻结的行存在例外，因此这些行将在其他事务的快照中可见，这违反了正常的隔离规则(这与具有“可重复读取”或“可序列化”级别的事务有关)。

To make sure of this, in another session, let's start a transaction with the Repeatable Read isolation level:

为确保这一点，在另一个会话中，让我们以“可重复读取”隔离级别启动事务：

|  => BEGIN ISOLATION LEVEL REPEATABLE READ;
|  => SELECT txid_current();

Note that this transaction created a data snapshot, but did not access the «tfreeze» table. We will now truncate the «tfreeze» table and load new rows there in one transaction. If a parallel transaction read the contents of «tfreeze», the TRUNCATE command would be locked to the end of this transaction.

注意，该事务创建了数据快照，但没有访问“ tfreeze”表。现在，我们将截断«tfreeze»表，并在一个事务中将新行加载到该表中。如果并行事务读取《 tfreeze》的内容，则TRUNCATE命令将被锁定到该事务的末尾。

=> BEGIN;
=> TRUNCATE tfreeze;
=> COPY tfreeze FROM stdin WITH FREEZE;

1	FOO
2	BAR
3	BAZ
\.

=> COMMIT;

Now the concurrent transaction sees the new data, although this violates isolation:

现在，并发事务看到了新数据，尽管这违反了隔离性：

|  => SELECT count(*) FROM tfreeze;

|   count 
|  -------
|       3
|  (1 row)

|  => COMMIT;

But since such data loading is unlikely to regularly happen, this is hardly an issue.

但是，由于这种数据加载不太可能定期发生，因此这几乎不是问题。

What is much worse is that COPY WITH FREEZE does not work with the visibility map — loaded pages are not tracked as containing only tuples visible to all. Therefore, when a vacuum operation accesses the table first, it has to process all the table again and create the visibility map. What is even worse is that data pages have the all-visible indicator in their own headers, and therefore, vacuuming not only reads the entire table, but also entirely rewrites it to set the needed bit. Unfortunately, the solution to this problem can be expected not earlier than in version 13 (discussion).

更糟糕的是，COPY WITH FREEZE无法与可见性图一起使用-加载的页面没有被跟踪为仅包含所有人可见的元组。因此，当真空操作首先访问该表时，它必须再次处理所有表并创建可见性图。更糟糕的是，数据页在其自己的标头中具有全可见的指示器，因此，清理不仅读取整个表，还完全重写它以设置所需的位。不幸的是，不早于版本13( 讨论 )就有望解决此问题。

结论 (Conclusion)

This completes the series of articles on isolation and multiversion concurrency control in PostgreSQL. Thank you for your attention and especially for comments. They help improve the contents and often detect the areas that require more intense attention on my part.

这就完成了有关PostgreSQL中隔离和多版本并发控制的系列文章。感谢您的关注，尤其是评论。它们有助于改善内容，并经常检测我需要更多关注的区域。

Stay with us, there is more to come!

和我们在一起，还有更多机会！