SET datestyle = 'ISO'; -- Create servers for each engine. CREATE SERVER param_bin_svr FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(driver 'binary'); CREATE USER MAPPING FOR CURRENT_USER SERVER param_bin_svr; CREATE SERVER param_http_svr FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(driver 'http'); CREATE USER MAPPING FOR CURRENT_USER SERVER param_http_svr; -- Create the schema in ClickHouse. SELECT clickhouse_raw_query('DROP DATABASE IF EXISTS param_test'); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('CREATE DATABASE param_test'); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query($$ CREATE TABLE param_test.ft1 ( c1 Int, c2 Int, c3 String, c4 Date, c5 Date, c6 String, c7 String, c8 Array(String) ) ENGINE = MergeTree PARTITION BY c4 ORDER BY (c1) $$); clickhouse_raw_query ---------------------- (1 row) -- Insert some data. SELECT clickhouse_raw_query($$ INSERT INTO param_test.ft1 SELECT number, number % 10, toString(number), addDays(toDate('1970-01-01'), number % 100), addDays(toDate('1970-01-01'), number % 100), number % 10, number % 10, ['foo'] FROM numbers(1, 110) $$); clickhouse_raw_query ---------------------- (1 row) -- =================================================================== -- binary foreign tables -- =================================================================== CREATE SCHEMA bin_test; CREATE FOREIGN TABLE bin_test.ft1 ( c1 int NOT NULL, c2 int NOT NULL, c3 text, c4 timestamptz, c5 timestamp, c6 varchar(10), c7 char(10) default 'ft1', c8 text[] ) SERVER param_bin_svr OPTIONS( database 'param_test', table_name 'ft1' ); CREATE FOREIGN TABLE bin_test.ft2 ( c1 int NOT NULL, c2 int NOT NULL, c3 text, c4 timestamptz, c5 timestamp, c6 varchar(10), c7 char(10) default 'f21', c8 text[] ) SERVER param_bin_svr OPTIONS( database 'param_test', table_name 'ft1' ); -- =================================================================== -- binary parameterized queries -- =================================================================== -- simple join PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM bin_test.ft1 t1, bin_test.ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st1(1, 2); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: t1.c3, t2.c3 Relations: (ft1 t1) INNER JOIN (ft2 t2) Remote SQL: SELECT r1.c3, r2.c3 FROM param_test.ft1 r1 ALL INNER JOIN param_test.ft1 r2 ON (TRUE) WHERE ((r2.c1 = 2)) AND ((r1.c1 = 1)) (4 rows) EXECUTE st1(1, 1); c3 | c3 ----+---- 1 | 1 (1 row) EXECUTE st1(101, 101); c3 | c3 -----+----- 101 | 101 (1 row) SET enable_hashjoin TO off; SET enable_sort TO off; -- subquery using stable function (can't be sent to remote, but is?) PREPARE st2(int) AS SELECT * FROM bin_test.ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM bin_test.ft2 t2 WHERE c1 > $1 AND date(c4) = date('1970-01-17')) ORDER BY c1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st2(10, 20); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8 Relations: (ft1 t1) LEFT SEMI JOIN (ft2 t2) Remote SQL: SELECT r1.c1, r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8 FROM param_test.ft1 r1 LEFT SEMI JOIN param_test.ft1 r3 ON (((r3.c1 > 10)) AND ((date(r3.c4) = '1970-01-17')) AND ((r1.c3 = r3.c3))) WHERE ((r1.c1 < 20)) ORDER BY r1.c1 ASC NULLS LAST (4 rows) EXECUTE st2(10, 20); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+------------------------+---------------------+----+----+------- 16 | 6 | 16 | 1970-01-17 00:00:00-08 | 1970-01-17 00:00:00 | 6 | 6 | {foo} (1 row) EXECUTE st2(101, 121); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+----+----+----+----+---- (0 rows) RESET enable_hashjoin; RESET enable_sort; -- subquery using immutable function (can be sent to remote) PREPARE st3(int) AS SELECT * FROM bin_test.ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM bin_test.ft2 t2 WHERE c1 > $1 AND date(c5) = date('1970-01-17'::timestamptz)) ORDER BY c1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st3(10, 20); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8 Relations: (ft1 t1) LEFT SEMI JOIN (ft2 t2) Remote SQL: SELECT r1.c1, r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8 FROM param_test.ft1 r1 LEFT SEMI JOIN param_test.ft1 r3 ON (((r3.c1 > 10)) AND ((date(r3.c5) = date('1970-01-17 08:00:00'))) AND ((r1.c3 = r3.c3))) WHERE ((r1.c1 < 20)) ORDER BY r1.c1 ASC NULLS LAST (4 rows) EXECUTE st3(10, 20); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+------------------------+---------------------+----+----+------- 16 | 6 | 16 | 1970-01-17 00:00:00-08 | 1970-01-17 00:00:00 | 6 | 6 | {foo} (1 row) EXECUTE st3(20, 30); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+----+----+----+----+---- (0 rows) -- custom plan should be chosen initially PREPARE st4(int) AS SELECT * FROM bin_test.ft1 t1 WHERE t1.c1 = $1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) -- once we try it enough times, should switch to generic plan EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN --------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = {p1:Int32})) (3 rows) -- value of $1 should not be sent to remote PREPARE st5(text[], int) AS SELECT * FROM bin_test.ft1 t1 WHERE c8 = $1 and c1 = $2; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = {p1:Array(String)})) AND ((c1 = {p2:Int32})) (3 rows) EXECUTE st5('{foo}', 1); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+------------------------+---------------------+----+----+------- 1 | 1 | 1 | 1970-01-02 00:00:00-08 | 1970-01-02 00:00:00 | 1 | 1 | {foo} (1 row) -- altering FDW options requires replanning PREPARE st6 AS SELECT * FROM bin_test.ft1 t1 WHERE t1.c1 = t1.c2 ORDER BY t1.c1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = c2)) ORDER BY c1 ASC NULLS LAST (3 rows) PREPARE st7 AS INSERT INTO bin_test.ft1 (c1,c2,c3) VALUES (1001,101,'foo'); EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Insert on bin_test.ft1 -> Result Output: 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time zone, NULL::character varying, 'ft1 '::character(10), NULL::text[] (3 rows) SELECT clickhouse_raw_query('RENAME TABLE param_test.ft1 TO param_test.t1'); clickhouse_raw_query ---------------------- (1 row) ALTER FOREIGN TABLE bin_test.ft1 OPTIONS (SET table_name 't1'); EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.t1 WHERE ((c1 = c2)) ORDER BY c1 ASC NULLS LAST (3 rows) EXECUTE st6; c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+------------------------+---------------------+----+----+------- 1 | 1 | 1 | 1970-01-02 00:00:00-08 | 1970-01-02 00:00:00 | 1 | 1 | {foo} 2 | 2 | 2 | 1970-01-03 00:00:00-08 | 1970-01-03 00:00:00 | 2 | 2 | {foo} 3 | 3 | 3 | 1970-01-04 00:00:00-08 | 1970-01-04 00:00:00 | 3 | 3 | {foo} 4 | 4 | 4 | 1970-01-05 00:00:00-08 | 1970-01-05 00:00:00 | 4 | 4 | {foo} 5 | 5 | 5 | 1970-01-06 00:00:00-08 | 1970-01-06 00:00:00 | 5 | 5 | {foo} 6 | 6 | 6 | 1970-01-07 00:00:00-08 | 1970-01-07 00:00:00 | 6 | 6 | {foo} 7 | 7 | 7 | 1970-01-08 00:00:00-08 | 1970-01-08 00:00:00 | 7 | 7 | {foo} 8 | 8 | 8 | 1970-01-09 00:00:00-08 | 1970-01-09 00:00:00 | 8 | 8 | {foo} 9 | 9 | 9 | 1970-01-10 00:00:00-08 | 1970-01-10 00:00:00 | 9 | 9 | {foo} (9 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Insert on bin_test.ft1 -> Result Output: 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time zone, NULL::character varying, 'ft1 '::character(10), NULL::text[] (3 rows) SELECT clickhouse_raw_query('RENAME TABLE param_test.t1 TO param_test.ft1'); clickhouse_raw_query ---------------------- (1 row) ALTER FOREIGN TABLE bin_test.ft1 OPTIONS (SET table_name 'ft1'); -- implicit parameter EXPLAIN (VERBOSE, COSTS OFF) SELECT c1, c2 FROM bin_test.ft1 WHERE c1 = (SELECT 4); QUERY PLAN --------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 Output: ft1.c1, ft1.c2 Remote SQL: SELECT c1, c2 FROM param_test.ft1 WHERE ((c1 = {p1:Int32})) InitPlan 1 (returns $0) -> Result Output: 4 (6 rows) SELECT c1, c2 FROM bin_test.ft1 WHERE c1 = (SELECT 4); c1 | c2 ----+---- 4 | 4 (1 row) -- cleanup DEALLOCATE st1; DEALLOCATE st2; DEALLOCATE st3; DEALLOCATE st4; DEALLOCATE st5; DEALLOCATE st6; DEALLOCATE st7; -- =================================================================== -- http foreign tables -- =================================================================== CREATE SCHEMA http_test; CREATE FOREIGN TABLE http_test.ft1 ( c1 int NOT NULL, c2 int NOT NULL, c3 text, c4 timestamptz, c5 timestamp, c6 varchar(10), c7 char(10) default 'ft1', c8 text[] ) SERVER param_http_svr OPTIONS( database 'param_test', table_name 'ft1' ); CREATE FOREIGN TABLE http_test.ft2 ( c1 int NOT NULL, c2 int NOT NULL, c3 text, c4 timestamptz, c5 timestamp, c6 varchar(10), c7 char(10) default 'f21', c8 text[] ) SERVER param_http_svr OPTIONS( database 'param_test', table_name 'ft1' ); -- =================================================================== -- http parameterized queries -- =================================================================== -- simple join PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM http_test.ft1 t1, http_test.ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st1(1, 2); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: t1.c3, t2.c3 Relations: (ft1 t1) INNER JOIN (ft2 t2) Remote SQL: SELECT r1.c3, r2.c3 FROM param_test.ft1 r1 ALL INNER JOIN param_test.ft1 r2 ON (TRUE) WHERE ((r2.c1 = 2)) AND ((r1.c1 = 1)) (4 rows) EXECUTE st1(1, 1); c3 | c3 ----+---- 1 | 1 (1 row) EXECUTE st1(101, 101); c3 | c3 -----+----- 101 | 101 (1 row) SET enable_hashjoin TO off; SET enable_sort TO off; -- subquery using stable function (can't be sent to remote, but is?) PREPARE st2(int) AS SELECT * FROM http_test.ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM http_test.ft2 t2 WHERE c1 > $1 AND date(c4) = date('1970-01-17')) ORDER BY c1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st2(10, 20); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8 Relations: (ft1 t1) LEFT SEMI JOIN (ft2 t2) Remote SQL: SELECT r1.c1, r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8 FROM param_test.ft1 r1 LEFT SEMI JOIN param_test.ft1 r3 ON (((r3.c1 > 10)) AND ((date(r3.c4) = '1970-01-17')) AND ((r1.c3 = r3.c3))) WHERE ((r1.c1 < 20)) ORDER BY r1.c1 ASC NULLS LAST (4 rows) EXECUTE st2(10, 20); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+------------------------+---------------------+----+------------+--------- 16 | 6 | 16 | 1970-01-17 00:00:00-08 | 1970-01-17 00:00:00 | 6 | 6 | {'foo'} (1 row) EXECUTE st2(101, 121); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+----+----+----+----+---- (0 rows) RESET enable_hashjoin; RESET enable_sort; -- subquery using immutable function (can be sent to remote) PREPARE st3(int) AS SELECT * FROM http_test.ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM http_test.ft2 t2 WHERE c1 > $1 AND date(c5) = date('1970-01-17'::timestamptz)) ORDER BY c1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st3(10, 20); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8 Relations: (ft1 t1) LEFT SEMI JOIN (ft2 t2) Remote SQL: SELECT r1.c1, r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8 FROM param_test.ft1 r1 LEFT SEMI JOIN param_test.ft1 r3 ON (((r3.c1 > 10)) AND ((date(r3.c5) = date('1970-01-17 08:00:00'))) AND ((r1.c3 = r3.c3))) WHERE ((r1.c1 < 20)) ORDER BY r1.c1 ASC NULLS LAST (4 rows) EXECUTE st3(10, 20); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+------------------------+---------------------+----+------------+--------- 16 | 6 | 16 | 1970-01-17 00:00:00-08 | 1970-01-17 00:00:00 | 6 | 6 | {'foo'} (1 row) EXECUTE st3(20, 30); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+----+----+----+----+---- (0 rows) -- custom plan should be chosen initially PREPARE st4(int) AS SELECT * FROM http_test.ft1 t1 WHERE t1.c1 = $1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) -- once we try it enough times, should switch to generic plan EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN --------------------------------------------------------------------------------------------------- Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = {p1:Int32})) (3 rows) -- value of $1 should not be sent to remote PREPARE st5(text[], int) AS SELECT * FROM http_test.ft1 t1 WHERE c8 = $1 and c1 = $2; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = {p1:Array(String)})) AND ((c1 = {p2:Int32})) (3 rows) EXECUTE st5('{foo}', 1); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+------------------------+---------------------+----+------------+--------- 1 | 1 | 1 | 1970-01-02 00:00:00-08 | 1970-01-02 00:00:00 | 1 | 1 | {'foo'} (1 row) -- altering FDW options requires replanning PREPARE st6 AS SELECT * FROM http_test.ft1 t1 WHERE t1.c1 = t1.c2 ORDER BY t1.c1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = c2)) ORDER BY c1 ASC NULLS LAST (3 rows) PREPARE st7 AS INSERT INTO http_test.ft1 (c1,c2,c3) VALUES (1001,101,'foo'); EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Insert on http_test.ft1 -> Result Output: 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time zone, NULL::character varying, 'ft1 '::character(10), NULL::text[] (3 rows) SELECT clickhouse_raw_query('RENAME TABLE param_test.ft1 TO param_test.t1'); clickhouse_raw_query ---------------------- (1 row) ALTER FOREIGN TABLE http_test.ft1 OPTIONS (SET table_name 't1'); EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.t1 WHERE ((c1 = c2)) ORDER BY c1 ASC NULLS LAST (3 rows) EXECUTE st6; c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+------------------------+---------------------+----+------------+--------- 1 | 1 | 1 | 1970-01-02 00:00:00-08 | 1970-01-02 00:00:00 | 1 | 1 | {'foo'} 2 | 2 | 2 | 1970-01-03 00:00:00-08 | 1970-01-03 00:00:00 | 2 | 2 | {'foo'} 3 | 3 | 3 | 1970-01-04 00:00:00-08 | 1970-01-04 00:00:00 | 3 | 3 | {'foo'} 4 | 4 | 4 | 1970-01-05 00:00:00-08 | 1970-01-05 00:00:00 | 4 | 4 | {'foo'} 5 | 5 | 5 | 1970-01-06 00:00:00-08 | 1970-01-06 00:00:00 | 5 | 5 | {'foo'} 6 | 6 | 6 | 1970-01-07 00:00:00-08 | 1970-01-07 00:00:00 | 6 | 6 | {'foo'} 7 | 7 | 7 | 1970-01-08 00:00:00-08 | 1970-01-08 00:00:00 | 7 | 7 | {'foo'} 8 | 8 | 8 | 1970-01-09 00:00:00-08 | 1970-01-09 00:00:00 | 8 | 8 | {'foo'} 9 | 9 | 9 | 1970-01-10 00:00:00-08 | 1970-01-10 00:00:00 | 9 | 9 | {'foo'} (9 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Insert on http_test.ft1 -> Result Output: 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time zone, NULL::character varying, 'ft1 '::character(10), NULL::text[] (3 rows) SELECT clickhouse_raw_query('RENAME TABLE param_test.t1 TO param_test.ft1'); clickhouse_raw_query ---------------------- (1 row) ALTER FOREIGN TABLE http_test.ft1 OPTIONS (SET table_name 'ft1'); -- implicit parameter EXPLAIN (VERBOSE, COSTS OFF) SELECT c1, c2 FROM http_test.ft1 WHERE c1 = (SELECT 4); QUERY PLAN --------------------------------------------------------------------------- Foreign Scan on http_test.ft1 Output: ft1.c1, ft1.c2 Remote SQL: SELECT c1, c2 FROM param_test.ft1 WHERE ((c1 = {p1:Int32})) InitPlan 1 (returns $0) -> Result Output: 4 (6 rows) SELECT c1, c2 FROM http_test.ft1 WHERE c1 = (SELECT 4); c1 | c2 ----+---- 4 | 4 (1 row) -- cleanup DEALLOCATE st1; DEALLOCATE st2; DEALLOCATE st3; DEALLOCATE st4; DEALLOCATE st5; DEALLOCATE st6; DEALLOCATE st7; -- Clean up. DROP USER MAPPING FOR CURRENT_USER SERVER param_bin_svr; DROP SERVER param_bin_svr CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to foreign table bin_test.ft1 drop cascades to foreign table bin_test.ft2 DROP USER MAPPING FOR CURRENT_USER SERVER param_http_svr; DROP SERVER param_http_svr CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to foreign table http_test.ft1 drop cascades to foreign table http_test.ft2