schema_support.sql

-- On master and worker nodes:
CREATE SCHEMA test_schema;
set search_path to test_schema;


-- On master node:
CREATE TABLE customer_reviews_hash
(
    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)[]
);

SELECT master_create_distributed_table('customer_reviews_hash', 'customer_id', 'hash');
SELECT master_create_worker_shards('customer_reviews_hash', 4, 1);


CREATE TABLE customer_reviews_append
(
    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)[]
);

SELECT master_create_distributed_table('customer_reviews_append', 'customer_id', 'append');
SELECT * FROM customer_reviews_append;



COPY customer_reviews_append FROM '/tmp/customer_reviews_1999.csv' WITH CSV;
EXPLAIN SELECT * FROM customer_reviews_append;

 
* COPY FROM  
* COPY TO -- Updated [267](https://github.com/citusdata/citus/issues/267)
* EXPLAIN -- Works fine
* SELECT 
   * real-time, task-tracker, router executors
   * router planner, multi planner
   * Various query types:
      * single-shard, multi-shard, task-tracker, repartition subquery, subquery pushdown (w/wthout UDFs defined on other schemas)
      * Aggragates, count distrint approximations, array Aggragates
      * Newly defined UDFs on the same schema / on cross schemas
      * JOINs on the same schema / on cross schemas
      * Foreign data wrappers such as cstore_fdw and file_fdw
      * Composite types on the same schema / on cross schemas
* INSERT / UPSERT / UPDATE / DELETE
* DROP TABLE
* ALTER TABLE 
* append_table_to_shard
* create_empty_shard -- Updated [267](https://github.com/citusdata/citus/issues/267)
* master_create_distributed_table for append
* master_create_distributed_table for hash + master_create_worker_shards -- Updated [267](https://github.com/citusdata/citus/issues/267)
* master_apply_delete_command -- Udated [73](https://github.com/citusdata/citus/issues/73)
* worker_fetch_regular_table -- Updated [504](https://github.com/citusdata/citus/issues/504)
* DROP EXTENSION
* DROP schema
* CREATE / DROP Indexes
* Prepared Statements
* master_copy_shard_placement
* shard_rebalancer
* CREATE TABLE AS (SELECT from schema.dist_table)
* CURSORS
* Multi user patch - Accesses to the users


-- Tests

---------------------------- 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') ;
 master_apply_delete_command 
-----------------------------
                           1

\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') ;
master_apply_delete_command 
-----------------------------
                           1

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') ;
 master_apply_delete_command 
-----------------------------
                           1

---------------------------- 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;
WARNING:  could not receive query results from localhost:9700
DETAIL:  Client error: could not fetch relation: "nation_append_2_102015"
WARNING:  could not receive query results from localhost:9701
DETAIL:  Client error: could not fetch relation: "nation_append_2_102015"
WARNING:  could not receive query results from localhost:9701
DETAIL:  Client error: could not fetch relation: "nation_append_2_102015"
WARNING:  could not receive query results from localhost:9700
DETAIL:  Client error: could not fetch relation: "nation_append_2_102015"
WARNING:  could not receive query results from localhost:9700
DETAIL:  Client error: could not fetch relation: "nation_append_2_102015"
WARNING:  could not receive query results from localhost:9701
DETAIL:  Client error: could not fetch relation: "nation_append_2_102015"
ERROR:  failed to execute job 12
DETAIL:  Failure due to failed task 6

-- worker log
STATEMENT:  SELECT worker_fetch_regular_table  ('nation_append_2_102015', 8192, '{localhost,localhost}', '{9700,9701}')


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');
 master_create_empty_shard 
---------------------------
                    102008
(1 row)

-- 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);
WARNING:  could not receive query results from localhost:9701
DETAIL:  Client error: could not copy table "public.nation_local" from "localhost:5432"
WARNING:  could not receive query results from localhost:9700
DETAIL:  Client error: could not copy table "public.nation_local" from "localhost:5432"
ERROR:  could not append table to any shard placement

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)

-- This creates the following COPY command on the worker:
\COPY "tpch.nation_append_102008" FROM 'base/pgsql_job_cache/table_102008';
ERROR:  relation "tpch.nation_append_102008" does not exist

-- However, it should be the following
\COPY tpch."nation_append_102008" FROM 'base/pgsql_job_cache/table_102008';


--- 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);
WARNING:  Bad result from localhost:9700
DETAIL:  Remote message: relation "nation_hash_102008" does not exist
ERROR:  could not modify any active placements


SELECT * FROM tpch.nation_hash WHERE n_nationkey = 25;
WARNING:  Bad result from localhost:9700
DETAIL:  Remote message: relation "nation_hash_102008" does not exist
ERROR:  could not receive query results

SET search_path to tpch;

INSERT INTO nation_hash (n_nationkey) VALUES (26);
WARNING:  Bad result from localhost:9700
DETAIL:  Remote message: relation "nation_hash_102008" does not exist
ERROR:  could not modify any active placements


---------------------------- 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);

postgres=# SELECT master_copy_shard_placement(102012, 'localhost', 9700, 'localhost', 9701);
WARNING:  could not receive query results from localhost:9701
DETAIL:  Client error: relation "nation_hash_102012" already exists
WARNING:  could not create shard on "localhost:9701"
ERROR:  could only create 0 of 1 of required shard replicas

-- Worker log
LOG:  duration: 1.439 ms  statement: SELECT worker_apply_shard_ddl_command (102012, 'DROP TABLE IF EXISTS nation_hash')
LOG:  duration: 1.244 ms  statement: SELECT worker_apply_shard_ddl_command (102012, 'CREATE SCHEMA IF NOT EXISTS tpch_1')
ERROR:  relation "nation_hash_102012" already exists
STATEMENT:  SELECT worker_apply_shard_ddl_command (102012, 'CREATE TABLE tpch_1.nation_hash (n_nationkey integer NOT NULL, n_name character(25) NOT NULL, n_regionkey integer NOT NULL, n_comment character varying(152))')


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);
WARNING:  could not receive query results from localhost:9701
DETAIL:  Client error: relation "nation_hash_102012" already exists
WARNING:  could not create shard on "localhost:9701"
ERROR:  could only create 0 of 1 of required shard replicas

-- Worker log
LOG:  duration: 1.439 ms  statement: SELECT worker_apply_shard_ddl_command (102012, 'DROP TABLE IF EXISTS nation_hash')
LOG:  duration: 1.244 ms  statement: SELECT worker_apply_shard_ddl_command (102012, 'CREATE SCHEMA IF NOT EXISTS tpch_1')
ERROR:  relation "nation_hash_102012" already exists
STATEMENT:  SELECT worker_apply_shard_ddl_command (102012, 'CREATE TABLE tpch_1.nation_hash (n_nationkey integer NOT NULL, n_name character(25) NOT NULL, n_regionkey integer NOT NULL, n_comment character varying(152))')

---------------------------- 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;

ERROR:  function "hll_union_agg" does not exist

---------------------------- 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;
ERROR:  cannot copy to foreign table "customer_reviews_102008"
DETAIL:  (null)


-- Worker log:

LOG:  duration: 5.084 ms  statement: SELECT worker_apply_shard_ddl_command (102009, 'CREATE EXTENSION IF NOT EXISTS cstore_fdw WITH SCHEMA public')
LOG:  duration: 5.122 ms  statement: SELECT worker_apply_shard_ddl_command (102009, 'CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw')
LOG:  duration: 2.032 ms  statement: SELECT worker_apply_shard_ddl_command (102009, 'CREATE SCHEMA IF NOT EXISTS fdw_test')
LOG:  duration: 14.941 ms  statement: SELECT worker_apply_shard_ddl_command (102009, '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 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'')')
LOG:  duration: 0.226 ms  statement: BEGIN
ERROR:  cannot copy to foreign table "customer_reviews_102009"
STATEMENT:  COPY fdw_test.customer_reviews_102009 FROM STDIN WITH (FORMAT BINARY)
LOG:  duration: 0.120 ms  statement: ROLLBACK



-- 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;
WARNING:  Bad result from localhost:9701
DETAIL:  Remote message: relation "fdw_test.customer_reviews_2_102010" does not exist
WARNING:  Bad result from localhost:9700
DETAIL:  Remote message: relation "fdw_test.customer_reviews_2_102010" does not exist
ERROR:  could not find any active placements


-- 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;
WARNING:  could not receive query results from localhost:9700
DETAIL:  Client error: type "new_composite_type" does not exist

ERROR:  type "new_composite_type" does not exist at character 138
STATEMENT:  COPY (SELECT n_nationkey, n_name, n_regionkey, n_comment, test_col FROM tpch_1.nation_hash_102009 nation_hash WHERE (test_col = '(a,a)'::new_composite_type)) TO STDOUT