text: -- ===================================================================
  -- create FDW objects
  -- ===================================================================
  
  CREATE EXTENSION postgres_fdw;
  
  CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
  CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (dbname 'contrib_regression');
  
  CREATE USER MAPPING FOR public SERVER testserver1
  	OPTIONS (user 'value', password 'value');
  CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
  
  -- ===================================================================
  -- create objects used through FDW loopback server
  -- ===================================================================
  CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz');
  CREATE SCHEMA "S 1";
  CREATE TABLE "S 1"."T 1" (
  	"C 1" int NOT NULL,
  	c2 int NOT NULL,
  	c3 text,
  	c4 timestamptz,
  	c5 timestamp,
  	c6 varchar(10),
  	c7 char(10),
  	c8 user_enum,
  	CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
  );
  CREATE TABLE "S 1"."T 2" (
  	c1 int NOT NULL,
  	c2 text,
  	CONSTRAINT t2_pkey PRIMARY KEY (c1)
  );
  
  INSERT INTO "S 1"."T 1"
  	SELECT id,
  	       id % 10,
  	       to_char(id, 'FM00000'),
  	       '1970-01-01'::timestamptz + ((id % 100) || ' days')::interval,
  	       '1970-01-01'::timestamp + ((id % 100) || ' days')::interval,
  	       id % 10,
  	       id % 10,
  	       'foo'::user_enum
  	FROM generate_series(1, 1000) id;
  INSERT INTO "S 1"."T 2"
  	SELECT id,
  	       'AAA' || to_char(id, 'FM000')
  	FROM generate_series(1, 100) id;
  
  ANALYZE "S 1"."T 1";
  ANALYZE "S 1"."T 2";
  
  -- ===================================================================
  -- create foreign tables
  -- ===================================================================
  CREATE FOREIGN TABLE ft1 (
  	c0 int,
  	c1 int NOT NULL,
  	c2 int NOT NULL,
  	c3 text,
  	c4 timestamptz,
  	c5 timestamp,
  	c6 varchar(10),
  	c7 char(10) default 'ft1',
  	c8 user_enum
  ) SERVER loopback;
  ALTER FOREIGN TABLE ft1 DROP COLUMN c0;
  
  CREATE FOREIGN TABLE ft2 (
  	c1 int NOT NULL,
  	c2 int NOT NULL,
  	cx int,
  	c3 text,
  	c4 timestamptz,
  	c5 timestamp,
  	c6 varchar(10),
  	c7 char(10) default 'ft2',
  	c8 user_enum
  ) SERVER loopback;
  ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
  
  -- ===================================================================
  -- tests for validator
  -- ===================================================================
  -- requiressl, krbsrvname and gsslib are omitted because they depend on
  -- configure options
  ALTER SERVER testserver1 OPTIONS (
  	use_remote_estimate 'false',
  	updatable 'true',
  	fdw_startup_cost '123.456',
  	fdw_tuple_cost '0.123',
  	service 'value',
  	connect_timeout 'value',
  	dbname 'value',
  	host 'value',
  	hostaddr 'value',
  	port 'value',
  	--client_encoding 'value',
  	application_name 'value',
  	--fallback_application_name 'value',
  	keepalives 'value',
  	keepalives_idle 'value',
  	keepalives_interval 'value',
  	-- requiressl 'value',
  	sslcompression 'value',
  	sslmode 'value',
  	sslcert 'value',
  	sslkey 'value',
  	sslrootcert 'value',
  	sslcrl 'value'
  	--requirepeer 'value',
  	-- krbsrvname 'value',
  	-- gsslib 'value',
  	--replication 'value'
  );
  ALTER USER MAPPING FOR public SERVER testserver1
  	OPTIONS (DROP user, DROP password);
  ALTER FOREIGN TABLE ft1 OPTIONS (schema_name 'S 1', table_name 'T 1');
  ALTER FOREIGN TABLE ft2 OPTIONS (schema_name 'S 1', table_name 'T 1');
  ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
  ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
  \det+
  
  -- Now we should be able to run ANALYZE.
  -- To exercise multiple code paths, we use local stats on ft1
  -- and remote-estimate mode on ft2.
  ANALYZE ft1;
  ALTER FOREIGN TABLE ft2 OPTIONS (use_remote_estimate 'true');
  
  -- ===================================================================
  -- simple queries
  -- ===================================================================
  -- single table, with/without alias
  EXPLAIN (COSTS false) SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
  SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
  EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
  SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
  -- whole-row reference
  EXPLAIN (VERBOSE, COSTS false) SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
  SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
  -- empty result
  SELECT * FROM ft1 WHERE false;
  -- with WHERE clause
  EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
  SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
  -- aggregate
  SELECT COUNT(*) FROM ft1 t1;
  -- join two tables
  SELECT t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
  -- subquery
  SELECT * FROM ft1 t1 WHERE t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 <= 10) ORDER BY c1;
  -- subquery+MAX
  SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1;
  -- used in CTE
  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;
  -- fixed values
  SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
  -- user-defined operator/function
  CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$
  BEGIN
  RETURN abs($1);
  END
  $$ LANGUAGE plpgsql IMMUTABLE;
  CREATE OPERATOR === (
      LEFTARG = int,
      RIGHTARG = int,
      PROCEDURE = int4eq,
      COMMUTATOR = ===,
      NEGATOR = !==
  );
  EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
  EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2;
  EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
  EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
  
  -- ===================================================================
  -- WHERE with remotely-executable conditions
  -- ===================================================================
  EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 1;         -- Var, OpExpr(b), Const
  EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2 = 0; -- BoolExpr
  EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 IS NULL;        -- NullTest
  EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL;    -- NullTest
  EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr
  EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = -c1;          -- OpExpr(l)
  EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE 1 = c1!;           -- OpExpr(r)
  EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE (c1 IS NOT NULL) IS DISTINCT FROM (c1 IS NOT NULL); -- DistinctExpr
  EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1 + 0]); -- ScalarArrayOpExpr
  EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = (ARRAY[c1,c2,3])[1]; -- ArrayRef
  EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c6 = E'foo''s\\bar';  -- check special chars
  EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c8 = 'foo';  -- can't be sent to remote
  -- parameterized remote path
  EXPLAIN (VERBOSE, COSTS false)
    SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;
  SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;
  -- check both safe and unsafe join conditions
  EXPLAIN (VERBOSE, COSTS false)
    SELECT * FROM ft2 a, ft2 b
    WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7);
  SELECT * FROM ft2 a, ft2 b
  WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7);
  -- bug before 9.3.5 due to sloppy handling of remote-estimate parameters
  SELECT * FROM ft1 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft2 WHERE c1 < 5));
  SELECT * FROM ft2 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft1 WHERE c1 < 5));
  
  -- ===================================================================
  -- parameterized queries
  -- ===================================================================
  -- simple join
  PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2;
  EXPLAIN (VERBOSE, COSTS false) EXECUTE st1(1, 2);
  EXECUTE st1(1, 1);
  EXECUTE st1(101, 101);
  -- subquery using stable function (can't be sent to remote)
  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;
  EXPLAIN (VERBOSE, COSTS false) EXECUTE st2(10, 20);
  EXECUTE st2(10, 20);
  EXECUTE st2(101, 121);
  -- subquery using immutable function (can be sent to remote)
  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;
  EXPLAIN (VERBOSE, COSTS false) EXECUTE st3(10, 20);
  EXECUTE st3(10, 20);
  EXECUTE st3(20, 30);
  -- custom plan should be chosen initially
  PREPARE st4(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 = $1;
  EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
  EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
  EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
  EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
  EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
  -- once we try it enough times, should switch to generic plan
  EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
  -- value of $1 should not be sent to remote
  PREPARE st5(user_enum,int) AS SELECT * FROM ft1 t1 WHERE c8 = $1 and c1 = $2;
  EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
  EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
  EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
  EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
  EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
  EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
  EXECUTE st5('foo', 1);
  
  -- cleanup
  DEALLOCATE st1;
  DEALLOCATE st2;
  DEALLOCATE st3;
  DEALLOCATE st4;
  DEALLOCATE st5;
  
  -- ===================================================================
  -- used in pl/pgsql function
  -- ===================================================================
  CREATE OR REPLACE FUNCTION f_test(p_c1 int) RETURNS int AS $$
  DECLARE
  	v_c1 int;
  BEGIN
      SELECT c1 INTO v_c1 FROM ft1 WHERE c1 = p_c1 LIMIT 1;
      PERFORM c1 FROM ft1 WHERE c1 = p_c1 AND p_c1 = v_c1 LIMIT 1;
      RETURN v_c1;
  END;
  $$ LANGUAGE plpgsql;
  SELECT f_test(100);
  DROP FUNCTION f_test(int);
  
  -- ===================================================================
  -- conversion error
  -- ===================================================================
  ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE int;
  SELECT * FROM ft1 WHERE c1 = 1;  -- ERROR
  ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum;
  
  -- ===================================================================
  -- subtransaction
  --  + local/remote error doesn't break cursor
  -- ===================================================================
  BEGIN;
  DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
  FETCH c;
  SAVEPOINT s;
  ERROR OUT;          -- ERROR
  ROLLBACK TO s;
  FETCH c;
  SAVEPOINT s;
  SELECT * FROM ft1 WHERE 1 / (c1 - 1) > 0;  -- ERROR
  ROLLBACK TO s;
  FETCH c;
  SELECT * FROM ft1 ORDER BY c1 LIMIT 1;
  COMMIT;
  
  -- ===================================================================
  -- test handling of collations
  -- ===================================================================
  create table loct3 (f1 text collate "C", f2 text);
  create foreign table ft3 (f1 text collate "C", f2 text)
    server loopback options (table_name 'loct3');
  
  -- can be sent to remote
  explain (verbose, costs off) select * from ft3 where f1 = 'foo';
  explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" = 'foo';
  explain (verbose, costs off) select * from ft3 where f2 = 'foo';
  -- can't be sent to remote
  explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" = 'foo';
  explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
  explain (verbose, costs off) select * from ft3 where f2 COLLATE "C" = 'foo';
  explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C";
  
  -- ===================================================================
  -- test writable foreign table stuff
  -- ===================================================================
  EXPLAIN (verbose, costs off)
  INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
  INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
  INSERT INTO ft2 (c1,c2,c3)
    VALUES (1101,201,'aaa'), (1102,202,'bbb'), (1103,203,'ccc') RETURNING *;
  INSERT INTO ft2 (c1,c2,c3) VALUES (1104,204,'ddd'), (1105,205,'eee');
  UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3;
  UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *;
  EXPLAIN (verbose, costs off)
  UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
    FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
  UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
    FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
  EXPLAIN (verbose, costs off)
    DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
  DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
  EXPLAIN (verbose, costs off)
  DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
  DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
  SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
  
  -- Test that trigger on remote table works as expected
  CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$
  BEGIN
      NEW.c3 = NEW.c3 || '_trig_update';
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  CREATE TRIGGER t1_br_insert BEFORE INSERT OR UPDATE
      ON "S 1"."T 1" FOR EACH ROW EXECUTE PROCEDURE "S 1".F_BRTRIG();
  
  INSERT INTO ft2 (c1,c2,c3) VALUES (1208, 818, 'fff') RETURNING *;
  INSERT INTO ft2 (c1,c2,c3,c6) VALUES (1218, 818, 'ggg', '(--;') RETURNING *;
  UPDATE ft2 SET c2 = c2 + 600 WHERE c1 % 10 = 8 AND c1 < 1200 RETURNING *;
  
  -- Test errors thrown on remote side during update
  ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0);
  
  INSERT INTO ft1(c1, c2) VALUES(11, 12);  -- duplicate key
  INSERT INTO ft1(c1, c2) VALUES(1111, -2);  -- c2positive
  UPDATE ft1 SET c2 = -c2 WHERE c1 = 1;  -- c2positive
  
  -- Test savepoint/rollback behavior
  select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
  select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
  begin;
  update ft2 set c2 = 42 where c2 = 0;
  select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
  savepoint s1;
  update ft2 set c2 = 44 where c2 = 4;
  select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
  release savepoint s1;
  select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
  savepoint s2;
  update ft2 set c2 = 46 where c2 = 6;
  select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
  rollback to savepoint s2;
  select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
  release savepoint s2;
  select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
  savepoint s3;
  update ft2 set c2 = -2 where c2 = 42 and c1 = 10; -- fail on remote side
  rollback to savepoint s3;
  select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
  release savepoint s3;
  select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
  -- none of the above is committed yet remotely
  select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
  commit;
  select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
  select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
  
  -- ===================================================================
  -- test serial columns (ie, sequence-based defaults)
  -- ===================================================================
  create table loc1 (f1 serial, f2 text);
  create foreign table rem1 (f1 serial, f2 text)
    server loopback options(table_name 'loc1');
  select pg_catalog.setval('rem1_f1_seq', 10, false);
  insert into loc1(f2) values('hi');
  insert into rem1(f2) values('hi remote');
  insert into loc1(f2) values('bye');
  insert into rem1(f2) values('bye remote');
  select * from loc1;
  select * from rem1;
  