MogDB 2.1 支持闪回事务查询/flashback table drop/truncate恢复

MogDB2.1版本发布已经有2月有余了，春节期间一直没时间测试，今天特意抽空简单测试一下针对闪回事务查询和flashback table drop相关的功能。首先需要调整如下几个相关参数：

gs_guc set -N all -I all -c "undo_zone_count=10000"           
gs_guc set -N all -I all -c "enable_default_ustore_table=on"  
gs_guc set -N all -I all -c "version_retention_age=10000"    
gs_guc set -N all -I all -c "enable_recyclebin=on"            
/data/mogdb/bin/gs_ctl restart -D /opt/mogdb/data/db1/

因为默认情况下MogDB 2.1版本中的回收站功能和ustore存储引擎特性未启用，因此需要打开上述功能。

从目前的文档来看；支持闪回事务查询和flashback table drop的恢复。这里我通过自己的虚拟机环境进行一下简单测试验证：

[omm@mogdb script]$ gsql -d enmotech -p26000 -Uroger
Password for user roger: 
gsql ((MogDB 2.1.0 build 56189e20) compiled at 2022-01-07 18:47:53 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

enmotech=>   
enmotech=> create table t0214(a int,b varchar(20));
CREATE TABLE
enmotech=> insert into t0214 values(1,'enmotech.com');
INSERT 0 1
enmotech=> insert into t0214 values(2,'killdb.com');
INSERT 0 1
enmotech=> insert into t0214 values(3,'baidu.com');
INSERT 0 1
enmotech=> select * from t0214;
 a |      b       
---+--------------
 1 | enmotech.com
 2 | killdb.com
 3 | baidu.com
(3 rows)
enmotech=> select current_timestamp;
        pg_systimestamp        
-------------------------------
 2022-02-14 21:16:53.717151+08
(1 row)

enmotech=> update t0214 set b='mogdb.io' where a=3;
UPDATE 1
enmotech=> select * from t0214;
 a |      b       
---+--------------
 1 | enmotech.com
 2 | killdb.com
 3 | mogdb.io
(3 rows)

enmotech=> 
enmotech=> select snptime,snpcsn from gs_txn_snapshot where snptime between '2022-02-14 21:16:53.717151+08' and '2022-02-14 21:17:53.717151+08';
            snptime            | snpcsn 
-------------------------------+--------
 2022-02-14 21:16:56.211226+08 |   2119
 2022-02-14 21:16:59.243046+08 |   2121
 2022-02-14 21:17:02.264878+08 |   2123
 2022-02-14 21:17:05.289966+08 |   2125
 2022-02-14 21:17:08.311199+08 |   2127
 2022-02-14 21:17:11.330451+08 |   2129
 2022-02-14 21:17:14.357411+08 |   2131
 2022-02-14 21:17:17.38367+08  |   2133
 2022-02-14 21:17:20.414649+08 |   2135
 2022-02-14 21:17:23.437261+08 |   2137
 2022-02-14 21:17:26.469704+08 |   2139
 2022-02-14 21:17:29.503769+08 |   2141
 2022-02-14 21:17:32.539126+08 |   2143
 2022-02-14 21:17:35.560913+08 |   2145
 2022-02-14 21:17:38.579216+08 |   2147
 2022-02-14 21:17:41.605395+08 |   2149
 2022-02-14 21:17:44.634837+08 |   2151
 2022-02-14 21:17:47.657877+08 |   2153
 2022-02-14 21:17:50.683656+08 |   2155
(19 rows)

--基于timestamp做闪回查询
enmotech=> select * from t0214 timecapsule timestamp to_timestamp(' 2022-02-14 21:17:02.264878','YYYY-MM-DD HH24:MI:SS.FF');
 a |      b       
---+--------------
 1 | enmotech.com
 2 | killdb.com
 3 | baidu.com
(3 rows)



--基于csn做闪回查询
enmotech=> select * from t0214 timecapsule csn 2121;
 a |      b       
---+--------------
 1 | enmotech.com
 2 | killdb.com
 3 | baidu.com
(3 rows)

enmotech=> 




---测试误删除

enmotech=> drop table t0214;
DROP TABLE
enmotech=> select rcyname,rcyoriginname,rcyrecycletime,rcytablespace from gs_recyclebin;
 rcyname | rcyoriginname | rcyrecycletime | rcytablespace 
---------+---------------+----------------+---------------
(0 rows)

enmotech=> \d t0214
Did not find any relation named "t0214".
enmotech=> 
enmotech=> create table t0214(a int,b varchar(20));
CREATE TABLE
enmotech-> \d t0214 
            Table "public.t0214"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | 
 b      | character varying(20) | 

enmotech-> 
enmotech=> insert into t0214 values(1,'enmotech.com');
INSERT 0 1
enmotech=> insert into t0214 values(2,'killdb.com');
INSERT 0 1
enmotech=> insert into t0214 values(3,'baidu.com');
INSERT 0 1
enmotech=> \d+ t0214  
                                Table "public.t0214"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 a      | integer               |           | plain    |              | 
 b      | character varying(20) |           | extended |              | 
Has OIDs: no
Options: orientation=row, compression=no, storage_type=USTORE

从上述来看，默认使用了Ustore存储引擎。该存储引擎不支持数据库table drop的闪回；仅支持闪回查询。 那么默认的astore存储引擎是否支持闪回事务查询呢？

enmotech=> drop table t0214;
DROP TABLE
enmotech=> select rcyname,rcyoriginname,rcyrecycletime,rcytablespace from gs_recyclebin;
 rcyname | rcyoriginname | rcyrecycletime | rcytablespace 
---------+---------------+----------------+---------------
(0 rows)

enmotech=>  create table t0214_1(a int,b varchar(20)) with (STORAGE_TYPE=ASTORE);
CREATE TABLE
enmotech=> insert into t0214_1 values(1,'enmotech.com');
INSERT 0 1
enmotech=> insert into t0214_1 values(2,'killdb.com');
INSERT 0 1
enmotech=> insert into t0214_1 values(3,'baidu.com');
INSERT 0 1
enmotech=> select * from t0214_1;
 a |      b       
---+--------------
 1 | enmotech.com
 2 | killdb.com
 3 | baidu.com
(3 rows)

enmotech-> \d+ t0214_1
                               Table "public.t0214_1"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 a      | integer               |           | plain    |              | 
 b      | character varying(20) |           | extended |              | 
Has OIDs: no
Options: orientation=row, storage_type=astore, compression=no

enmotech=> 
enmotech=>  select current_timestamp;
       pg_systimestamp        
------------------------------
 2022-02-14 21:45:41.13098+08
(1 row)

enmotech=> select * from t0214_1;
 a |      b       
---+--------------
 1 | enmotech.com
 2 | killdb.com
 3 | baidu.com
(3 rows)

enmotech=> update t0214_1 set b='云和恩墨' where a=3;
UPDATE 1
enmotech=> select current_timestamp;
        pg_systimestamp        
-------------------------------
 2022-02-14 21:46:43.930901+08
(1 row)

enmotech=> select snptime,snpcsn from gs_txn_snapshot where snptime between '2022-02-14 21:45:41.13098+08' and '2022-02-14 21:46:43.930901+08';
            snptime            | snpcsn 
-------------------------------+--------
 2022-02-14 21:45:41.832082+08 |   3278
 2022-02-14 21:45:44.854854+08 |   3280
 2022-02-14 21:45:47.890183+08 |   3282
 2022-02-14 21:45:50.920906+08 |   3284
 2022-02-14 21:45:53.945547+08 |   3286
 2022-02-14 21:45:56.965802+08 |   3288
 2022-02-14 21:45:59.991219+08 |   3290
 2022-02-14 21:46:03.011581+08 |   3292
 2022-02-14 21:46:06.030345+08 |   3294
 2022-02-14 21:46:09.055675+08 |   3296
 2022-02-14 21:46:12.080262+08 |   3298
 2022-02-14 21:46:15.102671+08 |   3300
 2022-02-14 21:46:18.122371+08 |   3302
 2022-02-14 21:46:21.148149+08 |   3304
 2022-02-14 21:46:24.180653+08 |   3307
 2022-02-14 21:46:27.211797+08 |   3309
 2022-02-14 21:46:30.235113+08 |   3311
 2022-02-14 21:46:33.252069+08 |   3313
 2022-02-14 21:46:36.281825+08 |   3315
 2022-02-14 21:46:39.306997+08 |   3317
 2022-02-14 21:46:42.337767+08 |   3319
(21 rows)

enmotech=> select * from t0214_1 timecapsule csn 3280;
ERROR:  Restore point too old
enmotech=> select * from t0214_1 timecapsule csn 3278;
ERROR:  Restore point too old
enmotech=> select * from t0214_1 timecapsule csn  3292;
ERROR:  Restore point too old

由此可见astore存储引擎暂时不支持闪回事务查询。下面进一步测试闪回表的功能支持情况：

enmotech=> drop table t0214;
DROP TABLE
enmotech=> drop table t0214_1;
DROP TABLE
enmotech=> select rcyname,rcyoriginname,rcyrecycletime,rcytablespace from gs_recyclebin;
           rcyname           | rcyoriginname |        rcyrecycletime         | rcytablespace 
-----------------------------+---------------+-------------------------------+---------------
 BIN$40004EB400B$22B2048==$0 | t0214_1       | 2022-02-14 21:33:22.091615+08 |             0
(1 row)

enmotech=>  timecapsule table "BIN$40004EB400B$22B2048==$0" to before drop rename to t0214_1;
TimeCapsule Table
enmotech=>  select rcyname,rcyoriginname,rcyrecycletime,rcytablespace from gs_recyclebin;
 rcyname | rcyoriginname | rcyrecycletime | rcytablespace 
---------+---------------+----------------+---------------
(0 rows)

enmotech=>  select * from t0214_1;
 a |      b       
---+--------------
 1 | enmotech.com
 2 | killdb.com
 3 | baidu.com
(3 rows)

enmotech=> 

---truncate drop 恢复
enmotech=> truncate table t0214_1;
TRUNCATE TABLE
enmotech=> select rcyname,rcyoriginname,rcyrecycletime,rcytablespace from gs_recyclebin;
           rcyname           | rcyoriginname |        rcyrecycletime         | rcytablespace 
-----------------------------+---------------+-------------------------------+---------------
 BIN$40004EB400B$24A8BC0==$0 | t0214_1       | 2022-02-15 10:15:24.168793+08 |             0
(1 row)

enmotech=> TIMECAPSULE TABLE t0214_1 to before truncate;
TimeCapsule Table
enmotech=> select * from t0214_1;
 a |      b       
---+--------------
 1 | enmotech.com
 2 | killdb.com
 3 | 云云和恩墨
(3 rows)

从测试来看回收站功能跟Oracle类似，也是产生BIN的表，暂时存放到回收站中。 这里进行简单总结：

1、闪回事务查询功能，仅支持ustore存储引擎，默认astore存储引擎暂不支持；
2、回收站功能仅支持astore存储引擎，暂不支持ustore存储引擎。

3、MogDB 回收站功能支持truncate table的恢复，这一点真心很赞！

话说上述两点总结看起来是不是有点绕？不管如何，这也是MogDB 2.1版本提供针对误操作场景的一些应对措施，是一个良好开端。