Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb9f117d

Browse files
committed
Add regression tests for remote execution of extension operators/functions.
Rather than relying on other extensions to be available for installation,let's just add some test objects to the postgres_fdw extension itselfwithin the regression script.
1 parentd894941 commitb9f117d

File tree

2 files changed

+182
-64
lines changed

2 files changed

+182
-64
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 131 additions & 47 deletions
Original file line numberDiff line numberDiff line change
@@ -112,6 +112,14 @@ ALTER SERVER testserver1 OPTIONS (
112112
-- gsslib 'value',
113113
--replication 'value'
114114
);
115+
-- Error, invalid list syntax
116+
ALTER SERVER testserver1 OPTIONS (ADD extensions 'foo; bar');
117+
ERROR: parameter "extensions" must be a list of extension names
118+
-- OK but gets a warning
119+
ALTER SERVER testserver1 OPTIONS (ADD extensions 'foo, bar');
120+
WARNING: extension "foo" is not installed
121+
WARNING: extension "bar" is not installed
122+
ALTER SERVER testserver1 OPTIONS (DROP extensions);
115123
ALTER USER MAPPING FOR public SERVER testserver1
116124
OPTIONS (DROP user, DROP password);
117125
ALTER FOREIGN TABLE ft1 OPTIONS (schema_name 'S 1', table_name 'T 1');
@@ -335,53 +343,6 @@ SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
335343
fixed |
336344
(1 row)
337345

338-
-- user-defined operator/function
339-
CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$
340-
BEGIN
341-
RETURN abs($1);
342-
END
343-
$$ LANGUAGE plpgsql IMMUTABLE;
344-
CREATE OPERATOR === (
345-
LEFTARG = int,
346-
RIGHTARG = int,
347-
PROCEDURE = int4eq,
348-
COMMUTATOR = ===,
349-
NEGATOR = !==
350-
);
351-
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
352-
QUERY PLAN
353-
-------------------------------------------------------------------------
354-
Foreign Scan on public.ft1 t1
355-
Output: c1, c2, c3, c4, c5, c6, c7, c8
356-
Filter: (t1.c1 = postgres_fdw_abs(t1.c2))
357-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
358-
(4 rows)
359-
360-
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2;
361-
QUERY PLAN
362-
-------------------------------------------------------------------------
363-
Foreign Scan on public.ft1 t1
364-
Output: c1, c2, c3, c4, c5, c6, c7, c8
365-
Filter: (t1.c1 === t1.c2)
366-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
367-
(4 rows)
368-
369-
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
370-
QUERY PLAN
371-
---------------------------------------------------------------------------------------------------
372-
Foreign Scan on public.ft1 t1
373-
Output: c1, c2, c3, c4, c5, c6, c7, c8
374-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = abs(c2)))
375-
(3 rows)
376-
377-
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
378-
QUERY PLAN
379-
----------------------------------------------------------------------------------------------
380-
Foreign Scan on public.ft1 t1
381-
Output: c1, c2, c3, c4, c5, c6, c7, c8
382-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = c2))
383-
(3 rows)
384-
385346
-- ===================================================================
386347
-- WHERE with remotely-executable conditions
387348
-- ===================================================================
@@ -672,6 +633,129 @@ EXPLAIN (VERBOSE, COSTS false)
672633
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
673634
(6 rows)
674635

636+
-- user-defined operator/function
637+
CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$
638+
BEGIN
639+
RETURN abs($1);
640+
END
641+
$$ LANGUAGE plpgsql IMMUTABLE;
642+
CREATE OPERATOR === (
643+
LEFTARG = int,
644+
RIGHTARG = int,
645+
PROCEDURE = int4eq,
646+
COMMUTATOR = ===
647+
);
648+
-- built-in operators and functions can be shipped for remote execution
649+
EXPLAIN (VERBOSE, COSTS false)
650+
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
651+
QUERY PLAN
652+
--------------------------------------------------------------------------
653+
Aggregate
654+
Output: count(c3)
655+
-> Foreign Scan on public.ft1 t1
656+
Output: c3
657+
Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" = abs(c2)))
658+
(5 rows)
659+
660+
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
661+
count
662+
-------
663+
9
664+
(1 row)
665+
666+
EXPLAIN (VERBOSE, COSTS false)
667+
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = t1.c2;
668+
QUERY PLAN
669+
---------------------------------------------------------------------
670+
Aggregate
671+
Output: count(c3)
672+
-> Foreign Scan on public.ft1 t1
673+
Output: c3
674+
Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" = c2))
675+
(5 rows)
676+
677+
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = t1.c2;
678+
count
679+
-------
680+
9
681+
(1 row)
682+
683+
-- by default, user-defined ones cannot
684+
EXPLAIN (VERBOSE, COSTS false)
685+
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
686+
QUERY PLAN
687+
-----------------------------------------------------------
688+
Aggregate
689+
Output: count(c3)
690+
-> Foreign Scan on public.ft1 t1
691+
Output: c3
692+
Filter: (t1.c1 = postgres_fdw_abs(t1.c2))
693+
Remote SQL: SELECT "C 1", c2, c3 FROM "S 1"."T 1"
694+
(6 rows)
695+
696+
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
697+
count
698+
-------
699+
9
700+
(1 row)
701+
702+
EXPLAIN (VERBOSE, COSTS false)
703+
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2;
704+
QUERY PLAN
705+
-----------------------------------------------------------
706+
Aggregate
707+
Output: count(c3)
708+
-> Foreign Scan on public.ft1 t1
709+
Output: c3
710+
Filter: (t1.c1 === t1.c2)
711+
Remote SQL: SELECT "C 1", c2, c3 FROM "S 1"."T 1"
712+
(6 rows)
713+
714+
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2;
715+
count
716+
-------
717+
9
718+
(1 row)
719+
720+
-- but let's put them in an extension ...
721+
ALTER EXTENSION postgres_fdw ADD FUNCTION postgres_fdw_abs(int);
722+
ALTER EXTENSION postgres_fdw ADD OPERATOR === (int, int);
723+
ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw');
724+
-- ... now they can be shipped
725+
EXPLAIN (VERBOSE, COSTS false)
726+
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
727+
QUERY PLAN
728+
----------------------------------------------------------------------------------------------
729+
Aggregate
730+
Output: count(c3)
731+
-> Foreign Scan on public.ft1 t1
732+
Output: c3
733+
Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" = public.postgres_fdw_abs(c2)))
734+
(5 rows)
735+
736+
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
737+
count
738+
-------
739+
9
740+
(1 row)
741+
742+
EXPLAIN (VERBOSE, COSTS false)
743+
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2;
744+
QUERY PLAN
745+
----------------------------------------------------------------------------------------
746+
Aggregate
747+
Output: count(c3)
748+
-> Foreign Scan on public.ft1 t1
749+
Output: c3
750+
Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(public.===) c2))
751+
(5 rows)
752+
753+
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2;
754+
count
755+
-------
756+
9
757+
(1 row)
758+
675759
-- ===================================================================
676760
-- parameterized queries
677761
-- ===================================================================

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 51 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -121,8 +121,17 @@ ALTER SERVER testserver1 OPTIONS (
121121
-- gsslib 'value',
122122
--replication 'value'
123123
);
124+
125+
-- Error, invalid list syntax
126+
ALTER SERVER testserver1 OPTIONS (ADD extensions'foo; bar');
127+
128+
-- OK but gets a warning
129+
ALTER SERVER testserver1 OPTIONS (ADD extensions'foo, bar');
130+
ALTER SERVER testserver1 OPTIONS (DROP extensions);
131+
124132
ALTERUSER MAPPING FOR public SERVER testserver1
125133
OPTIONS (DROP user, DROP password);
134+
126135
ALTER FOREIGN TABLE ft1 OPTIONS (schema_name'S 1', table_name'T 1');
127136
ALTER FOREIGN TABLE ft2 OPTIONS (schema_name'S 1', table_name'T 1');
128137
ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (column_name'C 1');
@@ -169,23 +178,6 @@ SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1;
169178
WITH t1AS (SELECT*FROM ft1WHERE c1<=10)SELECTt2.c1,t2.c2,t2.c3,t2.c4FROM t1, ft2 t2WHEREt1.c1=t2.c1ORDER BYt1.c1;
170179
-- fixed values
171180
SELECT'fixed',NULLFROM ft1 t1WHERE c1=1;
172-
-- user-defined operator/function
173-
CREATEFUNCTIONpostgres_fdw_abs(int) RETURNSintAS $$
174-
BEGIN
175-
RETURN abs($1);
176-
END
177-
$$ LANGUAGE plpgsql IMMUTABLE;
178-
CREATE OPERATOR=== (
179-
LEFTARG=int,
180-
RIGHTARG=int,
181-
PROCEDURE= int4eq,
182-
COMMUTATOR====,
183-
NEGATOR=!==
184-
);
185-
EXPLAIN (VERBOSE, COSTS false)SELECT*FROM ft1 t1WHEREt1.c1= postgres_fdw_abs(t1.c2);
186-
EXPLAIN (VERBOSE, COSTS false)SELECT*FROM ft1 t1WHEREt1.c1===t1.c2;
187-
EXPLAIN (VERBOSE, COSTS false)SELECT*FROM ft1 t1WHEREt1.c1= abs(t1.c2);
188-
EXPLAIN (VERBOSE, COSTS false)SELECT*FROM ft1 t1WHEREt1.c1=t1.c2;
189181

190182
-- ===================================================================
191183
-- WHERE with remotely-executable conditions
@@ -222,6 +214,48 @@ EXPLAIN (VERBOSE, COSTS false)
222214
EXPLAIN (VERBOSE, COSTS false)
223215
SELECT*FROM ft2ORDER BYft2.c1,ft2.c3 collate"C";
224216

217+
-- user-defined operator/function
218+
CREATEFUNCTIONpostgres_fdw_abs(int) RETURNSintAS $$
219+
BEGIN
220+
RETURN abs($1);
221+
END
222+
$$ LANGUAGE plpgsql IMMUTABLE;
223+
CREATE OPERATOR=== (
224+
LEFTARG=int,
225+
RIGHTARG=int,
226+
PROCEDURE= int4eq,
227+
COMMUTATOR====
228+
);
229+
230+
-- built-in operators and functions can be shipped for remote execution
231+
EXPLAIN (VERBOSE, COSTS false)
232+
SELECTcount(c3)FROM ft1 t1WHEREt1.c1= abs(t1.c2);
233+
SELECTcount(c3)FROM ft1 t1WHEREt1.c1= abs(t1.c2);
234+
EXPLAIN (VERBOSE, COSTS false)
235+
SELECTcount(c3)FROM ft1 t1WHEREt1.c1=t1.c2;
236+
SELECTcount(c3)FROM ft1 t1WHEREt1.c1=t1.c2;
237+
238+
-- by default, user-defined ones cannot
239+
EXPLAIN (VERBOSE, COSTS false)
240+
SELECTcount(c3)FROM ft1 t1WHEREt1.c1= postgres_fdw_abs(t1.c2);
241+
SELECTcount(c3)FROM ft1 t1WHEREt1.c1= postgres_fdw_abs(t1.c2);
242+
EXPLAIN (VERBOSE, COSTS false)
243+
SELECTcount(c3)FROM ft1 t1WHEREt1.c1===t1.c2;
244+
SELECTcount(c3)FROM ft1 t1WHEREt1.c1===t1.c2;
245+
246+
-- but let's put them in an extension ...
247+
ALTER EXTENSION postgres_fdw ADD FUNCTION postgres_fdw_abs(int);
248+
ALTER EXTENSION postgres_fdw ADD OPERATOR=== (int,int);
249+
ALTER SERVER loopback OPTIONS (ADD extensions'postgres_fdw');
250+
251+
-- ... now they can be shipped
252+
EXPLAIN (VERBOSE, COSTS false)
253+
SELECTcount(c3)FROM ft1 t1WHEREt1.c1= postgres_fdw_abs(t1.c2);
254+
SELECTcount(c3)FROM ft1 t1WHEREt1.c1= postgres_fdw_abs(t1.c2);
255+
EXPLAIN (VERBOSE, COSTS false)
256+
SELECTcount(c3)FROM ft1 t1WHEREt1.c1===t1.c2;
257+
SELECTcount(c3)FROM ft1 t1WHEREt1.c1===t1.c2;
258+
225259
-- ===================================================================
226260
-- parameterized queries
227261
-- ===================================================================

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp