闪回多级配置

闪回的多级配置

为了实现更细粒度的闪回模式控制，flashback_mode支持多级配置，包括表级，schame级和全局配置。表级和schema级的配置选项同样分为三种，与全局的闪回模式参数一一对应：

flashback_mode	schema级	表级	含义
none	0	0	不允许关闭进行闪回
mod	1	1	允许闪回表修改操作，无法闪回truncate和drop操作
all	2	2	允许闪回所有操作

下面分别介绍各个级别闪回参数的配置方法和用例

表级配置

使用表选项配置到表上的闪回模式参数，有最高的优先级。配置表级闪回模式参数的语句为

CREATE TABLE ... with (flashback_mode=0/1/2);
ALTER TABLE ... set (flashback_mode=0/1/2);

表级闪回参数配置为1

可以闪回DML操作，以及一些不删除表文件的操作，无法闪回truncate和drop操作

seaboxsql=# CREATE TABLE t1(a int, b text) with (flashback_mode=1);
CREATE TABLE
seaboxsql=# insert into t1 select g, g||'abcd' from generate_series(1, 10)g;
INSERT 0 10
seaboxsql=# select txid_current();
 txid_current 
--------------
          737
(1 row)

seaboxsql=# insert into t1 select g, g||'abcd' from generate_series(11, 20)g;
INSERT 0 10
seaboxsql=# flashback table t1 xid 737;
FLASHBACK TABLE
seaboxsql=# select * from t1;
 a  |   b    
----+--------
  1 | 1abcd
  2 | 2abcd
  3 | 3abcd
  4 | 4abcd
  5 | 5abcd
  6 | 6abcd
  7 | 7abcd
  8 | 8abcd
  9 | 9abcd
 10 | 10abcd
(10 rows)

seaboxsql=# select txid_current();
 txid_current 
--------------
          740
(1 row)

seaboxsql=# drop table t1;
DROP TABLE
seaboxsql=# flashback table t1 xid 740;
ERROR:  relation "t1" dose not support flashback DROP/TRUNCATE operation

表级闪回参数配置为2

seaboxsql=# CREATE TABLE t1(a int, b text) with (flashback_mode=2);
CREATE TABLE
seaboxsql=# insert into t1 select g, g||'abcd' from generate_series(1, 10)g;
INSERT 0 10
seaboxsql=# select txid_current();
 txid_current 
--------------
          737
(1 row)

seaboxsql=# insert into t1 select g, g||'abcd' from generate_series(11, 20)g;
INSERT 0 10
seaboxsql=# flashback table t1 xid 737;
FLASHBACK TABLE
seaboxsql=# select * from t1;
 a  |   b    
----+--------
  1 | 1abcd
  2 | 2abcd
  3 | 3abcd
  4 | 4abcd
  5 | 5abcd
  6 | 6abcd
  7 | 7abcd
  8 | 8abcd
  9 | 9abcd
 10 | 10abcd
(10 rows)

seaboxsql=# select txid_current();
 txid_current 
--------------
          740
(1 row)

seaboxsql=# drop table t1;
DROP TABLE
seaboxsql=# flashback table t1 xid 740;
FLASHBACK TABLE
seaboxsql=# select * from t1;
 a  |   b    
----+--------
  1 | 1abcd
  2 | 2abcd
  3 | 3abcd
  4 | 4abcd
  5 | 5abcd
  6 | 6abcd
  7 | 7abcd
  8 | 8abcd
  9 | 9abcd
 10 | 10abcd
(10 rows)

schema级配置

配置到schema级别的闪回模式参数，优先级低于表级参数，但高于全局参数。配置表级闪回模式参数的语句为

CREATE SCHEMA ... with (flashback_mode=0/1/2);
ALTER SCHEMA ... set (flashback_mode=0/1/2);

schema级别参数设置为1

seaboxsql=# create schema test with (flashback_mode=1);
CREATE SCHEMA
seaboxsql=# create table test.t1 (a int, b text);
CREATE TABLE
seaboxsql=# insert into test.t1 select g, g||'abcd' from generate_series(1, 10)g;
INSERT 0 10
seaboxsql=# select txid_current();
 txid_current 
--------------
          761
(1 row)

seaboxsql=# insert into test.t1 select g, g||'abcd' from generate_series(11, 20)g;
INSERT 0 10
seaboxsql=# flashback table test.t1 xid 761;
FLASHBACK TABLE
seaboxsql=# select * from test.t1;
 a  |   b    
----+--------
  1 | 1abcd
  2 | 2abcd
  3 | 3abcd
  4 | 4abcd
  5 | 5abcd
  6 | 6abcd
  7 | 7abcd
  8 | 8abcd
  9 | 9abcd
 10 | 10abcd
(10 rows)

seaboxsql=# drop table test.t1;
DROP TABLE
seaboxsql=# flashback table test.t1 xid 761;
ERROR:  relation "t1" dose not support flashback DROP/TRUNCATE operation

schema级别参数设置为2

seaboxsql=# create schema test with (flashback_mode=2);
CREATE SCHEMA
seaboxsql=# create table test.t1 (a int, b text);
CREATE TABLE
seaboxsql=# insert into test.t1 select g, g||'abcd' from generate_series(1, 10)g;
INSERT 0 10
seaboxsql=# select txid_current();
 txid_current 
--------------
          761
(1 row)

seaboxsql=# insert into test.t1 select g, g||'abcd' from generate_series(11, 20)g;
INSERT 0 10
seaboxsql=# flashback table test.t1 xid 761;
FLASHBACK TABLE
seaboxsql=# select * from test.t1;
 a  |   b    
----+--------
  1 | 1abcd
  2 | 2abcd
  3 | 3abcd
  4 | 4abcd
  5 | 5abcd
  6 | 6abcd
  7 | 7abcd
  8 | 8abcd
  9 | 9abcd
 10 | 10abcd
(10 rows)

seaboxsql=# drop table test.t1;
DROP TABLE
seaboxsql=# flashback table test.t1 xid 761;
FLASHBACK TABLE
seaboxsql=# select * from t1;
 a  |   b    
----+--------
  1 | 1abcd
  2 | 2abcd
  3 | 3abcd
  4 | 4abcd
  5 | 5abcd
  6 | 6abcd
  7 | 7abcd
  8 | 8abcd
  9 | 9abcd
 10 | 10abcd
(10 rows)

多级闪回模式配置

当有多个级别的flashback_mode同时设置时，我们以表级>schema级>全局级别的优先级来确定一张表的flashback_mode。需要注意的是，开启全局闪回参数（至少设置为flashback_mode='mod'）级别，才能开启表级和schema级别的闪回参数。使用场景例如下：

对指定表开启闪回

seaboxsql=# alter system set flashback_mode = 'mod';
ALTER SYSTEM
seaboxsql=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

seaboxsql=# CREATE TABLE t1(a int, b text) with (flashback_mode=2);
CREATE TABLE
seaboxsql=# insert into t1 select g, g||'abcd' from generate_series(1, 10)g;
INSERT 0 10
seaboxsql=# select txid_current();
 txid_current 
--------------
          737
(1 row)

seaboxsql=# insert into t1 select g, g||'abcd' from generate_series(11, 20)g;
INSERT 0 10
seaboxsql=# flashback table t1 xid 737;
FLASHBACK TABLE
seaboxsql=# select * from t1;
 a  |   b    
----+--------
  1 | 1abcd
  2 | 2abcd
  3 | 3abcd
  4 | 4abcd
  5 | 5abcd
  6 | 6abcd
  7 | 7abcd
  8 | 8abcd
  9 | 9abcd
 10 | 10abcd
(10 rows)

seaboxsql=# select txid_current();
 txid_current 
--------------
          740
(1 row)

seaboxsql=# drop table t1;
DROP TABLE
seaboxsql=# flashback table t1 xid 740;
FLASHBACK TABLE
seaboxsql=# select * from t1;
 a  |   b    
----+--------
  1 | 1abcd
  2 | 2abcd
  3 | 3abcd
  4 | 4abcd
  5 | 5abcd
  6 | 6abcd
  7 | 7abcd
  8 | 8abcd
  9 | 9abcd
 10 | 10abcd
(10 rows)

对指定schema开启闪回

seaboxsql=# alter system set flashback_mode = 'mod';
ALTER SYSTEM
seaboxsql=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

seaboxsql=# create schema test with (flashback_mode=2);
CREATE SCHEMA
seaboxsql=# create table test.t1 (a int, b text);
CREATE TABLE
seaboxsql=# insert into test.t1 select g, g||'abcd' from generate_series(1, 10)g;
INSERT 0 10
seaboxsql=# select txid_current();
 txid_current 
--------------
          761
(1 row)

seaboxsql=# insert into test.t1 select g, g||'abcd' from generate_series(11, 20)g;
INSERT 0 10
seaboxsql=# flashback table test.t1 xid 761;
FLASHBACK TABLE
seaboxsql=# select * from test.t1;
 a  |   b    
----+--------
  1 | 1abcd
  2 | 2abcd
  3 | 3abcd
  4 | 4abcd
  5 | 5abcd
  6 | 6abcd
  7 | 7abcd
  8 | 8abcd
  9 | 9abcd
 10 | 10abcd
(10 rows)

seaboxsql=# drop table test.t1;
DROP TABLE
seaboxsql=# flashback table test.t1 xid 761;
FLASHBACK TABLE
seaboxsql=# select * from t1;
 a  |   b    
----+--------
  1 | 1abcd
  2 | 2abcd
  3 | 3abcd
  4 | 4abcd
  5 | 5abcd
  6 | 6abcd
  7 | 7abcd
  8 | 8abcd
  9 | 9abcd
 10 | 10abcd
(10 rows)

指定schema开启闪回，schema下指定表关闭闪回

seaboxsql=# alter system set flashback_mode = 'mod';
ALTER SYSTEM
seaboxsql=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

seaboxsql=# create schema test with (flashback_mode=2);
CREATE SCHEMA
seaboxsql=# create table test.t1 (a int, b text) with (flashback_mode=1);
CREATE TABLE
seaboxsql=# insert into test.t1 select g, g||'abcd' from generate_series(1, 10)g;
INSERT 0 10
seaboxsql=# select txid_current();
 txid_current 
--------------
          761
(1 row)

seaboxsql=# insert into test.t1 select g, g||'abcd' from generate_series(11, 20)g;
INSERT 0 10
seaboxsql=# flashback table test.t1 xid 761;
FLASHBACK TABLE
seaboxsql=# select * from test.t1;
 a  |   b    
----+--------
  1 | 1abcd
  2 | 2abcd
  3 | 3abcd
  4 | 4abcd
  5 | 5abcd
  6 | 6abcd
  7 | 7abcd
  8 | 8abcd
  9 | 9abcd
 10 | 10abcd
(10 rows)

seaboxsql=# drop table test.t1;
DROP TABLE
seaboxsql=# flashback table test.t1 xid 761;
relation "t1" dose not support flashback DROP/TRUNCATE operation