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