Skip to content

FDW extension patch #7

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
wants to merge 8 commits into from
Closed

FDW extension patch #7

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
8 commits
Select commit Hold shift + click to select a range
3f5b45b
Patch status at 20151001
pramsey Oct 3, 2015
ed33e74
Fixes from Andres Freund patch review
pramsey Oct 4, 2015
5e495de
Comment use of hash vis-a-vis cache invalidation
pramsey Oct 6, 2015
a04c63b
Merge branch 'master' into fdw-extension-support
pramsey Oct 6, 2015
37970c2
Remove comment change
pramsey Oct 6, 2015
d77489f
Ensure shippability info is cached per-server
pramsey Oct 6, 2015
bda377f
Add regression test and small fix turned up by regression
pramsey Oct 6, 2015
91b6622
Use pgsql MemSet?
pramsey Oct 6, 2015
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
7 changes: 5 additions & 2 deletions contrib/postgres_fdw/Makefile
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,7 +1,7 @@
# contrib/postgres_fdw/Makefile

MODULE_big = postgres_fdw
OBJS = postgres_fdw.o option.o deparse.o connection.o $(WIN32RES)
OBJS = postgres_fdw.o option.o deparse.o connection.o shippable.o $(WIN32RES)
PGFILEDESC = "postgres_fdw - foreign data wrapper for PostgreSQL"

PG_CPPFLAGS = -I$(libpq_srcdir)
Expand All @@ -10,7 +10,10 @@ SHLIB_LINK = $(libpq)
EXTENSION = postgres_fdw
DATA = postgres_fdw--1.0.sql

REGRESS = postgres_fdw
# Note: shippable tests depend on postgres_fdw tests setup
REGRESS = postgres_fdw shippable
# Note: shippable tests require cube and seg
EXTRA_INSTALL = contrib/cube contrib/seg

ifdef USE_PGXS
PG_CONFIG = pg_config
Expand Down
37 changes: 30 additions & 7 deletions contrib/postgres_fdw/deparse.c
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -233,6 +233,9 @@ foreign_expr_walker(Node *node,
Oid collation;
FDWCollateState state;

/* Access extension metadata from fpinfo on baserel */
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *)(glob_cxt->foreignrel->fdw_private);

/* Need do nothing for empty subexpressions */
if (node == NULL)
return true;
Expand Down Expand Up @@ -378,7 +381,8 @@ foreign_expr_walker(Node *node,
* can't be sent to remote because it might have incompatible
* semantics on remote side.
*/
if (!is_builtin(fe->funcid))
if (!is_builtin(fe->funcid) &&
!is_shippable(fe->funcid, ProcedureRelationId, fpinfo->server, fpinfo->extensions))
return false;

/*
Expand Down Expand Up @@ -426,7 +430,8 @@ foreign_expr_walker(Node *node,
* (If the operator is, surely its underlying function is
* too.)
*/
if (!is_builtin(oe->opno))
if (!is_builtin(oe->opno) &&
!is_shippable(oe->opno, OperatorRelationId, fpinfo->server, fpinfo->extensions))
return false;

/*
Expand Down Expand Up @@ -466,7 +471,8 @@ foreign_expr_walker(Node *node,
/*
* Again, only built-in operators can be sent to remote.
*/
if (!is_builtin(oe->opno))
if (!is_builtin(oe->opno) &&
!is_shippable(oe->opno, OperatorRelationId, fpinfo->server, fpinfo->extensions))
return false;

/*
Expand Down Expand Up @@ -616,7 +622,9 @@ foreign_expr_walker(Node *node,
* If result type of given expression is not built-in, it can't be sent to
* remote because it might have incompatible semantics on remote side.
*/
if (check_type && !is_builtin(exprType(node)))
if (check_type &&
!is_builtin(exprType(node)) &&
!is_shippable(exprType(node), TypeRelationId, fpinfo->server, fpinfo->extensions))
return false;

/*
Expand Down Expand Up @@ -1351,6 +1359,9 @@ deparseConst(Const *node, deparse_expr_cxt *context)
bool isfloat = false;
bool needlabel;

/* Access extension metadata from fpinfo on baserel */
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *)(context->foreignrel->fdw_private);

if (node->constisnull)
{
appendStringInfoString(buf, "NULL");
Expand Down Expand Up @@ -1428,9 +1439,21 @@ deparseConst(Const *node, deparse_expr_cxt *context)
break;
}
if (needlabel)
appendStringInfo(buf, "::%s",
format_type_with_typemod(node->consttype,
node->consttypmod));
{
/*
* References to extension types need to be fully qualified,
* but references to built-in types shouldn't be.
*/
if (!is_builtin(node->consttype) &&
is_shippable(node->consttype, TypeRelationId, fpinfo->server, fpinfo->extensions))
{
appendStringInfo(buf, "::%s", format_type_be_qualified(node->consttype));
}
else
{
appendStringInfo(buf, "::%s", format_type_with_typemod(node->consttype, node->consttypmod));
}
}
}

/*
Expand Down
225 changes: 225 additions & 0 deletions contrib/postgres_fdw/expected/shippable.out
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,225 @@
-- ===================================================================
-- create FDW objects
-- ===================================================================
-- Error, extension isn't installed yet
ALTER SERVER loopback OPTIONS (ADD extensions 'cube');
ERROR: required extension "cube" is not installed
HINT: Extension must be installed locally before it can be used on a remote server.
-- Try again
CREATE EXTENSION cube;
ALTER SERVER loopback OPTIONS (ADD extensions 'cube');
ALTER SERVER loopback OPTIONS (DROP extensions);
-- ===================================================================
-- create objects used through FDW loopback server
-- ===================================================================
CREATE SCHEMA "SH 1";
CREATE TABLE "SH 1"."TBL 1" (
"C 1" int NOT NULL,
c2 int NOT NULL,
c3 cube,
c4 timestamptz
);
INSERT INTO "SH 1"."TBL 1"
SELECT id,
2 * id,
cube(id,2*id),
'1970-01-01'::timestamptz + ((id % 100) || ' days')::interval
FROM generate_series(1, 1000) id;
ANALYZE "SH 1"."TBL 1";
-- ===================================================================
-- create foreign table
-- ===================================================================
CREATE FOREIGN TABLE shft1 (
"C 1" int NOT NULL,
c2 int NOT NULL,
c3 cube,
c4 timestamptz
) SERVER loopback
OPTIONS (schema_name 'SH 1', table_name 'TBL 1');
-- ===================================================================
-- simple queries
-- ===================================================================
-- without operator shipping
EXPLAIN (COSTS false) SELECT * FROM shft1 LIMIT 1;
QUERY PLAN
-----------------------------
Limit
-> Foreign Scan on shft1
(2 rows)

EXPLAIN VERBOSE SELECT c2 FROM shft1 WHERE c3 && cube(1.5,2.5);
QUERY PLAN
---------------------------------------------------------------------
Foreign Scan on public.shft1 (cost=100.00..205.06 rows=15 width=4)
Output: c2
Filter: (shft1.c3 && '(1.5),(2.5)'::cube)
Remote SQL: SELECT c2, c3 FROM "SH 1"."TBL 1"
(4 rows)

SELECT c2 FROM shft1 WHERE c3 && cube(1.5,2.5);
c2
----
2
4
(2 rows)

EXPLAIN VERBOSE SELECT c2 FROM shft1 WHERE c3 && '(1.5),(2.5)'::cube;
QUERY PLAN
---------------------------------------------------------------------
Foreign Scan on public.shft1 (cost=100.00..205.06 rows=15 width=4)
Output: c2
Filter: (shft1.c3 && '(1.5),(2.5)'::cube)
Remote SQL: SELECT c2, c3 FROM "SH 1"."TBL 1"
(4 rows)

-- with operator shipping
ALTER SERVER loopback OPTIONS (ADD extensions 'cube');
EXPLAIN VERBOSE SELECT c2 FROM shft1 WHERE c3 && cube(1.5,2.5);
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Foreign Scan on public.shft1 (cost=100.00..146.86 rows=15 width=4)
Output: c2
Remote SQL: SELECT c2 FROM "SH 1"."TBL 1" WHERE ((c3 OPERATOR(public.&&) '(1.5),(2.5)'::public.cube))
(3 rows)

SELECT c2 FROM shft1 WHERE c3 && cube(1.5,2.5);
c2
----
2
4
(2 rows)

EXPLAIN VERBOSE SELECT c2 FROM shft1 WHERE c3 && '(1.5),(2.5)'::cube;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Foreign Scan on public.shft1 (cost=100.00..146.86 rows=15 width=4)
Output: c2
Remote SQL: SELECT c2 FROM "SH 1"."TBL 1" WHERE ((c3 OPERATOR(public.&&) '(1.5),(2.5)'::public.cube))
(3 rows)

EXPLAIN VERBOSE SELECT cube_dim(c3) FROM shft1 WHERE c3 && '(1.5),(2.5)'::cube;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Foreign Scan on public.shft1 (cost=100.00..128.43 rows=7 width=32)
Output: cube_dim(c3)
Remote SQL: SELECT c3 FROM "SH 1"."TBL 1" WHERE ((c3 OPERATOR(public.&&) '(1.5),(2.5)'::public.cube))
(3 rows)

SELECT cube_dim(c3) FROM shft1 WHERE c3 && '(1.5),(2.5)'::cube;
cube_dim
----------
1
1
(2 rows)

EXPLAIN VERBOSE SELECT c2 FROM shft1 WHERE cube_dim(c3) = 1 LIMIT 2;
QUERY PLAN
-------------------------------------------------------------------------------------
Limit (cost=100.00..107.22 rows=2 width=4)
Output: c2
-> Foreign Scan on public.shft1 (cost=100.00..154.18 rows=15 width=4)
Output: c2
Remote SQL: SELECT c2 FROM "SH 1"."TBL 1" WHERE ((public.cube_dim(c3) = 1))
(5 rows)

SELECT c2 FROM shft1 WHERE cube_dim(c3) = 1 LIMIT 2;
c2
----
2
4
(2 rows)

-- ===================================================================
-- add a second server with different extension shipping
-- ===================================================================
DO $d$
BEGIN
EXECUTE $$CREATE SERVER loopback_two FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname '$$||current_database()||$$',
port '$$||current_setting('port')||$$'
)$$;
END;
$d$;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback_two;
CREATE EXTENSION seg;
CREATE TABLE seg_local (
id integer,
s seg,
n text
);
INSERT INTO seg_local (id, s, n) VALUES (1, '1.0 .. 2.0', 'foo');
INSERT INTO seg_local (id, s, n) VALUES (2, '3.0 .. 4.0', 'bar');
INSERT INTO seg_local (id, s, n) VALUES (3, '5.0 .. 6.0', 'baz');
ANALYZE seg_local;
CREATE FOREIGN TABLE seg_remote_two (
id integer,
s seg,
n text
) SERVER loopback_two
OPTIONS (table_name 'seg_local');
SELECT id FROM seg_local WHERE s && '5.8 .. 6.2'::seg AND n = 'baz';
id
----
3
(1 row)

EXPLAIN VERBOSE SELECT id FROM seg_remote_two WHERE s && '5.8 .. 6.2'::seg AND n = 'baz';
QUERY PLAN
-----------------------------------------------------------------------------
Foreign Scan on public.seg_remote_two (cost=100.00..157.88 rows=1 width=4)
Output: id
Filter: (seg_remote_two.s && '5.8 .. 6.2'::seg)
Remote SQL: SELECT id, s FROM public.seg_local WHERE ((n = 'baz'::text))
(4 rows)

ALTER SERVER loopback_two OPTIONS (ADD extensions 'seg');
EXPLAIN VERBOSE SELECT id FROM seg_remote_two WHERE s && '5.8 .. 6.2'::seg AND n = 'baz';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.seg_remote_two (cost=100.00..153.89 rows=1 width=4)
Output: id
Remote SQL: SELECT id FROM public.seg_local WHERE ((s OPERATOR(public.&&) '5.8 .. 6.2'::public.seg)) AND ((n = 'baz'::text))
(3 rows)

CREATE FOREIGN TABLE seg_remote_one (
id integer,
s seg,
n text
) SERVER loopback
OPTIONS (table_name 'seg_local');
SELECT id FROM seg_remote_one WHERE s && '5.8 .. 6.2'::seg AND n = 'baz';
id
----
3
(1 row)

EXPLAIN VERBOSE SELECT id FROM seg_remote_one WHERE s && '5.8 .. 6.2'::seg AND n = 'baz';
QUERY PLAN
-----------------------------------------------------------------------------
Foreign Scan on public.seg_remote_one (cost=100.00..157.88 rows=1 width=4)
Output: id
Filter: (seg_remote_one.s && '5.8 .. 6.2'::seg)
Remote SQL: SELECT id, s FROM public.seg_local WHERE ((n = 'baz'::text))
(4 rows)

EXPLAIN VERBOSE SELECT id FROM seg_remote_two WHERE s && '5.8 .. 6.2'::seg AND n = 'baz';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.seg_remote_two (cost=100.00..153.89 rows=1 width=4)
Output: id
Remote SQL: SELECT id FROM public.seg_local WHERE ((s OPERATOR(public.&&) '5.8 .. 6.2'::public.seg)) AND ((n = 'baz'::text))
(3 rows)

-- ===================================================================
-- clean up
-- ===================================================================
DROP FOREIGN TABLE seg_remote_one, seg_remote_two;
DROP USER MAPPING FOR CURRENT_USER SERVER loopback_two;
DROP SERVER loopback_two;
DROP TABLE seg_local;
DROP FOREIGN TABLE shft1;
DROP TABLE "SH 1"."TBL 1";
DROP SCHEMA "SH 1";
DROP EXTENSION cube;
DROP EXTENSION seg;
ALTER SERVER loopback OPTIONS (DROP extensions);
Loading