pg_dirtyread 闪回查询

梦里梦外; 2022-10-13 15:51 135阅读 0赞

os: centos 7.6  
db: postgresql 12

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.6.1810 (Core) 
    # 
    # yum list installed |grep -i postgre
    postgresql12.x86_64                12.7-1PGDG.rhel7                    @pgdg12  
    postgresql12-contrib.x86_64        12.7-1PGDG.rhel7                    @pgdg12  
    postgresql12-devel.x86_64          12.7-1PGDG.rhel7                    @pgdg12  
    postgresql12-docs.x86_64           12.7-1PGDG.rhel7                    @pgdg12  
    postgresql12-libs.x86_64           12.7-1PGDG.rhel7                    @pgdg12  
    postgresql12-llvmjit.x86_64        12.7-1PGDG.rhel7                    @pgdg12  
    postgresql12-odbc.x86_64           13.00.0000-1PGDG.rhel7              @pgdg12  
    postgresql12-odbc-debuginfo.x86_64 12.02.0000-1PGDG.rhel7              @pgdg-common
    postgresql12-plperl.x86_64         12.7-1PGDG.rhel7                    @pgdg12  
    postgresql12-plpython.x86_64       12.7-1PGDG.rhel7                    @pgdg12  
    postgresql12-plpython3.x86_64      12.7-1PGDG.rhel7                    @pgdg12  
    postgresql12-pltcl.x86_64          12.7-1PGDG.rhel7                    @pgdg12  
    postgresql12-server.x86_64         12.7-1PGDG.rhel7                    @pgdg12  
    postgresql12-tcl.x86_64            2.7.5-1.rhel7                       @pgdg12  
    postgresql12-test.x86_64           12.7-1PGDG.rhel7                    @pgdg12

# 下载安装 #

# export PGHOME=/usr/pgsql-12
    export PGDATA=/var/lib/pgsql/12/data
    
    export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${PGHOME}/lib
    export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
    export PATH=${PGHOME}/bin:$PATH
    export MANPATH=${PGHOME}/share/man:$MANPATH
    
    export TMP=/tmp
    export TEMP=/tmp
    export TMPDIR=/tmp
    
    # cd /opt
    # wget https://github.com/df7cb/pg_dirtyread/archive/refs/tags/2.2.tar.gz
    # tar -zxvf ./2.2.tar.gz
    # cd pg_dirtyread-2.2
    # make
    # make install

# 创建 extension #

pgbenchdb=# select * from pg_available_extensions where name like '%dirty%';
         name     | default_version | installed_version |                 comment                  
    --------------+-----------------+-------------------+------------------------------------------
     pg_dirtyread | 2               |                   | Read dead but unvacuumed rows from table
    (1 row)
    
    pgbenchdb=# create extension pg_dirtyread;
    
    pgbenchdb=# \dx+ pg_dirtyread
    Objects in extension "pg_dirtyread"
           Object description        
    ---------------------------------
     function pg_dirtyread(regclass)
    (1 row)

# dml 验证 #

pgbenchdb=# create table tmp_t0(
    id   int8,
    name varchar(100)
    );
    
    pgbenchdb=# insert into tmp_t0(id,name) values(1,'1'),(2,'2'),(3,'3'),(4,'4'),(5,'5');
    
    pgbenchdb=# select * from tmp_t0;
     id | name 
    ----+------
      1 | 1
      2 | 2
      3 | 3
      4 | 4
      5 | 5
    (5 rows)

pgbenchdb=# delete from tmp_t0 where id in (1,2);
    
    pgbenchdb=# select * from tmp_t0;
     id | name 
    ----+------
      3 | 3
      4 | 4
      5 | 5
    (3 rows)

pgbenchdb=# SELECT * FROM pg_dirtyread('tmp_t0') as t(id int8, name varchar(100));
     id | name 
    ----+------
      1 | 1
      2 | 2
      3 | 3
      4 | 4
      5 | 5
    (5 rows)

看到被删除的数据 id=1、id=2

执行下 vacuum

pgbenchdb=# vacuum tmp_t0;
    pgbenchdb=# SELECT * FROM pg_dirtyread('tmp_t0') as t(id int8, name varchar(100));
     id | name 
    ----+------
      3 | 3
      4 | 4
      5 | 5
    (3 rows)

发现数据被抹掉了。

看官方建议需要设置表级别关闭 vacuum,这样做弊端还是很很明显。

ALTER TABLE foo tmp_t0 (
    	autovacuum_enabled = false, toast.autovacuum_enabled = false
    );

# ddl 验证 #

pgbenchdb=# ALTER TABLE tmp_t0 DROP COLUMN name;
    
    pgbenchdb=# SELECT * FROM pg_dirtyread('tmp_t0') tmp_t0_tmp(id int8, dropped_2 varchar(100));
     id | dropped_2 
    ----+-----------
      3 | 3
      4 | 4
      5 | 5
    (3 rows)
    
    
    pgbenchdb=# SELECT * FROM pg_dirtyread('tmp_t0') AS tmp_t0_tmp(
                tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,
                id bigint, dropped_2 varchar(100) );
    		   
     tableoid | ctid  |  xmin  | xmax | cmin | cmax | dead | id | dropped_2 
    ----------+-------+--------+------+------+------+------+----+-----------
        24578 | (0,3) | 245715 |    0 |    0 |    0 | f    |  3 | 3
        24578 | (0,4) | 245715 |    0 |    0 |    0 | f    |  4 | 4
        24578 | (0,5) | 245715 |    0 |    0 |    0 | f    |  5 | 5
    (3 rows)

参考  
https://github.com/df7cb/pg\_dirtyread