支持增加列/删除列的闪回查询
更新时间 2025-02-05 09:37:13
最近更新时间: 2025-02-05 09:37:13
本页为您介绍增加列/删除列的闪回查询的方法。
在foo表增加info列，并插入数据
teledb=# alter table foo add column info text;
ALTER TABLE
teledb=# insert into foo values (7,'ggg', 'new added column');
INSERT 0 1查询给定时间点的历史数据（增加列情况）
teledb=# SELECT pg_xact_commit_timestamp(xmin) as xmin_time, pg_xact_commit_timestamp(case xmax when 0 then null else xmax end) as xmax_time,*
FROM pg_dirtyread('foo') AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,bar bigint, baz text, info text)
where (pg_xact_commit_timestamp(xmin) <= ' 2023-11-02 16:43:02' and
(pg_xact_commit_timestamp(case xmax when 0 then null else xmax end) < '2023-11-02 16:43:02') is distinct from true
) ;
xmin_time | xmax_time | tableoid | ctid | xmin | xmax | cmin | cmax | dead | bar | baz | info
-------------------------------+-----------+----------+-------+------+------+------+------+------+-----+----------+------
2023-11-02 16:42:47.230036+08 | | 16432 | (0,3) | 680 | 0 | 0 | 0 | f | 5 | eee |
2023-11-02 16:42:41.46109+08 | | 16432 | (0,3) | 659 | 0 | 0 | 0 | f | 3 | ccc_new2 |
(2 rows)删除baz列
teledb=# ALTER TABLE foo DROP COLUMN baz;
ALTER TABLE查询给定时间点的历史数据（删除列情况）
使用dropped_N来访问第N列，从1开始计数。例如dropped_2 表示foo表被删除的第二列baz列。
teledb=# SELECT pg_xact_commit_timestamp(xmin) as xmin_time, pg_xact_commit_timestamp(case xmax when 0 then null else xmax end) as xmax_time,*
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, info text)
where (pg_xact_commit_timestamp(xmin) <= ' 2023-11-02 16:43:02' and
(pg_xact_commit_timestamp(case xmax when 0 then null else xmax end) < '2023-11-02 16:43:02') is distinct from true
) ;
xmin_time | xmax_time | tableoid | ctid | xmin | xmax | cmin | cmax | dead | bar | dropped_2 | info
-------------------------------+-----------+----------+-------+------+------+------+------+------+-----+-----------+------
2023-11-02 16:42:47.230036+08 | | 16432 | (0,3) | 680 | 0 | 0 | 0 | f | 5 | eee |
2023-11-02 16:42:41.46109+08 | | 16432 | (0,3) | 659 | 0 | 0 | 0 | f | 3 | ccc_new2 |
(2 rows)
