【海量】【ustore】【闪回】外键不支持闪回表，支持闪回查询当前csn

【标题描述】:外键不支持闪回表，但是支持闪回查询
【测试类型：SQL功能/存储功能/接口功能/工具功能/性能/并发/压力长稳/故障注入/安全/资料/编码规范】【测试版本：x.x.x】 问题描述

【操作系统和硬件信息】(查询命令: cat /etc/system-release, uname -a):
![输入图片说明](https://foruda.gitee.com/images/1718190983854892171/3b50e7ce_12773987.png "屏幕截图")
【测试环境】(单机/1主x备x级联备):
单机
【被测功能】:
ustore-闪回
【测试类型】:
功能测试
【数据库版本】(查询命令: gaussdb -V):
gaussdb (openGauss 6.0.0 build 982a7bec) compiled at 2024-05-29 09:11:24 commit o last mr
【预置条件】:

【操作步骤】(请填写详细的操作步骤):
1. --创建记录函数和记录表，方便获取时间戳和csn
drop table t_timecapsule_test_tmp;
create table t_timecapsule_test_tmp(id int, snaptime timestamptz, snapcsn bigint);
CREATE OR REPLACE FUNCTION findCsn(int8)
  RETURNS INTEGER
  LANGUAGE plpgsql
AS
$BODY$
declare
  count integer;
begin
  count = (select snapcsn from t_timecapsule_test_tmp where id =  $1);
  return count;
end;
$BODY$;
CREATE OR REPLACE FUNCTION findTime(int8)
  RETURNS timestamptz
  LANGUAGE plpgsql
AS
$BODY$
declare
  count timestamptz;
begin
  count = (select snaptime from t_timecapsule_test_tmp where id =  $1);
  return count;
end;
$BODY$;

2、参数设置
alter system set enable_recyclebin=on;
alter system set recyclebin_retention_time=10000;
alter system set undo_retention_time=10000;
3、创建表和数据
--清理历史的无效csn数据
truncate t_timecapsule_test_tmp;
--添加各类约束--主键约束、外键约束、唯一约束、check\null\not null约束、default
drop table t9;
drop table t9_foreign;
create table t9_foreign(id serial primary key,col varchar(20) not null unique)with(storage_type=ustore);
create table t9(
c1 int primary key,
c2 number not null,
c3 varchar(20) not null unique default 'null',
c4 bigint check (c4>5),
c5 int,
c6 int,
c7 text,
constraint f1 foreign key (c5) references t9_foreign(id),
constraint f2 foreign key (c6) references t9(c1)
)with(storage_type=ustore);
select pg_sleep(4);
insert into t_timecapsule_test_tmp select 1, now(), int8in(xidout(next_csn)) from gs_get_next_xid_csn();
select * from t_timecapsule_test_tmp;
insert into t9_foreign(col) values('text'||generate_series(1,10));
select pg_sleep(4);
insert into t_timecapsule_test_tmp select 2, now(), int8in(xidout(next_csn)) from gs_get_next_xid_csn();
select * from t_timecapsule_test_tmp;
insert into t9 values (
generate_series(1,10),
generate_series(1,10),
'var'||generate_series(1,10),
generate_series(6,10),
generate_series(1,10),
generate_series(1,10),
'text'||generate_series(1,10));
select pg_sleep(4);
insert into t_timecapsule_test_tmp select 3, now(), int8in(xidout(next_csn)) from gs_get_next_xid_csn();
select * from t_timecapsule_test_tmp;

4、执行闪回查询/闪回表
select * from t9 timecapsule csn findCsn(1);
select * from t9 timecapsule timestamp findTime(1);
timecapsule table t9 to csn findCsn(1);
timecapsule table t9 to timestamp findTime(1);

select * from t9 timecapsule csn findCsn(2);
select * from t9 timecapsule timestamp findTime(2);
timecapsule table t9 to csn findCsn(2);
timecapsule table t9 to timestamp findTime(2);

select * from t9 timecapsule csn findCsn(3);
select * from t9 timecapsule timestamp findTime(3);
timecapsule table t9 to csn findCsn(3);
timecapsule table t9 to timestamp findTime(3);

【预期输出】:
闪回查询和闪回表均执行报错
【实际输出】:
1、前2次的闪回查询未报错而是返回空
![输入图片说明](https://foruda.gitee.com/images/1718191701276142343/8ead5499_12773987.png "屏幕截图")
![输入图片说明](https://foruda.gitee.com/images/1718191684830789955/d3945f25_12773987.png "屏幕截图")
2、最后csn和时间戳的闪回查询未报错，并且查询不为空
![输入图片说明](https://foruda.gitee.com/images/1718191670899859260/76436902_12773987.png "屏幕截图")
【原因分析】:
1. 这个问题的根因
2. 问题推断过程
3. 还有哪些原因可能造成类似现象
4. 该问题是否有临时规避措施
5. 问题解决方案
6. 预计修复问题时间

【日志信息】(请附上日志文件、截图、coredump信息):

【测试代码】:

openGauss/openGauss-server

内容风险标识

评论 (4)

openGauss/openGauss-server .gitee-modal { width: 500px !important; }

内容风险标识