schema_support_regression_test.sql

---------------------------- TEST 1 - Shard Creation ------------------------

CREATE SCHEMA tpch;
set search_path to tpch;

CREATE TABLE nation_append (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));
SELECT master_create_distributed_table('nation_append', 'n_nationkey', 'append');

\COPY nation_append FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|'

SELECT master_create_empty_shard('nation_append');
SELECT count(*) from nation_append;

CREATE TABLE nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('nation_hash', 4, 1);

CREATE SCHEMA tpch_2;
set search_path to public;


CREATE TABLE tpch_2.nation_append (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));
SELECT master_create_distributed_table('tpch_2.nation_append', 'n_nationkey', 'append');

\COPY tpch_2.nation_append FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|'

SELECT master_create_empty_shard('tpch_2.nation_append');
SELECT count(*) from nation_append;

CREATE TABLE tpch_2.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('tpch_2.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch_2.nation_hash', 4, 1);


---------------------------- TEST 2 - EXPLAIN ------------------------

SET search_path to tpch;
EXPLAIN SELECT * FROM nation_append;

SET search_path to public;
EXPLAIN SELECT * FROM tpch.nation_append;

---------------------------- TEST 3 - master_apply_delete_command() ------------------------

CREATE SCHEMA tpch;

CREATE TABLE tpch.nation_append (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));
SELECT master_create_distributed_table('tpch.nation_append', 'n_nationkey', 'append');

\COPY tpch.nation_append FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|'

 SELECT master_apply_delete_command('DELETE FROM tpch.nation_append') ;

\COPY tpch.nation_append FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|'

SET search_path TO tpch;
SELECT master_apply_delete_command('DELETE FROM nation_append') ;

SET search_path TO public;
 \COPY tpch.nation_append FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|'


SET search_path TO tpch;
SELECT master_apply_delete_command('DELETE FROM tpch.nation_append') ;


---------------------------- TEST 4 - worker_fetch_regular_table() ------------------------

CREATE SCHEMA tpch;

CREATE TABLE tpch.nation_append (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));
SELECT master_create_distributed_table('tpch.nation_append', 'n_nationkey', 'append');

\COPY tpch.nation_append FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|'


CREATE TABLE tpch.nation_append_2 (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));
SELECT master_create_distributed_table('tpch.nation_append_2', 'n_nationkey', 'append');

\COPY tpch.nation_append_2 FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|'


SELECT count(*) FROM tpch.nation_append t1, tpch.nation_append_2 t2 WHERE t1.n_name = t2.n_name;

SET search_path to tpch;
SELECT count(*) FROM nation_append t1, nation_append_2 t2 WHERE t1.n_name = t2.n_name;


---------------------------- TEST 5 - master_append_table_to_shard() ------------------------

CREATE SCHEMA tpch;

CREATE TABLE tpch.nation_append (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));
SELECT master_create_distributed_table('tpch.nation_append', 'n_nationkey', 'append');

SELECT master_create_empty_shard('tpch.nation_append');


-- CREATE TABLE on publid schema
CREATE TABLE public.nation_local
(
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

\COPY public.nation_local FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|'


SELECT master_append_table_to_shard(102008, 'public.nation_local', 'localhost', 5432);

set search_path TO tpch;

SELECT master_append_table_to_shard(102008, 'public.nation_local', 'localhost', 5432);


-- worker log
SELECT worker_append_table_to_shard ('nation_append_102008', 'nation_local', 'localhost', 5432)


--- Note that for master_append_table_to_shard() ADD TESTS WITH CREATING A LOCAL TABLE ON ANOTHER SCHEMA OTHER THAN PUBLIC AS WELL 

---------------------------- TEST 6 master_create_empty_shard() ------------------------

CREATE SCHEMA tpch;

CREATE TABLE tpch.nation_append (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));
SELECT master_create_distributed_table('tpch.nation_append', 'n_nationkey', 'append');

SELECT master_create_empty_shard('tpch.nation_append');

SET search_path to tpch;

SELECT master_create_empty_shard('nation_append');


---------------------------- TEST 7 INSERT/UPDATE/DELETE/ Router Queries ------------------------


CREATE SCHEMA tpch;

CREATE TABLE tpch.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('tpch.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch.nation_hash', 4, 1);

INSERT INTO tpch.nation_hash (n_nationkey) VALUES (26);



SELECT * FROM tpch.nation_hash WHERE n_nationkey = 25;


SET search_path to tpch;

INSERT INTO nation_hash (n_nationkey) VALUES (26);



---------------------------- TEST 8 DROP TABLE ------------------------
CREATE SCHEMA tpch;

CREATE TABLE tpch.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('tpch.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch.nation_hash', 4, 1);

DROP TABLE tpch.nation_hash;



CREATE TABLE tpch.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('tpch.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch.nation_hash', 4, 1);

SET search_path TO tpch;
DROP TABLE nation_hash;


---------------------------- TEST 8 DROP SCHEMA ------------------------

CREATE SCHEMA tpch;

CREATE TABLE tpch.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('tpch.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch.nation_hash', 4, 1);

DROP SCHEMA tpch;


CREATE SCHEMA tpch;
CREATE TABLE tpch.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('tpch.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch.nation_hash', 4, 1);

SET search_path TO tpch;

DROP SCHEMA tpch;


---------------------------- TEST 9 CREATE/DROP INDEX ------------------------

CREATE SCHEMA tpch;

CREATE TABLE tpch.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('tpch.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch.nation_hash', 4, 1);


SET search_path TO public;
CREATE INDEX i1 ON tpch.nation_hash(n_name);


SET search_path TO tpch;
CREATE INDEX i2 ON nation_hash(n_nationkey);


SET search_path TO public;
DROP INDEX tpch.i1;

---------------------------- TEST 10 COPY TO ------------------------

CREATE SCHEMA tpch;

CREATE TABLE tpch.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('tpch.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch.nation_hash', 4, 1);


\COPY tpch.nation_hash FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|';


SET search_path TO public;
COPY tpch.nation_hash TO STDOUT;


SET search_path TO tpch;
COPY nation_hash TO STDOUT;

---------------------------- TEST 11 CREATE TABLE AS ------------------------

CREATE SCHEMA tpch;

CREATE TABLE tpch.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('tpch.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch.nation_hash', 4, 1);

\COPY tpch.nation_hash FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|';

SET search_path TO public;
CREATE TABLE nation_copy_1 AS (SELECT * FROM tpch.nation_hash);

SET search_path TO tpch;
CREATE TABLE nation_copy_1 AS (SELECT * FROM nation_hash);


---------------------------- TEST 12 JOINs ------------------------


CREATE SCHEMA tpch_1;
CREATE SCHEMA tpch_2;


CREATE TABLE nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));



CREATE TABLE tpch_1.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

CREATE TABLE tpch_1.nation_hash_2 (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));


CREATE TABLE tpch_2.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));


SELECT master_create_distributed_table('public.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('public.nation_hash', 4, 1);
\COPY public.nation_hash FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|';


SELECT master_create_distributed_table('tpch_1.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch_1.nation_hash', 4, 1);
\COPY tpch_1.nation_hash FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|';

SELECT master_create_distributed_table('tpch_1.nation_hash_2', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch_1.nation_hash_2', 4, 1);
\COPY tpch_1.nation_hash_2 FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|';


SELECT master_create_distributed_table('tpch_2.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch_2.nation_hash', 4, 1);
\COPY tpch_2.nation_hash FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|';


-- fails
SET search_path TO public;
SELECT 
    count (*)
FROM
    tpch_1.nation_hash n1, tpch_2.nation_hash n2 
WHERE
    n1.n_nationkey = n2.n_nationkey;


-- fails
SET search_path TO tpch_1;
SELECT 
    count (*)
FROM
    tpch_1.nation_hash n1, tpch_2.nation_hash n2 
WHERE
    n1.n_nationkey = n2.n_nationkey;

SET search_path TO tpch_2;
SELECT 
    count (*)
FROM
    tpch_1.nation_hash n1, tpch_2.nation_hash n2 
WHERE
    n1.n_nationkey = n2.n_nationkey;


-- fails
SET search_path TO public;
SELECT 
    count (*)
FROM
    tpch_1.nation_hash n1, tpch_1.nation_hash_2 n2 
WHERE
    n1.n_nationkey = n2.n_nationkey;


-- fails
SET search_path TO tpch_1;
SELECT 
    count (*)
FROM
    nation_hash n1, nation_hash_2 n2 
WHERE
    n1.n_nationkey = n2.n_nationkey;


-- single repartition joins 

-- works
SET search_path TO public;
SELECT 
    count (*)
FROM
    tpch_1.nation_hash n1, tpch_2.nation_hash n2 
WHERE
    n1.n_nationkey = n2.n_regionkey;


-- works
SET search_path TO tpch_1;
SELECT 
    count (*)
FROM
    nation_hash n1, nation_hash_2 n2 
WHERE
    n1.n_nationkey = n2.n_regionkey;

-- works
SET search_path TO tpch_1;
SELECT 
    count (*)
FROM
    nation_hash n1, tpch_2.nation_hash n2 
WHERE
    n1.n_nationkey = n2.n_regionkey;

-- hash repartition joins 

-- works
SET search_path TO public;
SELECT 
    count (*)
FROM
    tpch_1.nation_hash n1, tpch_2.nation_hash n2 
WHERE
    n1.n_regionkey = n2.n_regionkey;


-- works
SET search_path TO tpch_1;
SELECT 
    count (*)
FROM
    nation_hash n1, nation_hash_2 n2 
WHERE
    n1.n_regionkey = n2.n_regionkey;

-- works
SET search_path TO tpch_1;
SELECT 
    count (*)
FROM
    nation_hash n1, tpch_2.nation_hash n2 
WHERE
    n1.n_regionkey = n2.n_regionkey;


---------------------------- TEST 13 master_copy_shard_placement() ------------------------


CREATE SCHEMA tpch_1;

CREATE TABLE tpch_1.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));


SELECT master_create_distributed_table('tpch_1.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch_1.nation_hash', 4, 2);
\COPY tpch_1.nation_hash FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|';

 SELECT * FROM pg_dist_shard_placement ;
 shardid | shardstate | shardlength | nodename  | nodeport 
---------+------------+-------------+-----------+----------
  102012 |          1 |           0 | localhost |     9700
  102012 |          1 |           0 | localhost |     9701
  102013 |          1 |           0 | localhost |     9701
  102013 |          1 |           0 | localhost |     9700
  102014 |          1 |           0 | localhost |     9700
  102014 |          1 |           0 | localhost |     9701
  102015 |          1 |           0 | localhost |     9701
  102015 |          1 |           0 | localhost |     9700
(8 rows)

UPDATE pg_dist_shard_placement SET shardstate = 3 WHERE shardid = 102012 and nodeport = 9701;
UPDATE 1


SET search_path TO public;
SELECT master_copy_shard_placement(102012, 'localhost', 9700, 'localhost', 9701);

 SELECT master_copy_shard_placement(102012, 'localhost', 9700, 'localhost', 9701);


SET search_path TO tpch_1;
SELECT master_copy_shard_placement(102012, 'localhost', 9700, 'localhost', 9701);

SELECT master_copy_shard_placement(102012, 'localhost', 9700, 'localhost', 9701);

---------------------------- TEST 14 Aggragation queries ------------------------
CREATE SCHEMA tpch_1;

SET search_path TO public;

CREATE TABLE tpch_1.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('tpch_1.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch_1.nation_hash', 4, 2);
\COPY tpch_1.nation_hash FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|';

SET search_path TO tpch_1 ;
SELECT max(n_regionkey) FROM nation_hash ;

set search_path to public;
SELECT max(n_regionkey) FROM tpch_1.nation_hash ;

SET search_path TO tpch_1 ;
SELECT array_agg(n_name) FROM nation_hash GROUP BY n_regionkey
        ORDER BY n_regionkey LIMIT 10;

SET search_path TO public ;
SELECT array_agg(n_name) FROM tpch_1.nation_hash GROUP BY n_regionkey
        ORDER BY n_regionkey LIMIT 10;

SET citus.count_distinct_error_rate TO 0.01;
SELECT COUNT (DISTINCT n_regionkey) FROM tpch_1.nation_hash;

SET search_path TO tpch_1 ;
SELECT COUNT (DISTINCT n_regionkey) FROM nation_hash;


---------------------------- TEST 15 -- Create UDFs ------------------------


CREATE SCHEMA tpch_1;

SET search_path TO public;

CREATE TABLE tpch_1.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('tpch_1.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch_1.nation_hash', 4, 2);
\COPY tpch_1.nation_hash FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|';


CREATE OR REPLACE FUNCTION dummyFunction(theValue integer)
    RETURNS text AS
$$
DECLARE
    strresult text;
BEGIN
    RETURN theValue * 3 / 2 + 1;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;


-- works fine
SET search_path TO public;
SELECT dummyFunction(n_nationkey) FROM tpch_1.nation_hash  GROUP BY 1;

-- works fine
SET search_path TO tpch_1;
SELECT dummyFunction(n_nationkey) FROM nation_hash  GROUP BY 1;


-- do this on both the master and workers
SET search_path TO tpch_1;
CREATE OR REPLACE FUNCTION dummyFunction2(theValue integer)
    RETURNS text AS
$$
DECLARE
    strresult text;
BEGIN
    RETURN theValue * 3 / 2 + 1;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;

-- works fine
SET search_path TO public;
SELECT tpch_1.dummyFunction2(n_nationkey) FROM tpch_1.nation_hash  GROUP BY 1;

-- works error
SET search_path TO tpch_1;
SELECT dummyFunction2(n_nationkey) FROM nation_hash  GROUP BY 1;

-- worker log
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
STATEMENT:  COPY (SELECT dummyfunction2(n_nationkey) AS dummyfunction2 FROM tpch_1.nation_hash_102023 nation_hash WHERE true GROUP BY (dummyfunction2(n_nationkey))) TO STDOUT


---------------------------- TEST 15 -- Create FDWs on different schema ------------------------
CREATE SCHEMA fdw_test;

SET search_path TO public;
CREATE EXTENSION cstore_fdw;

-- create server object
CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;

-- create foreign table
CREATE FOREIGN TABLE fdw_test.customer_reviews
(
    customer_id TEXT,
    review_date DATE,
    review_rating INTEGER,
    review_votes INTEGER,
    review_helpful_votes INTEGER,
    product_id CHAR(10),
    product_title TEXT,
    product_sales_rank BIGINT,
    product_group TEXT,
    product_category TEXT,
    product_subcategory TEXT,
    similar_product_ids CHAR(10)[]
)
SERVER cstore_server
OPTIONS(compression 'pglz');

-- Make regular PostgreSQL table distributed
SELECT master_create_distributed_table('fdw_test.customer_reviews', 'review_date', 'append');

\COPY fdw_test.customer_reviews FROM '/tmp/customer_reviews_1999.csv' WITH CSV;


-- now create the table on the fdw_test schema
SET search_path TO fdw_test;

-- create foreign table
CREATE FOREIGN TABLE customer_reviews_2
(
    customer_id TEXT,
    review_date DATE,
    review_rating INTEGER,
    review_votes INTEGER,
    review_helpful_votes INTEGER,
    product_id CHAR(10),
    product_title TEXT,
    product_sales_rank BIGINT,
    product_group TEXT,
    product_category TEXT,
    product_subcategory TEXT,
    similar_product_ids CHAR(10)[]
)
SERVER cstore_server
OPTIONS(compression 'pglz');

-- Make regular PostgreSQL table distributed
SELECT master_create_distributed_table('customer_reviews_2', 'review_date', 'append');

\COPY customer_reviews_2 FROM '/tmp/customer_reviews_1999.csv' WITH CSV;

-- worker log
 SELECT worker_apply_shard_ddl_command (102010, 'CREATE FOREIGN TABLE customer_reviews_2 (customer_id text, review_date date, review_rating integer, review_votes integer, review_helpful_votes integer, product_id character(10), product_title text, product_sales_rank bigint, product_group text, product_category text, product_subcategory text, similar_product_ids character(10)[]) SERVER cstore_server OPTIONS (compression ''pglz'')')


 -- Once the above steps work as expected, try the following queries

 SELECT 
    product_category, 
    avg(review_rating) as avg_rating 
FROM 
    customer_reviews 
GROUP BY 
    product_category
ORDER BY 
    avg_rating DESC
LIMIT 10;



---------------------------- TEST 16 -- Composite types on different schemas ------------------------

CREATE SCHEMA tpch_1;

SET search_path TO public;

CREATE TYPE tpch_1.new_composite_type as (key1 text, key2 text);


CREATE TABLE tpch_1.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152),
    test_col tpch_1.new_composite_type
    );

SELECT master_create_distributed_table('tpch_1.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch_1.nation_hash', 4, 2);
    
\COPY tpch_1.nation_hash FROM STDIN WITH CSV
1,'name',1,'comment_1',"(a,a)"
2,'name',2,'comment_2',"(a,b)"
3,'name',3,'comment_3',"(a,c)"
4,'name',4,'comment_4',"(a,d)"
5,'name',5,'comment_5',"(a,e)"

-- works fine
SELECT * FROM tpch_1.nation_hash  WHERE test_col = '(a,a)'::tpch_1.new_composite_type;
 n_nationkey |          n_name           | n_regionkey |  n_comment  | test_col 
-------------+---------------------------+-------------+-------------+----------
           1 | 'name'                    |           1 | 'comment_1' | (a,a)
           1 | 'name'                    |           1 | 'comment_1' | (a,a)
           1 | 'name'                    |           1 | 'comment_1' | (a,a)
(3 rows)



SET search_path TO tpch_1;
\COPY nation_hash FROM STDIN WITH CSV
1,'name',1,'comment_1',"(a,a)"
2,'name',2,'comment_2',"(a,b)"
3,'name',3,'comment_3',"(a,c)"
4,'name',4,'comment_4',"(a,d)"
5,'name',5,'comment_5',"(a,e)"

-- oops cannot add the type info
SELECT * FROM nation_hash WHERE test_col = '(a,a)'::new_composite_type;



---------------------------- TEST 17 -- Prepared statements  ------------------------


CREATE SCHEMA tpch_1;

CREATE TABLE tpch_1.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('tpch_1.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch_1.nation_hash', 4, 2);
\COPY tpch_1.nation_hash FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|';

SET search_path TO public;
PREPARE preparedStatementCreatedOnPublic AS SELECT * FROM tpch_1.nation_hash;

SET search_path TO tpch_1;
PREPARE preparedStatementCreatedOnTPCH1 AS SELECT * FROM nation_hash;

SET search_path TO public;
EXECUTE preparedStatementCreatedOnPublic;
EXECUTE preparedStatementCreatedOnTPCH1;

SET search_path TO tpch_1;
EXECUTE preparedStatementCreatedOnPublic;
EXECUTE preparedStatementCreatedOnTPCH1;

---------------------------- TEST 18 -- Create extension Citus on a different schema  ------------------------

\c - - - 5432
DROP EXTENSION citus;
CREATE SCHEMA test_schema;
CREATE EXTENSION citus;

\c - - - 9700
DROP EXTENSION citus;
CREATE SCHEMA test_schema;
CREATE EXTENSION citus;

\c - - - 9701
DROP EXTENSION citus;
CREATE SCHEMA test_schema;
CREATE EXTENSION citus;

\c - - - 5432
SET search_path TO public;

CREATE TABLE test_schema.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('test_schema.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('test_schema.nation_hash', 4, 2);
\COPY test_schema.nation_hash FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|';



---------------------------- TEST 19 -- Shard Rebalancer ------------------------

CREATE SCHEMA tpch_1;

CREATE TABLE tpch_1.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));


SELECT master_create_distributed_table('tpch_1.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch_1.nation_hash', 4, 1);
\COPY tpch_1.nation_hash FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/nation.data' with delimiter '|';

\c - - - 5432
CREATE EXTENSION shard_rebalancer;

\c - - - 9700
CREATE EXTENSION shard_rebalancer;

\c - - - 9701
CREATE EXTENSION shard_rebalancer;

\c - - - 5432


--------------- TEST 19.1 on public namespace -------------------
-- errors
SET search_path TO public;
SELECT replicate_table_shards('tpch_1.nation_hash');

-- worker log
    BEGIN
    EXECUTE 'DROP TABLE IF EXISTS nation_hash_102011';
    EXECUTE 'SELECT worker_fetch_regular_table  (''nation_hash_102011'', 0, ''{localhost}'', ''{9701}'')';
    END$$;


--------------- TEST 19.2 on tpch namespace -------------------
SET search_path TO tpch_1;

SELECT public.replicate_table_shards('nation_hash');

---------------------------- TEST 20 - Cursors ------------------------


CREATE SCHEMA tpch_1;

SET search_path TO public;

CREATE TABLE tpch_1.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152) 

);

SELECT master_create_distributed_table('tpch_1.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch_1.nation_hash', 4, 2);
    
\COPY tpch_1.nation_hash FROM STDIN WITH CSV
1,'name',1,'comment_1'
2,'name',2,'comment_2'
3,'name',3,'comment_3'
4,'name',4,'comment_4'
5,'name',5,'comment_5'

-- try on real-time executor
SET search_path TO public;
BEGIN;
DECLARE test_cursor CURSOR FOR 
    SELECT *
        FROM tpch_1.nation_hash;
        FETCH test_cursor;
FETCH test_cursor;
END;

SET search_path TO tpch_1;
BEGIN;
DECLARE test_cursor CURSOR FOR 
    SELECT *
        FROM nation_hash;
        FETCH test_cursor;
FETCH test_cursor;
END;

-- try on router executor
SET search_path TO public;
BEGIN;
DECLARE test_cursor CURSOR FOR 
    SELECT *
        FROM tpch_1.nation_hash
        WHERE n_nationkey = 1;
        FETCH test_cursor;
FETCH test_cursor;
END;

SET search_path TO tpch_1;
BEGIN;
DECLARE test_cursor CURSOR FOR 
    SELECT *
        FROM nation_hash
        WHERE n_nationkey = 1;
        FETCH test_cursor;
FETCH test_cursor;
END;


---------------------------- TEST 21 - Subqueries ------------------------

-- Tests including single table repartition and subquery pushdown

CREATE SCHEMA tpch_1;


CREATE TABLE tpch_1.lineitem (
    l_orderkey bigint not null,
    l_partkey integer not null,
    l_suppkey integer not null,
    l_linenumber integer not null,
    l_quantity decimal(15, 2) not null,
    l_extendedprice decimal(15, 2) not null,
    l_discount decimal(15, 2) not null,
    l_tax decimal(15, 2) not null,
    l_returnflag char(1) not null,
    l_linestatus char(1) not null,
    l_shipdate date not null,
    l_commitdate date not null,
    l_receiptdate date not null,
    l_shipinstruct char(25) not null,
    l_shipmode char(10) not null,
    l_comment varchar(44) not null,
     PRIMARY KEY(l_orderkey, l_linenumber) );

SELECT master_create_distributed_table('tpch_1.lineitem', 'l_orderkey', 'hash');
SELECT master_create_worker_shards('tpch_1.lineitem', 16, 1);


COPY tpch_1.lineitem FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/lineitem.1.data' with delimiter '|';
COPY tpch_1.lineitem FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/lineitem.2.data' with delimiter '|';

SET citus.task_executor_type TO 'task-tracker';


SET search_path TO public;
select
    total,
    avg(avg_count) as total_avg_count
from
    (select
        number_sum,
        count(*) as total,
        avg(total_count) avg_count
    from
        (select
            l_suppkey,
            sum(l_linenumber) as number_sum,
            count(*) as total_count
        from
            tpch_1.lineitem
        where
            l_partkey > 100 and
            l_quantity > 2 and
            l_orderkey < 10000
        group by
            l_suppkey) as distributed_table
    where
        number_sum >= 10
    group by
        number_sum) as distributed_table_2
group by
    total
order by
    total;


SET search_path TO tpch_1;
select
    total,
    avg(avg_count) as total_avg_count
from
    (select
        number_sum,
        count(*) as total,
        avg(total_count) avg_count
    from
        (select
            l_suppkey,
            sum(l_linenumber) as number_sum,
            count(*) as total_count
        from
            lineitem
        where
            l_partkey > 100 and
            l_quantity > 2 and
            l_orderkey < 10000
        group by
            l_suppkey) as distributed_table
    where
        number_sum >= 10
    group by
        number_sum) as distributed_table_2
group by
    total
order by
    total;


-- now test with subquery pushdown

CREATE TABLE tpch_1.orders (
    o_orderkey bigint not null,
    o_custkey integer not null,
    o_orderstatus char(1) not null,
    o_totalprice decimal(15,2) not null,
    o_orderdate date not null,
    o_orderpriority char(15) not null,
    o_clerk char(15) not null,
    o_shippriority integer not null,
    o_comment varchar(79) not null,
    PRIMARY KEY(o_orderkey) );

SELECT master_create_distributed_table('tpch_1.orders', 'o_orderkey', 'hash');
SELECT master_create_worker_shards('tpch_1.orders', 16, 1);


COPY tpch_1.orders FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/orders.1.data' with delimiter '|';
COPY tpch_1.orders FROM '/Users/onderkalaci/Documents/citus_code/citus/src/test/regress/data/orders.2.data' with delimiter '|';


SET citus.subquery TO on;

SET search_path TO public;
SELECT
    avg(unit_price)
FROM
    (SELECT
        l_orderkey,
        avg(o_totalprice / l_quantity) AS unit_price
    FROM
        tpch_1.lineitem,
        tpch_1.orders
    WHERE
        l_orderkey = o_orderkey
    GROUP BY
        l_orderkey) AS unit_prices
WHERE
    unit_price > 1000 AND
    unit_price < 10000;

SET search_path TO tpch_1;
SELECT
    avg(unit_price)
FROM
    (SELECT
        l_orderkey,
        avg(o_totalprice / l_quantity) AS unit_price
    FROM
        lineitem,
        orders
    WHERE
        l_orderkey = o_orderkey
    GROUP BY
        l_orderkey) AS unit_prices
WHERE
    unit_price > 1000 AND
    unit_price < 10000;



---------------------------- TEST 22 -  ALTER TABLE ------------------------

CREATE SCHEMA tpch;

CREATE TABLE tpch.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('tpch.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch.nation_hash', 4, 1);

\COPY tpch.nation_hash FROM STDIN WITH CSV
1,'name',1,'comment_1'
2,'name',2,'comment_2'
3,'name',3,'comment_3'
4,'name',4,'comment_4'
5,'name',5,'comment_5'


SET search_path TO public;
ALTER TABLE tpch.nation_hash ADD COLUMN tmp_col INT;

ALTER TABLE tpch.nation_hash  DROP COLUMN IF EXISTS non_existent_column;

SET search_path TO tpch;
ALTER TABLE nation_hash ADD COLUMN tmp_col_2 INT;

ALTER TABLE nation_hash  DROP COLUMN IF EXISTS non_existent_column;
ALTER TABLE nation_hash ALTER COLUMN n_comment SET DEFAULT 'comment';

---------------------------- TEST 23 -  master_modify_multiple_shards UDF ------------------------

CREATE SCHEMA tpch;

CREATE TABLE tpch.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('tpch.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch.nation_hash', 4, 1);

\COPY tpch.nation_hash FROM STDIN WITH CSV
1,'name',1,'comment_1'
2,'name',2,'comment_2'
3,'name',3,'comment_3'
4,'name',4,'comment_4'
5,'name',5,'comment_5'

SET search_path TO public;
SELECT master_modify_multiple_shards('UPDATE tpch.nation_hash SET n_regionkey = n_regionkey + 1');

-- same error
SET search_path TO tpch;
SELECT master_modify_multiple_shards('UPDATE nation_hash SET n_regionkey = n_regionkey + 1');


---------------------------- TEST 24 - ALTER TABLE SET SCHEMA ------------------------

CREATE SCHEMA tpch;

CREATE TABLE nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('nation_hash', 4, 1);

\COPY nation_hash FROM STDIN WITH CSV
1,'name',1,'comment_1'
2,'name',2,'comment_2'
3,'name',3,'comment_3'
4,'name',4,'comment_4'
5,'name',5,'comment_5'

SELECT * FROM nation_hash;

ALTER TABLE nation_hash SET SCHEMA tpch;

SELECT * FROM tpch.nation_hash;



---------------------------- TEST 25 - Schema Authorization ------------------------

psql postgres 

CREATE USER test_user;
\c - - - 9700
CREATE USER test_user;
\c - - - 9701
CREATE USER test_user;


-- now connect to the master again
psql postgres -p 5432

CREATE SCHEMA tpch AUTHORIZATION test_user;

psql postgres -p 5432 -U test_user

CREATE TABLE tpch.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));


SELECT master_create_distributed_table('tpch.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch.nation_hash', 4, 1);
WARNING:  could not receive query results from localhost:9700
DETAIL:  Client error: permission denied for database postgres

-- worker log
ERROR:  permission denied for database postgres
STATEMENT:  SELECT worker_apply_shard_ddl_command (102008, 'CREATE SCHEMA IF NOT EXISTS tpch')

CREATE_SCHEMA_COMMAND => master_protocol.h

---------------------------- TEST 26 - master_drop_all_shards ------------------------

CREATE SCHEMA tpch;

CREATE TABLE tpch.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('tpch.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch.nation_hash', 4, 1);

\COPY tpch.nation_hash FROM STDIN WITH CSV
1,'name',1,'comment_1'
2,'name',2,'comment_2'
3,'name',3,'comment_3'
4,'name',4,'comment_4'
5,'name',5,'comment_5'


SET search_path TO public;
SELECT master_drop_all_shards('tpch.nation_hash'::regclass, 'tpch', 'nation_hash');


-- same error
SET search_path TO tpch;
SELECT master_drop_all_shards('tpch.nation_hash'::regclass, 'tpch', 'nation_hash');


---------------------------- TEST 26 - master_get_table_metadata ------------------------


CREATE SCHEMA tpch;

CREATE TABLE tpch.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('tpch.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch.nation_hash', 4, 1);

\COPY tpch.nation_hash FROM STDIN WITH CSV
1,'name',1,'comment_1'
2,'name',2,'comment_2'
3,'name',3,'comment_3'
4,'name',4,'comment_4'
5,'name',5,'comment_5'


SET search_path TO public;
SELECT * FROM master_get_table_metadata('tpch.nation_hash');


SET search_path TO tpch;
SELECT * FROM master_get_table_metadata('nation_hash');


---------------------------- TEST 27 - master_get_table_ddl_events ------------------------


CREATE SCHEMA tpch;

CREATE TABLE tpch.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('tpch.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch.nation_hash', 4, 1);

\COPY tpch.nation_hash FROM STDIN WITH CSV
1,'name',1,'comment_1'
2,'name',2,'comment_2'
3,'name',3,'comment_3'
4,'name',4,'comment_4'
5,'name',5,'comment_5'


SET search_path TO public;
SELECT * FROM master_get_table_ddl_events('tpch.nation_hash');


SET search_path TO tpch;
SELECT * FROM master_get_table_ddl_events('nation_hash');


---------------------------- TEST 27 - master_get_table_ddl_events ------------------------
CREATE SCHEMA tpch;

CREATE TABLE tpch.nation_hash (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,
    n_comment varchar(152));

SELECT master_create_distributed_table('tpch.nation_hash', 'n_nationkey', 'hash');
SELECT master_create_worker_shards('tpch.nation_hash', 4, 1);

\COPY tpch.nation_hash FROM STDIN WITH CSV
1,'name',1,'comment_1'
2,'name',2,'comment_2'
3,'name',3,'comment_3'
4,'name',4,'comment_4'
5,'name',5,'comment_5'

SET search_path TO public;
SELECT * FROM master_update_shard_statistics(102008);


SET search_path TO tpch;
SELECT * FROM master_update_shard_statistics(102008);