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     END;
  15 $d$;
  16
  17 CREATE USER MAPPING FOR public SERVER testserver1
  18         OPTIONS (user 'value', password 'value');
  19 CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
  20
  21 -- ===================================================================
  22 -- create objects used through FDW loopback server
  23 -- ===================================================================
  24 CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz');
  25 CREATE SCHEMA "S 1";
  26 CREATE TABLE "S 1"."T 1" (
  27         "C 1" int NOT NULL,
  28         c2 int NOT NULL,
  29         c3 text,
  30         c4 timestamptz,
  31         c5 timestamp,
  32         c6 varchar(10),
  33         c7 char(10),
  34         c8 user_enum,
  35         CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
  36 );
  37 CREATE TABLE "S 1"."T 2" (
  38         c1 int NOT NULL,
  39         c2 text,
  40         CONSTRAINT t2_pkey PRIMARY KEY (c1)
  41 );
  42
  43 INSERT INTO "S 1"."T 1"
  44         SELECT id,
  45                id % 10,
  46                to_char(id, 'FM00000'),
  47                '1970-01-01'::timestamptz + ((id % 100) || ' days')::interval,
  48                '1970-01-01'::timestamp + ((id % 100) || ' days')::interval,
  49                id % 10,
  50                id % 10,
  51                'foo'::user_enum
  52         FROM generate_series(1, 1000) id;
  53 INSERT INTO "S 1"."T 2"
  54         SELECT id,
  55                'AAA' || to_char(id, 'FM000')
  56         FROM generate_series(1, 100) id;
  57
  58 ANALYZE "S 1"."T 1";
  59 ANALYZE "S 1"."T 2";
  60
  61 -- ===================================================================
  62 -- create foreign tables
  63 -- ===================================================================
  64 CREATE FOREIGN TABLE ft1 (
  65         c0 int,
  66         c1 int NOT NULL,
  67         c2 int NOT NULL,
  68         c3 text,
  69         c4 timestamptz,
  70         c5 timestamp,
  71         c6 varchar(10),
  72         c7 char(10) default 'ft1',
  73         c8 user_enum
  74 ) SERVER loopback;
  75 ALTER FOREIGN TABLE ft1 DROP COLUMN c0;
  76
  77 CREATE FOREIGN TABLE ft2 (
  78         c1 int NOT NULL,
  79         c2 int NOT NULL,
  80         cx int,
  81         c3 text,
  82         c4 timestamptz,
  83         c5 timestamp,
  84         c6 varchar(10),
  85         c7 char(10) default 'ft2',
  86         c8 user_enum
  87 ) SERVER loopback;
  88 ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
  89
  90 -- ===================================================================
  91 -- tests for validator
  92 -- ===================================================================
  93 -- requiressl, krbsrvname and gsslib are omitted because they depend on
  94 -- configure options
  95 ALTER SERVER testserver1 OPTIONS (
  96         use_remote_estimate 'false',
  97         updatable 'true',
  98         fdw_startup_cost '123.456',
  99         fdw_tuple_cost '0.123',
 100         service 'value',
 101         connect_timeout 'value',
 102         dbname 'value',
 103         host 'value',
 104         hostaddr 'value',
 105         port 'value',
 106         --client_encoding 'value',
 107         application_name 'value',
 108         --fallback_application_name 'value',
 109         keepalives 'value',
 110         keepalives_idle 'value',
 111         keepalives_interval 'value',
 112         -- requiressl 'value',
 113         sslcompression 'value',
 114         sslmode 'value',
 115         sslcert 'value',
 116         sslkey 'value',
 117         sslrootcert 'value',
 118         sslcrl 'value'
 119         --requirepeer 'value',
 120         -- krbsrvname 'value',
 121         -- gsslib 'value',
 122         --replication 'value'
 123 );
 124 ALTER USER MAPPING FOR public SERVER testserver1
 125         OPTIONS (DROP user, DROP password);
 126 ALTER FOREIGN TABLE ft1 OPTIONS (schema_name 'S 1', table_name 'T 1');
 127 ALTER FOREIGN TABLE ft2 OPTIONS (schema_name 'S 1', table_name 'T 1');
 128 ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
 129 ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
 130 \det+
 131
 132 -- Now we should be able to run ANALYZE.
 133 -- To exercise multiple code paths, we use local stats on ft1
 134 -- and remote-estimate mode on ft2.
 135 ANALYZE ft1;
 136 ALTER FOREIGN TABLE ft2 OPTIONS (use_remote_estimate 'true');
 137
 138 -- ===================================================================
 139 -- simple queries
 140 -- ===================================================================
 141 -- single table, with/without alias
 142 EXPLAIN (COSTS false) SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
 143 SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
 144 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
 145 SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
 146 -- whole-row reference
 147 EXPLAIN (VERBOSE, COSTS false) SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
 148 SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
 149 -- empty result
 150 SELECT * FROM ft1 WHERE false;
 151 -- with WHERE clause
 152 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
 153 SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
 154 -- with FOR UPDATE/SHARE
 155 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = 101 FOR UPDATE;
 156 SELECT * FROM ft1 t1 WHERE c1 = 101 FOR UPDATE;
 157 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = 102 FOR SHARE;
 158 SELECT * FROM ft1 t1 WHERE c1 = 102 FOR SHARE;
 159 -- aggregate
 160 SELECT COUNT(*) FROM ft1 t1;
 161 -- join two tables
 162 SELECT t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
 163 -- subquery
 164 SELECT * FROM ft1 t1 WHERE t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 <= 10) ORDER BY c1;
 165 -- subquery+MAX
 166 SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1;
 167 -- used in CTE
 168 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;
 169 -- fixed values
 170 SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
 171 -- user-defined operator/function
 172 CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$
 173 BEGIN
 174 RETURN abs($1);
 175 END
 176 $$ LANGUAGE plpgsql IMMUTABLE;
 177 CREATE OPERATOR === (
 178     LEFTARG = int,
 179     RIGHTARG = int,
 180     PROCEDURE = int4eq,
 181     COMMUTATOR = ===,
 182     NEGATOR = !==
 183 );
 184 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
 185 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2;
 186 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
 187 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
 188
 189 -- ===================================================================
 190 -- WHERE with remotely-executable conditions
 191 -- ===================================================================
 192 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 1;         -- Var, OpExpr(b), Const
 193 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2 = 0; -- BoolExpr
 194 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 IS NULL;        -- NullTest
 195 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL;    -- NullTest
 196 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr
 197 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = -c1;          -- OpExpr(l)
 198 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE 1 = c1!;           -- OpExpr(r)
 199 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE (c1 IS NOT NULL) IS DISTINCT FROM (c1 IS NOT NULL); -- DistinctExpr
 200 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1 + 0]); -- ScalarArrayOpExpr
 201 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = (ARRAY[c1,c2,3])[1]; -- ArrayRef
 202 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c6 = E'foo''s\\bar';  -- check special chars
 203 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c8 = 'foo';  -- can't be sent to remote
 204 -- parameterized remote path
 205 EXPLAIN (VERBOSE, COSTS false)
 206   SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;
 207 SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;
 208 -- check both safe and unsafe join conditions
 209 EXPLAIN (VERBOSE, COSTS false)
 210   SELECT * FROM ft2 a, ft2 b
 211   WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7);
 212 SELECT * FROM ft2 a, ft2 b
 213 WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7);
 214 -- bug before 9.3.5 due to sloppy handling of remote-estimate parameters
 215 SELECT * FROM ft1 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft2 WHERE c1 < 5));
 216 SELECT * FROM ft2 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft1 WHERE c1 < 5));
 217
 218 -- ===================================================================
 219 -- parameterized queries
 220 -- ===================================================================
 221 -- simple join
 222 PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2;
 223 EXPLAIN (VERBOSE, COSTS false) EXECUTE st1(1, 2);
 224 EXECUTE st1(1, 1);
 225 EXECUTE st1(101, 101);
 226 -- subquery using stable function (can't be sent to remote)
 227 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;
 228 EXPLAIN (VERBOSE, COSTS false) EXECUTE st2(10, 20);
 229 EXECUTE st2(10, 20);
 230 EXECUTE st2(101, 121);
 231 -- subquery using immutable function (can be sent to remote)
 232 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;
 233 EXPLAIN (VERBOSE, COSTS false) EXECUTE st3(10, 20);
 234 EXECUTE st3(10, 20);
 235 EXECUTE st3(20, 30);
 236 -- custom plan should be chosen initially
 237 PREPARE st4(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 = $1;
 238 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 239 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 240 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 241 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 242 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 243 -- once we try it enough times, should switch to generic plan
 244 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 245 -- value of $1 should not be sent to remote
 246 PREPARE st5(user_enum,int) AS SELECT * FROM ft1 t1 WHERE c8 = $1 and c1 = $2;
 247 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 248 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 249 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 250 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 251 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 252 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 253 EXECUTE st5('foo', 1);
 254
 255 -- cleanup
 256 DEALLOCATE st1;
 257 DEALLOCATE st2;
 258 DEALLOCATE st3;
 259 DEALLOCATE st4;
 260 DEALLOCATE st5;
 261
 262 -- System columns, except ctid, should not be sent to remote
 263 EXPLAIN (VERBOSE, COSTS false)
 264 SELECT * FROM ft1 t1 WHERE t1.tableoid = 'pg_class'::regclass LIMIT 1;
 265 SELECT * FROM ft1 t1 WHERE t1.tableoid = 'ft1'::regclass LIMIT 1;
 266 EXPLAIN (VERBOSE, COSTS false)
 267 SELECT tableoid::regclass, * FROM ft1 t1 LIMIT 1;
 268 SELECT tableoid::regclass, * FROM ft1 t1 LIMIT 1;
 269 EXPLAIN (VERBOSE, COSTS false)
 270 SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)';
 271 SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)';
 272 EXPLAIN (VERBOSE, COSTS false)
 273 SELECT ctid, * FROM ft1 t1 LIMIT 1;
 274 SELECT ctid, * FROM ft1 t1 LIMIT 1;
 275
 276 -- ===================================================================
 277 -- used in pl/pgsql function
 278 -- ===================================================================
 279 CREATE OR REPLACE FUNCTION f_test(p_c1 int) RETURNS int AS $$
 280 DECLARE
 281         v_c1 int;
 282 BEGIN
 283     SELECT c1 INTO v_c1 FROM ft1 WHERE c1 = p_c1 LIMIT 1;
 284     PERFORM c1 FROM ft1 WHERE c1 = p_c1 AND p_c1 = v_c1 LIMIT 1;
 285     RETURN v_c1;
 286 END;
 287 $$ LANGUAGE plpgsql;
 288 SELECT f_test(100);
 289 DROP FUNCTION f_test(int);
 290
 291 -- ===================================================================
 292 -- conversion error
 293 -- ===================================================================
 294 ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE int;
 295 SELECT * FROM ft1 WHERE c1 = 1;  -- ERROR
 296 ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum;
 297
 298 -- ===================================================================
 299 -- subtransaction
 300 --  + local/remote error doesn't break cursor
 301 -- ===================================================================
 302 BEGIN;
 303 DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
 304 FETCH c;
 305 SAVEPOINT s;
 306 ERROR OUT;          -- ERROR
 307 ROLLBACK TO s;
 308 FETCH c;
 309 SAVEPOINT s;
 310 SELECT * FROM ft1 WHERE 1 / (c1 - 1) > 0;  -- ERROR
 311 ROLLBACK TO s;
 312 FETCH c;
 313 SELECT * FROM ft1 ORDER BY c1 LIMIT 1;
 314 COMMIT;
 315
 316 -- ===================================================================
 317 -- test handling of collations
 318 -- ===================================================================
 319 create table loct3 (f1 text collate "C" unique, f2 text, f3 varchar(10) unique);
 320 create foreign table ft3 (f1 text collate "C", f2 text, f3 varchar(10))
 321   server loopback options (table_name 'loct3', use_remote_estimate 'true');
 322
 323 -- can be sent to remote
 324 explain (verbose, costs off) select * from ft3 where f1 = 'foo';
 325 explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" = 'foo';
 326 explain (verbose, costs off) select * from ft3 where f2 = 'foo';
 327 explain (verbose, costs off) select * from ft3 where f3 = 'foo';
 328 explain (verbose, costs off) select * from ft3 f, loct3 l
 329   where f.f3 = l.f3 and l.f1 = 'foo';
 330 -- can't be sent to remote
 331 explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" = 'foo';
 332 explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
 333 explain (verbose, costs off) select * from ft3 where f2 COLLATE "C" = 'foo';
 334 explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C";
 335 explain (verbose, costs off) select * from ft3 f, loct3 l
 336   where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo';
 337
 338 -- ===================================================================
 339 -- test writable foreign table stuff
 340 -- ===================================================================
 341 EXPLAIN (verbose, costs off)
 342 INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
 343 INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
 344 INSERT INTO ft2 (c1,c2,c3)
 345   VALUES (1101,201,'aaa'), (1102,202,'bbb'), (1103,203,'ccc') RETURNING *;
 346 INSERT INTO ft2 (c1,c2,c3) VALUES (1104,204,'ddd'), (1105,205,'eee');
 347 UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3;
 348 UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *;
 349 EXPLAIN (verbose, costs off)
 350 UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
 351   FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
 352 UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
 353   FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
 354 EXPLAIN (verbose, costs off)
 355   DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
 356 DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
 357 EXPLAIN (verbose, costs off)
 358 DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
 359 DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
 360 SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
 361
 362 -- Test that trigger on remote table works as expected
 363 CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$
 364 BEGIN
 365     NEW.c3 = NEW.c3 || '_trig_update';
 366     RETURN NEW;
 367 END;
 368 $$ LANGUAGE plpgsql;
 369 CREATE TRIGGER t1_br_insert BEFORE INSERT OR UPDATE
 370     ON "S 1"."T 1" FOR EACH ROW EXECUTE PROCEDURE "S 1".F_BRTRIG();
 371
 372 INSERT INTO ft2 (c1,c2,c3) VALUES (1208, 818, 'fff') RETURNING *;
 373 INSERT INTO ft2 (c1,c2,c3,c6) VALUES (1218, 818, 'ggg', '(--;') RETURNING *;
 374 UPDATE ft2 SET c2 = c2 + 600 WHERE c1 % 10 = 8 AND c1 < 1200 RETURNING *;
 375
 376 -- Test errors thrown on remote side during update
 377 ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0);
 378
 379 INSERT INTO ft1(c1, c2) VALUES(11, 12);  -- duplicate key
 380 INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT DO NOTHING; -- works
 381 INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO NOTHING; -- unsupported
 382 INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO UPDATE SET c3 = 'ffg'; -- unsupported
 383 INSERT INTO ft1(c1, c2) VALUES(1111, -2);  -- c2positive
 384 UPDATE ft1 SET c2 = -c2 WHERE c1 = 1;  -- c2positive
 385
 386 -- Test savepoint/rollback behavior
 387 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 388 select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
 389 begin;
 390 update ft2 set c2 = 42 where c2 = 0;
 391 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 392 savepoint s1;
 393 update ft2 set c2 = 44 where c2 = 4;
 394 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 395 release savepoint s1;
 396 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 397 savepoint s2;
 398 update ft2 set c2 = 46 where c2 = 6;
 399 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 400 rollback to savepoint s2;
 401 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 402 release savepoint s2;
 403 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 404 savepoint s3;
 405 update ft2 set c2 = -2 where c2 = 42 and c1 = 10; -- fail on remote side
 406 rollback to savepoint s3;
 407 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 408 release savepoint s3;
 409 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 410 -- none of the above is committed yet remotely
 411 select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
 412 commit;
 413 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 414 select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
 415
 416 -- ===================================================================
 417 -- test check constraints
 418 -- ===================================================================
 419
 420 -- Consistent check constraints provide consistent results
 421 ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0);
 422 EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 < 0;
 423 SELECT count(*) FROM ft1 WHERE c2 < 0;
 424 SET constraint_exclusion = 'on';
 425 EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 < 0;
 426 SELECT count(*) FROM ft1 WHERE c2 < 0;
 427 RESET constraint_exclusion;
 428 -- check constraint is enforced on the remote side, not locally
 429 INSERT INTO ft1(c1, c2) VALUES(1111, -2);  -- c2positive
 430 UPDATE ft1 SET c2 = -c2 WHERE c1 = 1;  -- c2positive
 431 ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2positive;
 432
 433 -- But inconsistent check constraints provide inconsistent results
 434 ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2negative CHECK (c2 < 0);
 435 EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 >= 0;
 436 SELECT count(*) FROM ft1 WHERE c2 >= 0;
 437 SET constraint_exclusion = 'on';
 438 EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 >= 0;
 439 SELECT count(*) FROM ft1 WHERE c2 >= 0;
 440 RESET constraint_exclusion;
 441 -- local check constraint is not actually enforced
 442 INSERT INTO ft1(c1, c2) VALUES(1111, 2);
 443 UPDATE ft1 SET c2 = c2 + 1 WHERE c1 = 1;
 444 ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative;
 445
 446 -- ===================================================================
 447 -- test serial columns (ie, sequence-based defaults)
 448 -- ===================================================================
 449 create table loc1 (f1 serial, f2 text);
 450 create foreign table rem1 (f1 serial, f2 text)
 451   server loopback options(table_name 'loc1');
 452 select pg_catalog.setval('rem1_f1_seq', 10, false);
 453 insert into loc1(f2) values('hi');
 454 insert into rem1(f2) values('hi remote');
 455 insert into loc1(f2) values('bye');
 456 insert into rem1(f2) values('bye remote');
 457 select * from loc1;
 458 select * from rem1;
 459
 460 -- ===================================================================
 461 -- test local triggers
 462 -- ===================================================================
 463
 464 -- Trigger functions "borrowed" from triggers regress test.
 465 CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS $$
 466 BEGIN
 467         RAISE NOTICE 'trigger_func(%) called: action = %, when = %, level = %',
 468                 TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
 469         RETURN NULL;
 470 END;$$;
 471
 472 CREATE TRIGGER trig_stmt_before BEFORE DELETE OR INSERT OR UPDATE ON rem1
 473         FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
 474 CREATE TRIGGER trig_stmt_after AFTER DELETE OR INSERT OR UPDATE ON rem1
 475         FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
 476
 477 CREATE OR REPLACE FUNCTION trigger_data()  RETURNS trigger
 478 LANGUAGE plpgsql AS $$
 479
 480 declare
 481         oldnew text[];
 482         relid text;
 483     argstr text;
 484 begin
 485
 486         relid := TG_relid::regclass;
 487         argstr := '';
 488         for i in 0 .. TG_nargs - 1 loop
 489                 if i > 0 then
 490                         argstr := argstr || ', ';
 491                 end if;
 492                 argstr := argstr || TG_argv[i];
 493         end loop;
 494
 495     RAISE NOTICE '%(%) % % % ON %',
 496                 tg_name, argstr, TG_when, TG_level, TG_OP, relid;
 497     oldnew := '{}'::text[];
 498         if TG_OP != 'INSERT' then
 499                 oldnew := array_append(oldnew, format('OLD: %s', OLD));
 500         end if;
 501
 502         if TG_OP != 'DELETE' then
 503                 oldnew := array_append(oldnew, format('NEW: %s', NEW));
 504         end if;
 505
 506     RAISE NOTICE '%', array_to_string(oldnew, ',');
 507
 508         if TG_OP = 'DELETE' then
 509                 return OLD;
 510         else
 511                 return NEW;
 512         end if;
 513 end;
 514 $$;
 515
 516 -- Test basic functionality
 517 CREATE TRIGGER trig_row_before
 518 BEFORE INSERT OR UPDATE OR DELETE ON rem1
 519 FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
 520
 521 CREATE TRIGGER trig_row_after
 522 AFTER INSERT OR UPDATE OR DELETE ON rem1
 523 FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
 524
 525 delete from rem1;
 526 insert into rem1 values(1,'insert');
 527 update rem1 set f2  = 'update' where f1 = 1;
 528 update rem1 set f2 = f2 || f2;
 529
 530
 531 -- cleanup
 532 DROP TRIGGER trig_row_before ON rem1;
 533 DROP TRIGGER trig_row_after ON rem1;
 534 DROP TRIGGER trig_stmt_before ON rem1;
 535 DROP TRIGGER trig_stmt_after ON rem1;
 536
 537 DELETE from rem1;
 538
 539
 540 -- Test WHEN conditions
 541
 542 CREATE TRIGGER trig_row_before_insupd
 543 BEFORE INSERT OR UPDATE ON rem1
 544 FOR EACH ROW
 545 WHEN (NEW.f2 like '%update%')
 546 EXECUTE PROCEDURE trigger_data(23,'skidoo');
 547
 548 CREATE TRIGGER trig_row_after_insupd
 549 AFTER INSERT OR UPDATE ON rem1
 550 FOR EACH ROW
 551 WHEN (NEW.f2 like '%update%')
 552 EXECUTE PROCEDURE trigger_data(23,'skidoo');
 553
 554 -- Insert or update not matching: nothing happens
 555 INSERT INTO rem1 values(1, 'insert');
 556 UPDATE rem1 set f2 = 'test';
 557
 558 -- Insert or update matching: triggers are fired
 559 INSERT INTO rem1 values(2, 'update');
 560 UPDATE rem1 set f2 = 'update update' where f1 = '2';
 561
 562 CREATE TRIGGER trig_row_before_delete
 563 BEFORE DELETE ON rem1
 564 FOR EACH ROW
 565 WHEN (OLD.f2 like '%update%')
 566 EXECUTE PROCEDURE trigger_data(23,'skidoo');
 567
 568 CREATE TRIGGER trig_row_after_delete
 569 AFTER DELETE ON rem1
 570 FOR EACH ROW
 571 WHEN (OLD.f2 like '%update%')
 572 EXECUTE PROCEDURE trigger_data(23,'skidoo');
 573
 574 -- Trigger is fired for f1=2, not for f1=1
 575 DELETE FROM rem1;
 576
 577 -- cleanup
 578 DROP TRIGGER trig_row_before_insupd ON rem1;
 579 DROP TRIGGER trig_row_after_insupd ON rem1;
 580 DROP TRIGGER trig_row_before_delete ON rem1;
 581 DROP TRIGGER trig_row_after_delete ON rem1;
 582
 583
 584 -- Test various RETURN statements in BEFORE triggers.
 585
 586 CREATE FUNCTION trig_row_before_insupdate() RETURNS TRIGGER AS $$
 587   BEGIN
 588     NEW.f2 := NEW.f2 || ' triggered !';
 589     RETURN NEW;
 590   END
 591 $$ language plpgsql;
 592
 593 CREATE TRIGGER trig_row_before_insupd
 594 BEFORE INSERT OR UPDATE ON rem1
 595 FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate();
 596
 597 -- The new values should have 'triggered' appended
 598 INSERT INTO rem1 values(1, 'insert');
 599 SELECT * from loc1;
 600 INSERT INTO rem1 values(2, 'insert') RETURNING f2;
 601 SELECT * from loc1;
 602 UPDATE rem1 set f2 = '';
 603 SELECT * from loc1;
 604 UPDATE rem1 set f2 = 'skidoo' RETURNING f2;
 605 SELECT * from loc1;
 606
 607 DELETE FROM rem1;
 608
 609 -- Add a second trigger, to check that the changes are propagated correctly
 610 -- from trigger to trigger
 611 CREATE TRIGGER trig_row_before_insupd2
 612 BEFORE INSERT OR UPDATE ON rem1
 613 FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate();
 614
 615 INSERT INTO rem1 values(1, 'insert');
 616 SELECT * from loc1;
 617 INSERT INTO rem1 values(2, 'insert') RETURNING f2;
 618 SELECT * from loc1;
 619 UPDATE rem1 set f2 = '';
 620 SELECT * from loc1;
 621 UPDATE rem1 set f2 = 'skidoo' RETURNING f2;
 622 SELECT * from loc1;
 623
 624 DROP TRIGGER trig_row_before_insupd ON rem1;
 625 DROP TRIGGER trig_row_before_insupd2 ON rem1;
 626
 627 DELETE from rem1;
 628
 629 INSERT INTO rem1 VALUES (1, 'test');
 630
 631 -- Test with a trigger returning NULL
 632 CREATE FUNCTION trig_null() RETURNS TRIGGER AS $$
 633   BEGIN
 634     RETURN NULL;
 635   END
 636 $$ language plpgsql;
 637
 638 CREATE TRIGGER trig_null
 639 BEFORE INSERT OR UPDATE OR DELETE ON rem1
 640 FOR EACH ROW EXECUTE PROCEDURE trig_null();
 641
 642 -- Nothing should have changed.
 643 INSERT INTO rem1 VALUES (2, 'test2');
 644
 645 SELECT * from loc1;
 646
 647 UPDATE rem1 SET f2 = 'test2';
 648
 649 SELECT * from loc1;
 650
 651 DELETE from rem1;
 652
 653 SELECT * from loc1;
 654
 655 DROP TRIGGER trig_null ON rem1;
 656 DELETE from rem1;
 657
 658 -- Test a combination of local and remote triggers
 659 CREATE TRIGGER trig_row_before
 660 BEFORE INSERT OR UPDATE OR DELETE ON rem1
 661 FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
 662
 663 CREATE TRIGGER trig_row_after
 664 AFTER INSERT OR UPDATE OR DELETE ON rem1
 665 FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
 666
 667 CREATE TRIGGER trig_local_before BEFORE INSERT OR UPDATE ON loc1
 668 FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate();
 669
 670 INSERT INTO rem1(f2) VALUES ('test');
 671 UPDATE rem1 SET f2 = 'testo';
 672
 673 -- Test returning a system attribute
 674 INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid;
 675
 676 -- ===================================================================
 677 -- test inheritance features
 678 -- ===================================================================
 679
 680 CREATE TABLE a (aa TEXT);
 681 CREATE TABLE loct (aa TEXT, bb TEXT);
 682 CREATE FOREIGN TABLE b (bb TEXT) INHERITS (a)
 683   SERVER loopback OPTIONS (table_name 'loct');
 684
 685 INSERT INTO a(aa) VALUES('aaa');
 686 INSERT INTO a(aa) VALUES('aaaa');
 687 INSERT INTO a(aa) VALUES('aaaaa');
 688
 689 INSERT INTO b(aa) VALUES('bbb');
 690 INSERT INTO b(aa) VALUES('bbbb');
 691 INSERT INTO b(aa) VALUES('bbbbb');
 692
 693 SELECT tableoid::regclass, * FROM a;
 694 SELECT tableoid::regclass, * FROM b;
 695 SELECT tableoid::regclass, * FROM ONLY a;
 696
 697 UPDATE a SET aa = 'zzzzzz' WHERE aa LIKE 'aaaa%';
 698
 699 SELECT tableoid::regclass, * FROM a;
 700 SELECT tableoid::regclass, * FROM b;
 701 SELECT tableoid::regclass, * FROM ONLY a;
 702
 703 UPDATE b SET aa = 'new';
 704
 705 SELECT tableoid::regclass, * FROM a;
 706 SELECT tableoid::regclass, * FROM b;
 707 SELECT tableoid::regclass, * FROM ONLY a;
 708
 709 UPDATE a SET aa = 'newtoo';
 710
 711 SELECT tableoid::regclass, * FROM a;
 712 SELECT tableoid::regclass, * FROM b;
 713 SELECT tableoid::regclass, * FROM ONLY a;
 714
 715 DELETE FROM a;
 716
 717 SELECT tableoid::regclass, * FROM a;
 718 SELECT tableoid::regclass, * FROM b;
 719 SELECT tableoid::regclass, * FROM ONLY a;
 720
 721 DROP TABLE a CASCADE;
 722 DROP TABLE loct;
 723
 724 -- Check SELECT FOR UPDATE/SHARE with an inherited source table
 725 create table loct1 (f1 int, f2 int, f3 int);
 726 create table loct2 (f1 int, f2 int, f3 int);
 727
 728 create table foo (f1 int, f2 int);
 729 create foreign table foo2 (f3 int) inherits (foo)
 730   server loopback options (table_name 'loct1');
 731 create table bar (f1 int, f2 int);
 732 create foreign table bar2 (f3 int) inherits (bar)
 733   server loopback options (table_name 'loct2');
 734
 735 insert into foo values(1,1);
 736 insert into foo values(3,3);
 737 insert into foo2 values(2,2,2);
 738 insert into foo2 values(4,4,4);
 739 insert into bar values(1,11);
 740 insert into bar values(2,22);
 741 insert into bar values(6,66);
 742 insert into bar2 values(3,33,33);
 743 insert into bar2 values(4,44,44);
 744 insert into bar2 values(7,77,77);
 745
 746 explain (verbose, costs off)
 747 select * from bar where f1 in (select f1 from foo) for update;
 748 select * from bar where f1 in (select f1 from foo) for update;
 749
 750 explain (verbose, costs off)
 751 select * from bar where f1 in (select f1 from foo) for share;
 752 select * from bar where f1 in (select f1 from foo) for share;
 753
 754 -- Check UPDATE with inherited target and an inherited source table
 755 explain (verbose, costs off)
 756 update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
 757 update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
 758
 759 select tableoid::regclass, * from bar order by 1,2;
 760
 761 -- Check UPDATE with inherited target and an appendrel subquery
 762 explain (verbose, costs off)
 763 update bar set f2 = f2 + 100
 764 from
 765   ( select f1 from foo union all select f1+3 from foo ) ss
 766 where bar.f1 = ss.f1;
 767 update bar set f2 = f2 + 100
 768 from
 769   ( select f1 from foo union all select f1+3 from foo ) ss
 770 where bar.f1 = ss.f1;
 771
 772 select tableoid::regclass, * from bar order by 1,2;
 773
 774 -- Test that WHERE CURRENT OF is not supported
 775 begin;
 776 declare c cursor for select * from bar where f1 = 7;
 777 fetch from c;
 778 update bar set f2 = null where current of c;
 779 rollback;
 780
 781 drop table foo cascade;
 782 drop table bar cascade;
 783 drop table loct1;
 784 drop table loct2;
 785
 786 -- ===================================================================
 787 -- test IMPORT FOREIGN SCHEMA
 788 -- ===================================================================
 789
 790 CREATE SCHEMA import_source;
 791 CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL);
 792 CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL, c3 text collate "POSIX");
 793 CREATE TYPE typ1 AS (m1 int, m2 varchar);
 794 CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
 795 CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
 796 CREATE TABLE import_source."x 5" (c1 float8);
 797 ALTER TABLE import_source."x 5" DROP COLUMN c1;
 798
 799 CREATE SCHEMA import_dest1;
 800 IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
 801 \det+ import_dest1
 802 \d import_dest1.*
 803
 804 -- Options
 805 CREATE SCHEMA import_dest2;
 806 IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
 807   OPTIONS (import_default 'true');
 808 \det+ import_dest2
 809 \d import_dest2.*
 810 CREATE SCHEMA import_dest3;
 811 IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
 812   OPTIONS (import_collate 'false', import_not_null 'false');
 813 \det+ import_dest3
 814 \d import_dest3.*
 815
 816 -- Check LIMIT TO and EXCEPT
 817 CREATE SCHEMA import_dest4;
 818 IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch)
 819   FROM SERVER loopback INTO import_dest4;
 820 \det+ import_dest4
 821 IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch)
 822   FROM SERVER loopback INTO import_dest4;
 823 \det+ import_dest4
 824
 825 -- Assorted error cases
 826 IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest4;
 827 IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO import_dest4;
 828 IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO notthere;
 829 IMPORT FOREIGN SCHEMA nonesuch FROM SERVER nowhere INTO notthere;
 830
 831 -- Check case of a type present only on the remote server.
 832 -- We can fake this by dropping the type locally in our transaction.
 833 CREATE TYPE "Colors" AS ENUM ('red', 'green', 'blue');
 834 CREATE TABLE import_source.t5 (c1 int, c2 text collate "C", "Col" "Colors");
 835
 836 CREATE SCHEMA import_dest5;
 837 BEGIN;
 838 DROP TYPE "Colors" CASCADE;
 839 IMPORT FOREIGN SCHEMA import_source LIMIT TO (t5)
 840   FROM SERVER loopback INTO import_dest5;  -- ERROR
 841 ROLLBACK;