mogdb 2.1 支持闪回事务查询/flashback table drop/truncate恢复 -m6米乐安卓版下载

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版本提供针对误操作场景的一些应对措施，是一个良好开端。