contrib/dblink/sql/postgresql_fdw.sql

   1 SET SEARCH_PATH = public;
   2 SET DATESTYLE = 'Postgres, MDY';
   3
   4 -- =============================================================================
   5 -- Prepare section
   6 -- =============================================================================
   7 -- create test user
   8 CREATE ROLE contrib_regression_role_1;
   9
  10 -- create remote database 1
  11 CREATE DATABASE contrib_regression_f1;
  12 \c contrib_regression_f1
  13 CREATE TABLE person (
  14         id integer not null,
  15         name text not null,
  16         birthday date,
  17         update_ts timestamp with time zone,
  18         constraint pk_person primary key (id)
  19 );
  20 BEGIN;
  21 INSERT INTO person VALUES(1, 'foo', '01-31-2000', '01-31-2000 00:00:00+00:00');
  22 INSERT INTO person VALUES(2, 'bar', '01-31-1900', '01-31-1900 00:00:00+00:00');
  23 INSERT INTO person VALUES(3, 'buz', NULL, NULL);
  24 COMMIT;
  25
  26 -- create remote database 2
  27 CREATE DATABASE contrib_regression_f2;
  28 \c contrib_regression_f2
  29 CREATE TABLE person (
  30         id integer not null,
  31         name text not null,
  32         birthday date,
  33         update_ts timestamp with time zone,
  34         constraint pk_person primary key (id)
  35 );
  36 BEGIN;
  37 INSERT INTO person VALUES(1, 'foo', '01-31-2000', '01-31-2000 00:00:00+00:00');
  38 INSERT INTO person VALUES(2, 'bar', '01-31-1900', '01-31-1900 00:00:00+00:00');
  39 INSERT INTO person VALUES(3, 'buz', NULL, NULL);
  40 COMMIT;
  41
  42 -- connect database for regression test
  43 \c contrib_regression
  44
  45 -- create FOREIGN DATA WRAPPER for PostgresSQL
  46 CREATE FOREIGN DATA WRAPPER contrib_regression_wrapper
  47         HANDLER postgresql_fdw_handler
  48     VALIDATOR postgresql_fdw_validator;
  49
  50 -- create FOREIGN SERVER for remote database 1
  51 CREATE SERVER contrib_regression_srv_1
  52         FOREIGN DATA WRAPPER contrib_regression_wrapper
  53     OPTIONS (host 'localhost', dbname 'contrib_regression_f1');
  54 CREATE USER MAPPING FOR PUBLIC SERVER contrib_regression_srv_1;
  55
  56 -- create FOREIGN SERVER for remote database 2
  57 CREATE SERVER contrib_regression_srv_2
  58         FOREIGN DATA WRAPPER contrib_regression_wrapper
  59     OPTIONS (host 'localhost', dbname 'contrib_regression_f2');
  60 CREATE USER MAPPING FOR PUBLIC SERVER contrib_regression_srv_2;
  61
  62 -- Check ALTER FOREIGN TABLE OWNER TO before create various test tables
  63 CREATE FOREIGN TABLE ft1 (c1 integer) SERVER contrib_regression_srv_2;
  64 ALTER FOREIGN TABLE ft1 OWNER TO contrib_regression_role_1;
  65 \d
  66 DROP FOREIGN TABLE ft1;
  67
  68 -- create entity of local table with same contents
  69 CREATE TABLE person_l (
  70         id integer not null,
  71         name text not null,
  72         birthday date,
  73         update_ts timestamp with time zone,
  74         constraint pk_person_l primary key (id)
  75 );
  76 BEGIN;
  77 INSERT INTO person_l VALUES(1, 'foo', '01-31-2000', '01-31-2000 00:00:00+00:00');
  78 INSERT INTO person_l VALUES(2, 'bar', '01-31-1900', '01-31-1900 00:00:00+00:00');
  79 INSERT INTO person_l VALUES(4, 'bar', NULL, NULL);
  80 COMMIT;
  81
  82 -- create foreign table which references table 'person'
  83 CREATE FOREIGN TABLE person (
  84         id integer not null,
  85         name text not null,
  86         birthday date,
  87         update_ts timestamp with time zone
  88 ) SERVER contrib_regression_srv_1;
  89 CREATE FOREIGN TABLE person2 (
  90         id integer not null,
  91         name text not null,
  92         birthday date,
  93         update_ts timestamp with time zone
  94 ) SERVER contrib_regression_srv_2 OPTIONS (nspname 'public', relname 'person');
  95 \det+
  96
  97 -- =============================================================================
  98 -- Misc statement section
  99 -- =============================================================================
 100 ALTER FOREIGN TABLE person INHERIT person_l;
 101 -- row lock via view is not allowed too.
 102 CREATE OR REPLACE VIEW v_person AS SELECT * FROM person;
 103 SELECT * FROM v_person FOR UPDATE NOWAIT;
 104 DROP VIEW v_person;
 105 -- row lock via CTE is not allowed but no error occurs.
 106 WITH t AS (SELECT * FROM person) SELECT * FROM t ORDER BY id FOR UPDATE NOWAIT; -- not error
 107 -- row lock in CTE is not allowed and an error occurs.
 108 WITH t AS (SELECT * FROM person FOR UPDATE) SELECT * FROM t ORDER BY id; -- error
 109
 110 -- cleanup
 111 ALTER FOREIGN TABLE person NO INHERIT person_l;
 112
 113 -- =============================================================================
 114 -- Connection cache test section
 115 -- XXX: some of these tests should been moved to regression test of core.
 116 -- =============================================================================
 117 -- clear connection cache
 118 DISCARD ALL;
 119
 120 -- access foreign table (no result needed)
 121 SELECT * FROM person WHERE 1 = 0;
 122
 123 -- one connection must be cached
 124 SELECT srvname, (user = usename) usename, fdwname
 125         FROM pg_foreign_connections ORDER BY srvname;
 126
 127 -- access remote database 2 (no result needed)
 128 SELECT id, name, birthday FROM person2 WHERE 1 = 0;
 129
 130 -- two connection must be cached
 131 SELECT srvname, (user = usename) usename, fdwname
 132         FROM pg_foreign_connections ORDER BY srvname;
 133
 134 -- disconnect from all servers
 135 DISCARD ALL;
 136
 137 -- connection cache must be empty
 138 SELECT srvname, (user = usename) usename, fdwname
 139         FROM pg_foreign_connections ORDER BY srvname;
 140
 141 -- access remote database 1 and 2 (no result needed)
 142 SELECT id, name, birthday FROM person WHERE 1 = 0;
 143 SELECT id, name, birthday FROM person2 WHERE 1 = 0;
 144
 145 -- two connection must be cached
 146 SELECT srvname, (user = usename) usename, fdwname
 147         FROM pg_foreign_connections ORDER BY srvname;
 148
 149 -- change authorization identifier
 150 SET SESSION AUTHORIZATION contrib_regression_role_1;
 151
 152 -- connection cache must be empty
 153 SELECT srvname, (user = usename) usename, fdwname
 154         FROM pg_foreign_connections ORDER BY srvname;
 155
 156 -- cleanup
 157 RESET SESSION AUTHORIZATION;
 158 DROP FOREIGN TABLE person2;
 159 \det+
 160
 161 -- =============================================================================
 162 -- Query test section
 163 -- =============================================================================
 164 -- all tuples with ORDER BY clause
 165 SELECT id, name, birthday,
 166         xmin, xmax, cmin, cmax, ctid, (tableoid = 'person'::regclass) tableoid
 167         FROM person ORDER BY id;
 168
 169 -- operator and function call in SELECT clause
 170 SELECT id + 10 id, upper(name) upper_name, birthday FROM person ORDER BY id;
 171
 172 -- various join/subquery situations
 173 SELECT f.id, f.name, f.birthday, p.id, p.name, p.birthday FROM person f JOIN person_l p ON (f.id = p.id) ORDER BY f.id, p.id;
 174 SELECT f.id, f.name, f.birthday, p.id, p.name, p.birthday FROM person f LEFT JOIN person_l p ON (f.id = p.id) ORDER BY f.id, p.id;
 175 SELECT f.id, f.name, f.birthday, p.id, p.name, p.birthday FROM person f RIGHT JOIN person_l p ON (f.id = p.id) ORDER BY f.id, p.id;
 176 SELECT f.id, f.name, f.birthday, p.id, p.name, p.birthday FROM person f FULL OUTER JOIN person_l p ON (f.id = p.id) ORDER BY f.id, p.id;
 177 SELECT id, name, birthday FROM person f WHERE f.id = (SELECT min(p.id) FROM person_l p) ORDER BY f.id;
 178 SELECT id, name, birthday FROM person f WHERE (f.id, f.name) IN (SELECT p.id, p.name FROM person_l p) ORDER BY f.id;
 179
 180 -- union/intersect/except
 181 SELECT id, name, birthday FROM person f UNION SELECT id, name, birthday FROM person_l p ORDER BY id;
 182 SELECT name FROM person f INTERSECT SELECT name FROM person_l p ORDER BY name;
 183 SELECT name FROM person f EXCEPT SELECT name FROM person_l p ORDER BY name;
 184
 185 -- WHERE clause evaluation in the foreign server
 186 SELECT id, name, birthday FROM person WHERE id = 1;
 187 SELECT id, name, birthday FROM person WHERE birthday IS NULL ORDER BY id;
 188 SELECT id, name, birthday FROM person WHERE id IN (1, -1, 5) ORDER BY id;
 189 SELECT id, name, birthday FROM person WHERE id IS DISTINCT from 1 ORDER BY id;
 190 SELECT id, name, birthday FROM person WHERE ARRAY[0,id,2] = ARRAY[0,1,2] ORDER BY id;
 191
 192 -- WHERE clause evaluation in local server
 193 SELECT id, name, birthday FROM person WHERE update_ts < '01-31-1999'::date ORDER BY id;
 194
 195 -- limit/offset
 196 SELECT id, name, birthday FROM person f ORDER BY id LIMIT 1 OFFSET 1;
 197
 198 -- PREPARE/EXECUTE
 199 PREPARE ST1(integer, integer) AS SELECT $1 param, id, name, birthday FROM person f WHERE f.id = $2;
 200 EXECUTE ST1(0, 1);
 201 EXECUTE ST1(1, 2);
 202 DEALLOCATE ST1;
 203
 204 -- =============================================================================
 205 -- DDL test section
 206 -- =============================================================================
 207 -- NOT NULL  and CHECK constraints in column constraint syntax
 208 CREATE FOREIGN TABLE ft2 (c1 integer NOT NULL, c2 text CHECK (length(c2) > 0)) SERVER contrib_regression_srv_1;
 209 \d+ ft2
 210
 211 -- CHECK constraints in table constraint syntax
 212 CREATE FOREIGN TABLE ft3 (c1 integer, c2 text, CONSTRAINT ft3_c2_check CHECK (length(c2) > 0)) SERVER contrib_regression_srv_1;
 213 \d+ ft3
 214
 215 -- PRIMARY KEY in column constraint syntax - error
 216 CREATE FOREIGN TABLE ft4 (c1 integer PRIMARY KEY) SERVER contrib_regression_srv_1; -- error
 217
 218 -- UNIQUE in column constraint syntax - error
 219 CREATE FOREIGN TABLE ft4 (c1 integer UNIQUE) SERVER contrib_regression_srv_1; -- error
 220
 221 -- FOREIGN KEY in column constraint syntax - error
 222 CREATE TABLE t1 (c1 integer PRIMARY KEY);
 223 CREATE FOREIGN TABLE ft4 (c1 integer REFERENCES t1 (c1)) SERVER contrib_regression_srv_1; -- error
 224
 225 -- PRIMARY KEY in table constraint syntax - error
 226 CREATE FOREIGN TABLE ft4 (c1 integer, CONSTRAINT ft4_pkey PRIMARY KEY (c1)) SERVER contrib_regression_srv_1; -- error
 227
 228 -- UNIQUE in table constraint syntax - error
 229 CREATE FOREIGN TABLE ft4 (c1 integer, CONSTRAINT ft4_c1_unique UNIQUE (c1)) SERVER contrib_regression_srv_1; -- error
 230
 231 -- FOREIGN KEY in table constraint syntax - error
 232 CREATE FOREIGN TABLE ft4 (c1 integer, CONSTRAINT ft4_c1_fkey FOREIGN KEY (c1) REFERENCES t1 (c1)) SERVER contrib_regression_srv_1; -- error
 233
 234 -- cleanup
 235 DROP FOREIGN TABLE ft2;
 236 DROP FOREIGN TABLE ft3;
 237 DROP TABLE t1;
 238
 239 -- delete all data to use as inherited (parent) table
 240 DELETE FROM person_l;
 241 -- change table definition
 242 \c contrib_regression_f1
 243 ALTER TABLE person ADD COLUMN dummy text;
 244 UPDATE person SET dummy = 'dummy value';
 245 \c contrib_regression
 246 ALTER FOREIGN TABLE person INHERIT person_l;
 247 ALTER TABLE person_l ADD COLUMN dummy text; -- added to person too
 248 CREATE RULE rl_person_insert AS ON INSERT TO person DO INSTEAD INSERT INTO person_l VALUES (NEW.*);
 249 \d+ person;
 250 \d+ person_l;
 251 -- content of person must be showed
 252 INSERT INTO person VALUES (-1, 'FOO', '2100-01-31', null, 'DUMMY');
 253 SELECT * FROM person_l ORDER BY id;
 254
 255 -- restore table definition
 256 \c contrib_regression_f1
 257 ALTER TABLE person DROP COLUMN dummy;
 258 \c contrib_regression
 259 DROP RULE rl_person_insert ON person;
 260 ALTER TABLE person_l DROP COLUMN dummy;
 261 ALTER FOREIGN TABLE person NO INHERIT person_l;
 262 \d+ person;
 263 -- no child table, result must be its own content
 264 SELECT * FROM person_l ORDER BY id;
 265
 266 -- query for foreign table which has no column
 267 ALTER FOREIGN TABLE person DROP COLUMN id;
 268 ALTER FOREIGN TABLE person DROP COLUMN name;
 269 ALTER FOREIGN TABLE person DROP COLUMN birthday;
 270 ALTER FOREIGN TABLE person DROP COLUMN update_ts;
 271 SELECT * FROM person;
 272
 273 -- =============================================================================
 274 -- Cleanup section
 275 -- =============================================================================
 276 DISCARD ALL;
 277 DROP FOREIGN DATA WRAPPER contrib_regression_wrapper CASCADE;
 278 DROP DATABASE contrib_regression_f1;
 279 DROP DATABASE contrib_regression_f2;
 280 DROP ROLE contrib_regression_role_1;