Cstore_fdw does not work on different schemas

[onderkalaci]

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



------------ SECOND PART OF THE TEST -----  Create the table when the search_path is set


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

So, since the root problem here is worker_apply_shard_ddl_command, this second part of the test relates to 267

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;