diff options
| -rw-r--r-- | contrib/postgres_fdw/connection.c | 29 | ||||
| -rw-r--r-- | contrib/postgres_fdw/deparse.c | 101 | ||||
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 164 |
3 files changed, 180 insertions, 114 deletions
diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c index 0e54901061c..32a3138ce0f 100644 --- a/contrib/postgres_fdw/connection.c +++ b/contrib/postgres_fdw/connection.c @@ -63,6 +63,7 @@ static bool xact_got_connection = false; /* prototypes of private functions */ static PGconn *connect_pg_server(ForeignServer *server, UserMapping *user); static void check_conn_params(const char **keywords, const char **values); +static void configure_remote_session(PGconn *conn); static void begin_remote_xact(ConnCacheEntry *entry); static void pgfdw_xact_callback(XactEvent event, void *arg); static void pgfdw_subxact_callback(SubXactEvent event, @@ -237,6 +238,9 @@ connect_pg_server(ForeignServer *server, UserMapping *user) errdetail("Non-superuser cannot connect if the server does not request a password."), errhint("Target server's authentication method must be changed."))); + /* Prepare new session for use */ + configure_remote_session(conn); + pfree(keywords); pfree(values); } @@ -282,6 +286,31 @@ check_conn_params(const char **keywords, const char **values) } /* + * Issue SET commands to make sure remote session is configured properly. + * + * We do this just once at connection, assuming nothing will change the + * values later. Since we'll never send volatile function calls to the + * remote, there shouldn't be any way to break this assumption from our end. + * It's possible to think of ways to break it at the remote end, eg making + * a foreign table point to a view that includes a set_config call --- + * but once you admit the possibility of a malicious view definition, + * there are any number of ways to break things. + */ +static void +configure_remote_session(PGconn *conn) +{ + const char *sql; + PGresult *res; + + /* Force the search path to contain only pg_catalog (see deparse.c) */ + sql = "SET search_path = pg_catalog"; + res = PQexec(conn, sql); + if (PQresultStatus(res) != PGRES_COMMAND_OK) + pgfdw_report_error(ERROR, res, true, sql); + PQclear(res); +} + +/* * Start remote transaction or subtransaction, if needed. * * Note that we always use at least REPEATABLE READ in the remote session. diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c index 7fc1f797ab2..f005e0f26fb 100644 --- a/contrib/postgres_fdw/deparse.c +++ b/contrib/postgres_fdw/deparse.c @@ -11,6 +11,9 @@ * One saving grace is that we only need deparse logic for node types that * we consider safe to send. * + * We assume that the remote session's search_path is exactly "pg_catalog", + * and thus we need schema-qualify all and only names outside pg_catalog. + * * Portions Copyright (c) 2012-2013, PostgreSQL Global Development Group * * IDENTIFICATION @@ -25,6 +28,7 @@ #include "access/htup_details.h" #include "access/sysattr.h" #include "access/transam.h" +#include "catalog/pg_namespace.h" #include "catalog/pg_operator.h" #include "catalog/pg_proc.h" #include "catalog/pg_type.h" @@ -73,6 +77,7 @@ static void deparseParam(StringInfo buf, Param *node, PlannerInfo *root); static void deparseArrayRef(StringInfo buf, ArrayRef *node, PlannerInfo *root); static void deparseFuncExpr(StringInfo buf, FuncExpr *node, PlannerInfo *root); static void deparseOpExpr(StringInfo buf, OpExpr *node, PlannerInfo *root); +static void deparseOperatorName(StringInfo buf, Form_pg_operator opform); static void deparseDistinctExpr(StringInfo buf, DistinctExpr *node, PlannerInfo *root); static void deparseScalarArrayOpExpr(StringInfo buf, ScalarArrayOpExpr *node, @@ -321,6 +326,18 @@ foreign_expr_walker(Node *node, foreign_expr_cxt *context) /* * Return true if given object is one of PostgreSQL's built-in objects. * + * We use FirstBootstrapObjectId as the cutoff, so that we only consider + * objects with hand-assigned OIDs to be "built in", not for instance any + * function or type defined in the information_schema. + * + * Our constraints for dealing with types are tighter than they are for + * functions or operators: we want to accept only types that are in pg_catalog + * (else format_type might incorrectly fail to schema-qualify their names), + * and we want to be sure that the remote server will use the same OID as + * we do (since we must transmit a numeric type OID when passing a value of + * the type as a query parameter). Both of these are reasons to reject + * objects created post-bootstrap. + * * XXX there is a problem with this, which is that the set of built-in * objects expands over time. Something that is built-in to us might not * be known to the remote server, if it's of an older version. But keeping @@ -329,7 +346,7 @@ foreign_expr_walker(Node *node, foreign_expr_cxt *context) static bool is_builtin(Oid oid) { - return (oid < FirstNormalObjectId); + return (oid < FirstBootstrapObjectId); } @@ -563,6 +580,10 @@ deparseRelation(StringInfo buf, Oid relid) relname = defGetString(def); } + /* + * Note: we could skip printing the schema name if it's pg_catalog, + * but that doesn't seem worth the trouble. + */ if (nspname == NULL) nspname = get_namespace_name(get_rel_namespace(relid)); if (relname == NULL) @@ -832,9 +853,6 @@ deparseArrayRef(StringInfo buf, ArrayRef *node, PlannerInfo *root) * Here not only explicit function calls and explicit casts but also implicit * casts are deparsed to avoid problems caused by different cast settings * between local and remote. - * - * Function name is always qualified by schema name to avoid problems caused - * by different setting of search_path on remote side. */ static void deparseFuncExpr(StringInfo buf, FuncExpr *node, PlannerInfo *root) @@ -842,7 +860,6 @@ deparseFuncExpr(StringInfo buf, FuncExpr *node, PlannerInfo *root) HeapTuple proctup; Form_pg_proc procform; const char *proname; - const char *schemaname; bool use_variadic; bool first; ListCell *arg; @@ -851,7 +868,6 @@ deparseFuncExpr(StringInfo buf, FuncExpr *node, PlannerInfo *root) if (!HeapTupleIsValid(proctup)) elog(ERROR, "cache lookup failed for function %u", node->funcid); procform = (Form_pg_proc) GETSTRUCT(proctup); - proname = NameStr(procform->proname); /* Check if need to print VARIADIC (cf. ruleutils.c) */ if (OidIsValid(procform->provariadic)) @@ -864,11 +880,18 @@ deparseFuncExpr(StringInfo buf, FuncExpr *node, PlannerInfo *root) else use_variadic = false; + /* Print schema name only if it's not pg_catalog */ + if (procform->pronamespace != PG_CATALOG_NAMESPACE) + { + const char *schemaname; + + schemaname = get_namespace_name(procform->pronamespace); + appendStringInfo(buf, "%s.", quote_identifier(schemaname)); + } + /* Deparse the function name ... */ - schemaname = get_namespace_name(procform->pronamespace); - appendStringInfo(buf, "%s.%s(", - quote_identifier(schemaname), - quote_identifier(proname)); + proname = NameStr(procform->proname); + appendStringInfo(buf, "%s(", quote_identifier(proname)); /* ... and all the arguments */ first = true; foreach(arg, node->args) @@ -887,16 +910,13 @@ deparseFuncExpr(StringInfo buf, FuncExpr *node, PlannerInfo *root) /* * Deparse given operator expression into buf. To avoid problems around - * priority of operations, we always parenthesize the arguments. Also we use - * OPERATOR(schema.operator) notation to determine remote operator exactly. + * priority of operations, we always parenthesize the arguments. */ static void deparseOpExpr(StringInfo buf, OpExpr *node, PlannerInfo *root) { HeapTuple tuple; Form_pg_operator form; - const char *opnspname; - char *opname; char oprkind; ListCell *arg; @@ -905,10 +925,6 @@ deparseOpExpr(StringInfo buf, OpExpr *node, PlannerInfo *root) if (!HeapTupleIsValid(tuple)) elog(ERROR, "cache lookup failed for operator %u", node->opno); form = (Form_pg_operator) GETSTRUCT(tuple); - - opnspname = quote_identifier(get_namespace_name(form->oprnamespace)); - /* opname is not a SQL identifier, so we don't need to quote it. */ - opname = NameStr(form->oprname); oprkind = form->oprkind; /* Sanity check. */ @@ -927,8 +943,8 @@ deparseOpExpr(StringInfo buf, OpExpr *node, PlannerInfo *root) appendStringInfoChar(buf, ' '); } - /* Deparse fully qualified operator name. */ - appendStringInfo(buf, "OPERATOR(%s.%s)", opnspname, opname); + /* Deparse operator name. */ + deparseOperatorName(buf, form); /* Deparse right operand. */ if (oprkind == 'l' || oprkind == 'b') @@ -944,6 +960,34 @@ deparseOpExpr(StringInfo buf, OpExpr *node, PlannerInfo *root) } /* + * Print the name of an operator. + */ +static void +deparseOperatorName(StringInfo buf, Form_pg_operator opform) +{ + char *opname; + + /* opname is not a SQL identifier, so we should not quote it. */ + opname = NameStr(opform->oprname); + + /* Print schema name only if it's not pg_catalog */ + if (opform->oprnamespace != PG_CATALOG_NAMESPACE) + { + const char *opnspname; + + opnspname = get_namespace_name(opform->oprnamespace); + /* Print fully qualified operator name. */ + appendStringInfo(buf, "OPERATOR(%s.%s)", + quote_identifier(opnspname), opname); + } + else + { + /* Just print operator name. */ + appendStringInfo(buf, "%s", opname); + } +} + +/* * Deparse IS DISTINCT FROM. */ static void @@ -960,9 +1004,7 @@ deparseDistinctExpr(StringInfo buf, DistinctExpr *node, PlannerInfo *root) /* * Deparse given ScalarArrayOpExpr expression into buf. To avoid problems - * around priority of operations, we always parenthesize the arguments. Also - * we use OPERATOR(schema.operator) notation to determine remote operator - * exactly. + * around priority of operations, we always parenthesize the arguments. */ static void deparseScalarArrayOpExpr(StringInfo buf, @@ -971,8 +1013,6 @@ deparseScalarArrayOpExpr(StringInfo buf, { HeapTuple tuple; Form_pg_operator form; - const char *opnspname; - char *opname; Expr *arg1; Expr *arg2; @@ -982,10 +1022,6 @@ deparseScalarArrayOpExpr(StringInfo buf, elog(ERROR, "cache lookup failed for operator %u", node->opno); form = (Form_pg_operator) GETSTRUCT(tuple); - opnspname = quote_identifier(get_namespace_name(form->oprnamespace)); - /* opname is not a SQL identifier, so we don't need to quote it. */ - opname = NameStr(form->oprname); - /* Sanity check. */ Assert(list_length(node->args) == 2); @@ -995,10 +1031,11 @@ deparseScalarArrayOpExpr(StringInfo buf, /* Deparse left operand. */ arg1 = linitial(node->args); deparseExpr(buf, arg1, root); + appendStringInfoChar(buf, ' '); - /* Deparse fully qualified operator name plus decoration. */ - appendStringInfo(buf, " OPERATOR(%s.%s) %s (", - opnspname, opname, node->useOr ? "ANY" : "ALL"); + /* Deparse operator name plus decoration. */ + deparseOperatorName(buf, form); + appendStringInfo(buf, " %s (", node->useOr ? "ANY" : "ALL"); /* Deparse right operand. */ arg2 = lsecond(node->args); diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 253cdca11a3..b81a3086738 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -188,11 +188,11 @@ SELECT * FROM ft1 WHERE false; -- with WHERE clause EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1'; - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((c7 OPERATOR(pg_catalog.>=) '1'::bpchar)) AND (("C 1" OPERATOR(pg_catalog.=) 101)) AND ((c6::text OPERATOR(pg_catalog.=) '1'::text)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((c7 >= '1'::bpchar)) AND (("C 1" = 101)) AND ((c6::text = '1'::text)) (3 rows) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1'; @@ -302,38 +302,38 @@ EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2; (4 rows) EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = abs(t1.c2); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) pg_catalog.abs(c2))) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = abs(c2))) (3 rows) EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2; - QUERY PLAN -------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) c2)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = c2)) (3 rows) -- =================================================================== -- WHERE with remotely-executable conditions -- =================================================================== EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 1; -- Var, OpExpr(b), Const - QUERY PLAN ------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 1)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2 = 0; -- BoolExpr - QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 100)) AND ((c2 OPERATOR(pg_catalog.=) 0)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 100)) AND ((c2 = 0)) (3 rows) EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 IS NULL; -- NullTest @@ -353,27 +353,27 @@ EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL; -- (3 rows) EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr - QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((pg_catalog.round(pg_catalog."numeric"(pg_catalog.abs("C 1")), 0) OPERATOR(pg_catalog.=) 1::numeric)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((round("numeric"(abs("C 1")), 0) = 1::numeric)) (3 rows) EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = -c1; -- OpExpr(l) - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) (OPERATOR(pg_catalog.-) "C 1"))) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = (- "C 1"))) (3 rows) EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE 1 = c1!; -- OpExpr(r) - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((1::numeric OPERATOR(pg_catalog.=) (pg_catalog.int8("C 1") OPERATOR(pg_catalog.!)))) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((1::numeric = (int8("C 1") !))) (3 rows) EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE (c1 IS NOT NULL) IS DISTINCT FROM (c1 IS NOT NULL); -- DistinctExpr @@ -385,27 +385,27 @@ EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE (c1 IS NOT NULL) IS DI (3 rows) EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1 + 0]); -- ScalarArrayOpExpr - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) ANY (ARRAY[c2, 1, ("C 1" OPERATOR(pg_catalog.+) 0)]))) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = ANY (ARRAY[c2, 1, ("C 1" + 0)]))) (3 rows) EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = (ARRAY[c1,c2,3])[1]; -- ArrayRef - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) ((ARRAY["C 1", c2, 3])[1]))) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = ((ARRAY["C 1", c2, 3])[1]))) (3 rows) EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c6 = E'foo''s\\bar'; -- check special chars - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((c6::text OPERATOR(pg_catalog.=) E'foo''s\\bar'::text)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((c6::text = E'foo''s\\bar'::text)) (3 rows) EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c8 = 'foo'; -- can't be sent to remote @@ -423,16 +423,16 @@ EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c8 = 'foo'; -- can't -- simple join PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2; EXPLAIN (VERBOSE, COSTS false) EXECUTE st1(1, 2); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +-------------------------------------------------------------------------------------------------------------- Nested Loop Output: t1.c3, t2.c3 -> Foreign Scan on public.ft1 t1 Output: t1.c3 - Remote SQL: SELECT NULL, NULL, c3, NULL, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 1)) + Remote SQL: SELECT NULL, NULL, c3, NULL, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" = 1)) -> Foreign Scan on public.ft2 t2 Output: t2.c3 - Remote SQL: SELECT NULL, NULL, c3, NULL, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 2)) + Remote SQL: SELECT NULL, NULL, c3, NULL, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" = 2)) (8 rows) EXECUTE st1(1, 1); @@ -450,8 +450,8 @@ EXECUTE st1(101, 101); -- subquery using stable function (can't be sent to remote) PREPARE st2(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND EXTRACT(dow FROM c4) = 6) ORDER BY c1; EXPLAIN (VERBOSE, COSTS false) EXECUTE st2(10, 20); - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------- Sort Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8 Sort Key: t1.c1 @@ -460,13 +460,13 @@ EXPLAIN (VERBOSE, COSTS false) EXECUTE st2(10, 20); Join Filter: (t1.c3 = t2.c3) -> Foreign Scan on public.ft1 t1 Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.<) 20)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" < 20)) -> Materialize Output: t2.c3 -> Foreign Scan on public.ft2 t2 Output: t2.c3 Filter: (date_part('dow'::text, t2.c4) = 6::double precision) - Remote SQL: SELECT NULL, NULL, c3, c4, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.>) 10)) + Remote SQL: SELECT NULL, NULL, c3, c4, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" > 10)) (15 rows) EXECUTE st2(10, 20); @@ -484,8 +484,8 @@ EXECUTE st1(101, 101); -- subquery using immutable function (can be sent to remote) PREPARE st3(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND EXTRACT(dow FROM c5) = 6) ORDER BY c1; EXPLAIN (VERBOSE, COSTS false) EXECUTE st3(10, 20); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Sort Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8 Sort Key: t1.c1 @@ -494,12 +494,12 @@ EXPLAIN (VERBOSE, COSTS false) EXECUTE st3(10, 20); Join Filter: (t1.c3 = t2.c3) -> Foreign Scan on public.ft1 t1 Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.<) 20)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" < 20)) -> Materialize Output: t2.c3 -> Foreign Scan on public.ft2 t2 Output: t2.c3 - Remote SQL: SELECT NULL, NULL, c3, NULL, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.>) 10)) AND ((pg_catalog.date_part('dow'::text, c5) OPERATOR(pg_catalog.=) 6::double precision)) + Remote SQL: SELECT NULL, NULL, c3, NULL, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" > 10)) AND ((date_part('dow'::text, c5) = 6::double precision)) (14 rows) EXECUTE st3(10, 20); @@ -517,108 +517,108 @@ EXECUTE st3(20, 30); -- custom plan should be chosen initially PREPARE st4(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 = $1; EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 1)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 1)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 1)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 1)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 1)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1)) (3 rows) -- once we try it enough times, should switch to generic plan EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1); - QUERY PLAN -------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) $1)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = $1)) (3 rows) -- value of $1 should not be sent to remote PREPARE st5(user_enum,int) AS SELECT * FROM ft1 t1 WHERE c8 = $1 and c1 = $2; EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Filter: (t1.c8 = 'foo'::user_enum) - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 1)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1)) (4 rows) EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Filter: (t1.c8 = 'foo'::user_enum) - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 1)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1)) (4 rows) EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Filter: (t1.c8 = 'foo'::user_enum) - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 1)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1)) (4 rows) EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Filter: (t1.c8 = 'foo'::user_enum) - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 1)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1)) (4 rows) EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Filter: (t1.c8 = 'foo'::user_enum) - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) 1)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1)) (4 rows) EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1); - QUERY PLAN -------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------- Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Filter: (t1.c8 = $1) - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(pg_catalog.=) $2)) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = $2)) (4 rows) EXECUTE st5('foo', 1); @@ -687,7 +687,7 @@ FETCH c; SAVEPOINT s; SELECT * FROM ft1 WHERE 1 / (c1 - 1) > 0; -- ERROR ERROR: division by zero -CONTEXT: Remote SQL command: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (((1 OPERATOR(pg_catalog./) ("C 1" OPERATOR(pg_catalog.-) 1)) OPERATOR(pg_catalog.>) 0)) +CONTEXT: Remote SQL command: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (((1 / ("C 1" - 1)) > 0)) ROLLBACK TO s; FETCH c; c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 |