multi_remaining_comprehensive_schema.sql

---------------------------- 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 19 -- Shard Rebalancer ------------------------

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

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

SELECT public.replicate_table_shards('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 28 -  ------------------------

-- Connect to the master
psql postgres -p 5432 
CREATE USER test_user;
CREATE SCHEMA tpch AUTHORIZATION test_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA tpch GRANT ALL ON TABLES TO test_user;


-- Connect to the worker 1
psql postgres -p 9700 
CREATE USER test_user;
CREATE SCHEMA tpch AUTHORIZATION test_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA tpch GRANT ALL ON TABLES TO test_user;

-- also give create grant to allow  SELECT worker_apply_shard_ddl_command (102008, 'CREATE SCHEMA IF NOT EXISTS tpch')
GRANT CREATE on DATABASE postgres TO test_user;

-- Connect to the worker 2
psql postgres -p 9701
CREATE USER test_user;
CREATE SCHEMA tpch AUTHORIZATION test_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA tpch GRANT ALL ON TABLES TO test_user;

-- also give create grant to allow  SELECT worker_apply_shard_ddl_command (102008, 'CREATE SCHEMA IF NOT EXISTS tpch')
GRANT CREATE on DATABASE postgres TO test_user;




-- now connect to the master again with 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);

\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'

SELECT * from tpch.nation_hash ;