-- test: sighup
show vacuum_defer_cleanup_age;
show version_retention_age;

\! @abs_bindir@/gs_guc reload -D @abs_srcdir@/tmp_check/datanode1/ -c "vacuum_defer_cleanup_age = 10000" > /dev/null 2>&1
select pg_sleep(4);
show vacuum_defer_cleanup_age;

\! @abs_bindir@/gs_guc reload -D @abs_srcdir@/tmp_check/datanode1/ -c "version_retention_age = 10000" > /dev/null 2>&1
select pg_sleep(4);
show version_retention_age;

----------------------------------------------------------------------------------
-- test version query, restore in one transaction
----------------------------------------------------------------------------------
create schema schema_test_3;
set search_path = schema_test_3;

drop table tmplog;
create table tmplog(tag int, csn1 number);

create or replace procedure f_save_csn(tag_ int)
as
begin
    -- perform pg_sleep(4);
    insert into tmplog select tag_, int8in(xidout(next_csn)) from gs_get_next_xid_csn();
end;
/

create or replace function f_get_csn(tag_ int)
return number
as
    csn_ number;
begin
    select csn1 into csn_ from tmplog where tag = tag_;
    return csn_;
end;
/

create or replace procedure f_clean_csn
as
begin
    delete from tmplog;
end;
/

-- testcase: in xact test
rollback;
call f_clean_csn();

drop table t1;
create table t1 (c1 int);
alter table t1 enable row movement;  
call f_save_csn(-1);

insert into t1 values (0);
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);
commit;
call f_save_csn(0);
commit;

start transaction;
update t1 set c1 = 10 where c1 = 0;
delete from t1 where c1 = 1;
insert into t1 values (4);
insert into t1 values (5);
insert into t1 values (6);

call f_save_csn(1);

update t1 set c1 = 100 where c1 = 10;
delete from t1 where c1 = 2;
delete from t1 where c1 = 4;
update t1 set c1 = 50 where c1 = 5;
insert into t1 values (7);
insert into t1 values (8);
insert into t1 values (9);

call f_save_csn(2);

update t1 set c1 = 1000 where c1 = 100;
update t1 set c1 = 70 where c1 = 7;
delete from t1 where c1 = 3;
delete from t1 where c1 = 8;
insert into t1 values (100000);
call f_save_csn(3);

savepoint s0;
select * from t1 timecapsule csn f_get_csn(-1);
rollback to s0;
select * from t1 timecapsule csn f_get_csn(0);
select * from t1 timecapsule csn f_get_csn(1);
select * from t1 timecapsule csn f_get_csn(2);
select * from t1 timecapsule csn f_get_csn(3);

savepoint s1;

timecapsule table t1 to csn f_get_csn(0);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(1);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(2);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(3);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(3);
select * from t1 order by c1; 

timecapsule table t1 to csn f_get_csn(2);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(1);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(0);
select * from t1 order by c1;

rollback to s1;
commit;
select * from t1 order by c1;
call f_save_csn(4); 

update t1 set c1 = 1001 where c1 = 1000;
update t1 set c1 = 500 where c1 = 50;
delete from t1 where c1 = 6;
update t1 set c1 = 90 where c1 = 9;
delete from t1 where c1 = 100000;
insert into t1 values (100001);
insert into t1 values (100002);
insert into t1 values (100003);
commit; 
select * from t1 order by c1;
call f_save_csn(5);

timecapsule table t1 to csn f_get_csn(-1);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(0);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(1);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(2);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(3);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(4);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(5);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(0);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(1);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(2);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(3);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(4);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(5);
select * from t1 order by c1; 

timecapsule table t1 to csn f_get_csn(4);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(3);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(2);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(1);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(0);
select * from t1 order by c1;

timecapsule table t1 to csn f_get_csn(5);
select * from t1 order by c1;

commit;
select * from t1 order by c1;

drop table t1;

drop table tmplog;
drop procedure f_save_csn;
drop function f_get_csn;
drop procedure f_clean_csn;

drop schema schema_test_3 cascade;
reset search_path;

-- reset vacuum_defer_cleanup_age
show vacuum_defer_cleanup_age;
show version_retention_age;

\! @abs_bindir@/gs_guc reload -D @abs_srcdir@/tmp_check/datanode1/ -c "vacuum_defer_cleanup_age = 0" > /dev/null 2>&1
select pg_sleep(4);
show vacuum_defer_cleanup_age;

\! @abs_bindir@/gs_guc reload -D @abs_srcdir@/tmp_check/datanode1/ -c "version_retention_age = 0" > /dev/null 2>&1
select pg_sleep(4);
show version_retention_age;