digoal
2023-01-14
PostgreSQL , PolarDB , 会话 , 脏读
1、如何读取活跃会话中未提交事务的数据?
通过脏读插件, 同时通过tuple head区分不同的tuple类别:
- 插入:
xmin=$xid, xmax=0 - 删除其他已提交数据:
xmin<>xmax, xmax=$xid - 删除自己插入的数据:
xmin=xmax=$xid - 更新其他已提交数据: = 删除其他已提交数据+插入 = 2条数据:
(xmin<>xmax, xmax=$xid) + (xmin=$xid, xmax=0) - 更新自己插入的数据: = 删除自己插入的数据+插入 = 2条数据:
(xmin=xmax=$xid) + (xmin=$xid, xmax=0)
2、如何flashback闪回误操作dml?
配置延迟垃圾回收,防止DML操作产生的旧(dead tuple)数据被过早vacuum(回收),给flashback机会窗口. 但是坏处是: 表和索引膨胀的可能变大, shared buffer浪费, 查询性能下降, vacuum无用功导致的cpu和读io升高影响性能.
#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
然后使用脏读, 获取被误操作事务操作的dead数据.
3、如何闪回ddl?
垃圾回收站或者event trigger.
其他参考:
- 《PostgreSQL 通过old version tuple模拟update, delete闪回》
- 《DB吐槽大会,第10期 - 不支持 flashback query》
- 《PostgreSQL 14 preview - System Versioned Temporal Table - flashback闪回、跟踪、审计 record/row/tuple历史版本》
- 《如何创建RDS PG 的秒级 flashback闪回实例, 实时容灾实例 - zfs - snapshot - clone - standby - compress》
- 《PostgreSQL pg_rewind，时间线修复，脑裂修复，flashback - 从库开启读写后，回退为只读从库。异步主从发生角色切换后，主库rewind为新主库的从库》
- 《PostgreSQL flashback(闪回) 功能实现与介绍》
- 《PostgreSQL Oracle 兼容性之 - 事件触发器实现类似Oracle的回收站功能》
- 《PostgreSQL 闪回 - flash back query emulate by trigger》
- 《PostgreSQL 回收站功能 - 基于HOOK的recycle bin pgtrashcan》
安装pg_dirtyread插件
https://github.com/df7cb/pg_dirtyread
cd ~
git clone --depth 1 https://github.com/df7cb/pg_dirtyread
cd pg_dirtyread
USE_PGXS=1 make
USE_PGXS=1 make install
digoaldeMacBook-Pro:pg_dirtyread digoal$ psql
psql (15.1)
Type "help" for help.
postgres=# create extension pg_dirtyread ;
CREATE EXTENSION
建立测试表
create table t (id int, info text);
session a
begin;
insert into t values (1,'a');
select txid_current_if_assigned();
txid_current_if_assigned
--------------------------
742
(1 row)
session b
begin;
insert into t values (2,'b');
select txid_current_if_assigned();
-[ RECORD 1 ]------------+----
txid_current_if_assigned | 743
session c
获得未结束事务的事务号: pg_stat_activity.backend_xid
postgres=# select * from pg_stat_activity where backend_type='client backend';;
-[ RECORD 1 ]----+--------------------------------------------------------------------
datid | 5
datname | postgres
pid | 1200
leader_pid |
usesysid | 10
usename | postgres
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2023-01-14 10:32:47.481692+08
xact_start | 2023-01-14 10:32:58.170729+08
query_start | 2023-01-14 10:32:58.64865+08
state_change | 2023-01-14 10:32:58.649045+08
wait_event_type | Client
wait_event | ClientRead
state | idle in transaction
backend_xid | 742
backend_xmin |
query_id |
query | select txid_current_if_assigned();
backend_type | client backend
-[ RECORD 2 ]----+--------------------------------------------------------------------
datid | 5
datname | postgres
pid | 1194
leader_pid |
usesysid | 10
usename | postgres
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2023-01-14 10:31:07.284393+08
xact_start | 2023-01-14 10:34:15.237452+08
query_start | 2023-01-14 10:34:15.237452+08
state_change | 2023-01-14 10:34:15.237454+08
wait_event_type |
wait_event |
state | active
backend_xid |
backend_xmin | 742
query_id |
query | select * from pg_stat_activity where backend_type='client backend';
backend_type | client backend
-[ RECORD 3 ]----+--------------------------------------------------------------------
datid | 5
datname | postgres
pid | 1085
leader_pid |
usesysid | 10
usename | postgres
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2023-01-14 10:25:12.514831+08
xact_start | 2023-01-14 10:33:05.779798+08
query_start | 2023-01-14 10:33:06.672514+08
state_change | 2023-01-14 10:33:06.672628+08
wait_event_type | Client
wait_event | ClientRead
state | idle in transaction
backend_xid | 743
backend_xmin |
query_id |
query | select txid_current_if_assigned();
backend_type | client backend
或者 pg_locks.transactionid
postgres=# select * from pg_locks where pid in (1194,1200,1085) and locktype='transactionid';;
-[ RECORD 1 ]------+--------------
locktype | transactionid
database |
relation |
page |
tuple |
virtualxid |
transactionid | 742
classid |
objid |
objsubid |
virtualtransaction | 5/10
pid | 1200
mode | ExclusiveLock
granted | t
fastpath | f
waitstart |
-[ RECORD 2 ]------+--------------
locktype | transactionid
database |
relation |
page |
tuple |
virtualxid |
transactionid | 743
classid |
objid |
objsubid |
virtualtransaction | 3/18
pid | 1085
mode | ExclusiveLock
granted | t
fastpath | f
waitstart |
脏读
postgres=# SELECT * FROM pg_dirtyread('t') as t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean, id int, info text);
tableoid | ctid | xmin | xmax | cmin | cmax | dead | id | info
----------+-------+------+------+------+------+------+----+------
16393 | (0,1) | 742 | 0 | 0 | 0 | f | 1 | a
16393 | (0,2) | 743 | 0 | 0 | 0 | f | 2 | b
(2 rows)
tuple.xmin, xmax ; pg_locks.transactionid ; pg_stat_activity.backend_xid 类型都是xid, 所以查询时不需要转换.
postgres=# \d pg_stat_activity
View "pg_catalog.pg_stat_activity"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
pid | integer | | |
leader_pid | integer | | |
usesysid | oid | | |
usename | name | | |
application_name | text | | |
client_addr | inet | | |
client_hostname | text | | |
client_port | integer | | |
backend_start | timestamp with time zone | | |
xact_start | timestamp with time zone | | |
query_start | timestamp with time zone | | |
state_change | timestamp with time zone | | |
wait_event_type | text | | |
wait_event | text | | |
state | text | | |
backend_xid | xid | | |
backend_xmin | xid | | |
query_id | bigint | | |
query | text | | |
backend_type | text | | |
postgres=# \d pg_locks
View "pg_catalog.pg_locks"
Column | Type | Collation | Nullable | Default
--------------------+--------------------------+-----------+----------+---------
locktype | text | | |
database | oid | | |
relation | oid | | |
page | integer | | |
tuple | smallint | | |
virtualxid | text | | |
transactionid | xid | | |
classid | oid | | |
objid | oid | | |
objsubid | smallint | | |
virtualtransaction | text | | |
pid | integer | | |
mode | text | | |
granted | boolean | | |
fastpath | boolean | | |
waitstart | timestamp with time zone | | |
如果用txid_current_if_assigned则需要转换一下.
《PostgreSQL xid(int4) to txid(int8)》
postgres=# \df txid_current_if_assigned
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+--------------------------+------------------+---------------------+------
pg_catalog | txid_current_if_assigned | bigint | | func
(1 row)
postgres=# select cast(xid8 '9000000000' as xid);
xid
-----------
410065408
(1 row)
session a:
postgres=*# delete from t;
DELETE 1
session b:
postgres=*# update t set info='b1';
UPDATE 1
session c:
postgres=# SELECT * FROM pg_dirtyread('t') as t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean, id int, info text);
tableoid | ctid | xmin | xmax | cmin | cmax | dead | id | info
----------+-------+------+------+------+------+------+----+------
16393 | (0,1) | 742 | 742 | 0 | 0 | f | 1 | a
16393 | (0,2) | 743 | 743 | 0 | 0 | f | 2 | b
16393 | (0,3) | 743 | 0 | 1 | 1 | f | 2 | b1
(3 rows)
使用xid, xmin, xmax进行过滤:
- 插入:
xmin=$xid, xmax=0 - 删除其他已提交数据:
xmin<>xmax, xmax=$xid - 删除自己插入的数据:
xmin=xmax=$xid - 更新其他已提交数据: = 删除其他已提交数据+插入 = 2条数据:
(xmin<>xmax, xmax=$xid) + (xmin=$xid, xmax=0) - 更新自己插入的数据: = 删除自己插入的数据+插入 = 2条数据:
(xmin=xmax=$xid) + (xmin=$xid, xmax=0)
