diff options
Diffstat (limited to 'contrib/postgres_fdw/sql/postgres_fdw.sql')
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 89 |
1 files changed, 10 insertions, 79 deletions
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 20a535b99d8..31b6c685b55 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -352,7 +352,7 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c3 IS NULL; -- Nu EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c3 IS NOT NULL; -- NullTest EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = -c1; -- OpExpr(l) -EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE (c1 IS NOT NULL) IS DISTINCT FROM (c1 IS NOT NULL); -- DistinctExpr +EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE (c3 IS NOT NULL) IS DISTINCT FROM (c3 IS NOT NULL); -- DistinctExpr EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1 + 0]); -- ScalarArrayOpExpr EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = (ARRAY[c1,c2,3])[1]; -- SubscriptingRef EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c6 = E'foo''s\\bar'; -- check special chars @@ -458,6 +458,15 @@ SELECT * FROM ft1 WHERE CASE c3 WHEN c6 THEN true ELSE c3 < 'bar' END; EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 WHERE CASE c3 COLLATE "C" WHEN c6 THEN true ELSE c3 < 'bar' END; +-- Test array type conversion pushdown +SET plan_cache_mode = force_generic_plan; +PREPARE s(varchar[]) AS SELECT count(*) FROM ft2 WHERE c6 = ANY ($1); +EXPLAIN (VERBOSE, COSTS OFF) +EXECUTE s(ARRAY['1','2']); +EXECUTE s(ARRAY['1','2']); +DEALLOCATE s; +RESET plan_cache_mode; + -- a regconfig constant referring to this text search configuration -- is initially unshippable CREATE TEXT SEARCH CONFIGURATION public.custom_search @@ -4201,84 +4210,6 @@ DROP FOREIGN TABLE remote_application_name; DROP VIEW my_application_name; -- =================================================================== --- test read-only and/or deferrable transactions --- =================================================================== -CREATE TABLE loct (f1 int, f2 text); -CREATE FUNCTION locf() RETURNS SETOF loct LANGUAGE SQL AS - 'UPDATE public.loct SET f2 = f2 || f2 RETURNING *'; -CREATE VIEW locv AS SELECT t.* FROM locf() t; -CREATE FOREIGN TABLE remt (f1 int, f2 text) - SERVER loopback OPTIONS (table_name 'locv'); -CREATE FOREIGN TABLE remt2 (f1 int, f2 text) - SERVER loopback2 OPTIONS (table_name 'locv'); -INSERT INTO loct VALUES (1, 'foo'), (2, 'bar'); - -START TRANSACTION READ ONLY; -SAVEPOINT s; -SELECT * FROM remt; -- should fail -ROLLBACK TO s; -RELEASE SAVEPOINT s; -SELECT * FROM remt; -- should fail -ROLLBACK; - -START TRANSACTION; -SAVEPOINT s; -SET transaction_read_only = on; -SELECT * FROM remt; -- should fail -ROLLBACK TO s; -RELEASE SAVEPOINT s; -SET transaction_read_only = on; -SELECT * FROM remt; -- should fail -ROLLBACK; - -START TRANSACTION; -SAVEPOINT s; -SELECT * FROM remt; -- should work -SET transaction_read_only = on; -SELECT * FROM remt; -- should fail -ROLLBACK TO s; -RELEASE SAVEPOINT s; -SELECT * FROM remt; -- should work -SET transaction_read_only = on; -SELECT * FROM remt; -- should fail -ROLLBACK; - -START TRANSACTION; -SAVEPOINT s; -SELECT * FROM remt; -- should work -SET transaction_read_only = on; -SELECT * FROM remt2; -- should fail -ROLLBACK TO s; -RELEASE SAVEPOINT s; -SELECT * FROM remt; -- should work -SET transaction_read_only = on; -SELECT * FROM remt2; -- should fail -ROLLBACK; - -DROP FOREIGN TABLE remt; -CREATE FOREIGN TABLE remt (f1 int, f2 text) - SERVER loopback OPTIONS (table_name 'loct'); - -START TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY; -SELECT * FROM remt; -COMMIT; - -START TRANSACTION ISOLATION LEVEL SERIALIZABLE DEFERRABLE; -SELECT * FROM remt; -COMMIT; - -START TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; -SELECT * FROM remt; -COMMIT; - --- Clean up -DROP FOREIGN TABLE remt; -DROP FOREIGN TABLE remt2; -DROP VIEW locv; -DROP FUNCTION locf(); -DROP TABLE loct; - --- =================================================================== -- test parallel commit and parallel abort -- =================================================================== ALTER SERVER loopback OPTIONS (ADD parallel_commit 'true'); |