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 END; 19$d$; 20 21CREATE USER MAPPING FOR public SERVER testserver1 22 OPTIONS (user 'value', password 'value'); 23CREATE USER MAPPING FOR CURRENT_USER SERVER loopback; 24CREATE USER MAPPING FOR CURRENT_USER SERVER loopback2; 25 26-- =================================================================== 27-- create objects used through FDW loopback server 28-- =================================================================== 29CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz'); 30CREATE SCHEMA "S 1"; 31CREATE TABLE "S 1"."T 1" ( 32 "C 1" int NOT NULL, 33 c2 int NOT NULL, 34 c3 text, 35 c4 timestamptz, 36 c5 timestamp, 37 c6 varchar(10), 38 c7 char(10), 39 c8 user_enum, 40 CONSTRAINT t1_pkey PRIMARY KEY ("C 1") 41); 42CREATE TABLE "S 1"."T 2" ( 43 c1 int NOT NULL, 44 c2 text, 45 CONSTRAINT t2_pkey PRIMARY KEY (c1) 46); 47CREATE TABLE "S 1"."T 3" ( 48 c1 int NOT NULL, 49 c2 int NOT NULL, 50 c3 text, 51 CONSTRAINT t3_pkey PRIMARY KEY (c1) 52); 53CREATE TABLE "S 1"."T 4" ( 54 c1 int NOT NULL, 55 c2 int NOT NULL, 56 c3 text, 57 CONSTRAINT t4_pkey PRIMARY KEY (c1) 58); 59 60-- Disable autovacuum for these tables to avoid unexpected effects of that 61ALTER TABLE "S 1"."T 1" SET (autovacuum_enabled = 'false'); 62ALTER TABLE "S 1"."T 2" SET (autovacuum_enabled = 'false'); 63ALTER TABLE "S 1"."T 3" SET (autovacuum_enabled = 'false'); 64ALTER TABLE "S 1"."T 4" SET (autovacuum_enabled = 'false'); 65 66INSERT INTO "S 1"."T 1" 67 SELECT id, 68 id % 10, 69 to_char(id, 'FM00000'), 70 '1970-01-01'::timestamptz + ((id % 100) || ' days')::interval, 71 '1970-01-01'::timestamp + ((id % 100) || ' days')::interval, 72 id % 10, 73 id % 10, 74 'foo'::user_enum 75 FROM generate_series(1, 1000) id; 76INSERT INTO "S 1"."T 2" 77 SELECT id, 78 'AAA' || to_char(id, 'FM000') 79 FROM generate_series(1, 100) id; 80INSERT INTO "S 1"."T 3" 81 SELECT id, 82 id + 1, 83 'AAA' || to_char(id, 'FM000') 84 FROM generate_series(1, 100) id; 85DELETE FROM "S 1"."T 3" WHERE c1 % 2 != 0; -- delete for outer join tests 86INSERT INTO "S 1"."T 4" 87 SELECT id, 88 id + 1, 89 'AAA' || to_char(id, 'FM000') 90 FROM generate_series(1, 100) id; 91DELETE FROM "S 1"."T 4" WHERE c1 % 3 != 0; -- delete for outer join tests 92 93ANALYZE "S 1"."T 1"; 94ANALYZE "S 1"."T 2"; 95ANALYZE "S 1"."T 3"; 96ANALYZE "S 1"."T 4"; 97 98-- =================================================================== 99-- create foreign tables 100-- =================================================================== 101CREATE FOREIGN TABLE ft1 ( 102 c0 int, 103 c1 int NOT NULL, 104 c2 int NOT NULL, 105 c3 text, 106 c4 timestamptz, 107 c5 timestamp, 108 c6 varchar(10), 109 c7 char(10) default 'ft1', 110 c8 user_enum 111) SERVER loopback; 112ALTER FOREIGN TABLE ft1 DROP COLUMN c0; 113 114CREATE FOREIGN TABLE ft2 ( 115 c1 int NOT NULL, 116 c2 int NOT NULL, 117 cx int, 118 c3 text, 119 c4 timestamptz, 120 c5 timestamp, 121 c6 varchar(10), 122 c7 char(10) default 'ft2', 123 c8 user_enum 124) SERVER loopback; 125ALTER FOREIGN TABLE ft2 DROP COLUMN cx; 126 127CREATE FOREIGN TABLE ft4 ( 128 c1 int NOT NULL, 129 c2 int NOT NULL, 130 c3 text 131) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 3'); 132 133CREATE FOREIGN TABLE ft5 ( 134 c1 int NOT NULL, 135 c2 int NOT NULL, 136 c3 text 137) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 4'); 138 139CREATE FOREIGN TABLE ft6 ( 140 c1 int NOT NULL, 141 c2 int NOT NULL, 142 c3 text 143) SERVER loopback2 OPTIONS (schema_name 'S 1', table_name 'T 4'); 144 145-- =================================================================== 146-- tests for validator 147-- =================================================================== 148-- requiressl and some other parameters are omitted because 149-- valid values for them depend on configure options 150ALTER SERVER testserver1 OPTIONS ( 151 use_remote_estimate 'false', 152 updatable 'true', 153 fdw_startup_cost '123.456', 154 fdw_tuple_cost '0.123', 155 service 'value', 156 connect_timeout 'value', 157 dbname 'value', 158 host 'value', 159 hostaddr 'value', 160 port 'value', 161 --client_encoding 'value', 162 application_name 'value', 163 --fallback_application_name 'value', 164 keepalives 'value', 165 keepalives_idle 'value', 166 keepalives_interval 'value', 167 tcp_user_timeout 'value', 168 -- requiressl 'value', 169 sslcompression 'value', 170 sslmode 'value', 171 sslcert 'value', 172 sslkey 'value', 173 sslrootcert 'value', 174 sslcrl 'value', 175 --requirepeer 'value', 176 krbsrvname 'value', 177 gsslib 'value' 178 --replication 'value' 179); 180 181-- Error, invalid list syntax 182ALTER SERVER testserver1 OPTIONS (ADD extensions 'foo; bar'); 183 184-- OK but gets a warning 185ALTER SERVER testserver1 OPTIONS (ADD extensions 'foo, bar'); 186ALTER SERVER testserver1 OPTIONS (DROP extensions); 187 188ALTER USER MAPPING FOR public SERVER testserver1 189 OPTIONS (DROP user, DROP password); 190 191-- Attempt to add a valid option that's not allowed in a user mapping 192ALTER USER MAPPING FOR public SERVER testserver1 193 OPTIONS (ADD sslmode 'require'); 194 195-- But we can add valid ones fine 196ALTER USER MAPPING FOR public SERVER testserver1 197 OPTIONS (ADD sslpassword 'dummy'); 198 199-- Ensure valid options we haven't used in a user mapping yet are 200-- permitted to check validation. 201ALTER USER MAPPING FOR public SERVER testserver1 202 OPTIONS (ADD sslkey 'value', ADD sslcert 'value'); 203 204ALTER FOREIGN TABLE ft1 OPTIONS (schema_name 'S 1', table_name 'T 1'); 205ALTER FOREIGN TABLE ft2 OPTIONS (schema_name 'S 1', table_name 'T 1'); 206ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (column_name 'C 1'); 207ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1'); 208\det+ 209 210-- Test that alteration of server options causes reconnection 211-- Remote's errors might be non-English, so hide them to ensure stable results 212\set VERBOSITY terse 213SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work 214ALTER SERVER loopback OPTIONS (SET dbname 'no such database'); 215SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should fail 216DO $d$ 217 BEGIN 218 EXECUTE $$ALTER SERVER loopback 219 OPTIONS (SET dbname '$$||current_database()||$$')$$; 220 END; 221$d$; 222SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work again 223 224-- Test that alteration of user mapping options causes reconnection 225ALTER USER MAPPING FOR CURRENT_USER SERVER loopback 226 OPTIONS (ADD user 'no such user'); 227SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should fail 228ALTER USER MAPPING FOR CURRENT_USER SERVER loopback 229 OPTIONS (DROP user); 230SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work again 231\set VERBOSITY default 232 233-- Now we should be able to run ANALYZE. 234-- To exercise multiple code paths, we use local stats on ft1 235-- and remote-estimate mode on ft2. 236ANALYZE ft1; 237ALTER FOREIGN TABLE ft2 OPTIONS (use_remote_estimate 'true'); 238 239-- =================================================================== 240-- simple queries 241-- =================================================================== 242-- single table without alias 243EXPLAIN (COSTS OFF) SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10; 244SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10; 245-- single table with alias - also test that tableoid sort is not pushed to remote side 246EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tableoid OFFSET 100 LIMIT 10; 247SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tableoid OFFSET 100 LIMIT 10; 248-- whole-row reference 249EXPLAIN (VERBOSE, COSTS OFF) SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; 250SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; 251-- empty result 252SELECT * FROM ft1 WHERE false; 253-- with WHERE clause 254EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1'; 255SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1'; 256-- with FOR UPDATE/SHARE 257EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = 101 FOR UPDATE; 258SELECT * FROM ft1 t1 WHERE c1 = 101 FOR UPDATE; 259EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = 102 FOR SHARE; 260SELECT * FROM ft1 t1 WHERE c1 = 102 FOR SHARE; 261-- aggregate 262SELECT COUNT(*) FROM ft1 t1; 263-- subquery 264SELECT * FROM ft1 t1 WHERE t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 <= 10) ORDER BY c1; 265-- subquery+MAX 266SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1; 267-- used in CTE 268WITH 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; 269-- fixed values 270SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1; 271-- Test forcing the remote server to produce sorted data for a merge join. 272SET enable_hashjoin TO false; 273SET enable_nestloop TO false; 274-- inner join; expressions in the clauses appear in the equivalence class list 275EXPLAIN (VERBOSE, COSTS OFF) 276 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; 277SELECT t1.c1, t2."C 1" FROM ft2 t1 JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") OFFSET 100 LIMIT 10; 278-- outer join; expressions in the clauses do not appear in equivalence class 279-- list but no output change as compared to the previous query 280EXPLAIN (VERBOSE, COSTS OFF) 281 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; 282SELECT 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; 283-- A join between local table and foreign join. ORDER BY clause is added to the 284-- foreign join so that the local table can be joined using merge join strategy. 285EXPLAIN (VERBOSE, COSTS OFF) 286 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; 287SELECT 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; 288-- Test similar to above, except that the full join prevents any equivalence 289-- classes from being merged. This produces single relation equivalence classes 290-- included in join restrictions. 291EXPLAIN (VERBOSE, COSTS OFF) 292 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; 293SELECT 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; 294-- Test similar to above with all full outer joins 295EXPLAIN (VERBOSE, COSTS OFF) 296 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; 297SELECT 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; 298RESET enable_hashjoin; 299RESET enable_nestloop; 300 301-- Test executing assertion in estimate_path_cost_size() that makes sure that 302-- retrieved_rows for foreign rel re-used to cost pre-sorted foreign paths is 303-- a sensible value even when the rel has tuples=0 304CREATE TABLE loct_empty (c1 int NOT NULL, c2 text); 305CREATE FOREIGN TABLE ft_empty (c1 int NOT NULL, c2 text) 306 SERVER loopback OPTIONS (table_name 'loct_empty'); 307INSERT INTO loct_empty 308 SELECT id, 'AAA' || to_char(id, 'FM000') FROM generate_series(1, 100) id; 309DELETE FROM loct_empty; 310ANALYZE ft_empty; 311EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft_empty ORDER BY c1; 312 313-- =================================================================== 314-- WHERE with remotely-executable conditions 315-- =================================================================== 316EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 1; -- Var, OpExpr(b), Const 317EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2 = 0; -- BoolExpr 318EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NULL; -- NullTest 319EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL; -- NullTest 320EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr 321EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = -c1; -- OpExpr(l) 322EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE 1 = c1!; -- OpExpr(r) 323EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE (c1 IS NOT NULL) IS DISTINCT FROM (c1 IS NOT NULL); -- DistinctExpr 324EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1 + 0]); -- ScalarArrayOpExpr 325EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = (ARRAY[c1,c2,3])[1]; -- SubscriptingRef 326EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c6 = E'foo''s\\bar'; -- check special chars 327EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c8 = 'foo'; -- can't be sent to remote 328-- parameterized remote path for foreign table 329EXPLAIN (VERBOSE, COSTS OFF) 330 SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2; 331SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2; 332 333-- check both safe and unsafe join conditions 334EXPLAIN (VERBOSE, COSTS OFF) 335 SELECT * FROM ft2 a, ft2 b 336 WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7); 337SELECT * FROM ft2 a, ft2 b 338WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7); 339-- bug before 9.3.5 due to sloppy handling of remote-estimate parameters 340SELECT * FROM ft1 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft2 WHERE c1 < 5)); 341SELECT * FROM ft2 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft1 WHERE c1 < 5)); 342-- we should not push order by clause with volatile expressions or unsafe 343-- collations 344EXPLAIN (VERBOSE, COSTS OFF) 345 SELECT * FROM ft2 ORDER BY ft2.c1, random(); 346EXPLAIN (VERBOSE, COSTS OFF) 347 SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C"; 348 349-- user-defined operator/function 350CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$ 351BEGIN 352RETURN abs($1); 353END 354$$ LANGUAGE plpgsql IMMUTABLE; 355CREATE OPERATOR === ( 356 LEFTARG = int, 357 RIGHTARG = int, 358 PROCEDURE = int4eq, 359 COMMUTATOR = === 360); 361 362-- built-in operators and functions can be shipped for remote execution 363EXPLAIN (VERBOSE, COSTS OFF) 364 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = abs(t1.c2); 365SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = abs(t1.c2); 366EXPLAIN (VERBOSE, COSTS OFF) 367 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = t1.c2; 368SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = t1.c2; 369 370-- by default, user-defined ones cannot 371EXPLAIN (VERBOSE, COSTS OFF) 372 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2); 373SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2); 374EXPLAIN (VERBOSE, COSTS OFF) 375 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 376SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 377 378-- ORDER BY can be shipped, though 379EXPLAIN (VERBOSE, COSTS OFF) 380 SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1; 381SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1; 382 383-- but let's put them in an extension ... 384ALTER EXTENSION postgres_fdw ADD FUNCTION postgres_fdw_abs(int); 385ALTER EXTENSION postgres_fdw ADD OPERATOR === (int, int); 386ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw'); 387 388-- ... now they can be shipped 389EXPLAIN (VERBOSE, COSTS OFF) 390 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2); 391SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2); 392EXPLAIN (VERBOSE, COSTS OFF) 393 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 394SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 395 396-- and both ORDER BY and LIMIT can be shipped 397EXPLAIN (VERBOSE, COSTS OFF) 398 SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1; 399SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1; 400 401-- =================================================================== 402-- JOIN queries 403-- =================================================================== 404-- Analyze ft4 and ft5 so that we have better statistics. These tables do not 405-- have use_remote_estimate set. 406ANALYZE ft4; 407ANALYZE ft5; 408 409-- join two tables 410EXPLAIN (VERBOSE, COSTS OFF) 411SELECT 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; 412SELECT 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; 413-- join three tables 414EXPLAIN (VERBOSE, COSTS OFF) 415SELECT 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; 416SELECT 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; 417-- left outer join 418EXPLAIN (VERBOSE, COSTS OFF) 419SELECT 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; 420SELECT 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; 421-- left outer join three tables 422EXPLAIN (VERBOSE, COSTS OFF) 423SELECT 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; 424SELECT 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; 425-- left outer join + placement of clauses. 426-- clauses within the nullable side are not pulled up, but top level clause on 427-- non-nullable side is pushed into non-nullable side 428EXPLAIN (VERBOSE, COSTS OFF) 429SELECT 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; 430SELECT 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; 431-- clauses within the nullable side are not pulled up, but the top level clause 432-- on nullable side is not pushed down into nullable side 433EXPLAIN (VERBOSE, COSTS OFF) 434SELECT 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) 435 WHERE (t2.c1 < 10 OR t2.c1 IS NULL) AND t1.c1 < 10; 436SELECT 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) 437 WHERE (t2.c1 < 10 OR t2.c1 IS NULL) AND t1.c1 < 10; 438-- right outer join 439EXPLAIN (VERBOSE, COSTS OFF) 440SELECT 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; 441SELECT 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; 442-- right outer join three tables 443EXPLAIN (VERBOSE, COSTS OFF) 444SELECT 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; 445SELECT 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; 446-- full outer join 447EXPLAIN (VERBOSE, COSTS OFF) 448SELECT 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; 449SELECT 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; 450-- full outer join with restrictions on the joining relations 451-- a. the joining relations are both base relations 452EXPLAIN (VERBOSE, COSTS OFF) 453SELECT 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; 454SELECT 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; 455EXPLAIN (VERBOSE, COSTS OFF) 456SELECT 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; 457SELECT 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; 458-- b. one of the joining relations is a base relation and the other is a join 459-- relation 460EXPLAIN (VERBOSE, COSTS OFF) 461SELECT 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; 462SELECT 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; 463-- c. test deparsing the remote query as nested subqueries 464EXPLAIN (VERBOSE, COSTS OFF) 465SELECT 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; 466SELECT 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; 467-- d. test deparsing rowmarked relations as subqueries 468EXPLAIN (VERBOSE, COSTS OFF) 469SELECT 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; 470SELECT 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; 471-- full outer join + inner join 472EXPLAIN (VERBOSE, COSTS OFF) 473SELECT 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; 474SELECT 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; 475-- full outer join three tables 476EXPLAIN (VERBOSE, COSTS OFF) 477SELECT 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; 478SELECT 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; 479-- full outer join + right outer join 480EXPLAIN (VERBOSE, COSTS OFF) 481SELECT 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; 482SELECT 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; 483-- right outer join + full outer join 484EXPLAIN (VERBOSE, COSTS OFF) 485SELECT 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; 486SELECT 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; 487-- full outer join + left outer join 488EXPLAIN (VERBOSE, COSTS OFF) 489SELECT 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; 490SELECT 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; 491-- left outer join + full outer join 492EXPLAIN (VERBOSE, COSTS OFF) 493SELECT 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; 494SELECT 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; 495-- right outer join + left outer join 496EXPLAIN (VERBOSE, COSTS OFF) 497SELECT 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; 498SELECT 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; 499-- left outer join + right outer join 500EXPLAIN (VERBOSE, COSTS OFF) 501SELECT 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; 502SELECT 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; 503-- full outer join + WHERE clause, only matched rows 504EXPLAIN (VERBOSE, COSTS OFF) 505SELECT 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; 506SELECT 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; 507-- full outer join + WHERE clause with shippable extensions set 508EXPLAIN (VERBOSE, COSTS OFF) 509SELECT 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; 510ALTER SERVER loopback OPTIONS (DROP extensions); 511-- full outer join + WHERE clause with shippable extensions not set 512EXPLAIN (VERBOSE, COSTS OFF) 513SELECT 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; 514ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw'); 515-- join two tables with FOR UPDATE clause 516-- tests whole-row reference for row marks 517EXPLAIN (VERBOSE, COSTS OFF) 518SELECT 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; 519SELECT 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; 520EXPLAIN (VERBOSE, COSTS OFF) 521SELECT 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; 522SELECT 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; 523-- join two tables with FOR SHARE clause 524EXPLAIN (VERBOSE, COSTS OFF) 525SELECT 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; 526SELECT 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; 527EXPLAIN (VERBOSE, COSTS OFF) 528SELECT 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; 529SELECT 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; 530-- join in CTE 531EXPLAIN (VERBOSE, COSTS OFF) 532WITH 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; 533WITH 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; 534-- ctid with whole-row reference 535EXPLAIN (VERBOSE, COSTS OFF) 536SELECT 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; 537-- SEMI JOIN, not pushed down 538EXPLAIN (VERBOSE, COSTS OFF) 539SELECT 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; 540SELECT 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; 541-- ANTI JOIN, not pushed down 542EXPLAIN (VERBOSE, COSTS OFF) 543SELECT 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; 544SELECT 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; 545-- CROSS JOIN can be pushed down 546EXPLAIN (VERBOSE, COSTS OFF) 547SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10; 548SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10; 549-- different server, not pushed down. No result expected. 550EXPLAIN (VERBOSE, COSTS OFF) 551SELECT 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; 552SELECT 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; 553-- unsafe join conditions (c8 has a UDT), not pushed down. Practically a CROSS 554-- JOIN since c8 in both tables has same value. 555EXPLAIN (VERBOSE, COSTS OFF) 556SELECT 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; 557SELECT 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; 558-- unsafe conditions on one side (c8 has a UDT), not pushed down. 559EXPLAIN (VERBOSE, COSTS OFF) 560SELECT 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; 561SELECT 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; 562-- join where unsafe to pushdown condition in WHERE clause has a column not 563-- in the SELECT clause. In this test unsafe clause needs to have column 564-- references from both joining sides so that the clause is not pushed down 565-- into one of the joining sides. 566EXPLAIN (VERBOSE, COSTS OFF) 567SELECT 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; 568SELECT 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; 569-- Aggregate after UNION, for testing setrefs 570EXPLAIN (VERBOSE, COSTS OFF) 571SELECT 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; 572SELECT 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; 573-- join with lateral reference 574EXPLAIN (VERBOSE, COSTS OFF) 575SELECT 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; 576SELECT 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; 577 578-- non-Var items in targetlist of the nullable rel of a join preventing 579-- push-down in some cases 580-- unable to push {ft1, ft2} 581EXPLAIN (VERBOSE, COSTS OFF) 582SELECT 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; 583SELECT 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; 584 585-- ok to push {ft1, ft2} but not {ft1, ft2, ft4} 586EXPLAIN (VERBOSE, COSTS OFF) 587SELECT 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; 588SELECT 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; 589 590-- join with nullable side with some columns with null values 591UPDATE ft5 SET c3 = null where c1 % 9 = 0; 592EXPLAIN (VERBOSE, COSTS OFF) 593SELECT 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; 594SELECT 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; 595 596-- multi-way join involving multiple merge joins 597-- (this case used to have EPQ-related planning problems) 598CREATE TABLE local_tbl (c1 int NOT NULL, c2 int NOT NULL, c3 text, CONSTRAINT local_tbl_pkey PRIMARY KEY (c1)); 599INSERT INTO local_tbl SELECT id, id % 10, to_char(id, 'FM0000') FROM generate_series(1, 1000) id; 600ANALYZE local_tbl; 601SET enable_nestloop TO false; 602SET enable_hashjoin TO false; 603EXPLAIN (VERBOSE, COSTS OFF) 604SELECT * FROM ft1, ft2, ft4, ft5, local_tbl WHERE ft1.c1 = ft2.c1 AND ft1.c2 = ft4.c1 605 AND ft1.c2 = ft5.c1 AND ft1.c2 = local_tbl.c1 AND ft1.c1 < 100 AND ft2.c1 < 100 FOR UPDATE; 606SELECT * FROM ft1, ft2, ft4, ft5, local_tbl WHERE ft1.c1 = ft2.c1 AND ft1.c2 = ft4.c1 607 AND ft1.c2 = ft5.c1 AND ft1.c2 = local_tbl.c1 AND ft1.c1 < 100 AND ft2.c1 < 100 FOR UPDATE; 608RESET enable_nestloop; 609RESET enable_hashjoin; 610DROP TABLE local_tbl; 611 612-- check join pushdown in situations where multiple userids are involved 613CREATE ROLE regress_view_owner SUPERUSER; 614CREATE USER MAPPING FOR regress_view_owner SERVER loopback; 615GRANT SELECT ON ft4 TO regress_view_owner; 616GRANT SELECT ON ft5 TO regress_view_owner; 617 618CREATE VIEW v4 AS SELECT * FROM ft4; 619CREATE VIEW v5 AS SELECT * FROM ft5; 620ALTER VIEW v5 OWNER TO regress_view_owner; 621EXPLAIN (VERBOSE, COSTS OFF) 622SELECT 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 623SELECT 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; 624ALTER VIEW v4 OWNER TO regress_view_owner; 625EXPLAIN (VERBOSE, COSTS OFF) 626SELECT 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 627SELECT 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; 628 629EXPLAIN (VERBOSE, COSTS OFF) 630SELECT 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 631SELECT 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; 632ALTER VIEW v4 OWNER TO CURRENT_USER; 633EXPLAIN (VERBOSE, COSTS OFF) 634SELECT 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 635SELECT 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; 636ALTER VIEW v4 OWNER TO regress_view_owner; 637 638-- cleanup 639DROP OWNED BY regress_view_owner; 640DROP ROLE regress_view_owner; 641 642 643-- =================================================================== 644-- Aggregate and grouping queries 645-- =================================================================== 646 647-- Simple aggregates 648explain (verbose, costs off) 649select 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; 650select 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; 651 652explain (verbose, costs off) 653select 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; 654select 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; 655 656-- Aggregate is not pushed down as aggregation contains random() 657explain (verbose, costs off) 658select sum(c1 * (random() <= 1)::int) as sum, avg(c1) from ft1; 659 660-- Aggregate over join query 661explain (verbose, costs off) 662select count(*), sum(t1.c1), avg(t2.c1) from ft1 t1 inner join ft1 t2 on (t1.c2 = t2.c2) where t1.c2 = 6; 663select count(*), sum(t1.c1), avg(t2.c1) from ft1 t1 inner join ft1 t2 on (t1.c2 = t2.c2) where t1.c2 = 6; 664 665-- Not pushed down due to local conditions present in underneath input rel 666explain (verbose, costs off) 667select 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; 668 669-- GROUP BY clause having expressions 670explain (verbose, costs off) 671select c2/2, sum(c2) * (c2/2) from ft1 group by c2/2 order by c2/2; 672select c2/2, sum(c2) * (c2/2) from ft1 group by c2/2 order by c2/2; 673 674-- Aggregates in subquery are pushed down. 675explain (verbose, costs off) 676select 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; 677select 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; 678 679-- Aggregate is still pushed down by taking unshippable expression out 680explain (verbose, costs off) 681select c2 * (random() <= 1)::int as sum1, sum(c1) * c2 as sum2 from ft1 group by c2 order by 1, 2; 682select c2 * (random() <= 1)::int as sum1, sum(c1) * c2 as sum2 from ft1 group by c2 order by 1, 2; 683 684-- Aggregate with unshippable GROUP BY clause are not pushed 685explain (verbose, costs off) 686select c2 * (random() <= 1)::int as c2 from ft2 group by c2 * (random() <= 1)::int order by 1; 687 688-- GROUP BY clause in various forms, cardinal, alias and constant expression 689explain (verbose, costs off) 690select count(c2) w, c2 x, 5 y, 7.0 z from ft1 group by 2, y, 9.0::int order by 2; 691select count(c2) w, c2 x, 5 y, 7.0 z from ft1 group by 2, y, 9.0::int order by 2; 692 693-- GROUP BY clause referring to same column multiple times 694-- Also, ORDER BY contains an aggregate function 695explain (verbose, costs off) 696select c2, c2 from ft1 where c2 > 6 group by 1, 2 order by sum(c1); 697select c2, c2 from ft1 where c2 > 6 group by 1, 2 order by sum(c1); 698 699-- Testing HAVING clause shippability 700explain (verbose, costs off) 701select c2, sum(c1) from ft2 group by c2 having avg(c1) < 500 and sum(c1) < 49800 order by c2; 702select c2, sum(c1) from ft2 group by c2 having avg(c1) < 500 and sum(c1) < 49800 order by c2; 703 704-- Unshippable HAVING clause will be evaluated locally, and other qual in HAVING clause is pushed down 705explain (verbose, costs off) 706select 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; 707select 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; 708 709-- Aggregate in HAVING clause is not pushable, and thus aggregation is not pushed down 710explain (verbose, costs off) 711select sum(c1) from ft1 group by c2 having avg(c1 * (random() <= 1)::int) > 100 order by 1; 712 713-- Remote aggregate in combination with a local Param (for the output 714-- of an initplan) can be trouble, per bug #15781 715explain (verbose, costs off) 716select exists(select 1 from pg_enum), sum(c1) from ft1; 717select exists(select 1 from pg_enum), sum(c1) from ft1; 718 719explain (verbose, costs off) 720select exists(select 1 from pg_enum), sum(c1) from ft1 group by 1; 721select exists(select 1 from pg_enum), sum(c1) from ft1 group by 1; 722 723 724-- Testing ORDER BY, DISTINCT, FILTER, Ordered-sets and VARIADIC within aggregates 725 726-- ORDER BY within aggregate, same column used to order 727explain (verbose, costs off) 728select array_agg(c1 order by c1) from ft1 where c1 < 100 group by c2 order by 1; 729select array_agg(c1 order by c1) from ft1 where c1 < 100 group by c2 order by 1; 730 731-- ORDER BY within aggregate, different column used to order also using DESC 732explain (verbose, costs off) 733select array_agg(c5 order by c1 desc) from ft2 where c2 = 6 and c1 < 50; 734select array_agg(c5 order by c1 desc) from ft2 where c2 = 6 and c1 < 50; 735 736-- DISTINCT within aggregate 737explain (verbose, costs off) 738select 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; 739select 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; 740 741-- DISTINCT combined with ORDER BY within aggregate 742explain (verbose, costs off) 743select 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; 744select 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; 745 746explain (verbose, costs off) 747select 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; 748select 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; 749 750-- FILTER within aggregate 751explain (verbose, costs off) 752select sum(c1) filter (where c1 < 100 and c2 > 5) from ft1 group by c2 order by 1 nulls last; 753select sum(c1) filter (where c1 < 100 and c2 > 5) from ft1 group by c2 order by 1 nulls last; 754 755-- DISTINCT, ORDER BY and FILTER within aggregate 756explain (verbose, costs off) 757select 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; 758select 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; 759 760-- Outer query is aggregation query 761explain (verbose, costs off) 762select 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; 763select 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; 764-- Inner query is aggregation query 765explain (verbose, costs off) 766select 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; 767select 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; 768 769-- Aggregate not pushed down as FILTER condition is not pushable 770explain (verbose, costs off) 771select sum(c1) filter (where (c1 / c1) * random() <= 1) from ft1 group by c2 order by 1; 772explain (verbose, costs off) 773select sum(c2) filter (where c2 in (select c2 from ft1 where c2 < 5)) from ft1; 774 775-- Ordered-sets within aggregate 776explain (verbose, costs off) 777select 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; 778select 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; 779 780-- Using multiple arguments within aggregates 781explain (verbose, costs off) 782select c1, rank(c1, c2) within group (order by c1, c2) from ft1 group by c1, c2 having c1 = 6 order by 1; 783select c1, rank(c1, c2) within group (order by c1, c2) from ft1 group by c1, c2 having c1 = 6 order by 1; 784 785-- User defined function for user defined aggregate, VARIADIC 786create function least_accum(anyelement, variadic anyarray) 787returns anyelement language sql as 788 'select least($1, min($2[i])) from generate_subscripts($2,1) g(i)'; 789create aggregate least_agg(variadic items anyarray) ( 790 stype = anyelement, sfunc = least_accum 791); 792 793-- Disable hash aggregation for plan stability. 794set enable_hashagg to false; 795 796-- Not pushed down due to user defined aggregate 797explain (verbose, costs off) 798select c2, least_agg(c1) from ft1 group by c2 order by c2; 799 800-- Add function and aggregate into extension 801alter extension postgres_fdw add function least_accum(anyelement, variadic anyarray); 802alter extension postgres_fdw add aggregate least_agg(variadic items anyarray); 803alter server loopback options (set extensions 'postgres_fdw'); 804 805-- Now aggregate will be pushed. Aggregate will display VARIADIC argument. 806explain (verbose, costs off) 807select c2, least_agg(c1) from ft1 where c2 < 100 group by c2 order by c2; 808select c2, least_agg(c1) from ft1 where c2 < 100 group by c2 order by c2; 809 810-- Remove function and aggregate from extension 811alter extension postgres_fdw drop function least_accum(anyelement, variadic anyarray); 812alter extension postgres_fdw drop aggregate least_agg(variadic items anyarray); 813alter server loopback options (set extensions 'postgres_fdw'); 814 815-- Not pushed down as we have dropped objects from extension. 816explain (verbose, costs off) 817select c2, least_agg(c1) from ft1 group by c2 order by c2; 818 819-- Cleanup 820reset enable_hashagg; 821drop aggregate least_agg(variadic items anyarray); 822drop function least_accum(anyelement, variadic anyarray); 823 824 825-- Testing USING OPERATOR() in ORDER BY within aggregate. 826-- For this, we need user defined operators along with operator family and 827-- operator class. Create those and then add them in extension. Note that 828-- user defined objects are considered unshippable unless they are part of 829-- the extension. 830create operator public.<^ ( 831 leftarg = int4, 832 rightarg = int4, 833 procedure = int4eq 834); 835 836create operator public.=^ ( 837 leftarg = int4, 838 rightarg = int4, 839 procedure = int4lt 840); 841 842create operator public.>^ ( 843 leftarg = int4, 844 rightarg = int4, 845 procedure = int4gt 846); 847 848create operator family my_op_family using btree; 849 850create function my_op_cmp(a int, b int) returns int as 851 $$begin return btint4cmp(a, b); end $$ language plpgsql; 852 853create operator class my_op_class for type int using btree family my_op_family as 854 operator 1 public.<^, 855 operator 3 public.=^, 856 operator 5 public.>^, 857 function 1 my_op_cmp(int, int); 858 859-- This will not be pushed as user defined sort operator is not part of the 860-- extension yet. 861explain (verbose, costs off) 862select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2; 863 864-- Update local stats on ft2 865ANALYZE ft2; 866 867-- Add into extension 868alter extension postgres_fdw add operator class my_op_class using btree; 869alter extension postgres_fdw add function my_op_cmp(a int, b int); 870alter extension postgres_fdw add operator family my_op_family using btree; 871alter extension postgres_fdw add operator public.<^(int, int); 872alter extension postgres_fdw add operator public.=^(int, int); 873alter extension postgres_fdw add operator public.>^(int, int); 874alter server loopback options (set extensions 'postgres_fdw'); 875 876-- Now this will be pushed as sort operator is part of the extension. 877explain (verbose, costs off) 878select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2; 879select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2; 880 881-- Remove from extension 882alter extension postgres_fdw drop operator class my_op_class using btree; 883alter extension postgres_fdw drop function my_op_cmp(a int, b int); 884alter extension postgres_fdw drop operator family my_op_family using btree; 885alter extension postgres_fdw drop operator public.<^(int, int); 886alter extension postgres_fdw drop operator public.=^(int, int); 887alter extension postgres_fdw drop operator public.>^(int, int); 888alter server loopback options (set extensions 'postgres_fdw'); 889 890-- This will not be pushed as sort operator is now removed from the extension. 891explain (verbose, costs off) 892select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2; 893 894-- Cleanup 895drop operator class my_op_class using btree; 896drop function my_op_cmp(a int, b int); 897drop operator family my_op_family using btree; 898drop operator public.>^(int, int); 899drop operator public.=^(int, int); 900drop operator public.<^(int, int); 901 902-- Input relation to aggregate push down hook is not safe to pushdown and thus 903-- the aggregate cannot be pushed down to foreign server. 904explain (verbose, costs off) 905select count(t1.c3) from ft2 t1 left join ft2 t2 on (t1.c1 = random() * t2.c2); 906 907-- Subquery in FROM clause having aggregate 908explain (verbose, costs off) 909select 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; 910select 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; 911 912-- FULL join with IS NULL check in HAVING 913explain (verbose, costs off) 914select 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; 915select 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; 916 917-- Aggregate over FULL join needing to deparse the joining relations as 918-- subqueries. 919explain (verbose, costs off) 920select 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); 921select 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); 922 923-- ORDER BY expression is part of the target list but not pushed down to 924-- foreign server. 925explain (verbose, costs off) 926select sum(c2) * (random() <= 1)::int as sum from ft1 order by 1; 927select sum(c2) * (random() <= 1)::int as sum from ft1 order by 1; 928 929-- LATERAL join, with parameterization 930set enable_hashagg to false; 931explain (verbose, costs off) 932select 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; 933select 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; 934reset enable_hashagg; 935 936-- bug #15613: bad plan for foreign table scan with lateral reference 937EXPLAIN (VERBOSE, COSTS OFF) 938SELECT ref_0.c2, subq_1.* 939FROM 940 "S 1"."T 1" AS ref_0, 941 LATERAL ( 942 SELECT ref_0."C 1" c1, subq_0.* 943 FROM (SELECT ref_0.c2, ref_1.c3 944 FROM ft1 AS ref_1) AS subq_0 945 RIGHT JOIN ft2 AS ref_3 ON (subq_0.c3 = ref_3.c3) 946 ) AS subq_1 947WHERE ref_0."C 1" < 10 AND subq_1.c3 = '00001' 948ORDER BY ref_0."C 1"; 949 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 962-- Check with placeHolderVars 963explain (verbose, costs off) 964select 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); 965select 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); 966 967 968-- Not supported cases 969-- Grouping sets 970explain (verbose, costs off) 971select c2, sum(c1) from ft1 where c2 < 3 group by rollup(c2) order by 1 nulls last; 972select c2, sum(c1) from ft1 where c2 < 3 group by rollup(c2) order by 1 nulls last; 973explain (verbose, costs off) 974select c2, sum(c1) from ft1 where c2 < 3 group by cube(c2) order by 1 nulls last; 975select c2, sum(c1) from ft1 where c2 < 3 group by cube(c2) order by 1 nulls last; 976explain (verbose, costs off) 977select c2, c6, sum(c1) from ft1 where c2 < 3 group by grouping sets(c2, c6) order by 1 nulls last, 2 nulls last; 978select c2, c6, sum(c1) from ft1 where c2 < 3 group by grouping sets(c2, c6) order by 1 nulls last, 2 nulls last; 979explain (verbose, costs off) 980select c2, sum(c1), grouping(c2) from ft1 where c2 < 3 group by c2 order by 1 nulls last; 981select c2, sum(c1), grouping(c2) from ft1 where c2 < 3 group by c2 order by 1 nulls last; 982 983-- DISTINCT itself is not pushed down, whereas underneath aggregate is pushed 984explain (verbose, costs off) 985select distinct sum(c1)/1000 s from ft2 where c2 < 6 group by c2 order by 1; 986select distinct sum(c1)/1000 s from ft2 where c2 < 6 group by c2 order by 1; 987 988-- WindowAgg 989explain (verbose, costs off) 990select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 group by c2 order by 1; 991select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 group by c2 order by 1; 992explain (verbose, costs off) 993select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 where c2 < 10 group by c2 order by 1; 994select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 where c2 < 10 group by c2 order by 1; 995explain (verbose, costs off) 996select 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; 997select 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; 998 999 1000-- =================================================================== 1001-- parameterized queries 1002-- =================================================================== 1003-- simple join 1004PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2; 1005EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st1(1, 2); 1006EXECUTE st1(1, 1); 1007EXECUTE st1(101, 101); 1008-- subquery using stable function (can't be sent to remote) 1009PREPARE 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; 1010EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st2(10, 20); 1011EXECUTE st2(10, 20); 1012EXECUTE st2(101, 121); 1013-- subquery using immutable function (can be sent to remote) 1014PREPARE 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; 1015EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st3(10, 20); 1016EXECUTE st3(10, 20); 1017EXECUTE st3(20, 30); 1018-- custom plan should be chosen initially 1019PREPARE st4(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 = $1; 1020EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 1021EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 1022EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 1023EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 1024EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 1025-- once we try it enough times, should switch to generic plan 1026EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 1027-- value of $1 should not be sent to remote 1028PREPARE st5(user_enum,int) AS SELECT * FROM ft1 t1 WHERE c8 = $1 and c1 = $2; 1029EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 1030EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 1031EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 1032EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 1033EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 1034EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 1035EXECUTE st5('foo', 1); 1036 1037-- altering FDW options requires replanning 1038PREPARE st6 AS SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2; 1039EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6; 1040PREPARE st7 AS INSERT INTO ft1 (c1,c2,c3) VALUES (1001,101,'foo'); 1041EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7; 1042ALTER TABLE "S 1"."T 1" RENAME TO "T 0"; 1043ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 0'); 1044EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6; 1045EXECUTE st6; 1046EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7; 1047ALTER TABLE "S 1"."T 0" RENAME TO "T 1"; 1048ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 1'); 1049 1050PREPARE st8 AS SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 1051EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8; 1052ALTER SERVER loopback OPTIONS (DROP extensions); 1053EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8; 1054EXECUTE st8; 1055ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw'); 1056 1057-- cleanup 1058DEALLOCATE st1; 1059DEALLOCATE st2; 1060DEALLOCATE st3; 1061DEALLOCATE st4; 1062DEALLOCATE st5; 1063DEALLOCATE st6; 1064DEALLOCATE st7; 1065DEALLOCATE st8; 1066 1067-- System columns, except ctid and oid, should not be sent to remote 1068EXPLAIN (VERBOSE, COSTS OFF) 1069SELECT * FROM ft1 t1 WHERE t1.tableoid = 'pg_class'::regclass LIMIT 1; 1070SELECT * FROM ft1 t1 WHERE t1.tableoid = 'ft1'::regclass LIMIT 1; 1071EXPLAIN (VERBOSE, COSTS OFF) 1072SELECT tableoid::regclass, * FROM ft1 t1 LIMIT 1; 1073SELECT tableoid::regclass, * FROM ft1 t1 LIMIT 1; 1074EXPLAIN (VERBOSE, COSTS OFF) 1075SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)'; 1076SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)'; 1077EXPLAIN (VERBOSE, COSTS OFF) 1078SELECT ctid, * FROM ft1 t1 LIMIT 1; 1079SELECT ctid, * FROM ft1 t1 LIMIT 1; 1080 1081-- =================================================================== 1082-- used in PL/pgSQL function 1083-- =================================================================== 1084CREATE OR REPLACE FUNCTION f_test(p_c1 int) RETURNS int AS $$ 1085DECLARE 1086 v_c1 int; 1087BEGIN 1088 SELECT c1 INTO v_c1 FROM ft1 WHERE c1 = p_c1 LIMIT 1; 1089 PERFORM c1 FROM ft1 WHERE c1 = p_c1 AND p_c1 = v_c1 LIMIT 1; 1090 RETURN v_c1; 1091END; 1092$$ LANGUAGE plpgsql; 1093SELECT f_test(100); 1094DROP FUNCTION f_test(int); 1095 1096-- =================================================================== 1097-- conversion error 1098-- =================================================================== 1099ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE int; 1100SELECT * FROM ft1 ftx(x1,x2,x3,x4,x5,x6,x7,x8) WHERE x1 = 1; -- ERROR 1101SELECT ftx.x1, ft2.c2, ftx.x8 FROM ft1 ftx(x1,x2,x3,x4,x5,x6,x7,x8), ft2 1102 WHERE ftx.x1 = ft2.c1 AND ftx.x1 = 1; -- ERROR 1103SELECT ftx.x1, ft2.c2, ftx FROM ft1 ftx(x1,x2,x3,x4,x5,x6,x7,x8), ft2 1104 WHERE ftx.x1 = ft2.c1 AND ftx.x1 = 1; -- ERROR 1105SELECT sum(c2), array_agg(c8) FROM ft1 GROUP BY c8; -- ERROR 1106ANALYZE ft1; -- ERROR 1107ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum; 1108 1109-- =================================================================== 1110-- subtransaction 1111-- + local/remote error doesn't break cursor 1112-- =================================================================== 1113BEGIN; 1114DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1; 1115FETCH c; 1116SAVEPOINT s; 1117ERROR OUT; -- ERROR 1118ROLLBACK TO s; 1119FETCH c; 1120SAVEPOINT s; 1121SELECT * FROM ft1 WHERE 1 / (c1 - 1) > 0; -- ERROR 1122ROLLBACK TO s; 1123FETCH c; 1124SELECT * FROM ft1 ORDER BY c1 LIMIT 1; 1125COMMIT; 1126 1127-- =================================================================== 1128-- test handling of collations 1129-- =================================================================== 1130create table loct3 (f1 text collate "C" unique, f2 text, f3 varchar(10) unique); 1131create foreign table ft3 (f1 text collate "C", f2 text, f3 varchar(10)) 1132 server loopback options (table_name 'loct3', use_remote_estimate 'true'); 1133 1134-- can be sent to remote 1135explain (verbose, costs off) select * from ft3 where f1 = 'foo'; 1136explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" = 'foo'; 1137explain (verbose, costs off) select * from ft3 where f2 = 'foo'; 1138explain (verbose, costs off) select * from ft3 where f3 = 'foo'; 1139explain (verbose, costs off) select * from ft3 f, loct3 l 1140 where f.f3 = l.f3 and l.f1 = 'foo'; 1141-- can't be sent to remote 1142explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" = 'foo'; 1143explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C"; 1144explain (verbose, costs off) select * from ft3 where f2 COLLATE "C" = 'foo'; 1145explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C"; 1146explain (verbose, costs off) select * from ft3 f, loct3 l 1147 where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo'; 1148 1149-- =================================================================== 1150-- test writable foreign table stuff 1151-- =================================================================== 1152EXPLAIN (verbose, costs off) 1153INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20; 1154INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20; 1155INSERT INTO ft2 (c1,c2,c3) 1156 VALUES (1101,201,'aaa'), (1102,202,'bbb'), (1103,203,'ccc') RETURNING *; 1157INSERT INTO ft2 (c1,c2,c3) VALUES (1104,204,'ddd'), (1105,205,'eee'); 1158EXPLAIN (verbose, costs off) 1159UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3; -- can be pushed down 1160UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3; 1161EXPLAIN (verbose, costs off) 1162UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *; -- can be pushed down 1163UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *; 1164EXPLAIN (verbose, costs off) 1165UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT 1166 FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; -- can be pushed down 1167UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT 1168 FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; 1169EXPLAIN (verbose, costs off) 1170 DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4; -- can be pushed down 1171DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4; 1172EXPLAIN (verbose, costs off) 1173DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; -- can be pushed down 1174DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; 1175SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1; 1176EXPLAIN (verbose, costs off) 1177INSERT INTO ft2 (c1,c2,c3) VALUES (1200,999,'foo') RETURNING tableoid::regclass; 1178INSERT INTO ft2 (c1,c2,c3) VALUES (1200,999,'foo') RETURNING tableoid::regclass; 1179EXPLAIN (verbose, costs off) 1180UPDATE ft2 SET c3 = 'bar' WHERE c1 = 1200 RETURNING tableoid::regclass; -- can be pushed down 1181UPDATE ft2 SET c3 = 'bar' WHERE c1 = 1200 RETURNING tableoid::regclass; 1182EXPLAIN (verbose, costs off) 1183DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass; -- can be pushed down 1184DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass; 1185 1186-- Test UPDATE/DELETE with RETURNING on a three-table join 1187INSERT INTO ft2 (c1,c2,c3) 1188 SELECT id, id - 1200, to_char(id, 'FM00000') FROM generate_series(1201, 1300) id; 1189EXPLAIN (verbose, costs off) 1190UPDATE ft2 SET c3 = 'foo' 1191 FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1) 1192 WHERE ft2.c1 > 1200 AND ft2.c2 = ft4.c1 1193 RETURNING ft2, ft2.*, ft4, ft4.*; -- can be pushed down 1194UPDATE ft2 SET c3 = 'foo' 1195 FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1) 1196 WHERE ft2.c1 > 1200 AND ft2.c2 = ft4.c1 1197 RETURNING ft2, ft2.*, ft4, ft4.*; 1198EXPLAIN (verbose, costs off) 1199DELETE FROM ft2 1200 USING ft4 LEFT JOIN ft5 ON (ft4.c1 = ft5.c1) 1201 WHERE ft2.c1 > 1200 AND ft2.c1 % 10 = 0 AND ft2.c2 = ft4.c1 1202 RETURNING 100; -- can be pushed down 1203DELETE FROM ft2 1204 USING ft4 LEFT JOIN ft5 ON (ft4.c1 = ft5.c1) 1205 WHERE ft2.c1 > 1200 AND ft2.c1 % 10 = 0 AND ft2.c2 = ft4.c1 1206 RETURNING 100; 1207DELETE FROM ft2 WHERE ft2.c1 > 1200; 1208 1209-- Test UPDATE with a MULTIEXPR sub-select 1210-- (maybe someday this'll be remotely executable, but not today) 1211EXPLAIN (verbose, costs off) 1212UPDATE ft2 AS target SET (c2, c7) = ( 1213 SELECT c2 * 10, c7 1214 FROM ft2 AS src 1215 WHERE target.c1 = src.c1 1216) WHERE c1 > 1100; 1217UPDATE ft2 AS target SET (c2, c7) = ( 1218 SELECT c2 * 10, c7 1219 FROM ft2 AS src 1220 WHERE target.c1 = src.c1 1221) WHERE c1 > 1100; 1222 1223UPDATE ft2 AS target SET (c2) = ( 1224 SELECT c2 / 10 1225 FROM ft2 AS src 1226 WHERE target.c1 = src.c1 1227) WHERE c1 > 1100; 1228 1229-- Test UPDATE/DELETE with WHERE or JOIN/ON conditions containing 1230-- user-defined operators/functions 1231ALTER SERVER loopback OPTIONS (DROP extensions); 1232INSERT INTO ft2 (c1,c2,c3) 1233 SELECT id, id % 10, to_char(id, 'FM00000') FROM generate_series(2001, 2010) id; 1234EXPLAIN (verbose, costs off) 1235UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *; -- can't be pushed down 1236UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *; 1237EXPLAIN (verbose, costs off) 1238UPDATE ft2 SET c3 = 'baz' 1239 FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1) 1240 WHERE ft2.c1 > 2000 AND ft2.c2 === ft4.c1 1241 RETURNING ft2.*, ft4.*, ft5.*; -- can't be pushed down 1242UPDATE ft2 SET c3 = 'baz' 1243 FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1) 1244 WHERE ft2.c1 > 2000 AND ft2.c2 === ft4.c1 1245 RETURNING ft2.*, ft4.*, ft5.*; 1246EXPLAIN (verbose, costs off) 1247DELETE FROM ft2 1248 USING ft4 INNER JOIN ft5 ON (ft4.c1 === ft5.c1) 1249 WHERE ft2.c1 > 2000 AND ft2.c2 = ft4.c1 1250 RETURNING ft2.c1, ft2.c2, ft2.c3; -- can't be pushed down 1251DELETE FROM ft2 1252 USING ft4 INNER JOIN ft5 ON (ft4.c1 === ft5.c1) 1253 WHERE ft2.c1 > 2000 AND ft2.c2 = ft4.c1 1254 RETURNING ft2.c1, ft2.c2, ft2.c3; 1255DELETE FROM ft2 WHERE ft2.c1 > 2000; 1256ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw'); 1257 1258-- Test that trigger on remote table works as expected 1259CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$ 1260BEGIN 1261 NEW.c3 = NEW.c3 || '_trig_update'; 1262 RETURN NEW; 1263END; 1264$$ LANGUAGE plpgsql; 1265CREATE TRIGGER t1_br_insert BEFORE INSERT OR UPDATE 1266 ON "S 1"."T 1" FOR EACH ROW EXECUTE PROCEDURE "S 1".F_BRTRIG(); 1267 1268INSERT INTO ft2 (c1,c2,c3) VALUES (1208, 818, 'fff') RETURNING *; 1269INSERT INTO ft2 (c1,c2,c3,c6) VALUES (1218, 818, 'ggg', '(--;') RETURNING *; 1270UPDATE ft2 SET c2 = c2 + 600 WHERE c1 % 10 = 8 AND c1 < 1200 RETURNING *; 1271 1272-- Test errors thrown on remote side during update 1273ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0); 1274 1275INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key 1276INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT DO NOTHING; -- works 1277INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO NOTHING; -- unsupported 1278INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO UPDATE SET c3 = 'ffg'; -- unsupported 1279INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive 1280UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive 1281 1282-- Test savepoint/rollback behavior 1283select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1284select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; 1285begin; 1286update ft2 set c2 = 42 where c2 = 0; 1287select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1288savepoint s1; 1289update ft2 set c2 = 44 where c2 = 4; 1290select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1291release savepoint s1; 1292select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1293savepoint s2; 1294update ft2 set c2 = 46 where c2 = 6; 1295select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1296rollback to savepoint s2; 1297select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1298release savepoint s2; 1299select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1300savepoint s3; 1301update ft2 set c2 = -2 where c2 = 42 and c1 = 10; -- fail on remote side 1302rollback to savepoint s3; 1303select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1304release savepoint s3; 1305select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1306-- none of the above is committed yet remotely 1307select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; 1308commit; 1309select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1310select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; 1311 1312VACUUM ANALYZE "S 1"."T 1"; 1313 1314-- Above DMLs add data with c6 as NULL in ft1, so test ORDER BY NULLS LAST and NULLs 1315-- FIRST behavior here. 1316-- ORDER BY DESC NULLS LAST options 1317EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 ORDER BY c6 DESC NULLS LAST, c1 OFFSET 795 LIMIT 10; 1318SELECT * FROM ft1 ORDER BY c6 DESC NULLS LAST, c1 OFFSET 795 LIMIT 10; 1319-- ORDER BY DESC NULLS FIRST options 1320EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 ORDER BY c6 DESC NULLS FIRST, c1 OFFSET 15 LIMIT 10; 1321SELECT * FROM ft1 ORDER BY c6 DESC NULLS FIRST, c1 OFFSET 15 LIMIT 10; 1322-- ORDER BY ASC NULLS FIRST options 1323EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10; 1324SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10; 1325 1326-- =================================================================== 1327-- test check constraints 1328-- =================================================================== 1329 1330-- Consistent check constraints provide consistent results 1331ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0); 1332EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 < 0; 1333SELECT count(*) FROM ft1 WHERE c2 < 0; 1334SET constraint_exclusion = 'on'; 1335EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 < 0; 1336SELECT count(*) FROM ft1 WHERE c2 < 0; 1337RESET constraint_exclusion; 1338-- check constraint is enforced on the remote side, not locally 1339INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive 1340UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive 1341ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2positive; 1342 1343-- But inconsistent check constraints provide inconsistent results 1344ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2negative CHECK (c2 < 0); 1345EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 >= 0; 1346SELECT count(*) FROM ft1 WHERE c2 >= 0; 1347SET constraint_exclusion = 'on'; 1348EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 >= 0; 1349SELECT count(*) FROM ft1 WHERE c2 >= 0; 1350RESET constraint_exclusion; 1351-- local check constraint is not actually enforced 1352INSERT INTO ft1(c1, c2) VALUES(1111, 2); 1353UPDATE ft1 SET c2 = c2 + 1 WHERE c1 = 1; 1354ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative; 1355 1356-- =================================================================== 1357-- test WITH CHECK OPTION constraints 1358-- =================================================================== 1359 1360CREATE FUNCTION row_before_insupd_trigfunc() RETURNS trigger AS $$BEGIN NEW.a := NEW.a + 10; RETURN NEW; END$$ LANGUAGE plpgsql; 1361 1362CREATE TABLE base_tbl (a int, b int); 1363ALTER TABLE base_tbl SET (autovacuum_enabled = 'false'); 1364CREATE TRIGGER row_before_insupd_trigger BEFORE INSERT OR UPDATE ON base_tbl FOR EACH ROW EXECUTE PROCEDURE row_before_insupd_trigfunc(); 1365CREATE FOREIGN TABLE foreign_tbl (a int, b int) 1366 SERVER loopback OPTIONS (table_name 'base_tbl'); 1367CREATE VIEW rw_view AS SELECT * FROM foreign_tbl 1368 WHERE a < b WITH CHECK OPTION; 1369\d+ rw_view 1370 1371EXPLAIN (VERBOSE, COSTS OFF) 1372INSERT INTO rw_view VALUES (0, 5); 1373INSERT INTO rw_view VALUES (0, 5); -- should fail 1374EXPLAIN (VERBOSE, COSTS OFF) 1375INSERT INTO rw_view VALUES (0, 15); 1376INSERT INTO rw_view VALUES (0, 15); -- ok 1377SELECT * FROM foreign_tbl; 1378 1379EXPLAIN (VERBOSE, COSTS OFF) 1380UPDATE rw_view SET b = b + 5; 1381UPDATE rw_view SET b = b + 5; -- should fail 1382EXPLAIN (VERBOSE, COSTS OFF) 1383UPDATE rw_view SET b = b + 15; 1384UPDATE rw_view SET b = b + 15; -- ok 1385SELECT * FROM foreign_tbl; 1386 1387DROP FOREIGN TABLE foreign_tbl CASCADE; 1388DROP TRIGGER row_before_insupd_trigger ON base_tbl; 1389DROP TABLE base_tbl; 1390 1391-- test WCO for partitions 1392 1393CREATE TABLE child_tbl (a int, b int); 1394ALTER TABLE child_tbl SET (autovacuum_enabled = 'false'); 1395CREATE TRIGGER row_before_insupd_trigger BEFORE INSERT OR UPDATE ON child_tbl FOR EACH ROW EXECUTE PROCEDURE row_before_insupd_trigfunc(); 1396CREATE FOREIGN TABLE foreign_tbl (a int, b int) 1397 SERVER loopback OPTIONS (table_name 'child_tbl'); 1398 1399CREATE TABLE parent_tbl (a int, b int) PARTITION BY RANGE(a); 1400ALTER TABLE parent_tbl ATTACH PARTITION foreign_tbl FOR VALUES FROM (0) TO (100); 1401 1402CREATE VIEW rw_view AS SELECT * FROM parent_tbl 1403 WHERE a < b WITH CHECK OPTION; 1404\d+ rw_view 1405 1406EXPLAIN (VERBOSE, COSTS OFF) 1407INSERT INTO rw_view VALUES (0, 5); 1408INSERT INTO rw_view VALUES (0, 5); -- should fail 1409EXPLAIN (VERBOSE, COSTS OFF) 1410INSERT INTO rw_view VALUES (0, 15); 1411INSERT INTO rw_view VALUES (0, 15); -- ok 1412SELECT * FROM foreign_tbl; 1413 1414EXPLAIN (VERBOSE, COSTS OFF) 1415UPDATE rw_view SET b = b + 5; 1416UPDATE rw_view SET b = b + 5; -- should fail 1417EXPLAIN (VERBOSE, COSTS OFF) 1418UPDATE rw_view SET b = b + 15; 1419UPDATE rw_view SET b = b + 15; -- ok 1420SELECT * FROM foreign_tbl; 1421 1422DROP FOREIGN TABLE foreign_tbl CASCADE; 1423DROP TRIGGER row_before_insupd_trigger ON child_tbl; 1424DROP TABLE parent_tbl CASCADE; 1425 1426DROP FUNCTION row_before_insupd_trigfunc; 1427 1428-- =================================================================== 1429-- test serial columns (ie, sequence-based defaults) 1430-- =================================================================== 1431create table loc1 (f1 serial, f2 text); 1432alter table loc1 set (autovacuum_enabled = 'false'); 1433create foreign table rem1 (f1 serial, f2 text) 1434 server loopback options(table_name 'loc1'); 1435select pg_catalog.setval('rem1_f1_seq', 10, false); 1436insert into loc1(f2) values('hi'); 1437insert into rem1(f2) values('hi remote'); 1438insert into loc1(f2) values('bye'); 1439insert into rem1(f2) values('bye remote'); 1440select * from loc1; 1441select * from rem1; 1442 1443-- =================================================================== 1444-- test generated columns 1445-- =================================================================== 1446create table gloc1 ( 1447 a int, 1448 b int generated always as (a * 2) stored); 1449alter table gloc1 set (autovacuum_enabled = 'false'); 1450create foreign table grem1 ( 1451 a int, 1452 b int generated always as (a * 2) stored) 1453 server loopback options(table_name 'gloc1'); 1454explain (verbose, costs off) 1455insert into grem1 (a) values (1), (2); 1456insert into grem1 (a) values (1), (2); 1457explain (verbose, costs off) 1458update grem1 set a = 22 where a = 2; 1459update grem1 set a = 22 where a = 2; 1460select * from gloc1; 1461select * from grem1; 1462delete from grem1; 1463 1464-- test copy from 1465copy grem1 from stdin; 14661 14672 1468\. 1469select * from gloc1; 1470select * from grem1; 1471delete from grem1; 1472 1473-- =================================================================== 1474-- test local triggers 1475-- =================================================================== 1476 1477-- Trigger functions "borrowed" from triggers regress test. 1478CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS $$ 1479BEGIN 1480 RAISE NOTICE 'trigger_func(%) called: action = %, when = %, level = %', 1481 TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL; 1482 RETURN NULL; 1483END;$$; 1484 1485CREATE TRIGGER trig_stmt_before BEFORE DELETE OR INSERT OR UPDATE ON rem1 1486 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); 1487CREATE TRIGGER trig_stmt_after AFTER DELETE OR INSERT OR UPDATE ON rem1 1488 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); 1489 1490CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger 1491LANGUAGE plpgsql AS $$ 1492 1493declare 1494 oldnew text[]; 1495 relid text; 1496 argstr text; 1497begin 1498 1499 relid := TG_relid::regclass; 1500 argstr := ''; 1501 for i in 0 .. TG_nargs - 1 loop 1502 if i > 0 then 1503 argstr := argstr || ', '; 1504 end if; 1505 argstr := argstr || TG_argv[i]; 1506 end loop; 1507 1508 RAISE NOTICE '%(%) % % % ON %', 1509 tg_name, argstr, TG_when, TG_level, TG_OP, relid; 1510 oldnew := '{}'::text[]; 1511 if TG_OP != 'INSERT' then 1512 oldnew := array_append(oldnew, format('OLD: %s', OLD)); 1513 end if; 1514 1515 if TG_OP != 'DELETE' then 1516 oldnew := array_append(oldnew, format('NEW: %s', NEW)); 1517 end if; 1518 1519 RAISE NOTICE '%', array_to_string(oldnew, ','); 1520 1521 if TG_OP = 'DELETE' then 1522 return OLD; 1523 else 1524 return NEW; 1525 end if; 1526end; 1527$$; 1528 1529-- Test basic functionality 1530CREATE TRIGGER trig_row_before 1531BEFORE INSERT OR UPDATE OR DELETE ON rem1 1532FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1533 1534CREATE TRIGGER trig_row_after 1535AFTER INSERT OR UPDATE OR DELETE ON rem1 1536FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1537 1538delete from rem1; 1539insert into rem1 values(1,'insert'); 1540update rem1 set f2 = 'update' where f1 = 1; 1541update rem1 set f2 = f2 || f2; 1542 1543 1544-- cleanup 1545DROP TRIGGER trig_row_before ON rem1; 1546DROP TRIGGER trig_row_after ON rem1; 1547DROP TRIGGER trig_stmt_before ON rem1; 1548DROP TRIGGER trig_stmt_after ON rem1; 1549 1550DELETE from rem1; 1551 1552-- Test multiple AFTER ROW triggers on a foreign table 1553CREATE TRIGGER trig_row_after1 1554AFTER INSERT OR UPDATE OR DELETE ON rem1 1555FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1556 1557CREATE TRIGGER trig_row_after2 1558AFTER INSERT OR UPDATE OR DELETE ON rem1 1559FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1560 1561insert into rem1 values(1,'insert'); 1562update rem1 set f2 = 'update' where f1 = 1; 1563update rem1 set f2 = f2 || f2; 1564delete from rem1; 1565 1566-- cleanup 1567DROP TRIGGER trig_row_after1 ON rem1; 1568DROP TRIGGER trig_row_after2 ON rem1; 1569 1570-- Test WHEN conditions 1571 1572CREATE TRIGGER trig_row_before_insupd 1573BEFORE INSERT OR UPDATE ON rem1 1574FOR EACH ROW 1575WHEN (NEW.f2 like '%update%') 1576EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1577 1578CREATE TRIGGER trig_row_after_insupd 1579AFTER INSERT OR UPDATE ON rem1 1580FOR EACH ROW 1581WHEN (NEW.f2 like '%update%') 1582EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1583 1584-- Insert or update not matching: nothing happens 1585INSERT INTO rem1 values(1, 'insert'); 1586UPDATE rem1 set f2 = 'test'; 1587 1588-- Insert or update matching: triggers are fired 1589INSERT INTO rem1 values(2, 'update'); 1590UPDATE rem1 set f2 = 'update update' where f1 = '2'; 1591 1592CREATE TRIGGER trig_row_before_delete 1593BEFORE DELETE ON rem1 1594FOR EACH ROW 1595WHEN (OLD.f2 like '%update%') 1596EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1597 1598CREATE TRIGGER trig_row_after_delete 1599AFTER DELETE ON rem1 1600FOR EACH ROW 1601WHEN (OLD.f2 like '%update%') 1602EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1603 1604-- Trigger is fired for f1=2, not for f1=1 1605DELETE FROM rem1; 1606 1607-- cleanup 1608DROP TRIGGER trig_row_before_insupd ON rem1; 1609DROP TRIGGER trig_row_after_insupd ON rem1; 1610DROP TRIGGER trig_row_before_delete ON rem1; 1611DROP TRIGGER trig_row_after_delete ON rem1; 1612 1613 1614-- Test various RETURN statements in BEFORE triggers. 1615 1616CREATE FUNCTION trig_row_before_insupdate() RETURNS TRIGGER AS $$ 1617 BEGIN 1618 NEW.f2 := NEW.f2 || ' triggered !'; 1619 RETURN NEW; 1620 END 1621$$ language plpgsql; 1622 1623CREATE TRIGGER trig_row_before_insupd 1624BEFORE INSERT OR UPDATE ON rem1 1625FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate(); 1626 1627-- The new values should have 'triggered' appended 1628INSERT INTO rem1 values(1, 'insert'); 1629SELECT * from loc1; 1630INSERT INTO rem1 values(2, 'insert') RETURNING f2; 1631SELECT * from loc1; 1632UPDATE rem1 set f2 = ''; 1633SELECT * from loc1; 1634UPDATE rem1 set f2 = 'skidoo' RETURNING f2; 1635SELECT * from loc1; 1636 1637EXPLAIN (verbose, costs off) 1638UPDATE rem1 set f1 = 10; -- all columns should be transmitted 1639UPDATE rem1 set f1 = 10; 1640SELECT * from loc1; 1641 1642DELETE FROM rem1; 1643 1644-- Add a second trigger, to check that the changes are propagated correctly 1645-- from trigger to trigger 1646CREATE TRIGGER trig_row_before_insupd2 1647BEFORE INSERT OR UPDATE ON rem1 1648FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate(); 1649 1650INSERT INTO rem1 values(1, 'insert'); 1651SELECT * from loc1; 1652INSERT INTO rem1 values(2, 'insert') RETURNING f2; 1653SELECT * from loc1; 1654UPDATE rem1 set f2 = ''; 1655SELECT * from loc1; 1656UPDATE rem1 set f2 = 'skidoo' RETURNING f2; 1657SELECT * from loc1; 1658 1659DROP TRIGGER trig_row_before_insupd ON rem1; 1660DROP TRIGGER trig_row_before_insupd2 ON rem1; 1661 1662DELETE from rem1; 1663 1664INSERT INTO rem1 VALUES (1, 'test'); 1665 1666-- Test with a trigger returning NULL 1667CREATE FUNCTION trig_null() RETURNS TRIGGER AS $$ 1668 BEGIN 1669 RETURN NULL; 1670 END 1671$$ language plpgsql; 1672 1673CREATE TRIGGER trig_null 1674BEFORE INSERT OR UPDATE OR DELETE ON rem1 1675FOR EACH ROW EXECUTE PROCEDURE trig_null(); 1676 1677-- Nothing should have changed. 1678INSERT INTO rem1 VALUES (2, 'test2'); 1679 1680SELECT * from loc1; 1681 1682UPDATE rem1 SET f2 = 'test2'; 1683 1684SELECT * from loc1; 1685 1686DELETE from rem1; 1687 1688SELECT * from loc1; 1689 1690DROP TRIGGER trig_null ON rem1; 1691DELETE from rem1; 1692 1693-- Test a combination of local and remote triggers 1694CREATE TRIGGER trig_row_before 1695BEFORE INSERT OR UPDATE OR DELETE ON rem1 1696FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1697 1698CREATE TRIGGER trig_row_after 1699AFTER INSERT OR UPDATE OR DELETE ON rem1 1700FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1701 1702CREATE TRIGGER trig_local_before BEFORE INSERT OR UPDATE ON loc1 1703FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate(); 1704 1705INSERT INTO rem1(f2) VALUES ('test'); 1706UPDATE rem1 SET f2 = 'testo'; 1707 1708-- Test returning a system attribute 1709INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid; 1710 1711-- cleanup 1712DROP TRIGGER trig_row_before ON rem1; 1713DROP TRIGGER trig_row_after ON rem1; 1714DROP TRIGGER trig_local_before ON loc1; 1715 1716 1717-- Test direct foreign table modification functionality 1718 1719-- Test with statement-level triggers 1720CREATE TRIGGER trig_stmt_before 1721 BEFORE DELETE OR INSERT OR UPDATE ON rem1 1722 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); 1723EXPLAIN (verbose, costs off) 1724UPDATE rem1 set f2 = ''; -- can be pushed down 1725EXPLAIN (verbose, costs off) 1726DELETE FROM rem1; -- can be pushed down 1727DROP TRIGGER trig_stmt_before ON rem1; 1728 1729CREATE TRIGGER trig_stmt_after 1730 AFTER DELETE OR INSERT OR UPDATE ON rem1 1731 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); 1732EXPLAIN (verbose, costs off) 1733UPDATE rem1 set f2 = ''; -- can be pushed down 1734EXPLAIN (verbose, costs off) 1735DELETE FROM rem1; -- can be pushed down 1736DROP TRIGGER trig_stmt_after ON rem1; 1737 1738-- Test with row-level ON INSERT triggers 1739CREATE TRIGGER trig_row_before_insert 1740BEFORE INSERT ON rem1 1741FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1742EXPLAIN (verbose, costs off) 1743UPDATE rem1 set f2 = ''; -- can be pushed down 1744EXPLAIN (verbose, costs off) 1745DELETE FROM rem1; -- can be pushed down 1746DROP TRIGGER trig_row_before_insert ON rem1; 1747 1748CREATE TRIGGER trig_row_after_insert 1749AFTER INSERT ON rem1 1750FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1751EXPLAIN (verbose, costs off) 1752UPDATE rem1 set f2 = ''; -- can be pushed down 1753EXPLAIN (verbose, costs off) 1754DELETE FROM rem1; -- can be pushed down 1755DROP TRIGGER trig_row_after_insert ON rem1; 1756 1757-- Test with row-level ON UPDATE triggers 1758CREATE TRIGGER trig_row_before_update 1759BEFORE UPDATE ON rem1 1760FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1761EXPLAIN (verbose, costs off) 1762UPDATE rem1 set f2 = ''; -- can't be pushed down 1763EXPLAIN (verbose, costs off) 1764DELETE FROM rem1; -- can be pushed down 1765DROP TRIGGER trig_row_before_update ON rem1; 1766 1767CREATE TRIGGER trig_row_after_update 1768AFTER UPDATE ON rem1 1769FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1770EXPLAIN (verbose, costs off) 1771UPDATE rem1 set f2 = ''; -- can't be pushed down 1772EXPLAIN (verbose, costs off) 1773DELETE FROM rem1; -- can be pushed down 1774DROP TRIGGER trig_row_after_update ON rem1; 1775 1776-- Test with row-level ON DELETE triggers 1777CREATE TRIGGER trig_row_before_delete 1778BEFORE DELETE ON rem1 1779FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1780EXPLAIN (verbose, costs off) 1781UPDATE rem1 set f2 = ''; -- can be pushed down 1782EXPLAIN (verbose, costs off) 1783DELETE FROM rem1; -- can't be pushed down 1784DROP TRIGGER trig_row_before_delete ON rem1; 1785 1786CREATE TRIGGER trig_row_after_delete 1787AFTER DELETE ON rem1 1788FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1789EXPLAIN (verbose, costs off) 1790UPDATE rem1 set f2 = ''; -- can be pushed down 1791EXPLAIN (verbose, costs off) 1792DELETE FROM rem1; -- can't be pushed down 1793DROP TRIGGER trig_row_after_delete ON rem1; 1794 1795-- =================================================================== 1796-- test inheritance features 1797-- =================================================================== 1798 1799CREATE TABLE a (aa TEXT); 1800CREATE TABLE loct (aa TEXT, bb TEXT); 1801ALTER TABLE a SET (autovacuum_enabled = 'false'); 1802ALTER TABLE loct SET (autovacuum_enabled = 'false'); 1803CREATE FOREIGN TABLE b (bb TEXT) INHERITS (a) 1804 SERVER loopback OPTIONS (table_name 'loct'); 1805 1806INSERT INTO a(aa) VALUES('aaa'); 1807INSERT INTO a(aa) VALUES('aaaa'); 1808INSERT INTO a(aa) VALUES('aaaaa'); 1809 1810INSERT INTO b(aa) VALUES('bbb'); 1811INSERT INTO b(aa) VALUES('bbbb'); 1812INSERT INTO b(aa) VALUES('bbbbb'); 1813 1814SELECT tableoid::regclass, * FROM a; 1815SELECT tableoid::regclass, * FROM b; 1816SELECT tableoid::regclass, * FROM ONLY a; 1817 1818UPDATE a SET aa = 'zzzzzz' WHERE aa LIKE 'aaaa%'; 1819 1820SELECT tableoid::regclass, * FROM a; 1821SELECT tableoid::regclass, * FROM b; 1822SELECT tableoid::regclass, * FROM ONLY a; 1823 1824UPDATE b SET aa = 'new'; 1825 1826SELECT tableoid::regclass, * FROM a; 1827SELECT tableoid::regclass, * FROM b; 1828SELECT tableoid::regclass, * FROM ONLY a; 1829 1830UPDATE a SET aa = 'newtoo'; 1831 1832SELECT tableoid::regclass, * FROM a; 1833SELECT tableoid::regclass, * FROM b; 1834SELECT tableoid::regclass, * FROM ONLY a; 1835 1836DELETE FROM a; 1837 1838SELECT tableoid::regclass, * FROM a; 1839SELECT tableoid::regclass, * FROM b; 1840SELECT tableoid::regclass, * FROM ONLY a; 1841 1842DROP TABLE a CASCADE; 1843DROP TABLE loct; 1844 1845-- Check SELECT FOR UPDATE/SHARE with an inherited source table 1846create table loct1 (f1 int, f2 int, f3 int); 1847create table loct2 (f1 int, f2 int, f3 int); 1848 1849alter table loct1 set (autovacuum_enabled = 'false'); 1850alter table loct2 set (autovacuum_enabled = 'false'); 1851 1852create table foo (f1 int, f2 int); 1853create foreign table foo2 (f3 int) inherits (foo) 1854 server loopback options (table_name 'loct1'); 1855create table bar (f1 int, f2 int); 1856create foreign table bar2 (f3 int) inherits (bar) 1857 server loopback options (table_name 'loct2'); 1858 1859alter table foo set (autovacuum_enabled = 'false'); 1860alter table bar set (autovacuum_enabled = 'false'); 1861 1862insert into foo values(1,1); 1863insert into foo values(3,3); 1864insert into foo2 values(2,2,2); 1865insert into foo2 values(4,4,4); 1866insert into bar values(1,11); 1867insert into bar values(2,22); 1868insert into bar values(6,66); 1869insert into bar2 values(3,33,33); 1870insert into bar2 values(4,44,44); 1871insert into bar2 values(7,77,77); 1872 1873explain (verbose, costs off) 1874select * from bar where f1 in (select f1 from foo) for update; 1875select * from bar where f1 in (select f1 from foo) for update; 1876 1877explain (verbose, costs off) 1878select * from bar where f1 in (select f1 from foo) for share; 1879select * from bar where f1 in (select f1 from foo) for share; 1880 1881-- Now check SELECT FOR UPDATE/SHARE with an inherited source table, 1882-- where the parent is itself a foreign table 1883create table loct4 (f1 int, f2 int, f3 int); 1884create foreign table foo2child (f3 int) inherits (foo2) 1885 server loopback options (table_name 'loct4'); 1886 1887explain (verbose, costs off) 1888select * from bar where f1 in (select f1 from foo2) for share; 1889select * from bar where f1 in (select f1 from foo2) for share; 1890 1891drop foreign table foo2child; 1892 1893-- And with a local child relation of the foreign table parent 1894create table foo2child (f3 int) inherits (foo2); 1895 1896explain (verbose, costs off) 1897select * from bar where f1 in (select f1 from foo2) for share; 1898select * from bar where f1 in (select f1 from foo2) for share; 1899 1900drop table foo2child; 1901 1902-- Check UPDATE with inherited target and an inherited source table 1903explain (verbose, costs off) 1904update bar set f2 = f2 + 100 where f1 in (select f1 from foo); 1905update bar set f2 = f2 + 100 where f1 in (select f1 from foo); 1906 1907select tableoid::regclass, * from bar order by 1,2; 1908 1909-- Check UPDATE with inherited target and an appendrel subquery 1910explain (verbose, costs off) 1911update bar set f2 = f2 + 100 1912from 1913 ( select f1 from foo union all select f1+3 from foo ) ss 1914where bar.f1 = ss.f1; 1915update bar set f2 = f2 + 100 1916from 1917 ( select f1 from foo union all select f1+3 from foo ) ss 1918where bar.f1 = ss.f1; 1919 1920select tableoid::regclass, * from bar order by 1,2; 1921 1922-- Test forcing the remote server to produce sorted data for a merge join, 1923-- but the foreign table is an inheritance child. 1924truncate table loct1; 1925truncate table only foo; 1926\set num_rows_foo 2000 1927insert into loct1 select generate_series(0, :num_rows_foo, 2), generate_series(0, :num_rows_foo, 2), generate_series(0, :num_rows_foo, 2); 1928insert into foo select generate_series(1, :num_rows_foo, 2), generate_series(1, :num_rows_foo, 2); 1929SET enable_hashjoin to false; 1930SET enable_nestloop to false; 1931alter foreign table foo2 options (use_remote_estimate 'true'); 1932create index i_loct1_f1 on loct1(f1); 1933create index i_foo_f1 on foo(f1); 1934analyze foo; 1935analyze loct1; 1936-- inner join; expressions in the clauses appear in the equivalence class list 1937explain (verbose, costs off) 1938 select foo.f1, loct1.f1 from foo join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10; 1939select foo.f1, loct1.f1 from foo join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10; 1940-- outer join; expressions in the clauses do not appear in equivalence class 1941-- list but no output change as compared to the previous query 1942explain (verbose, costs off) 1943 select foo.f1, loct1.f1 from foo left join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10; 1944select foo.f1, loct1.f1 from foo left join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10; 1945RESET enable_hashjoin; 1946RESET enable_nestloop; 1947 1948-- Test that WHERE CURRENT OF is not supported 1949begin; 1950declare c cursor for select * from bar where f1 = 7; 1951fetch from c; 1952update bar set f2 = null where current of c; 1953rollback; 1954 1955explain (verbose, costs off) 1956delete from foo where f1 < 5 returning *; 1957delete from foo where f1 < 5 returning *; 1958explain (verbose, costs off) 1959update bar set f2 = f2 + 100 returning *; 1960update bar set f2 = f2 + 100 returning *; 1961 1962-- Test that UPDATE/DELETE with inherited target works with row-level triggers 1963CREATE TRIGGER trig_row_before 1964BEFORE UPDATE OR DELETE ON bar2 1965FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1966 1967CREATE TRIGGER trig_row_after 1968AFTER UPDATE OR DELETE ON bar2 1969FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1970 1971explain (verbose, costs off) 1972update bar set f2 = f2 + 100; 1973update bar set f2 = f2 + 100; 1974 1975explain (verbose, costs off) 1976delete from bar where f2 < 400; 1977delete from bar where f2 < 400; 1978 1979-- cleanup 1980drop table foo cascade; 1981drop table bar cascade; 1982drop table loct1; 1983drop table loct2; 1984 1985-- Test pushing down UPDATE/DELETE joins to the remote server 1986create table parent (a int, b text); 1987create table loct1 (a int, b text); 1988create table loct2 (a int, b text); 1989create foreign table remt1 (a int, b text) 1990 server loopback options (table_name 'loct1'); 1991create foreign table remt2 (a int, b text) 1992 server loopback options (table_name 'loct2'); 1993alter foreign table remt1 inherit parent; 1994 1995insert into remt1 values (1, 'foo'); 1996insert into remt1 values (2, 'bar'); 1997insert into remt2 values (1, 'foo'); 1998insert into remt2 values (2, 'bar'); 1999 2000analyze remt1; 2001analyze remt2; 2002 2003explain (verbose, costs off) 2004update parent set b = parent.b || remt2.b from remt2 where parent.a = remt2.a returning *; 2005update parent set b = parent.b || remt2.b from remt2 where parent.a = remt2.a returning *; 2006explain (verbose, costs off) 2007delete from parent using remt2 where parent.a = remt2.a returning parent; 2008delete from parent using remt2 where parent.a = remt2.a returning parent; 2009 2010-- cleanup 2011drop foreign table remt1; 2012drop foreign table remt2; 2013drop table loct1; 2014drop table loct2; 2015drop table parent; 2016 2017-- =================================================================== 2018-- test tuple routing for foreign-table partitions 2019-- =================================================================== 2020 2021-- Test insert tuple routing 2022create table itrtest (a int, b text) partition by list (a); 2023create table loct1 (a int check (a in (1)), b text); 2024create foreign table remp1 (a int check (a in (1)), b text) server loopback options (table_name 'loct1'); 2025create table loct2 (a int check (a in (2)), b text); 2026create foreign table remp2 (b text, a int check (a in (2))) server loopback options (table_name 'loct2'); 2027alter table itrtest attach partition remp1 for values in (1); 2028alter table itrtest attach partition remp2 for values in (2); 2029 2030insert into itrtest values (1, 'foo'); 2031insert into itrtest values (1, 'bar') returning *; 2032insert into itrtest values (2, 'baz'); 2033insert into itrtest values (2, 'qux') returning *; 2034insert into itrtest values (1, 'test1'), (2, 'test2') returning *; 2035 2036select tableoid::regclass, * FROM itrtest; 2037select tableoid::regclass, * FROM remp1; 2038select tableoid::regclass, * FROM remp2; 2039 2040delete from itrtest; 2041 2042create unique index loct1_idx on loct1 (a); 2043 2044-- DO NOTHING without an inference specification is supported 2045insert into itrtest values (1, 'foo') on conflict do nothing returning *; 2046insert into itrtest values (1, 'foo') on conflict do nothing returning *; 2047 2048-- But other cases are not supported 2049insert into itrtest values (1, 'bar') on conflict (a) do nothing; 2050insert into itrtest values (1, 'bar') on conflict (a) do update set b = excluded.b; 2051 2052select tableoid::regclass, * FROM itrtest; 2053 2054delete from itrtest; 2055 2056drop index loct1_idx; 2057 2058-- Test that remote triggers work with insert tuple routing 2059create function br_insert_trigfunc() returns trigger as $$ 2060begin 2061 new.b := new.b || ' triggered !'; 2062 return new; 2063end 2064$$ language plpgsql; 2065create trigger loct1_br_insert_trigger before insert on loct1 2066 for each row execute procedure br_insert_trigfunc(); 2067create trigger loct2_br_insert_trigger before insert on loct2 2068 for each row execute procedure br_insert_trigfunc(); 2069 2070-- The new values are concatenated with ' triggered !' 2071insert into itrtest values (1, 'foo') returning *; 2072insert into itrtest values (2, 'qux') returning *; 2073insert into itrtest values (1, 'test1'), (2, 'test2') returning *; 2074with result as (insert into itrtest values (1, 'test1'), (2, 'test2') returning *) select * from result; 2075 2076drop trigger loct1_br_insert_trigger on loct1; 2077drop trigger loct2_br_insert_trigger on loct2; 2078 2079drop table itrtest; 2080drop table loct1; 2081drop table loct2; 2082 2083-- Test update tuple routing 2084create table utrtest (a int, b text) partition by list (a); 2085create table loct (a int check (a in (1)), b text); 2086create foreign table remp (a int check (a in (1)), b text) server loopback options (table_name 'loct'); 2087create table locp (a int check (a in (2)), b text); 2088alter table utrtest attach partition remp for values in (1); 2089alter table utrtest attach partition locp for values in (2); 2090 2091insert into utrtest values (1, 'foo'); 2092insert into utrtest values (2, 'qux'); 2093 2094select tableoid::regclass, * FROM utrtest; 2095select tableoid::regclass, * FROM remp; 2096select tableoid::regclass, * FROM locp; 2097 2098-- It's not allowed to move a row from a partition that is foreign to another 2099update utrtest set a = 2 where b = 'foo' returning *; 2100 2101-- But the reverse is allowed 2102update utrtest set a = 1 where b = 'qux' returning *; 2103 2104select tableoid::regclass, * FROM utrtest; 2105select tableoid::regclass, * FROM remp; 2106select tableoid::regclass, * FROM locp; 2107 2108-- The executor should not let unexercised FDWs shut down 2109update utrtest set a = 1 where b = 'foo'; 2110 2111-- Test that remote triggers work with update tuple routing 2112create trigger loct_br_insert_trigger before insert on loct 2113 for each row execute procedure br_insert_trigfunc(); 2114 2115delete from utrtest; 2116insert into utrtest values (2, 'qux'); 2117 2118-- Check case where the foreign partition is a subplan target rel 2119explain (verbose, costs off) 2120update utrtest set a = 1 where a = 1 or a = 2 returning *; 2121-- The new values are concatenated with ' triggered !' 2122update utrtest set a = 1 where a = 1 or a = 2 returning *; 2123 2124delete from utrtest; 2125insert into utrtest values (2, 'qux'); 2126 2127-- Check case where the foreign partition isn't a subplan target rel 2128explain (verbose, costs off) 2129update utrtest set a = 1 where a = 2 returning *; 2130-- The new values are concatenated with ' triggered !' 2131update utrtest set a = 1 where a = 2 returning *; 2132 2133drop trigger loct_br_insert_trigger on loct; 2134 2135-- We can move rows to a foreign partition that has been updated already, 2136-- but can't move rows to a foreign partition that hasn't been updated yet 2137 2138delete from utrtest; 2139insert into utrtest values (1, 'foo'); 2140insert into utrtest values (2, 'qux'); 2141 2142-- Test the former case: 2143-- with a direct modification plan 2144explain (verbose, costs off) 2145update utrtest set a = 1 returning *; 2146update utrtest set a = 1 returning *; 2147 2148delete from utrtest; 2149insert into utrtest values (1, 'foo'); 2150insert into utrtest values (2, 'qux'); 2151 2152-- with a non-direct modification plan 2153explain (verbose, costs off) 2154update utrtest set a = 1 from (values (1), (2)) s(x) where a = s.x returning *; 2155update utrtest set a = 1 from (values (1), (2)) s(x) where a = s.x returning *; 2156 2157-- Change the definition of utrtest so that the foreign partition get updated 2158-- after the local partition 2159delete from utrtest; 2160alter table utrtest detach partition remp; 2161drop foreign table remp; 2162alter table loct drop constraint loct_a_check; 2163alter table loct add check (a in (3)); 2164create foreign table remp (a int check (a in (3)), b text) server loopback options (table_name 'loct'); 2165alter table utrtest attach partition remp for values in (3); 2166insert into utrtest values (2, 'qux'); 2167insert into utrtest values (3, 'xyzzy'); 2168 2169-- Test the latter case: 2170-- with a direct modification plan 2171explain (verbose, costs off) 2172update utrtest set a = 3 returning *; 2173update utrtest set a = 3 returning *; -- ERROR 2174 2175-- with a non-direct modification plan 2176explain (verbose, costs off) 2177update utrtest set a = 3 from (values (2), (3)) s(x) where a = s.x returning *; 2178update utrtest set a = 3 from (values (2), (3)) s(x) where a = s.x returning *; -- ERROR 2179 2180drop table utrtest; 2181drop table loct; 2182 2183-- Test copy tuple routing 2184create table ctrtest (a int, b text) partition by list (a); 2185create table loct1 (a int check (a in (1)), b text); 2186create foreign table remp1 (a int check (a in (1)), b text) server loopback options (table_name 'loct1'); 2187create table loct2 (a int check (a in (2)), b text); 2188create foreign table remp2 (b text, a int check (a in (2))) server loopback options (table_name 'loct2'); 2189alter table ctrtest attach partition remp1 for values in (1); 2190alter table ctrtest attach partition remp2 for values in (2); 2191 2192copy ctrtest from stdin; 21931 foo 21942 qux 2195\. 2196 2197select tableoid::regclass, * FROM ctrtest; 2198select tableoid::regclass, * FROM remp1; 2199select tableoid::regclass, * FROM remp2; 2200 2201-- Copying into foreign partitions directly should work as well 2202copy remp1 from stdin; 22031 bar 2204\. 2205 2206select tableoid::regclass, * FROM remp1; 2207 2208drop table ctrtest; 2209drop table loct1; 2210drop table loct2; 2211 2212-- =================================================================== 2213-- test COPY FROM 2214-- =================================================================== 2215 2216create table loc2 (f1 int, f2 text); 2217alter table loc2 set (autovacuum_enabled = 'false'); 2218create foreign table rem2 (f1 int, f2 text) server loopback options(table_name 'loc2'); 2219 2220-- Test basic functionality 2221copy rem2 from stdin; 22221 foo 22232 bar 2224\. 2225select * from rem2; 2226 2227delete from rem2; 2228 2229-- Test check constraints 2230alter table loc2 add constraint loc2_f1positive check (f1 >= 0); 2231alter foreign table rem2 add constraint rem2_f1positive check (f1 >= 0); 2232 2233-- check constraint is enforced on the remote side, not locally 2234copy rem2 from stdin; 22351 foo 22362 bar 2237\. 2238copy rem2 from stdin; -- ERROR 2239-1 xyzzy 2240\. 2241select * from rem2; 2242 2243alter foreign table rem2 drop constraint rem2_f1positive; 2244alter table loc2 drop constraint loc2_f1positive; 2245 2246delete from rem2; 2247 2248-- Test local triggers 2249create trigger trig_stmt_before before insert on rem2 2250 for each statement execute procedure trigger_func(); 2251create trigger trig_stmt_after after insert on rem2 2252 for each statement execute procedure trigger_func(); 2253create trigger trig_row_before before insert on rem2 2254 for each row execute procedure trigger_data(23,'skidoo'); 2255create trigger trig_row_after after insert on rem2 2256 for each row execute procedure trigger_data(23,'skidoo'); 2257 2258copy rem2 from stdin; 22591 foo 22602 bar 2261\. 2262select * from rem2; 2263 2264drop trigger trig_row_before on rem2; 2265drop trigger trig_row_after on rem2; 2266drop trigger trig_stmt_before on rem2; 2267drop trigger trig_stmt_after on rem2; 2268 2269delete from rem2; 2270 2271create trigger trig_row_before_insert before insert on rem2 2272 for each row execute procedure trig_row_before_insupdate(); 2273 2274-- The new values are concatenated with ' triggered !' 2275copy rem2 from stdin; 22761 foo 22772 bar 2278\. 2279select * from rem2; 2280 2281drop trigger trig_row_before_insert on rem2; 2282 2283delete from rem2; 2284 2285create trigger trig_null before insert on rem2 2286 for each row execute procedure trig_null(); 2287 2288-- Nothing happens 2289copy rem2 from stdin; 22901 foo 22912 bar 2292\. 2293select * from rem2; 2294 2295drop trigger trig_null on rem2; 2296 2297delete from rem2; 2298 2299-- Test remote triggers 2300create trigger trig_row_before_insert before insert on loc2 2301 for each row execute procedure trig_row_before_insupdate(); 2302 2303-- The new values are concatenated with ' triggered !' 2304copy rem2 from stdin; 23051 foo 23062 bar 2307\. 2308select * from rem2; 2309 2310drop trigger trig_row_before_insert on loc2; 2311 2312delete from rem2; 2313 2314create trigger trig_null before insert on loc2 2315 for each row execute procedure trig_null(); 2316 2317-- Nothing happens 2318copy rem2 from stdin; 23191 foo 23202 bar 2321\. 2322select * from rem2; 2323 2324drop trigger trig_null on loc2; 2325 2326delete from rem2; 2327 2328-- Test a combination of local and remote triggers 2329create trigger rem2_trig_row_before before insert on rem2 2330 for each row execute procedure trigger_data(23,'skidoo'); 2331create trigger rem2_trig_row_after after insert on rem2 2332 for each row execute procedure trigger_data(23,'skidoo'); 2333create trigger loc2_trig_row_before_insert before insert on loc2 2334 for each row execute procedure trig_row_before_insupdate(); 2335 2336copy rem2 from stdin; 23371 foo 23382 bar 2339\. 2340select * from rem2; 2341 2342drop trigger rem2_trig_row_before on rem2; 2343drop trigger rem2_trig_row_after on rem2; 2344drop trigger loc2_trig_row_before_insert on loc2; 2345 2346delete from rem2; 2347 2348-- test COPY FROM with foreign table created in the same transaction 2349create table loc3 (f1 int, f2 text); 2350begin; 2351create foreign table rem3 (f1 int, f2 text) 2352 server loopback options(table_name 'loc3'); 2353copy rem3 from stdin; 23541 foo 23552 bar 2356\. 2357commit; 2358select * from rem3; 2359drop foreign table rem3; 2360drop table loc3; 2361 2362-- =================================================================== 2363-- test IMPORT FOREIGN SCHEMA 2364-- =================================================================== 2365 2366CREATE SCHEMA import_source; 2367CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL); 2368CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL, c3 text collate "POSIX"); 2369CREATE TYPE typ1 AS (m1 int, m2 varchar); 2370CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1); 2371CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42)); 2372CREATE TABLE import_source."x 5" (c1 float8); 2373ALTER TABLE import_source."x 5" DROP COLUMN c1; 2374CREATE TABLE import_source."x 6" (c1 int, c2 int generated always as (c1 * 2) stored); 2375CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1); 2376CREATE TABLE import_source.t4_part PARTITION OF import_source.t4 2377 FOR VALUES FROM (1) TO (100); 2378 2379CREATE SCHEMA import_dest1; 2380IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1; 2381\det+ import_dest1.* 2382\d import_dest1.* 2383 2384-- Options 2385CREATE SCHEMA import_dest2; 2386IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2 2387 OPTIONS (import_default 'true'); 2388\det+ import_dest2.* 2389\d import_dest2.* 2390CREATE SCHEMA import_dest3; 2391IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3 2392 OPTIONS (import_collate 'false', import_generated 'false', import_not_null 'false'); 2393\det+ import_dest3.* 2394\d import_dest3.* 2395 2396-- Check LIMIT TO and EXCEPT 2397CREATE SCHEMA import_dest4; 2398IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch) 2399 FROM SERVER loopback INTO import_dest4; 2400\det+ import_dest4.* 2401IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch) 2402 FROM SERVER loopback INTO import_dest4; 2403\det+ import_dest4.* 2404 2405-- Assorted error cases 2406IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest4; 2407IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO import_dest4; 2408IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO notthere; 2409IMPORT FOREIGN SCHEMA nonesuch FROM SERVER nowhere INTO notthere; 2410 2411-- Check case of a type present only on the remote server. 2412-- We can fake this by dropping the type locally in our transaction. 2413CREATE TYPE "Colors" AS ENUM ('red', 'green', 'blue'); 2414CREATE TABLE import_source.t5 (c1 int, c2 text collate "C", "Col" "Colors"); 2415 2416CREATE SCHEMA import_dest5; 2417BEGIN; 2418DROP TYPE "Colors" CASCADE; 2419IMPORT FOREIGN SCHEMA import_source LIMIT TO (t5) 2420 FROM SERVER loopback INTO import_dest5; -- ERROR 2421 2422ROLLBACK; 2423 2424BEGIN; 2425 2426 2427CREATE SERVER fetch101 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( fetch_size '101' ); 2428 2429SELECT count(*) 2430FROM pg_foreign_server 2431WHERE srvname = 'fetch101' 2432AND srvoptions @> array['fetch_size=101']; 2433 2434ALTER SERVER fetch101 OPTIONS( SET fetch_size '202' ); 2435 2436SELECT count(*) 2437FROM pg_foreign_server 2438WHERE srvname = 'fetch101' 2439AND srvoptions @> array['fetch_size=101']; 2440 2441SELECT count(*) 2442FROM pg_foreign_server 2443WHERE srvname = 'fetch101' 2444AND srvoptions @> array['fetch_size=202']; 2445 2446CREATE FOREIGN TABLE table30000 ( x int ) SERVER fetch101 OPTIONS ( fetch_size '30000' ); 2447 2448SELECT COUNT(*) 2449FROM pg_foreign_table 2450WHERE ftrelid = 'table30000'::regclass 2451AND ftoptions @> array['fetch_size=30000']; 2452 2453ALTER FOREIGN TABLE table30000 OPTIONS ( SET fetch_size '60000'); 2454 2455SELECT COUNT(*) 2456FROM pg_foreign_table 2457WHERE ftrelid = 'table30000'::regclass 2458AND ftoptions @> array['fetch_size=30000']; 2459 2460SELECT COUNT(*) 2461FROM pg_foreign_table 2462WHERE ftrelid = 'table30000'::regclass 2463AND ftoptions @> array['fetch_size=60000']; 2464 2465ROLLBACK; 2466 2467-- =================================================================== 2468-- test partitionwise joins 2469-- =================================================================== 2470SET enable_partitionwise_join=on; 2471 2472CREATE TABLE fprt1 (a int, b int, c varchar) PARTITION BY RANGE(a); 2473CREATE TABLE fprt1_p1 (LIKE fprt1); 2474CREATE TABLE fprt1_p2 (LIKE fprt1); 2475ALTER TABLE fprt1_p1 SET (autovacuum_enabled = 'false'); 2476ALTER TABLE fprt1_p2 SET (autovacuum_enabled = 'false'); 2477INSERT INTO fprt1_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 2) i; 2478INSERT INTO fprt1_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 2) i; 2479CREATE FOREIGN TABLE ftprt1_p1 PARTITION OF fprt1 FOR VALUES FROM (0) TO (250) 2480 SERVER loopback OPTIONS (table_name 'fprt1_p1', use_remote_estimate 'true'); 2481CREATE FOREIGN TABLE ftprt1_p2 PARTITION OF fprt1 FOR VALUES FROM (250) TO (500) 2482 SERVER loopback OPTIONS (TABLE_NAME 'fprt1_p2'); 2483ANALYZE fprt1; 2484ANALYZE fprt1_p1; 2485ANALYZE fprt1_p2; 2486 2487CREATE TABLE fprt2 (a int, b int, c varchar) PARTITION BY RANGE(b); 2488CREATE TABLE fprt2_p1 (LIKE fprt2); 2489CREATE TABLE fprt2_p2 (LIKE fprt2); 2490ALTER TABLE fprt2_p1 SET (autovacuum_enabled = 'false'); 2491ALTER TABLE fprt2_p2 SET (autovacuum_enabled = 'false'); 2492INSERT INTO fprt2_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 3) i; 2493INSERT INTO fprt2_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 3) i; 2494CREATE FOREIGN TABLE ftprt2_p1 (b int, c varchar, a int) 2495 SERVER loopback OPTIONS (table_name 'fprt2_p1', use_remote_estimate 'true'); 2496ALTER TABLE fprt2 ATTACH PARTITION ftprt2_p1 FOR VALUES FROM (0) TO (250); 2497CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (250) TO (500) 2498 SERVER loopback OPTIONS (table_name 'fprt2_p2', use_remote_estimate 'true'); 2499ANALYZE fprt2; 2500ANALYZE fprt2_p1; 2501ANALYZE fprt2_p2; 2502 2503-- inner join three tables 2504EXPLAIN (COSTS OFF) 2505SELECT 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; 2506SELECT 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; 2507 2508-- left outer join + nullable clause 2509EXPLAIN (VERBOSE, COSTS OFF) 2510SELECT 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; 2511SELECT 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; 2512 2513-- with whole-row reference; partitionwise join does not apply 2514EXPLAIN (COSTS OFF) 2515SELECT 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; 2516SELECT 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; 2517 2518-- join with lateral reference 2519EXPLAIN (COSTS OFF) 2520SELECT 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; 2521SELECT 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; 2522 2523-- with PHVs, partitionwise join selected but no join pushdown 2524EXPLAIN (COSTS OFF) 2525SELECT 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; 2526SELECT 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; 2527 2528-- test FOR UPDATE; partitionwise join does not apply 2529EXPLAIN (COSTS OFF) 2530SELECT 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; 2531SELECT 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; 2532 2533RESET enable_partitionwise_join; 2534 2535 2536-- =================================================================== 2537-- test partitionwise aggregates 2538-- =================================================================== 2539 2540CREATE TABLE pagg_tab (a int, b int, c text) PARTITION BY RANGE(a); 2541 2542CREATE TABLE pagg_tab_p1 (LIKE pagg_tab); 2543CREATE TABLE pagg_tab_p2 (LIKE pagg_tab); 2544CREATE TABLE pagg_tab_p3 (LIKE pagg_tab); 2545 2546INSERT INTO pagg_tab_p1 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 10; 2547INSERT 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; 2548INSERT 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; 2549 2550-- Create foreign partitions 2551CREATE FOREIGN TABLE fpagg_tab_p1 PARTITION OF pagg_tab FOR VALUES FROM (0) TO (10) SERVER loopback OPTIONS (table_name 'pagg_tab_p1'); 2552CREATE FOREIGN TABLE fpagg_tab_p2 PARTITION OF pagg_tab FOR VALUES FROM (10) TO (20) SERVER loopback OPTIONS (table_name 'pagg_tab_p2');; 2553CREATE FOREIGN TABLE fpagg_tab_p3 PARTITION OF pagg_tab FOR VALUES FROM (20) TO (30) SERVER loopback OPTIONS (table_name 'pagg_tab_p3');; 2554 2555ANALYZE pagg_tab; 2556ANALYZE fpagg_tab_p1; 2557ANALYZE fpagg_tab_p2; 2558ANALYZE fpagg_tab_p3; 2559 2560-- When GROUP BY clause matches with PARTITION KEY. 2561-- Plan with partitionwise aggregates is disabled 2562SET enable_partitionwise_aggregate TO false; 2563EXPLAIN (COSTS OFF) 2564SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1; 2565 2566-- Plan with partitionwise aggregates is enabled 2567SET enable_partitionwise_aggregate TO true; 2568EXPLAIN (COSTS OFF) 2569SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1; 2570SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1; 2571 2572-- Check with whole-row reference 2573-- Should have all the columns in the target list for the given relation 2574EXPLAIN (VERBOSE, COSTS OFF) 2575SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1; 2576SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1; 2577 2578-- When GROUP BY clause does not match with PARTITION KEY. 2579EXPLAIN (COSTS OFF) 2580SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700 ORDER BY 1; 2581 2582-- =================================================================== 2583-- access rights and superuser 2584-- =================================================================== 2585 2586-- Non-superuser cannot create a FDW without a password in the connstr 2587CREATE ROLE regress_nosuper NOSUPERUSER; 2588 2589GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO regress_nosuper; 2590 2591SET ROLE regress_nosuper; 2592 2593SHOW is_superuser; 2594 2595-- This will be OK, we can create the FDW 2596DO $d$ 2597 BEGIN 2598 EXECUTE $$CREATE SERVER loopback_nopw FOREIGN DATA WRAPPER postgres_fdw 2599 OPTIONS (dbname '$$||current_database()||$$', 2600 port '$$||current_setting('port')||$$' 2601 )$$; 2602 END; 2603$d$; 2604 2605-- But creation of user mappings for non-superusers should fail 2606CREATE USER MAPPING FOR public SERVER loopback_nopw; 2607CREATE USER MAPPING FOR CURRENT_USER SERVER loopback_nopw; 2608 2609CREATE FOREIGN TABLE ft1_nopw ( 2610 c1 int NOT NULL, 2611 c2 int NOT NULL, 2612 c3 text, 2613 c4 timestamptz, 2614 c5 timestamp, 2615 c6 varchar(10), 2616 c7 char(10) default 'ft1', 2617 c8 user_enum 2618) SERVER loopback_nopw OPTIONS (schema_name 'public', table_name 'ft1'); 2619 2620SELECT * FROM ft1_nopw LIMIT 1; 2621 2622-- If we add a password to the connstr it'll fail, because we don't allow passwords 2623-- in connstrs only in user mappings. 2624 2625DO $d$ 2626 BEGIN 2627 EXECUTE $$ALTER SERVER loopback_nopw OPTIONS (ADD password 'dummypw')$$; 2628 END; 2629$d$; 2630 2631-- If we add a password for our user mapping instead, we should get a different 2632-- error because the password wasn't actually *used* when we run with trust auth. 2633-- 2634-- This won't work with installcheck, but neither will most of the FDW checks. 2635 2636ALTER USER MAPPING FOR CURRENT_USER SERVER loopback_nopw OPTIONS (ADD password 'dummypw'); 2637 2638SELECT * FROM ft1_nopw LIMIT 1; 2639 2640-- Unpriv user cannot make the mapping passwordless 2641ALTER USER MAPPING FOR CURRENT_USER SERVER loopback_nopw OPTIONS (ADD password_required 'false'); 2642 2643 2644SELECT * FROM ft1_nopw LIMIT 1; 2645 2646RESET ROLE; 2647 2648-- But the superuser can 2649ALTER USER MAPPING FOR regress_nosuper SERVER loopback_nopw OPTIONS (ADD password_required 'false'); 2650 2651SET ROLE regress_nosuper; 2652 2653-- Should finally work now 2654SELECT * FROM ft1_nopw LIMIT 1; 2655 2656-- unpriv user also cannot set sslcert / sslkey on the user mapping 2657-- first set password_required so we see the right error messages 2658ALTER USER MAPPING FOR CURRENT_USER SERVER loopback_nopw OPTIONS (SET password_required 'true'); 2659ALTER USER MAPPING FOR CURRENT_USER SERVER loopback_nopw OPTIONS (ADD sslcert 'foo.crt'); 2660ALTER USER MAPPING FOR CURRENT_USER SERVER loopback_nopw OPTIONS (ADD sslkey 'foo.key'); 2661 2662-- We're done with the role named after a specific user and need to check the 2663-- changes to the public mapping. 2664DROP USER MAPPING FOR CURRENT_USER SERVER loopback_nopw; 2665 2666-- This will fail again as it'll resolve the user mapping for public, which 2667-- lacks password_required=false 2668SELECT * FROM ft1_nopw LIMIT 1; 2669 2670RESET ROLE; 2671 2672-- The user mapping for public is passwordless and lacks the password_required=false 2673-- mapping option, but will work because the current user is a superuser. 2674SELECT * FROM ft1_nopw LIMIT 1; 2675 2676-- cleanup 2677DROP USER MAPPING FOR public SERVER loopback_nopw; 2678DROP OWNED BY regress_nosuper; 2679DROP ROLE regress_nosuper; 2680 2681-- Clean-up 2682RESET enable_partitionwise_aggregate; 2683 2684-- Two-phase transactions are not supported. 2685BEGIN; 2686SELECT count(*) FROM ft1; 2687-- error here 2688PREPARE TRANSACTION 'fdw_tpc'; 2689ROLLBACK; 2690 2691-- =================================================================== 2692-- test connection invalidation cases 2693-- =================================================================== 2694-- This test case is for closing the connection in pgfdw_xact_callback 2695BEGIN; 2696-- Connection xact depth becomes 1 i.e. the connection is in midst of the xact. 2697SELECT 1 FROM ft1 LIMIT 1; 2698-- Connection is not closed at the end of the alter statement in 2699-- pgfdw_inval_callback. That's because the connection is in midst of this 2700-- xact, it is just marked as invalid. 2701ALTER SERVER loopback OPTIONS (ADD use_remote_estimate 'off'); 2702-- The invalid connection gets closed in pgfdw_xact_callback during commit. 2703COMMIT; 2704