granicus.if.org Git - postgresql/blob - contrib/postgres_fdw/sql/postgres_fdw.sql

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