PostgreSQL的学习心得和知识总结（一百二十三）|深入理解PostgreSQL数据库开源扩展pg_dirtyread的使用场景和实现原理

目录结构

---

注：提前言明 本文借鉴了以下博主、书籍或网站的内容，其列表如下：

1、参考书籍：《PostgreSQL数据库内核分析》
2、参考书籍：《数据库事务处理的艺术：事务管理与并发控制》
3、PostgreSQL数据库仓库链接，点击前往
4、日本著名PostgreSQL数据库专家 铃木启修 网站主页，点击前往
5、参考书籍：《PostgreSQL中文手册》
6、参考书籍：《PostgreSQL指南：内幕探索》，点击前往
7、参考书籍：《事务处理 概念与技术》
8、本人 pg_dirtyread git仓库，点击前往

---

1、本文内容全部来源于开源社区 GitHub和以上博主的贡献，本文也免费开源（可能会存在问题，评论区等待大佬们的指正）
2、本文目的：开源共享 抛砖引玉 一起学习
3、本文不提供任何资源 不存在任何交易 与任何组织和机构无关
4、大家可以根据需要自行 复制粘贴以及作为其他个人用途，但是不允许转载 不允许商用 （写作不易，还请见谅 💖）
5、本文内容基于PostgreSQL15.1源码开发而成

---

---

---

文章快速说明索引

学习目标：

做数据库内核开发久了就会有一种 少年得志，年少轻狂 的错觉，然鹅细细一品觉得自己其实不算特别优秀 远远没有达到自己想要的。也许光鲜的表面掩盖了空洞的内在，每每想到于此，皆有夜半临渊如履薄冰之感。为了睡上几个踏实觉，即日起 暂缓其他基于PostgreSQL数据库的兼容功能开发，近段时间 将着重于学习分享Postgres的基础知识和实践内幕。

---

学习内容：（详见目录）

1、PostgreSQL数据库开源扩展pg_dirtyread的使用场景和实现原理

---

学习时间：

2023-02-16 10:18:54

---

学习产出：

1、PostgreSQL数据库基础知识回顾 1个
2、CSDN 技术博客 1篇
3、PostgreSQL数据库内核深入学习

---

注：下面我们所有的学习环境是Centos7+PostgreSQL15.1(pg_backtrace1.0)+Oracle19C+MySQL8.0

postgres=# select version();version                                   
-----------------------------------------------------------------------------PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.1.0, 64-bit
(1 row)postgres=# \dxList of installed extensionsName     | Version |   Schema   |         Description          
--------------+---------+------------+------------------------------pg_backtrace | 1.0     | public     | Dump backtrace i errorsplpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)postgres=##-----------------------------------------------------------------------------#SQL> select * from v$version;          BANNER        Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production	
BANNER_FULL	  Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.17.0.0.0	
BANNER_LEGACY Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production	
CON_ID 0#-----------------------------------------------------------------------------#mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.06 sec)mysql>

---

问题描述背景说明

pg_dirtyread这个插件之前我们已经多次提过，一直没有详细介绍过其使用场景和实现原理。接下来我们快速过一下这个短小精悍功能强大的插件：

[postgres@song197:~/postgres/contrib → REL_15_1]$ git clone [e-mail]:TsinghuaLucky912/pg_dirtyread.git
Cloning into 'pg_dirtyread'...
remote: Enumerating objects: 422, done.
remote: Counting objects: 100% (27/27), done.
remote: Compressing objects: 100% (23/23), done.
remote: Total 422 (delta 10), reused 15 (delta 4), pack-reused 395
Receiving objects: 100% (422/422), 88.79 KiB | 0 bytes/s, done.
Resolving deltas: 100% (238/238), done.
[postgres@song197:~/postgres/contrib → REL_15_1]$ 

[postgres@song197:~/postgres/contrib → REL_15_1]$ cd pg_dirtyread/
[postgres@song197:~/postgres/contrib/pg_dirtyread → master]$ ls
contrib  dirtyread_tupconvert.c  expected  Makefile                  pg_dirtyread--1.0.sql  pg_dirtyread.c        README.md  tupconvert.c.upstream
debian   dirtyread_tupconvert.h  LICENSE   pg_dirtyread--1.0--2.sql  pg_dirtyread--2.sql    pg_dirtyread.control  sql        tupconvert.h.upstream
[postgres@song197:~/postgres/contrib/pg_dirtyread → master]$ 
[postgres@song197:~/postgres/contrib/pg_dirtyread → master]$ 
[postgres@song197:~/postgres/contrib/pg_dirtyread → master]$ 
[postgres@song197:~/postgres/contrib/pg_dirtyread → master]$ make 
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -g -O0 -fPIC -I. -I./ -I/home/postgres/test/include/server -I/home/postgres/test/include/internal  -D_GNU_SOURCE   -c -o pg_dirtyread.o pg_dirtyread.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -g -O0 -fPIC -I. -I./ -I/home/postgres/test/include/server -I/home/postgres/test/include/internal  -D_GNU_SOURCE   -c -o dirtyread_tupconvert.o dirtyread_tupconvert.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -g -O0 -fPIC -shared -o pg_dirtyread.so pg_dirtyread.o dirtyread_tupconvert.o -L/home/postgres/test/lib    -Wl,--as-needed -Wl,-rpath,'/home/postgres/test/lib',--enable-new-dtags  
[postgres@song197:~/postgres/contrib/pg_dirtyread → master]$ ls
contrib  dirtyread_tupconvert.c  dirtyread_tupconvert.o  LICENSE   pg_dirtyread--1.0--2.sql  pg_dirtyread--2.sql  pg_dirtyread.control  pg_dirtyread.so  sql                    tupconvert.h.upstream
debian   dirtyread_tupconvert.h  expected                Makefile  pg_dirtyread--1.0.sql     pg_dirtyread.c       pg_dirtyread.o        README.md        tupconvert.c.upstream
[postgres@song197:~/postgres/contrib/pg_dirtyread → master]$ 
[postgres@song197:~/postgres/contrib/pg_dirtyread → master]$ 
[postgres@song197:~/postgres/contrib/pg_dirtyread → master]$ make install -j8
/usr/bin/mkdir -p '/home/postgres/test/lib'
/usr/bin/mkdir -p '/home/postgres/test/share/extension'
/usr/bin/install -c -m 755  pg_dirtyread.so '/home/postgres/test/lib/pg_dirtyread.so'
/usr/bin/mkdir -p '/home/postgres/test/share/extension'
/usr/bin/install -c -m 644 .//pg_dirtyread.control '/home/postgres/test/share/extension/'
/usr/bin/install -c -m 644 .//pg_dirtyread--1.0.sql .//pg_dirtyread--1.0--2.sql .//pg_dirtyread--2.sql  '/home/postgres/test/share/extension/'
[postgres@song197:~/postgres/contrib/pg_dirtyread → master]$ 

---

pg_dirtyread 扩展提供了从表中读取死的但未清理的行的能力。支持 PostgreSQL 9.2 及更高版本。（在 9.2 上，至少需要 9.2.9。）一旦构建并安装了 pg_dirtyread，您就可以将它添加到数据库中。加载 pg_dirtyread 就像以超级用户身份连接到数据库并运行一样简单：

  CREATE EXTENSION pg_dirtyread;SELECT * FROM pg_dirtyread('tablename') AS t(col1 type1, col2 type2, ...);

需要注意：pg_dirtyread() 函数返回 RECORD，因此有必要附加一个描述表模式的表别名子句。列按名称匹配，因此可以在别名中省略某些列，或重新排列列。如下：

postgres=# \dxList of installed extensionsName   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)postgres=# CREATE EXTENSION pg_dirtyread;
CREATE EXTENSION
postgres=# \dxList of installed extensionsName     | Version |   Schema   |               Description                
--------------+---------+------------+------------------------------------------pg_dirtyread | 2       | public     | Read dead but unvacuumed rows from tableplpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)postgres=# CREATE TABLE foo (bar bigint, baz text);
CREATE TABLE
postgres=# ALTER TABLE foo SET (
postgres(#     autovacuum_enabled = false, toast.autovacuum_enabled = false
postgres(#   );
ALTER TABLE
postgres=# select * from pg_type where typname like '%foo%';oid  | typname | typnamespace | typowner | typlen | typbyval | typtype | typcategory | typispreferred | typisdefined | typdelim | typrelid |      typsubscript       | typelem | typarray | typinput  | typoutput  | typreceive  |   typsend   | typmodin | typmodout |    typanalyze    | typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typcollation | typdefaultbin | typdefault | typacl 
-------+---------+--------------+----------+--------+----------+---------+-------------+----------------+--------------+----------+----------+-------------------------+---------+----------+-----------+------------+-------------+-------------+----------+-----------+------------------+----------+------------+------------+-------------+-----------+----------+--------------+---------------+------------+--------16388 | foo     |         2200 |       10 |     -1 | f        | c       | C           | f              | t            | ,        |    16386 | -                       |       0 |    16387 | record_in | record_out | record_recv | record_send | -        | -         | -                | d        | x          | f          |           0 |        -1 |        0 |            0 |               |            | 16387 | _foo    |         2200 |       10 |     -1 | f        | b       | A           | f              | t            | ,        |        0 | array_subscript_handler |   16388 |        0 | array_in  | array_out  | array_recv  | array_send  | -        | -         | array_typanalyze | d        | x          | f          |           0 |        -1 |        0 |            0 |               |            | 
(2 rows)postgres=#
postgres=# INSERT INTO foo VALUES (1, 'Test'), (2, 'New Test');
INSERT 0 2
postgres=#

postgres=# create extension pageinspect ;
CREATE EXTENSION
postgres=# \dxList of installed extensionsName     | Version |   Schema   |                      Description                      
--------------+---------+------------+-------------------------------------------------------pageinspect  | 1.10    | public     | inspect the contents of database pages at a low levelpg_dirtyread | 2       | public     | Read dead but unvacuumed rows from tableplpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)postgres=# select * from heap_page_items(get_raw_page('foo',0)) ;lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |                t_data                
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------------------------1 |   8152 |        1 |     37 |    736 |      0 |        0 | (0,1)  |           2 |       2050 |     24 |        |       | \x01000000000000000b546573742 |   8104 |        1 |     41 |    736 |      0 |        0 | (0,2)  |           2 |       2050 |     24 |        |       | \x0200000000000000134e65772054657374
(2 rows)postgres=# DELETE FROM foo WHERE bar = 1;
DELETE 1
postgres=# select * from heap_page_items(get_raw_page('foo',0)) ;lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |                t_data                
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------------------------1 |   8152 |        1 |     37 |    736 |    738 |        0 | (0,1)  |        8194 |        258 |     24 |        |       | \x01000000000000000b546573742 |   8104 |        1 |     41 |    736 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |       | \x0200000000000000134e65772054657374
(2 rows)postgres=# select * from foo;bar |   baz    
-----+----------2 | New Test
(1 row)postgres=# select * from heap_page_items(get_raw_page('foo',0)) ;lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |                t_data                
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------------------------1 |   8152 |        1 |     37 |    736 |    738 |        0 | (0,1)  |        8194 |       1282 |     24 |        |       | \x01000000000000000b546573742 |   8104 |        1 |     41 |    736 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |       | \x0200000000000000134e65772054657374
(2 rows)postgres=#

如上，foo表并没有vacuum，所以使用pageinspect也是可以看到 死元组 的存在，如下使用pg_dirtyread再看一下(并看一下清理之后的)：

postgres=# select * from foo;bar |   baz    
-----+----------2 | New Test
(1 row)postgres=# SELECT * FROM pg_dirtyread('foo') as t(bar bigint, baz text);bar |   baz    
-----+----------1 | Test2 | New Test
(2 rows)postgres=# vacuum foo;
VACUUM
postgres=# SELECT * FROM pg_dirtyread('foo') as t(bar bigint, baz text);bar |   baz    
-----+----------2 | New Test
(1 row)postgres=# select * from heap_page_items(get_raw_page('foo',0)) ;lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |                t_data                
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------------------------1 |      0 |        0 |      0 |        |        |          |        |             |            |        |        |       | 2 |   8144 |        1 |     41 |    736 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |       | \x0200000000000000134e65772054657374
(2 rows)postgres=#

---

在pg_dirtyread的新版本中，又新增了一些功能(Dropped Columns和System Columns) 如下：

---

Dropped Columns：只要表没有被重写（例如通过 VACUUM FULL 或 CLUSTER），就可以检索删除列的内容。使用 dropped_N 访问第 N 列，从 1 开始计数(下面有演示)。PostgreSQL 删除了原始列的类型信息，因此如果在表别名中指定了正确的类型，则只能进行少量完整性检查；检查的是类型长度、类型对齐、类型修饰符和按值传递。

postgres=# \d
Did not find any relations.
postgres=# CREATE TABLE ab(a text, b text);
CREATE TABLE
postgres=# INSERT INTO ab VALUES ('Hello', 'World');
INSERT 0 1
postgres=# ALTER TABLE ab DROP COLUMN b;
ALTER TABLE
postgres=# DELETE FROM ab;
DELETE 1
postgres=# select * from ab;a 
---
(0 rows)postgres=# SELECT * FROM pg_dirtyread('ab') ab(a text, dropped_2 text);a   | dropped_2 
-------+-----------Hello | World
(1 row)postgres=# SELECT * FROM pg_dirtyread('ab') ab(a text, b text);
2023-02-16 11:24:17.799 CST [4660] ERROR:  Error converting tuple descriptors!
2023-02-16 11:24:17.799 CST [4660] DETAIL:  Attribute "b" does not exist in type ab.
2023-02-16 11:24:17.799 CST [4660] STATEMENT:  SELECT * FROM pg_dirtyread('ab') ab(a text, b text);
ERROR:  Error converting tuple descriptors!
DETAIL:  Attribute "b" does not exist in type ab.
postgres=#

---

System Columns：可以通过将它们包含在附加到 pg_dirtyread() 调用的表别名中来检索系统列，例如 xmax 和 ctid。一个布尔类型的特殊列 dead 可用于报告死行（如 HeapTupleIsSurelyDead）。死列在恢复期间不可用，即最明显的是在备用服务器上不可用。oid 列仅在 PostgreSQL 版本 11 及更早版本中可用。

postgres=# CREATE TABLE foo2 (bar bigint, baz text);
CREATE TABLE
postgres=# ALTER TABLE foo2 SET (
postgres(#     autovacuum_enabled = false, toast.autovacuum_enabled = false
postgres(#   );
ALTER TABLE
postgres=# INSERT INTO foo2 VALUES (1, 'Test'), (2, 'New Test');
INSERT 0 2
postgres=# select * from foo2 ;bar |   baz    
-----+----------1 | Test2 | New Test
(2 rows)postgres=# DELETE FROM foo2 WHERE bar = 1;
DELETE 1
postgres=# SELECT * FROM pg_dirtyread('foo2') AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,
postgres(#            bar bigint, baz text);tableoid | ctid  | xmin | xmax | cmin | cmax | dead | bar |   baz    
----------+-------+------+------+------+------+------+-----+----------16442 | (0,1) |  747 |  748 |    0 |    0 | f    |   1 | Test16442 | (0,2) |  747 |    0 |    0 |    0 | f    |   2 | New Test
(2 rows)postgres=#

当然这两个也可以结合在一起进行使用，如下：

postgres=# SELECT * FROM pg_dirtyread('foo2') AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,bar bigint, baz text);tableoid | ctid  | xmin | xmax | cmin | cmax | dead | bar |   baz    
----------+-------+------+------+------+------+------+-----+----------16442 | (0,1) |  747 |  748 |    0 |    0 | f    |   1 | Test16442 | (0,2) |  747 |    0 |    0 |    0 | f    |   2 | New Test
(2 rows)postgres=# ALTER TABLE foo2 DROP COLUMN baz;
ALTER TABLE
postgres=# SELECT * FROM pg_dirtyread('foo2') AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,bar bigint, dropped_2 text);tableoid | ctid  | xmin | xmax | cmin | cmax | dead | bar | dropped_2 
----------+-------+------+------+------+------+------+-----+-----------16442 | (0,1) |  747 |  748 |    0 |    0 | f    |   1 | Test16442 | (0,2) |  747 |    0 |    0 |    0 | f    |   2 | New Test
(2 rows)postgres=# vacuum foo2 ;
VACUUM
postgres=# SELECT * FROM pg_dirtyread('foo2') AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,bar bigint, dropped_2 text);tableoid | ctid  | xmin | xmax | cmin | cmax | dead | bar | dropped_2 
----------+-------+------+------+------+------+------+-----+-----------16442 | (0,2) |  747 |    0 |    0 |    0 | f    |   2 | New Test
(1 row)postgres=#

---

功能实现源码解析

postgres=# \dfList of functionsSchema |     Name     | Result data type | Argument data types | Type 
--------+--------------+------------------+---------------------+------public | pg_dirtyread | SETOF record     | regclass            | func
(1 row)postgres=# \df+ pg_dirtyread List of functionsSchema |     Name     | Result data type | Argument data types | Type | Volatility | Parallel |  Owner   | Security | Access privileges | Language | Source code  | Description 
--------+--------------+------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+--------------+-------------public | pg_dirtyread | SETOF record     | regclass            | func | volatile   | unsafe   | postgres | invoker  |                   | c        | pg_dirtyread | 
(1 row)postgres=#

---

接下来，以下面SQL为例，详细介绍一下这个插件的作用原理：

postgres=# SELECT * FROM foo ;bar |   baz    
-----+----------2 | New Test
(1 row)postgres=# ALTER TABLE foo DROP COLUMN baz;
ALTER TABLE
postgres=# SELECT * FROM pg_dirtyread('foo') AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,bar bigint, dropped_2 text);tableoid | ctid  | xmin | xmax | cmin | cmax | dead | bar | dropped_2 
----------+-------+------+------+------+------+------+-----+-----------16447 | (0,1) |  754 |  755 |    0 |    0 | t    |   1 | Test16447 | (0,2) |  754 |    0 |    0 |    0 | f    |   2 | New Test
(2 rows)postgres=#-- foo 表第一行数据被delete；第二列 baz被drop；此时表并未被清理

此时函数调用堆栈，如下：

pg_dirtyread.so!pg_dirtyread(FunctionCallInfo fcinfo)
ExecMakeTableFunctionResult(SetExprState * setexpr, ExprContext * econtext, MemoryContext argContext, TupleDesc expectedDesc, _Bool randomAccess)
FunctionNext(FunctionScanState * node)
ExecScanFetch(ScanState * node, ExecScanAccessMtd accessMtd, ExecScanRecheckMtd recheckMtd)
ExecScan(ScanState * node, ExecScanAccessMtd accessMtd, ExecScanRecheckMtd recheckMtd)
ExecFunctionScan(PlanState * pstate)
ExecProcNodeFirst(PlanState * node)
ExecProcNode(PlanState * node)
ExecutePlan(EState * estate, PlanState * planstate, _Bool use_parallel_mode, CmdType operation, _Bool sendTuples, uint64 numberTuples, ScanDirection direction, DestReceiver * dest, _Bool execute_once)
standard_ExecutorRun(QueryDesc * queryDesc, ScanDirection direction, uint64 count, _Bool execute_once)
ExecutorRun(QueryDesc * queryDesc, ScanDirection direction, uint64 count, _Bool execute_once)
PortalRunSelect(Portal portal, _Bool forward, long count, DestReceiver * dest)
PortalRun(Portal portal, long count, _Bool isTopLevel, _Bool run_once, DestReceiver * dest, DestReceiver * altdest, QueryCompletion * qc)
exec_simple_query(const char * query_string)
PostgresMain(const char * dbname, const char * username)
BackendRun(Port * port)
BackendStartup(Port * port)
ServerLoop()
PostmasterMain(int argc, char ** argv)
main(int argc, char ** argv)

---

如上的SQL 返回值是record oid => '2249'，此刻的tupdesc 如下：

---

接下来看一下今天的第一个重点，dirtyread_convert_tuples_by_name函数：

// contrib/pg_dirtyread/dirtyread_tupconvert.c/** The conversion setup routines have the following common API:* 转换设置例程具有以下通用 API：** The setup routine checks whether the given source and destination tuple* descriptors are logically compatible.  If not, it throws an error.* If so, it returns NULL if they are physically compatible (ie, no conversion* is needed), else a TupleConversionMap that can be used by do_convert_tuple* to perform the conversion.* 设置例程检查给定的源和目标元组描述符在逻辑上是否兼容* 如果不是，它会抛出一个错误* 如果是这样，如果它们在物理上兼容（即不需要转换），则返回 NULL，否则返回 do_convert_tuple 可以用来执行转换的 TupleConversionMap** The TupleConversionMap, if needed, is palloc'd in the caller's memory* context.  Also, the given tuple descriptors are referenced by the map,* so they must survive as long as the map is needed.* 如果需要，TupleConversionMap 会在调用者的内存上下文中被分配* 此外，给定的元组描述符由map引用，因此只要需要map，它们就必须存在** The caller must supply a suitable primary error message to be used if* a compatibility error is thrown.  Recommended coding practice is to use* gettext_noop() on this string, so that it is translatable but won't* actually be translated unless the error gets thrown.* 如果抛出兼容性错误，调用者必须提供要使用的合适的主要错误消息* 推荐的编码做法是在此字符串上使用 gettext_noop() ，这样它是可翻译的，但除非抛出错误，否则实际上不会被翻译*** Implementation notes:* 实施说明：** The key component of a TupleConversionMap is an attrMap[] array with* one entry per output column.  This entry contains the 1-based index of* the corresponding input column, or zero to force a NULL value (for* a dropped output column).  The TupleConversionMap also contains workspace* arrays.* TupleConversionMap 的关键组件是一个 attrMap[] 数组，每个输出列有一个条目* 此条目包含相应输入列的从 1 开始的索引，或零以强制 NULL 值（对于删除的输出列）* TupleConversionMap 还包含工作区数组*//** Set up for tuple conversion, matching input and output columns by name.* (Dropped columns are ignored in both input and output.)	This is intended* for use when the rowtypes are related by inheritance, so we expect an exact* match of both type and typmod.  The error messages will be a bit unhelpful* unless both rowtypes are named composite types.* 设置元组转换，按名称匹配输入和输出列* （丢弃的列在输入和输出中都被忽略。）* 这是为了在行类型通过继承相关时使用，所以我们期望类型和 typmod 完全匹配* 除非两个行类型都命名为复合类型，否则错误消息将有点无用*/
TupleConversionMap *
dirtyread_convert_tuples_by_name(TupleDesc indesc,TupleDesc outdesc,const char *msg)
{.../* Verify compatibility and prepare attribute-number map *//* 验证兼容性并准备属性编号映射 */attrMap = dirtyread_convert_tuples_by_name_map(indesc, outdesc, msg);...
}

此时说明一下：

/*
indesc: 该表的列 在这里 为2列(即使第二列被删掉了，注意 attisdropped 属性)outdesc: 我们这里要输入的 record 对应 (这里自然就是 7个系统列 + 2个用户的列)msg: "Error converting tuple descriptors!"
*/

接下来先看一下dirtyread_convert_tuples_by_name_map函数：

// contrib/pg_dirtyread/dirtyread_tupconvert.cstatic const struct system_columns_t {char	   *attname;Oid			atttypid;int32		atttypmod;int			attnum;
} system_columns[] = {{ "ctid",     TIDOID,  -1, SelfItemPointerAttributeNumber },
#if PG_VERSION_NUM < 120000{ "oid",      OIDOID,  -1, ObjectIdAttributeNumber },
#endif{ "xmin",     XIDOID,  -1, MinTransactionIdAttributeNumber },{ "cmin",     CIDOID,  -1, MinCommandIdAttributeNumber },{ "xmax",     XIDOID,  -1, MaxTransactionIdAttributeNumber },{ "cmax",     CIDOID,  -1, MaxCommandIdAttributeNumber },{ "tableoid", OIDOID,  -1, TableOidAttributeNumber },{ "dead",     BOOLOID, -1, DeadFakeAttributeNumber }, /* fake column to return HeapTupleIsSurelyDead */{ 0 },
};/** Return a palloc'd bare attribute map for tuple conversion, matching input* and output columns by name.  (Dropped columns are ignored in both input and* output.)  This is normally a subroutine for convert_tuples_by_name, but can* be used standalone.* 返回用于元组转换的 palloc 裸属性映射，按名称匹配输入和输出列* （丢弃的列在输入和输出中都被忽略。）* 这通常是 convert_tuples_by_name 的子例程，但可以单独使用** This version from dirtyread_tupconvert.c adds the ability to retrieve dropped* columns by requesting "dropped_N" as output column, where N is the attnum.* 这个来自 dirtyread_tupconvert.c 的版本增加了通过请求“dropped_N”作为输出列来检索删除的列的能力，其中 N 是 attnum*/
AttrNumber *
dirtyread_convert_tuples_by_name_map(TupleDesc indesc,TupleDesc outdesc,const char *msg)
{...// 这个遍历 outdescfor (i = 0; i < n; i++){...// 这个遍历 indescfor (j = 0; j < indesc->natts; j++){...}.../* Check dropped columns */if (attrMap[i] == 0)if (strncmp(attname, "dropped_", sizeof("dropped_") - 1) == 0){...}.../* Check system columns */if (attrMap[i] == 0)for (j = 0; system_columns[j].attname; j++)if (strcmp(attname, system_columns[j].attname) == 0){...}...}...
}

这里我们把indesc和outdesc的关键属性(重要的)列举，如下：

attrelid	attname	atttypid	attlen	attnum	attisdropped
16447	bar	20	8	1	f
同上	空	0	-1	2	t

---

attrelid	attname	atttypid	attlen	attnum	attisdropped
0	tableoid	26	4	1	f
0	ctid	27	6	2	f
0	xmin	28	4	3	f
0	xmax	28	4	4	f
0	cmin	29	4	5	f
0	cmax	29	4	6	f
0	dead	16	1	7	f
0	bar	20	8	8	f
0	dropped_2	25	-1	9	f

---

解释一下上面这9个输出列，其匹配如下：

// contrib/pg_dirtyread/dirtyread_tupconvert.h
// FirstLowInvalidHeapAttributeNumber -7
#define DeadFakeAttributeNumber FirstLowInvalidHeapAttributeNumber/*检查系统列 attrMap[0] = system_columns[5].attnum;  -6 就是 TableOidAttributeNumber
检查系统列 attrMap[1] = system_columns[0].attnum;  -1 就是 SelfItemPointerAttributeNumber
检查系统列 attrMap[2] = system_columns[1].attnum;  -2 就是 MinTransactionIdAttributeNumber
检查系统列 attrMap[3] = system_columns[3].attnum;  -4 就是 MaxTransactionIdAttributeNumber
检查系统列 attrMap[4] = system_columns[2].attnum;  -3 就是 MinCommandIdAttributeNumber
检查系统列 attrMap[5] = system_columns[4].attnum;  -5 就是 MaxCommandIdAttributeNumber
检查系统列 attrMap[6] = system_columns[6].attnum;  -7 就是 DeadFakeAttributeNumber检查表的列 attrMap[7] = (AttrNumber) (0 + 1); 1 就是 bar 列检查删除列 attrMap[8] = (AttrNumber) 2; 2就是 原 baz 列
*/

经过函数dirtyread_convert_tuples_by_name_map之后的attrMap就是上面的内容！之后函数dirtyread_convert_tuples_by_name也就结束了！

---

继续 如下：

如上图所示：接下来就是遍历这个表 heap_beginscan，以ForwardScanDirection的方式获取全部元组，如下：

// contrib/pg_dirtyread/pg_dirtyread.c...if ((tuplein = heap_getnext(usr_ctx->scan, ForwardScanDirection)) != NULL){if (usr_ctx->map != NULL){tuplein = dirtyread_do_convert_tuple(tuplein, usr_ctx->map, usr_ctx->oldest_xmin);SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuplein));}elseSRF_RETURN_NEXT(funcctx, heap_copy_tuple_as_datum(tuplein, usr_ctx->reltupdesc));}else{heap_endscan(usr_ctx->scan);
#if PG_VERSION_NUM >= 120000table_close(usr_ctx->rel, AccessShareLock);
#elseheap_close(usr_ctx->rel, AccessShareLock);
#endifSRF_RETURN_DONE(funcctx);}
}

---

接下来 我们再介绍一下 第二个重点 dirtyread_do_convert_tuple函数，如下：

// contrib/pg_dirtyread/dirtyread_tupconvert.c/** Perform conversion of a tuple according to the map.* 根据映射执行元组的转换*/
HeapTuple
dirtyread_do_convert_tuple(HeapTuple tuple, TupleConversionMap *map, OldestXminType oldest_xmin)
{AttrNumber *attrMap =
#if PG_VERSION_NUM >= 130000map->attrMap->attnums;
#elsemap->attrMap;
#endifDatum	   *invalues = map->invalues;bool	   *inisnull = map->inisnull;Datum	   *outvalues = map->outvalues;bool	   *outisnull = map->outisnull;int			outnatts = map->outdesc->natts;int			i;/** Extract all the values of the old tuple, offsetting the arrays so that* invalues[0] is left NULL and invalues[1] is the first source attribute;* this exactly matches the numbering convention in attrMap.* * 提取旧元组的所有值，偏移数组，使 invalues[0] 为 NULL，invalues[1] 为第一个源属性* 这完全符合 attrMap 中的编号约定*/heap_deform_tuple(tuple, map->indesc, invalues + 1, inisnull + 1);/** Transpose into proper fields of the new tuple.* 转置到新元组的适当字段中*/for (i = 0; i < outnatts; i++){int			j = attrMap[i];if (j == DeadFakeAttributeNumber){outvalues[i] = HeapTupleIsSurelyDead(tuple
#if PG_VERSION_NUM < 90400->t_data
#endif, oldest_xmin);outisnull[i] = false;}else if (j < 0)outvalues[i] = heap_getsysattr(tuple, j, map->indesc, &outisnull[i]);else{outvalues[i] = invalues[j];outisnull[i] = inisnull[j];}}/** Now form the new tuple.*/return heap_form_tuple(map->outdesc, outvalues, outisnull);
}

其他的不再详解，我们这里主要看一下 最下面的那个 for 循环，如下：

1. 如果是 dead 则其值为 HeapTupleIsSurelyDead 函数的返回值
2. 为系统列 其值获取使用 heap_getsysattr
3. 表的普通列 使用invalues[j]方式

最后通过函数heap_form_tuple来构造这个(要返回的)元组：

	/** Now form the new tuple.*/return heap_form_tuple(map->outdesc, outvalues, outisnull);

---

如上是函数第一次调用(第一行的构造逻辑)，轮到第二次 此时的函数堆栈调用如下(同上)：

第二行元组 构造 省略！表遍历结束，再无元组 pg_dirtyread函数执行完成！

postgres=# SELECT * FROM pg_dirtyread('foo') AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,bar bigint, dropped_2 text);tableoid | ctid  | xmin | xmax | cmin | cmax | dead | bar | dropped_2 
----------+-------+------+------+------+------+------+-----+-----------16447 | (0,1) |  754 |  755 |    0 |    0 | t    |   1 | Test16447 | (0,2) |  754 |    0 |    0 |    0 | f    |   2 | New Test
(2 rows)postgres=#

---

功能实现细节说明

接下来，我们详细看一下几个细节，如下：

细节一：判断dead返回值的函数HeapTupleIsSurelyDead，如下：

// src/backend/access/heap/heapam_visibility.c/** HeapTupleIsSurelyDead**	Cheaply determine whether a tuple is surely dead to all onlookers.*	We sometimes use this in lieu of HeapTupleSatisfiesVacuum when the*	tuple has just been tested by another visibility routine (usually*	HeapTupleSatisfiesMVCC) and, therefore, any hint bits that can be set*	should already be set.  We assume that if no hint bits are set, the xmin*	or xmax transaction is still running.  This is therefore faster than*	HeapTupleSatisfiesVacuum, because we consult neither procarray nor CLOG.*	It's okay to return false when in doubt, but we must return true only*	if the tuple is removable.*	 *	廉价地确定一个元组是否对所有旁观者来说肯定是死的*	当元组刚刚被另一个可见性例程（通常是 HeapTupleSatisfiesMVCC）测试时，*	我们有时会使用它代替 HeapTupleSatisfiesVacuum，因此，应该已经设置了可以设置的任何提示位*	*	我们假设如果没有设置提示位，则 xmin 或 xmax 事务仍在运行*	因此，这比 HeapTupleSatisfiesVacuum 更快，因为我们既不查询 procarray 也不查询 CLOG*	有疑问时返回 false 是可以的，但只有当元组可移动时我们才必须返回 true*/
bool
HeapTupleIsSurelyDead(HeapTuple htup, GlobalVisState *vistest);

两个参数：

/*
htup： 当前判断的 元组vistest：如下
*/

其值的获取来源，如下：

// src/backend/storage/ipc/procarray.c/** If rel != NULL, return test state appropriate for relation, otherwise* return state usable for all relations.  The latter may consider XIDs as* not-yet-visible-to-everyone that a state for a specific relation would* already consider visible-to-everyone.* 如果 rel != NULL，则返回适用于关系的测试状态，否则返回可用于所有关系的状态* 后者可能将 XID 视为尚未对所有人可见，而特定关系的状态已将其视为对所有人可见** This needs to be called while a snapshot is active or registered, otherwise* there are wraparound and other dangers.* 这需要在快照处于活动状态或已注册时调用，否则会有环绕和其他危险** See comment for GlobalVisState for details.*/
GlobalVisState *
GlobalVisTestFor(Relation rel)
{GlobalVisState *state = NULL;/* XXX: we should assert that a snapshot is pushed or registered */Assert(RecentXmin);switch (GlobalVisHorizonKindForRel(rel)){case VISHORIZON_SHARED:state = &GlobalVisSharedRels;break;case VISHORIZON_CATALOG:state = &GlobalVisCatalogRels;break;case VISHORIZON_DATA:state = &GlobalVisDataRels;break;case VISHORIZON_TEMP:state = &GlobalVisTempRels;break;}Assert(FullTransactionIdIsValid(state->definitely_needed) &&FullTransactionIdIsValid(state->maybe_needed));return state;
}

---

我们继续看一下：

postgres=# SELECT * FROM pg_dirtyread('foo') AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,bar bigint, dropped_2 text);tableoid | ctid  | xmin | xmax | cmin | cmax | dead | bar | dropped_2 
----------+-------+------+------+------+------+------+-----+-----------16447 | (0,1) |  754 |  755 |    0 |    0 | t    |   1 | Test16447 | (0,2) |  754 |    0 |    0 |    0 | f    |   2 | New Test
(2 rows)postgres=# select txid_status(754);txid_status 
-------------committed
(1 row)postgres=# select txid_status(755);txid_status 
-------------committed
(1 row)postgres=# SELECT txid_current ();txid_current 
--------------757
(1 row)postgres=#

事务754插入这两行数据，事务755删除第一行(这两个事务都已提交)。关于元组可见性的内容 不在详解，有兴趣的小伙伴可以看一下本人之前的博客：

• PostgreSQL的学习心得和知识总结（四十九）|深入理解PostgreSQL数据库行可见性判断机制基础，点击前往