cluster_drop_table.sql

SELECT cluster_add_node('localhost', 9700);
SELECT cluster_activate_node('localhost', 9700);

SELECT cluster_add_node('localhost', 9701);
SELECT cluster_activate_node('localhost', 9701);




-- test 1, very basic test
CREATE TABLE t1 (a int);
SELECT cluster_create_distributed_table('t1', 'a', 'hash');
SELECT cluster_create_shards('t1', 16);
DROP TABLE t1;

-- test2, very basic test 2
BEGIN;
CREATE TABLE t1 (a int);
SELECT cluster_create_distributed_table('t1', 'a', 'hash');
SELECT cluster_create_shards('t1', 128);
DROP TABLE t1;
END;


-- test3, drop clustered table and then cancel via CTRL_C
BEGIN;
CREATE TABLE t1 (a int);
SELECT cluster_create_distributed_table('t1', 'a', 'hash');
SELECT cluster_create_shards('t1', 16);
COMMIT;

BEGIN;
DROP TABLE t1;

press CTRL_C


-- test4, drop clustered table and then rollback
BEGIN;
CREATE TABLE t1 (a int);
SELECT cluster_create_distributed_table('t1', 'a', 'hash');
SELECT cluster_create_shards('t1', 16);
COMMIT;

BEGIN;
DROP TABLE t1;
ROLLBACK;

-- test5, drop multiple clustered tables
BEGIN;
CREATE TABLE t1 (a int);
SELECT cluster_create_distributed_table('t1', 'a', 'hash');
SELECT cluster_create_shards('t1', 16);

CREATE TABLE t2 (a int);
SELECT cluster_create_distributed_table('t2', 'a', 'hash');
SELECT cluster_create_shards('t2', 16);
COMMIT;

BEGIN;
DROP TABLE t1, t2;
ROLLBACK;


-- test6, drop both regular, distributed and clustered tables 
BEGIN;
CREATE TABLE t1 (a int);
SELECT cluster_create_distributed_table('t1', 'a', 'hash');
SELECT cluster_create_shards('t1', 16);

CREATE TABLE t2 (a int);
SELECT master_create_distributed_table('t2', 'a', 'hash');
SELECT master_create_worker_shards('t2', 16, 1);
COMMIT;

CREATE TABLE t3 (a int);


BEGIN;
DROP TABLE t1, t2, t3;
END;

-- test7, test6 with rollback
BEGIN;
CREATE TABLE t1 (a int);
SELECT cluster_create_distributed_table('t1', 'a', 'hash');
SELECT cluster_create_shards('t1', 16);

CREATE TABLE t2 (a int);
SELECT master_create_distributed_table('t2', 'a', 'hash');
SELECT master_create_worker_shards('t2', 16, 1);
CREATE TABLE t3 (a int);

COMMIT;

BEGIN;
DROP TABLE t1, t2, t3;
ROLLBACK;


-- test8, basic test with master_apply_delete_command
BEGIN;
CREATE TABLE t1 (a int);
SELECT  master_create_distributed_table('t1', 'a', 'append');
COPY t1 FROM '/tmp/vals_1';
COPY t1 FROM '/tmp/vals_2';
COPY t1 FROM '/tmp/vals_3';
COPY t1 FROM '/tmp/vals_4';
END;

BEGIN;
SELECT master_apply_delete_command('DELETE FROM t1 WHERE a > 2');
END;


-- test9, test8 with rollback
BEGIN;
CREATE TABLE t1 (a int);
SELECT  master_create_distributed_table('t1', 'a', 'append');

COPY t1 FROM '/tmp/vals_1';
COPY t1 FROM '/tmp/vals_2';
COPY t1 FROM '/tmp/vals_3';
COPY t1 FROM '/tmp/vals_4';
END;

BEGIN;

SELECT master_apply_delete_command('DELETE FROM t1 WHERE a > 2');
ROLLBACK;


-- test 10, multiple master_apply_delete_commands
BEGIN;
CREATE TABLE t1 (a int);
SELECT  master_create_distributed_table('t1', 'a', 'hash');
SELECT master_create_worker_shards('t1', 4, 2);

CREATE TABLE t2 (a int);
SELECT  master_create_distributed_table('t2', 'a', 'hash');
SELECT master_create_worker_shards('t2', 4, 2);
COMMIT;

BEGIN;
SELECT master_apply_delete_command('DELETE FROM t1');
SELECT master_apply_delete_command('DELETE FROM t2');
END;


-- test 11, test 10 with rollback
BEGIN;
CREATE TABLE t1 (a int);
SELECT  master_create_distributed_table('t1', 'a', 'hash');
SELECT master_create_worker_shards('t1', 4, 2);

CREATE TABLE t2 (a int);
SELECT  master_create_distributed_table('t2', 'a', 'hash');
SELECT master_create_worker_shards('t2', 4, 2);
COMMIT;

BEGIN;
SELECT master_apply_delete_command('DELETE FROM t1');
SELECT master_apply_delete_command('DELETE FROM t2');
ROLLBACK;


-- test 12, drop with master_apply_delete_command

BEGIN;
CREATE TABLE t1 (a int);
SELECT  master_create_distributed_table('t1', 'a', 'hash');
SELECT master_create_worker_shards('t1', 4, 2);
COMMIT;

BEGIN;
SELECT master_apply_delete_command('DELETE FROM t1');
DROP TABLE t1;
END;



-- test 13, with rollback
BEGIN;
CREATE TABLE t1 (a int);
SELECT  master_create_distributed_table('t1', 'a', 'hash');
SELECT master_create_worker_shards('t1', 4, 2);
COMMIT;

BEGIN;
SELECT master_apply_delete_command('DELETE FROM t1');
DROP TABLE t1;
ROLLBACK;


-- test 14, do some random tests when one of the workers down