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è)良好開端。
「喜歡這篇文章，您的關(guān)注和贊賞是給作者最好的鼓勵(lì)」
關(guān)注作者
【版權(quán)聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容，轉(zhuǎn)載時(shí)必須標(biāo)注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權(quán)追究責(zé)任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權(quán)的內(nèi)容，歡迎發(fā)送郵件至：contact@modb.pro進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，墨天輪將立刻刪除相關(guān)內(nèi)容。
