MogDB2.1版本發(fā)布已經(jīng)有2月有余了，春節(jié)期間一直沒時(shí)間測試，今天特意抽空簡單測試一下針對閃回事務(wù)查詢和flashback table drop相關(guān)的功能。首先需要調(diào)整如下幾個(gè)相關(guān)參數(shù)：

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/

因?yàn)槟J(rèn)情況下MogDB 2.1版本中的回收站功能和ustore存儲引擎特性未啟用，因此需要打開上述功能。

從目前的文檔來看；支持閃回事務(wù)查詢和flashback table drop的恢復(fù)。這里我通過自己的虛擬機(jī)環(huán)境進(jìn)行一下簡單測試驗(yàn)證：

[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

從上述來看，默認(rèn)使用了Ustore存儲引擎。該存儲引擎不支持?jǐn)?shù)據(jù)庫table drop的閃回；僅支持閃回查詢。 那么默認(rèn)的astore存儲引擎是否支持閃回事務(wù)查詢呢？

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存儲引擎暫時(shí)不支持閃回事務(wù)查詢。下面進(jìn)一步測試閃回表的功能支持情況：

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 恢復(fù)
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類似，也是產(chǎn)生BIN的表，暫時(shí)存放到回收站中。 這里進(jìn)行簡單總結(jié)：

1、閃回事務(wù)查詢功能，僅支持ustore存儲引擎，默認(rèn)astore存儲引擎暫不支持；
2、回收站功能僅支持astore存儲引擎，暫不支持ustore存儲引擎。

3、MogDB 回收站功能支持truncate table的恢復(fù)，這一點(diǎn)真心很贊！

話說上述兩點(diǎn)總結(jié)看起來是不是有點(diǎn)繞？不管如何，這也是MogDB 2.1版本提供針對誤操作場景的一些應(yīng)對措施，是一個(gè)良好開端。