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