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-- A table with oids. CREATE FOREIGN TABLE doesn't support the 140-- WITH OIDS option, but ALTER does. 141CREATE FOREIGN TABLE ft_pg_type ( 142 typname name, 143 typlen smallint 144) SERVER loopback OPTIONS (schema_name 'pg_catalog', table_name 'pg_type'); 145ALTER TABLE ft_pg_type SET WITH OIDS; 146 147-- =================================================================== 148-- tests for validator 149-- =================================================================== 150-- requiressl and some other parameters are omitted because 151-- valid values for them depend on configure options 152ALTER SERVER testserver1 OPTIONS ( 153 use_remote_estimate 'false', 154 updatable 'true', 155 fdw_startup_cost '123.456', 156 fdw_tuple_cost '0.123', 157 service 'value', 158 connect_timeout 'value', 159 dbname 'value', 160 host 'value', 161 hostaddr 'value', 162 port 'value', 163 --client_encoding 'value', 164 application_name 'value', 165 --fallback_application_name 'value', 166 keepalives 'value', 167 keepalives_idle 'value', 168 keepalives_interval 'value', 169 -- requiressl 'value', 170 sslcompression 'value', 171 sslmode 'value', 172 sslcert 'value', 173 sslkey 'value', 174 sslrootcert 'value', 175 sslcrl 'value', 176 --requirepeer 'value', 177 krbsrvname 'value', 178 gsslib 'value' 179 --replication 'value' 180); 181 182-- Error, invalid list syntax 183ALTER SERVER testserver1 OPTIONS (ADD extensions 'foo; bar'); 184 185-- OK but gets a warning 186ALTER SERVER testserver1 OPTIONS (ADD extensions 'foo, bar'); 187ALTER SERVER testserver1 OPTIONS (DROP extensions); 188 189ALTER USER MAPPING FOR public SERVER testserver1 190 OPTIONS (DROP user, DROP password); 191 192ALTER FOREIGN TABLE ft1 OPTIONS (schema_name 'S 1', table_name 'T 1'); 193ALTER FOREIGN TABLE ft2 OPTIONS (schema_name 'S 1', table_name 'T 1'); 194ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (column_name 'C 1'); 195ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1'); 196\det+ 197 198-- Test that alteration of server options causes reconnection 199-- Remote's errors might be non-English, so hide them to ensure stable results 200\set VERBOSITY terse 201SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work 202ALTER SERVER loopback OPTIONS (SET dbname 'no such database'); 203SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should fail 204DO $d$ 205 BEGIN 206 EXECUTE $$ALTER SERVER loopback 207 OPTIONS (SET dbname '$$||current_database()||$$')$$; 208 END; 209$d$; 210SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work again 211 212-- Test that alteration of user mapping options causes reconnection 213ALTER USER MAPPING FOR CURRENT_USER SERVER loopback 214 OPTIONS (ADD user 'no such user'); 215SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should fail 216ALTER USER MAPPING FOR CURRENT_USER SERVER loopback 217 OPTIONS (DROP user); 218SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work again 219\set VERBOSITY default 220 221-- Now we should be able to run ANALYZE. 222-- To exercise multiple code paths, we use local stats on ft1 223-- and remote-estimate mode on ft2. 224ANALYZE ft1; 225ALTER FOREIGN TABLE ft2 OPTIONS (use_remote_estimate 'true'); 226 227-- =================================================================== 228-- simple queries 229-- =================================================================== 230-- single table without alias 231EXPLAIN (COSTS OFF) SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10; 232SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10; 233-- single table with alias - also test that tableoid sort is not pushed to remote side 234EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tableoid OFFSET 100 LIMIT 10; 235SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tableoid OFFSET 100 LIMIT 10; 236-- whole-row reference 237EXPLAIN (VERBOSE, COSTS OFF) SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; 238SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; 239-- empty result 240SELECT * FROM ft1 WHERE false; 241-- with WHERE clause 242EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1'; 243SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1'; 244-- with FOR UPDATE/SHARE 245EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = 101 FOR UPDATE; 246SELECT * FROM ft1 t1 WHERE c1 = 101 FOR UPDATE; 247EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = 102 FOR SHARE; 248SELECT * FROM ft1 t1 WHERE c1 = 102 FOR SHARE; 249-- aggregate 250SELECT COUNT(*) FROM ft1 t1; 251-- subquery 252SELECT * FROM ft1 t1 WHERE t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 <= 10) ORDER BY c1; 253-- subquery+MAX 254SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1; 255-- used in CTE 256WITH 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; 257-- fixed values 258SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1; 259-- Test forcing the remote server to produce sorted data for a merge join. 260SET enable_hashjoin TO false; 261SET enable_nestloop TO false; 262-- inner join; expressions in the clauses appear in the equivalence class list 263EXPLAIN (VERBOSE, COSTS OFF) 264 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; 265SELECT t1.c1, t2."C 1" FROM ft2 t1 JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") OFFSET 100 LIMIT 10; 266-- outer join; expressions in the clauses do not appear in equivalence class 267-- list but no output change as compared to the previous query 268EXPLAIN (VERBOSE, COSTS OFF) 269 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; 270SELECT 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; 271-- A join between local table and foreign join. ORDER BY clause is added to the 272-- foreign join so that the local table can be joined using merge join strategy. 273EXPLAIN (VERBOSE, COSTS OFF) 274 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; 275SELECT 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; 276-- Test similar to above, except that the full join prevents any equivalence 277-- classes from being merged. This produces single relation equivalence classes 278-- included in join restrictions. 279EXPLAIN (VERBOSE, COSTS OFF) 280 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; 281SELECT 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; 282-- Test similar to above with all full outer joins 283EXPLAIN (VERBOSE, COSTS OFF) 284 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; 285SELECT 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; 286RESET enable_hashjoin; 287RESET enable_nestloop; 288 289-- =================================================================== 290-- WHERE with remotely-executable conditions 291-- =================================================================== 292EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 1; -- Var, OpExpr(b), Const 293EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2 = 0; -- BoolExpr 294EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NULL; -- NullTest 295EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL; -- NullTest 296EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr 297EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = -c1; -- OpExpr(l) 298EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE 1 = c1!; -- OpExpr(r) 299EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE (c1 IS NOT NULL) IS DISTINCT FROM (c1 IS NOT NULL); -- DistinctExpr 300EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1 + 0]); -- ScalarArrayOpExpr 301EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = (ARRAY[c1,c2,3])[1]; -- ArrayRef 302EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c6 = E'foo''s\\bar'; -- check special chars 303EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c8 = 'foo'; -- can't be sent to remote 304-- parameterized remote path for foreign table 305EXPLAIN (VERBOSE, COSTS OFF) 306 SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2; 307SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2; 308 309-- check both safe and unsafe join conditions 310EXPLAIN (VERBOSE, COSTS OFF) 311 SELECT * FROM ft2 a, ft2 b 312 WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7); 313SELECT * FROM ft2 a, ft2 b 314WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7); 315-- bug before 9.3.5 due to sloppy handling of remote-estimate parameters 316SELECT * FROM ft1 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft2 WHERE c1 < 5)); 317SELECT * FROM ft2 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft1 WHERE c1 < 5)); 318-- we should not push order by clause with volatile expressions or unsafe 319-- collations 320EXPLAIN (VERBOSE, COSTS OFF) 321 SELECT * FROM ft2 ORDER BY ft2.c1, random(); 322EXPLAIN (VERBOSE, COSTS OFF) 323 SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C"; 324 325-- user-defined operator/function 326CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$ 327BEGIN 328RETURN abs($1); 329END 330$$ LANGUAGE plpgsql IMMUTABLE; 331CREATE OPERATOR === ( 332 LEFTARG = int, 333 RIGHTARG = int, 334 PROCEDURE = int4eq, 335 COMMUTATOR = === 336); 337 338-- built-in operators and functions can be shipped for remote execution 339EXPLAIN (VERBOSE, COSTS OFF) 340 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = abs(t1.c2); 341SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = 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-- by default, user-defined ones cannot 347EXPLAIN (VERBOSE, COSTS OFF) 348 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2); 349SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2); 350EXPLAIN (VERBOSE, COSTS OFF) 351 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 352SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 353 354-- but let's put them in an extension ... 355ALTER EXTENSION postgres_fdw ADD FUNCTION postgres_fdw_abs(int); 356ALTER EXTENSION postgres_fdw ADD OPERATOR === (int, int); 357ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw'); 358 359-- ... now they can be shipped 360EXPLAIN (VERBOSE, COSTS OFF) 361 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2); 362SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2); 363EXPLAIN (VERBOSE, COSTS OFF) 364 SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 365SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 366 367-- =================================================================== 368-- JOIN queries 369-- =================================================================== 370-- Analyze ft4 and ft5 so that we have better statistics. These tables do not 371-- have use_remote_estimate set. 372ANALYZE ft4; 373ANALYZE ft5; 374 375-- join two tables 376EXPLAIN (VERBOSE, COSTS OFF) 377SELECT 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; 378SELECT 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; 379-- join three tables 380EXPLAIN (VERBOSE, COSTS OFF) 381SELECT 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; 382SELECT 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; 383-- left outer join 384EXPLAIN (VERBOSE, COSTS OFF) 385SELECT 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; 386SELECT 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; 387-- left outer join three tables 388EXPLAIN (VERBOSE, COSTS OFF) 389SELECT 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; 390SELECT 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; 391-- left outer join + placement of clauses. 392-- clauses within the nullable side are not pulled up, but top level clause on 393-- non-nullable side is pushed into non-nullable side 394EXPLAIN (VERBOSE, COSTS OFF) 395SELECT 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; 396SELECT 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; 397-- clauses within the nullable side are not pulled up, but the top level clause 398-- on nullable side is not pushed down into nullable side 399EXPLAIN (VERBOSE, COSTS OFF) 400SELECT 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) 401 WHERE (t2.c1 < 10 OR t2.c1 IS NULL) AND t1.c1 < 10; 402SELECT 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) 403 WHERE (t2.c1 < 10 OR t2.c1 IS NULL) AND t1.c1 < 10; 404-- right outer join 405EXPLAIN (VERBOSE, COSTS OFF) 406SELECT 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; 407SELECT 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; 408-- right outer join three tables 409EXPLAIN (VERBOSE, COSTS OFF) 410SELECT 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; 411SELECT 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; 412-- full outer join 413EXPLAIN (VERBOSE, COSTS OFF) 414SELECT 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; 415SELECT 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; 416-- full outer join with restrictions on the joining relations 417-- a. the joining relations are both base relations 418EXPLAIN (VERBOSE, COSTS OFF) 419SELECT 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; 420SELECT 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; 421EXPLAIN (VERBOSE, COSTS OFF) 422SELECT 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; 423SELECT 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; 424-- b. one of the joining relations is a base relation and the other is a join 425-- relation 426EXPLAIN (VERBOSE, COSTS OFF) 427SELECT 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; 428SELECT 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; 429-- c. test deparsing the remote query as nested subqueries 430EXPLAIN (VERBOSE, COSTS OFF) 431SELECT 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; 432SELECT 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; 433-- d. test deparsing rowmarked relations as subqueries 434EXPLAIN (VERBOSE, COSTS OFF) 435SELECT 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; 436SELECT 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; 437-- full outer join + inner join 438EXPLAIN (VERBOSE, COSTS OFF) 439SELECT 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; 440SELECT 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; 441-- full outer join three tables 442EXPLAIN (VERBOSE, COSTS OFF) 443SELECT 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; 444SELECT 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; 445-- full outer join + right outer join 446EXPLAIN (VERBOSE, COSTS OFF) 447SELECT 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; 448SELECT 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; 449-- right outer join + full outer join 450EXPLAIN (VERBOSE, COSTS OFF) 451SELECT 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; 452SELECT 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; 453-- full outer join + left outer join 454EXPLAIN (VERBOSE, COSTS OFF) 455SELECT 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; 456SELECT 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; 457-- left outer join + full outer join 458EXPLAIN (VERBOSE, COSTS OFF) 459SELECT 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; 460SELECT 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; 461-- right outer join + left outer join 462EXPLAIN (VERBOSE, COSTS OFF) 463SELECT 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; 464SELECT 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; 465-- left outer join + right outer join 466EXPLAIN (VERBOSE, COSTS OFF) 467SELECT 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; 468SELECT 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; 469-- full outer join + WHERE clause, only matched rows 470EXPLAIN (VERBOSE, COSTS OFF) 471SELECT 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; 472SELECT 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; 473-- full outer join + WHERE clause with shippable extensions set 474EXPLAIN (VERBOSE, COSTS OFF) 475SELECT 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; 476ALTER SERVER loopback OPTIONS (DROP extensions); 477-- full outer join + WHERE clause with shippable extensions not set 478EXPLAIN (VERBOSE, COSTS OFF) 479SELECT 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; 480ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw'); 481-- join two tables with FOR UPDATE clause 482-- tests whole-row reference for row marks 483EXPLAIN (VERBOSE, COSTS OFF) 484SELECT 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; 485SELECT 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; 486EXPLAIN (VERBOSE, COSTS OFF) 487SELECT 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; 488SELECT 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; 489-- join two tables with FOR SHARE clause 490EXPLAIN (VERBOSE, COSTS OFF) 491SELECT 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; 492SELECT 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; 493EXPLAIN (VERBOSE, COSTS OFF) 494SELECT 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; 495SELECT 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; 496-- join in CTE 497EXPLAIN (VERBOSE, COSTS OFF) 498WITH 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; 499WITH 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; 500-- ctid with whole-row reference 501EXPLAIN (VERBOSE, COSTS OFF) 502SELECT 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; 503-- SEMI JOIN, not pushed down 504EXPLAIN (VERBOSE, COSTS OFF) 505SELECT 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; 506SELECT 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; 507-- ANTI JOIN, not pushed down 508EXPLAIN (VERBOSE, COSTS OFF) 509SELECT 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; 510SELECT 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; 511-- CROSS JOIN, not pushed down 512EXPLAIN (VERBOSE, COSTS OFF) 513SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10; 514SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10; 515-- different server, not pushed down. No result expected. 516EXPLAIN (VERBOSE, COSTS OFF) 517SELECT 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; 518SELECT 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; 519-- unsafe join conditions (c8 has a UDT), not pushed down. Practically a CROSS 520-- JOIN since c8 in both tables has same value. 521EXPLAIN (VERBOSE, COSTS OFF) 522SELECT 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; 523SELECT 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; 524-- unsafe conditions on one side (c8 has a UDT), not pushed down. 525EXPLAIN (VERBOSE, COSTS OFF) 526SELECT 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; 527SELECT 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; 528-- join where unsafe to pushdown condition in WHERE clause has a column not 529-- in the SELECT clause. In this test unsafe clause needs to have column 530-- references from both joining sides so that the clause is not pushed down 531-- into one of the joining sides. 532EXPLAIN (VERBOSE, COSTS OFF) 533SELECT 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; 534SELECT 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; 535-- Aggregate after UNION, for testing setrefs 536EXPLAIN (VERBOSE, COSTS OFF) 537SELECT 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; 538SELECT 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; 539-- join with lateral reference 540EXPLAIN (VERBOSE, COSTS OFF) 541SELECT 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; 542SELECT 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; 543 544-- non-Var items in targetlist of the nullable rel of a join preventing 545-- push-down in some cases 546-- unable to push {ft1, ft2} 547EXPLAIN (VERBOSE, COSTS OFF) 548SELECT 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; 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; 550 551-- ok to push {ft1, ft2} but not {ft1, ft2, ft4} 552EXPLAIN (VERBOSE, COSTS OFF) 553SELECT 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; 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; 555 556-- join with nullable side with some columns with null values 557UPDATE ft5 SET c3 = null where c1 % 9 = 0; 558EXPLAIN (VERBOSE, COSTS OFF) 559SELECT 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; 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; 561 562-- multi-way join involving multiple merge joins 563-- (this case used to have EPQ-related planning problems) 564SET enable_nestloop TO false; 565SET enable_hashjoin TO false; 566EXPLAIN (VERBOSE, COSTS OFF) 567SELECT * FROM ft1, ft2, ft4, ft5 WHERE ft1.c1 = ft2.c1 AND ft1.c2 = ft4.c1 568 AND ft1.c2 = ft5.c1 AND ft1.c1 < 100 AND ft2.c1 < 100 FOR UPDATE; 569SELECT * FROM ft1, ft2, ft4, ft5 WHERE ft1.c1 = ft2.c1 AND ft1.c2 = ft4.c1 570 AND ft1.c2 = ft5.c1 AND ft1.c1 < 100 AND ft2.c1 < 100 FOR UPDATE; 571RESET enable_nestloop; 572RESET enable_hashjoin; 573 574-- check join pushdown in situations where multiple userids are involved 575CREATE ROLE regress_view_owner; 576CREATE USER MAPPING FOR regress_view_owner SERVER loopback; 577GRANT SELECT ON ft4 TO regress_view_owner; 578GRANT SELECT ON ft5 TO regress_view_owner; 579 580CREATE VIEW v4 AS SELECT * FROM ft4; 581CREATE VIEW v5 AS SELECT * FROM ft5; 582ALTER VIEW v5 OWNER TO regress_view_owner; 583EXPLAIN (VERBOSE, COSTS OFF) 584SELECT 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 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; 586ALTER VIEW v4 OWNER TO regress_view_owner; 587EXPLAIN (VERBOSE, COSTS OFF) 588SELECT 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 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; 590 591EXPLAIN (VERBOSE, COSTS OFF) 592SELECT 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 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; 594ALTER VIEW v4 OWNER TO CURRENT_USER; 595EXPLAIN (VERBOSE, COSTS OFF) 596SELECT 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 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; 598ALTER VIEW v4 OWNER TO regress_view_owner; 599 600-- cleanup 601DROP OWNED BY regress_view_owner; 602DROP ROLE regress_view_owner; 603 604 605-- =================================================================== 606-- Aggregate and grouping queries 607-- =================================================================== 608 609-- Simple aggregates 610explain (verbose, costs off) 611select 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; 612select 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; 613 614-- Aggregate is not pushed down as aggregation contains random() 615explain (verbose, costs off) 616select sum(c1 * (random() <= 1)::int) as sum, avg(c1) from ft1; 617 618-- Aggregate over join query 619explain (verbose, costs off) 620select count(*), sum(t1.c1), avg(t2.c1) from ft1 t1 inner join ft1 t2 on (t1.c2 = t2.c2) where t1.c2 = 6; 621select count(*), sum(t1.c1), avg(t2.c1) from ft1 t1 inner join ft1 t2 on (t1.c2 = t2.c2) where t1.c2 = 6; 622 623-- Not pushed down due to local conditions present in underneath input rel 624explain (verbose, costs off) 625select 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; 626 627-- GROUP BY clause having expressions 628explain (verbose, costs off) 629select c2/2, sum(c2) * (c2/2) from ft1 group by c2/2 order by c2/2; 630select c2/2, sum(c2) * (c2/2) from ft1 group by c2/2 order by c2/2; 631 632-- Aggregates in subquery are pushed down. 633explain (verbose, costs off) 634select 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; 635select 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; 636 637-- Aggregate is still pushed down by taking unshippable expression out 638explain (verbose, costs off) 639select c2 * (random() <= 1)::int as sum1, sum(c1) * c2 as sum2 from ft1 group by c2 order by 1, 2; 640select c2 * (random() <= 1)::int as sum1, sum(c1) * c2 as sum2 from ft1 group by c2 order by 1, 2; 641 642-- Aggregate with unshippable GROUP BY clause are not pushed 643explain (verbose, costs off) 644select c2 * (random() <= 1)::int as c2 from ft2 group by c2 * (random() <= 1)::int order by 1; 645 646-- GROUP BY clause in various forms, cardinal, alias and constant expression 647explain (verbose, costs off) 648select count(c2) w, c2 x, 5 y, 7.0 z from ft1 group by 2, y, 9.0::int order by 2; 649select count(c2) w, c2 x, 5 y, 7.0 z from ft1 group by 2, y, 9.0::int order by 2; 650 651-- GROUP BY clause referring to same column multiple times 652-- Also, ORDER BY contains an aggregate function 653explain (verbose, costs off) 654select c2, c2 from ft1 where c2 > 6 group by 1, 2 order by sum(c1); 655select c2, c2 from ft1 where c2 > 6 group by 1, 2 order by sum(c1); 656 657-- Testing HAVING clause shippability 658explain (verbose, costs off) 659select c2, sum(c1) from ft2 group by c2 having avg(c1) < 500 and sum(c1) < 49800 order by c2; 660select c2, sum(c1) from ft2 group by c2 having avg(c1) < 500 and sum(c1) < 49800 order by c2; 661 662-- Unshippable HAVING clause will be evaluated locally, and other qual in HAVING clause is pushed down 663explain (verbose, costs off) 664select 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; 665select 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; 666 667-- Aggregate in HAVING clause is not pushable, and thus aggregation is not pushed down 668explain (verbose, costs off) 669select sum(c1) from ft1 group by c2 having avg(c1 * (random() <= 1)::int) > 100 order by 1; 670 671-- Remote aggregate in combination with a local Param (for the output 672-- of an initplan) can be trouble, per bug #15781 673explain (verbose, costs off) 674select exists(select 1 from pg_enum), sum(c1) from ft1; 675select exists(select 1 from pg_enum), sum(c1) from ft1; 676 677explain (verbose, costs off) 678select exists(select 1 from pg_enum), sum(c1) from ft1 group by 1; 679select exists(select 1 from pg_enum), sum(c1) from ft1 group by 1; 680 681 682-- Testing ORDER BY, DISTINCT, FILTER, Ordered-sets and VARIADIC within aggregates 683 684-- ORDER BY within aggregate, same column used to order 685explain (verbose, costs off) 686select array_agg(c1 order by c1) from ft1 where c1 < 100 group by c2 order by 1; 687select array_agg(c1 order by c1) from ft1 where c1 < 100 group by c2 order by 1; 688 689-- ORDER BY within aggregate, different column used to order also using DESC 690explain (verbose, costs off) 691select array_agg(c5 order by c1 desc) from ft2 where c2 = 6 and c1 < 50; 692select array_agg(c5 order by c1 desc) from ft2 where c2 = 6 and c1 < 50; 693 694-- DISTINCT within aggregate 695explain (verbose, costs off) 696select 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; 697select 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; 698 699-- DISTINCT combined with ORDER BY within aggregate 700explain (verbose, costs off) 701select 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; 702select 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; 703 704explain (verbose, costs off) 705select 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; 706select 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; 707 708-- FILTER within aggregate 709explain (verbose, costs off) 710select sum(c1) filter (where c1 < 100 and c2 > 5) from ft1 group by c2 order by 1 nulls last; 711select sum(c1) filter (where c1 < 100 and c2 > 5) from ft1 group by c2 order by 1 nulls last; 712 713-- DISTINCT, ORDER BY and FILTER within aggregate 714explain (verbose, costs off) 715select 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; 716select 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; 717 718-- Outer query is aggregation query 719explain (verbose, costs off) 720select 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; 721select 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; 722-- Inner query is aggregation query 723explain (verbose, costs off) 724select 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; 725select 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; 726 727-- Aggregate not pushed down as FILTER condition is not pushable 728explain (verbose, costs off) 729select sum(c1) filter (where (c1 / c1) * random() <= 1) from ft1 group by c2 order by 1; 730explain (verbose, costs off) 731select sum(c2) filter (where c2 in (select c2 from ft1 where c2 < 5)) from ft1; 732 733-- Ordered-sets within aggregate 734explain (verbose, costs off) 735select 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; 736select 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; 737 738-- Using multiple arguments within aggregates 739explain (verbose, costs off) 740select c1, rank(c1, c2) within group (order by c1, c2) from ft1 group by c1, c2 having c1 = 6 order by 1; 741select c1, rank(c1, c2) within group (order by c1, c2) from ft1 group by c1, c2 having c1 = 6 order by 1; 742 743-- User defined function for user defined aggregate, VARIADIC 744create function least_accum(anyelement, variadic anyarray) 745returns anyelement language sql as 746 'select least($1, min($2[i])) from generate_subscripts($2,1) g(i)'; 747create aggregate least_agg(variadic items anyarray) ( 748 stype = anyelement, sfunc = least_accum 749); 750 751-- Disable hash aggregation for plan stability. 752set enable_hashagg to false; 753 754-- Not pushed down due to user defined aggregate 755explain (verbose, costs off) 756select c2, least_agg(c1) from ft1 group by c2 order by c2; 757 758-- Add function and aggregate into extension 759alter extension postgres_fdw add function least_accum(anyelement, variadic anyarray); 760alter extension postgres_fdw add aggregate least_agg(variadic items anyarray); 761alter server loopback options (set extensions 'postgres_fdw'); 762 763-- Now aggregate will be pushed. Aggregate will display VARIADIC argument. 764explain (verbose, costs off) 765select c2, least_agg(c1) from ft1 where c2 < 100 group by c2 order by c2; 766select c2, least_agg(c1) from ft1 where c2 < 100 group by c2 order by c2; 767 768-- Remove function and aggregate from extension 769alter extension postgres_fdw drop function least_accum(anyelement, variadic anyarray); 770alter extension postgres_fdw drop aggregate least_agg(variadic items anyarray); 771alter server loopback options (set extensions 'postgres_fdw'); 772 773-- Not pushed down as we have dropped objects from extension. 774explain (verbose, costs off) 775select c2, least_agg(c1) from ft1 group by c2 order by c2; 776 777-- Cleanup 778reset enable_hashagg; 779drop aggregate least_agg(variadic items anyarray); 780drop function least_accum(anyelement, variadic anyarray); 781 782 783-- Testing USING OPERATOR() in ORDER BY within aggregate. 784-- For this, we need user defined operators along with operator family and 785-- operator class. Create those and then add them in extension. Note that 786-- user defined objects are considered unshippable unless they are part of 787-- the extension. 788create operator public.<^ ( 789 leftarg = int4, 790 rightarg = int4, 791 procedure = int4eq 792); 793 794create operator public.=^ ( 795 leftarg = int4, 796 rightarg = int4, 797 procedure = int4lt 798); 799 800create operator public.>^ ( 801 leftarg = int4, 802 rightarg = int4, 803 procedure = int4gt 804); 805 806create operator family my_op_family using btree; 807 808create function my_op_cmp(a int, b int) returns int as 809 $$begin return btint4cmp(a, b); end $$ language plpgsql; 810 811create operator class my_op_class for type int using btree family my_op_family as 812 operator 1 public.<^, 813 operator 3 public.=^, 814 operator 5 public.>^, 815 function 1 my_op_cmp(int, int); 816 817-- This will not be pushed as user defined sort operator is not part of the 818-- extension yet. 819explain (verbose, costs off) 820select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2; 821 822-- Add into extension 823alter extension postgres_fdw add operator class my_op_class using btree; 824alter extension postgres_fdw add function my_op_cmp(a int, b int); 825alter extension postgres_fdw add operator family my_op_family using btree; 826alter extension postgres_fdw add operator public.<^(int, int); 827alter extension postgres_fdw add operator public.=^(int, int); 828alter extension postgres_fdw add operator public.>^(int, int); 829alter server loopback options (set extensions 'postgres_fdw'); 830 831-- Now this will be pushed as sort operator is part of the extension. 832explain (verbose, costs off) 833select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2; 834select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2; 835 836-- Remove from extension 837alter extension postgres_fdw drop operator class my_op_class using btree; 838alter extension postgres_fdw drop function my_op_cmp(a int, b int); 839alter extension postgres_fdw drop operator family my_op_family using btree; 840alter extension postgres_fdw drop operator public.<^(int, int); 841alter extension postgres_fdw drop operator public.=^(int, int); 842alter extension postgres_fdw drop operator public.>^(int, int); 843alter server loopback options (set extensions 'postgres_fdw'); 844 845-- This will not be pushed as sort operator is now removed from the extension. 846explain (verbose, costs off) 847select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2; 848 849-- Cleanup 850drop operator class my_op_class using btree; 851drop function my_op_cmp(a int, b int); 852drop operator family my_op_family using btree; 853drop operator public.>^(int, int); 854drop operator public.=^(int, int); 855drop operator public.<^(int, int); 856 857-- Input relation to aggregate push down hook is not safe to pushdown and thus 858-- the aggregate cannot be pushed down to foreign server. 859explain (verbose, costs off) 860select count(t1.c3) from ft1 t1, ft1 t2 where t1.c1 = postgres_fdw_abs(t1.c2); 861 862-- Subquery in FROM clause having aggregate 863explain (verbose, costs off) 864select 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; 865select 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; 866 867-- FULL join with IS NULL check in HAVING 868explain (verbose, costs off) 869select 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; 870select 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; 871 872-- Aggregate over FULL join needing to deparse the joining relations as 873-- subqueries. 874explain (verbose, costs off) 875select 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); 876select 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); 877 878-- ORDER BY expression is part of the target list but not pushed down to 879-- foreign server. 880explain (verbose, costs off) 881select sum(c2) * (random() <= 1)::int as sum from ft1 order by 1; 882select sum(c2) * (random() <= 1)::int as sum from ft1 order by 1; 883 884-- LATERAL join, with parameterization 885set enable_hashagg to false; 886explain (verbose, costs off) 887select 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; 888select 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; 889reset enable_hashagg; 890 891-- bug #15613: bad plan for foreign table scan with lateral reference 892EXPLAIN (VERBOSE, COSTS OFF) 893SELECT ref_0.c2, subq_1.* 894FROM 895 "S 1"."T 1" AS ref_0, 896 LATERAL ( 897 SELECT ref_0."C 1" c1, subq_0.* 898 FROM (SELECT ref_0.c2, ref_1.c3 899 FROM ft1 AS ref_1) AS subq_0 900 RIGHT JOIN ft2 AS ref_3 ON (subq_0.c3 = ref_3.c3) 901 ) AS subq_1 902WHERE ref_0."C 1" < 10 AND subq_1.c3 = '00001' 903ORDER BY ref_0."C 1"; 904 905SELECT ref_0.c2, subq_1.* 906FROM 907 "S 1"."T 1" AS ref_0, 908 LATERAL ( 909 SELECT ref_0."C 1" c1, subq_0.* 910 FROM (SELECT ref_0.c2, ref_1.c3 911 FROM ft1 AS ref_1) AS subq_0 912 RIGHT JOIN ft2 AS ref_3 ON (subq_0.c3 = ref_3.c3) 913 ) AS subq_1 914WHERE ref_0."C 1" < 10 AND subq_1.c3 = '00001' 915ORDER BY ref_0."C 1"; 916 917-- Check with placeHolderVars 918explain (verbose, costs off) 919select 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); 920select 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); 921 922 923-- Not supported cases 924-- Grouping sets 925explain (verbose, costs off) 926select c2, sum(c1) from ft1 where c2 < 3 group by rollup(c2) order by 1 nulls last; 927select c2, sum(c1) from ft1 where c2 < 3 group by rollup(c2) order by 1 nulls last; 928explain (verbose, costs off) 929select c2, sum(c1) from ft1 where c2 < 3 group by cube(c2) order by 1 nulls last; 930select c2, sum(c1) from ft1 where c2 < 3 group by cube(c2) order by 1 nulls last; 931explain (verbose, costs off) 932select c2, c6, sum(c1) from ft1 where c2 < 3 group by grouping sets(c2, c6) order by 1 nulls last, 2 nulls last; 933select c2, c6, sum(c1) from ft1 where c2 < 3 group by grouping sets(c2, c6) order by 1 nulls last, 2 nulls last; 934explain (verbose, costs off) 935select c2, sum(c1), grouping(c2) from ft1 where c2 < 3 group by c2 order by 1 nulls last; 936select c2, sum(c1), grouping(c2) from ft1 where c2 < 3 group by c2 order by 1 nulls last; 937 938-- DISTINCT itself is not pushed down, whereas underneath aggregate is pushed 939explain (verbose, costs off) 940select distinct sum(c1)/1000 s from ft2 where c2 < 6 group by c2 order by 1; 941select distinct sum(c1)/1000 s from ft2 where c2 < 6 group by c2 order by 1; 942 943-- WindowAgg 944explain (verbose, costs off) 945select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 group by c2 order by 1; 946select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 group by c2 order by 1; 947explain (verbose, costs off) 948select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 where c2 < 10 group by c2 order by 1; 949select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 where c2 < 10 group by c2 order by 1; 950explain (verbose, costs off) 951select 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; 952select 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; 953 954 955-- =================================================================== 956-- parameterized queries 957-- =================================================================== 958-- simple join 959PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2; 960EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st1(1, 2); 961EXECUTE st1(1, 1); 962EXECUTE st1(101, 101); 963-- subquery using stable function (can't be sent to remote) 964PREPARE 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; 965EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st2(10, 20); 966EXECUTE st2(10, 20); 967EXECUTE st2(101, 121); 968-- subquery using immutable function (can be sent to remote) 969PREPARE 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; 970EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st3(10, 20); 971EXECUTE st3(10, 20); 972EXECUTE st3(20, 30); 973-- custom plan should be chosen initially 974PREPARE st4(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 = $1; 975EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 976EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 977EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 978EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 979EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 980-- once we try it enough times, should switch to generic plan 981EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); 982-- value of $1 should not be sent to remote 983PREPARE st5(user_enum,int) AS SELECT * FROM ft1 t1 WHERE c8 = $1 and c1 = $2; 984EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 985EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 986EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 987EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 988EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 989EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1); 990EXECUTE st5('foo', 1); 991 992-- altering FDW options requires replanning 993PREPARE st6 AS SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2; 994EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6; 995PREPARE st7 AS INSERT INTO ft1 (c1,c2,c3) VALUES (1001,101,'foo'); 996EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7; 997ALTER TABLE "S 1"."T 1" RENAME TO "T 0"; 998ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 0'); 999EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6; 1000EXECUTE st6; 1001EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7; 1002ALTER TABLE "S 1"."T 0" RENAME TO "T 1"; 1003ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 1'); 1004 1005PREPARE st8 AS SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; 1006EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8; 1007ALTER SERVER loopback OPTIONS (DROP extensions); 1008EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8; 1009EXECUTE st8; 1010ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw'); 1011 1012-- cleanup 1013DEALLOCATE st1; 1014DEALLOCATE st2; 1015DEALLOCATE st3; 1016DEALLOCATE st4; 1017DEALLOCATE st5; 1018DEALLOCATE st6; 1019DEALLOCATE st7; 1020DEALLOCATE st8; 1021 1022-- System columns, except ctid and oid, should not be sent to remote 1023EXPLAIN (VERBOSE, COSTS OFF) 1024SELECT * FROM ft1 t1 WHERE t1.tableoid = 'pg_class'::regclass LIMIT 1; 1025SELECT * FROM ft1 t1 WHERE t1.tableoid = 'ft1'::regclass LIMIT 1; 1026EXPLAIN (VERBOSE, COSTS OFF) 1027SELECT tableoid::regclass, * FROM ft1 t1 LIMIT 1; 1028SELECT tableoid::regclass, * FROM ft1 t1 LIMIT 1; 1029EXPLAIN (VERBOSE, COSTS OFF) 1030SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)'; 1031SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)'; 1032EXPLAIN (VERBOSE, COSTS OFF) 1033SELECT ctid, * FROM ft1 t1 LIMIT 1; 1034SELECT ctid, * FROM ft1 t1 LIMIT 1; 1035EXPLAIN (VERBOSE, COSTS OFF) 1036SELECT oid, * FROM ft_pg_type WHERE typname = 'int4'; 1037SELECT oid, * FROM ft_pg_type WHERE typname = 'int4'; 1038 1039-- =================================================================== 1040-- used in PL/pgSQL function 1041-- =================================================================== 1042CREATE OR REPLACE FUNCTION f_test(p_c1 int) RETURNS int AS $$ 1043DECLARE 1044 v_c1 int; 1045BEGIN 1046 SELECT c1 INTO v_c1 FROM ft1 WHERE c1 = p_c1 LIMIT 1; 1047 PERFORM c1 FROM ft1 WHERE c1 = p_c1 AND p_c1 = v_c1 LIMIT 1; 1048 RETURN v_c1; 1049END; 1050$$ LANGUAGE plpgsql; 1051SELECT f_test(100); 1052DROP FUNCTION f_test(int); 1053 1054-- =================================================================== 1055-- conversion error 1056-- =================================================================== 1057ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE int; 1058SELECT * FROM ft1 ftx(x1,x2,x3,x4,x5,x6,x7,x8) WHERE x1 = 1; -- ERROR 1059SELECT ftx.x1, ft2.c2, ftx.x8 FROM ft1 ftx(x1,x2,x3,x4,x5,x6,x7,x8), ft2 1060 WHERE ftx.x1 = ft2.c1 AND ftx.x1 = 1; -- ERROR 1061SELECT ftx.x1, ft2.c2, ftx FROM ft1 ftx(x1,x2,x3,x4,x5,x6,x7,x8), ft2 1062 WHERE ftx.x1 = ft2.c1 AND ftx.x1 = 1; -- ERROR 1063SELECT sum(c2), array_agg(c8) FROM ft1 GROUP BY c8; -- ERROR 1064ANALYZE ft1; -- ERROR 1065ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum; 1066 1067-- =================================================================== 1068-- subtransaction 1069-- + local/remote error doesn't break cursor 1070-- =================================================================== 1071BEGIN; 1072DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1; 1073FETCH c; 1074SAVEPOINT s; 1075ERROR OUT; -- ERROR 1076ROLLBACK TO s; 1077FETCH c; 1078SAVEPOINT s; 1079SELECT * FROM ft1 WHERE 1 / (c1 - 1) > 0; -- ERROR 1080ROLLBACK TO s; 1081FETCH c; 1082SELECT * FROM ft1 ORDER BY c1 LIMIT 1; 1083COMMIT; 1084 1085-- =================================================================== 1086-- test handling of collations 1087-- =================================================================== 1088create table loct3 (f1 text collate "C" unique, f2 text, f3 varchar(10) unique); 1089create foreign table ft3 (f1 text collate "C", f2 text, f3 varchar(10)) 1090 server loopback options (table_name 'loct3', use_remote_estimate 'true'); 1091 1092-- can be sent to remote 1093explain (verbose, costs off) select * from ft3 where f1 = 'foo'; 1094explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" = 'foo'; 1095explain (verbose, costs off) select * from ft3 where f2 = 'foo'; 1096explain (verbose, costs off) select * from ft3 where f3 = 'foo'; 1097explain (verbose, costs off) select * from ft3 f, loct3 l 1098 where f.f3 = l.f3 and l.f1 = 'foo'; 1099-- can't be sent to remote 1100explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" = 'foo'; 1101explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C"; 1102explain (verbose, costs off) select * from ft3 where f2 COLLATE "C" = 'foo'; 1103explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C"; 1104explain (verbose, costs off) select * from ft3 f, loct3 l 1105 where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo'; 1106 1107-- =================================================================== 1108-- test writable foreign table stuff 1109-- =================================================================== 1110EXPLAIN (verbose, costs off) 1111INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20; 1112INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20; 1113INSERT INTO ft2 (c1,c2,c3) 1114 VALUES (1101,201,'aaa'), (1102,202,'bbb'), (1103,203,'ccc') RETURNING *; 1115INSERT INTO ft2 (c1,c2,c3) VALUES (1104,204,'ddd'), (1105,205,'eee'); 1116EXPLAIN (verbose, costs off) 1117UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3; -- can be pushed down 1118UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3; 1119EXPLAIN (verbose, costs off) 1120UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *; -- can be pushed down 1121UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *; 1122EXPLAIN (verbose, costs off) 1123UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT 1124 FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; -- can't be pushed down 1125UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT 1126 FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; 1127EXPLAIN (verbose, costs off) 1128 DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4; -- can be pushed down 1129DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4; 1130EXPLAIN (verbose, costs off) 1131DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; -- can't be pushed down 1132DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; 1133SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1; 1134EXPLAIN (verbose, costs off) 1135INSERT INTO ft2 (c1,c2,c3) VALUES (9999,999,'foo') RETURNING tableoid::regclass; 1136INSERT INTO ft2 (c1,c2,c3) VALUES (9999,999,'foo') RETURNING tableoid::regclass; 1137EXPLAIN (verbose, costs off) 1138UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass; -- can be pushed down 1139UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass; 1140EXPLAIN (verbose, costs off) 1141DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass; -- can be pushed down 1142DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass; 1143 1144-- Test UPDATE with a MULTIEXPR sub-select 1145-- (maybe someday this'll be remotely executable, but not today) 1146EXPLAIN (verbose, costs off) 1147UPDATE ft2 AS target SET (c2, c7) = ( 1148 SELECT c2 * 10, c7 1149 FROM ft2 AS src 1150 WHERE target.c1 = src.c1 1151) WHERE c1 > 1100; 1152UPDATE ft2 AS target SET (c2, c7) = ( 1153 SELECT c2 * 10, c7 1154 FROM ft2 AS src 1155 WHERE target.c1 = src.c1 1156) WHERE c1 > 1100; 1157 1158UPDATE ft2 AS target SET (c2) = ( 1159 SELECT c2 / 10 1160 FROM ft2 AS src 1161 WHERE target.c1 = src.c1 1162) WHERE c1 > 1100; 1163 1164-- Test that trigger on remote table works as expected 1165CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$ 1166BEGIN 1167 NEW.c3 = NEW.c3 || '_trig_update'; 1168 RETURN NEW; 1169END; 1170$$ LANGUAGE plpgsql; 1171CREATE TRIGGER t1_br_insert BEFORE INSERT OR UPDATE 1172 ON "S 1"."T 1" FOR EACH ROW EXECUTE PROCEDURE "S 1".F_BRTRIG(); 1173 1174INSERT INTO ft2 (c1,c2,c3) VALUES (1208, 818, 'fff') RETURNING *; 1175INSERT INTO ft2 (c1,c2,c3,c6) VALUES (1218, 818, 'ggg', '(--;') RETURNING *; 1176UPDATE ft2 SET c2 = c2 + 600 WHERE c1 % 10 = 8 AND c1 < 1200 RETURNING *; 1177 1178-- Test errors thrown on remote side during update 1179ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0); 1180 1181INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key 1182INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT DO NOTHING; -- works 1183INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO NOTHING; -- unsupported 1184INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO UPDATE SET c3 = 'ffg'; -- unsupported 1185INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive 1186UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive 1187 1188-- Test savepoint/rollback behavior 1189select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1190select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; 1191begin; 1192update ft2 set c2 = 42 where c2 = 0; 1193select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1194savepoint s1; 1195update ft2 set c2 = 44 where c2 = 4; 1196select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1197release savepoint s1; 1198select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1199savepoint s2; 1200update ft2 set c2 = 46 where c2 = 6; 1201select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1202rollback to savepoint s2; 1203select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1204release savepoint s2; 1205select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1206savepoint s3; 1207update ft2 set c2 = -2 where c2 = 42 and c1 = 10; -- fail on remote side 1208rollback to savepoint s3; 1209select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1210release savepoint s3; 1211select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1212-- none of the above is committed yet remotely 1213select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; 1214commit; 1215select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; 1216select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; 1217 1218-- Above DMLs add data with c6 as NULL in ft1, so test ORDER BY NULLS LAST and NULLs 1219-- FIRST behavior here. 1220-- ORDER BY DESC NULLS LAST options 1221EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 ORDER BY c6 DESC NULLS LAST, c1 OFFSET 795 LIMIT 10; 1222SELECT * FROM ft1 ORDER BY c6 DESC NULLS LAST, c1 OFFSET 795 LIMIT 10; 1223-- ORDER BY DESC NULLS FIRST options 1224EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 ORDER BY c6 DESC NULLS FIRST, c1 OFFSET 15 LIMIT 10; 1225SELECT * FROM ft1 ORDER BY c6 DESC NULLS FIRST, c1 OFFSET 15 LIMIT 10; 1226-- ORDER BY ASC NULLS FIRST options 1227EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10; 1228SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10; 1229 1230-- =================================================================== 1231-- test check constraints 1232-- =================================================================== 1233 1234-- Consistent check constraints provide consistent results 1235ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0); 1236EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 < 0; 1237SELECT count(*) FROM ft1 WHERE c2 < 0; 1238SET constraint_exclusion = 'on'; 1239EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 < 0; 1240SELECT count(*) FROM ft1 WHERE c2 < 0; 1241RESET constraint_exclusion; 1242-- check constraint is enforced on the remote side, not locally 1243INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive 1244UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive 1245ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2positive; 1246 1247-- But inconsistent check constraints provide inconsistent results 1248ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2negative CHECK (c2 < 0); 1249EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 >= 0; 1250SELECT count(*) FROM ft1 WHERE c2 >= 0; 1251SET constraint_exclusion = 'on'; 1252EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 >= 0; 1253SELECT count(*) FROM ft1 WHERE c2 >= 0; 1254RESET constraint_exclusion; 1255-- local check constraint is not actually enforced 1256INSERT INTO ft1(c1, c2) VALUES(1111, 2); 1257UPDATE ft1 SET c2 = c2 + 1 WHERE c1 = 1; 1258ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative; 1259 1260-- =================================================================== 1261-- test WITH CHECK OPTION constraints 1262-- =================================================================== 1263 1264CREATE TABLE base_tbl (a int, b int); 1265CREATE FOREIGN TABLE foreign_tbl (a int, b int) 1266 SERVER loopback OPTIONS(table_name 'base_tbl'); 1267CREATE VIEW rw_view AS SELECT * FROM foreign_tbl 1268 WHERE a < b WITH CHECK OPTION; 1269\d+ rw_view 1270 1271INSERT INTO rw_view VALUES (0, 10); -- ok 1272INSERT INTO rw_view VALUES (10, 0); -- should fail 1273EXPLAIN (VERBOSE, COSTS OFF) 1274UPDATE rw_view SET b = 20 WHERE a = 0; -- not pushed down 1275UPDATE rw_view SET b = 20 WHERE a = 0; -- ok 1276EXPLAIN (VERBOSE, COSTS OFF) 1277UPDATE rw_view SET b = -20 WHERE a = 0; -- not pushed down 1278UPDATE rw_view SET b = -20 WHERE a = 0; -- should fail 1279SELECT * FROM foreign_tbl; 1280 1281DROP FOREIGN TABLE foreign_tbl CASCADE; 1282DROP TABLE base_tbl; 1283 1284-- =================================================================== 1285-- test serial columns (ie, sequence-based defaults) 1286-- =================================================================== 1287create table loc1 (f1 serial, f2 text); 1288create foreign table rem1 (f1 serial, f2 text) 1289 server loopback options(table_name 'loc1'); 1290select pg_catalog.setval('rem1_f1_seq', 10, false); 1291insert into loc1(f2) values('hi'); 1292insert into rem1(f2) values('hi remote'); 1293insert into loc1(f2) values('bye'); 1294insert into rem1(f2) values('bye remote'); 1295select * from loc1; 1296select * from rem1; 1297 1298-- =================================================================== 1299-- test local triggers 1300-- =================================================================== 1301 1302-- Trigger functions "borrowed" from triggers regress test. 1303CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS $$ 1304BEGIN 1305 RAISE NOTICE 'trigger_func(%) called: action = %, when = %, level = %', 1306 TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL; 1307 RETURN NULL; 1308END;$$; 1309 1310CREATE TRIGGER trig_stmt_before BEFORE DELETE OR INSERT OR UPDATE ON rem1 1311 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); 1312CREATE TRIGGER trig_stmt_after AFTER DELETE OR INSERT OR UPDATE ON rem1 1313 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); 1314 1315CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger 1316LANGUAGE plpgsql AS $$ 1317 1318declare 1319 oldnew text[]; 1320 relid text; 1321 argstr text; 1322begin 1323 1324 relid := TG_relid::regclass; 1325 argstr := ''; 1326 for i in 0 .. TG_nargs - 1 loop 1327 if i > 0 then 1328 argstr := argstr || ', '; 1329 end if; 1330 argstr := argstr || TG_argv[i]; 1331 end loop; 1332 1333 RAISE NOTICE '%(%) % % % ON %', 1334 tg_name, argstr, TG_when, TG_level, TG_OP, relid; 1335 oldnew := '{}'::text[]; 1336 if TG_OP != 'INSERT' then 1337 oldnew := array_append(oldnew, format('OLD: %s', OLD)); 1338 end if; 1339 1340 if TG_OP != 'DELETE' then 1341 oldnew := array_append(oldnew, format('NEW: %s', NEW)); 1342 end if; 1343 1344 RAISE NOTICE '%', array_to_string(oldnew, ','); 1345 1346 if TG_OP = 'DELETE' then 1347 return OLD; 1348 else 1349 return NEW; 1350 end if; 1351end; 1352$$; 1353 1354-- Test basic functionality 1355CREATE TRIGGER trig_row_before 1356BEFORE INSERT OR UPDATE OR DELETE ON rem1 1357FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1358 1359CREATE TRIGGER trig_row_after 1360AFTER INSERT OR UPDATE OR DELETE ON rem1 1361FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1362 1363delete from rem1; 1364insert into rem1 values(1,'insert'); 1365update rem1 set f2 = 'update' where f1 = 1; 1366update rem1 set f2 = f2 || f2; 1367 1368 1369-- cleanup 1370DROP TRIGGER trig_row_before ON rem1; 1371DROP TRIGGER trig_row_after ON rem1; 1372DROP TRIGGER trig_stmt_before ON rem1; 1373DROP TRIGGER trig_stmt_after ON rem1; 1374 1375DELETE from rem1; 1376 1377 1378-- Test WHEN conditions 1379 1380CREATE TRIGGER trig_row_before_insupd 1381BEFORE INSERT OR UPDATE ON rem1 1382FOR EACH ROW 1383WHEN (NEW.f2 like '%update%') 1384EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1385 1386CREATE TRIGGER trig_row_after_insupd 1387AFTER INSERT OR UPDATE ON rem1 1388FOR EACH ROW 1389WHEN (NEW.f2 like '%update%') 1390EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1391 1392-- Insert or update not matching: nothing happens 1393INSERT INTO rem1 values(1, 'insert'); 1394UPDATE rem1 set f2 = 'test'; 1395 1396-- Insert or update matching: triggers are fired 1397INSERT INTO rem1 values(2, 'update'); 1398UPDATE rem1 set f2 = 'update update' where f1 = '2'; 1399 1400CREATE TRIGGER trig_row_before_delete 1401BEFORE DELETE ON rem1 1402FOR EACH ROW 1403WHEN (OLD.f2 like '%update%') 1404EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1405 1406CREATE TRIGGER trig_row_after_delete 1407AFTER DELETE ON rem1 1408FOR EACH ROW 1409WHEN (OLD.f2 like '%update%') 1410EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1411 1412-- Trigger is fired for f1=2, not for f1=1 1413DELETE FROM rem1; 1414 1415-- cleanup 1416DROP TRIGGER trig_row_before_insupd ON rem1; 1417DROP TRIGGER trig_row_after_insupd ON rem1; 1418DROP TRIGGER trig_row_before_delete ON rem1; 1419DROP TRIGGER trig_row_after_delete ON rem1; 1420 1421 1422-- Test various RETURN statements in BEFORE triggers. 1423 1424CREATE FUNCTION trig_row_before_insupdate() RETURNS TRIGGER AS $$ 1425 BEGIN 1426 NEW.f2 := NEW.f2 || ' triggered !'; 1427 RETURN NEW; 1428 END 1429$$ language plpgsql; 1430 1431CREATE TRIGGER trig_row_before_insupd 1432BEFORE INSERT OR UPDATE ON rem1 1433FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate(); 1434 1435-- The new values should have 'triggered' appended 1436INSERT INTO rem1 values(1, 'insert'); 1437SELECT * from loc1; 1438INSERT INTO rem1 values(2, 'insert') RETURNING f2; 1439SELECT * from loc1; 1440UPDATE rem1 set f2 = ''; 1441SELECT * from loc1; 1442UPDATE rem1 set f2 = 'skidoo' RETURNING f2; 1443SELECT * from loc1; 1444 1445EXPLAIN (verbose, costs off) 1446UPDATE rem1 set f1 = 10; -- all columns should be transmitted 1447UPDATE rem1 set f1 = 10; 1448SELECT * from loc1; 1449 1450DELETE FROM rem1; 1451 1452-- Add a second trigger, to check that the changes are propagated correctly 1453-- from trigger to trigger 1454CREATE TRIGGER trig_row_before_insupd2 1455BEFORE INSERT OR UPDATE ON rem1 1456FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate(); 1457 1458INSERT INTO rem1 values(1, 'insert'); 1459SELECT * from loc1; 1460INSERT INTO rem1 values(2, 'insert') RETURNING f2; 1461SELECT * from loc1; 1462UPDATE rem1 set f2 = ''; 1463SELECT * from loc1; 1464UPDATE rem1 set f2 = 'skidoo' RETURNING f2; 1465SELECT * from loc1; 1466 1467DROP TRIGGER trig_row_before_insupd ON rem1; 1468DROP TRIGGER trig_row_before_insupd2 ON rem1; 1469 1470DELETE from rem1; 1471 1472INSERT INTO rem1 VALUES (1, 'test'); 1473 1474-- Test with a trigger returning NULL 1475CREATE FUNCTION trig_null() RETURNS TRIGGER AS $$ 1476 BEGIN 1477 RETURN NULL; 1478 END 1479$$ language plpgsql; 1480 1481CREATE TRIGGER trig_null 1482BEFORE INSERT OR UPDATE OR DELETE ON rem1 1483FOR EACH ROW EXECUTE PROCEDURE trig_null(); 1484 1485-- Nothing should have changed. 1486INSERT INTO rem1 VALUES (2, 'test2'); 1487 1488SELECT * from loc1; 1489 1490UPDATE rem1 SET f2 = 'test2'; 1491 1492SELECT * from loc1; 1493 1494DELETE from rem1; 1495 1496SELECT * from loc1; 1497 1498DROP TRIGGER trig_null ON rem1; 1499DELETE from rem1; 1500 1501-- Test a combination of local and remote triggers 1502CREATE TRIGGER trig_row_before 1503BEFORE INSERT OR UPDATE OR DELETE ON rem1 1504FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1505 1506CREATE TRIGGER trig_row_after 1507AFTER INSERT OR UPDATE OR DELETE ON rem1 1508FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1509 1510CREATE TRIGGER trig_local_before BEFORE INSERT OR UPDATE ON loc1 1511FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate(); 1512 1513INSERT INTO rem1(f2) VALUES ('test'); 1514UPDATE rem1 SET f2 = 'testo'; 1515 1516-- Test returning a system attribute 1517INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid; 1518 1519-- cleanup 1520DROP TRIGGER trig_row_before ON rem1; 1521DROP TRIGGER trig_row_after ON rem1; 1522DROP TRIGGER trig_local_before ON loc1; 1523 1524 1525-- Test direct foreign table modification functionality 1526 1527-- Test with statement-level triggers 1528CREATE TRIGGER trig_stmt_before 1529 BEFORE DELETE OR INSERT OR UPDATE ON rem1 1530 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); 1531EXPLAIN (verbose, costs off) 1532UPDATE rem1 set f2 = ''; -- can be pushed down 1533EXPLAIN (verbose, costs off) 1534DELETE FROM rem1; -- can be pushed down 1535DROP TRIGGER trig_stmt_before ON rem1; 1536 1537CREATE TRIGGER trig_stmt_after 1538 AFTER DELETE OR INSERT OR UPDATE ON rem1 1539 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); 1540EXPLAIN (verbose, costs off) 1541UPDATE rem1 set f2 = ''; -- can be pushed down 1542EXPLAIN (verbose, costs off) 1543DELETE FROM rem1; -- can be pushed down 1544DROP TRIGGER trig_stmt_after ON rem1; 1545 1546-- Test with row-level ON INSERT triggers 1547CREATE TRIGGER trig_row_before_insert 1548BEFORE INSERT ON rem1 1549FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1550EXPLAIN (verbose, costs off) 1551UPDATE rem1 set f2 = ''; -- can be pushed down 1552EXPLAIN (verbose, costs off) 1553DELETE FROM rem1; -- can be pushed down 1554DROP TRIGGER trig_row_before_insert ON rem1; 1555 1556CREATE TRIGGER trig_row_after_insert 1557AFTER INSERT ON rem1 1558FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1559EXPLAIN (verbose, costs off) 1560UPDATE rem1 set f2 = ''; -- can be pushed down 1561EXPLAIN (verbose, costs off) 1562DELETE FROM rem1; -- can be pushed down 1563DROP TRIGGER trig_row_after_insert ON rem1; 1564 1565-- Test with row-level ON UPDATE triggers 1566CREATE TRIGGER trig_row_before_update 1567BEFORE UPDATE ON rem1 1568FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1569EXPLAIN (verbose, costs off) 1570UPDATE rem1 set f2 = ''; -- can't be pushed down 1571EXPLAIN (verbose, costs off) 1572DELETE FROM rem1; -- can be pushed down 1573DROP TRIGGER trig_row_before_update ON rem1; 1574 1575CREATE TRIGGER trig_row_after_update 1576AFTER UPDATE ON rem1 1577FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1578EXPLAIN (verbose, costs off) 1579UPDATE rem1 set f2 = ''; -- can't be pushed down 1580EXPLAIN (verbose, costs off) 1581DELETE FROM rem1; -- can be pushed down 1582DROP TRIGGER trig_row_after_update ON rem1; 1583 1584-- Test with row-level ON DELETE triggers 1585CREATE TRIGGER trig_row_before_delete 1586BEFORE DELETE ON rem1 1587FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1588EXPLAIN (verbose, costs off) 1589UPDATE rem1 set f2 = ''; -- can be pushed down 1590EXPLAIN (verbose, costs off) 1591DELETE FROM rem1; -- can't be pushed down 1592DROP TRIGGER trig_row_before_delete ON rem1; 1593 1594CREATE TRIGGER trig_row_after_delete 1595AFTER DELETE ON rem1 1596FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1597EXPLAIN (verbose, costs off) 1598UPDATE rem1 set f2 = ''; -- can be pushed down 1599EXPLAIN (verbose, costs off) 1600DELETE FROM rem1; -- can't be pushed down 1601DROP TRIGGER trig_row_after_delete ON rem1; 1602 1603-- =================================================================== 1604-- test inheritance features 1605-- =================================================================== 1606 1607CREATE TABLE a (aa TEXT); 1608CREATE TABLE loct (aa TEXT, bb TEXT); 1609CREATE FOREIGN TABLE b (bb TEXT) INHERITS (a) 1610 SERVER loopback OPTIONS (table_name 'loct'); 1611 1612INSERT INTO a(aa) VALUES('aaa'); 1613INSERT INTO a(aa) VALUES('aaaa'); 1614INSERT INTO a(aa) VALUES('aaaaa'); 1615 1616INSERT INTO b(aa) VALUES('bbb'); 1617INSERT INTO b(aa) VALUES('bbbb'); 1618INSERT INTO b(aa) VALUES('bbbbb'); 1619 1620SELECT tableoid::regclass, * FROM a; 1621SELECT tableoid::regclass, * FROM b; 1622SELECT tableoid::regclass, * FROM ONLY a; 1623 1624UPDATE a SET aa = 'zzzzzz' WHERE aa LIKE 'aaaa%'; 1625 1626SELECT tableoid::regclass, * FROM a; 1627SELECT tableoid::regclass, * FROM b; 1628SELECT tableoid::regclass, * FROM ONLY a; 1629 1630UPDATE b SET aa = 'new'; 1631 1632SELECT tableoid::regclass, * FROM a; 1633SELECT tableoid::regclass, * FROM b; 1634SELECT tableoid::regclass, * FROM ONLY a; 1635 1636UPDATE a SET aa = 'newtoo'; 1637 1638SELECT tableoid::regclass, * FROM a; 1639SELECT tableoid::regclass, * FROM b; 1640SELECT tableoid::regclass, * FROM ONLY a; 1641 1642DELETE FROM a; 1643 1644SELECT tableoid::regclass, * FROM a; 1645SELECT tableoid::regclass, * FROM b; 1646SELECT tableoid::regclass, * FROM ONLY a; 1647 1648DROP TABLE a CASCADE; 1649DROP TABLE loct; 1650 1651-- Check SELECT FOR UPDATE/SHARE with an inherited source table 1652create table loct1 (f1 int, f2 int, f3 int); 1653create table loct2 (f1 int, f2 int, f3 int); 1654 1655create table foo (f1 int, f2 int); 1656create foreign table foo2 (f3 int) inherits (foo) 1657 server loopback options (table_name 'loct1'); 1658create table bar (f1 int, f2 int); 1659create foreign table bar2 (f3 int) inherits (bar) 1660 server loopback options (table_name 'loct2'); 1661 1662insert into foo values(1,1); 1663insert into foo values(3,3); 1664insert into foo2 values(2,2,2); 1665insert into foo2 values(4,4,4); 1666insert into bar values(1,11); 1667insert into bar values(2,22); 1668insert into bar values(6,66); 1669insert into bar2 values(3,33,33); 1670insert into bar2 values(4,44,44); 1671insert into bar2 values(7,77,77); 1672 1673explain (verbose, costs off) 1674select * from bar where f1 in (select f1 from foo) for update; 1675select * from bar where f1 in (select f1 from foo) for update; 1676 1677explain (verbose, costs off) 1678select * from bar where f1 in (select f1 from foo) for share; 1679select * from bar where f1 in (select f1 from foo) for share; 1680 1681-- Check UPDATE with inherited target and an inherited source table 1682explain (verbose, costs off) 1683update bar set f2 = f2 + 100 where f1 in (select f1 from foo); 1684update bar set f2 = f2 + 100 where f1 in (select f1 from foo); 1685 1686select tableoid::regclass, * from bar order by 1,2; 1687 1688-- Check UPDATE with inherited target and an appendrel subquery 1689explain (verbose, costs off) 1690update bar set f2 = f2 + 100 1691from 1692 ( select f1 from foo union all select f1+3 from foo ) ss 1693where bar.f1 = ss.f1; 1694update bar set f2 = f2 + 100 1695from 1696 ( select f1 from foo union all select f1+3 from foo ) ss 1697where bar.f1 = ss.f1; 1698 1699select tableoid::regclass, * from bar order by 1,2; 1700 1701-- Test forcing the remote server to produce sorted data for a merge join, 1702-- but the foreign table is an inheritance child. 1703truncate table loct1; 1704truncate table only foo; 1705\set num_rows_foo 2000 1706insert into loct1 select generate_series(0, :num_rows_foo, 2), generate_series(0, :num_rows_foo, 2), generate_series(0, :num_rows_foo, 2); 1707insert into foo select generate_series(1, :num_rows_foo, 2), generate_series(1, :num_rows_foo, 2); 1708SET enable_hashjoin to false; 1709SET enable_nestloop to false; 1710alter foreign table foo2 options (use_remote_estimate 'true'); 1711create index i_loct1_f1 on loct1(f1); 1712create index i_foo_f1 on foo(f1); 1713analyze foo; 1714analyze loct1; 1715-- inner join; expressions in the clauses appear in the equivalence class list 1716explain (verbose, costs off) 1717 select foo.f1, loct1.f1 from foo join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10; 1718select foo.f1, loct1.f1 from foo join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10; 1719-- outer join; expressions in the clauses do not appear in equivalence class 1720-- list but no output change as compared to the previous query 1721explain (verbose, costs off) 1722 select foo.f1, loct1.f1 from foo left join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10; 1723select foo.f1, loct1.f1 from foo left join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10; 1724RESET enable_hashjoin; 1725RESET enable_nestloop; 1726 1727-- Test that WHERE CURRENT OF is not supported 1728begin; 1729declare c cursor for select * from bar where f1 = 7; 1730fetch from c; 1731update bar set f2 = null where current of c; 1732rollback; 1733 1734explain (verbose, costs off) 1735delete from foo where f1 < 5 returning *; 1736delete from foo where f1 < 5 returning *; 1737explain (verbose, costs off) 1738update bar set f2 = f2 + 100 returning *; 1739update bar set f2 = f2 + 100 returning *; 1740 1741-- Test that UPDATE/DELETE with inherited target works with row-level triggers 1742CREATE TRIGGER trig_row_before 1743BEFORE UPDATE OR DELETE ON bar2 1744FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1745 1746CREATE TRIGGER trig_row_after 1747AFTER UPDATE OR DELETE ON bar2 1748FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); 1749 1750explain (verbose, costs off) 1751update bar set f2 = f2 + 100; 1752update bar set f2 = f2 + 100; 1753 1754explain (verbose, costs off) 1755delete from bar where f2 < 400; 1756delete from bar where f2 < 400; 1757 1758-- cleanup 1759drop table foo cascade; 1760drop table bar cascade; 1761drop table loct1; 1762drop table loct2; 1763 1764-- =================================================================== 1765-- test IMPORT FOREIGN SCHEMA 1766-- =================================================================== 1767 1768CREATE SCHEMA import_source; 1769CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL); 1770CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL, c3 text collate "POSIX"); 1771CREATE TYPE typ1 AS (m1 int, m2 varchar); 1772CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1); 1773CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42)); 1774CREATE TABLE import_source."x 5" (c1 float8); 1775ALTER TABLE import_source."x 5" DROP COLUMN c1; 1776CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1); 1777CREATE TABLE import_source.t4_part PARTITION OF import_source.t4 1778 FOR VALUES FROM (1) TO (100); 1779 1780CREATE SCHEMA import_dest1; 1781IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1; 1782\det+ import_dest1.* 1783\d import_dest1.* 1784 1785-- Options 1786CREATE SCHEMA import_dest2; 1787IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2 1788 OPTIONS (import_default 'true'); 1789\det+ import_dest2.* 1790\d import_dest2.* 1791CREATE SCHEMA import_dest3; 1792IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3 1793 OPTIONS (import_collate 'false', import_not_null 'false'); 1794\det+ import_dest3.* 1795\d import_dest3.* 1796 1797-- Check LIMIT TO and EXCEPT 1798CREATE SCHEMA import_dest4; 1799IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch) 1800 FROM SERVER loopback INTO import_dest4; 1801\det+ import_dest4.* 1802IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch) 1803 FROM SERVER loopback INTO import_dest4; 1804\det+ import_dest4.* 1805 1806-- Assorted error cases 1807IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest4; 1808IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO import_dest4; 1809IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO notthere; 1810IMPORT FOREIGN SCHEMA nonesuch FROM SERVER nowhere INTO notthere; 1811 1812-- Check case of a type present only on the remote server. 1813-- We can fake this by dropping the type locally in our transaction. 1814CREATE TYPE "Colors" AS ENUM ('red', 'green', 'blue'); 1815CREATE TABLE import_source.t5 (c1 int, c2 text collate "C", "Col" "Colors"); 1816 1817CREATE SCHEMA import_dest5; 1818BEGIN; 1819DROP TYPE "Colors" CASCADE; 1820IMPORT FOREIGN SCHEMA import_source LIMIT TO (t5) 1821 FROM SERVER loopback INTO import_dest5; -- ERROR 1822 1823ROLLBACK; 1824 1825BEGIN; 1826 1827 1828CREATE SERVER fetch101 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( fetch_size '101' ); 1829 1830SELECT count(*) 1831FROM pg_foreign_server 1832WHERE srvname = 'fetch101' 1833AND srvoptions @> array['fetch_size=101']; 1834 1835ALTER SERVER fetch101 OPTIONS( SET fetch_size '202' ); 1836 1837SELECT count(*) 1838FROM pg_foreign_server 1839WHERE srvname = 'fetch101' 1840AND srvoptions @> array['fetch_size=101']; 1841 1842SELECT count(*) 1843FROM pg_foreign_server 1844WHERE srvname = 'fetch101' 1845AND srvoptions @> array['fetch_size=202']; 1846 1847CREATE FOREIGN TABLE table30000 ( x int ) SERVER fetch101 OPTIONS ( fetch_size '30000' ); 1848 1849SELECT COUNT(*) 1850FROM pg_foreign_table 1851WHERE ftrelid = 'table30000'::regclass 1852AND ftoptions @> array['fetch_size=30000']; 1853 1854ALTER FOREIGN TABLE table30000 OPTIONS ( SET fetch_size '60000'); 1855 1856SELECT COUNT(*) 1857FROM pg_foreign_table 1858WHERE ftrelid = 'table30000'::regclass 1859AND ftoptions @> array['fetch_size=30000']; 1860 1861SELECT COUNT(*) 1862FROM pg_foreign_table 1863WHERE ftrelid = 'table30000'::regclass 1864AND ftoptions @> array['fetch_size=60000']; 1865 1866ROLLBACK; 1867 1868-- =================================================================== 1869-- test connection invalidation cases 1870-- =================================================================== 1871-- This test case is for closing the connection in pgfdw_xact_callback 1872BEGIN; 1873-- Connection xact depth becomes 1 i.e. the connection is in midst of the xact. 1874SELECT 1 FROM ft1 LIMIT 1; 1875-- Connection is not closed at the end of the alter statement in 1876-- pgfdw_inval_callback. That's because the connection is in midst of this 1877-- xact, it is just marked as invalid. 1878ALTER SERVER loopback OPTIONS (ADD use_remote_estimate 'off'); 1879-- The invalid connection gets closed in pgfdw_xact_callback during commit. 1880COMMIT; 1881