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 60INSERT INTO "S 1"."T 1" 61 SELECT id, 62 id % 10, 63 to_char(id, 'FM00000'), 64 '1970-01-01'::timestamptz + ((id % 100) || ' days')::interval, 65 '1970-01-01'::timestamp + ((id % 100) || ' days')::interval, 66 id % 10, 67 id % 10, 68 'foo'::user_enum 69 FROM generate_series(1, 1000) id; 70INSERT INTO "S 1"."T 2" 71 SELECT id, 72 'AAA' || to_char(id, 'FM000') 73 FROM generate_series(1, 100) id; 74INSERT INTO "S 1"."T 3" 75 SELECT id, 76 id + 1, 77 'AAA' || to_char(id, 'FM000') 78 FROM generate_series(1, 100) id; 79DELETE FROM "S 1"."T 3" WHERE c1 % 2 != 0; -- delete for outer join tests 80INSERT INTO "S 1"."T 4" 81 SELECT id, 82 id + 1, 83 'AAA' || to_char(id, 'FM000') 84 FROM generate_series(1, 100) id; 85DELETE FROM "S 1"."T 4" WHERE c1 % 3 != 0; -- delete for outer join tests 86 87ANALYZE "S 1"."T 1"; 88ANALYZE "S 1"."T 2"; 89ANALYZE "S 1"."T 3"; 90ANALYZE "S 1"."T 4"; 91 92-- =================================================================== 93-- create foreign tables 94-- =================================================================== 95CREATE FOREIGN TABLE ft1 ( 96 c0 int, 97 c1 int NOT NULL, 98 c2 int NOT NULL, 99 c3 text, 100 c4 timestamptz, 101 c5 timestamp, 102 c6 varchar(10), 103 c7 char(10) default 'ft1', 104 c8 user_enum 105) SERVER loopback; 106ALTER FOREIGN TABLE ft1 DROP COLUMN c0; 107 108CREATE FOREIGN TABLE ft2 ( 109 c1 int NOT NULL, 110 c2 int NOT NULL, 111 cx int, 112 c3 text, 113 c4 timestamptz, 114 c5 timestamp, 115 c6 varchar(10), 116 c7 char(10) default 'ft2', 117 c8 user_enum 118) SERVER loopback; 119ALTER FOREIGN TABLE ft2 DROP COLUMN cx; 120 121CREATE FOREIGN TABLE ft4 ( 122 c1 int NOT NULL, 123 c2 int NOT NULL, 124 c3 text 125) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 3'); 126 127CREATE FOREIGN TABLE ft5 ( 128 c1 int NOT NULL, 129 c2 int NOT NULL, 130 c3 text 131) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 4'); 132 133CREATE FOREIGN TABLE ft6 ( 134 c1 int NOT NULL, 135 c2 int NOT NULL, 136 c3 text 137) SERVER loopback2 OPTIONS (schema_name 'S 1', table_name 'T 4'); 138 139-- =================================================================== 140-- tests for validator 141-- =================================================================== 142-- requiressl and some other parameters are omitted because 143-- valid values for them depend on configure options 144ALTER SERVER testserver1 OPTIONS ( 145 use_remote_estimate 'false', 146 updatable 'true', 147 fdw_startup_cost '123.456', 148 fdw_tuple_cost '0.123', 149 service 'value', 150 connect_timeout 'value', 151 dbname 'value', 152 host 'value', 153 hostaddr 'value', 154 port 'value', 155 --client_encoding 'value', 156 application_name 'value', 157 --fallback_application_name 'value', 158 keepalives 'value', 159 keepalives_idle 'value', 160 keepalives_interval 'value', 161 -- requiressl 'value', 162 sslcompression 'value', 163 sslmode 'value', 164 sslcert 'value', 165 sslkey 'value', 166 sslrootcert 'value', 167 sslcrl 'value', 168 --requirepeer 'value', 169 krbsrvname 'value', 170 gsslib 'value' 171 --replication 'value' 172); 173 174-- Error, invalid list syntax 175ALTER SERVER testserver1 OPTIONS (ADD extensions 'foo; bar'); 176 177-- OK but gets a warning 178ALTER SERVER testserver1 OPTIONS (ADD extensions 'foo, bar'); 179ALTER SERVER testserver1 OPTIONS (DROP extensions); 180 181ALTER USER MAPPING FOR public SERVER testserver1 182 OPTIONS (DROP user, DROP password); 183 184ALTER FOREIGN TABLE ft1 OPTIONS (schema_name 'S 1', table_name 'T 1'); 185ALTER FOREIGN TABLE ft2 OPTIONS (schema_name 'S 1', table_name 'T 1'); 186ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (column_name 'C 1'); 187ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1'); 188\det+ 189 190-- Test that alteration of server options causes reconnection 191-- Remote's errors might be non-English, so hide them to ensure stable results 192\set VERBOSITY terse 193SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work 194ALTER SERVER loopback OPTIONS (SET dbname 'no such database'); 195SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should fail 196DO $d$ 197 BEGIN 198 EXECUTE $$ALTER SERVER loopback 199 OPTIONS (SET dbname '$$||current_database()||$$')$$; 200 END; 201$d$; 202SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work again 203 204-- Test that alteration of user mapping options causes reconnection 205ALTER USER MAPPING FOR CURRENT_USER SERVER loopback 206 OPTIONS (ADD user 'no such user'); 207SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should fail 208ALTER USER MAPPING FOR CURRENT_USER SERVER loopback 209 OPTIONS (DROP user); 210SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work again 211\set VERBOSITY default 212 213-- Now we should be able to run ANALYZE. 214-- To exercise multiple code paths, we use local stats on ft1 215-- and remote-estimate mode on ft2. 216ANALYZE ft1; 217ALTER FOREIGN TABLE ft2 OPTIONS (use_remote_estimate 'true'); 218 219-- =================================================================== 220-- simple queries 221-- =================================================================== 222-- single table without alias 223EXPLAIN (COSTS OFF) SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10; 224SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10; 225-- single table with alias - also test that tableoid sort is not pushed to remote side 226EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tableoid OFFSET 100 LIMIT 10; 227SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tableoid OFFSET 100 LIMIT 10; 228-- whole-row reference 229EXPLAIN (VERBOSE, COSTS OFF) SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; 230SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; 231-- empty result 232SELECT * FROM ft1 WHERE false; 233-- with WHERE clause 234EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1'; 235SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1'; 236-- with FOR UPDATE/SHARE 237EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = 101 FOR UPDATE; 238SELECT * FROM ft1 t1 WHERE c1 = 101 FOR UPDATE; 239EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = 102 FOR SHARE; 240SELECT * FROM ft1 t1 WHERE c1 = 102 FOR SHARE; 241-- aggregate 242SELECT COUNT(*) FROM ft1 t1; 243-- subquery 244SELECT * FROM ft1 t1 WHERE t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 <= 10) ORDER BY c1; 245-- subquery+MAX 246SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1; 247-- used in CTE 248WITH 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; 249-- fixed values 250SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1; 251-- Test forcing the remote server to produce sorted data for a merge join. 252SET enable_hashjoin TO false; 253SET enable_nestloop TO false; 254-- inner join; expressions in the clauses appear in the equivalence class list 255EXPLAIN (VERBOSE, COSTS OFF) 256 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; 257SELECT t1.c1, t2."C 1" FROM ft2 t1 JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") OFFSET 100 LIMIT 10; 258-- outer join; expressions in the clauses do not appear in equivalence class 259-- list but no output change as compared to the previous query 260EXPLAIN (VERBOSE, COSTS OFF) 261 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; 262SELECT 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; 263-- A join between local table and foreign join. ORDER BY clause is added to the 264-- foreign join so that the local table can be joined using merge join strategy. 265EXPLAIN (VERBOSE, COSTS OFF) 266 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; 267SELECT 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; 268-- Test similar to above, except that the full join prevents any equivalence 269-- classes from being merged. This produces single relation equivalence classes 270-- included in join restrictions. 271EXPLAIN (VERBOSE, COSTS OFF) 272 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; 273SELECT 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; 274-- Test similar to above with all full outer joins 275EXPLAIN (VERBOSE, COSTS OFF) 276 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; 277SELECT 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; 278RESET enable_hashjoin; 279RESET enable_nestloop; 280 281-- =================================================================== 282-- WHERE with remotely-executable conditions 283-- =================================================================== 284EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 1; -- Var, OpExpr(b), Const 285EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2 = 0; -- BoolExpr 286EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NULL; -- NullTest 287EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL; -- NullTest 288EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr 289EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = -c1; -- OpExpr(l) 290EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE 1 = c1!; -- OpExpr(r) 291EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE (c1 IS NOT NULL) IS DISTINCT FROM (c1 IS NOT NULL); -- DistinctExpr 292EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1 + 0]); -- ScalarArrayOpExpr 293EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = (ARRAY[c1,c2,3])[1]; -- ArrayRef 294EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c6 = E'foo''s\\bar'; -- check special chars 295EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c8 = 'foo'; -- can't be sent to remote 296-- parameterized remote path for foreign table 297EXPLAIN (VERBOSE, COSTS OFF) 298 SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2; 299SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2; 300 301-- check both safe and unsafe join conditions 302EXPLAIN (VERBOSE, COSTS OFF) 303 SELECT * FROM ft2 a, ft2 b 304 WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7); 305SELECT * FROM ft2 a, ft2 b 306WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7); 307-- bug before 9.3.5 due to sloppy handling of remote-estimate parameters 308SELECT * FROM ft1 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft2 WHERE c1 < 5)); 309SELECT * FROM ft2 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft1 WHERE c1 < 5)); 310-- we should not push order by clause with volatile expressions or unsafe 311-- collations 312EXPLAIN (VERBOSE, COSTS OFF) 313 SELECT * FROM ft2 ORDER BY ft2.c1, random(); 314EXPLAIN (VERBOSE, COSTS OFF) 315 SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C"; 316 317-- user-defined operator/function 318CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$ 319BEGIN 320RETURN abs($1); 321END 322$$ LANGUAGE plpgsql IMMUTABLE; 323CREATE OPERATOR === ( 324 LEFTARG = int, 325 RIGHTARG = int, 326 PROCEDURE = int4eq, 327 COMMUTATOR = === 328); 329 330-- built-in operators and functions can be shipped for remote execution 331EXPLAIN (VERBOSE, COSTS OFF) 332 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = abs(t1.c2); 333SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = abs(t1.c2); 334EXPLAIN (VERBOSE, COSTS OFF) 335 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = t1.c2; 336SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = t1.c2; 337 338-- by default, user-defined ones cannot 339EXPLAIN (VERBOSE, COSTS OFF) 340 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2); 341SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2); 342EXPLAIN (VERBOSE, COSTS OFF) 343 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 344SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 345 346-- but let's put them in an extension ... 347ALTER EXTENSION postgres_fdw ADD FUNCTION postgres_fdw_abs(int); 348ALTER EXTENSION postgres_fdw ADD OPERATOR === (int, int); 349ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw'); 350 351-- ... now they can be shipped 352EXPLAIN (VERBOSE, COSTS OFF) 353 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2); 354SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2); 355EXPLAIN (VERBOSE, COSTS OFF) 356 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 357SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 358 359-- =================================================================== 360-- JOIN queries 361-- =================================================================== 362-- Analyze ft4 and ft5 so that we have better statistics. These tables do not 363-- have use_remote_estimate set. 364ANALYZE ft4; 365ANALYZE ft5; 366 367-- join two tables 368EXPLAIN (VERBOSE, COSTS OFF) 369SELECT 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; 370SELECT 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; 371-- join three tables 372EXPLAIN (VERBOSE, COSTS OFF) 373SELECT 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; 374SELECT 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; 375-- left outer join 376EXPLAIN (VERBOSE, COSTS OFF) 377SELECT 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; 378SELECT 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; 379-- left outer join three tables 380EXPLAIN (VERBOSE, COSTS OFF) 381SELECT 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; 382SELECT 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; 383-- left outer join + placement of clauses. 384-- clauses within the nullable side are not pulled up, but top level clause on 385-- non-nullable side is pushed into non-nullable side 386EXPLAIN (VERBOSE, COSTS OFF) 387SELECT 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; 388SELECT 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; 389-- clauses within the nullable side are not pulled up, but the top level clause 390-- on nullable side is not pushed down into nullable side 391EXPLAIN (VERBOSE, COSTS OFF) 392SELECT 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) 393 WHERE (t2.c1 < 10 OR t2.c1 IS NULL) AND t1.c1 < 10; 394SELECT 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) 395 WHERE (t2.c1 < 10 OR t2.c1 IS NULL) AND t1.c1 < 10; 396-- right outer join 397EXPLAIN (VERBOSE, COSTS OFF) 398SELECT 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; 399SELECT 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; 400-- right outer join three tables 401EXPLAIN (VERBOSE, COSTS OFF) 402SELECT 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; 403SELECT 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; 404-- full outer join 405EXPLAIN (VERBOSE, COSTS OFF) 406SELECT 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; 407SELECT 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; 408-- full outer join with restrictions on the joining relations 409EXPLAIN (VERBOSE, COSTS OFF) 410SELECT 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; 411SELECT 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; 412-- full outer join + inner join 413EXPLAIN (VERBOSE, COSTS OFF) 414SELECT 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; 415SELECT 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; 416-- full outer join three tables 417EXPLAIN (VERBOSE, COSTS OFF) 418SELECT 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; 419SELECT 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; 420-- full outer join + right outer join 421EXPLAIN (VERBOSE, COSTS OFF) 422SELECT 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; 423SELECT 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; 424-- right outer join + full outer join 425EXPLAIN (VERBOSE, COSTS OFF) 426SELECT 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; 427SELECT 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; 428-- full outer join + left outer join 429EXPLAIN (VERBOSE, COSTS OFF) 430SELECT 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; 431SELECT 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; 432-- left outer join + full outer join 433EXPLAIN (VERBOSE, COSTS OFF) 434SELECT 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; 435SELECT 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; 436-- right outer join + left outer join 437EXPLAIN (VERBOSE, COSTS OFF) 438SELECT 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; 439SELECT 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; 440-- left outer join + right outer join 441EXPLAIN (VERBOSE, COSTS OFF) 442SELECT 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; 443SELECT 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; 444-- full outer join + WHERE clause, only matched rows 445EXPLAIN (VERBOSE, COSTS OFF) 446SELECT 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; 447SELECT 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; 448-- full outer join + WHERE clause with shippable extensions set 449EXPLAIN (VERBOSE, COSTS OFF) 450SELECT 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; 451ALTER SERVER loopback OPTIONS (DROP extensions); 452-- full outer join + WHERE clause with shippable extensions not set 453EXPLAIN (VERBOSE, COSTS OFF) 454SELECT 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; 455ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw'); 456-- join two tables with FOR UPDATE clause 457-- tests whole-row reference for row marks 458EXPLAIN (VERBOSE, COSTS OFF) 459SELECT 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; 460SELECT 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; 461EXPLAIN (VERBOSE, COSTS OFF) 462SELECT 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; 463SELECT 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; 464-- join two tables with FOR SHARE clause 465EXPLAIN (VERBOSE, COSTS OFF) 466SELECT 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; 467SELECT 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; 468EXPLAIN (VERBOSE, COSTS OFF) 469SELECT 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; 470SELECT 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; 471-- join in CTE 472EXPLAIN (VERBOSE, COSTS OFF) 473WITH t (c1_1, c1_3, c2_1) AS (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; 474WITH t (c1_1, c1_3, c2_1) AS (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; 475-- ctid with whole-row reference 476EXPLAIN (VERBOSE, COSTS OFF) 477SELECT 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; 478-- SEMI JOIN, not pushed down 479EXPLAIN (VERBOSE, COSTS OFF) 480SELECT 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; 481SELECT 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; 482-- ANTI JOIN, not pushed down 483EXPLAIN (VERBOSE, COSTS OFF) 484SELECT 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; 485SELECT 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; 486-- CROSS JOIN, not pushed down 487EXPLAIN (VERBOSE, COSTS OFF) 488SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10; 489SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10; 490-- different server, not pushed down. No result expected. 491EXPLAIN (VERBOSE, COSTS OFF) 492SELECT 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; 493SELECT 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; 494-- unsafe join conditions (c8 has a UDT), not pushed down. Practically a CROSS 495-- JOIN since c8 in both tables has same value. 496EXPLAIN (VERBOSE, COSTS OFF) 497SELECT 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; 498SELECT 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; 499-- unsafe conditions on one side (c8 has a UDT), not pushed down. 500EXPLAIN (VERBOSE, COSTS OFF) 501SELECT 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; 502SELECT 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; 503-- join where unsafe to pushdown condition in WHERE clause has a column not 504-- in the SELECT clause. In this test unsafe clause needs to have column 505-- references from both joining sides so that the clause is not pushed down 506-- into one of the joining sides. 507EXPLAIN (VERBOSE, COSTS OFF) 508SELECT 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; 509SELECT 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; 510-- Aggregate after UNION, for testing setrefs 511EXPLAIN (VERBOSE, COSTS OFF) 512SELECT 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; 513SELECT 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; 514-- join with lateral reference 515EXPLAIN (VERBOSE, COSTS OFF) 516SELECT 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; 517SELECT 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; 518 519-- bug #15613: bad plan for foreign table scan with lateral reference 520EXPLAIN (VERBOSE, COSTS OFF) 521SELECT ref_0.c2, subq_1.* 522FROM 523 "S 1"."T 1" AS ref_0, 524 LATERAL ( 525 SELECT ref_0."C 1" c1, subq_0.* 526 FROM (SELECT ref_0.c2, ref_1.c3 527 FROM ft1 AS ref_1) AS subq_0 528 RIGHT JOIN ft2 AS ref_3 ON (subq_0.c3 = ref_3.c3) 529 ) AS subq_1 530WHERE ref_0."C 1" < 10 AND subq_1.c3 = '00001' 531ORDER BY ref_0."C 1"; 532 533SELECT ref_0.c2, subq_1.* 534FROM 535 "S 1"."T 1" AS ref_0, 536 LATERAL ( 537 SELECT ref_0."C 1" c1, subq_0.* 538 FROM (SELECT ref_0.c2, ref_1.c3 539 FROM ft1 AS ref_1) AS subq_0 540 RIGHT JOIN ft2 AS ref_3 ON (subq_0.c3 = ref_3.c3) 541 ) AS subq_1 542WHERE ref_0."C 1" < 10 AND subq_1.c3 = '00001' 543ORDER BY ref_0."C 1"; 544 545-- non-Var items in targetlist of the nullable rel of a join preventing 546-- push-down in some cases 547-- unable to push {ft1, ft2} 548EXPLAIN (VERBOSE, COSTS OFF) 549SELECT 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; 550SELECT 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; 551 552-- ok to push {ft1, ft2} but not {ft1, ft2, ft4} 553EXPLAIN (VERBOSE, COSTS OFF) 554SELECT 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; 555SELECT 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; 556 557-- join with nullable side with some columns with null values 558UPDATE ft5 SET c3 = null where c1 % 9 = 0; 559EXPLAIN (VERBOSE, COSTS OFF) 560SELECT 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; 561SELECT 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; 562 563-- multi-way join involving multiple merge joins 564-- (this case used to have EPQ-related planning problems) 565SET enable_nestloop TO false; 566SET enable_hashjoin TO false; 567EXPLAIN (VERBOSE, COSTS OFF) 568SELECT * FROM ft1, ft2, ft4, ft5 WHERE ft1.c1 = ft2.c1 AND ft1.c2 = ft4.c1 569 AND ft1.c2 = ft5.c1 AND ft1.c1 < 100 AND ft2.c1 < 100 FOR UPDATE; 570SELECT * FROM ft1, ft2, ft4, ft5 WHERE ft1.c1 = ft2.c1 AND ft1.c2 = ft4.c1 571 AND ft1.c2 = ft5.c1 AND ft1.c1 < 100 AND ft2.c1 < 100 FOR UPDATE; 572RESET enable_nestloop; 573RESET enable_hashjoin; 574 575-- check join pushdown in situations where multiple userids are involved 576CREATE ROLE regress_view_owner; 577CREATE USER MAPPING FOR regress_view_owner SERVER loopback; 578GRANT SELECT ON ft4 TO regress_view_owner; 579GRANT SELECT ON ft5 TO regress_view_owner; 580 581CREATE VIEW v4 AS SELECT * FROM ft4; 582CREATE VIEW v5 AS SELECT * FROM ft5; 583ALTER VIEW v5 OWNER TO regress_view_owner; 584EXPLAIN (VERBOSE, COSTS OFF) 585SELECT 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 586SELECT 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; 587ALTER VIEW v4 OWNER TO regress_view_owner; 588EXPLAIN (VERBOSE, COSTS OFF) 589SELECT 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 590SELECT 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; 591 592EXPLAIN (VERBOSE, COSTS OFF) 593SELECT 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 594SELECT 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; 595ALTER VIEW v4 OWNER TO CURRENT_USER; 596EXPLAIN (VERBOSE, COSTS OFF) 597SELECT 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 598SELECT 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; 599ALTER VIEW v4 OWNER TO regress_view_owner; 600 601-- cleanup 602DROP OWNED BY regress_view_owner; 603DROP ROLE regress_view_owner; 604 605-- =================================================================== 606-- parameterized queries 607-- =================================================================== 608-- simple join 609PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2; 610EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st1(1, 2); 611EXECUTE st1(1, 1); 612EXECUTE st1(101, 101); 613-- subquery using stable function (can't be sent to remote) 614PREPARE 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; 615EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st2(10, 20); 616EXECUTE st2(10, 20); 617EXECUTE st2(101, 121); 618-- subquery using immutable function (can be sent to remote) 619PREPARE 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; 620EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st3(10, 20); 621EXECUTE st3(10, 20); 622EXECUTE st3(20, 30); 623-- custom plan should be chosen initially 624PREPARE st4(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 = $1; 625EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 626EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 627EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 628EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 629EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 630-- once we try it enough times, should switch to generic plan 631EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 632-- value of $1 should not be sent to remote 633PREPARE st5(user_enum,int) AS SELECT * FROM ft1 t1 WHERE c8 = $1 and c1 = $2; 634EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 635EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 636EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 637EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 638EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 639EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 640EXECUTE st5('foo', 1); 641 642-- altering FDW options requires replanning 643PREPARE st6 AS SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2; 644EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6; 645PREPARE st7 AS INSERT INTO ft1 (c1,c2,c3) VALUES (1001,101,'foo'); 646EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7; 647ALTER TABLE "S 1"."T 1" RENAME TO "T 0"; 648ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 0'); 649EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6; 650EXECUTE st6; 651EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7; 652ALTER TABLE "S 1"."T 0" RENAME TO "T 1"; 653ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 1'); 654 655PREPARE st8 AS SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 656EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8; 657ALTER SERVER loopback OPTIONS (DROP extensions); 658EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8; 659EXECUTE st8; 660ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw'); 661 662-- cleanup 663DEALLOCATE st1; 664DEALLOCATE st2; 665DEALLOCATE st3; 666DEALLOCATE st4; 667DEALLOCATE st5; 668DEALLOCATE st6; 669DEALLOCATE st7; 670DEALLOCATE st8; 671 672-- System columns, except ctid, should not be sent to remote 673EXPLAIN (VERBOSE, COSTS OFF) 674SELECT * FROM ft1 t1 WHERE t1.tableoid = 'pg_class'::regclass LIMIT 1; 675SELECT * FROM ft1 t1 WHERE t1.tableoid = 'ft1'::regclass LIMIT 1; 676EXPLAIN (VERBOSE, COSTS OFF) 677SELECT tableoid::regclass, * FROM ft1 t1 LIMIT 1; 678SELECT tableoid::regclass, * FROM ft1 t1 LIMIT 1; 679EXPLAIN (VERBOSE, COSTS OFF) 680SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)'; 681SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)'; 682EXPLAIN (VERBOSE, COSTS OFF) 683SELECT ctid, * FROM ft1 t1 LIMIT 1; 684SELECT ctid, * FROM ft1 t1 LIMIT 1; 685 686-- =================================================================== 687-- used in pl/pgsql function 688-- =================================================================== 689CREATE OR REPLACE FUNCTION f_test(p_c1 int) RETURNS int AS $$ 690DECLARE 691 v_c1 int; 692BEGIN 693 SELECT c1 INTO v_c1 FROM ft1 WHERE c1 = p_c1 LIMIT 1; 694 PERFORM c1 FROM ft1 WHERE c1 = p_c1 AND p_c1 = v_c1 LIMIT 1; 695 RETURN v_c1; 696END; 697$$ LANGUAGE plpgsql; 698SELECT f_test(100); 699DROP FUNCTION f_test(int); 700 701-- =================================================================== 702-- conversion error 703-- =================================================================== 704ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE int; 705SELECT * FROM ft1 ftx(x1,x2,x3,x4,x5,x6,x7,x8) WHERE x1 = 1; -- ERROR 706SELECT ftx.x1, ft2.c2, ftx.x8 FROM ft1 ftx(x1,x2,x3,x4,x5,x6,x7,x8), ft2 707 WHERE ftx.x1 = ft2.c1 AND ftx.x1 = 1; -- ERROR 708SELECT ftx.x1, ft2.c2, ftx FROM ft1 ftx(x1,x2,x3,x4,x5,x6,x7,x8), ft2 709 WHERE ftx.x1 = ft2.c1 AND ftx.x1 = 1; -- ERROR 710ANALYZE ft1; -- ERROR 711ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum; 712 713-- =================================================================== 714-- subtransaction 715-- + local/remote error doesn't break cursor 716-- =================================================================== 717BEGIN; 718DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1; 719FETCH c; 720SAVEPOINT s; 721ERROR OUT; -- ERROR 722ROLLBACK TO s; 723FETCH c; 724SAVEPOINT s; 725SELECT * FROM ft1 WHERE 1 / (c1 - 1) > 0; -- ERROR 726ROLLBACK TO s; 727FETCH c; 728SELECT * FROM ft1 ORDER BY c1 LIMIT 1; 729COMMIT; 730 731-- =================================================================== 732-- test handling of collations 733-- =================================================================== 734create table loct3 (f1 text collate "C" unique, f2 text, f3 varchar(10) unique); 735create foreign table ft3 (f1 text collate "C", f2 text, f3 varchar(10)) 736 server loopback options (table_name 'loct3', use_remote_estimate 'true'); 737 738-- can be sent to remote 739explain (verbose, costs off) select * from ft3 where f1 = 'foo'; 740explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" = 'foo'; 741explain (verbose, costs off) select * from ft3 where f2 = 'foo'; 742explain (verbose, costs off) select * from ft3 where f3 = 'foo'; 743explain (verbose, costs off) select * from ft3 f, loct3 l 744 where f.f3 = l.f3 and l.f1 = 'foo'; 745-- can't be sent to remote 746explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" = 'foo'; 747explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C"; 748explain (verbose, costs off) select * from ft3 where f2 COLLATE "C" = 'foo'; 749explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C"; 750explain (verbose, costs off) select * from ft3 f, loct3 l 751 where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo'; 752 753-- =================================================================== 754-- test writable foreign table stuff 755-- =================================================================== 756EXPLAIN (verbose, costs off) 757INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20; 758INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20; 759INSERT INTO ft2 (c1,c2,c3) 760 VALUES (1101,201,'aaa'), (1102,202,'bbb'), (1103,203,'ccc') RETURNING *; 761INSERT INTO ft2 (c1,c2,c3) VALUES (1104,204,'ddd'), (1105,205,'eee'); 762EXPLAIN (verbose, costs off) 763UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3; -- can be pushed down 764UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3; 765EXPLAIN (verbose, costs off) 766UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *; -- can be pushed down 767UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *; 768EXPLAIN (verbose, costs off) 769UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT 770 FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; -- can't be pushed down 771UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT 772 FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; 773EXPLAIN (verbose, costs off) 774 DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4; -- can be pushed down 775DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4; 776EXPLAIN (verbose, costs off) 777DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; -- can't be pushed down 778DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; 779SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1; 780EXPLAIN (verbose, costs off) 781INSERT INTO ft2 (c1,c2,c3) VALUES (9999,999,'foo') RETURNING tableoid::regclass; 782INSERT INTO ft2 (c1,c2,c3) VALUES (9999,999,'foo') RETURNING tableoid::regclass; 783EXPLAIN (verbose, costs off) 784UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass; -- can be pushed down 785UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass; 786EXPLAIN (verbose, costs off) 787DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass; -- can be pushed down 788DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass; 789 790-- Test UPDATE with a MULTIEXPR sub-select 791-- (maybe someday this'll be remotely executable, but not today) 792EXPLAIN (verbose, costs off) 793UPDATE ft2 AS target SET (c2, c7) = ( 794 SELECT c2 * 10, c7 795 FROM ft2 AS src 796 WHERE target.c1 = src.c1 797) WHERE c1 > 1100; 798UPDATE ft2 AS target SET (c2, c7) = ( 799 SELECT c2 * 10, c7 800 FROM ft2 AS src 801 WHERE target.c1 = src.c1 802) WHERE c1 > 1100; 803 804UPDATE ft2 AS target SET (c2) = ( 805 SELECT c2 / 10 806 FROM ft2 AS src 807 WHERE target.c1 = src.c1 808) WHERE c1 > 1100; 809 810-- Test that trigger on remote table works as expected 811CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$ 812BEGIN 813 NEW.c3 = NEW.c3 || '_trig_update'; 814 RETURN NEW; 815END; 816$$ LANGUAGE plpgsql; 817CREATE TRIGGER t1_br_insert BEFORE INSERT OR UPDATE 818 ON "S 1"."T 1" FOR EACH ROW EXECUTE PROCEDURE "S 1".F_BRTRIG(); 819 820INSERT INTO ft2 (c1,c2,c3) VALUES (1208, 818, 'fff') RETURNING *; 821INSERT INTO ft2 (c1,c2,c3,c6) VALUES (1218, 818, 'ggg', '(--;') RETURNING *; 822UPDATE ft2 SET c2 = c2 + 600 WHERE c1 % 10 = 8 AND c1 < 1200 RETURNING *; 823 824-- Test errors thrown on remote side during update 825ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0); 826 827INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key 828INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT DO NOTHING; -- works 829INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO NOTHING; -- unsupported 830INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO UPDATE SET c3 = 'ffg'; -- unsupported 831INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive 832UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive 833 834-- Test savepoint/rollback behavior 835select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 836select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; 837begin; 838update ft2 set c2 = 42 where c2 = 0; 839select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 840savepoint s1; 841update ft2 set c2 = 44 where c2 = 4; 842select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 843release savepoint s1; 844select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 845savepoint s2; 846update ft2 set c2 = 46 where c2 = 6; 847select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 848rollback to savepoint s2; 849select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 850release savepoint s2; 851select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 852savepoint s3; 853update ft2 set c2 = -2 where c2 = 42 and c1 = 10; -- fail on remote side 854rollback to savepoint s3; 855select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 856release savepoint s3; 857select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 858-- none of the above is committed yet remotely 859select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; 860commit; 861select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 862select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; 863 864-- Above DMLs add data with c6 as NULL in ft1, so test ORDER BY NULLS LAST and NULLs 865-- FIRST behavior here. 866-- ORDER BY DESC NULLS LAST options 867EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 ORDER BY c6 DESC NULLS LAST, c1 OFFSET 795 LIMIT 10; 868SELECT * FROM ft1 ORDER BY c6 DESC NULLS LAST, c1 OFFSET 795 LIMIT 10; 869-- ORDER BY DESC NULLS FIRST options 870EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 ORDER BY c6 DESC NULLS FIRST, c1 OFFSET 15 LIMIT 10; 871SELECT * FROM ft1 ORDER BY c6 DESC NULLS FIRST, c1 OFFSET 15 LIMIT 10; 872-- ORDER BY ASC NULLS FIRST options 873EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10; 874SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10; 875 876-- =================================================================== 877-- test check constraints 878-- =================================================================== 879 880-- Consistent check constraints provide consistent results 881ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0); 882EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 < 0; 883SELECT count(*) FROM ft1 WHERE c2 < 0; 884SET constraint_exclusion = 'on'; 885EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 < 0; 886SELECT count(*) FROM ft1 WHERE c2 < 0; 887RESET constraint_exclusion; 888-- check constraint is enforced on the remote side, not locally 889INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive 890UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive 891ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2positive; 892 893-- But inconsistent check constraints provide inconsistent results 894ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2negative CHECK (c2 < 0); 895EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 >= 0; 896SELECT count(*) FROM ft1 WHERE c2 >= 0; 897SET constraint_exclusion = 'on'; 898EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 >= 0; 899SELECT count(*) FROM ft1 WHERE c2 >= 0; 900RESET constraint_exclusion; 901-- local check constraint is not actually enforced 902INSERT INTO ft1(c1, c2) VALUES(1111, 2); 903UPDATE ft1 SET c2 = c2 + 1 WHERE c1 = 1; 904ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative; 905 906-- =================================================================== 907-- test WITH CHECK OPTION constraints 908-- =================================================================== 909 910CREATE TABLE base_tbl (a int, b int); 911CREATE FOREIGN TABLE foreign_tbl (a int, b int) 912 SERVER loopback OPTIONS(table_name 'base_tbl'); 913CREATE VIEW rw_view AS SELECT * FROM foreign_tbl 914 WHERE a < b WITH CHECK OPTION; 915\d+ rw_view 916 917INSERT INTO rw_view VALUES (0, 10); -- ok 918INSERT INTO rw_view VALUES (10, 0); -- should fail 919EXPLAIN (VERBOSE, COSTS OFF) 920UPDATE rw_view SET b = 20 WHERE a = 0; -- not pushed down 921UPDATE rw_view SET b = 20 WHERE a = 0; -- ok 922EXPLAIN (VERBOSE, COSTS OFF) 923UPDATE rw_view SET b = -20 WHERE a = 0; -- not pushed down 924UPDATE rw_view SET b = -20 WHERE a = 0; -- should fail 925SELECT * FROM foreign_tbl; 926 927DROP FOREIGN TABLE foreign_tbl CASCADE; 928DROP TABLE base_tbl; 929 930-- =================================================================== 931-- test serial columns (ie, sequence-based defaults) 932-- =================================================================== 933create table loc1 (f1 serial, f2 text); 934create foreign table rem1 (f1 serial, f2 text) 935 server loopback options(table_name 'loc1'); 936select pg_catalog.setval('rem1_f1_seq', 10, false); 937insert into loc1(f2) values('hi'); 938insert into rem1(f2) values('hi remote'); 939insert into loc1(f2) values('bye'); 940insert into rem1(f2) values('bye remote'); 941select * from loc1; 942select * from rem1; 943 944-- =================================================================== 945-- test local triggers 946-- =================================================================== 947 948-- Trigger functions "borrowed" from triggers regress test. 949CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS $$ 950BEGIN 951 RAISE NOTICE 'trigger_func(%) called: action = %, when = %, level = %', 952 TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL; 953 RETURN NULL; 954END;$$; 955 956CREATE TRIGGER trig_stmt_before BEFORE DELETE OR INSERT OR UPDATE ON rem1 957 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); 958CREATE TRIGGER trig_stmt_after AFTER DELETE OR INSERT OR UPDATE ON rem1 959 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); 960 961CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger 962LANGUAGE plpgsql AS $$ 963 964declare 965 oldnew text[]; 966 relid text; 967 argstr text; 968begin 969 970 relid := TG_relid::regclass; 971 argstr := ''; 972 for i in 0 .. TG_nargs - 1 loop 973 if i > 0 then 974 argstr := argstr || ', '; 975 end if; 976 argstr := argstr || TG_argv[i]; 977 end loop; 978 979 RAISE NOTICE '%(%) % % % ON %', 980 tg_name, argstr, TG_when, TG_level, TG_OP, relid; 981 oldnew := '{}'::text[]; 982 if TG_OP != 'INSERT' then 983 oldnew := array_append(oldnew, format('OLD: %s', OLD)); 984 end if; 985 986 if TG_OP != 'DELETE' then 987 oldnew := array_append(oldnew, format('NEW: %s', NEW)); 988 end if; 989 990 RAISE NOTICE '%', array_to_string(oldnew, ','); 991 992 if TG_OP = 'DELETE' then 993 return OLD; 994 else 995 return NEW; 996 end if; 997end; 998$$; 999 1000-- Test basic functionality 1001CREATE TRIGGER trig_row_before 1002BEFORE INSERT OR UPDATE OR DELETE ON rem1 1003FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1004 1005CREATE TRIGGER trig_row_after 1006AFTER INSERT OR UPDATE OR DELETE ON rem1 1007FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1008 1009delete from rem1; 1010insert into rem1 values(1,'insert'); 1011update rem1 set f2 = 'update' where f1 = 1; 1012update rem1 set f2 = f2 || f2; 1013 1014 1015-- cleanup 1016DROP TRIGGER trig_row_before ON rem1; 1017DROP TRIGGER trig_row_after ON rem1; 1018DROP TRIGGER trig_stmt_before ON rem1; 1019DROP TRIGGER trig_stmt_after ON rem1; 1020 1021DELETE from rem1; 1022 1023 1024-- Test WHEN conditions 1025 1026CREATE TRIGGER trig_row_before_insupd 1027BEFORE INSERT OR UPDATE ON rem1 1028FOR EACH ROW 1029WHEN (NEW.f2 like '%update%') 1030EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1031 1032CREATE TRIGGER trig_row_after_insupd 1033AFTER INSERT OR UPDATE ON rem1 1034FOR EACH ROW 1035WHEN (NEW.f2 like '%update%') 1036EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1037 1038-- Insert or update not matching: nothing happens 1039INSERT INTO rem1 values(1, 'insert'); 1040UPDATE rem1 set f2 = 'test'; 1041 1042-- Insert or update matching: triggers are fired 1043INSERT INTO rem1 values(2, 'update'); 1044UPDATE rem1 set f2 = 'update update' where f1 = '2'; 1045 1046CREATE TRIGGER trig_row_before_delete 1047BEFORE DELETE ON rem1 1048FOR EACH ROW 1049WHEN (OLD.f2 like '%update%') 1050EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1051 1052CREATE TRIGGER trig_row_after_delete 1053AFTER DELETE ON rem1 1054FOR EACH ROW 1055WHEN (OLD.f2 like '%update%') 1056EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1057 1058-- Trigger is fired for f1=2, not for f1=1 1059DELETE FROM rem1; 1060 1061-- cleanup 1062DROP TRIGGER trig_row_before_insupd ON rem1; 1063DROP TRIGGER trig_row_after_insupd ON rem1; 1064DROP TRIGGER trig_row_before_delete ON rem1; 1065DROP TRIGGER trig_row_after_delete ON rem1; 1066 1067 1068-- Test various RETURN statements in BEFORE triggers. 1069 1070CREATE FUNCTION trig_row_before_insupdate() RETURNS TRIGGER AS $$ 1071 BEGIN 1072 NEW.f2 := NEW.f2 || ' triggered !'; 1073 RETURN NEW; 1074 END 1075$$ language plpgsql; 1076 1077CREATE TRIGGER trig_row_before_insupd 1078BEFORE INSERT OR UPDATE ON rem1 1079FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate(); 1080 1081-- The new values should have 'triggered' appended 1082INSERT INTO rem1 values(1, 'insert'); 1083SELECT * from loc1; 1084INSERT INTO rem1 values(2, 'insert') RETURNING f2; 1085SELECT * from loc1; 1086UPDATE rem1 set f2 = ''; 1087SELECT * from loc1; 1088UPDATE rem1 set f2 = 'skidoo' RETURNING f2; 1089SELECT * from loc1; 1090 1091EXPLAIN (verbose, costs off) 1092UPDATE rem1 set f1 = 10; -- all columns should be transmitted 1093UPDATE rem1 set f1 = 10; 1094SELECT * from loc1; 1095 1096DELETE FROM rem1; 1097 1098-- Add a second trigger, to check that the changes are propagated correctly 1099-- from trigger to trigger 1100CREATE TRIGGER trig_row_before_insupd2 1101BEFORE INSERT OR UPDATE ON rem1 1102FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate(); 1103 1104INSERT INTO rem1 values(1, 'insert'); 1105SELECT * from loc1; 1106INSERT INTO rem1 values(2, 'insert') RETURNING f2; 1107SELECT * from loc1; 1108UPDATE rem1 set f2 = ''; 1109SELECT * from loc1; 1110UPDATE rem1 set f2 = 'skidoo' RETURNING f2; 1111SELECT * from loc1; 1112 1113DROP TRIGGER trig_row_before_insupd ON rem1; 1114DROP TRIGGER trig_row_before_insupd2 ON rem1; 1115 1116DELETE from rem1; 1117 1118INSERT INTO rem1 VALUES (1, 'test'); 1119 1120-- Test with a trigger returning NULL 1121CREATE FUNCTION trig_null() RETURNS TRIGGER AS $$ 1122 BEGIN 1123 RETURN NULL; 1124 END 1125$$ language plpgsql; 1126 1127CREATE TRIGGER trig_null 1128BEFORE INSERT OR UPDATE OR DELETE ON rem1 1129FOR EACH ROW EXECUTE PROCEDURE trig_null(); 1130 1131-- Nothing should have changed. 1132INSERT INTO rem1 VALUES (2, 'test2'); 1133 1134SELECT * from loc1; 1135 1136UPDATE rem1 SET f2 = 'test2'; 1137 1138SELECT * from loc1; 1139 1140DELETE from rem1; 1141 1142SELECT * from loc1; 1143 1144DROP TRIGGER trig_null ON rem1; 1145DELETE from rem1; 1146 1147-- Test a combination of local and remote triggers 1148CREATE TRIGGER trig_row_before 1149BEFORE INSERT OR UPDATE OR DELETE ON rem1 1150FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1151 1152CREATE TRIGGER trig_row_after 1153AFTER INSERT OR UPDATE OR DELETE ON rem1 1154FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1155 1156CREATE TRIGGER trig_local_before BEFORE INSERT OR UPDATE ON loc1 1157FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate(); 1158 1159INSERT INTO rem1(f2) VALUES ('test'); 1160UPDATE rem1 SET f2 = 'testo'; 1161 1162-- Test returning a system attribute 1163INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid; 1164 1165-- cleanup 1166DROP TRIGGER trig_row_before ON rem1; 1167DROP TRIGGER trig_row_after ON rem1; 1168DROP TRIGGER trig_local_before ON loc1; 1169 1170 1171-- Test direct foreign table modification functionality 1172 1173-- Test with statement-level triggers 1174CREATE TRIGGER trig_stmt_before 1175 BEFORE DELETE OR INSERT OR UPDATE ON rem1 1176 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); 1177EXPLAIN (verbose, costs off) 1178UPDATE rem1 set f2 = ''; -- can be pushed down 1179EXPLAIN (verbose, costs off) 1180DELETE FROM rem1; -- can be pushed down 1181DROP TRIGGER trig_stmt_before ON rem1; 1182 1183CREATE TRIGGER trig_stmt_after 1184 AFTER DELETE OR INSERT OR UPDATE ON rem1 1185 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); 1186EXPLAIN (verbose, costs off) 1187UPDATE rem1 set f2 = ''; -- can be pushed down 1188EXPLAIN (verbose, costs off) 1189DELETE FROM rem1; -- can be pushed down 1190DROP TRIGGER trig_stmt_after ON rem1; 1191 1192-- Test with row-level ON INSERT triggers 1193CREATE TRIGGER trig_row_before_insert 1194BEFORE INSERT ON rem1 1195FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1196EXPLAIN (verbose, costs off) 1197UPDATE rem1 set f2 = ''; -- can be pushed down 1198EXPLAIN (verbose, costs off) 1199DELETE FROM rem1; -- can be pushed down 1200DROP TRIGGER trig_row_before_insert ON rem1; 1201 1202CREATE TRIGGER trig_row_after_insert 1203AFTER INSERT ON rem1 1204FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1205EXPLAIN (verbose, costs off) 1206UPDATE rem1 set f2 = ''; -- can be pushed down 1207EXPLAIN (verbose, costs off) 1208DELETE FROM rem1; -- can be pushed down 1209DROP TRIGGER trig_row_after_insert ON rem1; 1210 1211-- Test with row-level ON UPDATE triggers 1212CREATE TRIGGER trig_row_before_update 1213BEFORE UPDATE ON rem1 1214FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1215EXPLAIN (verbose, costs off) 1216UPDATE rem1 set f2 = ''; -- can't be pushed down 1217EXPLAIN (verbose, costs off) 1218DELETE FROM rem1; -- can be pushed down 1219DROP TRIGGER trig_row_before_update ON rem1; 1220 1221CREATE TRIGGER trig_row_after_update 1222AFTER UPDATE ON rem1 1223FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1224EXPLAIN (verbose, costs off) 1225UPDATE rem1 set f2 = ''; -- can't be pushed down 1226EXPLAIN (verbose, costs off) 1227DELETE FROM rem1; -- can be pushed down 1228DROP TRIGGER trig_row_after_update ON rem1; 1229 1230-- Test with row-level ON DELETE triggers 1231CREATE TRIGGER trig_row_before_delete 1232BEFORE DELETE ON rem1 1233FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1234EXPLAIN (verbose, costs off) 1235UPDATE rem1 set f2 = ''; -- can be pushed down 1236EXPLAIN (verbose, costs off) 1237DELETE FROM rem1; -- can't be pushed down 1238DROP TRIGGER trig_row_before_delete ON rem1; 1239 1240CREATE TRIGGER trig_row_after_delete 1241AFTER DELETE ON rem1 1242FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1243EXPLAIN (verbose, costs off) 1244UPDATE rem1 set f2 = ''; -- can be pushed down 1245EXPLAIN (verbose, costs off) 1246DELETE FROM rem1; -- can't be pushed down 1247DROP TRIGGER trig_row_after_delete ON rem1; 1248 1249-- =================================================================== 1250-- test inheritance features 1251-- =================================================================== 1252 1253CREATE TABLE a (aa TEXT); 1254CREATE TABLE loct (aa TEXT, bb TEXT); 1255CREATE FOREIGN TABLE b (bb TEXT) INHERITS (a) 1256 SERVER loopback OPTIONS (table_name 'loct'); 1257 1258INSERT INTO a(aa) VALUES('aaa'); 1259INSERT INTO a(aa) VALUES('aaaa'); 1260INSERT INTO a(aa) VALUES('aaaaa'); 1261 1262INSERT INTO b(aa) VALUES('bbb'); 1263INSERT INTO b(aa) VALUES('bbbb'); 1264INSERT INTO b(aa) VALUES('bbbbb'); 1265 1266SELECT tableoid::regclass, * FROM a; 1267SELECT tableoid::regclass, * FROM b; 1268SELECT tableoid::regclass, * FROM ONLY a; 1269 1270UPDATE a SET aa = 'zzzzzz' WHERE aa LIKE 'aaaa%'; 1271 1272SELECT tableoid::regclass, * FROM a; 1273SELECT tableoid::regclass, * FROM b; 1274SELECT tableoid::regclass, * FROM ONLY a; 1275 1276UPDATE b SET aa = 'new'; 1277 1278SELECT tableoid::regclass, * FROM a; 1279SELECT tableoid::regclass, * FROM b; 1280SELECT tableoid::regclass, * FROM ONLY a; 1281 1282UPDATE a SET aa = 'newtoo'; 1283 1284SELECT tableoid::regclass, * FROM a; 1285SELECT tableoid::regclass, * FROM b; 1286SELECT tableoid::regclass, * FROM ONLY a; 1287 1288DELETE FROM a; 1289 1290SELECT tableoid::regclass, * FROM a; 1291SELECT tableoid::regclass, * FROM b; 1292SELECT tableoid::regclass, * FROM ONLY a; 1293 1294DROP TABLE a CASCADE; 1295DROP TABLE loct; 1296 1297-- Check SELECT FOR UPDATE/SHARE with an inherited source table 1298create table loct1 (f1 int, f2 int, f3 int); 1299create table loct2 (f1 int, f2 int, f3 int); 1300 1301create table foo (f1 int, f2 int); 1302create foreign table foo2 (f3 int) inherits (foo) 1303 server loopback options (table_name 'loct1'); 1304create table bar (f1 int, f2 int); 1305create foreign table bar2 (f3 int) inherits (bar) 1306 server loopback options (table_name 'loct2'); 1307 1308insert into foo values(1,1); 1309insert into foo values(3,3); 1310insert into foo2 values(2,2,2); 1311insert into foo2 values(4,4,4); 1312insert into bar values(1,11); 1313insert into bar values(2,22); 1314insert into bar values(6,66); 1315insert into bar2 values(3,33,33); 1316insert into bar2 values(4,44,44); 1317insert into bar2 values(7,77,77); 1318 1319explain (verbose, costs off) 1320select * from bar where f1 in (select f1 from foo) for update; 1321select * from bar where f1 in (select f1 from foo) for update; 1322 1323explain (verbose, costs off) 1324select * from bar where f1 in (select f1 from foo) for share; 1325select * from bar where f1 in (select f1 from foo) for share; 1326 1327-- Check UPDATE with inherited target and an inherited source table 1328explain (verbose, costs off) 1329update bar set f2 = f2 + 100 where f1 in (select f1 from foo); 1330update bar set f2 = f2 + 100 where f1 in (select f1 from foo); 1331 1332select tableoid::regclass, * from bar order by 1,2; 1333 1334-- Check UPDATE with inherited target and an appendrel subquery 1335explain (verbose, costs off) 1336update bar set f2 = f2 + 100 1337from 1338 ( select f1 from foo union all select f1+3 from foo ) ss 1339where bar.f1 = ss.f1; 1340update bar set f2 = f2 + 100 1341from 1342 ( select f1 from foo union all select f1+3 from foo ) ss 1343where bar.f1 = ss.f1; 1344 1345select tableoid::regclass, * from bar order by 1,2; 1346 1347-- Test forcing the remote server to produce sorted data for a merge join, 1348-- but the foreign table is an inheritance child. 1349truncate table loct1; 1350truncate table only foo; 1351\set num_rows_foo 2000 1352insert into loct1 select generate_series(0, :num_rows_foo, 2), generate_series(0, :num_rows_foo, 2), generate_series(0, :num_rows_foo, 2); 1353insert into foo select generate_series(1, :num_rows_foo, 2), generate_series(1, :num_rows_foo, 2); 1354SET enable_hashjoin to false; 1355SET enable_nestloop to false; 1356alter foreign table foo2 options (use_remote_estimate 'true'); 1357create index i_loct1_f1 on loct1(f1); 1358create index i_foo_f1 on foo(f1); 1359analyze foo; 1360analyze loct1; 1361-- inner join; expressions in the clauses appear in the equivalence class list 1362explain (verbose, costs off) 1363 select foo.f1, loct1.f1 from foo join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10; 1364select foo.f1, loct1.f1 from foo join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10; 1365-- outer join; expressions in the clauses do not appear in equivalence class 1366-- list but no output change as compared to the previous query 1367explain (verbose, costs off) 1368 select foo.f1, loct1.f1 from foo left join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10; 1369select foo.f1, loct1.f1 from foo left join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10; 1370RESET enable_hashjoin; 1371RESET enable_nestloop; 1372 1373-- Test that WHERE CURRENT OF is not supported 1374begin; 1375declare c cursor for select * from bar where f1 = 7; 1376fetch from c; 1377update bar set f2 = null where current of c; 1378rollback; 1379 1380explain (verbose, costs off) 1381delete from foo where f1 < 5 returning *; 1382delete from foo where f1 < 5 returning *; 1383explain (verbose, costs off) 1384update bar set f2 = f2 + 100 returning *; 1385update bar set f2 = f2 + 100 returning *; 1386 1387-- Test that UPDATE/DELETE with inherited target works with row-level triggers 1388CREATE TRIGGER trig_row_before 1389BEFORE UPDATE OR DELETE ON bar2 1390FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1391 1392CREATE TRIGGER trig_row_after 1393AFTER UPDATE OR DELETE ON bar2 1394FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1395 1396explain (verbose, costs off) 1397update bar set f2 = f2 + 100; 1398update bar set f2 = f2 + 100; 1399 1400explain (verbose, costs off) 1401delete from bar where f2 < 400; 1402delete from bar where f2 < 400; 1403 1404-- cleanup 1405drop table foo cascade; 1406drop table bar cascade; 1407drop table loct1; 1408drop table loct2; 1409 1410-- =================================================================== 1411-- test IMPORT FOREIGN SCHEMA 1412-- =================================================================== 1413 1414CREATE SCHEMA import_source; 1415CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL); 1416CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL, c3 text collate "POSIX"); 1417CREATE TYPE typ1 AS (m1 int, m2 varchar); 1418CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1); 1419CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42)); 1420CREATE TABLE import_source."x 5" (c1 float8); 1421ALTER TABLE import_source."x 5" DROP COLUMN c1; 1422 1423CREATE SCHEMA import_dest1; 1424IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1; 1425\det+ import_dest1.* 1426\d import_dest1.* 1427 1428-- Options 1429CREATE SCHEMA import_dest2; 1430IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2 1431 OPTIONS (import_default 'true'); 1432\det+ import_dest2.* 1433\d import_dest2.* 1434CREATE SCHEMA import_dest3; 1435IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3 1436 OPTIONS (import_collate 'false', import_not_null 'false'); 1437\det+ import_dest3.* 1438\d import_dest3.* 1439 1440-- Check LIMIT TO and EXCEPT 1441CREATE SCHEMA import_dest4; 1442IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch) 1443 FROM SERVER loopback INTO import_dest4; 1444\det+ import_dest4.* 1445IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch) 1446 FROM SERVER loopback INTO import_dest4; 1447\det+ import_dest4.* 1448 1449-- Assorted error cases 1450IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest4; 1451IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO import_dest4; 1452IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO notthere; 1453IMPORT FOREIGN SCHEMA nonesuch FROM SERVER nowhere INTO notthere; 1454 1455-- Check case of a type present only on the remote server. 1456-- We can fake this by dropping the type locally in our transaction. 1457CREATE TYPE "Colors" AS ENUM ('red', 'green', 'blue'); 1458CREATE TABLE import_source.t5 (c1 int, c2 text collate "C", "Col" "Colors"); 1459 1460CREATE SCHEMA import_dest5; 1461BEGIN; 1462DROP TYPE "Colors" CASCADE; 1463IMPORT FOREIGN SCHEMA import_source LIMIT TO (t5) 1464 FROM SERVER loopback INTO import_dest5; -- ERROR 1465 1466ROLLBACK; 1467 1468BEGIN; 1469 1470 1471CREATE SERVER fetch101 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( fetch_size '101' ); 1472 1473SELECT count(*) 1474FROM pg_foreign_server 1475WHERE srvname = 'fetch101' 1476AND srvoptions @> array['fetch_size=101']; 1477 1478ALTER SERVER fetch101 OPTIONS( SET fetch_size '202' ); 1479 1480SELECT count(*) 1481FROM pg_foreign_server 1482WHERE srvname = 'fetch101' 1483AND srvoptions @> array['fetch_size=101']; 1484 1485SELECT count(*) 1486FROM pg_foreign_server 1487WHERE srvname = 'fetch101' 1488AND srvoptions @> array['fetch_size=202']; 1489 1490CREATE FOREIGN TABLE table30000 ( x int ) SERVER fetch101 OPTIONS ( fetch_size '30000' ); 1491 1492SELECT COUNT(*) 1493FROM pg_foreign_table 1494WHERE ftrelid = 'table30000'::regclass 1495AND ftoptions @> array['fetch_size=30000']; 1496 1497ALTER FOREIGN TABLE table30000 OPTIONS ( SET fetch_size '60000'); 1498 1499SELECT COUNT(*) 1500FROM pg_foreign_table 1501WHERE ftrelid = 'table30000'::regclass 1502AND ftoptions @> array['fetch_size=30000']; 1503 1504SELECT COUNT(*) 1505FROM pg_foreign_table 1506WHERE ftrelid = 'table30000'::regclass 1507AND ftoptions @> array['fetch_size=60000']; 1508 1509ROLLBACK; 1510 1511-- =================================================================== 1512-- test connection invalidation cases 1513-- =================================================================== 1514-- This test case is for closing the connection in pgfdw_xact_callback 1515BEGIN; 1516-- Connection xact depth becomes 1 i.e. the connection is in midst of the xact. 1517SELECT 1 FROM ft1 LIMIT 1; 1518-- Connection is not closed at the end of the alter statement in 1519-- pgfdw_inval_callback. That's because the connection is in midst of this 1520-- xact, it is just marked as invalid. 1521ALTER SERVER loopback OPTIONS (ADD use_remote_estimate 'off'); 1522-- The invalid connection gets closed in pgfdw_xact_callback during commit. 1523COMMIT; 1524