DROP TABLE IF EXISTS mpp_row_table1;
DROP TABLE IF EXISTS mpp_col_table1;
DROP TABLE IF EXISTS mpp_row_partition_table1;
DROP TABLE IF EXISTS mpp_col_partition_table1;


DROP TABLE IF EXISTS mpp_row_table2;
DROP TABLE IF EXISTS mpp_col_table2;
DROP TABLE IF EXISTS mpp_row_partition_table2;
DROP TABLE IF EXISTS mpp_col_parititon_table2;

CREATE SCHEMA redis_schema;

CREATE TABLE mpp_row_table1(id int, id2 int, name varchar);
CREATE INDEX mpp_row_table1_index1 on mpp_row_table1(id);
CREATE INDEX mpp_row_table1_index2 on mpp_row_table1(name);
CREATE INDEX mpp_row_table1_index3 on mpp_row_table1(id);
INSERT INTO mpp_row_table1 values(generate_series(1,20),111,'122');
alter table mpp_row_table1 drop column id2;

CREATE TABLE mpp_col_table1(id int, id2 int, name varchar)with(orientation = column);
CREATE INDEX mpp_col_table1_index1 on mpp_col_table1(id);
CREATE INDEX mpp_col_table1_index2 on mpp_col_table1(name);
INSERT INTO mpp_col_table1 VALUES(generate_series(1,20),111, '122');
alter table mpp_col_table1 drop column id2;

CREATE TABLE mpp_row_partition_table1(id int, id2 int, name varchar) partition by range(id) (partition p1 values less than (6),partition p2 values less than (10));
CREATE INDEX mpp_row_partition_table1_index1 on mpp_row_partition_table1(id) local;
CREATE INDEX mpp_row_partition_table1_index2 on mpp_row_partition_table1(name) local;
CREATE INDEX mpp_row_partition_table1_index3 on mpp_row_partition_table1(id) local;
INSERT INTO mpp_row_partition_table1 VALUES(1,111,'122');
INSERT INTO mpp_row_partition_table1 VALUES(8,811,'822');
alter table mpp_row_partition_table1 drop column id2;

CREATE TABLE mpp_col_partition_table1(id int, id2 int, name varchar) with(orientation = column) partition by range(id) (partition p1 values less than (6),partition p2 values less than (11));
CREATE INDEX mpp_col_partition_table1_index1 on mpp_col_partition_table1(id) local;
CREATE INDEX mpp_col_partition_table1_index2 on mpp_col_partition_table1(name) local;
INSERT INTO mpp_col_partition_table1 VALUES(1, 111,'122');
INSERT INTO mpp_col_partition_table1 VALUES(8, 811,'122');
alter table mpp_col_partition_table1 drop column id2;

ALTER TABLE mpp_row_table1 SET (append_mode=on, rel_cn_oid=12345);
ALTER TABLE mpp_row_partition_table1 SET (append_mode=on, rel_cn_oid=12345);

CREATE INDEX create_fail on mpp_row_table1(id);
CREATE INDEX create_fail2 on mpp_row_partition_table1(id);
REINDEX TABLE mpp_row_table1;
REINDEX TABLE mpp_row_partition_table1;
REINDEX INDEX mpp_row_table1_index1;
REINDEX INDEX mpp_row_partition_table1_index1;

CREATE TABLE redis_schema.mpp_row_table2(like mpp_row_table1 including INDEXES including storage INCLUDING RELOPTIONS including distribution, nodeid int,tupleblocknum bigint,tupleoffset int);
CREATE TABLE mpp_col_table2(like mpp_col_table1 including storage INCLUDING RELOPTIONS including distribution, nodeid int,tupleblocknum bigint,tupleoffset int);
CREATE TABLE redis_schema.mpp_row_partition_table2(like mpp_row_partition_table1 including INDEXES including storage INCLUDING RELOPTIONS including distribution including partition,nodeid int,partitionoid int,tupleblocknum bigint,tupleoffset int);
CREATE TABLE mpp_col_partition_table2(like mpp_col_partition_table1 including storage INCLUDING RELOPTIONS including distribution including partition,nodeid int,partitionoid int,tupleblocknum bigint,tupleoffset int);

select c.relname from pg_class as a, pg_index as b, pg_class as c where a.relname in ('mpp_row_table1', 'mpp_row_table2', 'mpp_row_partition_table1', 'mpp_row_partition_table2') and a.oid=b.indrelid and b.indexrelid=c.oid order by c.relname;

INSERT INTO redis_schema.mpp_row_table2 values(generate_series(1,20),'222',233,244,255);

INSERT INTO mpp_col_table2 VALUES(generate_series(1,20),'222',233,244,255);

INSERT INTO redis_schema.mpp_row_partition_table2 VALUES(1,'222',233,244,255,266);
INSERT INTO redis_schema.mpp_row_partition_table2 VALUES(8,'822',833,844,855,866);

INSERT INTO mpp_col_partition_table2 VALUES(1,'222',233,244,255,266);
INSERT INTO mpp_col_partition_table2 VALUES(8,'822',833,844,855,866);


alter table mpp_row_table1 add column nodeid int,add column tupleblocknum bigint, add column tupleoffset int;
alter table mpp_row_table1 drop column nodeid,drop column tupleblocknum, drop column tupleoffset;

alter table redis_schema.mpp_row_table2 drop column nodeid,drop column tupleblocknum, drop column tupleoffset;

select gs_switch_relfilenode('mpp_row_table1','redis_schema.mpp_row_table2');

--procedure to switch relname and check the data count
create or replace procedure switch_relfilenode_and_check_for_nonpart_table(t1 in TEXT, t1_index1 in TEXT, t1_index2 in TEXT, t1_index3 in TEXT, t2 in TEXT, t2_index1 in TEXT, t2_index2 in TEXT, t2_index3 in TEXT, public_schema IN TEXT, tmp_schema in TEXT)
as
old_num_t1 integer;
old_t1_index1 integer;
old_t1_index2 integer;
old_t1_index3 integer;
old_num_t2 integer;
old_t2_index1 integer;
old_t2_index2 integer;
old_t2_index3 integer;
new_num_t1 integer;
new_t1_index1 integer;
new_t1_index2 integer;
new_t1_index3 integer;
new_num_t2 integer;
new_t2_index1 integer;
new_t2_index2 integer;
new_t2_index3 integer;
t1_fullname TEXT;
t2_fullname TEXT;
begin
    execute('execute direct on (datanode1) ''select a.relfilenode from pg_class as a, pg_namespace as b where a.relname=''''' || t1 || ''''' and a.relnamespace=b.oid and b.nspname=''''' || public_schema || ''''' ''') into old_num_t1;
    execute('execute direct on (datanode1) ''select a.relfilenode from pg_class as a, pg_namespace as b where a.relname=''''' || t1_index1 || ''''' and a.relnamespace=b.oid and b.nspname=''''' || public_schema || ''''' ''') into old_t1_index1;
    execute('execute direct on (datanode1) ''select a.relfilenode from pg_class as a, pg_namespace as b where a.relname=''''' || t1_index2 || ''''' and a.relnamespace=b.oid and b.nspname=''''' || public_schema || ''''' ''') into old_t1_index2;
    execute('execute direct on (datanode1) ''select a.relfilenode from pg_class as a, pg_namespace as b where a.relname=''''' || t1_index3 || ''''' and a.relnamespace=b.oid and b.nspname=''''' || public_schema || ''''' ''') into old_t1_index3;
    execute('execute direct on (datanode1) ''select a.relfilenode from pg_class as a, pg_namespace as b where a.relname=''''' || t2 || ''''' and a.relnamespace=b.oid and b.nspname=''''' || tmp_schema || ''''' ''') into old_num_t2;
    execute('execute direct on (datanode1) ''select a.relfilenode from pg_class as a, pg_namespace as b where a.relname=''''' || t2_index1 || ''''' and a.relnamespace=b.oid and b.nspname=''''' || tmp_schema || ''''' ''') into old_t2_index1;
    execute('execute direct on (datanode1) ''select a.relfilenode from pg_class as a, pg_namespace as b where a.relname=''''' || t2_index2 || ''''' and a.relnamespace=b.oid and b.nspname=''''' || tmp_schema || ''''' ''') into old_t2_index2;
    execute('execute direct on (datanode1) ''select a.relfilenode from pg_class as a, pg_namespace as b where a.relname=''''' || t2_index3 || ''''' and a.relnamespace=b.oid and b.nspname=''''' || tmp_schema || ''''' ''') into old_t2_index3;
    t1_fullname := public_schema||'.'||t1;
    t2_fullname := tmp_schema||'.'||t2;
    execute('select gs_switch_relfilenode(''' || t1_fullname || ''' , ''' || t2_fullname || ''')');
    execute('execute direct on (datanode1) ''select a.relfilenode from pg_class as a, pg_namespace as b where a.relname=''''' || t1 || ''''' and a.relnamespace=b.oid and b.nspname=''''' || public_schema || ''''' ''') into new_num_t1;
    execute('execute direct on (datanode1) ''select a.relfilenode from pg_class as a, pg_namespace as b where a.relname=''''' || t1_index1 || ''''' and a.relnamespace=b.oid and b.nspname=''''' || public_schema || ''''' ''') into new_t1_index1;
    execute('execute direct on (datanode1) ''select a.relfilenode from pg_class as a, pg_namespace as b where a.relname=''''' || t1_index2 || ''''' and a.relnamespace=b.oid and b.nspname=''''' || public_schema || ''''' ''') into new_t1_index2;
    execute('execute direct on (datanode1) ''select a.relfilenode from pg_class as a, pg_namespace as b where a.relname=''''' || t1_index3 || ''''' and a.relnamespace=b.oid and b.nspname=''''' || public_schema || ''''' ''') into new_t1_index3;
    execute('execute direct on (datanode1) ''select a.relfilenode from pg_class as a, pg_namespace as b where a.relname=''''' || t2 || ''''' and a.relnamespace=b.oid and b.nspname=''''' || tmp_schema || ''''' ''') into new_num_t2;
    execute('execute direct on (datanode1) ''select a.relfilenode from pg_class as a, pg_namespace as b where a.relname=''''' || t2_index1 || ''''' and a.relnamespace=b.oid and b.nspname=''''' || tmp_schema || ''''' ''') into new_t2_index1;
    execute('execute direct on (datanode1) ''select a.relfilenode from pg_class as a, pg_namespace as b where a.relname=''''' || t2_index2 || ''''' and a.relnamespace=b.oid and b.nspname=''''' || tmp_schema || ''''' ''') into new_t2_index2;
    execute('execute direct on (datanode1) ''select a.relfilenode from pg_class as a, pg_namespace as b where a.relname=''''' || t2_index3 || ''''' and a.relnamespace=b.oid and b.nspname=''''' || tmp_schema || ''''' ''') into new_t2_index3;
    if (old_num_t1 = new_num_t2 and old_t1_index1 = new_t2_index1 and old_t1_index2 = new_t2_index2 and old_t1_index3 = new_t2_index3 and old_num_t2 = new_num_t1 and old_t2_index1 = new_t1_index1 and old_t2_index2 = new_t1_index2 and old_t2_index3 = new_t1_index3) then
    else
    end if;
    return;
end;
/

select switch_relfilenode_and_check_for_nonpart_table('mpp_row_table1', 'mpp_row_table1_index1', 'mpp_row_table1_index2', 'mpp_row_table1_index3', 'mpp_row_table2', 'mpp_row_table1_index1', 'mpp_row_table1_index2', 'mpp_row_table1_index3', 'public', 'redis_schema');

select * from mpp_row_table1 order by 1;
select * from redis_schema.mpp_row_table2 order by 1;

alter table mpp_col_table1 add column nodeid int,add column tupleblocknum bigint, add column tupleoffset int;
alter table mpp_col_table1 drop column nodeid,drop column tupleblocknum, drop column tupleoffset;

alter table mpp_col_table2 drop column nodeid,drop column tupleblocknum, drop column tupleoffset;

select gs_switch_relfilenode('mpp_col_table1','mpp_col_table2');
select * from mpp_col_table1 order by id;
select * from mpp_col_table2 order by id;


alter table mpp_row_partition_table1 add column nodeid int,add column partitionoid int,add column tupleblocknum bigint, add column tupleoffset int;
alter table mpp_row_partition_table1 drop column nodeid,drop column partitionoid,drop column tupleblocknum, drop column tupleoffset;

alter table redis_schema.mpp_row_partition_table2 drop column nodeid,drop column partitionoid,drop column tupleblocknum, drop column tupleoffset;

--procedure to switch relname and check the data count
create or replace procedure switch_relfilenode_and_check_for_part_table(t1 in TEXT, t1_index1 in TEXT, t1_index2 in TEXT, t1_index3 in TEXT, t2 in TEXT, t2_index1 in TEXT, t2_index2 in TEXT, t2_index3 in TEXT, table_part_name in TEXT, index_part_name1 in TEXT, index_part_name2 in TEXT, public_schema IN TEXT, tmp_schema in TEXT)
as
old_num_t1 integer;
old_t1_index1 integer;
old_t1_index2 integer;
old_t1_index3 integer;
old_num_t2 integer;
old_t2_index1 integer;
old_t2_index2 integer;
old_t2_index3 integer;
new_num_t1 integer;
new_t1_index1 integer;
new_t1_index2 integer;
new_t1_index3 integer;
new_num_t2 integer;
new_t2_index1 integer;
new_t2_index2 integer;
new_t2_index3 integer;
t1_fullname TEXT;
t2_fullname TEXT;
begin
    execute('execute direct on (datanode1) ''select b.relfilenode from pg_class as a, pg_partition as b, pg_namespace as c where a.relname=''''' || t1 || ''''' and b.relname=''''' || table_part_name || ''''' and a.oid=b.parentid and a.relnamespace=c.oid and c.nspname=''''' || public_schema || ''''' ''') into old_num_t1;
    execute('execute direct on (datanode1) ''select b.relfilenode from pg_class as a, pg_partition as b, pg_namespace as c where a.relname=''''' || t1_index1 || ''''' and b.relname=''''' || index_part_name1 || ''''' and a.oid=b.parentid and a.relnamespace=c.oid and c.nspname=''''' || public_schema || ''''' ''') into old_t1_index1;
    execute('execute direct on (datanode1) ''select b.relfilenode from pg_class as a, pg_partition as b, pg_namespace as c where a.relname=''''' || t1_index2 || ''''' and b.relname=''''' || index_part_name2 || ''''' and a.oid=b.parentid and a.relnamespace=c.oid and c.nspname=''''' || public_schema || ''''' ''') into old_t1_index2;
    execute('execute direct on (datanode1) ''select b.relfilenode from pg_class as a, pg_partition as b, pg_namespace as c where a.relname=''''' || t1_index3 || ''''' and b.relname=''''' || index_part_name1 || ''''' and a.oid=b.parentid and a.relnamespace=c.oid and c.nspname=''''' || public_schema || ''''' ''') into old_t1_index3;
    execute('execute direct on (datanode1) ''select b.relfilenode from pg_class as a, pg_partition as b, pg_namespace as c where a.relname=''''' || t2 || ''''' and b.relname=''''' || table_part_name || ''''' and a.oid=b.parentid and a.relnamespace=c.oid and c.nspname=''''' || tmp_schema || ''''' ''') into old_num_t2;
    execute('execute direct on (datanode1) ''select b.relfilenode from pg_class as a, pg_partition as b, pg_namespace as c where a.relname=''''' || t2_index1 || ''''' and b.relname=''''' || index_part_name1 || ''''' and a.oid=b.parentid and a.relnamespace=c.oid and c.nspname=''''' || tmp_schema || ''''' ''') into old_t2_index1;
    execute('execute direct on (datanode1) ''select b.relfilenode from pg_class as a, pg_partition as b, pg_namespace as c where a.relname=''''' || t2_index2 || ''''' and b.relname=''''' || index_part_name2 || ''''' and a.oid=b.parentid and a.relnamespace=c.oid and c.nspname=''''' || tmp_schema || ''''' ''') into old_t2_index2;
    execute('execute direct on (datanode1) ''select b.relfilenode from pg_class as a, pg_partition as b, pg_namespace as c where a.relname=''''' || t2_index3 || ''''' and b.relname=''''' || index_part_name1 || ''''' and a.oid=b.parentid and a.relnamespace=c.oid and c.nspname=''''' || tmp_schema || ''''' ''') into old_t2_index3;
    t1_fullname := public_schema||'.'||t1;
    t2_fullname := tmp_schema||'.'||t2;
    execute('select gs_switch_relfilenode(''' || t1_fullname || ''' , ''' || t2_fullname || ''')');
    execute('execute direct on (datanode1) ''select b.relfilenode from pg_class as a, pg_partition as b, pg_namespace as c where a.relname=''''' || t1 || ''''' and b.relname=''''' || table_part_name || ''''' and a.oid=b.parentid and a.relnamespace=c.oid and c.nspname=''''' || public_schema || ''''' ''') into new_num_t1;
    execute('execute direct on (datanode1) ''select b.relfilenode from pg_class as a, pg_partition as b, pg_namespace as c where a.relname=''''' || t1_index1 || ''''' and b.relname=''''' || index_part_name1 || ''''' and a.oid=b.parentid and a.relnamespace=c.oid and c.nspname=''''' || public_schema || ''''' ''') into new_t1_index1;
    execute('execute direct on (datanode1) ''select b.relfilenode from pg_class as a, pg_partition as b, pg_namespace as c where a.relname=''''' || t1_index2 || ''''' and b.relname=''''' || index_part_name2 || ''''' and a.oid=b.parentid and a.relnamespace=c.oid and c.nspname=''''' || public_schema || ''''' ''') into new_t1_index2;
    execute('execute direct on (datanode1) ''select b.relfilenode from pg_class as a, pg_partition as b, pg_namespace as c where a.relname=''''' || t1_index3 || ''''' and b.relname=''''' || index_part_name1 || ''''' and a.oid=b.parentid and a.relnamespace=c.oid and c.nspname=''''' || public_schema || ''''' ''') into new_t1_index3;
    execute('execute direct on (datanode1) ''select b.relfilenode from pg_class as a, pg_partition as b, pg_namespace as c where a.relname=''''' || t2 || ''''' and b.relname=''''' || table_part_name || ''''' and a.oid=b.parentid and a.relnamespace=c.oid and c.nspname=''''' || tmp_schema || ''''' ''') into new_num_t2;
    execute('execute direct on (datanode1) ''select b.relfilenode from pg_class as a, pg_partition as b, pg_namespace as c where a.relname=''''' || t2_index1 || ''''' and b.relname=''''' || index_part_name1 || ''''' and a.oid=b.parentid and a.relnamespace=c.oid and c.nspname=''''' || tmp_schema || ''''' ''') into new_t2_index1;
    execute('execute direct on (datanode1) ''select b.relfilenode from pg_class as a, pg_partition as b, pg_namespace as c where a.relname=''''' || t2_index2 || ''''' and b.relname=''''' || index_part_name2 || ''''' and a.oid=b.parentid and a.relnamespace=c.oid and c.nspname=''''' || tmp_schema || ''''' ''') into new_t2_index2;
    execute('execute direct on (datanode1) ''select b.relfilenode from pg_class as a, pg_partition as b, pg_namespace as c where a.relname=''''' || t2_index3 || ''''' and b.relname=''''' || index_part_name1 || ''''' and a.oid=b.parentid and a.relnamespace=c.oid and c.nspname=''''' || tmp_schema || ''''' ''') into new_t2_index3;
    if (old_num_t1 = new_num_t2 and old_t1_index1 = new_t2_index1 and old_t1_index2 = new_t2_index2 and old_t1_index3 = new_t2_index3 and old_num_t2 = new_num_t1 and old_t2_index1 = new_t1_index1 and old_t2_index2 = new_t1_index2 and old_t2_index3 = new_t1_index3) then
    else
    end if;
    return;
end;
/


select switch_relfilenode_and_check_for_part_table('mpp_row_partition_table1', 'mpp_row_partition_table1_index1', 'mpp_row_partition_table1_index2', 'mpp_row_partition_table1_index3', 'mpp_row_partition_table2', 'mpp_row_partition_table1_index1', 'mpp_row_partition_table1_index2', 'mpp_row_partition_table1_index3', 'p1', 'p1_id_idx', 'p1_name_idx', 'public', 'redis_schema');
select * from mpp_row_partition_table1 order by id;
select * from redis_schema.mpp_row_partition_table2 order by id;

alter table mpp_col_partition_table1 add column nodeid int,add column partitionoid int,add column tupleblocknum bigint, add column tupleoffset int;
alter table mpp_col_partition_table1 drop column nodeid,drop column partitionoid,drop column tupleblocknum, drop column tupleoffset;

alter table mpp_col_partition_table2 drop column nodeid,drop column partitionoid,drop column tupleblocknum, drop column tupleoffset;

select gs_switch_relfilenode('mpp_col_partition_table1','mpp_col_partition_table2');
select * from mpp_col_partition_table1 order by id;
select * from mpp_col_partition_table2 order by id;

/* add test for swap attinfos during swap relfilenode */
-- no partition table;
create table row_t1(c1 int, c2 int);
create table row_t2(like row_t1 including storage INCLUDING RELOPTIONS including distribution, c3 int, c4 int);
insert into row_t1 values(1,2);
insert into row_t2 values(3,4,1,2);

alter table row_t2 drop column c3, drop column c4;

alter table row_t1 add column c3 int, add column c4 int;
alter table row_t1 drop column c3, drop column c4;

-- get original natts
select relname, relnatts from pg_class where relname in ('row_t1', 'row_t2') order by relname;
select gs_switch_relfilenode('row_t1','row_t2'); 

-- relnatts must be swap
select relname, relnatts from pg_class where relname in ('row_t1', 'row_t2') order by relname;
select * from row_t1 order by 1;
select * from row_t2 order by 1;

-- partition table;
CREATE TABLE row_partition_t1(id int, name varchar) partition by range(id) (partition p1 values less than (6),partition p2 values less than (10));
CREATE TABLE row_partition_t2(like row_partition_t1 including storage INCLUDING RELOPTIONS including distribution including partition,i int, j int);

insert into row_partition_t1 values(1, 'row_partition_t1');
insert into row_partition_t2 values(1, 'row_partition_t2', 1, 1);

alter table row_partition_t2 drop column i, drop column j;

alter table row_partition_t1 add column i int, add column j int;
alter table row_partition_t1 drop column i, drop column j;

-- get original the natts
select relname, relnatts from pg_class where relname in ('row_partition_t1', 'row_partition_t2') order by relname;
select gs_switch_relfilenode('row_partition_t1', 'row_partition_t2');
-- check whether the natts is changed.
select relname, relnatts from pg_class where relname in ('row_partition_t1', 'row_partition_t2') order by relname;

select * from row_partition_t1 order by id;
select * from row_partition_t2 order by id;


DROP TABLE mpp_row_table1;
DROP TABLE mpp_col_table1;
DROP TABLE mpp_row_partition_table1;
DROP TABLE mpp_col_partition_table1;

DROP TABLE redis_schema.mpp_row_table2;
DROP TABLE mpp_col_table2;
DROP TABLE redis_schema.mpp_row_partition_table2;
DROP TABLE mpp_col_partition_table2;

DROP TABLE row_partition_t1;
DROP TABLE row_partition_t2;
DROP TABLE row_t1;
DROP TABLE row_t2;
DROP PROCEDURE switch_relfilenode_and_check_for_nonpart_table;
DROP PROCEDURE switch_relfilenode_and_check_for_part_table;
DROP SCHEMA redis_schema cascade;
select DISTINCT node_name from pg_pooler_status order by 1;