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 CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
   9   OPTIONS (dbname 'contrib_regression');
  10
  11 CREATE USER MAPPING FOR public SERVER testserver1
  12         OPTIONS (user 'value', password 'value');
  13 CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
  14
  15 -- ===================================================================
  16 -- create objects used through FDW loopback server
  17 -- ===================================================================
  18 CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz');
  19 CREATE SCHEMA "S 1";
  20 CREATE TABLE "S 1"."T 1" (
  21         "C 1" int NOT NULL,
  22         c2 int NOT NULL,
  23         c3 text,
  24         c4 timestamptz,
  25         c5 timestamp,
  26         c6 varchar(10),
  27         c7 char(10),
  28         c8 user_enum,
  29         CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
  30 );
  31 CREATE TABLE "S 1"."T 2" (
  32         c1 int NOT NULL,
  33         c2 text,
  34         CONSTRAINT t2_pkey PRIMARY KEY (c1)
  35 );
  36
  37 INSERT INTO "S 1"."T 1"
  38         SELECT id,
  39                id % 10,
  40                to_char(id, 'FM00000'),
  41                '1970-01-01'::timestamptz + ((id % 100) || ' days')::interval,
  42                '1970-01-01'::timestamp + ((id % 100) || ' days')::interval,
  43                id % 10,
  44                id % 10,
  45                'foo'::user_enum
  46         FROM generate_series(1, 1000) id;
  47 INSERT INTO "S 1"."T 2"
  48         SELECT id,
  49                'AAA' || to_char(id, 'FM000')
  50         FROM generate_series(1, 100) id;
  51
  52 ANALYZE "S 1"."T 1";
  53 ANALYZE "S 1"."T 2";
  54
  55 -- ===================================================================
  56 -- create foreign tables
  57 -- ===================================================================
  58 CREATE FOREIGN TABLE ft1 (
  59         c0 int,
  60         c1 int NOT NULL,
  61         c2 int NOT NULL,
  62         c3 text,
  63         c4 timestamptz,
  64         c5 timestamp,
  65         c6 varchar(10),
  66         c7 char(10) default 'ft1',
  67         c8 user_enum
  68 ) SERVER loopback;
  69 ALTER FOREIGN TABLE ft1 DROP COLUMN c0;
  70
  71 CREATE FOREIGN TABLE ft2 (
  72         c1 int NOT NULL,
  73         c2 int NOT NULL,
  74         cx int,
  75         c3 text,
  76         c4 timestamptz,
  77         c5 timestamp,
  78         c6 varchar(10),
  79         c7 char(10) default 'ft2',
  80         c8 user_enum
  81 ) SERVER loopback;
  82 ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
  83
  84 -- ===================================================================
  85 -- tests for validator
  86 -- ===================================================================
  87 -- requiressl, krbsrvname and gsslib are omitted because they depend on
  88 -- configure options
  89 ALTER SERVER testserver1 OPTIONS (
  90         use_remote_estimate 'false',
  91         fdw_startup_cost '123.456',
  92         fdw_tuple_cost '0.123',
  93         service 'value',
  94         connect_timeout 'value',
  95         dbname 'value',
  96         host 'value',
  97         hostaddr 'value',
  98         port 'value',
  99         --client_encoding 'value',
 100         application_name 'value',
 101         --fallback_application_name 'value',
 102         keepalives 'value',
 103         keepalives_idle 'value',
 104         keepalives_interval 'value',
 105         -- requiressl 'value',
 106         sslcompression 'value',
 107         sslmode 'value',
 108         sslcert 'value',
 109         sslkey 'value',
 110         sslrootcert 'value',
 111         sslcrl 'value'
 112         --requirepeer 'value',
 113         -- krbsrvname 'value',
 114         -- gsslib 'value',
 115         --replication 'value'
 116 );
 117 ALTER USER MAPPING FOR public SERVER testserver1
 118         OPTIONS (DROP user, DROP password);
 119 ALTER FOREIGN TABLE ft1 OPTIONS (schema_name 'S 1', table_name 'T 1');
 120 ALTER FOREIGN TABLE ft2 OPTIONS (schema_name 'S 1', table_name 'T 1');
 121 ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
 122 ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
 123 \det+
 124
 125 -- Now we should be able to run ANALYZE.
 126 -- To exercise multiple code paths, we use local stats on ft1
 127 -- and remote-estimate mode on ft2.
 128 ANALYZE ft1;
 129 ALTER FOREIGN TABLE ft2 OPTIONS (use_remote_estimate 'true');
 130
 131 -- ===================================================================
 132 -- simple queries
 133 -- ===================================================================
 134 -- single table, with/without alias
 135 EXPLAIN (COSTS false) SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
 136 SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
 137 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
 138 SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
 139 -- whole-row reference
 140 EXPLAIN (VERBOSE, COSTS false) SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
 141 SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
 142 -- empty result
 143 SELECT * FROM ft1 WHERE false;
 144 -- with WHERE clause
 145 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
 146 SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
 147 -- aggregate
 148 SELECT COUNT(*) FROM ft1 t1;
 149 -- join two tables
 150 SELECT t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
 151 -- subquery
 152 SELECT * FROM ft1 t1 WHERE t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 <= 10) ORDER BY c1;
 153 -- subquery+MAX
 154 SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1;
 155 -- used in CTE
 156 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;
 157 -- fixed values
 158 SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
 159 -- user-defined operator/function
 160 CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$
 161 BEGIN
 162 RETURN abs($1);
 163 END
 164 $$ LANGUAGE plpgsql IMMUTABLE;
 165 CREATE OPERATOR === (
 166     LEFTARG = int,
 167     RIGHTARG = int,
 168     PROCEDURE = int4eq,
 169     COMMUTATOR = ===,
 170     NEGATOR = !==
 171 );
 172 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
 173 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2;
 174 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
 175 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
 176
 177 -- ===================================================================
 178 -- WHERE with remotely-executable conditions
 179 -- ===================================================================
 180 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 1;         -- Var, OpExpr(b), Const
 181 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2 = 0; -- BoolExpr
 182 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 IS NULL;        -- NullTest
 183 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL;    -- NullTest
 184 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr
 185 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = -c1;          -- OpExpr(l)
 186 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE 1 = c1!;           -- OpExpr(r)
 187 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE (c1 IS NOT NULL) IS DISTINCT FROM (c1 IS NOT NULL); -- DistinctExpr
 188 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1 + 0]); -- ScalarArrayOpExpr
 189 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = (ARRAY[c1,c2,3])[1]; -- ArrayRef
 190 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c6 = E'foo''s\\bar';  -- check special chars
 191 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c8 = 'foo';  -- can't be sent to remote
 192 -- parameterized remote path
 193 EXPLAIN (VERBOSE, COSTS false)
 194   SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;
 195 SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;
 196
 197 -- ===================================================================
 198 -- parameterized queries
 199 -- ===================================================================
 200 -- simple join
 201 PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2;
 202 EXPLAIN (VERBOSE, COSTS false) EXECUTE st1(1, 2);
 203 EXECUTE st1(1, 1);
 204 EXECUTE st1(101, 101);
 205 -- subquery using stable function (can't be sent to remote)
 206 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;
 207 EXPLAIN (VERBOSE, COSTS false) EXECUTE st2(10, 20);
 208 EXECUTE st2(10, 20);
 209 EXECUTE st2(101, 121);
 210 -- subquery using immutable function (can be sent to remote)
 211 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;
 212 EXPLAIN (VERBOSE, COSTS false) EXECUTE st3(10, 20);
 213 EXECUTE st3(10, 20);
 214 EXECUTE st3(20, 30);
 215 -- custom plan should be chosen initially
 216 PREPARE st4(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 = $1;
 217 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 218 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 219 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 220 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 221 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 222 -- once we try it enough times, should switch to generic plan
 223 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 224 -- value of $1 should not be sent to remote
 225 PREPARE st5(user_enum,int) AS SELECT * FROM ft1 t1 WHERE c8 = $1 and c1 = $2;
 226 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 227 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 228 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 229 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 230 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 231 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 232 EXECUTE st5('foo', 1);
 233
 234 -- cleanup
 235 DEALLOCATE st1;
 236 DEALLOCATE st2;
 237 DEALLOCATE st3;
 238 DEALLOCATE st4;
 239 DEALLOCATE st5;
 240
 241 -- ===================================================================
 242 -- used in pl/pgsql function
 243 -- ===================================================================
 244 CREATE OR REPLACE FUNCTION f_test(p_c1 int) RETURNS int AS $$
 245 DECLARE
 246         v_c1 int;
 247 BEGIN
 248     SELECT c1 INTO v_c1 FROM ft1 WHERE c1 = p_c1 LIMIT 1;
 249     PERFORM c1 FROM ft1 WHERE c1 = p_c1 AND p_c1 = v_c1 LIMIT 1;
 250     RETURN v_c1;
 251 END;
 252 $$ LANGUAGE plpgsql;
 253 SELECT f_test(100);
 254 DROP FUNCTION f_test(int);
 255
 256 -- ===================================================================
 257 -- conversion error
 258 -- ===================================================================
 259 ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE int;
 260 SELECT * FROM ft1 WHERE c1 = 1;  -- ERROR
 261 ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum;
 262
 263 -- ===================================================================
 264 -- subtransaction
 265 --  + local/remote error doesn't break cursor
 266 -- ===================================================================
 267 BEGIN;
 268 DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
 269 FETCH c;
 270 SAVEPOINT s;
 271 ERROR OUT;          -- ERROR
 272 ROLLBACK TO s;
 273 FETCH c;
 274 SAVEPOINT s;
 275 SELECT * FROM ft1 WHERE 1 / (c1 - 1) > 0;  -- ERROR
 276 ROLLBACK TO s;
 277 FETCH c;
 278 SELECT * FROM ft1 ORDER BY c1 LIMIT 1;
 279 COMMIT;
 280
 281 -- ===================================================================
 282 -- test handling of collations
 283 -- ===================================================================
 284 create table loct3 (f1 text collate "C", f2 text);
 285 create foreign table ft3 (f1 text collate "C", f2 text)
 286   server loopback options (table_name 'loct3');
 287
 288 -- can be sent to remote
 289 explain (verbose, costs off) select * from ft3 where f1 = 'foo';
 290 explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" = 'foo';
 291 explain (verbose, costs off) select * from ft3 where f2 = 'foo';
 292 -- can't be sent to remote
 293 explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" = 'foo';
 294 explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
 295 explain (verbose, costs off) select * from ft3 where f2 COLLATE "C" = 'foo';
 296 explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C";
 297
 298 -- ===================================================================
 299 -- test writable foreign table stuff
 300 -- ===================================================================
 301 EXPLAIN (verbose, costs off)
 302 INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
 303 INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
 304 INSERT INTO ft2 (c1,c2,c3)
 305   VALUES (1101,201,'aaa'), (1102,202,'bbb'), (1103,203,'ccc') RETURNING *;
 306 INSERT INTO ft2 (c1,c2,c3) VALUES (1104,204,'ddd'), (1105,205,'eee');
 307 UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3;
 308 UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *;
 309 EXPLAIN (verbose, costs off)
 310 UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
 311   FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
 312 UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
 313   FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
 314 EXPLAIN (verbose, costs off)
 315   DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
 316 DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
 317 EXPLAIN (verbose, costs off)
 318 DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
 319 DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
 320 SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
 321
 322 -- Test that trigger on remote table works as expected
 323 CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$
 324 BEGIN
 325     NEW.c3 = NEW.c3 || '_trig_update';
 326     RETURN NEW;
 327 END;
 328 $$ LANGUAGE plpgsql;
 329 CREATE TRIGGER t1_br_insert BEFORE INSERT OR UPDATE
 330     ON "S 1"."T 1" FOR EACH ROW EXECUTE PROCEDURE "S 1".F_BRTRIG();
 331
 332 INSERT INTO ft2 (c1,c2,c3) VALUES (1208, 218, 'fff') RETURNING *;
 333 INSERT INTO ft2 (c1,c2,c3,c6) VALUES (1218, 218, 'ggg', '(--;') RETURNING *;
 334 UPDATE ft2 SET c2 = c2 + 600 WHERE c1 % 10 = 8 RETURNING *;
 335
 336 -- Test errors thrown on remote side during update
 337 ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0);
 338
 339 INSERT INTO ft1(c1, c2) VALUES(11, 12);  -- duplicate key
 340 INSERT INTO ft1(c1, c2) VALUES(1111, -2);  -- c2positive
 341 UPDATE ft1 SET c2 = -c2 WHERE c1 = 1;  -- c2positive
 342
 343 -- Test savepoint/rollback behavior
 344 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 345 select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
 346 begin;
 347 update ft2 set c2 = 42 where c2 = 0;
 348 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 349 savepoint s1;
 350 update ft2 set c2 = 44 where c2 = 4;
 351 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 352 release savepoint s1;
 353 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 354 savepoint s2;
 355 update ft2 set c2 = 46 where c2 = 6;
 356 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 357 rollback to savepoint s2;
 358 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 359 release savepoint s2;
 360 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 361 savepoint s3;
 362 update ft2 set c2 = -2 where c2 = 42 and c1 = 10; -- fail on remote side
 363 rollback to savepoint s3;
 364 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 365 release savepoint s3;
 366 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 367 -- none of the above is committed yet remotely
 368 select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
 369 commit;
 370 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 371 select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
 372
 373 -- ===================================================================
 374 -- test serial columns (ie, sequence-based defaults)
 375 -- ===================================================================
 376 create table loc1 (f1 serial, f2 text);
 377 create foreign table rem1 (f1 serial, f2 text)
 378   server loopback options(table_name 'loc1');
 379 select pg_catalog.setval('rem1_f1_seq', 10, false);
 380 insert into loc1(f2) values('hi');
 381 insert into rem1(f2) values('hi remote');
 382 insert into loc1(f2) values('bye');
 383 insert into rem1(f2) values('bye remote');
 384 select * from loc1;
 385 select * from rem1;