1-- =================================================================== 2-- create FDW objects 3-- =================================================================== 4 5CREATE EXTENSION postgres_fdw; 6 7CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw; 8DO $d$ 9 BEGIN 10 EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw 11 OPTIONS (dbname '$$||current_database()||$$', 12 port '$$||current_setting('port')||$$' 13 )$$; 14 EXECUTE $$CREATE SERVER loopback2 FOREIGN DATA WRAPPER postgres_fdw 15 OPTIONS (dbname '$$||current_database()||$$', 16 port '$$||current_setting('port')||$$' 17 )$$; 18 EXECUTE $$CREATE SERVER loopback3 FOREIGN DATA WRAPPER postgres_fdw 19 OPTIONS (dbname '$$||current_database()||$$', 20 port '$$||current_setting('port')||$$' 21 )$$; 22 END; 23$d$; 24 25CREATE USER MAPPING FOR public SERVER testserver1 26 OPTIONS (user 'value', password 'value'); 27CREATE USER MAPPING FOR CURRENT_USER SERVER loopback; 28CREATE USER MAPPING FOR CURRENT_USER SERVER loopback2; 29CREATE USER MAPPING FOR public SERVER loopback3; 30 31-- =================================================================== 32-- create objects used through FDW loopback server 33-- =================================================================== 34CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz'); 35CREATE SCHEMA "S 1"; 36CREATE TABLE "S 1"."T 1" ( 37 "C 1" int NOT NULL, 38 c2 int NOT NULL, 39 c3 text, 40 c4 timestamptz, 41 c5 timestamp, 42 c6 varchar(10), 43 c7 char(10), 44 c8 user_enum, 45 CONSTRAINT t1_pkey PRIMARY KEY ("C 1") 46); 47CREATE TABLE "S 1"."T 2" ( 48 c1 int NOT NULL, 49 c2 text, 50 CONSTRAINT t2_pkey PRIMARY KEY (c1) 51); 52CREATE TABLE "S 1"."T 3" ( 53 c1 int NOT NULL, 54 c2 int NOT NULL, 55 c3 text, 56 CONSTRAINT t3_pkey PRIMARY KEY (c1) 57); 58CREATE TABLE "S 1"."T 4" ( 59 c1 int NOT NULL, 60 c2 int NOT NULL, 61 c3 text, 62 CONSTRAINT t4_pkey PRIMARY KEY (c1) 63); 64 65-- Disable autovacuum for these tables to avoid unexpected effects of that 66ALTER TABLE "S 1"."T 1" SET (autovacuum_enabled = 'false'); 67ALTER TABLE "S 1"."T 2" SET (autovacuum_enabled = 'false'); 68ALTER TABLE "S 1"."T 3" SET (autovacuum_enabled = 'false'); 69ALTER TABLE "S 1"."T 4" SET (autovacuum_enabled = 'false'); 70 71INSERT INTO "S 1"."T 1" 72 SELECT id, 73 id % 10, 74 to_char(id, 'FM00000'), 75 '1970-01-01'::timestamptz + ((id % 100) || ' days')::interval, 76 '1970-01-01'::timestamp + ((id % 100) || ' days')::interval, 77 id % 10, 78 id % 10, 79 'foo'::user_enum 80 FROM generate_series(1, 1000) id; 81INSERT INTO "S 1"."T 2" 82 SELECT id, 83 'AAA' || to_char(id, 'FM000') 84 FROM generate_series(1, 100) id; 85INSERT INTO "S 1"."T 3" 86 SELECT id, 87 id + 1, 88 'AAA' || to_char(id, 'FM000') 89 FROM generate_series(1, 100) id; 90DELETE FROM "S 1"."T 3" WHERE c1 % 2 != 0; -- delete for outer join tests 91INSERT INTO "S 1"."T 4" 92 SELECT id, 93 id + 1, 94 'AAA' || to_char(id, 'FM000') 95 FROM generate_series(1, 100) id; 96DELETE FROM "S 1"."T 4" WHERE c1 % 3 != 0; -- delete for outer join tests 97 98ANALYZE "S 1"."T 1"; 99ANALYZE "S 1"."T 2"; 100ANALYZE "S 1"."T 3"; 101ANALYZE "S 1"."T 4"; 102 103-- =================================================================== 104-- create foreign tables 105-- =================================================================== 106CREATE FOREIGN TABLE ft1 ( 107 c0 int, 108 c1 int NOT NULL, 109 c2 int NOT NULL, 110 c3 text, 111 c4 timestamptz, 112 c5 timestamp, 113 c6 varchar(10), 114 c7 char(10) default 'ft1', 115 c8 user_enum 116) SERVER loopback; 117ALTER FOREIGN TABLE ft1 DROP COLUMN c0; 118 119CREATE FOREIGN TABLE ft2 ( 120 c1 int NOT NULL, 121 c2 int NOT NULL, 122 cx int, 123 c3 text, 124 c4 timestamptz, 125 c5 timestamp, 126 c6 varchar(10), 127 c7 char(10) default 'ft2', 128 c8 user_enum 129) SERVER loopback; 130ALTER FOREIGN TABLE ft2 DROP COLUMN cx; 131 132CREATE FOREIGN TABLE ft4 ( 133 c1 int NOT NULL, 134 c2 int NOT NULL, 135 c3 text 136) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 3'); 137 138CREATE FOREIGN TABLE ft5 ( 139 c1 int NOT NULL, 140 c2 int NOT NULL, 141 c3 text 142) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 4'); 143 144CREATE FOREIGN TABLE ft6 ( 145 c1 int NOT NULL, 146 c2 int NOT NULL, 147 c3 text 148) SERVER loopback2 OPTIONS (schema_name 'S 1', table_name 'T 4'); 149 150CREATE FOREIGN TABLE ft7 ( 151 c1 int NOT NULL, 152 c2 int NOT NULL, 153 c3 text 154) SERVER loopback3 OPTIONS (schema_name 'S 1', table_name 'T 4'); 155 156-- =================================================================== 157-- tests for validator 158-- =================================================================== 159-- requiressl and some other parameters are omitted because 160-- valid values for them depend on configure options 161ALTER SERVER testserver1 OPTIONS ( 162 use_remote_estimate 'false', 163 updatable 'true', 164 fdw_startup_cost '123.456', 165 fdw_tuple_cost '0.123', 166 service 'value', 167 connect_timeout 'value', 168 dbname 'value', 169 host 'value', 170 hostaddr 'value', 171 port 'value', 172 --client_encoding 'value', 173 application_name 'value', 174 --fallback_application_name 'value', 175 keepalives 'value', 176 keepalives_idle 'value', 177 keepalives_interval 'value', 178 tcp_user_timeout 'value', 179 -- requiressl 'value', 180 sslcompression 'value', 181 sslmode 'value', 182 sslcert 'value', 183 sslkey 'value', 184 sslrootcert 'value', 185 sslcrl 'value', 186 --requirepeer 'value', 187 krbsrvname 'value', 188 gsslib 'value' 189 --replication 'value' 190); 191 192-- Error, invalid list syntax 193ALTER SERVER testserver1 OPTIONS (ADD extensions 'foo; bar'); 194 195-- OK but gets a warning 196ALTER SERVER testserver1 OPTIONS (ADD extensions 'foo, bar'); 197ALTER SERVER testserver1 OPTIONS (DROP extensions); 198 199ALTER USER MAPPING FOR public SERVER testserver1 200 OPTIONS (DROP user, DROP password); 201 202-- Attempt to add a valid option that's not allowed in a user mapping 203ALTER USER MAPPING FOR public SERVER testserver1 204 OPTIONS (ADD sslmode 'require'); 205 206-- But we can add valid ones fine 207ALTER USER MAPPING FOR public SERVER testserver1 208 OPTIONS (ADD sslpassword 'dummy'); 209 210-- Ensure valid options we haven't used in a user mapping yet are 211-- permitted to check validation. 212ALTER USER MAPPING FOR public SERVER testserver1 213 OPTIONS (ADD sslkey 'value', ADD sslcert 'value'); 214 215ALTER FOREIGN TABLE ft1 OPTIONS (schema_name 'S 1', table_name 'T 1'); 216ALTER FOREIGN TABLE ft2 OPTIONS (schema_name 'S 1', table_name 'T 1'); 217ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (column_name 'C 1'); 218ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1'); 219\det+ 220 221-- Test that alteration of server options causes reconnection 222-- Remote's errors might be non-English, so hide them to ensure stable results 223\set VERBOSITY terse 224SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work 225ALTER SERVER loopback OPTIONS (SET dbname 'no such database'); 226SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should fail 227DO $d$ 228 BEGIN 229 EXECUTE $$ALTER SERVER loopback 230 OPTIONS (SET dbname '$$||current_database()||$$')$$; 231 END; 232$d$; 233SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work again 234 235-- Test that alteration of user mapping options causes reconnection 236ALTER USER MAPPING FOR CURRENT_USER SERVER loopback 237 OPTIONS (ADD user 'no such user'); 238SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should fail 239ALTER USER MAPPING FOR CURRENT_USER SERVER loopback 240 OPTIONS (DROP user); 241SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work again 242\set VERBOSITY default 243 244-- Now we should be able to run ANALYZE. 245-- To exercise multiple code paths, we use local stats on ft1 246-- and remote-estimate mode on ft2. 247ANALYZE ft1; 248ALTER FOREIGN TABLE ft2 OPTIONS (use_remote_estimate 'true'); 249 250-- =================================================================== 251-- simple queries 252-- =================================================================== 253-- single table without alias 254EXPLAIN (COSTS OFF) SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10; 255SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10; 256-- single table with alias - also test that tableoid sort is not pushed to remote side 257EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tableoid OFFSET 100 LIMIT 10; 258SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tableoid OFFSET 100 LIMIT 10; 259-- whole-row reference 260EXPLAIN (VERBOSE, COSTS OFF) SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; 261SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; 262-- empty result 263SELECT * FROM ft1 WHERE false; 264-- with WHERE clause 265EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1'; 266SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1'; 267-- with FOR UPDATE/SHARE 268EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = 101 FOR UPDATE; 269SELECT * FROM ft1 t1 WHERE c1 = 101 FOR UPDATE; 270EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = 102 FOR SHARE; 271SELECT * FROM ft1 t1 WHERE c1 = 102 FOR SHARE; 272-- aggregate 273SELECT COUNT(*) FROM ft1 t1; 274-- subquery 275SELECT * FROM ft1 t1 WHERE t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 <= 10) ORDER BY c1; 276-- subquery+MAX 277SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1; 278-- used in CTE 279WITH t1 AS (SELECT * FROM ft1 WHERE c1 <= 10) SELECT t2.c1, t2.c2, t2.c3, t2.c4 FROM t1, ft2 t2 WHERE t1.c1 = t2.c1 ORDER BY t1.c1; 280-- fixed values 281SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1; 282-- Test forcing the remote server to produce sorted data for a merge join. 283SET enable_hashjoin TO false; 284SET enable_nestloop TO false; 285-- inner join; expressions in the clauses appear in the equivalence class list 286EXPLAIN (VERBOSE, COSTS OFF) 287 SELECT t1.c1, t2."C 1" FROM ft2 t1 JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") OFFSET 100 LIMIT 10; 288SELECT t1.c1, t2."C 1" FROM ft2 t1 JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") OFFSET 100 LIMIT 10; 289-- outer join; expressions in the clauses do not appear in equivalence class 290-- list but no output change as compared to the previous query 291EXPLAIN (VERBOSE, COSTS OFF) 292 SELECT t1.c1, t2."C 1" FROM ft2 t1 LEFT JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") OFFSET 100 LIMIT 10; 293SELECT t1.c1, t2."C 1" FROM ft2 t1 LEFT JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") OFFSET 100 LIMIT 10; 294-- A join between local table and foreign join. ORDER BY clause is added to the 295-- foreign join so that the local table can be joined using merge join strategy. 296EXPLAIN (VERBOSE, COSTS OFF) 297 SELECT t1."C 1" FROM "S 1"."T 1" t1 left join ft1 t2 join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10; 298SELECT t1."C 1" FROM "S 1"."T 1" t1 left join ft1 t2 join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10; 299-- Test similar to above, except that the full join prevents any equivalence 300-- classes from being merged. This produces single relation equivalence classes 301-- included in join restrictions. 302EXPLAIN (VERBOSE, COSTS OFF) 303 SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 left join ft1 t2 full join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10; 304SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 left join ft1 t2 full join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10; 305-- Test similar to above with all full outer joins 306EXPLAIN (VERBOSE, COSTS OFF) 307 SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 full join ft1 t2 full join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10; 308SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 full join ft1 t2 full join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10; 309RESET enable_hashjoin; 310RESET enable_nestloop; 311 312-- Test executing assertion in estimate_path_cost_size() that makes sure that 313-- retrieved_rows for foreign rel re-used to cost pre-sorted foreign paths is 314-- a sensible value even when the rel has tuples=0 315CREATE TABLE loct_empty (c1 int NOT NULL, c2 text); 316CREATE FOREIGN TABLE ft_empty (c1 int NOT NULL, c2 text) 317 SERVER loopback OPTIONS (table_name 'loct_empty'); 318INSERT INTO loct_empty 319 SELECT id, 'AAA' || to_char(id, 'FM000') FROM generate_series(1, 100) id; 320DELETE FROM loct_empty; 321ANALYZE ft_empty; 322EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft_empty ORDER BY c1; 323 324-- =================================================================== 325-- WHERE with remotely-executable conditions 326-- =================================================================== 327EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 1; -- Var, OpExpr(b), Const 328EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2 = 0; -- BoolExpr 329EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NULL; -- NullTest 330EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL; -- NullTest 331EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr 332EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = -c1; -- OpExpr(l) 333EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE (c1 IS NOT NULL) IS DISTINCT FROM (c1 IS NOT NULL); -- DistinctExpr 334EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1 + 0]); -- ScalarArrayOpExpr 335EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = (ARRAY[c1,c2,3])[1]; -- SubscriptingRef 336EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c6 = E'foo''s\\bar'; -- check special chars 337EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c8 = 'foo'; -- can't be sent to remote 338-- parameterized remote path for foreign table 339EXPLAIN (VERBOSE, COSTS OFF) 340 SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2; 341SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2; 342 343-- check both safe and unsafe join conditions 344EXPLAIN (VERBOSE, COSTS OFF) 345 SELECT * FROM ft2 a, ft2 b 346 WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7); 347SELECT * FROM ft2 a, ft2 b 348WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7); 349-- bug before 9.3.5 due to sloppy handling of remote-estimate parameters 350SELECT * FROM ft1 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft2 WHERE c1 < 5)); 351SELECT * FROM ft2 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft1 WHERE c1 < 5)); 352-- we should not push order by clause with volatile expressions or unsafe 353-- collations 354EXPLAIN (VERBOSE, COSTS OFF) 355 SELECT * FROM ft2 ORDER BY ft2.c1, random(); 356EXPLAIN (VERBOSE, COSTS OFF) 357 SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C"; 358 359-- user-defined operator/function 360CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$ 361BEGIN 362RETURN abs($1); 363END 364$$ LANGUAGE plpgsql IMMUTABLE; 365CREATE OPERATOR === ( 366 LEFTARG = int, 367 RIGHTARG = int, 368 PROCEDURE = int4eq, 369 COMMUTATOR = === 370); 371 372-- built-in operators and functions can be shipped for remote execution 373EXPLAIN (VERBOSE, COSTS OFF) 374 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = abs(t1.c2); 375SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = abs(t1.c2); 376EXPLAIN (VERBOSE, COSTS OFF) 377 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = t1.c2; 378SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = t1.c2; 379 380-- by default, user-defined ones cannot 381EXPLAIN (VERBOSE, COSTS OFF) 382 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2); 383SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2); 384EXPLAIN (VERBOSE, COSTS OFF) 385 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 386SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 387 388-- ORDER BY can be shipped, though 389EXPLAIN (VERBOSE, COSTS OFF) 390 SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1; 391SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1; 392 393-- but let's put them in an extension ... 394ALTER EXTENSION postgres_fdw ADD FUNCTION postgres_fdw_abs(int); 395ALTER EXTENSION postgres_fdw ADD OPERATOR === (int, int); 396ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw'); 397 398-- ... now they can be shipped 399EXPLAIN (VERBOSE, COSTS OFF) 400 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2); 401SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2); 402EXPLAIN (VERBOSE, COSTS OFF) 403 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 404SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 405 406-- and both ORDER BY and LIMIT can be shipped 407EXPLAIN (VERBOSE, COSTS OFF) 408 SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1; 409SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1; 410 411-- =================================================================== 412-- JOIN queries 413-- =================================================================== 414-- Analyze ft4 and ft5 so that we have better statistics. These tables do not 415-- have use_remote_estimate set. 416ANALYZE ft4; 417ANALYZE ft5; 418 419-- join two tables 420EXPLAIN (VERBOSE, COSTS OFF) 421SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; 422SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; 423-- join three tables 424EXPLAIN (VERBOSE, COSTS OFF) 425SELECT t1.c1, t2.c2, t3.c3 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) JOIN ft4 t3 ON (t3.c1 = t1.c1) ORDER BY t1.c3, t1.c1 OFFSET 10 LIMIT 10; 426SELECT t1.c1, t2.c2, t3.c3 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) JOIN ft4 t3 ON (t3.c1 = t1.c1) ORDER BY t1.c3, t1.c1 OFFSET 10 LIMIT 10; 427-- left outer join 428EXPLAIN (VERBOSE, COSTS OFF) 429SELECT t1.c1, t2.c1 FROM ft4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; 430SELECT t1.c1, t2.c1 FROM ft4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; 431-- left outer join three tables 432EXPLAIN (VERBOSE, COSTS OFF) 433SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; 434SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; 435-- left outer join + placement of clauses. 436-- clauses within the nullable side are not pulled up, but top level clause on 437-- non-nullable side is pushed into non-nullable side 438EXPLAIN (VERBOSE, COSTS OFF) 439SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM ft4 t1 LEFT JOIN (SELECT * FROM ft5 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1) WHERE t1.c1 < 10; 440SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM ft4 t1 LEFT JOIN (SELECT * FROM ft5 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1) WHERE t1.c1 < 10; 441-- clauses within the nullable side are not pulled up, but the top level clause 442-- on nullable side is not pushed down into nullable side 443EXPLAIN (VERBOSE, COSTS OFF) 444SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM ft4 t1 LEFT JOIN (SELECT * FROM ft5 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1) 445 WHERE (t2.c1 < 10 OR t2.c1 IS NULL) AND t1.c1 < 10; 446SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM ft4 t1 LEFT JOIN (SELECT * FROM ft5 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1) 447 WHERE (t2.c1 < 10 OR t2.c1 IS NULL) AND t1.c1 < 10; 448-- right outer join 449EXPLAIN (VERBOSE, COSTS OFF) 450SELECT t1.c1, t2.c1 FROM ft5 t1 RIGHT JOIN ft4 t2 ON (t1.c1 = t2.c1) ORDER BY t2.c1, t1.c1 OFFSET 10 LIMIT 10; 451SELECT t1.c1, t2.c1 FROM ft5 t1 RIGHT JOIN ft4 t2 ON (t1.c1 = t2.c1) ORDER BY t2.c1, t1.c1 OFFSET 10 LIMIT 10; 452-- right outer join three tables 453EXPLAIN (VERBOSE, COSTS OFF) 454SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; 455SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; 456-- full outer join 457EXPLAIN (VERBOSE, COSTS OFF) 458SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10; 459SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10; 460-- full outer join with restrictions on the joining relations 461-- a. the joining relations are both base relations 462EXPLAIN (VERBOSE, COSTS OFF) 463SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1; 464SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1; 465EXPLAIN (VERBOSE, COSTS OFF) 466SELECT 1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t2 ON (TRUE) OFFSET 10 LIMIT 10; 467SELECT 1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t2 ON (TRUE) OFFSET 10 LIMIT 10; 468-- b. one of the joining relations is a base relation and the other is a join 469-- relation 470EXPLAIN (VERBOSE, COSTS OFF) 471SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT t2.c1, t3.c1 FROM ft4 t2 LEFT JOIN ft5 t3 ON (t2.c1 = t3.c1) WHERE (t2.c1 between 50 and 60)) ss(a, b) ON (t1.c1 = ss.a) ORDER BY t1.c1, ss.a, ss.b; 472SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT t2.c1, t3.c1 FROM ft4 t2 LEFT JOIN ft5 t3 ON (t2.c1 = t3.c1) WHERE (t2.c1 between 50 and 60)) ss(a, b) ON (t1.c1 = ss.a) ORDER BY t1.c1, ss.a, ss.b; 473-- c. test deparsing the remote query as nested subqueries 474EXPLAIN (VERBOSE, COSTS OFF) 475SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT t2.c1, t3.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t2 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t3 ON (t2.c1 = t3.c1) WHERE t2.c1 IS NULL OR t2.c1 IS NOT NULL) ss(a, b) ON (t1.c1 = ss.a) ORDER BY t1.c1, ss.a, ss.b; 476SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT t2.c1, t3.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t2 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t3 ON (t2.c1 = t3.c1) WHERE t2.c1 IS NULL OR t2.c1 IS NOT NULL) ss(a, b) ON (t1.c1 = ss.a) ORDER BY t1.c1, ss.a, ss.b; 477-- d. test deparsing rowmarked relations as subqueries 478EXPLAIN (VERBOSE, COSTS OFF) 479SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM "S 1"."T 3" WHERE c1 = 50) t1 INNER JOIN (SELECT t2.c1, t3.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t2 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t3 ON (t2.c1 = t3.c1) WHERE t2.c1 IS NULL OR t2.c1 IS NOT NULL) ss(a, b) ON (TRUE) ORDER BY t1.c1, ss.a, ss.b FOR UPDATE OF t1; 480SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM "S 1"."T 3" WHERE c1 = 50) t1 INNER JOIN (SELECT t2.c1, t3.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t2 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t3 ON (t2.c1 = t3.c1) WHERE t2.c1 IS NULL OR t2.c1 IS NOT NULL) ss(a, b) ON (TRUE) ORDER BY t1.c1, ss.a, ss.b FOR UPDATE OF t1; 481-- full outer join + inner join 482EXPLAIN (VERBOSE, COSTS OFF) 483SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10; 484SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10; 485-- full outer join three tables 486EXPLAIN (VERBOSE, COSTS OFF) 487SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; 488SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; 489-- full outer join + right outer join 490EXPLAIN (VERBOSE, COSTS OFF) 491SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; 492SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; 493-- right outer join + full outer join 494EXPLAIN (VERBOSE, COSTS OFF) 495SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; 496SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; 497-- full outer join + left outer join 498EXPLAIN (VERBOSE, COSTS OFF) 499SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; 500SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; 501-- left outer join + full outer join 502EXPLAIN (VERBOSE, COSTS OFF) 503SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; 504SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; 505SET enable_memoize TO off; 506-- right outer join + left outer join 507EXPLAIN (VERBOSE, COSTS OFF) 508SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; 509SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; 510RESET enable_memoize; 511-- left outer join + right outer join 512EXPLAIN (VERBOSE, COSTS OFF) 513SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; 514SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; 515-- full outer join + WHERE clause, only matched rows 516EXPLAIN (VERBOSE, COSTS OFF) 517SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) WHERE (t1.c1 = t2.c1 OR t1.c1 IS NULL) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; 518SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) WHERE (t1.c1 = t2.c1 OR t1.c1 IS NULL) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; 519-- full outer join + WHERE clause with shippable extensions set 520EXPLAIN (VERBOSE, COSTS OFF) 521SELECT t1.c1, t2.c2, t1.c3 FROM ft1 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE postgres_fdw_abs(t1.c1) > 0 OFFSET 10 LIMIT 10; 522ALTER SERVER loopback OPTIONS (DROP extensions); 523-- full outer join + WHERE clause with shippable extensions not set 524EXPLAIN (VERBOSE, COSTS OFF) 525SELECT t1.c1, t2.c2, t1.c3 FROM ft1 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE postgres_fdw_abs(t1.c1) > 0 OFFSET 10 LIMIT 10; 526ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw'); 527-- join two tables with FOR UPDATE clause 528-- tests whole-row reference for row marks 529EXPLAIN (VERBOSE, COSTS OFF) 530SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE OF t1; 531SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE OF t1; 532EXPLAIN (VERBOSE, COSTS OFF) 533SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE; 534SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE; 535-- join two tables with FOR SHARE clause 536EXPLAIN (VERBOSE, COSTS OFF) 537SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE OF t1; 538SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE OF t1; 539EXPLAIN (VERBOSE, COSTS OFF) 540SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE; 541SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE; 542-- join in CTE 543EXPLAIN (VERBOSE, COSTS OFF) 544WITH t (c1_1, c1_3, c2_1) AS MATERIALIZED (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10; 545WITH t (c1_1, c1_3, c2_1) AS MATERIALIZED (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10; 546-- ctid with whole-row reference 547EXPLAIN (VERBOSE, COSTS OFF) 548SELECT t1.ctid, t1, t2, t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; 549-- SEMI JOIN, not pushed down 550EXPLAIN (VERBOSE, COSTS OFF) 551SELECT t1.c1 FROM ft1 t1 WHERE EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c1) ORDER BY t1.c1 OFFSET 100 LIMIT 10; 552SELECT t1.c1 FROM ft1 t1 WHERE EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c1) ORDER BY t1.c1 OFFSET 100 LIMIT 10; 553-- ANTI JOIN, not pushed down 554EXPLAIN (VERBOSE, COSTS OFF) 555SELECT t1.c1 FROM ft1 t1 WHERE NOT EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c2) ORDER BY t1.c1 OFFSET 100 LIMIT 10; 556SELECT t1.c1 FROM ft1 t1 WHERE NOT EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c2) ORDER BY t1.c1 OFFSET 100 LIMIT 10; 557-- CROSS JOIN can be pushed down 558EXPLAIN (VERBOSE, COSTS OFF) 559SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10; 560SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10; 561-- different server, not pushed down. No result expected. 562EXPLAIN (VERBOSE, COSTS OFF) 563SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN ft6 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10; 564SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN ft6 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10; 565-- unsafe join conditions (c8 has a UDT), not pushed down. Practically a CROSS 566-- JOIN since c8 in both tables has same value. 567EXPLAIN (VERBOSE, COSTS OFF) 568SELECT t1.c1, t2.c1 FROM ft1 t1 LEFT JOIN ft2 t2 ON (t1.c8 = t2.c8) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10; 569SELECT t1.c1, t2.c1 FROM ft1 t1 LEFT JOIN ft2 t2 ON (t1.c8 = t2.c8) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10; 570-- unsafe conditions on one side (c8 has a UDT), not pushed down. 571EXPLAIN (VERBOSE, COSTS OFF) 572SELECT t1.c1, t2.c1 FROM ft1 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = 'foo' ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; 573SELECT t1.c1, t2.c1 FROM ft1 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = 'foo' ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; 574-- join where unsafe to pushdown condition in WHERE clause has a column not 575-- in the SELECT clause. In this test unsafe clause needs to have column 576-- references from both joining sides so that the clause is not pushed down 577-- into one of the joining sides. 578EXPLAIN (VERBOSE, COSTS OFF) 579SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = t2.c8 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; 580SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = t2.c8 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; 581-- Aggregate after UNION, for testing setrefs 582EXPLAIN (VERBOSE, COSTS OFF) 583SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) UNION SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) AS t (t1c1, t2c1) GROUP BY t1c1 ORDER BY t1c1 OFFSET 100 LIMIT 10; 584SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) UNION SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) AS t (t1c1, t2c1) GROUP BY t1c1 ORDER BY t1c1 OFFSET 100 LIMIT 10; 585-- join with lateral reference 586EXPLAIN (VERBOSE, COSTS OFF) 587SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10; 588SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10; 589 590-- non-Var items in targetlist of the nullable rel of a join preventing 591-- push-down in some cases 592-- unable to push {ft1, ft2} 593EXPLAIN (VERBOSE, COSTS OFF) 594SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15; 595SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15; 596 597-- ok to push {ft1, ft2} but not {ft1, ft2, ft4} 598EXPLAIN (VERBOSE, COSTS OFF) 599SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15; 600SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15; 601 602-- join with nullable side with some columns with null values 603UPDATE ft5 SET c3 = null where c1 % 9 = 0; 604EXPLAIN (VERBOSE, COSTS OFF) 605SELECT ft5, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2 FROM ft5 left join ft4 on ft5.c1 = ft4.c1 WHERE ft4.c1 BETWEEN 10 and 30 ORDER BY ft5.c1, ft4.c1; 606SELECT ft5, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2 FROM ft5 left join ft4 on ft5.c1 = ft4.c1 WHERE ft4.c1 BETWEEN 10 and 30 ORDER BY ft5.c1, ft4.c1; 607 608-- multi-way join involving multiple merge joins 609-- (this case used to have EPQ-related planning problems) 610CREATE TABLE local_tbl (c1 int NOT NULL, c2 int NOT NULL, c3 text, CONSTRAINT local_tbl_pkey PRIMARY KEY (c1)); 611INSERT INTO local_tbl SELECT id, id % 10, to_char(id, 'FM0000') FROM generate_series(1, 1000) id; 612ANALYZE local_tbl; 613SET enable_nestloop TO false; 614SET enable_hashjoin TO false; 615EXPLAIN (VERBOSE, COSTS OFF) 616SELECT * FROM ft1, ft2, ft4, ft5, local_tbl WHERE ft1.c1 = ft2.c1 AND ft1.c2 = ft4.c1 617 AND ft1.c2 = ft5.c1 AND ft1.c2 = local_tbl.c1 AND ft1.c1 < 100 AND ft2.c1 < 100 FOR UPDATE; 618SELECT * FROM ft1, ft2, ft4, ft5, local_tbl WHERE ft1.c1 = ft2.c1 AND ft1.c2 = ft4.c1 619 AND ft1.c2 = ft5.c1 AND ft1.c2 = local_tbl.c1 AND ft1.c1 < 100 AND ft2.c1 < 100 FOR UPDATE; 620RESET enable_nestloop; 621RESET enable_hashjoin; 622DROP TABLE local_tbl; 623 624-- check join pushdown in situations where multiple userids are involved 625CREATE ROLE regress_view_owner SUPERUSER; 626CREATE USER MAPPING FOR regress_view_owner SERVER loopback; 627GRANT SELECT ON ft4 TO regress_view_owner; 628GRANT SELECT ON ft5 TO regress_view_owner; 629 630CREATE VIEW v4 AS SELECT * FROM ft4; 631CREATE VIEW v5 AS SELECT * FROM ft5; 632ALTER VIEW v5 OWNER TO regress_view_owner; 633EXPLAIN (VERBOSE, COSTS OFF) 634SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN v5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; -- can't be pushed down, different view owners 635SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN v5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; 636ALTER VIEW v4 OWNER TO regress_view_owner; 637EXPLAIN (VERBOSE, COSTS OFF) 638SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN v5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; -- can be pushed down 639SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN v5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; 640 641EXPLAIN (VERBOSE, COSTS OFF) 642SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; -- can't be pushed down, view owner not current user 643SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; 644ALTER VIEW v4 OWNER TO CURRENT_USER; 645EXPLAIN (VERBOSE, COSTS OFF) 646SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; -- can be pushed down 647SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; 648ALTER VIEW v4 OWNER TO regress_view_owner; 649 650-- cleanup 651DROP OWNED BY regress_view_owner; 652DROP ROLE regress_view_owner; 653 654 655-- =================================================================== 656-- Aggregate and grouping queries 657-- =================================================================== 658 659-- Simple aggregates 660explain (verbose, costs off) 661select count(c6), sum(c1), avg(c1), min(c2), max(c1), stddev(c2), sum(c1) * (random() <= 1)::int as sum2 from ft1 where c2 < 5 group by c2 order by 1, 2; 662select count(c6), sum(c1), avg(c1), min(c2), max(c1), stddev(c2), sum(c1) * (random() <= 1)::int as sum2 from ft1 where c2 < 5 group by c2 order by 1, 2; 663 664explain (verbose, costs off) 665select count(c6), sum(c1), avg(c1), min(c2), max(c1), stddev(c2), sum(c1) * (random() <= 1)::int as sum2 from ft1 where c2 < 5 group by c2 order by 1, 2 limit 1; 666select count(c6), sum(c1), avg(c1), min(c2), max(c1), stddev(c2), sum(c1) * (random() <= 1)::int as sum2 from ft1 where c2 < 5 group by c2 order by 1, 2 limit 1; 667 668-- Aggregate is not pushed down as aggregation contains random() 669explain (verbose, costs off) 670select sum(c1 * (random() <= 1)::int) as sum, avg(c1) from ft1; 671 672-- Aggregate over join query 673explain (verbose, costs off) 674select count(*), sum(t1.c1), avg(t2.c1) from ft1 t1 inner join ft1 t2 on (t1.c2 = t2.c2) where t1.c2 = 6; 675select count(*), sum(t1.c1), avg(t2.c1) from ft1 t1 inner join ft1 t2 on (t1.c2 = t2.c2) where t1.c2 = 6; 676 677-- Not pushed down due to local conditions present in underneath input rel 678explain (verbose, costs off) 679select sum(t1.c1), count(t2.c1) from ft1 t1 inner join ft2 t2 on (t1.c1 = t2.c1) where ((t1.c1 * t2.c1)/(t1.c1 * t2.c1)) * random() <= 1; 680 681-- GROUP BY clause having expressions 682explain (verbose, costs off) 683select c2/2, sum(c2) * (c2/2) from ft1 group by c2/2 order by c2/2; 684select c2/2, sum(c2) * (c2/2) from ft1 group by c2/2 order by c2/2; 685 686-- Aggregates in subquery are pushed down. 687explain (verbose, costs off) 688select count(x.a), sum(x.a) from (select c2 a, sum(c1) b from ft1 group by c2, sqrt(c1) order by 1, 2) x; 689select count(x.a), sum(x.a) from (select c2 a, sum(c1) b from ft1 group by c2, sqrt(c1) order by 1, 2) x; 690 691-- Aggregate is still pushed down by taking unshippable expression out 692explain (verbose, costs off) 693select c2 * (random() <= 1)::int as sum1, sum(c1) * c2 as sum2 from ft1 group by c2 order by 1, 2; 694select c2 * (random() <= 1)::int as sum1, sum(c1) * c2 as sum2 from ft1 group by c2 order by 1, 2; 695 696-- Aggregate with unshippable GROUP BY clause are not pushed 697explain (verbose, costs off) 698select c2 * (random() <= 1)::int as c2 from ft2 group by c2 * (random() <= 1)::int order by 1; 699 700-- GROUP BY clause in various forms, cardinal, alias and constant expression 701explain (verbose, costs off) 702select count(c2) w, c2 x, 5 y, 7.0 z from ft1 group by 2, y, 9.0::int order by 2; 703select count(c2) w, c2 x, 5 y, 7.0 z from ft1 group by 2, y, 9.0::int order by 2; 704 705-- GROUP BY clause referring to same column multiple times 706-- Also, ORDER BY contains an aggregate function 707explain (verbose, costs off) 708select c2, c2 from ft1 where c2 > 6 group by 1, 2 order by sum(c1); 709select c2, c2 from ft1 where c2 > 6 group by 1, 2 order by sum(c1); 710 711-- Testing HAVING clause shippability 712explain (verbose, costs off) 713select c2, sum(c1) from ft2 group by c2 having avg(c1) < 500 and sum(c1) < 49800 order by c2; 714select c2, sum(c1) from ft2 group by c2 having avg(c1) < 500 and sum(c1) < 49800 order by c2; 715 716-- Unshippable HAVING clause will be evaluated locally, and other qual in HAVING clause is pushed down 717explain (verbose, costs off) 718select count(*) from (select c5, count(c1) from ft1 group by c5, sqrt(c2) having (avg(c1) / avg(c1)) * random() <= 1 and avg(c1) < 500) x; 719select count(*) from (select c5, count(c1) from ft1 group by c5, sqrt(c2) having (avg(c1) / avg(c1)) * random() <= 1 and avg(c1) < 500) x; 720 721-- Aggregate in HAVING clause is not pushable, and thus aggregation is not pushed down 722explain (verbose, costs off) 723select sum(c1) from ft1 group by c2 having avg(c1 * (random() <= 1)::int) > 100 order by 1; 724 725-- Remote aggregate in combination with a local Param (for the output 726-- of an initplan) can be trouble, per bug #15781 727explain (verbose, costs off) 728select exists(select 1 from pg_enum), sum(c1) from ft1; 729select exists(select 1 from pg_enum), sum(c1) from ft1; 730 731explain (verbose, costs off) 732select exists(select 1 from pg_enum), sum(c1) from ft1 group by 1; 733select exists(select 1 from pg_enum), sum(c1) from ft1 group by 1; 734 735 736-- Testing ORDER BY, DISTINCT, FILTER, Ordered-sets and VARIADIC within aggregates 737 738-- ORDER BY within aggregate, same column used to order 739explain (verbose, costs off) 740select array_agg(c1 order by c1) from ft1 where c1 < 100 group by c2 order by 1; 741select array_agg(c1 order by c1) from ft1 where c1 < 100 group by c2 order by 1; 742 743-- ORDER BY within aggregate, different column used to order also using DESC 744explain (verbose, costs off) 745select array_agg(c5 order by c1 desc) from ft2 where c2 = 6 and c1 < 50; 746select array_agg(c5 order by c1 desc) from ft2 where c2 = 6 and c1 < 50; 747 748-- DISTINCT within aggregate 749explain (verbose, costs off) 750select array_agg(distinct (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; 751select array_agg(distinct (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; 752 753-- DISTINCT combined with ORDER BY within aggregate 754explain (verbose, costs off) 755select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; 756select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; 757 758explain (verbose, costs off) 759select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc nulls last) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; 760select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc nulls last) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; 761 762-- FILTER within aggregate 763explain (verbose, costs off) 764select sum(c1) filter (where c1 < 100 and c2 > 5) from ft1 group by c2 order by 1 nulls last; 765select sum(c1) filter (where c1 < 100 and c2 > 5) from ft1 group by c2 order by 1 nulls last; 766 767-- DISTINCT, ORDER BY and FILTER within aggregate 768explain (verbose, costs off) 769select sum(c1%3), sum(distinct c1%3 order by c1%3) filter (where c1%3 < 2), c2 from ft1 where c2 = 6 group by c2; 770select sum(c1%3), sum(distinct c1%3 order by c1%3) filter (where c1%3 < 2), c2 from ft1 where c2 = 6 group by c2; 771 772-- Outer query is aggregation query 773explain (verbose, costs off) 774select distinct (select count(*) filter (where t2.c2 = 6 and t2.c1 < 10) from ft1 t1 where t1.c1 = 6) from ft2 t2 where t2.c2 % 6 = 0 order by 1; 775select distinct (select count(*) filter (where t2.c2 = 6 and t2.c1 < 10) from ft1 t1 where t1.c1 = 6) from ft2 t2 where t2.c2 % 6 = 0 order by 1; 776-- Inner query is aggregation query 777explain (verbose, costs off) 778select distinct (select count(t1.c1) filter (where t2.c2 = 6 and t2.c1 < 10) from ft1 t1 where t1.c1 = 6) from ft2 t2 where t2.c2 % 6 = 0 order by 1; 779select distinct (select count(t1.c1) filter (where t2.c2 = 6 and t2.c1 < 10) from ft1 t1 where t1.c1 = 6) from ft2 t2 where t2.c2 % 6 = 0 order by 1; 780 781-- Aggregate not pushed down as FILTER condition is not pushable 782explain (verbose, costs off) 783select sum(c1) filter (where (c1 / c1) * random() <= 1) from ft1 group by c2 order by 1; 784explain (verbose, costs off) 785select sum(c2) filter (where c2 in (select c2 from ft1 where c2 < 5)) from ft1; 786 787-- Ordered-sets within aggregate 788explain (verbose, costs off) 789select c2, rank('10'::varchar) within group (order by c6), percentile_cont(c2/10::numeric) within group (order by c1) from ft1 where c2 < 10 group by c2 having percentile_cont(c2/10::numeric) within group (order by c1) < 500 order by c2; 790select c2, rank('10'::varchar) within group (order by c6), percentile_cont(c2/10::numeric) within group (order by c1) from ft1 where c2 < 10 group by c2 having percentile_cont(c2/10::numeric) within group (order by c1) < 500 order by c2; 791 792-- Using multiple arguments within aggregates 793explain (verbose, costs off) 794select c1, rank(c1, c2) within group (order by c1, c2) from ft1 group by c1, c2 having c1 = 6 order by 1; 795select c1, rank(c1, c2) within group (order by c1, c2) from ft1 group by c1, c2 having c1 = 6 order by 1; 796 797-- User defined function for user defined aggregate, VARIADIC 798create function least_accum(anyelement, variadic anyarray) 799returns anyelement language sql as 800 'select least($1, min($2[i])) from generate_subscripts($2,1) g(i)'; 801create aggregate least_agg(variadic items anyarray) ( 802 stype = anyelement, sfunc = least_accum 803); 804 805-- Disable hash aggregation for plan stability. 806set enable_hashagg to false; 807 808-- Not pushed down due to user defined aggregate 809explain (verbose, costs off) 810select c2, least_agg(c1) from ft1 group by c2 order by c2; 811 812-- Add function and aggregate into extension 813alter extension postgres_fdw add function least_accum(anyelement, variadic anyarray); 814alter extension postgres_fdw add aggregate least_agg(variadic items anyarray); 815alter server loopback options (set extensions 'postgres_fdw'); 816 817-- Now aggregate will be pushed. Aggregate will display VARIADIC argument. 818explain (verbose, costs off) 819select c2, least_agg(c1) from ft1 where c2 < 100 group by c2 order by c2; 820select c2, least_agg(c1) from ft1 where c2 < 100 group by c2 order by c2; 821 822-- Remove function and aggregate from extension 823alter extension postgres_fdw drop function least_accum(anyelement, variadic anyarray); 824alter extension postgres_fdw drop aggregate least_agg(variadic items anyarray); 825alter server loopback options (set extensions 'postgres_fdw'); 826 827-- Not pushed down as we have dropped objects from extension. 828explain (verbose, costs off) 829select c2, least_agg(c1) from ft1 group by c2 order by c2; 830 831-- Cleanup 832reset enable_hashagg; 833drop aggregate least_agg(variadic items anyarray); 834drop function least_accum(anyelement, variadic anyarray); 835 836 837-- Testing USING OPERATOR() in ORDER BY within aggregate. 838-- For this, we need user defined operators along with operator family and 839-- operator class. Create those and then add them in extension. Note that 840-- user defined objects are considered unshippable unless they are part of 841-- the extension. 842create operator public.<^ ( 843 leftarg = int4, 844 rightarg = int4, 845 procedure = int4eq 846); 847 848create operator public.=^ ( 849 leftarg = int4, 850 rightarg = int4, 851 procedure = int4lt 852); 853 854create operator public.>^ ( 855 leftarg = int4, 856 rightarg = int4, 857 procedure = int4gt 858); 859 860create operator family my_op_family using btree; 861 862create function my_op_cmp(a int, b int) returns int as 863 $$begin return btint4cmp(a, b); end $$ language plpgsql; 864 865create operator class my_op_class for type int using btree family my_op_family as 866 operator 1 public.<^, 867 operator 3 public.=^, 868 operator 5 public.>^, 869 function 1 my_op_cmp(int, int); 870 871-- This will not be pushed as user defined sort operator is not part of the 872-- extension yet. 873explain (verbose, costs off) 874select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2; 875 876-- Update local stats on ft2 877ANALYZE ft2; 878 879-- Add into extension 880alter extension postgres_fdw add operator class my_op_class using btree; 881alter extension postgres_fdw add function my_op_cmp(a int, b int); 882alter extension postgres_fdw add operator family my_op_family using btree; 883alter extension postgres_fdw add operator public.<^(int, int); 884alter extension postgres_fdw add operator public.=^(int, int); 885alter extension postgres_fdw add operator public.>^(int, int); 886alter server loopback options (set extensions 'postgres_fdw'); 887 888-- Now this will be pushed as sort operator is part of the extension. 889explain (verbose, costs off) 890select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2; 891select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2; 892 893-- Remove from extension 894alter extension postgres_fdw drop operator class my_op_class using btree; 895alter extension postgres_fdw drop function my_op_cmp(a int, b int); 896alter extension postgres_fdw drop operator family my_op_family using btree; 897alter extension postgres_fdw drop operator public.<^(int, int); 898alter extension postgres_fdw drop operator public.=^(int, int); 899alter extension postgres_fdw drop operator public.>^(int, int); 900alter server loopback options (set extensions 'postgres_fdw'); 901 902-- This will not be pushed as sort operator is now removed from the extension. 903explain (verbose, costs off) 904select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2; 905 906-- Cleanup 907drop operator class my_op_class using btree; 908drop function my_op_cmp(a int, b int); 909drop operator family my_op_family using btree; 910drop operator public.>^(int, int); 911drop operator public.=^(int, int); 912drop operator public.<^(int, int); 913 914-- Input relation to aggregate push down hook is not safe to pushdown and thus 915-- the aggregate cannot be pushed down to foreign server. 916explain (verbose, costs off) 917select count(t1.c3) from ft2 t1 left join ft2 t2 on (t1.c1 = random() * t2.c2); 918 919-- Subquery in FROM clause having aggregate 920explain (verbose, costs off) 921select count(*), x.b from ft1, (select c2 a, sum(c1) b from ft1 group by c2) x where ft1.c2 = x.a group by x.b order by 1, 2; 922select count(*), x.b from ft1, (select c2 a, sum(c1) b from ft1 group by c2) x where ft1.c2 = x.a group by x.b order by 1, 2; 923 924-- FULL join with IS NULL check in HAVING 925explain (verbose, costs off) 926select avg(t1.c1), sum(t2.c1) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) group by t2.c1 having (avg(t1.c1) is null and sum(t2.c1) < 10) or sum(t2.c1) is null order by 1 nulls last, 2; 927select avg(t1.c1), sum(t2.c1) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) group by t2.c1 having (avg(t1.c1) is null and sum(t2.c1) < 10) or sum(t2.c1) is null order by 1 nulls last, 2; 928 929-- Aggregate over FULL join needing to deparse the joining relations as 930-- subqueries. 931explain (verbose, costs off) 932select count(*), sum(t1.c1), avg(t2.c1) from (select c1 from ft4 where c1 between 50 and 60) t1 full join (select c1 from ft5 where c1 between 50 and 60) t2 on (t1.c1 = t2.c1); 933select count(*), sum(t1.c1), avg(t2.c1) from (select c1 from ft4 where c1 between 50 and 60) t1 full join (select c1 from ft5 where c1 between 50 and 60) t2 on (t1.c1 = t2.c1); 934 935-- ORDER BY expression is part of the target list but not pushed down to 936-- foreign server. 937explain (verbose, costs off) 938select sum(c2) * (random() <= 1)::int as sum from ft1 order by 1; 939select sum(c2) * (random() <= 1)::int as sum from ft1 order by 1; 940 941-- LATERAL join, with parameterization 942set enable_hashagg to false; 943explain (verbose, costs off) 944select c2, sum from "S 1"."T 1" t1, lateral (select sum(t2.c1 + t1."C 1") sum from ft2 t2 group by t2.c1) qry where t1.c2 * 2 = qry.sum and t1.c2 < 3 and t1."C 1" < 100 order by 1; 945select c2, sum from "S 1"."T 1" t1, lateral (select sum(t2.c1 + t1."C 1") sum from ft2 t2 group by t2.c1) qry where t1.c2 * 2 = qry.sum and t1.c2 < 3 and t1."C 1" < 100 order by 1; 946reset enable_hashagg; 947 948-- bug #15613: bad plan for foreign table scan with lateral reference 949EXPLAIN (VERBOSE, COSTS OFF) 950SELECT ref_0.c2, subq_1.* 951FROM 952 "S 1"."T 1" AS ref_0, 953 LATERAL ( 954 SELECT ref_0."C 1" c1, subq_0.* 955 FROM (SELECT ref_0.c2, ref_1.c3 956 FROM ft1 AS ref_1) AS subq_0 957 RIGHT JOIN ft2 AS ref_3 ON (subq_0.c3 = ref_3.c3) 958 ) AS subq_1 959WHERE ref_0."C 1" < 10 AND subq_1.c3 = '00001' 960ORDER BY ref_0."C 1"; 961 962SELECT ref_0.c2, subq_1.* 963FROM 964 "S 1"."T 1" AS ref_0, 965 LATERAL ( 966 SELECT ref_0."C 1" c1, subq_0.* 967 FROM (SELECT ref_0.c2, ref_1.c3 968 FROM ft1 AS ref_1) AS subq_0 969 RIGHT JOIN ft2 AS ref_3 ON (subq_0.c3 = ref_3.c3) 970 ) AS subq_1 971WHERE ref_0."C 1" < 10 AND subq_1.c3 = '00001' 972ORDER BY ref_0."C 1"; 973 974-- Check with placeHolderVars 975explain (verbose, costs off) 976select sum(q.a), count(q.b) from ft4 left join (select 13, avg(ft1.c1), sum(ft2.c1) from ft1 right join ft2 on (ft1.c1 = ft2.c1)) q(a, b, c) on (ft4.c1 <= q.b); 977select sum(q.a), count(q.b) from ft4 left join (select 13, avg(ft1.c1), sum(ft2.c1) from ft1 right join ft2 on (ft1.c1 = ft2.c1)) q(a, b, c) on (ft4.c1 <= q.b); 978 979 980-- Not supported cases 981-- Grouping sets 982explain (verbose, costs off) 983select c2, sum(c1) from ft1 where c2 < 3 group by rollup(c2) order by 1 nulls last; 984select c2, sum(c1) from ft1 where c2 < 3 group by rollup(c2) order by 1 nulls last; 985explain (verbose, costs off) 986select c2, sum(c1) from ft1 where c2 < 3 group by cube(c2) order by 1 nulls last; 987select c2, sum(c1) from ft1 where c2 < 3 group by cube(c2) order by 1 nulls last; 988explain (verbose, costs off) 989select c2, c6, sum(c1) from ft1 where c2 < 3 group by grouping sets(c2, c6) order by 1 nulls last, 2 nulls last; 990select c2, c6, sum(c1) from ft1 where c2 < 3 group by grouping sets(c2, c6) order by 1 nulls last, 2 nulls last; 991explain (verbose, costs off) 992select c2, sum(c1), grouping(c2) from ft1 where c2 < 3 group by c2 order by 1 nulls last; 993select c2, sum(c1), grouping(c2) from ft1 where c2 < 3 group by c2 order by 1 nulls last; 994 995-- DISTINCT itself is not pushed down, whereas underneath aggregate is pushed 996explain (verbose, costs off) 997select distinct sum(c1)/1000 s from ft2 where c2 < 6 group by c2 order by 1; 998select distinct sum(c1)/1000 s from ft2 where c2 < 6 group by c2 order by 1; 999 1000-- WindowAgg 1001explain (verbose, costs off) 1002select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 group by c2 order by 1; 1003select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 group by c2 order by 1; 1004explain (verbose, costs off) 1005select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 where c2 < 10 group by c2 order by 1; 1006select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 where c2 < 10 group by c2 order by 1; 1007explain (verbose, costs off) 1008select c2, array_agg(c2) over (partition by c2%2 order by c2 range between current row and unbounded following) from ft1 where c2 < 10 group by c2 order by 1; 1009select c2, array_agg(c2) over (partition by c2%2 order by c2 range between current row and unbounded following) from ft1 where c2 < 10 group by c2 order by 1; 1010 1011 1012-- =================================================================== 1013-- parameterized queries 1014-- =================================================================== 1015-- simple join 1016PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2; 1017EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st1(1, 2); 1018EXECUTE st1(1, 1); 1019EXECUTE st1(101, 101); 1020-- subquery using stable function (can't be sent to remote) 1021PREPARE st2(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND date(c4) = '1970-01-17'::date) ORDER BY c1; 1022EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st2(10, 20); 1023EXECUTE st2(10, 20); 1024EXECUTE st2(101, 121); 1025-- subquery using immutable function (can be sent to remote) 1026PREPARE st3(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND date(c5) = '1970-01-17'::date) ORDER BY c1; 1027EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st3(10, 20); 1028EXECUTE st3(10, 20); 1029EXECUTE st3(20, 30); 1030-- custom plan should be chosen initially 1031PREPARE st4(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 = $1; 1032EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 1033EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 1034EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 1035EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 1036EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 1037-- once we try it enough times, should switch to generic plan 1038EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 1039-- value of $1 should not be sent to remote 1040PREPARE st5(user_enum,int) AS SELECT * FROM ft1 t1 WHERE c8 = $1 and c1 = $2; 1041EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 1042EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 1043EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 1044EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 1045EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 1046EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 1047EXECUTE st5('foo', 1); 1048 1049-- altering FDW options requires replanning 1050PREPARE st6 AS SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2; 1051EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6; 1052PREPARE st7 AS INSERT INTO ft1 (c1,c2,c3) VALUES (1001,101,'foo'); 1053EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7; 1054ALTER TABLE "S 1"."T 1" RENAME TO "T 0"; 1055ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 0'); 1056EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6; 1057EXECUTE st6; 1058EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7; 1059ALTER TABLE "S 1"."T 0" RENAME TO "T 1"; 1060ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 1'); 1061 1062PREPARE st8 AS SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 1063EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8; 1064ALTER SERVER loopback OPTIONS (DROP extensions); 1065EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8; 1066EXECUTE st8; 1067ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw'); 1068 1069-- cleanup 1070DEALLOCATE st1; 1071DEALLOCATE st2; 1072DEALLOCATE st3; 1073DEALLOCATE st4; 1074DEALLOCATE st5; 1075DEALLOCATE st6; 1076DEALLOCATE st7; 1077DEALLOCATE st8; 1078 1079-- System columns, except ctid and oid, should not be sent to remote 1080EXPLAIN (VERBOSE, COSTS OFF) 1081SELECT * FROM ft1 t1 WHERE t1.tableoid = 'pg_class'::regclass LIMIT 1; 1082SELECT * FROM ft1 t1 WHERE t1.tableoid = 'ft1'::regclass LIMIT 1; 1083EXPLAIN (VERBOSE, COSTS OFF) 1084SELECT tableoid::regclass, * FROM ft1 t1 LIMIT 1; 1085SELECT tableoid::regclass, * FROM ft1 t1 LIMIT 1; 1086EXPLAIN (VERBOSE, COSTS OFF) 1087SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)'; 1088SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)'; 1089EXPLAIN (VERBOSE, COSTS OFF) 1090SELECT ctid, * FROM ft1 t1 LIMIT 1; 1091SELECT ctid, * FROM ft1 t1 LIMIT 1; 1092 1093-- =================================================================== 1094-- used in PL/pgSQL function 1095-- =================================================================== 1096CREATE OR REPLACE FUNCTION f_test(p_c1 int) RETURNS int AS $$ 1097DECLARE 1098 v_c1 int; 1099BEGIN 1100 SELECT c1 INTO v_c1 FROM ft1 WHERE c1 = p_c1 LIMIT 1; 1101 PERFORM c1 FROM ft1 WHERE c1 = p_c1 AND p_c1 = v_c1 LIMIT 1; 1102 RETURN v_c1; 1103END; 1104$$ LANGUAGE plpgsql; 1105SELECT f_test(100); 1106DROP FUNCTION f_test(int); 1107 1108-- =================================================================== 1109-- REINDEX 1110-- =================================================================== 1111-- remote table is not created here 1112CREATE FOREIGN TABLE reindex_foreign (c1 int, c2 int) 1113 SERVER loopback2 OPTIONS (table_name 'reindex_local'); 1114REINDEX TABLE reindex_foreign; -- error 1115REINDEX TABLE CONCURRENTLY reindex_foreign; -- error 1116DROP FOREIGN TABLE reindex_foreign; 1117-- partitions and foreign tables 1118CREATE TABLE reind_fdw_parent (c1 int) PARTITION BY RANGE (c1); 1119CREATE TABLE reind_fdw_0_10 PARTITION OF reind_fdw_parent 1120 FOR VALUES FROM (0) TO (10); 1121CREATE FOREIGN TABLE reind_fdw_10_20 PARTITION OF reind_fdw_parent 1122 FOR VALUES FROM (10) TO (20) 1123 SERVER loopback OPTIONS (table_name 'reind_local_10_20'); 1124REINDEX TABLE reind_fdw_parent; -- ok 1125REINDEX TABLE CONCURRENTLY reind_fdw_parent; -- ok 1126DROP TABLE reind_fdw_parent; 1127 1128-- =================================================================== 1129-- conversion error 1130-- =================================================================== 1131ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE int; 1132SELECT * FROM ft1 ftx(x1,x2,x3,x4,x5,x6,x7,x8) WHERE x1 = 1; -- ERROR 1133SELECT ftx.x1, ft2.c2, ftx.x8 FROM ft1 ftx(x1,x2,x3,x4,x5,x6,x7,x8), ft2 1134 WHERE ftx.x1 = ft2.c1 AND ftx.x1 = 1; -- ERROR 1135SELECT ftx.x1, ft2.c2, ftx FROM ft1 ftx(x1,x2,x3,x4,x5,x6,x7,x8), ft2 1136 WHERE ftx.x1 = ft2.c1 AND ftx.x1 = 1; -- ERROR 1137SELECT sum(c2), array_agg(c8) FROM ft1 GROUP BY c8; -- ERROR 1138ANALYZE ft1; -- ERROR 1139ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum; 1140 1141-- =================================================================== 1142-- subtransaction 1143-- + local/remote error doesn't break cursor 1144-- =================================================================== 1145BEGIN; 1146DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1; 1147FETCH c; 1148SAVEPOINT s; 1149ERROR OUT; -- ERROR 1150ROLLBACK TO s; 1151FETCH c; 1152SAVEPOINT s; 1153SELECT * FROM ft1 WHERE 1 / (c1 - 1) > 0; -- ERROR 1154ROLLBACK TO s; 1155FETCH c; 1156SELECT * FROM ft1 ORDER BY c1 LIMIT 1; 1157COMMIT; 1158 1159-- =================================================================== 1160-- test handling of collations 1161-- =================================================================== 1162create table loct3 (f1 text collate "C" unique, f2 text, f3 varchar(10) unique); 1163create foreign table ft3 (f1 text collate "C", f2 text, f3 varchar(10)) 1164 server loopback options (table_name 'loct3', use_remote_estimate 'true'); 1165 1166-- can be sent to remote 1167explain (verbose, costs off) select * from ft3 where f1 = 'foo'; 1168explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" = 'foo'; 1169explain (verbose, costs off) select * from ft3 where f2 = 'foo'; 1170explain (verbose, costs off) select * from ft3 where f3 = 'foo'; 1171explain (verbose, costs off) select * from ft3 f, loct3 l 1172 where f.f3 = l.f3 and l.f1 = 'foo'; 1173-- can't be sent to remote 1174explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" = 'foo'; 1175explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C"; 1176explain (verbose, costs off) select * from ft3 where f2 COLLATE "C" = 'foo'; 1177explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C"; 1178explain (verbose, costs off) select * from ft3 f, loct3 l 1179 where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo'; 1180 1181-- =================================================================== 1182-- test writable foreign table stuff 1183-- =================================================================== 1184EXPLAIN (verbose, costs off) 1185INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20; 1186INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20; 1187INSERT INTO ft2 (c1,c2,c3) 1188 VALUES (1101,201,'aaa'), (1102,202,'bbb'), (1103,203,'ccc') RETURNING *; 1189INSERT INTO ft2 (c1,c2,c3) VALUES (1104,204,'ddd'), (1105,205,'eee'); 1190EXPLAIN (verbose, costs off) 1191UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3; -- can be pushed down 1192UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3; 1193EXPLAIN (verbose, costs off) 1194UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *; -- can be pushed down 1195UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *; 1196EXPLAIN (verbose, costs off) 1197UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT 1198 FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; -- can be pushed down 1199UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT 1200 FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; 1201EXPLAIN (verbose, costs off) 1202 DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4; -- can be pushed down 1203DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4; 1204EXPLAIN (verbose, costs off) 1205DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; -- can be pushed down 1206DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; 1207SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1; 1208EXPLAIN (verbose, costs off) 1209INSERT INTO ft2 (c1,c2,c3) VALUES (1200,999,'foo') RETURNING tableoid::regclass; 1210INSERT INTO ft2 (c1,c2,c3) VALUES (1200,999,'foo') RETURNING tableoid::regclass; 1211EXPLAIN (verbose, costs off) 1212UPDATE ft2 SET c3 = 'bar' WHERE c1 = 1200 RETURNING tableoid::regclass; -- can be pushed down 1213UPDATE ft2 SET c3 = 'bar' WHERE c1 = 1200 RETURNING tableoid::regclass; 1214EXPLAIN (verbose, costs off) 1215DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass; -- can be pushed down 1216DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass; 1217 1218-- Test UPDATE/DELETE with RETURNING on a three-table join 1219INSERT INTO ft2 (c1,c2,c3) 1220 SELECT id, id - 1200, to_char(id, 'FM00000') FROM generate_series(1201, 1300) id; 1221EXPLAIN (verbose, costs off) 1222UPDATE ft2 SET c3 = 'foo' 1223 FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1) 1224 WHERE ft2.c1 > 1200 AND ft2.c2 = ft4.c1 1225 RETURNING ft2, ft2.*, ft4, ft4.*; -- can be pushed down 1226UPDATE ft2 SET c3 = 'foo' 1227 FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1) 1228 WHERE ft2.c1 > 1200 AND ft2.c2 = ft4.c1 1229 RETURNING ft2, ft2.*, ft4, ft4.*; 1230EXPLAIN (verbose, costs off) 1231DELETE FROM ft2 1232 USING ft4 LEFT JOIN ft5 ON (ft4.c1 = ft5.c1) 1233 WHERE ft2.c1 > 1200 AND ft2.c1 % 10 = 0 AND ft2.c2 = ft4.c1 1234 RETURNING 100; -- can be pushed down 1235DELETE FROM ft2 1236 USING ft4 LEFT JOIN ft5 ON (ft4.c1 = ft5.c1) 1237 WHERE ft2.c1 > 1200 AND ft2.c1 % 10 = 0 AND ft2.c2 = ft4.c1 1238 RETURNING 100; 1239DELETE FROM ft2 WHERE ft2.c1 > 1200; 1240 1241-- Test UPDATE with a MULTIEXPR sub-select 1242-- (maybe someday this'll be remotely executable, but not today) 1243EXPLAIN (verbose, costs off) 1244UPDATE ft2 AS target SET (c2, c7) = ( 1245 SELECT c2 * 10, c7 1246 FROM ft2 AS src 1247 WHERE target.c1 = src.c1 1248) WHERE c1 > 1100; 1249UPDATE ft2 AS target SET (c2, c7) = ( 1250 SELECT c2 * 10, c7 1251 FROM ft2 AS src 1252 WHERE target.c1 = src.c1 1253) WHERE c1 > 1100; 1254 1255UPDATE ft2 AS target SET (c2) = ( 1256 SELECT c2 / 10 1257 FROM ft2 AS src 1258 WHERE target.c1 = src.c1 1259) WHERE c1 > 1100; 1260 1261-- Test UPDATE involving a join that can be pushed down, 1262-- but a SET clause that can't be 1263EXPLAIN (VERBOSE, COSTS OFF) 1264UPDATE ft2 d SET c2 = CASE WHEN random() >= 0 THEN d.c2 ELSE 0 END 1265 FROM ft2 AS t WHERE d.c1 = t.c1 AND d.c1 > 1000; 1266UPDATE ft2 d SET c2 = CASE WHEN random() >= 0 THEN d.c2 ELSE 0 END 1267 FROM ft2 AS t WHERE d.c1 = t.c1 AND d.c1 > 1000; 1268 1269-- Test UPDATE/DELETE with WHERE or JOIN/ON conditions containing 1270-- user-defined operators/functions 1271ALTER SERVER loopback OPTIONS (DROP extensions); 1272INSERT INTO ft2 (c1,c2,c3) 1273 SELECT id, id % 10, to_char(id, 'FM00000') FROM generate_series(2001, 2010) id; 1274EXPLAIN (verbose, costs off) 1275UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *; -- can't be pushed down 1276UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *; 1277EXPLAIN (verbose, costs off) 1278UPDATE ft2 SET c3 = 'baz' 1279 FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1) 1280 WHERE ft2.c1 > 2000 AND ft2.c2 === ft4.c1 1281 RETURNING ft2.*, ft4.*, ft5.*; -- can't be pushed down 1282UPDATE ft2 SET c3 = 'baz' 1283 FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1) 1284 WHERE ft2.c1 > 2000 AND ft2.c2 === ft4.c1 1285 RETURNING ft2.*, ft4.*, ft5.*; 1286EXPLAIN (verbose, costs off) 1287DELETE FROM ft2 1288 USING ft4 INNER JOIN ft5 ON (ft4.c1 === ft5.c1) 1289 WHERE ft2.c1 > 2000 AND ft2.c2 = ft4.c1 1290 RETURNING ft2.c1, ft2.c2, ft2.c3; -- can't be pushed down 1291DELETE FROM ft2 1292 USING ft4 INNER JOIN ft5 ON (ft4.c1 === ft5.c1) 1293 WHERE ft2.c1 > 2000 AND ft2.c2 = ft4.c1 1294 RETURNING ft2.c1, ft2.c2, ft2.c3; 1295DELETE FROM ft2 WHERE ft2.c1 > 2000; 1296ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw'); 1297 1298-- Test that trigger on remote table works as expected 1299CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$ 1300BEGIN 1301 NEW.c3 = NEW.c3 || '_trig_update'; 1302 RETURN NEW; 1303END; 1304$$ LANGUAGE plpgsql; 1305CREATE TRIGGER t1_br_insert BEFORE INSERT OR UPDATE 1306 ON "S 1"."T 1" FOR EACH ROW EXECUTE PROCEDURE "S 1".F_BRTRIG(); 1307 1308INSERT INTO ft2 (c1,c2,c3) VALUES (1208, 818, 'fff') RETURNING *; 1309INSERT INTO ft2 (c1,c2,c3,c6) VALUES (1218, 818, 'ggg', '(--;') RETURNING *; 1310UPDATE ft2 SET c2 = c2 + 600 WHERE c1 % 10 = 8 AND c1 < 1200 RETURNING *; 1311 1312-- Test errors thrown on remote side during update 1313ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0); 1314 1315INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key 1316INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT DO NOTHING; -- works 1317INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO NOTHING; -- unsupported 1318INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO UPDATE SET c3 = 'ffg'; -- unsupported 1319INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive 1320UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive 1321 1322-- Test savepoint/rollback behavior 1323select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1324select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; 1325begin; 1326update ft2 set c2 = 42 where c2 = 0; 1327select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1328savepoint s1; 1329update ft2 set c2 = 44 where c2 = 4; 1330select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1331release savepoint s1; 1332select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1333savepoint s2; 1334update ft2 set c2 = 46 where c2 = 6; 1335select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1336rollback to savepoint s2; 1337select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1338release savepoint s2; 1339select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1340savepoint s3; 1341update ft2 set c2 = -2 where c2 = 42 and c1 = 10; -- fail on remote side 1342rollback to savepoint s3; 1343select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1344release savepoint s3; 1345select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1346-- none of the above is committed yet remotely 1347select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; 1348commit; 1349select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1350select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; 1351 1352VACUUM ANALYZE "S 1"."T 1"; 1353 1354-- Above DMLs add data with c6 as NULL in ft1, so test ORDER BY NULLS LAST and NULLs 1355-- FIRST behavior here. 1356-- ORDER BY DESC NULLS LAST options 1357EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 ORDER BY c6 DESC NULLS LAST, c1 OFFSET 795 LIMIT 10; 1358SELECT * FROM ft1 ORDER BY c6 DESC NULLS LAST, c1 OFFSET 795 LIMIT 10; 1359-- ORDER BY DESC NULLS FIRST options 1360EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 ORDER BY c6 DESC NULLS FIRST, c1 OFFSET 15 LIMIT 10; 1361SELECT * FROM ft1 ORDER BY c6 DESC NULLS FIRST, c1 OFFSET 15 LIMIT 10; 1362-- ORDER BY ASC NULLS FIRST options 1363EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10; 1364SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10; 1365 1366-- =================================================================== 1367-- test check constraints 1368-- =================================================================== 1369 1370-- Consistent check constraints provide consistent results 1371ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0); 1372EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 < 0; 1373SELECT count(*) FROM ft1 WHERE c2 < 0; 1374SET constraint_exclusion = 'on'; 1375EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 < 0; 1376SELECT count(*) FROM ft1 WHERE c2 < 0; 1377RESET constraint_exclusion; 1378-- check constraint is enforced on the remote side, not locally 1379INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive 1380UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive 1381ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2positive; 1382 1383-- But inconsistent check constraints provide inconsistent results 1384ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2negative CHECK (c2 < 0); 1385EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 >= 0; 1386SELECT count(*) FROM ft1 WHERE c2 >= 0; 1387SET constraint_exclusion = 'on'; 1388EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 >= 0; 1389SELECT count(*) FROM ft1 WHERE c2 >= 0; 1390RESET constraint_exclusion; 1391-- local check constraint is not actually enforced 1392INSERT INTO ft1(c1, c2) VALUES(1111, 2); 1393UPDATE ft1 SET c2 = c2 + 1 WHERE c1 = 1; 1394ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative; 1395 1396-- =================================================================== 1397-- test WITH CHECK OPTION constraints 1398-- =================================================================== 1399 1400CREATE FUNCTION row_before_insupd_trigfunc() RETURNS trigger AS $$BEGIN NEW.a := NEW.a + 10; RETURN NEW; END$$ LANGUAGE plpgsql; 1401 1402CREATE TABLE base_tbl (a int, b int); 1403ALTER TABLE base_tbl SET (autovacuum_enabled = 'false'); 1404CREATE TRIGGER row_before_insupd_trigger BEFORE INSERT OR UPDATE ON base_tbl FOR EACH ROW EXECUTE PROCEDURE row_before_insupd_trigfunc(); 1405CREATE FOREIGN TABLE foreign_tbl (a int, b int) 1406 SERVER loopback OPTIONS (table_name 'base_tbl'); 1407CREATE VIEW rw_view AS SELECT * FROM foreign_tbl 1408 WHERE a < b WITH CHECK OPTION; 1409\d+ rw_view 1410 1411EXPLAIN (VERBOSE, COSTS OFF) 1412INSERT INTO rw_view VALUES (0, 5); 1413INSERT INTO rw_view VALUES (0, 5); -- should fail 1414EXPLAIN (VERBOSE, COSTS OFF) 1415INSERT INTO rw_view VALUES (0, 15); 1416INSERT INTO rw_view VALUES (0, 15); -- ok 1417SELECT * FROM foreign_tbl; 1418 1419EXPLAIN (VERBOSE, COSTS OFF) 1420UPDATE rw_view SET b = b + 5; 1421UPDATE rw_view SET b = b + 5; -- should fail 1422EXPLAIN (VERBOSE, COSTS OFF) 1423UPDATE rw_view SET b = b + 15; 1424UPDATE rw_view SET b = b + 15; -- ok 1425SELECT * FROM foreign_tbl; 1426 1427DROP FOREIGN TABLE foreign_tbl CASCADE; 1428DROP TRIGGER row_before_insupd_trigger ON base_tbl; 1429DROP TABLE base_tbl; 1430 1431-- test WCO for partitions 1432 1433CREATE TABLE child_tbl (a int, b int); 1434ALTER TABLE child_tbl SET (autovacuum_enabled = 'false'); 1435CREATE TRIGGER row_before_insupd_trigger BEFORE INSERT OR UPDATE ON child_tbl FOR EACH ROW EXECUTE PROCEDURE row_before_insupd_trigfunc(); 1436CREATE FOREIGN TABLE foreign_tbl (a int, b int) 1437 SERVER loopback OPTIONS (table_name 'child_tbl'); 1438 1439CREATE TABLE parent_tbl (a int, b int) PARTITION BY RANGE(a); 1440ALTER TABLE parent_tbl ATTACH PARTITION foreign_tbl FOR VALUES FROM (0) TO (100); 1441 1442CREATE VIEW rw_view AS SELECT * FROM parent_tbl 1443 WHERE a < b WITH CHECK OPTION; 1444\d+ rw_view 1445 1446EXPLAIN (VERBOSE, COSTS OFF) 1447INSERT INTO rw_view VALUES (0, 5); 1448INSERT INTO rw_view VALUES (0, 5); -- should fail 1449EXPLAIN (VERBOSE, COSTS OFF) 1450INSERT INTO rw_view VALUES (0, 15); 1451INSERT INTO rw_view VALUES (0, 15); -- ok 1452SELECT * FROM foreign_tbl; 1453 1454EXPLAIN (VERBOSE, COSTS OFF) 1455UPDATE rw_view SET b = b + 5; 1456UPDATE rw_view SET b = b + 5; -- should fail 1457EXPLAIN (VERBOSE, COSTS OFF) 1458UPDATE rw_view SET b = b + 15; 1459UPDATE rw_view SET b = b + 15; -- ok 1460SELECT * FROM foreign_tbl; 1461 1462DROP FOREIGN TABLE foreign_tbl CASCADE; 1463DROP TRIGGER row_before_insupd_trigger ON child_tbl; 1464DROP TABLE parent_tbl CASCADE; 1465 1466DROP FUNCTION row_before_insupd_trigfunc; 1467 1468-- =================================================================== 1469-- test serial columns (ie, sequence-based defaults) 1470-- =================================================================== 1471create table loc1 (f1 serial, f2 text); 1472alter table loc1 set (autovacuum_enabled = 'false'); 1473create foreign table rem1 (f1 serial, f2 text) 1474 server loopback options(table_name 'loc1'); 1475select pg_catalog.setval('rem1_f1_seq', 10, false); 1476insert into loc1(f2) values('hi'); 1477insert into rem1(f2) values('hi remote'); 1478insert into loc1(f2) values('bye'); 1479insert into rem1(f2) values('bye remote'); 1480select * from loc1; 1481select * from rem1; 1482 1483-- =================================================================== 1484-- test generated columns 1485-- =================================================================== 1486create table gloc1 ( 1487 a int, 1488 b int generated always as (a * 2) stored); 1489alter table gloc1 set (autovacuum_enabled = 'false'); 1490create foreign table grem1 ( 1491 a int, 1492 b int generated always as (a * 2) stored) 1493 server loopback options(table_name 'gloc1'); 1494explain (verbose, costs off) 1495insert into grem1 (a) values (1), (2); 1496insert into grem1 (a) values (1), (2); 1497explain (verbose, costs off) 1498update grem1 set a = 22 where a = 2; 1499update grem1 set a = 22 where a = 2; 1500select * from gloc1; 1501select * from grem1; 1502delete from grem1; 1503 1504-- test copy from 1505copy grem1 from stdin; 15061 15072 1508\. 1509select * from gloc1; 1510select * from grem1; 1511delete from grem1; 1512 1513-- test batch insert 1514alter server loopback options (add batch_size '10'); 1515explain (verbose, costs off) 1516insert into grem1 (a) values (1), (2); 1517insert into grem1 (a) values (1), (2); 1518select * from gloc1; 1519select * from grem1; 1520delete from grem1; 1521alter server loopback options (drop batch_size); 1522 1523-- =================================================================== 1524-- test local triggers 1525-- =================================================================== 1526 1527-- Trigger functions "borrowed" from triggers regress test. 1528CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS $$ 1529BEGIN 1530 RAISE NOTICE 'trigger_func(%) called: action = %, when = %, level = %', 1531 TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL; 1532 RETURN NULL; 1533END;$$; 1534 1535CREATE TRIGGER trig_stmt_before BEFORE DELETE OR INSERT OR UPDATE ON rem1 1536 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); 1537CREATE TRIGGER trig_stmt_after AFTER DELETE OR INSERT OR UPDATE ON rem1 1538 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); 1539 1540CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger 1541LANGUAGE plpgsql AS $$ 1542 1543declare 1544 oldnew text[]; 1545 relid text; 1546 argstr text; 1547begin 1548 1549 relid := TG_relid::regclass; 1550 argstr := ''; 1551 for i in 0 .. TG_nargs - 1 loop 1552 if i > 0 then 1553 argstr := argstr || ', '; 1554 end if; 1555 argstr := argstr || TG_argv[i]; 1556 end loop; 1557 1558 RAISE NOTICE '%(%) % % % ON %', 1559 tg_name, argstr, TG_when, TG_level, TG_OP, relid; 1560 oldnew := '{}'::text[]; 1561 if TG_OP != 'INSERT' then 1562 oldnew := array_append(oldnew, format('OLD: %s', OLD)); 1563 end if; 1564 1565 if TG_OP != 'DELETE' then 1566 oldnew := array_append(oldnew, format('NEW: %s', NEW)); 1567 end if; 1568 1569 RAISE NOTICE '%', array_to_string(oldnew, ','); 1570 1571 if TG_OP = 'DELETE' then 1572 return OLD; 1573 else 1574 return NEW; 1575 end if; 1576end; 1577$$; 1578 1579-- Test basic functionality 1580CREATE TRIGGER trig_row_before 1581BEFORE INSERT OR UPDATE OR DELETE ON rem1 1582FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1583 1584CREATE TRIGGER trig_row_after 1585AFTER INSERT OR UPDATE OR DELETE ON rem1 1586FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1587 1588delete from rem1; 1589insert into rem1 values(1,'insert'); 1590update rem1 set f2 = 'update' where f1 = 1; 1591update rem1 set f2 = f2 || f2; 1592 1593 1594-- cleanup 1595DROP TRIGGER trig_row_before ON rem1; 1596DROP TRIGGER trig_row_after ON rem1; 1597DROP TRIGGER trig_stmt_before ON rem1; 1598DROP TRIGGER trig_stmt_after ON rem1; 1599 1600DELETE from rem1; 1601 1602-- Test multiple AFTER ROW triggers on a foreign table 1603CREATE TRIGGER trig_row_after1 1604AFTER INSERT OR UPDATE OR DELETE ON rem1 1605FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1606 1607CREATE TRIGGER trig_row_after2 1608AFTER INSERT OR UPDATE OR DELETE ON rem1 1609FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1610 1611insert into rem1 values(1,'insert'); 1612update rem1 set f2 = 'update' where f1 = 1; 1613update rem1 set f2 = f2 || f2; 1614delete from rem1; 1615 1616-- cleanup 1617DROP TRIGGER trig_row_after1 ON rem1; 1618DROP TRIGGER trig_row_after2 ON rem1; 1619 1620-- Test WHEN conditions 1621 1622CREATE TRIGGER trig_row_before_insupd 1623BEFORE INSERT OR UPDATE ON rem1 1624FOR EACH ROW 1625WHEN (NEW.f2 like '%update%') 1626EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1627 1628CREATE TRIGGER trig_row_after_insupd 1629AFTER INSERT OR UPDATE ON rem1 1630FOR EACH ROW 1631WHEN (NEW.f2 like '%update%') 1632EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1633 1634-- Insert or update not matching: nothing happens 1635INSERT INTO rem1 values(1, 'insert'); 1636UPDATE rem1 set f2 = 'test'; 1637 1638-- Insert or update matching: triggers are fired 1639INSERT INTO rem1 values(2, 'update'); 1640UPDATE rem1 set f2 = 'update update' where f1 = '2'; 1641 1642CREATE TRIGGER trig_row_before_delete 1643BEFORE DELETE ON rem1 1644FOR EACH ROW 1645WHEN (OLD.f2 like '%update%') 1646EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1647 1648CREATE TRIGGER trig_row_after_delete 1649AFTER DELETE ON rem1 1650FOR EACH ROW 1651WHEN (OLD.f2 like '%update%') 1652EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1653 1654-- Trigger is fired for f1=2, not for f1=1 1655DELETE FROM rem1; 1656 1657-- cleanup 1658DROP TRIGGER trig_row_before_insupd ON rem1; 1659DROP TRIGGER trig_row_after_insupd ON rem1; 1660DROP TRIGGER trig_row_before_delete ON rem1; 1661DROP TRIGGER trig_row_after_delete ON rem1; 1662 1663 1664-- Test various RETURN statements in BEFORE triggers. 1665 1666CREATE FUNCTION trig_row_before_insupdate() RETURNS TRIGGER AS $$ 1667 BEGIN 1668 NEW.f2 := NEW.f2 || ' triggered !'; 1669 RETURN NEW; 1670 END 1671$$ language plpgsql; 1672 1673CREATE TRIGGER trig_row_before_insupd 1674BEFORE INSERT OR UPDATE ON rem1 1675FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate(); 1676 1677-- The new values should have 'triggered' appended 1678INSERT INTO rem1 values(1, 'insert'); 1679SELECT * from loc1; 1680INSERT INTO rem1 values(2, 'insert') RETURNING f2; 1681SELECT * from loc1; 1682UPDATE rem1 set f2 = ''; 1683SELECT * from loc1; 1684UPDATE rem1 set f2 = 'skidoo' RETURNING f2; 1685SELECT * from loc1; 1686 1687EXPLAIN (verbose, costs off) 1688UPDATE rem1 set f1 = 10; -- all columns should be transmitted 1689UPDATE rem1 set f1 = 10; 1690SELECT * from loc1; 1691 1692DELETE FROM rem1; 1693 1694-- Add a second trigger, to check that the changes are propagated correctly 1695-- from trigger to trigger 1696CREATE TRIGGER trig_row_before_insupd2 1697BEFORE INSERT OR UPDATE ON rem1 1698FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate(); 1699 1700INSERT INTO rem1 values(1, 'insert'); 1701SELECT * from loc1; 1702INSERT INTO rem1 values(2, 'insert') RETURNING f2; 1703SELECT * from loc1; 1704UPDATE rem1 set f2 = ''; 1705SELECT * from loc1; 1706UPDATE rem1 set f2 = 'skidoo' RETURNING f2; 1707SELECT * from loc1; 1708 1709DROP TRIGGER trig_row_before_insupd ON rem1; 1710DROP TRIGGER trig_row_before_insupd2 ON rem1; 1711 1712DELETE from rem1; 1713 1714INSERT INTO rem1 VALUES (1, 'test'); 1715 1716-- Test with a trigger returning NULL 1717CREATE FUNCTION trig_null() RETURNS TRIGGER AS $$ 1718 BEGIN 1719 RETURN NULL; 1720 END 1721$$ language plpgsql; 1722 1723CREATE TRIGGER trig_null 1724BEFORE INSERT OR UPDATE OR DELETE ON rem1 1725FOR EACH ROW EXECUTE PROCEDURE trig_null(); 1726 1727-- Nothing should have changed. 1728INSERT INTO rem1 VALUES (2, 'test2'); 1729 1730SELECT * from loc1; 1731 1732UPDATE rem1 SET f2 = 'test2'; 1733 1734SELECT * from loc1; 1735 1736DELETE from rem1; 1737 1738SELECT * from loc1; 1739 1740DROP TRIGGER trig_null ON rem1; 1741DELETE from rem1; 1742 1743-- Test a combination of local and remote triggers 1744CREATE TRIGGER trig_row_before 1745BEFORE INSERT OR UPDATE OR DELETE ON rem1 1746FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1747 1748CREATE TRIGGER trig_row_after 1749AFTER INSERT OR UPDATE OR DELETE ON rem1 1750FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1751 1752CREATE TRIGGER trig_local_before BEFORE INSERT OR UPDATE ON loc1 1753FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate(); 1754 1755INSERT INTO rem1(f2) VALUES ('test'); 1756UPDATE rem1 SET f2 = 'testo'; 1757 1758-- Test returning a system attribute 1759INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid; 1760 1761-- cleanup 1762DROP TRIGGER trig_row_before ON rem1; 1763DROP TRIGGER trig_row_after ON rem1; 1764DROP TRIGGER trig_local_before ON loc1; 1765 1766 1767-- Test direct foreign table modification functionality 1768EXPLAIN (verbose, costs off) 1769DELETE FROM rem1; -- can be pushed down 1770EXPLAIN (verbose, costs off) 1771DELETE FROM rem1 WHERE false; -- currently can't be pushed down 1772 1773-- Test with statement-level triggers 1774CREATE TRIGGER trig_stmt_before 1775 BEFORE DELETE OR INSERT OR UPDATE ON rem1 1776 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); 1777EXPLAIN (verbose, costs off) 1778UPDATE rem1 set f2 = ''; -- can be pushed down 1779EXPLAIN (verbose, costs off) 1780DELETE FROM rem1; -- can be pushed down 1781DROP TRIGGER trig_stmt_before ON rem1; 1782 1783CREATE TRIGGER trig_stmt_after 1784 AFTER DELETE OR INSERT OR UPDATE ON rem1 1785 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); 1786EXPLAIN (verbose, costs off) 1787UPDATE rem1 set f2 = ''; -- can be pushed down 1788EXPLAIN (verbose, costs off) 1789DELETE FROM rem1; -- can be pushed down 1790DROP TRIGGER trig_stmt_after ON rem1; 1791 1792-- Test with row-level ON INSERT triggers 1793CREATE TRIGGER trig_row_before_insert 1794BEFORE INSERT ON rem1 1795FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1796EXPLAIN (verbose, costs off) 1797UPDATE rem1 set f2 = ''; -- can be pushed down 1798EXPLAIN (verbose, costs off) 1799DELETE FROM rem1; -- can be pushed down 1800DROP TRIGGER trig_row_before_insert ON rem1; 1801 1802CREATE TRIGGER trig_row_after_insert 1803AFTER INSERT ON rem1 1804FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1805EXPLAIN (verbose, costs off) 1806UPDATE rem1 set f2 = ''; -- can be pushed down 1807EXPLAIN (verbose, costs off) 1808DELETE FROM rem1; -- can be pushed down 1809DROP TRIGGER trig_row_after_insert ON rem1; 1810 1811-- Test with row-level ON UPDATE triggers 1812CREATE TRIGGER trig_row_before_update 1813BEFORE UPDATE ON rem1 1814FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1815EXPLAIN (verbose, costs off) 1816UPDATE rem1 set f2 = ''; -- can't be pushed down 1817EXPLAIN (verbose, costs off) 1818DELETE FROM rem1; -- can be pushed down 1819DROP TRIGGER trig_row_before_update ON rem1; 1820 1821CREATE TRIGGER trig_row_after_update 1822AFTER UPDATE ON rem1 1823FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1824EXPLAIN (verbose, costs off) 1825UPDATE rem1 set f2 = ''; -- can't be pushed down 1826EXPLAIN (verbose, costs off) 1827DELETE FROM rem1; -- can be pushed down 1828DROP TRIGGER trig_row_after_update ON rem1; 1829 1830-- Test with row-level ON DELETE triggers 1831CREATE TRIGGER trig_row_before_delete 1832BEFORE DELETE ON rem1 1833FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1834EXPLAIN (verbose, costs off) 1835UPDATE rem1 set f2 = ''; -- can be pushed down 1836EXPLAIN (verbose, costs off) 1837DELETE FROM rem1; -- can't be pushed down 1838DROP TRIGGER trig_row_before_delete ON rem1; 1839 1840CREATE TRIGGER trig_row_after_delete 1841AFTER DELETE ON rem1 1842FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1843EXPLAIN (verbose, costs off) 1844UPDATE rem1 set f2 = ''; -- can be pushed down 1845EXPLAIN (verbose, costs off) 1846DELETE FROM rem1; -- can't be pushed down 1847DROP TRIGGER trig_row_after_delete ON rem1; 1848 1849-- =================================================================== 1850-- test inheritance features 1851-- =================================================================== 1852 1853CREATE TABLE a (aa TEXT); 1854CREATE TABLE loct (aa TEXT, bb TEXT); 1855ALTER TABLE a SET (autovacuum_enabled = 'false'); 1856ALTER TABLE loct SET (autovacuum_enabled = 'false'); 1857CREATE FOREIGN TABLE b (bb TEXT) INHERITS (a) 1858 SERVER loopback OPTIONS (table_name 'loct'); 1859 1860INSERT INTO a(aa) VALUES('aaa'); 1861INSERT INTO a(aa) VALUES('aaaa'); 1862INSERT INTO a(aa) VALUES('aaaaa'); 1863 1864INSERT INTO b(aa) VALUES('bbb'); 1865INSERT INTO b(aa) VALUES('bbbb'); 1866INSERT INTO b(aa) VALUES('bbbbb'); 1867 1868SELECT tableoid::regclass, * FROM a; 1869SELECT tableoid::regclass, * FROM b; 1870SELECT tableoid::regclass, * FROM ONLY a; 1871 1872UPDATE a SET aa = 'zzzzzz' WHERE aa LIKE 'aaaa%'; 1873 1874SELECT tableoid::regclass, * FROM a; 1875SELECT tableoid::regclass, * FROM b; 1876SELECT tableoid::regclass, * FROM ONLY a; 1877 1878UPDATE b SET aa = 'new'; 1879 1880SELECT tableoid::regclass, * FROM a; 1881SELECT tableoid::regclass, * FROM b; 1882SELECT tableoid::regclass, * FROM ONLY a; 1883 1884UPDATE a SET aa = 'newtoo'; 1885 1886SELECT tableoid::regclass, * FROM a; 1887SELECT tableoid::regclass, * FROM b; 1888SELECT tableoid::regclass, * FROM ONLY a; 1889 1890DELETE FROM a; 1891 1892SELECT tableoid::regclass, * FROM a; 1893SELECT tableoid::regclass, * FROM b; 1894SELECT tableoid::regclass, * FROM ONLY a; 1895 1896DROP TABLE a CASCADE; 1897DROP TABLE loct; 1898 1899-- Check SELECT FOR UPDATE/SHARE with an inherited source table 1900create table loct1 (f1 int, f2 int, f3 int); 1901create table loct2 (f1 int, f2 int, f3 int); 1902 1903alter table loct1 set (autovacuum_enabled = 'false'); 1904alter table loct2 set (autovacuum_enabled = 'false'); 1905 1906create table foo (f1 int, f2 int); 1907create foreign table foo2 (f3 int) inherits (foo) 1908 server loopback options (table_name 'loct1'); 1909create table bar (f1 int, f2 int); 1910create foreign table bar2 (f3 int) inherits (bar) 1911 server loopback options (table_name 'loct2'); 1912 1913alter table foo set (autovacuum_enabled = 'false'); 1914alter table bar set (autovacuum_enabled = 'false'); 1915 1916insert into foo values(1,1); 1917insert into foo values(3,3); 1918insert into foo2 values(2,2,2); 1919insert into foo2 values(4,4,4); 1920insert into bar values(1,11); 1921insert into bar values(2,22); 1922insert into bar values(6,66); 1923insert into bar2 values(3,33,33); 1924insert into bar2 values(4,44,44); 1925insert into bar2 values(7,77,77); 1926 1927explain (verbose, costs off) 1928select * from bar where f1 in (select f1 from foo) for update; 1929select * from bar where f1 in (select f1 from foo) for update; 1930 1931explain (verbose, costs off) 1932select * from bar where f1 in (select f1 from foo) for share; 1933select * from bar where f1 in (select f1 from foo) for share; 1934 1935-- Now check SELECT FOR UPDATE/SHARE with an inherited source table, 1936-- where the parent is itself a foreign table 1937create table loct4 (f1 int, f2 int, f3 int); 1938create foreign table foo2child (f3 int) inherits (foo2) 1939 server loopback options (table_name 'loct4'); 1940 1941explain (verbose, costs off) 1942select * from bar where f1 in (select f1 from foo2) for share; 1943select * from bar where f1 in (select f1 from foo2) for share; 1944 1945drop foreign table foo2child; 1946 1947-- And with a local child relation of the foreign table parent 1948create table foo2child (f3 int) inherits (foo2); 1949 1950explain (verbose, costs off) 1951select * from bar where f1 in (select f1 from foo2) for share; 1952select * from bar where f1 in (select f1 from foo2) for share; 1953 1954drop table foo2child; 1955 1956-- Check UPDATE with inherited target and an inherited source table 1957explain (verbose, costs off) 1958update bar set f2 = f2 + 100 where f1 in (select f1 from foo); 1959update bar set f2 = f2 + 100 where f1 in (select f1 from foo); 1960 1961select tableoid::regclass, * from bar order by 1,2; 1962 1963-- Check UPDATE with inherited target and an appendrel subquery 1964explain (verbose, costs off) 1965update bar set f2 = f2 + 100 1966from 1967 ( select f1 from foo union all select f1+3 from foo ) ss 1968where bar.f1 = ss.f1; 1969update bar set f2 = f2 + 100 1970from 1971 ( select f1 from foo union all select f1+3 from foo ) ss 1972where bar.f1 = ss.f1; 1973 1974select tableoid::regclass, * from bar order by 1,2; 1975 1976-- Test forcing the remote server to produce sorted data for a merge join, 1977-- but the foreign table is an inheritance child. 1978truncate table loct1; 1979truncate table only foo; 1980\set num_rows_foo 2000 1981insert into loct1 select generate_series(0, :num_rows_foo, 2), generate_series(0, :num_rows_foo, 2), generate_series(0, :num_rows_foo, 2); 1982insert into foo select generate_series(1, :num_rows_foo, 2), generate_series(1, :num_rows_foo, 2); 1983SET enable_hashjoin to false; 1984SET enable_nestloop to false; 1985alter foreign table foo2 options (use_remote_estimate 'true'); 1986create index i_loct1_f1 on loct1(f1); 1987create index i_foo_f1 on foo(f1); 1988analyze foo; 1989analyze loct1; 1990-- inner join; expressions in the clauses appear in the equivalence class list 1991explain (verbose, costs off) 1992 select foo.f1, loct1.f1 from foo join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10; 1993select foo.f1, loct1.f1 from foo join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10; 1994-- outer join; expressions in the clauses do not appear in equivalence class 1995-- list but no output change as compared to the previous query 1996explain (verbose, costs off) 1997 select foo.f1, loct1.f1 from foo left join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10; 1998select foo.f1, loct1.f1 from foo left join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10; 1999RESET enable_hashjoin; 2000RESET enable_nestloop; 2001 2002-- Test that WHERE CURRENT OF is not supported 2003begin; 2004declare c cursor for select * from bar where f1 = 7; 2005fetch from c; 2006update bar set f2 = null where current of c; 2007rollback; 2008 2009explain (verbose, costs off) 2010delete from foo where f1 < 5 returning *; 2011delete from foo where f1 < 5 returning *; 2012explain (verbose, costs off) 2013update bar set f2 = f2 + 100 returning *; 2014update bar set f2 = f2 + 100 returning *; 2015 2016-- Test that UPDATE/DELETE with inherited target works with row-level triggers 2017CREATE TRIGGER trig_row_before 2018BEFORE UPDATE OR DELETE ON bar2 2019FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 2020 2021CREATE TRIGGER trig_row_after 2022AFTER UPDATE OR DELETE ON bar2 2023FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 2024 2025explain (verbose, costs off) 2026update bar set f2 = f2 + 100; 2027update bar set f2 = f2 + 100; 2028 2029explain (verbose, costs off) 2030delete from bar where f2 < 400; 2031delete from bar where f2 < 400; 2032 2033-- cleanup 2034drop table foo cascade; 2035drop table bar cascade; 2036drop table loct1; 2037drop table loct2; 2038 2039-- Test pushing down UPDATE/DELETE joins to the remote server 2040create table parent (a int, b text); 2041create table loct1 (a int, b text); 2042create table loct2 (a int, b text); 2043create foreign table remt1 (a int, b text) 2044 server loopback options (table_name 'loct1'); 2045create foreign table remt2 (a int, b text) 2046 server loopback options (table_name 'loct2'); 2047alter foreign table remt1 inherit parent; 2048 2049insert into remt1 values (1, 'foo'); 2050insert into remt1 values (2, 'bar'); 2051insert into remt2 values (1, 'foo'); 2052insert into remt2 values (2, 'bar'); 2053 2054analyze remt1; 2055analyze remt2; 2056 2057explain (verbose, costs off) 2058update parent set b = parent.b || remt2.b from remt2 where parent.a = remt2.a returning *; 2059update parent set b = parent.b || remt2.b from remt2 where parent.a = remt2.a returning *; 2060explain (verbose, costs off) 2061delete from parent using remt2 where parent.a = remt2.a returning parent; 2062delete from parent using remt2 where parent.a = remt2.a returning parent; 2063 2064-- cleanup 2065drop foreign table remt1; 2066drop foreign table remt2; 2067drop table loct1; 2068drop table loct2; 2069drop table parent; 2070 2071-- =================================================================== 2072-- test tuple routing for foreign-table partitions 2073-- =================================================================== 2074 2075-- Test insert tuple routing 2076create table itrtest (a int, b text) partition by list (a); 2077create table loct1 (a int check (a in (1)), b text); 2078create foreign table remp1 (a int check (a in (1)), b text) server loopback options (table_name 'loct1'); 2079create table loct2 (a int check (a in (2)), b text); 2080create foreign table remp2 (b text, a int check (a in (2))) server loopback options (table_name 'loct2'); 2081alter table itrtest attach partition remp1 for values in (1); 2082alter table itrtest attach partition remp2 for values in (2); 2083 2084insert into itrtest values (1, 'foo'); 2085insert into itrtest values (1, 'bar') returning *; 2086insert into itrtest values (2, 'baz'); 2087insert into itrtest values (2, 'qux') returning *; 2088insert into itrtest values (1, 'test1'), (2, 'test2') returning *; 2089 2090select tableoid::regclass, * FROM itrtest; 2091select tableoid::regclass, * FROM remp1; 2092select tableoid::regclass, * FROM remp2; 2093 2094delete from itrtest; 2095 2096create unique index loct1_idx on loct1 (a); 2097 2098-- DO NOTHING without an inference specification is supported 2099insert into itrtest values (1, 'foo') on conflict do nothing returning *; 2100insert into itrtest values (1, 'foo') on conflict do nothing returning *; 2101 2102-- But other cases are not supported 2103insert into itrtest values (1, 'bar') on conflict (a) do nothing; 2104insert into itrtest values (1, 'bar') on conflict (a) do update set b = excluded.b; 2105 2106select tableoid::regclass, * FROM itrtest; 2107 2108delete from itrtest; 2109 2110drop index loct1_idx; 2111 2112-- Test that remote triggers work with insert tuple routing 2113create function br_insert_trigfunc() returns trigger as $$ 2114begin 2115 new.b := new.b || ' triggered !'; 2116 return new; 2117end 2118$$ language plpgsql; 2119create trigger loct1_br_insert_trigger before insert on loct1 2120 for each row execute procedure br_insert_trigfunc(); 2121create trigger loct2_br_insert_trigger before insert on loct2 2122 for each row execute procedure br_insert_trigfunc(); 2123 2124-- The new values are concatenated with ' triggered !' 2125insert into itrtest values (1, 'foo') returning *; 2126insert into itrtest values (2, 'qux') returning *; 2127insert into itrtest values (1, 'test1'), (2, 'test2') returning *; 2128with result as (insert into itrtest values (1, 'test1'), (2, 'test2') returning *) select * from result; 2129 2130drop trigger loct1_br_insert_trigger on loct1; 2131drop trigger loct2_br_insert_trigger on loct2; 2132 2133drop table itrtest; 2134drop table loct1; 2135drop table loct2; 2136 2137-- Test update tuple routing 2138create table utrtest (a int, b text) partition by list (a); 2139create table loct (a int check (a in (1)), b text); 2140create foreign table remp (a int check (a in (1)), b text) server loopback options (table_name 'loct'); 2141create table locp (a int check (a in (2)), b text); 2142alter table utrtest attach partition remp for values in (1); 2143alter table utrtest attach partition locp for values in (2); 2144 2145insert into utrtest values (1, 'foo'); 2146insert into utrtest values (2, 'qux'); 2147 2148select tableoid::regclass, * FROM utrtest; 2149select tableoid::regclass, * FROM remp; 2150select tableoid::regclass, * FROM locp; 2151 2152-- It's not allowed to move a row from a partition that is foreign to another 2153update utrtest set a = 2 where b = 'foo' returning *; 2154 2155-- But the reverse is allowed 2156update utrtest set a = 1 where b = 'qux' returning *; 2157 2158select tableoid::regclass, * FROM utrtest; 2159select tableoid::regclass, * FROM remp; 2160select tableoid::regclass, * FROM locp; 2161 2162-- The executor should not let unexercised FDWs shut down 2163update utrtest set a = 1 where b = 'foo'; 2164 2165-- Test that remote triggers work with update tuple routing 2166create trigger loct_br_insert_trigger before insert on loct 2167 for each row execute procedure br_insert_trigfunc(); 2168 2169delete from utrtest; 2170insert into utrtest values (2, 'qux'); 2171 2172-- Check case where the foreign partition is a subplan target rel 2173explain (verbose, costs off) 2174update utrtest set a = 1 where a = 1 or a = 2 returning *; 2175-- The new values are concatenated with ' triggered !' 2176update utrtest set a = 1 where a = 1 or a = 2 returning *; 2177 2178delete from utrtest; 2179insert into utrtest values (2, 'qux'); 2180 2181-- Check case where the foreign partition isn't a subplan target rel 2182explain (verbose, costs off) 2183update utrtest set a = 1 where a = 2 returning *; 2184-- The new values are concatenated with ' triggered !' 2185update utrtest set a = 1 where a = 2 returning *; 2186 2187drop trigger loct_br_insert_trigger on loct; 2188 2189-- We can move rows to a foreign partition that has been updated already, 2190-- but can't move rows to a foreign partition that hasn't been updated yet 2191 2192delete from utrtest; 2193insert into utrtest values (1, 'foo'); 2194insert into utrtest values (2, 'qux'); 2195 2196-- Test the former case: 2197-- with a direct modification plan 2198explain (verbose, costs off) 2199update utrtest set a = 1 returning *; 2200update utrtest set a = 1 returning *; 2201 2202delete from utrtest; 2203insert into utrtest values (1, 'foo'); 2204insert into utrtest values (2, 'qux'); 2205 2206-- with a non-direct modification plan 2207explain (verbose, costs off) 2208update utrtest set a = 1 from (values (1), (2)) s(x) where a = s.x returning *; 2209update utrtest set a = 1 from (values (1), (2)) s(x) where a = s.x returning *; 2210 2211-- Change the definition of utrtest so that the foreign partition get updated 2212-- after the local partition 2213delete from utrtest; 2214alter table utrtest detach partition remp; 2215drop foreign table remp; 2216alter table loct drop constraint loct_a_check; 2217alter table loct add check (a in (3)); 2218create foreign table remp (a int check (a in (3)), b text) server loopback options (table_name 'loct'); 2219alter table utrtest attach partition remp for values in (3); 2220insert into utrtest values (2, 'qux'); 2221insert into utrtest values (3, 'xyzzy'); 2222 2223-- Test the latter case: 2224-- with a direct modification plan 2225explain (verbose, costs off) 2226update utrtest set a = 3 returning *; 2227update utrtest set a = 3 returning *; -- ERROR 2228 2229-- with a non-direct modification plan 2230explain (verbose, costs off) 2231update utrtest set a = 3 from (values (2), (3)) s(x) where a = s.x returning *; 2232update utrtest set a = 3 from (values (2), (3)) s(x) where a = s.x returning *; -- ERROR 2233 2234drop table utrtest; 2235drop table loct; 2236 2237-- Test copy tuple routing 2238create table ctrtest (a int, b text) partition by list (a); 2239create table loct1 (a int check (a in (1)), b text); 2240create foreign table remp1 (a int check (a in (1)), b text) server loopback options (table_name 'loct1'); 2241create table loct2 (a int check (a in (2)), b text); 2242create foreign table remp2 (b text, a int check (a in (2))) server loopback options (table_name 'loct2'); 2243alter table ctrtest attach partition remp1 for values in (1); 2244alter table ctrtest attach partition remp2 for values in (2); 2245 2246copy ctrtest from stdin; 22471 foo 22482 qux 2249\. 2250 2251select tableoid::regclass, * FROM ctrtest; 2252select tableoid::regclass, * FROM remp1; 2253select tableoid::regclass, * FROM remp2; 2254 2255-- Copying into foreign partitions directly should work as well 2256copy remp1 from stdin; 22571 bar 2258\. 2259 2260select tableoid::regclass, * FROM remp1; 2261 2262drop table ctrtest; 2263drop table loct1; 2264drop table loct2; 2265 2266-- =================================================================== 2267-- test COPY FROM 2268-- =================================================================== 2269 2270create table loc2 (f1 int, f2 text); 2271alter table loc2 set (autovacuum_enabled = 'false'); 2272create foreign table rem2 (f1 int, f2 text) server loopback options(table_name 'loc2'); 2273 2274-- Test basic functionality 2275copy rem2 from stdin; 22761 foo 22772 bar 2278\. 2279select * from rem2; 2280 2281delete from rem2; 2282 2283-- Test check constraints 2284alter table loc2 add constraint loc2_f1positive check (f1 >= 0); 2285alter foreign table rem2 add constraint rem2_f1positive check (f1 >= 0); 2286 2287-- check constraint is enforced on the remote side, not locally 2288copy rem2 from stdin; 22891 foo 22902 bar 2291\. 2292copy rem2 from stdin; -- ERROR 2293-1 xyzzy 2294\. 2295select * from rem2; 2296 2297alter foreign table rem2 drop constraint rem2_f1positive; 2298alter table loc2 drop constraint loc2_f1positive; 2299 2300delete from rem2; 2301 2302-- Test local triggers 2303create trigger trig_stmt_before before insert on rem2 2304 for each statement execute procedure trigger_func(); 2305create trigger trig_stmt_after after insert on rem2 2306 for each statement execute procedure trigger_func(); 2307create trigger trig_row_before before insert on rem2 2308 for each row execute procedure trigger_data(23,'skidoo'); 2309create trigger trig_row_after after insert on rem2 2310 for each row execute procedure trigger_data(23,'skidoo'); 2311 2312copy rem2 from stdin; 23131 foo 23142 bar 2315\. 2316select * from rem2; 2317 2318drop trigger trig_row_before on rem2; 2319drop trigger trig_row_after on rem2; 2320drop trigger trig_stmt_before on rem2; 2321drop trigger trig_stmt_after on rem2; 2322 2323delete from rem2; 2324 2325create trigger trig_row_before_insert before insert on rem2 2326 for each row execute procedure trig_row_before_insupdate(); 2327 2328-- The new values are concatenated with ' triggered !' 2329copy rem2 from stdin; 23301 foo 23312 bar 2332\. 2333select * from rem2; 2334 2335drop trigger trig_row_before_insert on rem2; 2336 2337delete from rem2; 2338 2339create trigger trig_null before insert on rem2 2340 for each row execute procedure trig_null(); 2341 2342-- Nothing happens 2343copy rem2 from stdin; 23441 foo 23452 bar 2346\. 2347select * from rem2; 2348 2349drop trigger trig_null on rem2; 2350 2351delete from rem2; 2352 2353-- Test remote triggers 2354create trigger trig_row_before_insert before insert on loc2 2355 for each row execute procedure trig_row_before_insupdate(); 2356 2357-- The new values are concatenated with ' triggered !' 2358copy rem2 from stdin; 23591 foo 23602 bar 2361\. 2362select * from rem2; 2363 2364drop trigger trig_row_before_insert on loc2; 2365 2366delete from rem2; 2367 2368create trigger trig_null before insert on loc2 2369 for each row execute procedure trig_null(); 2370 2371-- Nothing happens 2372copy rem2 from stdin; 23731 foo 23742 bar 2375\. 2376select * from rem2; 2377 2378drop trigger trig_null on loc2; 2379 2380delete from rem2; 2381 2382-- Test a combination of local and remote triggers 2383create trigger rem2_trig_row_before before insert on rem2 2384 for each row execute procedure trigger_data(23,'skidoo'); 2385create trigger rem2_trig_row_after after insert on rem2 2386 for each row execute procedure trigger_data(23,'skidoo'); 2387create trigger loc2_trig_row_before_insert before insert on loc2 2388 for each row execute procedure trig_row_before_insupdate(); 2389 2390copy rem2 from stdin; 23911 foo 23922 bar 2393\. 2394select * from rem2; 2395 2396drop trigger rem2_trig_row_before on rem2; 2397drop trigger rem2_trig_row_after on rem2; 2398drop trigger loc2_trig_row_before_insert on loc2; 2399 2400delete from rem2; 2401 2402-- test COPY FROM with foreign table created in the same transaction 2403create table loc3 (f1 int, f2 text); 2404begin; 2405create foreign table rem3 (f1 int, f2 text) 2406 server loopback options(table_name 'loc3'); 2407copy rem3 from stdin; 24081 foo 24092 bar 2410\. 2411commit; 2412select * from rem3; 2413drop foreign table rem3; 2414drop table loc3; 2415 2416-- =================================================================== 2417-- test for TRUNCATE 2418-- =================================================================== 2419CREATE TABLE tru_rtable0 (id int primary key); 2420CREATE FOREIGN TABLE tru_ftable (id int) 2421 SERVER loopback OPTIONS (table_name 'tru_rtable0'); 2422INSERT INTO tru_rtable0 (SELECT x FROM generate_series(1,10) x); 2423 2424CREATE TABLE tru_ptable (id int) PARTITION BY HASH(id); 2425CREATE TABLE tru_ptable__p0 PARTITION OF tru_ptable 2426 FOR VALUES WITH (MODULUS 2, REMAINDER 0); 2427CREATE TABLE tru_rtable1 (id int primary key); 2428CREATE FOREIGN TABLE tru_ftable__p1 PARTITION OF tru_ptable 2429 FOR VALUES WITH (MODULUS 2, REMAINDER 1) 2430 SERVER loopback OPTIONS (table_name 'tru_rtable1'); 2431INSERT INTO tru_ptable (SELECT x FROM generate_series(11,20) x); 2432 2433CREATE TABLE tru_pk_table(id int primary key); 2434CREATE TABLE tru_fk_table(fkey int references tru_pk_table(id)); 2435INSERT INTO tru_pk_table (SELECT x FROM generate_series(1,10) x); 2436INSERT INTO tru_fk_table (SELECT x % 10 + 1 FROM generate_series(5,25) x); 2437CREATE FOREIGN TABLE tru_pk_ftable (id int) 2438 SERVER loopback OPTIONS (table_name 'tru_pk_table'); 2439 2440CREATE TABLE tru_rtable_parent (id int); 2441CREATE TABLE tru_rtable_child (id int); 2442CREATE FOREIGN TABLE tru_ftable_parent (id int) 2443 SERVER loopback OPTIONS (table_name 'tru_rtable_parent'); 2444CREATE FOREIGN TABLE tru_ftable_child () INHERITS (tru_ftable_parent) 2445 SERVER loopback OPTIONS (table_name 'tru_rtable_child'); 2446INSERT INTO tru_rtable_parent (SELECT x FROM generate_series(1,8) x); 2447INSERT INTO tru_rtable_child (SELECT x FROM generate_series(10, 18) x); 2448 2449-- normal truncate 2450SELECT sum(id) FROM tru_ftable; -- 55 2451TRUNCATE tru_ftable; 2452SELECT count(*) FROM tru_rtable0; -- 0 2453SELECT count(*) FROM tru_ftable; -- 0 2454 2455-- 'truncatable' option 2456ALTER SERVER loopback OPTIONS (ADD truncatable 'false'); 2457TRUNCATE tru_ftable; -- error 2458ALTER FOREIGN TABLE tru_ftable OPTIONS (ADD truncatable 'true'); 2459TRUNCATE tru_ftable; -- accepted 2460ALTER FOREIGN TABLE tru_ftable OPTIONS (SET truncatable 'false'); 2461TRUNCATE tru_ftable; -- error 2462ALTER SERVER loopback OPTIONS (DROP truncatable); 2463ALTER FOREIGN TABLE tru_ftable OPTIONS (SET truncatable 'false'); 2464TRUNCATE tru_ftable; -- error 2465ALTER FOREIGN TABLE tru_ftable OPTIONS (SET truncatable 'true'); 2466TRUNCATE tru_ftable; -- accepted 2467 2468-- partitioned table with both local and foreign tables as partitions 2469SELECT sum(id) FROM tru_ptable; -- 155 2470TRUNCATE tru_ptable; 2471SELECT count(*) FROM tru_ptable; -- 0 2472SELECT count(*) FROM tru_ptable__p0; -- 0 2473SELECT count(*) FROM tru_ftable__p1; -- 0 2474SELECT count(*) FROM tru_rtable1; -- 0 2475 2476-- 'CASCADE' option 2477SELECT sum(id) FROM tru_pk_ftable; -- 55 2478TRUNCATE tru_pk_ftable; -- failed by FK reference 2479TRUNCATE tru_pk_ftable CASCADE; 2480SELECT count(*) FROM tru_pk_ftable; -- 0 2481SELECT count(*) FROM tru_fk_table; -- also truncated,0 2482 2483-- truncate two tables at a command 2484INSERT INTO tru_ftable (SELECT x FROM generate_series(1,8) x); 2485INSERT INTO tru_pk_ftable (SELECT x FROM generate_series(3,10) x); 2486SELECT count(*) from tru_ftable; -- 8 2487SELECT count(*) from tru_pk_ftable; -- 8 2488TRUNCATE tru_ftable, tru_pk_ftable CASCADE; 2489SELECT count(*) from tru_ftable; -- 0 2490SELECT count(*) from tru_pk_ftable; -- 0 2491 2492-- truncate with ONLY clause 2493-- Since ONLY is specified, the table tru_ftable_child that inherits 2494-- tru_ftable_parent locally is not truncated. 2495TRUNCATE ONLY tru_ftable_parent; 2496SELECT sum(id) FROM tru_ftable_parent; -- 126 2497TRUNCATE tru_ftable_parent; 2498SELECT count(*) FROM tru_ftable_parent; -- 0 2499 2500-- in case when remote table has inherited children 2501CREATE TABLE tru_rtable0_child () INHERITS (tru_rtable0); 2502INSERT INTO tru_rtable0 (SELECT x FROM generate_series(5,9) x); 2503INSERT INTO tru_rtable0_child (SELECT x FROM generate_series(10,14) x); 2504SELECT sum(id) FROM tru_ftable; -- 95 2505 2506-- Both parent and child tables in the foreign server are truncated 2507-- even though ONLY is specified because ONLY has no effect 2508-- when truncating a foreign table. 2509TRUNCATE ONLY tru_ftable; 2510SELECT count(*) FROM tru_ftable; -- 0 2511 2512INSERT INTO tru_rtable0 (SELECT x FROM generate_series(21,25) x); 2513INSERT INTO tru_rtable0_child (SELECT x FROM generate_series(26,30) x); 2514SELECT sum(id) FROM tru_ftable; -- 255 2515TRUNCATE tru_ftable; -- truncate both of parent and child 2516SELECT count(*) FROM tru_ftable; -- 0 2517 2518-- cleanup 2519DROP FOREIGN TABLE tru_ftable_parent, tru_ftable_child, tru_pk_ftable,tru_ftable__p1,tru_ftable; 2520DROP TABLE tru_rtable0, tru_rtable1, tru_ptable, tru_ptable__p0, tru_pk_table, tru_fk_table, 2521tru_rtable_parent,tru_rtable_child, tru_rtable0_child; 2522 2523-- =================================================================== 2524-- test IMPORT FOREIGN SCHEMA 2525-- =================================================================== 2526 2527CREATE SCHEMA import_source; 2528CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL); 2529CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL, c3 text collate "POSIX"); 2530CREATE TYPE typ1 AS (m1 int, m2 varchar); 2531CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1); 2532CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42)); 2533CREATE TABLE import_source."x 5" (c1 float8); 2534ALTER TABLE import_source."x 5" DROP COLUMN c1; 2535CREATE TABLE import_source."x 6" (c1 int, c2 int generated always as (c1 * 2) stored); 2536CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1); 2537CREATE TABLE import_source.t4_part PARTITION OF import_source.t4 2538 FOR VALUES FROM (1) TO (100); 2539CREATE TABLE import_source.t4_part2 PARTITION OF import_source.t4 2540 FOR VALUES FROM (100) TO (200); 2541 2542CREATE SCHEMA import_dest1; 2543IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1; 2544\det+ import_dest1.* 2545\d import_dest1.* 2546 2547-- Options 2548CREATE SCHEMA import_dest2; 2549IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2 2550 OPTIONS (import_default 'true'); 2551\det+ import_dest2.* 2552\d import_dest2.* 2553CREATE SCHEMA import_dest3; 2554IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3 2555 OPTIONS (import_collate 'false', import_generated 'false', import_not_null 'false'); 2556\det+ import_dest3.* 2557\d import_dest3.* 2558 2559-- Check LIMIT TO and EXCEPT 2560CREATE SCHEMA import_dest4; 2561IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch, t4_part) 2562 FROM SERVER loopback INTO import_dest4; 2563\det+ import_dest4.* 2564IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch, t4_part) 2565 FROM SERVER loopback INTO import_dest4; 2566\det+ import_dest4.* 2567 2568-- Assorted error cases 2569IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest4; 2570IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO import_dest4; 2571IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO notthere; 2572IMPORT FOREIGN SCHEMA nonesuch FROM SERVER nowhere INTO notthere; 2573 2574-- Check case of a type present only on the remote server. 2575-- We can fake this by dropping the type locally in our transaction. 2576CREATE TYPE "Colors" AS ENUM ('red', 'green', 'blue'); 2577CREATE TABLE import_source.t5 (c1 int, c2 text collate "C", "Col" "Colors"); 2578 2579CREATE SCHEMA import_dest5; 2580BEGIN; 2581DROP TYPE "Colors" CASCADE; 2582IMPORT FOREIGN SCHEMA import_source LIMIT TO (t5) 2583 FROM SERVER loopback INTO import_dest5; -- ERROR 2584 2585ROLLBACK; 2586 2587BEGIN; 2588 2589 2590CREATE SERVER fetch101 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( fetch_size '101' ); 2591 2592SELECT count(*) 2593FROM pg_foreign_server 2594WHERE srvname = 'fetch101' 2595AND srvoptions @> array['fetch_size=101']; 2596 2597ALTER SERVER fetch101 OPTIONS( SET fetch_size '202' ); 2598 2599SELECT count(*) 2600FROM pg_foreign_server 2601WHERE srvname = 'fetch101' 2602AND srvoptions @> array['fetch_size=101']; 2603 2604SELECT count(*) 2605FROM pg_foreign_server 2606WHERE srvname = 'fetch101' 2607AND srvoptions @> array['fetch_size=202']; 2608 2609CREATE FOREIGN TABLE table30000 ( x int ) SERVER fetch101 OPTIONS ( fetch_size '30000' ); 2610 2611SELECT COUNT(*) 2612FROM pg_foreign_table 2613WHERE ftrelid = 'table30000'::regclass 2614AND ftoptions @> array['fetch_size=30000']; 2615 2616ALTER FOREIGN TABLE table30000 OPTIONS ( SET fetch_size '60000'); 2617 2618SELECT COUNT(*) 2619FROM pg_foreign_table 2620WHERE ftrelid = 'table30000'::regclass 2621AND ftoptions @> array['fetch_size=30000']; 2622 2623SELECT COUNT(*) 2624FROM pg_foreign_table 2625WHERE ftrelid = 'table30000'::regclass 2626AND ftoptions @> array['fetch_size=60000']; 2627 2628ROLLBACK; 2629 2630-- =================================================================== 2631-- test partitionwise joins 2632-- =================================================================== 2633SET enable_partitionwise_join=on; 2634 2635CREATE TABLE fprt1 (a int, b int, c varchar) PARTITION BY RANGE(a); 2636CREATE TABLE fprt1_p1 (LIKE fprt1); 2637CREATE TABLE fprt1_p2 (LIKE fprt1); 2638ALTER TABLE fprt1_p1 SET (autovacuum_enabled = 'false'); 2639ALTER TABLE fprt1_p2 SET (autovacuum_enabled = 'false'); 2640INSERT INTO fprt1_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 2) i; 2641INSERT INTO fprt1_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 2) i; 2642CREATE FOREIGN TABLE ftprt1_p1 PARTITION OF fprt1 FOR VALUES FROM (0) TO (250) 2643 SERVER loopback OPTIONS (table_name 'fprt1_p1', use_remote_estimate 'true'); 2644CREATE FOREIGN TABLE ftprt1_p2 PARTITION OF fprt1 FOR VALUES FROM (250) TO (500) 2645 SERVER loopback OPTIONS (TABLE_NAME 'fprt1_p2'); 2646ANALYZE fprt1; 2647ANALYZE fprt1_p1; 2648ANALYZE fprt1_p2; 2649 2650CREATE TABLE fprt2 (a int, b int, c varchar) PARTITION BY RANGE(b); 2651CREATE TABLE fprt2_p1 (LIKE fprt2); 2652CREATE TABLE fprt2_p2 (LIKE fprt2); 2653ALTER TABLE fprt2_p1 SET (autovacuum_enabled = 'false'); 2654ALTER TABLE fprt2_p2 SET (autovacuum_enabled = 'false'); 2655INSERT INTO fprt2_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 3) i; 2656INSERT INTO fprt2_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 3) i; 2657CREATE FOREIGN TABLE ftprt2_p1 (b int, c varchar, a int) 2658 SERVER loopback OPTIONS (table_name 'fprt2_p1', use_remote_estimate 'true'); 2659ALTER TABLE fprt2 ATTACH PARTITION ftprt2_p1 FOR VALUES FROM (0) TO (250); 2660CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (250) TO (500) 2661 SERVER loopback OPTIONS (table_name 'fprt2_p2', use_remote_estimate 'true'); 2662ANALYZE fprt2; 2663ANALYZE fprt2_p1; 2664ANALYZE fprt2_p2; 2665 2666-- inner join three tables 2667EXPLAIN (COSTS OFF) 2668SELECT t1.a,t2.b,t3.c FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) INNER JOIN fprt1 t3 ON (t2.b = t3.a) WHERE t1.a % 25 =0 ORDER BY 1,2,3; 2669SELECT t1.a,t2.b,t3.c FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) INNER JOIN fprt1 t3 ON (t2.b = t3.a) WHERE t1.a % 25 =0 ORDER BY 1,2,3; 2670 2671-- left outer join + nullable clause 2672EXPLAIN (VERBOSE, COSTS OFF) 2673SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3; 2674SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3; 2675 2676-- with whole-row reference; partitionwise join does not apply 2677EXPLAIN (COSTS OFF) 2678SELECT t1.wr, t2.wr FROM (SELECT t1 wr, a FROM fprt1 t1 WHERE t1.a % 25 = 0) t1 FULL JOIN (SELECT t2 wr, b FROM fprt2 t2 WHERE t2.b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY 1,2; 2679SELECT t1.wr, t2.wr FROM (SELECT t1 wr, a FROM fprt1 t1 WHERE t1.a % 25 = 0) t1 FULL JOIN (SELECT t2 wr, b FROM fprt2 t2 WHERE t2.b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY 1,2; 2680 2681-- join with lateral reference 2682EXPLAIN (COSTS OFF) 2683SELECT t1.a,t1.b FROM fprt1 t1, LATERAL (SELECT t2.a, t2.b FROM fprt2 t2 WHERE t1.a = t2.b AND t1.b = t2.a) q WHERE t1.a%25 = 0 ORDER BY 1,2; 2684SELECT t1.a,t1.b FROM fprt1 t1, LATERAL (SELECT t2.a, t2.b FROM fprt2 t2 WHERE t1.a = t2.b AND t1.b = t2.a) q WHERE t1.a%25 = 0 ORDER BY 1,2; 2685 2686-- with PHVs, partitionwise join selected but no join pushdown 2687EXPLAIN (COSTS OFF) 2688SELECT t1.a, t1.phv, t2.b, t2.phv FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE a % 25 = 0) t1 FULL JOIN (SELECT 't2_phv' phv, * FROM fprt2 WHERE b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY t1.a, t2.b; 2689SELECT t1.a, t1.phv, t2.b, t2.phv FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE a % 25 = 0) t1 FULL JOIN (SELECT 't2_phv' phv, * FROM fprt2 WHERE b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY t1.a, t2.b; 2690 2691-- test FOR UPDATE; partitionwise join does not apply 2692EXPLAIN (COSTS OFF) 2693SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1; 2694SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1; 2695 2696RESET enable_partitionwise_join; 2697 2698 2699-- =================================================================== 2700-- test partitionwise aggregates 2701-- =================================================================== 2702 2703CREATE TABLE pagg_tab (a int, b int, c text) PARTITION BY RANGE(a); 2704 2705CREATE TABLE pagg_tab_p1 (LIKE pagg_tab); 2706CREATE TABLE pagg_tab_p2 (LIKE pagg_tab); 2707CREATE TABLE pagg_tab_p3 (LIKE pagg_tab); 2708 2709INSERT INTO pagg_tab_p1 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 10; 2710INSERT INTO pagg_tab_p2 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 20 and (i % 30) >= 10; 2711INSERT INTO pagg_tab_p3 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 30 and (i % 30) >= 20; 2712 2713-- Create foreign partitions 2714CREATE FOREIGN TABLE fpagg_tab_p1 PARTITION OF pagg_tab FOR VALUES FROM (0) TO (10) SERVER loopback OPTIONS (table_name 'pagg_tab_p1'); 2715CREATE FOREIGN TABLE fpagg_tab_p2 PARTITION OF pagg_tab FOR VALUES FROM (10) TO (20) SERVER loopback OPTIONS (table_name 'pagg_tab_p2'); 2716CREATE FOREIGN TABLE fpagg_tab_p3 PARTITION OF pagg_tab FOR VALUES FROM (20) TO (30) SERVER loopback OPTIONS (table_name 'pagg_tab_p3'); 2717 2718ANALYZE pagg_tab; 2719ANALYZE fpagg_tab_p1; 2720ANALYZE fpagg_tab_p2; 2721ANALYZE fpagg_tab_p3; 2722 2723-- When GROUP BY clause matches with PARTITION KEY. 2724-- Plan with partitionwise aggregates is disabled 2725SET enable_partitionwise_aggregate TO false; 2726EXPLAIN (COSTS OFF) 2727SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1; 2728 2729-- Plan with partitionwise aggregates is enabled 2730SET enable_partitionwise_aggregate TO true; 2731EXPLAIN (COSTS OFF) 2732SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1; 2733SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1; 2734 2735-- Check with whole-row reference 2736-- Should have all the columns in the target list for the given relation 2737EXPLAIN (VERBOSE, COSTS OFF) 2738SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1; 2739SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1; 2740 2741-- When GROUP BY clause does not match with PARTITION KEY. 2742EXPLAIN (COSTS OFF) 2743SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700 ORDER BY 1; 2744 2745-- =================================================================== 2746-- access rights and superuser 2747-- =================================================================== 2748 2749-- Non-superuser cannot create a FDW without a password in the connstr 2750CREATE ROLE regress_nosuper NOSUPERUSER; 2751 2752GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO regress_nosuper; 2753 2754SET ROLE regress_nosuper; 2755 2756SHOW is_superuser; 2757 2758-- This will be OK, we can create the FDW 2759DO $d$ 2760 BEGIN 2761 EXECUTE $$CREATE SERVER loopback_nopw FOREIGN DATA WRAPPER postgres_fdw 2762 OPTIONS (dbname '$$||current_database()||$$', 2763 port '$$||current_setting('port')||$$' 2764 )$$; 2765 END; 2766$d$; 2767 2768-- But creation of user mappings for non-superusers should fail 2769CREATE USER MAPPING FOR public SERVER loopback_nopw; 2770CREATE USER MAPPING FOR CURRENT_USER SERVER loopback_nopw; 2771 2772CREATE FOREIGN TABLE ft1_nopw ( 2773 c1 int NOT NULL, 2774 c2 int NOT NULL, 2775 c3 text, 2776 c4 timestamptz, 2777 c5 timestamp, 2778 c6 varchar(10), 2779 c7 char(10) default 'ft1', 2780 c8 user_enum 2781) SERVER loopback_nopw OPTIONS (schema_name 'public', table_name 'ft1'); 2782 2783SELECT 1 FROM ft1_nopw LIMIT 1; 2784 2785-- If we add a password to the connstr it'll fail, because we don't allow passwords 2786-- in connstrs only in user mappings. 2787 2788DO $d$ 2789 BEGIN 2790 EXECUTE $$ALTER SERVER loopback_nopw OPTIONS (ADD password 'dummypw')$$; 2791 END; 2792$d$; 2793 2794-- If we add a password for our user mapping instead, we should get a different 2795-- error because the password wasn't actually *used* when we run with trust auth. 2796-- 2797-- This won't work with installcheck, but neither will most of the FDW checks. 2798 2799ALTER USER MAPPING FOR CURRENT_USER SERVER loopback_nopw OPTIONS (ADD password 'dummypw'); 2800 2801SELECT 1 FROM ft1_nopw LIMIT 1; 2802 2803-- Unpriv user cannot make the mapping passwordless 2804ALTER USER MAPPING FOR CURRENT_USER SERVER loopback_nopw OPTIONS (ADD password_required 'false'); 2805 2806 2807SELECT 1 FROM ft1_nopw LIMIT 1; 2808 2809RESET ROLE; 2810 2811-- But the superuser can 2812ALTER USER MAPPING FOR regress_nosuper SERVER loopback_nopw OPTIONS (ADD password_required 'false'); 2813 2814SET ROLE regress_nosuper; 2815 2816-- Should finally work now 2817SELECT 1 FROM ft1_nopw LIMIT 1; 2818 2819-- unpriv user also cannot set sslcert / sslkey on the user mapping 2820-- first set password_required so we see the right error messages 2821ALTER USER MAPPING FOR CURRENT_USER SERVER loopback_nopw OPTIONS (SET password_required 'true'); 2822ALTER USER MAPPING FOR CURRENT_USER SERVER loopback_nopw OPTIONS (ADD sslcert 'foo.crt'); 2823ALTER USER MAPPING FOR CURRENT_USER SERVER loopback_nopw OPTIONS (ADD sslkey 'foo.key'); 2824 2825-- We're done with the role named after a specific user and need to check the 2826-- changes to the public mapping. 2827DROP USER MAPPING FOR CURRENT_USER SERVER loopback_nopw; 2828 2829-- This will fail again as it'll resolve the user mapping for public, which 2830-- lacks password_required=false 2831SELECT 1 FROM ft1_nopw LIMIT 1; 2832 2833RESET ROLE; 2834 2835-- The user mapping for public is passwordless and lacks the password_required=false 2836-- mapping option, but will work because the current user is a superuser. 2837SELECT 1 FROM ft1_nopw LIMIT 1; 2838 2839-- cleanup 2840DROP USER MAPPING FOR public SERVER loopback_nopw; 2841DROP OWNED BY regress_nosuper; 2842DROP ROLE regress_nosuper; 2843 2844-- Clean-up 2845RESET enable_partitionwise_aggregate; 2846 2847-- Two-phase transactions are not supported. 2848BEGIN; 2849SELECT count(*) FROM ft1; 2850-- error here 2851PREPARE TRANSACTION 'fdw_tpc'; 2852ROLLBACK; 2853 2854-- =================================================================== 2855-- reestablish new connection 2856-- =================================================================== 2857 2858-- Change application_name of remote connection to special one 2859-- so that we can easily terminate the connection later. 2860ALTER SERVER loopback OPTIONS (application_name 'fdw_retry_check'); 2861 2862-- If debug_discard_caches is active, it results in 2863-- dropping remote connections after every transaction, making it 2864-- impossible to test termination meaningfully. So turn that off 2865-- for this test. 2866SET debug_discard_caches = 0; 2867 2868-- Make sure we have a remote connection. 2869SELECT 1 FROM ft1 LIMIT 1; 2870 2871-- Terminate the remote connection and wait for the termination to complete. 2872SELECT pg_terminate_backend(pid, 180000) FROM pg_stat_activity 2873 WHERE application_name = 'fdw_retry_check'; 2874 2875-- This query should detect the broken connection when starting new remote 2876-- transaction, reestablish new connection, and then succeed. 2877BEGIN; 2878SELECT 1 FROM ft1 LIMIT 1; 2879 2880-- If we detect the broken connection when starting a new remote 2881-- subtransaction, we should fail instead of establishing a new connection. 2882-- Terminate the remote connection and wait for the termination to complete. 2883SELECT pg_terminate_backend(pid, 180000) FROM pg_stat_activity 2884 WHERE application_name = 'fdw_retry_check'; 2885SAVEPOINT s; 2886-- The text of the error might vary across platforms, so only show SQLSTATE. 2887\set VERBOSITY sqlstate 2888SELECT 1 FROM ft1 LIMIT 1; -- should fail 2889\set VERBOSITY default 2890COMMIT; 2891 2892RESET debug_discard_caches; 2893 2894-- ============================================================================= 2895-- test connection invalidation cases and postgres_fdw_get_connections function 2896-- ============================================================================= 2897-- Let's ensure to close all the existing cached connections. 2898SELECT 1 FROM postgres_fdw_disconnect_all(); 2899-- No cached connections, so no records should be output. 2900SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1; 2901-- This test case is for closing the connection in pgfdw_xact_callback 2902BEGIN; 2903-- Connection xact depth becomes 1 i.e. the connection is in midst of the xact. 2904SELECT 1 FROM ft1 LIMIT 1; 2905SELECT 1 FROM ft7 LIMIT 1; 2906-- List all the existing cached connections. loopback and loopback3 should be 2907-- output. 2908SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1; 2909-- Connections are not closed at the end of the alter and drop statements. 2910-- That's because the connections are in midst of this xact, 2911-- they are just marked as invalid in pgfdw_inval_callback. 2912ALTER SERVER loopback OPTIONS (ADD use_remote_estimate 'off'); 2913DROP SERVER loopback3 CASCADE; 2914-- List all the existing cached connections. loopback and loopback3 2915-- should be output as invalid connections. Also the server name for 2916-- loopback3 should be NULL because the server was dropped. 2917SELECT * FROM postgres_fdw_get_connections() ORDER BY 1; 2918-- The invalid connections get closed in pgfdw_xact_callback during commit. 2919COMMIT; 2920-- All cached connections were closed while committing above xact, so no 2921-- records should be output. 2922SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1; 2923 2924-- ======================================================================= 2925-- test postgres_fdw_disconnect and postgres_fdw_disconnect_all functions 2926-- ======================================================================= 2927BEGIN; 2928-- Ensure to cache loopback connection. 2929SELECT 1 FROM ft1 LIMIT 1; 2930-- Ensure to cache loopback2 connection. 2931SELECT 1 FROM ft6 LIMIT 1; 2932-- List all the existing cached connections. loopback and loopback2 should be 2933-- output. 2934SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1; 2935-- Issue a warning and return false as loopback connection is still in use and 2936-- can not be closed. 2937SELECT postgres_fdw_disconnect('loopback'); 2938-- List all the existing cached connections. loopback and loopback2 should be 2939-- output. 2940SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1; 2941-- Return false as connections are still in use, warnings are issued. 2942-- But disable warnings temporarily because the order of them is not stable. 2943SET client_min_messages = 'ERROR'; 2944SELECT postgres_fdw_disconnect_all(); 2945RESET client_min_messages; 2946COMMIT; 2947-- Ensure that loopback2 connection is closed. 2948SELECT 1 FROM postgres_fdw_disconnect('loopback2'); 2949SELECT server_name FROM postgres_fdw_get_connections() WHERE server_name = 'loopback2'; 2950-- Return false as loopback2 connection is closed already. 2951SELECT postgres_fdw_disconnect('loopback2'); 2952-- Return an error as there is no foreign server with given name. 2953SELECT postgres_fdw_disconnect('unknownserver'); 2954-- Let's ensure to close all the existing cached connections. 2955SELECT 1 FROM postgres_fdw_disconnect_all(); 2956-- No cached connections, so no records should be output. 2957SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1; 2958 2959-- ============================================================================= 2960-- test case for having multiple cached connections for a foreign server 2961-- ============================================================================= 2962CREATE ROLE regress_multi_conn_user1 SUPERUSER; 2963CREATE ROLE regress_multi_conn_user2 SUPERUSER; 2964CREATE USER MAPPING FOR regress_multi_conn_user1 SERVER loopback; 2965CREATE USER MAPPING FOR regress_multi_conn_user2 SERVER loopback; 2966 2967BEGIN; 2968-- Will cache loopback connection with user mapping for regress_multi_conn_user1 2969SET ROLE regress_multi_conn_user1; 2970SELECT 1 FROM ft1 LIMIT 1; 2971RESET ROLE; 2972 2973-- Will cache loopback connection with user mapping for regress_multi_conn_user2 2974SET ROLE regress_multi_conn_user2; 2975SELECT 1 FROM ft1 LIMIT 1; 2976RESET ROLE; 2977 2978-- Should output two connections for loopback server 2979SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1; 2980COMMIT; 2981-- Let's ensure to close all the existing cached connections. 2982SELECT 1 FROM postgres_fdw_disconnect_all(); 2983-- No cached connections, so no records should be output. 2984SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1; 2985 2986-- Clean up 2987DROP USER MAPPING FOR regress_multi_conn_user1 SERVER loopback; 2988DROP USER MAPPING FOR regress_multi_conn_user2 SERVER loopback; 2989DROP ROLE regress_multi_conn_user1; 2990DROP ROLE regress_multi_conn_user2; 2991 2992-- =================================================================== 2993-- Test foreign server level option keep_connections 2994-- =================================================================== 2995-- By default, the connections associated with foreign server are cached i.e. 2996-- keep_connections option is on. Set it to off. 2997ALTER SERVER loopback OPTIONS (keep_connections 'off'); 2998-- connection to loopback server is closed at the end of xact 2999-- as keep_connections was set to off. 3000SELECT 1 FROM ft1 LIMIT 1; 3001-- No cached connections, so no records should be output. 3002SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1; 3003ALTER SERVER loopback OPTIONS (SET keep_connections 'on'); 3004 3005-- =================================================================== 3006-- batch insert 3007-- =================================================================== 3008 3009BEGIN; 3010 3011CREATE SERVER batch10 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( batch_size '10' ); 3012 3013SELECT count(*) 3014FROM pg_foreign_server 3015WHERE srvname = 'batch10' 3016AND srvoptions @> array['batch_size=10']; 3017 3018ALTER SERVER batch10 OPTIONS( SET batch_size '20' ); 3019 3020SELECT count(*) 3021FROM pg_foreign_server 3022WHERE srvname = 'batch10' 3023AND srvoptions @> array['batch_size=10']; 3024 3025SELECT count(*) 3026FROM pg_foreign_server 3027WHERE srvname = 'batch10' 3028AND srvoptions @> array['batch_size=20']; 3029 3030CREATE FOREIGN TABLE table30 ( x int ) SERVER batch10 OPTIONS ( batch_size '30' ); 3031 3032SELECT COUNT(*) 3033FROM pg_foreign_table 3034WHERE ftrelid = 'table30'::regclass 3035AND ftoptions @> array['batch_size=30']; 3036 3037ALTER FOREIGN TABLE table30 OPTIONS ( SET batch_size '40'); 3038 3039SELECT COUNT(*) 3040FROM pg_foreign_table 3041WHERE ftrelid = 'table30'::regclass 3042AND ftoptions @> array['batch_size=30']; 3043 3044SELECT COUNT(*) 3045FROM pg_foreign_table 3046WHERE ftrelid = 'table30'::regclass 3047AND ftoptions @> array['batch_size=40']; 3048 3049ROLLBACK; 3050 3051CREATE TABLE batch_table ( x int ); 3052 3053CREATE FOREIGN TABLE ftable ( x int ) SERVER loopback OPTIONS ( table_name 'batch_table', batch_size '10' ); 3054EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO ftable SELECT * FROM generate_series(1, 10) i; 3055INSERT INTO ftable SELECT * FROM generate_series(1, 10) i; 3056INSERT INTO ftable SELECT * FROM generate_series(11, 31) i; 3057INSERT INTO ftable VALUES (32); 3058INSERT INTO ftable VALUES (33), (34); 3059SELECT COUNT(*) FROM ftable; 3060TRUNCATE batch_table; 3061DROP FOREIGN TABLE ftable; 3062 3063-- try if large batches exceed max number of bind parameters 3064CREATE FOREIGN TABLE ftable ( x int ) SERVER loopback OPTIONS ( table_name 'batch_table', batch_size '100000' ); 3065INSERT INTO ftable SELECT * FROM generate_series(1, 70000) i; 3066SELECT COUNT(*) FROM ftable; 3067TRUNCATE batch_table; 3068DROP FOREIGN TABLE ftable; 3069 3070-- Disable batch insert 3071CREATE FOREIGN TABLE ftable ( x int ) SERVER loopback OPTIONS ( table_name 'batch_table', batch_size '1' ); 3072EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO ftable VALUES (1), (2); 3073INSERT INTO ftable VALUES (1), (2); 3074SELECT COUNT(*) FROM ftable; 3075DROP FOREIGN TABLE ftable; 3076DROP TABLE batch_table; 3077 3078-- Use partitioning 3079CREATE TABLE batch_table ( x int ) PARTITION BY HASH (x); 3080 3081CREATE TABLE batch_table_p0 (LIKE batch_table); 3082CREATE FOREIGN TABLE batch_table_p0f 3083 PARTITION OF batch_table 3084 FOR VALUES WITH (MODULUS 3, REMAINDER 0) 3085 SERVER loopback 3086 OPTIONS (table_name 'batch_table_p0', batch_size '10'); 3087 3088CREATE TABLE batch_table_p1 (LIKE batch_table); 3089CREATE FOREIGN TABLE batch_table_p1f 3090 PARTITION OF batch_table 3091 FOR VALUES WITH (MODULUS 3, REMAINDER 1) 3092 SERVER loopback 3093 OPTIONS (table_name 'batch_table_p1', batch_size '1'); 3094 3095CREATE TABLE batch_table_p2 3096 PARTITION OF batch_table 3097 FOR VALUES WITH (MODULUS 3, REMAINDER 2); 3098 3099INSERT INTO batch_table SELECT * FROM generate_series(1, 66) i; 3100SELECT COUNT(*) FROM batch_table; 3101 3102-- Check that enabling batched inserts doesn't interfere with cross-partition 3103-- updates 3104CREATE TABLE batch_cp_upd_test (a int) PARTITION BY LIST (a); 3105CREATE TABLE batch_cp_upd_test1 (LIKE batch_cp_upd_test); 3106CREATE FOREIGN TABLE batch_cp_upd_test1_f 3107 PARTITION OF batch_cp_upd_test 3108 FOR VALUES IN (1) 3109 SERVER loopback 3110 OPTIONS (table_name 'batch_cp_upd_test1', batch_size '10'); 3111CREATE TABLE batch_cp_up_test1 PARTITION OF batch_cp_upd_test 3112 FOR VALUES IN (2); 3113INSERT INTO batch_cp_upd_test VALUES (1), (2); 3114 3115-- The following moves a row from the local partition to the foreign one 3116UPDATE batch_cp_upd_test t SET a = 1 FROM (VALUES (1), (2)) s(a) WHERE t.a = s.a; 3117SELECT tableoid::regclass, * FROM batch_cp_upd_test; 3118 3119-- Clean up 3120DROP TABLE batch_table, batch_cp_upd_test, batch_table_p0, batch_table_p1 CASCADE; 3121 3122-- Use partitioning 3123ALTER SERVER loopback OPTIONS (ADD batch_size '10'); 3124 3125CREATE TABLE batch_table ( x int, field1 text, field2 text) PARTITION BY HASH (x); 3126 3127CREATE TABLE batch_table_p0 (LIKE batch_table); 3128ALTER TABLE batch_table_p0 ADD CONSTRAINT p0_pkey PRIMARY KEY (x); 3129CREATE FOREIGN TABLE batch_table_p0f 3130 PARTITION OF batch_table 3131 FOR VALUES WITH (MODULUS 2, REMAINDER 0) 3132 SERVER loopback 3133 OPTIONS (table_name 'batch_table_p0'); 3134 3135CREATE TABLE batch_table_p1 (LIKE batch_table); 3136ALTER TABLE batch_table_p1 ADD CONSTRAINT p1_pkey PRIMARY KEY (x); 3137CREATE FOREIGN TABLE batch_table_p1f 3138 PARTITION OF batch_table 3139 FOR VALUES WITH (MODULUS 2, REMAINDER 1) 3140 SERVER loopback 3141 OPTIONS (table_name 'batch_table_p1'); 3142 3143INSERT INTO batch_table SELECT i, 'test'||i, 'test'|| i FROM generate_series(1, 50) i; 3144SELECT COUNT(*) FROM batch_table; 3145SELECT * FROM batch_table ORDER BY x; 3146 3147ALTER SERVER loopback OPTIONS (DROP batch_size); 3148 3149-- =================================================================== 3150-- test asynchronous execution 3151-- =================================================================== 3152 3153ALTER SERVER loopback OPTIONS (DROP extensions); 3154ALTER SERVER loopback OPTIONS (ADD async_capable 'true'); 3155ALTER SERVER loopback2 OPTIONS (ADD async_capable 'true'); 3156 3157CREATE TABLE async_pt (a int, b int, c text) PARTITION BY RANGE (a); 3158CREATE TABLE base_tbl1 (a int, b int, c text); 3159CREATE TABLE base_tbl2 (a int, b int, c text); 3160CREATE FOREIGN TABLE async_p1 PARTITION OF async_pt FOR VALUES FROM (1000) TO (2000) 3161 SERVER loopback OPTIONS (table_name 'base_tbl1'); 3162CREATE FOREIGN TABLE async_p2 PARTITION OF async_pt FOR VALUES FROM (2000) TO (3000) 3163 SERVER loopback2 OPTIONS (table_name 'base_tbl2'); 3164INSERT INTO async_p1 SELECT 1000 + i, i, to_char(i, 'FM0000') FROM generate_series(0, 999, 5) i; 3165INSERT INTO async_p2 SELECT 2000 + i, i, to_char(i, 'FM0000') FROM generate_series(0, 999, 5) i; 3166ANALYZE async_pt; 3167 3168-- simple queries 3169CREATE TABLE result_tbl (a int, b int, c text); 3170 3171EXPLAIN (VERBOSE, COSTS OFF) 3172INSERT INTO result_tbl SELECT * FROM async_pt WHERE b % 100 = 0; 3173INSERT INTO result_tbl SELECT * FROM async_pt WHERE b % 100 = 0; 3174 3175SELECT * FROM result_tbl ORDER BY a; 3176DELETE FROM result_tbl; 3177 3178EXPLAIN (VERBOSE, COSTS OFF) 3179INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505; 3180INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505; 3181 3182SELECT * FROM result_tbl ORDER BY a; 3183DELETE FROM result_tbl; 3184 3185-- Check case where multiple partitions use the same connection 3186CREATE TABLE base_tbl3 (a int, b int, c text); 3187CREATE FOREIGN TABLE async_p3 PARTITION OF async_pt FOR VALUES FROM (3000) TO (4000) 3188 SERVER loopback2 OPTIONS (table_name 'base_tbl3'); 3189INSERT INTO async_p3 SELECT 3000 + i, i, to_char(i, 'FM0000') FROM generate_series(0, 999, 5) i; 3190ANALYZE async_pt; 3191 3192EXPLAIN (VERBOSE, COSTS OFF) 3193INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505; 3194INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505; 3195 3196SELECT * FROM result_tbl ORDER BY a; 3197DELETE FROM result_tbl; 3198 3199DROP FOREIGN TABLE async_p3; 3200DROP TABLE base_tbl3; 3201 3202-- Check case where the partitioned table has local/remote partitions 3203CREATE TABLE async_p3 PARTITION OF async_pt FOR VALUES FROM (3000) TO (4000); 3204INSERT INTO async_p3 SELECT 3000 + i, i, to_char(i, 'FM0000') FROM generate_series(0, 999, 5) i; 3205ANALYZE async_pt; 3206 3207EXPLAIN (VERBOSE, COSTS OFF) 3208INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505; 3209INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505; 3210 3211SELECT * FROM result_tbl ORDER BY a; 3212DELETE FROM result_tbl; 3213 3214-- partitionwise joins 3215SET enable_partitionwise_join TO true; 3216 3217CREATE TABLE join_tbl (a1 int, b1 int, c1 text, a2 int, b2 int, c2 text); 3218 3219EXPLAIN (VERBOSE, COSTS OFF) 3220INSERT INTO join_tbl SELECT * FROM async_pt t1, async_pt t2 WHERE t1.a = t2.a AND t1.b = t2.b AND t1.b % 100 = 0; 3221INSERT INTO join_tbl SELECT * FROM async_pt t1, async_pt t2 WHERE t1.a = t2.a AND t1.b = t2.b AND t1.b % 100 = 0; 3222 3223SELECT * FROM join_tbl ORDER BY a1; 3224DELETE FROM join_tbl; 3225 3226RESET enable_partitionwise_join; 3227 3228-- Test rescan of an async Append node with do_exec_prune=false 3229SET enable_hashjoin TO false; 3230 3231EXPLAIN (VERBOSE, COSTS OFF) 3232INSERT INTO join_tbl SELECT * FROM async_p1 t1, async_pt t2 WHERE t1.a = t2.a AND t1.b = t2.b AND t1.b % 100 = 0; 3233INSERT INTO join_tbl SELECT * FROM async_p1 t1, async_pt t2 WHERE t1.a = t2.a AND t1.b = t2.b AND t1.b % 100 = 0; 3234 3235SELECT * FROM join_tbl ORDER BY a1; 3236DELETE FROM join_tbl; 3237 3238RESET enable_hashjoin; 3239 3240-- Test interaction of async execution with plan-time partition pruning 3241EXPLAIN (VERBOSE, COSTS OFF) 3242SELECT * FROM async_pt WHERE a < 3000; 3243 3244EXPLAIN (VERBOSE, COSTS OFF) 3245SELECT * FROM async_pt WHERE a < 2000; 3246 3247-- Test interaction of async execution with run-time partition pruning 3248SET plan_cache_mode TO force_generic_plan; 3249 3250PREPARE async_pt_query (int, int) AS 3251 INSERT INTO result_tbl SELECT * FROM async_pt WHERE a < $1 AND b === $2; 3252 3253EXPLAIN (VERBOSE, COSTS OFF) 3254EXECUTE async_pt_query (3000, 505); 3255EXECUTE async_pt_query (3000, 505); 3256 3257SELECT * FROM result_tbl ORDER BY a; 3258DELETE FROM result_tbl; 3259 3260EXPLAIN (VERBOSE, COSTS OFF) 3261EXECUTE async_pt_query (2000, 505); 3262EXECUTE async_pt_query (2000, 505); 3263 3264SELECT * FROM result_tbl ORDER BY a; 3265DELETE FROM result_tbl; 3266 3267RESET plan_cache_mode; 3268 3269CREATE TABLE local_tbl(a int, b int, c text); 3270INSERT INTO local_tbl VALUES (1505, 505, 'foo'), (2505, 505, 'bar'); 3271ANALYZE local_tbl; 3272 3273CREATE INDEX base_tbl1_idx ON base_tbl1 (a); 3274CREATE INDEX base_tbl2_idx ON base_tbl2 (a); 3275CREATE INDEX async_p3_idx ON async_p3 (a); 3276ANALYZE base_tbl1; 3277ANALYZE base_tbl2; 3278ANALYZE async_p3; 3279 3280ALTER FOREIGN TABLE async_p1 OPTIONS (use_remote_estimate 'true'); 3281ALTER FOREIGN TABLE async_p2 OPTIONS (use_remote_estimate 'true'); 3282 3283EXPLAIN (VERBOSE, COSTS OFF) 3284SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; 3285EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) 3286SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; 3287SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; 3288 3289ALTER FOREIGN TABLE async_p1 OPTIONS (DROP use_remote_estimate); 3290ALTER FOREIGN TABLE async_p2 OPTIONS (DROP use_remote_estimate); 3291 3292DROP TABLE local_tbl; 3293DROP INDEX base_tbl1_idx; 3294DROP INDEX base_tbl2_idx; 3295DROP INDEX async_p3_idx; 3296 3297-- Test that pending requests are processed properly 3298SET enable_mergejoin TO false; 3299SET enable_hashjoin TO false; 3300 3301EXPLAIN (VERBOSE, COSTS OFF) 3302SELECT * FROM async_pt t1, async_p2 t2 WHERE t1.a = t2.a AND t1.b === 505; 3303SELECT * FROM async_pt t1, async_p2 t2 WHERE t1.a = t2.a AND t1.b === 505; 3304 3305CREATE TABLE local_tbl (a int, b int, c text); 3306INSERT INTO local_tbl VALUES (1505, 505, 'foo'); 3307ANALYZE local_tbl; 3308 3309EXPLAIN (VERBOSE, COSTS OFF) 3310SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a; 3311EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) 3312SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a; 3313SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a; 3314 3315EXPLAIN (VERBOSE, COSTS OFF) 3316SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; 3317EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) 3318SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; 3319SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; 3320 3321-- Check with foreign modify 3322CREATE TABLE base_tbl3 (a int, b int, c text); 3323CREATE FOREIGN TABLE remote_tbl (a int, b int, c text) 3324 SERVER loopback OPTIONS (table_name 'base_tbl3'); 3325INSERT INTO remote_tbl VALUES (2505, 505, 'bar'); 3326 3327CREATE TABLE base_tbl4 (a int, b int, c text); 3328CREATE FOREIGN TABLE insert_tbl (a int, b int, c text) 3329 SERVER loopback OPTIONS (table_name 'base_tbl4'); 3330 3331EXPLAIN (VERBOSE, COSTS OFF) 3332INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_tbl); 3333INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_tbl); 3334 3335SELECT * FROM insert_tbl ORDER BY a; 3336 3337-- Check with direct modify 3338EXPLAIN (VERBOSE, COSTS OFF) 3339WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *) 3340INSERT INTO join_tbl SELECT * FROM async_pt LEFT JOIN t ON (async_pt.a = t.a AND async_pt.b = t.b) WHERE async_pt.b === 505; 3341WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *) 3342INSERT INTO join_tbl SELECT * FROM async_pt LEFT JOIN t ON (async_pt.a = t.a AND async_pt.b = t.b) WHERE async_pt.b === 505; 3343 3344SELECT * FROM join_tbl ORDER BY a1; 3345DELETE FROM join_tbl; 3346 3347DROP TABLE local_tbl; 3348DROP FOREIGN TABLE remote_tbl; 3349DROP FOREIGN TABLE insert_tbl; 3350DROP TABLE base_tbl3; 3351DROP TABLE base_tbl4; 3352 3353RESET enable_mergejoin; 3354RESET enable_hashjoin; 3355 3356-- Test that UPDATE/DELETE with inherited target works with async_capable enabled 3357EXPLAIN (VERBOSE, COSTS OFF) 3358UPDATE async_pt SET c = c || c WHERE b = 0 RETURNING *; 3359UPDATE async_pt SET c = c || c WHERE b = 0 RETURNING *; 3360EXPLAIN (VERBOSE, COSTS OFF) 3361DELETE FROM async_pt WHERE b = 0 RETURNING *; 3362DELETE FROM async_pt WHERE b = 0 RETURNING *; 3363 3364-- Check EXPLAIN ANALYZE for a query that scans empty partitions asynchronously 3365DELETE FROM async_p1; 3366DELETE FROM async_p2; 3367DELETE FROM async_p3; 3368 3369EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) 3370SELECT * FROM async_pt; 3371 3372-- Clean up 3373DROP TABLE async_pt; 3374DROP TABLE base_tbl1; 3375DROP TABLE base_tbl2; 3376DROP TABLE result_tbl; 3377DROP TABLE join_tbl; 3378 3379ALTER SERVER loopback OPTIONS (DROP async_capable); 3380ALTER SERVER loopback2 OPTIONS (DROP async_capable); 3381 3382-- =================================================================== 3383-- test invalid server and foreign table options 3384-- =================================================================== 3385-- Invalid fdw_startup_cost option 3386CREATE SERVER inv_scst FOREIGN DATA WRAPPER postgres_fdw 3387 OPTIONS(fdw_startup_cost '100$%$#$#'); 3388-- Invalid fdw_tuple_cost option 3389CREATE SERVER inv_scst FOREIGN DATA WRAPPER postgres_fdw 3390 OPTIONS(fdw_tuple_cost '100$%$#$#'); 3391-- Invalid fetch_size option 3392CREATE FOREIGN TABLE inv_fsz (c1 int ) 3393 SERVER loopback OPTIONS (fetch_size '100$%$#$#'); 3394-- Invalid batch_size option 3395CREATE FOREIGN TABLE inv_bsz (c1 int ) 3396 SERVER loopback OPTIONS (batch_size '100$%$#$#'); 3397