| blob | 426080ae39b4cd86f0847d08c74cb98f2b345ccd |
1 --
2 -- Test foreign-data wrapper and server management.
3 --
4 -- Clean up in case a prior regression run failed
5 -- Suppress NOTICE messages when roles don't exist
6 SET client_min_messages TO 'warning';
7 DROP ROLE IF EXISTS regress_foreign_data_user, regress_test_role, regress_test_role2, regress_test_role_super, regress_test_indirect, regress_unprivileged_role;
8 RESET client_min_messages;
9 CREATE ROLE regress_foreign_data_user LOGIN SUPERUSER;
10 SET SESSION AUTHORIZATION 'regress_foreign_data_user';
11 CREATE ROLE regress_test_role;
12 CREATE ROLE regress_test_role2;
13 CREATE ROLE regress_test_role_super SUPERUSER;
14 CREATE ROLE regress_test_indirect;
15 CREATE ROLE regress_unprivileged_role;
16 CREATE FOREIGN DATA WRAPPER dummy;
17 COMMENT ON FOREIGN DATA WRAPPER dummy IS 'useless';
18 CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator;
19 -- At this point we should have 2 built-in wrappers and no servers.
20 SELECT fdwname, fdwhandler::regproc, fdwvalidator::regproc, fdwoptions FROM pg_foreign_data_wrapper ORDER BY 1, 2, 3;
21 fdwname | fdwhandler | fdwvalidator | fdwoptions
22 ------------+------------+--------------------------+------------
23 dummy | - | - |
24 postgresql | - | postgresql_fdw_validator |
25 (2 rows)
27 SELECT srvname, srvoptions FROM pg_foreign_server;
28 srvname | srvoptions
29 ---------+------------
30 (0 rows)
32 SELECT * FROM pg_user_mapping;
33 oid | umuser | umserver | umoptions
34 -----+--------+----------+-----------
35 (0 rows)
37 -- CREATE FOREIGN DATA WRAPPER
38 CREATE FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR
39 ERROR: function bar(text[], oid) does not exist
40 CREATE FOREIGN DATA WRAPPER foo;
41 \dew
42 List of foreign-data wrappers
43 Name | Owner | Handler | Validator
44 ------------+---------------------------+---------+--------------------------
45 dummy | regress_foreign_data_user | - | -
46 foo | regress_foreign_data_user | - | -
47 postgresql | regress_foreign_data_user | - | postgresql_fdw_validator
48 (3 rows)
50 CREATE FOREIGN DATA WRAPPER foo; -- duplicate
51 ERROR: foreign-data wrapper "foo" already exists
52 DROP FOREIGN DATA WRAPPER foo;
53 CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1');
54 \dew+
55 List of foreign-data wrappers
56 Name | Owner | Handler | Validator | Access privileges | FDW options | Description
57 ------------+---------------------------+---------+--------------------------+-------------------+---------------+-------------
58 dummy | regress_foreign_data_user | - | - | | | useless
59 foo | regress_foreign_data_user | - | - | | (testing '1') |
60 postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
61 (3 rows)
63 DROP FOREIGN DATA WRAPPER foo;
64 CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', testing '2'); -- ERROR
65 ERROR: option "testing" provided more than once
66 CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2');
67 \dew+
68 List of foreign-data wrappers
69 Name | Owner | Handler | Validator | Access privileges | FDW options | Description
70 ------------+---------------------------+---------+--------------------------+-------------------+----------------------------+-------------
71 dummy | regress_foreign_data_user | - | - | | | useless
72 foo | regress_foreign_data_user | - | - | | (testing '1', another '2') |
73 postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
74 (3 rows)
76 DROP FOREIGN DATA WRAPPER foo;
77 SET ROLE regress_test_role;
78 CREATE FOREIGN DATA WRAPPER foo; -- ERROR
79 ERROR: permission denied to create foreign-data wrapper "foo"
80 HINT: Must be superuser to create a foreign-data wrapper.
81 RESET ROLE;
82 CREATE FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator;
83 \dew+
84 List of foreign-data wrappers
85 Name | Owner | Handler | Validator | Access privileges | FDW options | Description
86 ------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
87 dummy | regress_foreign_data_user | - | - | | | useless
88 foo | regress_foreign_data_user | - | postgresql_fdw_validator | | |
89 postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
90 (3 rows)
92 -- HANDLER related checks
93 CREATE FUNCTION invalid_fdw_handler() RETURNS int LANGUAGE SQL AS 'SELECT 1;';
94 CREATE FOREIGN DATA WRAPPER test_fdw HANDLER invalid_fdw_handler; -- ERROR
95 ERROR: function invalid_fdw_handler must return type fdw_handler
96 CREATE FOREIGN DATA WRAPPER test_fdw HANDLER test_fdw_handler HANDLER invalid_fdw_handler; -- ERROR
97 ERROR: conflicting or redundant options
98 LINE 1: ...GN DATA WRAPPER test_fdw HANDLER test_fdw_handler HANDLER in...
99 ^
100 CREATE FOREIGN DATA WRAPPER test_fdw HANDLER test_fdw_handler;
101 DROP FOREIGN DATA WRAPPER test_fdw;
102 -- ALTER FOREIGN DATA WRAPPER
103 ALTER FOREIGN DATA WRAPPER foo; -- ERROR
104 ERROR: syntax error at or near ";"
105 LINE 1: ALTER FOREIGN DATA WRAPPER foo;
106 ^
107 ALTER FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR
108 ERROR: function bar(text[], oid) does not exist
109 ALTER FOREIGN DATA WRAPPER foo NO VALIDATOR;
110 \dew+
111 List of foreign-data wrappers
112 Name | Owner | Handler | Validator | Access privileges | FDW options | Description
113 ------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
114 dummy | regress_foreign_data_user | - | - | | | useless
115 foo | regress_foreign_data_user | - | - | | |
116 postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
117 (3 rows)
119 ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '1', b '2');
120 ALTER FOREIGN DATA WRAPPER foo OPTIONS (SET c '4'); -- ERROR
121 ERROR: option "c" not found
122 ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP c); -- ERROR
123 ERROR: option "c" not found
124 ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x);
125 \dew+
126 List of foreign-data wrappers
127 Name | Owner | Handler | Validator | Access privileges | FDW options | Description
128 ------------+---------------------------+---------+--------------------------+-------------------+----------------+-------------
129 dummy | regress_foreign_data_user | - | - | | | useless
130 foo | regress_foreign_data_user | - | - | | (a '1', b '2') |
131 postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
132 (3 rows)
134 ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4');
135 \dew+
136 List of foreign-data wrappers
137 Name | Owner | Handler | Validator | Access privileges | FDW options | Description
138 ------------+---------------------------+---------+--------------------------+-------------------+----------------+-------------
139 dummy | regress_foreign_data_user | - | - | | | useless
140 foo | regress_foreign_data_user | - | - | | (b '3', c '4') |
141 postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
142 (3 rows)
144 ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2');
145 ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR
146 ERROR: option "b" provided more than once
147 \dew+
148 List of foreign-data wrappers
149 Name | Owner | Handler | Validator | Access privileges | FDW options | Description
150 ------------+---------------------------+---------+--------------------------+-------------------+-----------------------+-------------
151 dummy | regress_foreign_data_user | - | - | | | useless
152 foo | regress_foreign_data_user | - | - | | (b '3', c '4', a '2') |
153 postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
154 (3 rows)
156 SET ROLE regress_test_role;
157 ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5'); -- ERROR
158 ERROR: permission denied to alter foreign-data wrapper "foo"
159 HINT: Must be superuser to alter a foreign-data wrapper.
160 SET ROLE regress_test_role_super;
161 ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5');
162 \dew+
163 List of foreign-data wrappers
164 Name | Owner | Handler | Validator | Access privileges | FDW options | Description
165 ------------+---------------------------+---------+--------------------------+-------------------+------------------------------+-------------
166 dummy | regress_foreign_data_user | - | - | | | useless
167 foo | regress_foreign_data_user | - | - | | (b '3', c '4', a '2', d '5') |
168 postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
169 (3 rows)
171 ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR
172 ERROR: permission denied to change owner of foreign-data wrapper "foo"
173 HINT: The owner of a foreign-data wrapper must be a superuser.
174 ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role_super;
175 ALTER ROLE regress_test_role_super NOSUPERUSER;
176 SET ROLE regress_test_role_super;
177 ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD e '6'); -- ERROR
178 ERROR: permission denied to alter foreign-data wrapper "foo"
179 HINT: Must be superuser to alter a foreign-data wrapper.
180 RESET ROLE;
181 \dew+
182 List of foreign-data wrappers
183 Name | Owner | Handler | Validator | Access privileges | FDW options | Description
184 ------------+---------------------------+---------+--------------------------+-------------------+------------------------------+-------------
185 dummy | regress_foreign_data_user | - | - | | | useless
186 foo | regress_test_role_super | - | - | | (b '3', c '4', a '2', d '5') |
187 postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
188 (3 rows)
190 ALTER FOREIGN DATA WRAPPER foo RENAME TO foo1;
191 \dew+
192 List of foreign-data wrappers
193 Name | Owner | Handler | Validator | Access privileges | FDW options | Description
194 ------------+---------------------------+---------+--------------------------+-------------------+------------------------------+-------------
195 dummy | regress_foreign_data_user | - | - | | | useless
196 foo1 | regress_test_role_super | - | - | | (b '3', c '4', a '2', d '5') |
197 postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
198 (3 rows)
200 ALTER FOREIGN DATA WRAPPER foo1 RENAME TO foo;
201 -- HANDLER related checks
202 ALTER FOREIGN DATA WRAPPER foo HANDLER invalid_fdw_handler; -- ERROR
203 ERROR: function invalid_fdw_handler must return type fdw_handler
204 ALTER FOREIGN DATA WRAPPER foo HANDLER test_fdw_handler HANDLER anything; -- ERROR
205 ERROR: conflicting or redundant options
206 LINE 1: ...FOREIGN DATA WRAPPER foo HANDLER test_fdw_handler HANDLER an...
207 ^
208 ALTER FOREIGN DATA WRAPPER foo HANDLER test_fdw_handler;
209 WARNING: changing the foreign-data wrapper handler can change behavior of existing foreign tables
210 DROP FUNCTION invalid_fdw_handler();
211 -- DROP FOREIGN DATA WRAPPER
212 DROP FOREIGN DATA WRAPPER nonexistent; -- ERROR
213 ERROR: foreign-data wrapper "nonexistent" does not exist
214 DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent;
215 NOTICE: foreign-data wrapper "nonexistent" does not exist, skipping
216 \dew+
217 List of foreign-data wrappers
218 Name | Owner | Handler | Validator | Access privileges | FDW options | Description
219 ------------+---------------------------+------------------+--------------------------+-------------------+------------------------------+-------------
220 dummy | regress_foreign_data_user | - | - | | | useless
221 foo | regress_test_role_super | test_fdw_handler | - | | (b '3', c '4', a '2', d '5') |
222 postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
223 (3 rows)
225 DROP ROLE regress_test_role_super; -- ERROR
226 ERROR: role "regress_test_role_super" cannot be dropped because some objects depend on it
227 DETAIL: owner of foreign-data wrapper foo
228 SET ROLE regress_test_role_super;
229 DROP FOREIGN DATA WRAPPER foo;
230 RESET ROLE;
231 DROP ROLE regress_test_role_super;
232 \dew+
233 List of foreign-data wrappers
234 Name | Owner | Handler | Validator | Access privileges | FDW options | Description
235 ------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
236 dummy | regress_foreign_data_user | - | - | | | useless
237 postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
238 (2 rows)
240 CREATE FOREIGN DATA WRAPPER foo;
241 CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
242 COMMENT ON SERVER s1 IS 'foreign server';
243 CREATE USER MAPPING FOR current_user SERVER s1;
244 CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR
245 ERROR: user mapping for "regress_foreign_data_user" already exists for server "s1"
246 CREATE USER MAPPING IF NOT EXISTS FOR current_user SERVER s1; -- NOTICE
247 NOTICE: user mapping for "regress_foreign_data_user" already exists for server "s1", skipping
248 \dew+
249 List of foreign-data wrappers
250 Name | Owner | Handler | Validator | Access privileges | FDW options | Description
251 ------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
252 dummy | regress_foreign_data_user | - | - | | | useless
253 foo | regress_foreign_data_user | - | - | | |
254 postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
255 (3 rows)
257 \des+
258 List of foreign servers
259 Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
260 ------+---------------------------+----------------------+-------------------+------+---------+-------------+----------------
261 s1 | regress_foreign_data_user | foo | | | | | foreign server
262 (1 row)
264 \deu+
265 List of user mappings
266 Server | User name | FDW options
267 --------+---------------------------+-------------
268 s1 | regress_foreign_data_user |
269 (1 row)
271 DROP FOREIGN DATA WRAPPER foo; -- ERROR
272 ERROR: cannot drop foreign-data wrapper foo because other objects depend on it
273 DETAIL: server s1 depends on foreign-data wrapper foo
274 user mapping for regress_foreign_data_user on server s1 depends on server s1
275 HINT: Use DROP ... CASCADE to drop the dependent objects too.
276 SET ROLE regress_test_role;
277 DROP FOREIGN DATA WRAPPER foo CASCADE; -- ERROR
278 ERROR: must be owner of foreign-data wrapper foo
279 RESET ROLE;
280 DROP FOREIGN DATA WRAPPER foo CASCADE;
281 NOTICE: drop cascades to 2 other objects
282 DETAIL: drop cascades to server s1
283 drop cascades to user mapping for regress_foreign_data_user on server s1
284 \dew+
285 List of foreign-data wrappers
286 Name | Owner | Handler | Validator | Access privileges | FDW options | Description
287 ------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
288 dummy | regress_foreign_data_user | - | - | | | useless
289 postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
290 (2 rows)
292 \des+
293 List of foreign servers
294 Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
295 ------+-------+----------------------+-------------------+------+---------+-------------+-------------
296 (0 rows)
298 \deu+
299 List of user mappings
300 Server | User name | FDW options
301 --------+-----------+-------------
302 (0 rows)
304 -- exercise CREATE SERVER
305 CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
306 ERROR: foreign-data wrapper "foo" does not exist
307 CREATE FOREIGN DATA WRAPPER foo OPTIONS ("test wrapper" 'true');
308 CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
309 CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
310 ERROR: server "s1" already exists
311 CREATE SERVER IF NOT EXISTS s1 FOREIGN DATA WRAPPER foo; -- No ERROR, just NOTICE
312 NOTICE: server "s1" already exists, skipping
313 CREATE SERVER s2 FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
314 CREATE SERVER s3 TYPE 'oracle' FOREIGN DATA WRAPPER foo;
315 CREATE SERVER s4 TYPE 'oracle' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
316 CREATE SERVER s5 VERSION '15.0' FOREIGN DATA WRAPPER foo;
317 CREATE SERVER s6 VERSION '16.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
318 CREATE SERVER s7 TYPE 'oracle' VERSION '17.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
319 CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (foo '1'); -- ERROR
320 ERROR: invalid option "foo"
321 HINT: Valid options in this context are: authtype, service, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib
322 CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db');
323 \des+
324 List of foreign servers
325 Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
326 ------+---------------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------
327 s1 | regress_foreign_data_user | foo | | | | |
328 s2 | regress_foreign_data_user | foo | | | | (host 'a', dbname 'b') |
329 s3 | regress_foreign_data_user | foo | | oracle | | |
330 s4 | regress_foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
331 s5 | regress_foreign_data_user | foo | | | 15.0 | |
332 s6 | regress_foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') |
333 s7 | regress_foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
334 s8 | regress_foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
335 (8 rows)
337 SET ROLE regress_test_role;
338 CREATE SERVER t1 FOREIGN DATA WRAPPER foo; -- ERROR: no usage on FDW
339 ERROR: permission denied for foreign-data wrapper foo
340 RESET ROLE;
341 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role;
342 SET ROLE regress_test_role;
343 CREATE SERVER t1 FOREIGN DATA WRAPPER foo;
344 RESET ROLE;
345 \des+
346 List of foreign servers
347 Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
348 ------+---------------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------
349 s1 | regress_foreign_data_user | foo | | | | |
350 s2 | regress_foreign_data_user | foo | | | | (host 'a', dbname 'b') |
351 s3 | regress_foreign_data_user | foo | | oracle | | |
352 s4 | regress_foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
353 s5 | regress_foreign_data_user | foo | | | 15.0 | |
354 s6 | regress_foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') |
355 s7 | regress_foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
356 s8 | regress_foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
357 t1 | regress_test_role | foo | | | | |
358 (9 rows)
360 REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;
361 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect;
362 SET ROLE regress_test_role;
363 CREATE SERVER t2 FOREIGN DATA WRAPPER foo; -- ERROR
364 ERROR: permission denied for foreign-data wrapper foo
365 RESET ROLE;
366 GRANT regress_test_indirect TO regress_test_role;
367 SET ROLE regress_test_role;
368 CREATE SERVER t2 FOREIGN DATA WRAPPER foo;
369 \des+
370 List of foreign servers
371 Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
372 ------+---------------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------
373 s1 | regress_foreign_data_user | foo | | | | |
374 s2 | regress_foreign_data_user | foo | | | | (host 'a', dbname 'b') |
375 s3 | regress_foreign_data_user | foo | | oracle | | |
376 s4 | regress_foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
377 s5 | regress_foreign_data_user | foo | | | 15.0 | |
378 s6 | regress_foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') |
379 s7 | regress_foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
380 s8 | regress_foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
381 t1 | regress_test_role | foo | | | | |
382 t2 | regress_test_role | foo | | | | |
383 (10 rows)
385 RESET ROLE;
386 REVOKE regress_test_indirect FROM regress_test_role;
387 -- ALTER SERVER
388 ALTER SERVER s0; -- ERROR
389 ERROR: syntax error at or near ";"
390 LINE 1: ALTER SERVER s0;
391 ^
392 ALTER SERVER s0 OPTIONS (a '1'); -- ERROR
393 ERROR: server "s0" does not exist
394 ALTER SERVER s1 VERSION '1.0' OPTIONS (servername 's1');
395 ALTER SERVER s2 VERSION '1.1';
396 ALTER SERVER s3 OPTIONS ("tns name" 'orcl', port '1521');
397 GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role;
398 GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION;
399 \des+
400 List of foreign servers
401 Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
402 ------+---------------------------+----------------------+-------------------------------------------------------+--------+---------+-----------------------------------+-------------
403 s1 | regress_foreign_data_user | foo | regress_foreign_data_user=U/regress_foreign_data_user+| | 1.0 | (servername 's1') |
404 | | | regress_test_role=U/regress_foreign_data_user | | | |
405 s2 | regress_foreign_data_user | foo | | | 1.1 | (host 'a', dbname 'b') |
406 s3 | regress_foreign_data_user | foo | | oracle | | ("tns name" 'orcl', port '1521') |
407 s4 | regress_foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
408 s5 | regress_foreign_data_user | foo | | | 15.0 | |
409 s6 | regress_foreign_data_user | foo | regress_foreign_data_user=U/regress_foreign_data_user+| | 16.0 | (host 'a', dbname 'b') |
410 | | | regress_test_role2=U*/regress_foreign_data_user | | | |
411 s7 | regress_foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
412 s8 | regress_foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
413 t1 | regress_test_role | foo | | | | |
414 t2 | regress_test_role | foo | | | | |
415 (10 rows)
417 SET ROLE regress_test_role;
418 ALTER SERVER s1 VERSION '1.1'; -- ERROR
419 ERROR: must be owner of foreign server s1
420 ALTER SERVER s1 OWNER TO regress_test_role; -- ERROR
421 ERROR: must be owner of foreign server s1
422 RESET ROLE;
423 ALTER SERVER s1 OWNER TO regress_test_role;
424 GRANT regress_test_role2 TO regress_test_role;
425 SET ROLE regress_test_role;
426 ALTER SERVER s1 VERSION '1.1';
427 ALTER SERVER s1 OWNER TO regress_test_role2; -- ERROR
428 ERROR: permission denied for foreign-data wrapper foo
429 RESET ROLE;
430 ALTER SERVER s8 OPTIONS (foo '1'); -- ERROR option validation
431 ERROR: invalid option "foo"
432 HINT: Valid options in this context are: authtype, service, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib
433 ALTER SERVER s8 OPTIONS (connect_timeout '30', SET dbname 'db1', DROP host);
434 SET ROLE regress_test_role;
435 ALTER SERVER s1 OWNER TO regress_test_indirect; -- ERROR
436 ERROR: must be member of role "regress_test_indirect"
437 RESET ROLE;
438 GRANT regress_test_indirect TO regress_test_role;
439 SET ROLE regress_test_role;
440 ALTER SERVER s1 OWNER TO regress_test_indirect;
441 RESET ROLE;
442 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect;
443 SET ROLE regress_test_role;
444 ALTER SERVER s1 OWNER TO regress_test_indirect;
445 RESET ROLE;
446 DROP ROLE regress_test_indirect; -- ERROR
447 ERROR: role "regress_test_indirect" cannot be dropped because some objects depend on it
448 DETAIL: privileges for foreign-data wrapper foo
449 owner of server s1
450 \des+
451 List of foreign servers
452 Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
453 ------+---------------------------+----------------------+-------------------------------------------------------+--------+---------+--------------------------------------+-------------
454 s1 | regress_test_indirect | foo | regress_test_indirect=U/regress_test_indirect | | 1.1 | (servername 's1') |
455 s2 | regress_foreign_data_user | foo | | | 1.1 | (host 'a', dbname 'b') |
456 s3 | regress_foreign_data_user | foo | | oracle | | ("tns name" 'orcl', port '1521') |
457 s4 | regress_foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
458 s5 | regress_foreign_data_user | foo | | | 15.0 | |
459 s6 | regress_foreign_data_user | foo | regress_foreign_data_user=U/regress_foreign_data_user+| | 16.0 | (host 'a', dbname 'b') |
460 | | | regress_test_role2=U*/regress_foreign_data_user | | | |
461 s7 | regress_foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
462 s8 | regress_foreign_data_user | postgresql | | | | (dbname 'db1', connect_timeout '30') |
463 t1 | regress_test_role | foo | | | | |
464 t2 | regress_test_role | foo | | | | |
465 (10 rows)
467 ALTER SERVER s8 RENAME to s8new;
468 \des+
469 List of foreign servers
470 Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
471 -------+---------------------------+----------------------+-------------------------------------------------------+--------+---------+--------------------------------------+-------------
472 s1 | regress_test_indirect | foo | regress_test_indirect=U/regress_test_indirect | | 1.1 | (servername 's1') |
473 s2 | regress_foreign_data_user | foo | | | 1.1 | (host 'a', dbname 'b') |
474 s3 | regress_foreign_data_user | foo | | oracle | | ("tns name" 'orcl', port '1521') |
475 s4 | regress_foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
476 s5 | regress_foreign_data_user | foo | | | 15.0 | |
477 s6 | regress_foreign_data_user | foo | regress_foreign_data_user=U/regress_foreign_data_user+| | 16.0 | (host 'a', dbname 'b') |
478 | | | regress_test_role2=U*/regress_foreign_data_user | | | |
479 s7 | regress_foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
480 s8new | regress_foreign_data_user | postgresql | | | | (dbname 'db1', connect_timeout '30') |
481 t1 | regress_test_role | foo | | | | |
482 t2 | regress_test_role | foo | | | | |
483 (10 rows)
485 ALTER SERVER s8new RENAME to s8;
486 -- DROP SERVER
487 DROP SERVER nonexistent; -- ERROR
488 ERROR: server "nonexistent" does not exist
489 DROP SERVER IF EXISTS nonexistent;
490 NOTICE: server "nonexistent" does not exist, skipping
491 \des
492 List of foreign servers
493 Name | Owner | Foreign-data wrapper
494 ------+---------------------------+----------------------
495 s1 | regress_test_indirect | foo
496 s2 | regress_foreign_data_user | foo
497 s3 | regress_foreign_data_user | foo
498 s4 | regress_foreign_data_user | foo
499 s5 | regress_foreign_data_user | foo
500 s6 | regress_foreign_data_user | foo
501 s7 | regress_foreign_data_user | foo
502 s8 | regress_foreign_data_user | postgresql
503 t1 | regress_test_role | foo
504 t2 | regress_test_role | foo
505 (10 rows)
507 SET ROLE regress_test_role;
508 DROP SERVER s2; -- ERROR
509 ERROR: must be owner of foreign server s2
510 DROP SERVER s1;
511 RESET ROLE;
512 \des
513 List of foreign servers
514 Name | Owner | Foreign-data wrapper
515 ------+---------------------------+----------------------
516 s2 | regress_foreign_data_user | foo
517 s3 | regress_foreign_data_user | foo
518 s4 | regress_foreign_data_user | foo
519 s5 | regress_foreign_data_user | foo
520 s6 | regress_foreign_data_user | foo
521 s7 | regress_foreign_data_user | foo
522 s8 | regress_foreign_data_user | postgresql
523 t1 | regress_test_role | foo
524 t2 | regress_test_role | foo
525 (9 rows)
527 ALTER SERVER s2 OWNER TO regress_test_role;
528 SET ROLE regress_test_role;
529 DROP SERVER s2;
530 RESET ROLE;
531 \des
532 List of foreign servers
533 Name | Owner | Foreign-data wrapper
534 ------+---------------------------+----------------------
535 s3 | regress_foreign_data_user | foo
536 s4 | regress_foreign_data_user | foo
537 s5 | regress_foreign_data_user | foo
538 s6 | regress_foreign_data_user | foo
539 s7 | regress_foreign_data_user | foo
540 s8 | regress_foreign_data_user | postgresql
541 t1 | regress_test_role | foo
542 t2 | regress_test_role | foo
543 (8 rows)
545 CREATE USER MAPPING FOR current_user SERVER s3;
546 \deu
547 List of user mappings
548 Server | User name
549 --------+---------------------------
550 s3 | regress_foreign_data_user
551 (1 row)
553 DROP SERVER s3; -- ERROR
554 ERROR: cannot drop server s3 because other objects depend on it
555 DETAIL: user mapping for regress_foreign_data_user on server s3 depends on server s3
556 HINT: Use DROP ... CASCADE to drop the dependent objects too.
557 DROP SERVER s3 CASCADE;
558 NOTICE: drop cascades to user mapping for regress_foreign_data_user on server s3
559 \des
560 List of foreign servers
561 Name | Owner | Foreign-data wrapper
562 ------+---------------------------+----------------------
563 s4 | regress_foreign_data_user | foo
564 s5 | regress_foreign_data_user | foo
565 s6 | regress_foreign_data_user | foo
566 s7 | regress_foreign_data_user | foo
567 s8 | regress_foreign_data_user | postgresql
568 t1 | regress_test_role | foo
569 t2 | regress_test_role | foo
570 (7 rows)
572 \deu
573 List of user mappings
574 Server | User name
575 --------+-----------
576 (0 rows)
578 -- CREATE USER MAPPING
579 CREATE USER MAPPING FOR regress_test_missing_role SERVER s1; -- ERROR
580 ERROR: role "regress_test_missing_role" does not exist
581 CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR
582 ERROR: server "s1" does not exist
583 CREATE USER MAPPING FOR current_user SERVER s4;
584 CREATE USER MAPPING FOR user SERVER s4; -- ERROR duplicate
585 ERROR: user mapping for "regress_foreign_data_user" already exists for server "s4"
586 CREATE USER MAPPING FOR public SERVER s4 OPTIONS ("this mapping" 'is public');
587 CREATE USER MAPPING FOR user SERVER s8 OPTIONS (username 'test', password 'secret'); -- ERROR
588 ERROR: invalid option "username"
589 HINT: Valid options in this context are: user, password
590 CREATE USER MAPPING FOR user SERVER s8 OPTIONS (user 'test', password 'secret');
591 ALTER SERVER s5 OWNER TO regress_test_role;
592 ALTER SERVER s6 OWNER TO regress_test_indirect;
593 SET ROLE regress_test_role;
594 CREATE USER MAPPING FOR current_user SERVER s5;
595 CREATE USER MAPPING FOR current_user SERVER s6 OPTIONS (username 'test');
596 CREATE USER MAPPING FOR current_user SERVER s7; -- ERROR
597 ERROR: permission denied for foreign server s7
598 CREATE USER MAPPING FOR public SERVER s8; -- ERROR
599 ERROR: must be owner of foreign server s8
600 RESET ROLE;
601 ALTER SERVER t1 OWNER TO regress_test_indirect;
602 SET ROLE regress_test_role;
603 CREATE USER MAPPING FOR current_user SERVER t1 OPTIONS (username 'bob', password 'boo');
604 CREATE USER MAPPING FOR public SERVER t1;
605 RESET ROLE;
606 \deu
607 List of user mappings
608 Server | User name
609 --------+---------------------------
610 s4 | public
611 s4 | regress_foreign_data_user
612 s5 | regress_test_role
613 s6 | regress_test_role
614 s8 | regress_foreign_data_user
615 t1 | public
616 t1 | regress_test_role
617 (7 rows)
619 -- ALTER USER MAPPING
620 ALTER USER MAPPING FOR regress_test_missing_role SERVER s4 OPTIONS (gotcha 'true'); -- ERROR
621 ERROR: role "regress_test_missing_role" does not exist
622 ALTER USER MAPPING FOR user SERVER ss4 OPTIONS (gotcha 'true'); -- ERROR
623 ERROR: server "ss4" does not exist
624 ALTER USER MAPPING FOR public SERVER s5 OPTIONS (gotcha 'true'); -- ERROR
625 ERROR: user mapping for "public" does not exist for server "s5"
626 ALTER USER MAPPING FOR current_user SERVER s8 OPTIONS (username 'test'); -- ERROR
627 ERROR: invalid option "username"
628 HINT: Valid options in this context are: user, password
629 ALTER USER MAPPING FOR current_user SERVER s8 OPTIONS (DROP user, SET password 'public');
630 SET ROLE regress_test_role;
631 ALTER USER MAPPING FOR current_user SERVER s5 OPTIONS (ADD modified '1');
632 ALTER USER MAPPING FOR public SERVER s4 OPTIONS (ADD modified '1'); -- ERROR
633 ERROR: must be owner of foreign server s4
634 ALTER USER MAPPING FOR public SERVER t1 OPTIONS (ADD modified '1');
635 RESET ROLE;
636 \deu+
637 List of user mappings
638 Server | User name | FDW options
639 --------+---------------------------+----------------------------------
640 s4 | public | ("this mapping" 'is public')
641 s4 | regress_foreign_data_user |
642 s5 | regress_test_role | (modified '1')
643 s6 | regress_test_role | (username 'test')
644 s8 | regress_foreign_data_user | (password 'public')
645 t1 | public | (modified '1')
646 t1 | regress_test_role | (username 'bob', password 'boo')
647 (7 rows)
649 -- DROP USER MAPPING
650 DROP USER MAPPING FOR regress_test_missing_role SERVER s4; -- ERROR
651 ERROR: role "regress_test_missing_role" does not exist
652 DROP USER MAPPING FOR user SERVER ss4;
653 ERROR: server "ss4" does not exist
654 DROP USER MAPPING FOR public SERVER s7; -- ERROR
655 ERROR: user mapping for "public" does not exist for server "s7"
656 DROP USER MAPPING IF EXISTS FOR regress_test_missing_role SERVER s4;
657 NOTICE: role "regress_test_missing_role" does not exist, skipping
658 DROP USER MAPPING IF EXISTS FOR user SERVER ss4;
659 NOTICE: server "ss4" does not exist, skipping
660 DROP USER MAPPING IF EXISTS FOR public SERVER s7;
661 NOTICE: user mapping for "public" does not exist for server "s7", skipping
662 CREATE USER MAPPING FOR public SERVER s8;
663 SET ROLE regress_test_role;
664 DROP USER MAPPING FOR public SERVER s8; -- ERROR
665 ERROR: must be owner of foreign server s8
666 RESET ROLE;
667 DROP SERVER s7;
668 \deu
669 List of user mappings
670 Server | User name
671 --------+---------------------------
672 s4 | public
673 s4 | regress_foreign_data_user
674 s5 | regress_test_role
675 s6 | regress_test_role
676 s8 | public
677 s8 | regress_foreign_data_user
678 t1 | public
679 t1 | regress_test_role
680 (8 rows)
682 -- CREATE FOREIGN TABLE
683 CREATE SCHEMA foreign_schema;
684 CREATE SERVER s0 FOREIGN DATA WRAPPER dummy;
685 CREATE FOREIGN TABLE ft1 (); -- ERROR
686 ERROR: syntax error at or near ";"
687 LINE 1: CREATE FOREIGN TABLE ft1 ();
688 ^
689 CREATE FOREIGN TABLE ft1 () SERVER no_server; -- ERROR
690 ERROR: server "no_server" does not exist
691 CREATE FOREIGN TABLE ft1 (
692 c1 integer OPTIONS ("param 1" 'val1') PRIMARY KEY,
693 c2 text OPTIONS (param2 'val2', param3 'val3'),
694 c3 date
695 ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR
696 ERROR: primary key constraints are not supported on foreign tables
697 LINE 2: c1 integer OPTIONS ("param 1" 'val1') PRIMARY KEY,
698 ^
699 CREATE TABLE ref_table (id integer PRIMARY KEY);
700 CREATE FOREIGN TABLE ft1 (
701 c1 integer OPTIONS ("param 1" 'val1') REFERENCES ref_table (id),
702 c2 text OPTIONS (param2 'val2', param3 'val3'),
703 c3 date
704 ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR
705 ERROR: foreign key constraints are not supported on foreign tables
706 LINE 2: c1 integer OPTIONS ("param 1" 'val1') REFERENCES ref_table ...
707 ^
708 DROP TABLE ref_table;
709 CREATE FOREIGN TABLE ft1 (
710 c1 integer OPTIONS ("param 1" 'val1') NOT NULL,
711 c2 text OPTIONS (param2 'val2', param3 'val3'),
712 c3 date,
713 UNIQUE (c3)
714 ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR
715 ERROR: unique constraints are not supported on foreign tables
716 LINE 5: UNIQUE (c3)
717 ^
718 CREATE FOREIGN TABLE ft1 (
719 c1 integer OPTIONS ("param 1" 'val1') NOT NULL,
720 c2 text OPTIONS (param2 'val2', param3 'val3') CHECK (c2 <> ''),
721 c3 date,
722 CHECK (c3 BETWEEN '1994-01-01'::date AND '1994-01-31'::date)
723 ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
724 COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
725 COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
726 \d+ ft1
727 Foreign table "public.ft1"
728 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
729 --------+---------+-----------+----------+---------+--------------------------------+----------+--------------+-------------
730 c1 | integer | | not null | | ("param 1" 'val1') | plain | | ft1.c1
731 c2 | text | | | | (param2 'val2', param3 'val3') | extended | |
732 c3 | date | | | | | plain | |
733 Check constraints:
734 "ft1_c2_check" CHECK (c2 <> ''::text)
735 "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date)
736 Server: s0
737 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
739 \det+
740 List of foreign tables
741 Schema | Table | Server | FDW options | Description
742 --------+-------+--------+-------------------------------------------------+-------------
743 public | ft1 | s0 | (delimiter ',', quote '"', "be quoted" 'value') | ft1
744 (1 row)
746 CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR
747 ERROR: cannot create index on relation "ft1"
748 DETAIL: This operation is not supported for foreign tables.
749 SELECT * FROM ft1; -- ERROR
750 ERROR: foreign-data wrapper "dummy" has no handler
751 EXPLAIN SELECT * FROM ft1; -- ERROR
752 ERROR: foreign-data wrapper "dummy" has no handler
753 CREATE TABLE lt1 (a INT) PARTITION BY RANGE (a);
754 CREATE FOREIGN TABLE ft_part1
755 PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) SERVER s0;
756 CREATE INDEX ON lt1 (a); -- skips partition
757 CREATE UNIQUE INDEX ON lt1 (a); -- ERROR
758 ERROR: cannot create unique index on partitioned table "lt1"
759 DETAIL: Table "lt1" contains partitions that are foreign tables.
760 ALTER TABLE lt1 ADD PRIMARY KEY (a); -- ERROR
761 ERROR: cannot create unique index on partitioned table "lt1"
762 DETAIL: Table "lt1" contains partitions that are foreign tables.
763 DROP TABLE lt1;
764 CREATE TABLE lt1 (a INT) PARTITION BY RANGE (a);
765 CREATE INDEX ON lt1 (a);
766 CREATE FOREIGN TABLE ft_part1
767 PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) SERVER s0;
768 CREATE FOREIGN TABLE ft_part2 (a INT) SERVER s0;
769 ALTER TABLE lt1 ATTACH PARTITION ft_part2 FOR VALUES FROM (1000) TO (2000);
770 DROP FOREIGN TABLE ft_part1, ft_part2;
771 CREATE UNIQUE INDEX ON lt1 (a);
772 ALTER TABLE lt1 ADD PRIMARY KEY (a);
773 CREATE FOREIGN TABLE ft_part1
774 PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) SERVER s0; -- ERROR
775 ERROR: cannot create foreign partition of partitioned table "lt1"
776 DETAIL: Table "lt1" contains indexes that are unique.
777 CREATE FOREIGN TABLE ft_part2 (a INT NOT NULL) SERVER s0;
778 ALTER TABLE lt1 ATTACH PARTITION ft_part2
779 FOR VALUES FROM (1000) TO (2000); -- ERROR
780 ERROR: cannot attach foreign table "ft_part2" as partition of partitioned table "lt1"
781 DETAIL: Partitioned table "lt1" contains unique indexes.
782 DROP TABLE lt1;
783 DROP FOREIGN TABLE ft_part2;
784 CREATE TABLE lt1 (a INT) PARTITION BY RANGE (a);
785 CREATE INDEX ON lt1 (a);
786 CREATE TABLE lt1_part1
787 PARTITION OF lt1 FOR VALUES FROM (0) TO (1000)
788 PARTITION BY RANGE (a);
789 CREATE FOREIGN TABLE ft_part_1_1
790 PARTITION OF lt1_part1 FOR VALUES FROM (0) TO (100) SERVER s0;
791 CREATE FOREIGN TABLE ft_part_1_2 (a INT) SERVER s0;
792 ALTER TABLE lt1_part1 ATTACH PARTITION ft_part_1_2 FOR VALUES FROM (100) TO (200);
793 CREATE UNIQUE INDEX ON lt1 (a);
794 ERROR: cannot create unique index on partitioned table "lt1"
795 DETAIL: Table "lt1" contains partitions that are foreign tables.
796 ALTER TABLE lt1 ADD PRIMARY KEY (a);
797 ERROR: cannot create unique index on partitioned table "lt1_part1"
798 DETAIL: Table "lt1_part1" contains partitions that are foreign tables.
799 DROP FOREIGN TABLE ft_part_1_1, ft_part_1_2;
800 CREATE UNIQUE INDEX ON lt1 (a);
801 ALTER TABLE lt1 ADD PRIMARY KEY (a);
802 CREATE FOREIGN TABLE ft_part_1_1
803 PARTITION OF lt1_part1 FOR VALUES FROM (0) TO (100) SERVER s0;
804 ERROR: cannot create foreign partition of partitioned table "lt1_part1"
805 DETAIL: Table "lt1_part1" contains indexes that are unique.
806 CREATE FOREIGN TABLE ft_part_1_2 (a INT NOT NULL) SERVER s0;
807 ALTER TABLE lt1_part1 ATTACH PARTITION ft_part_1_2 FOR VALUES FROM (100) TO (200);
808 ERROR: cannot attach foreign table "ft_part_1_2" as partition of partitioned table "lt1_part1"
809 DETAIL: Partitioned table "lt1_part1" contains unique indexes.
810 DROP TABLE lt1;
811 DROP FOREIGN TABLE ft_part_1_2;
812 -- ALTER FOREIGN TABLE
813 COMMENT ON FOREIGN TABLE ft1 IS 'foreign table';
814 COMMENT ON FOREIGN TABLE ft1 IS NULL;
815 COMMENT ON COLUMN ft1.c1 IS 'foreign column';
816 COMMENT ON COLUMN ft1.c1 IS NULL;
817 ALTER FOREIGN TABLE ft1 ADD COLUMN c4 integer;
818 ALTER FOREIGN TABLE ft1 ADD COLUMN c5 integer DEFAULT 0;
819 ALTER FOREIGN TABLE ft1 ADD COLUMN c6 integer;
820 ALTER FOREIGN TABLE ft1 ADD COLUMN c7 integer NOT NULL;
821 ALTER FOREIGN TABLE ft1 ADD COLUMN c8 integer;
822 ALTER FOREIGN TABLE ft1 ADD COLUMN c9 integer;
823 ALTER FOREIGN TABLE ft1 ADD COLUMN c10 integer OPTIONS (p1 'v1');
824 ALTER FOREIGN TABLE ft1 ALTER COLUMN c4 SET DEFAULT 0;
825 ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 DROP DEFAULT;
826 ALTER FOREIGN TABLE ft1 ALTER COLUMN c6 SET NOT NULL;
827 ALTER FOREIGN TABLE ft1 ALTER COLUMN c7 DROP NOT NULL;
828 ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10) USING '0'; -- ERROR
829 ERROR: "ft1" is not a table
830 ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10);
831 ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE text;
832 ALTER FOREIGN TABLE ft1 ALTER COLUMN xmin OPTIONS (ADD p1 'v1'); -- ERROR
833 ERROR: cannot alter system column "xmin"
834 ALTER FOREIGN TABLE ft1 ALTER COLUMN c7 OPTIONS (ADD p1 'v1', ADD p2 'v2'),
835 ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2');
836 ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1);
837 ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 SET STATISTICS 10000;
838 ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 SET (n_distinct = 100);
839 ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STATISTICS -1;
840 ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STORAGE PLAIN;
841 \d+ ft1
842 Foreign table "public.ft1"
843 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
844 --------+---------+-----------+----------+---------+--------------------------------+----------+--------------+-------------
845 c1 | integer | | not null | | ("param 1" 'val1') | plain | 10000 |
846 c2 | text | | | | (param2 'val2', param3 'val3') | extended | |
847 c3 | date | | | | | plain | |
848 c4 | integer | | | 0 | | plain | |
849 c5 | integer | | | | | plain | |
850 c6 | integer | | not null | | | plain | |
851 c7 | integer | | | | (p1 'v1', p2 'v2') | plain | |
852 c8 | text | | | | (p2 'V2') | plain | |
853 c9 | integer | | | | | plain | |
854 c10 | integer | | | | (p1 'v1') | plain | |
855 Check constraints:
856 "ft1_c2_check" CHECK (c2 <> ''::text)
857 "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date)
858 Server: s0
859 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
861 -- can't change the column type if it's used elsewhere
862 CREATE TABLE use_ft1_column_type (x ft1);
863 ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR
864 ERROR: cannot alter foreign table "ft1" because column "use_ft1_column_type.x" uses its row type
865 DROP TABLE use_ft1_column_type;
866 ALTER FOREIGN TABLE ft1 ADD PRIMARY KEY (c7); -- ERROR
867 ERROR: primary key constraints are not supported on foreign tables
868 LINE 1: ALTER FOREIGN TABLE ft1 ADD PRIMARY KEY (c7);
869 ^
870 ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NOT VALID;
871 ALTER FOREIGN TABLE ft1 ALTER CONSTRAINT ft1_c9_check DEFERRABLE; -- ERROR
872 ERROR: ALTER action ALTER CONSTRAINT cannot be performed on relation "ft1"
873 DETAIL: This operation is not supported for foreign tables.
874 ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c9_check;
875 ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR
876 ERROR: constraint "no_const" of relation "ft1" does not exist
877 ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const;
878 NOTICE: constraint "no_const" of relation "ft1" does not exist, skipping
879 ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role;
880 ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@');
881 ALTER FOREIGN TABLE ft1 DROP COLUMN no_column; -- ERROR
882 ERROR: column "no_column" of relation "ft1" does not exist
883 ALTER FOREIGN TABLE ft1 DROP COLUMN IF EXISTS no_column;
884 NOTICE: column "no_column" of relation "ft1" does not exist, skipping
885 ALTER FOREIGN TABLE ft1 DROP COLUMN c9;
886 ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema;
887 ALTER FOREIGN TABLE ft1 SET TABLESPACE ts; -- ERROR
888 ERROR: relation "ft1" does not exist
889 ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1;
890 ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
891 \d foreign_schema.foreign_table_1
892 Foreign table "foreign_schema.foreign_table_1"
893 Column | Type | Collation | Nullable | Default | FDW options
894 ------------------+---------+-----------+----------+---------+--------------------------------
895 foreign_column_1 | integer | | not null | | ("param 1" 'val1')
896 c2 | text | | | | (param2 'val2', param3 'val3')
897 c3 | date | | | |
898 c4 | integer | | | 0 |
899 c5 | integer | | | |
900 c6 | integer | | not null | |
901 c7 | integer | | | | (p1 'v1', p2 'v2')
902 c8 | text | | | | (p2 'V2')
903 c10 | integer | | | | (p1 'v1')
904 Check constraints:
905 "ft1_c2_check" CHECK (c2 <> ''::text)
906 "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date)
907 Server: s0
908 FDW options: (quote '~', "be quoted" 'value', escape '@')
910 -- alter noexisting table
911 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c4 integer;
912 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
913 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c6 integer;
914 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
915 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c7 integer NOT NULL;
916 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
917 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c8 integer;
918 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
919 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c9 integer;
920 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
921 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c10 integer OPTIONS (p1 'v1');
922 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
923 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c6 SET NOT NULL;
924 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
925 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c7 DROP NOT NULL;
926 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
927 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 TYPE char(10);
928 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
929 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 SET DATA TYPE text;
930 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
931 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c7 OPTIONS (ADD p1 'v1', ADD p2 'v2'),
932 ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2');
933 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
934 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1);
935 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
936 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP CONSTRAINT IF EXISTS no_const;
937 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
938 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP CONSTRAINT ft1_c1_check;
939 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
940 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 OWNER TO regress_test_role;
941 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
942 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@');
943 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
944 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP COLUMN IF EXISTS no_column;
945 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
946 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP COLUMN c9;
947 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
948 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 SET SCHEMA foreign_schema;
949 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
950 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 RENAME c1 TO foreign_column_1;
951 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
952 ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 RENAME TO foreign_table_1;
953 NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
954 -- Information schema
955 SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;
956 foreign_data_wrapper_catalog | foreign_data_wrapper_name | authorization_identifier | library_name | foreign_data_wrapper_language
957 ------------------------------+---------------------------+---------------------------+--------------+-------------------------------
958 regression | dummy | regress_foreign_data_user | | c
959 regression | foo | regress_foreign_data_user | | c
960 regression | postgresql | regress_foreign_data_user | | c
961 (3 rows)
963 SELECT * FROM information_schema.foreign_data_wrapper_options ORDER BY 1, 2, 3;
964 foreign_data_wrapper_catalog | foreign_data_wrapper_name | option_name | option_value
965 ------------------------------+---------------------------+--------------+--------------
966 regression | foo | test wrapper | true
967 (1 row)
969 SELECT * FROM information_schema.foreign_servers ORDER BY 1, 2;
970 foreign_server_catalog | foreign_server_name | foreign_data_wrapper_catalog | foreign_data_wrapper_name | foreign_server_type | foreign_server_version | authorization_identifier
971 ------------------------+---------------------+------------------------------+---------------------------+---------------------+------------------------+---------------------------
972 regression | s0 | regression | dummy | | | regress_foreign_data_user
973 regression | s4 | regression | foo | oracle | | regress_foreign_data_user
974 regression | s5 | regression | foo | | 15.0 | regress_test_role
975 regression | s6 | regression | foo | | 16.0 | regress_test_indirect
976 regression | s8 | regression | postgresql | | | regress_foreign_data_user
977 regression | t1 | regression | foo | | | regress_test_indirect
978 regression | t2 | regression | foo | | | regress_test_role
979 (7 rows)
981 SELECT * FROM information_schema.foreign_server_options ORDER BY 1, 2, 3;
982 foreign_server_catalog | foreign_server_name | option_name | option_value
983 ------------------------+---------------------+-----------------+--------------
984 regression | s4 | dbname | b
985 regression | s4 | host | a
986 regression | s6 | dbname | b
987 regression | s6 | host | a
988 regression | s8 | connect_timeout | 30
989 regression | s8 | dbname | db1
990 (6 rows)
992 SELECT * FROM information_schema.user_mappings ORDER BY lower(authorization_identifier), 2, 3;
993 authorization_identifier | foreign_server_catalog | foreign_server_name
994 ---------------------------+------------------------+---------------------
995 PUBLIC | regression | s4
996 PUBLIC | regression | s8
997 PUBLIC | regression | t1
998 regress_foreign_data_user | regression | s4
999 regress_foreign_data_user | regression | s8
1000 regress_test_role | regression | s5
1001 regress_test_role | regression | s6
1002 regress_test_role | regression | t1
1003 (8 rows)
1005 SELECT * FROM information_schema.user_mapping_options ORDER BY lower(authorization_identifier), 2, 3, 4;
1006 authorization_identifier | foreign_server_catalog | foreign_server_name | option_name | option_value
1007 ---------------------------+------------------------+---------------------+--------------+--------------
1008 PUBLIC | regression | s4 | this mapping | is public
1009 PUBLIC | regression | t1 | modified | 1
1010 regress_foreign_data_user | regression | s8 | password | public
1011 regress_test_role | regression | s5 | modified | 1
1012 regress_test_role | regression | s6 | username | test
1013 regress_test_role | regression | t1 | password | boo
1014 regress_test_role | regression | t1 | username | bob
1015 (7 rows)
1017 SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5;
1018 grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable
1019 ---------------------------+---------------------------+----------------+---------------+-------------+----------------------+----------------+--------------
1020 regress_foreign_data_user | regress_foreign_data_user | regression | | foo | FOREIGN DATA WRAPPER | USAGE | YES
1021 regress_foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO
1022 regress_test_indirect | regress_test_indirect | regression | | s6 | FOREIGN SERVER | USAGE | YES
1023 regress_test_indirect | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES
1024 (4 rows)
1026 SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5;
1027 grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable
1028 ---------------------------+---------------------------+----------------+---------------+-------------+----------------------+----------------+--------------
1029 regress_foreign_data_user | regress_foreign_data_user | regression | | foo | FOREIGN DATA WRAPPER | USAGE | YES
1030 regress_foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO
1031 regress_test_indirect | regress_test_indirect | regression | | s6 | FOREIGN SERVER | USAGE | YES
1032 regress_test_indirect | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES
1033 (4 rows)
1035 SELECT * FROM information_schema.foreign_tables ORDER BY 1, 2, 3;
1036 foreign_table_catalog | foreign_table_schema | foreign_table_name | foreign_server_catalog | foreign_server_name
1037 -----------------------+----------------------+--------------------+------------------------+---------------------
1038 regression | foreign_schema | foreign_table_1 | regression | s0
1039 (1 row)
1041 SELECT * FROM information_schema.foreign_table_options ORDER BY 1, 2, 3, 4;
1042 foreign_table_catalog | foreign_table_schema | foreign_table_name | option_name | option_value
1043 -----------------------+----------------------+--------------------+-------------+--------------
1044 regression | foreign_schema | foreign_table_1 | be quoted | value
1045 regression | foreign_schema | foreign_table_1 | escape | @
1046 regression | foreign_schema | foreign_table_1 | quote | ~
1047 (3 rows)
1049 SET ROLE regress_test_role;
1050 SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;
1051 authorization_identifier | foreign_server_catalog | foreign_server_name | option_name | option_value
1052 --------------------------+------------------------+---------------------+-------------+--------------
1053 PUBLIC | regression | t1 | modified | 1
1054 regress_test_role | regression | s5 | modified | 1
1055 regress_test_role | regression | s6 | username | test
1056 regress_test_role | regression | t1 | password | boo
1057 regress_test_role | regression | t1 | username | bob
1058 (5 rows)
1060 SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5;
1061 grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable
1062 ---------------------------+-----------------------+----------------+---------------+-------------+----------------------+----------------+--------------
1063 regress_foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO
1064 regress_test_indirect | regress_test_indirect | regression | | s6 | FOREIGN SERVER | USAGE | YES
1065 regress_test_indirect | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES
1066 (3 rows)
1068 SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5;
1069 grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable
1070 ---------------------------+-----------------------+----------------+---------------+-------------+----------------------+----------------+--------------
1071 regress_foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO
1072 regress_test_indirect | regress_test_indirect | regression | | s6 | FOREIGN SERVER | USAGE | YES
1073 regress_test_indirect | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES
1074 (3 rows)
1076 DROP USER MAPPING FOR current_user SERVER t1;
1077 SET ROLE regress_test_role2;
1078 SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;
1079 authorization_identifier | foreign_server_catalog | foreign_server_name | option_name | option_value
1080 --------------------------+------------------------+---------------------+-------------+--------------
1081 regress_test_role | regression | s6 | username |
1082 (1 row)
1084 RESET ROLE;
1085 -- has_foreign_data_wrapper_privilege
1086 SELECT has_foreign_data_wrapper_privilege('regress_test_role',
1087 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE');
1088 has_foreign_data_wrapper_privilege
1089 ------------------------------------
1090 t
1091 (1 row)
1093 SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE');
1094 has_foreign_data_wrapper_privilege
1095 ------------------------------------
1096 t
1097 (1 row)
1099 SELECT has_foreign_data_wrapper_privilege(
1100 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'),
1101 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE');
1102 has_foreign_data_wrapper_privilege
1103 ------------------------------------
1104 t
1105 (1 row)
1107 SELECT has_foreign_data_wrapper_privilege(
1108 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE');
1109 has_foreign_data_wrapper_privilege
1110 ------------------------------------
1111 t
1112 (1 row)
1114 SELECT has_foreign_data_wrapper_privilege(
1115 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 'foo', 'USAGE');
1116 has_foreign_data_wrapper_privilege
1117 ------------------------------------
1118 t
1119 (1 row)
1121 SELECT has_foreign_data_wrapper_privilege('foo', 'USAGE');
1122 has_foreign_data_wrapper_privilege
1123 ------------------------------------
1124 t
1125 (1 row)
1127 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role;
1128 SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE');
1129 has_foreign_data_wrapper_privilege
1130 ------------------------------------
1131 t
1132 (1 row)
1134 -- has_server_privilege
1135 SELECT has_server_privilege('regress_test_role',
1136 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE');
1137 has_server_privilege
1138 ----------------------
1139 f
1140 (1 row)
1142 SELECT has_server_privilege('regress_test_role', 's8', 'USAGE');
1143 has_server_privilege
1144 ----------------------
1145 f
1146 (1 row)
1148 SELECT has_server_privilege(
1149 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'),
1150 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE');
1151 has_server_privilege
1152 ----------------------
1153 f
1154 (1 row)
1156 SELECT has_server_privilege(
1157 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE');
1158 has_server_privilege
1159 ----------------------
1160 t
1161 (1 row)
1163 SELECT has_server_privilege(
1164 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 's8', 'USAGE');
1165 has_server_privilege
1166 ----------------------
1167 f
1168 (1 row)
1170 SELECT has_server_privilege('s8', 'USAGE');
1171 has_server_privilege
1172 ----------------------
1173 t
1174 (1 row)
1176 GRANT USAGE ON FOREIGN SERVER s8 TO regress_test_role;
1177 SELECT has_server_privilege('regress_test_role', 's8', 'USAGE');
1178 has_server_privilege
1179 ----------------------
1180 t
1181 (1 row)
1183 REVOKE USAGE ON FOREIGN SERVER s8 FROM regress_test_role;
1184 GRANT USAGE ON FOREIGN SERVER s4 TO regress_test_role;
1185 DROP USER MAPPING FOR public SERVER s4;
1186 ALTER SERVER s6 OPTIONS (DROP host, DROP dbname);
1187 ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (DROP username);
1188 ALTER FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator;
1189 WARNING: changing the foreign-data wrapper validator can cause the options for dependent objects to become invalid
1190 -- Privileges
1191 SET ROLE regress_unprivileged_role;
1192 CREATE FOREIGN DATA WRAPPER foobar; -- ERROR
1193 ERROR: permission denied to create foreign-data wrapper "foobar"
1194 HINT: Must be superuser to create a foreign-data wrapper.
1195 ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR
1196 ERROR: permission denied to alter foreign-data wrapper "foo"
1197 HINT: Must be superuser to alter a foreign-data wrapper.
1198 ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_unprivileged_role; -- ERROR
1199 ERROR: permission denied to change owner of foreign-data wrapper "foo"
1200 HINT: Must be superuser to change owner of a foreign-data wrapper.
1201 DROP FOREIGN DATA WRAPPER foo; -- ERROR
1202 ERROR: must be owner of foreign-data wrapper foo
1203 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR
1204 ERROR: permission denied for foreign-data wrapper foo
1205 CREATE SERVER s9 FOREIGN DATA WRAPPER foo; -- ERROR
1206 ERROR: permission denied for foreign-data wrapper foo
1207 ALTER SERVER s4 VERSION '0.5'; -- ERROR
1208 ERROR: must be owner of foreign server s4
1209 ALTER SERVER s4 OWNER TO regress_unprivileged_role; -- ERROR
1210 ERROR: must be owner of foreign server s4
1211 DROP SERVER s4; -- ERROR
1212 ERROR: must be owner of foreign server s4
1213 GRANT USAGE ON FOREIGN SERVER s4 TO regress_test_role; -- ERROR
1214 ERROR: permission denied for foreign server s4
1215 CREATE USER MAPPING FOR public SERVER s4; -- ERROR
1216 ERROR: must be owner of foreign server s4
1217 ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (gotcha 'true'); -- ERROR
1218 ERROR: must be owner of foreign server s6
1219 DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR
1220 ERROR: must be owner of foreign server s6
1221 RESET ROLE;
1222 GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO regress_unprivileged_role;
1223 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_unprivileged_role WITH GRANT OPTION;
1224 SET ROLE regress_unprivileged_role;
1225 CREATE FOREIGN DATA WRAPPER foobar; -- ERROR
1226 ERROR: permission denied to create foreign-data wrapper "foobar"
1227 HINT: Must be superuser to create a foreign-data wrapper.
1228 ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR
1229 ERROR: permission denied to alter foreign-data wrapper "foo"
1230 HINT: Must be superuser to alter a foreign-data wrapper.
1231 DROP FOREIGN DATA WRAPPER foo; -- ERROR
1232 ERROR: must be owner of foreign-data wrapper foo
1233 GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO regress_test_role; -- WARNING
1234 WARNING: no privileges were granted for "postgresql"
1235 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role;
1236 CREATE SERVER s9 FOREIGN DATA WRAPPER postgresql;
1237 ALTER SERVER s6 VERSION '0.5'; -- ERROR
1238 ERROR: must be owner of foreign server s6
1239 DROP SERVER s6; -- ERROR
1240 ERROR: must be owner of foreign server s6
1241 GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role; -- ERROR
1242 ERROR: permission denied for foreign server s6
1243 GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role;
1244 CREATE USER MAPPING FOR public SERVER s6; -- ERROR
1245 ERROR: must be owner of foreign server s6
1246 CREATE USER MAPPING FOR public SERVER s9;
1247 ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (gotcha 'true'); -- ERROR
1248 ERROR: must be owner of foreign server s6
1249 DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR
1250 ERROR: must be owner of foreign server s6
1251 RESET ROLE;
1252 REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_unprivileged_role; -- ERROR
1253 ERROR: dependent privileges exist
1254 HINT: Use CASCADE to revoke them too.
1255 REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_unprivileged_role CASCADE;
1256 SET ROLE regress_unprivileged_role;
1257 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR
1258 ERROR: permission denied for foreign-data wrapper foo
1259 CREATE SERVER s10 FOREIGN DATA WRAPPER foo; -- ERROR
1260 ERROR: permission denied for foreign-data wrapper foo
1261 ALTER SERVER s9 VERSION '1.1';
1262 GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role;
1263 CREATE USER MAPPING FOR current_user SERVER s9;
1264 DROP SERVER s9 CASCADE;
1265 NOTICE: drop cascades to 2 other objects
1266 DETAIL: drop cascades to user mapping for public on server s9
1267 drop cascades to user mapping for regress_unprivileged_role on server s9
1268 RESET ROLE;
1269 CREATE SERVER s9 FOREIGN DATA WRAPPER foo;
1270 GRANT USAGE ON FOREIGN SERVER s9 TO regress_unprivileged_role;
1271 SET ROLE regress_unprivileged_role;
1272 ALTER SERVER s9 VERSION '1.2'; -- ERROR
1273 ERROR: must be owner of foreign server s9
1274 GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role; -- WARNING
1275 WARNING: no privileges were granted for "s9"
1276 CREATE USER MAPPING FOR current_user SERVER s9;
1277 DROP SERVER s9 CASCADE; -- ERROR
1278 ERROR: must be owner of foreign server s9
1279 -- Check visibility of user mapping data
1280 SET ROLE regress_test_role;
1281 CREATE SERVER s10 FOREIGN DATA WRAPPER foo;
1282 CREATE USER MAPPING FOR public SERVER s10 OPTIONS (user 'secret');
1283 CREATE USER MAPPING FOR regress_unprivileged_role SERVER s10 OPTIONS (user 'secret');
1284 -- owner of server can see some option fields
1285 \deu+
1286 List of user mappings
1287 Server | User name | FDW options
1288 --------+---------------------------+-------------------
1289 s10 | public | ("user" 'secret')
1290 s10 | regress_unprivileged_role |
1291 s4 | regress_foreign_data_user |
1292 s5 | regress_test_role | (modified '1')
1293 s6 | regress_test_role |
1294 s8 | public |
1295 s8 | regress_foreign_data_user |
1296 s9 | regress_unprivileged_role |
1297 t1 | public | (modified '1')
1298 (9 rows)
1300 RESET ROLE;
1301 -- superuser can see all option fields
1302 \deu+
1303 List of user mappings
1304 Server | User name | FDW options
1305 --------+---------------------------+---------------------
1306 s10 | public | ("user" 'secret')
1307 s10 | regress_unprivileged_role | ("user" 'secret')
1308 s4 | regress_foreign_data_user |
1309 s5 | regress_test_role | (modified '1')
1310 s6 | regress_test_role |
1311 s8 | public |
1312 s8 | regress_foreign_data_user | (password 'public')
1313 s9 | regress_unprivileged_role |
1314 t1 | public | (modified '1')
1315 (9 rows)
1317 -- unprivileged user cannot see any option field
1318 SET ROLE regress_unprivileged_role;
1319 \deu+
1320 List of user mappings
1321 Server | User name | FDW options
1322 --------+---------------------------+-------------
1323 s10 | public |
1324 s10 | regress_unprivileged_role |
1325 s4 | regress_foreign_data_user |
1326 s5 | regress_test_role |
1327 s6 | regress_test_role |
1328 s8 | public |
1329 s8 | regress_foreign_data_user |
1330 s9 | regress_unprivileged_role |
1331 t1 | public |
1332 (9 rows)
1334 RESET ROLE;
1335 DROP SERVER s10 CASCADE;
1336 NOTICE: drop cascades to 2 other objects
1337 DETAIL: drop cascades to user mapping for public on server s10
1338 drop cascades to user mapping for regress_unprivileged_role on server s10
1339 -- Triggers
1340 CREATE FUNCTION dummy_trigger() RETURNS TRIGGER AS $$
1341 BEGIN
1342 RETURN NULL;
1343 END
1344 $$ language plpgsql;
1345 CREATE TRIGGER trigtest_before_stmt BEFORE INSERT OR UPDATE OR DELETE
1346 ON foreign_schema.foreign_table_1
1347 FOR EACH STATEMENT
1348 EXECUTE PROCEDURE dummy_trigger();
1349 CREATE TRIGGER trigtest_after_stmt AFTER INSERT OR UPDATE OR DELETE
1350 ON foreign_schema.foreign_table_1
1351 FOR EACH STATEMENT
1352 EXECUTE PROCEDURE dummy_trigger();
1353 CREATE TRIGGER trigtest_after_stmt_tt AFTER INSERT OR UPDATE OR DELETE -- ERROR
1354 ON foreign_schema.foreign_table_1
1355 REFERENCING NEW TABLE AS new_table
1356 FOR EACH STATEMENT
1357 EXECUTE PROCEDURE dummy_trigger();
1358 ERROR: "foreign_table_1" is a foreign table
1359 DETAIL: Triggers on foreign tables cannot have transition tables.
1360 CREATE TRIGGER trigtest_before_row BEFORE INSERT OR UPDATE OR DELETE
1361 ON foreign_schema.foreign_table_1
1362 FOR EACH ROW
1363 EXECUTE PROCEDURE dummy_trigger();
1364 CREATE TRIGGER trigtest_after_row AFTER INSERT OR UPDATE OR DELETE
1365 ON foreign_schema.foreign_table_1
1366 FOR EACH ROW
1367 EXECUTE PROCEDURE dummy_trigger();
1368 CREATE CONSTRAINT TRIGGER trigtest_constraint AFTER INSERT OR UPDATE OR DELETE
1369 ON foreign_schema.foreign_table_1
1370 FOR EACH ROW
1371 EXECUTE PROCEDURE dummy_trigger();
1372 ERROR: "foreign_table_1" is a foreign table
1373 DETAIL: Foreign tables cannot have constraint triggers.
1374 ALTER FOREIGN TABLE foreign_schema.foreign_table_1
1375 DISABLE TRIGGER trigtest_before_stmt;
1376 ALTER FOREIGN TABLE foreign_schema.foreign_table_1
1377 ENABLE TRIGGER trigtest_before_stmt;
1378 DROP TRIGGER trigtest_before_stmt ON foreign_schema.foreign_table_1;
1379 DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1;
1380 DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1;
1381 DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1;
1382 DROP FUNCTION dummy_trigger();
1383 -- Table inheritance
1384 CREATE TABLE fd_pt1 (
1385 c1 integer NOT NULL,
1386 c2 text,
1387 c3 date
1388 );
1389 CREATE FOREIGN TABLE ft2 () INHERITS (fd_pt1)
1390 SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
1391 \d+ fd_pt1
1392 Table "public.fd_pt1"
1393 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1394 --------+---------+-----------+----------+---------+----------+--------------+-------------
1395 c1 | integer | | not null | | plain | |
1396 c2 | text | | | | extended | |
1397 c3 | date | | | | plain | |
1398 Child tables: ft2
1400 \d+ ft2
1401 Foreign table "public.ft2"
1402 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1403 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1404 c1 | integer | | not null | | | plain | |
1405 c2 | text | | | | | extended | |
1406 c3 | date | | | | | plain | |
1407 Server: s0
1408 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
1409 Inherits: fd_pt1
1411 DROP FOREIGN TABLE ft2;
1412 \d+ fd_pt1
1413 Table "public.fd_pt1"
1414 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1415 --------+---------+-----------+----------+---------+----------+--------------+-------------
1416 c1 | integer | | not null | | plain | |
1417 c2 | text | | | | extended | |
1418 c3 | date | | | | plain | |
1420 CREATE FOREIGN TABLE ft2 (
1421 c1 integer NOT NULL,
1422 c2 text,
1423 c3 date
1424 ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
1425 \d+ ft2
1426 Foreign table "public.ft2"
1427 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1428 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1429 c1 | integer | | not null | | | plain | |
1430 c2 | text | | | | | extended | |
1431 c3 | date | | | | | plain | |
1432 Server: s0
1433 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
1435 ALTER FOREIGN TABLE ft2 INHERIT fd_pt1;
1436 \d+ fd_pt1
1437 Table "public.fd_pt1"
1438 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1439 --------+---------+-----------+----------+---------+----------+--------------+-------------
1440 c1 | integer | | not null | | plain | |
1441 c2 | text | | | | extended | |
1442 c3 | date | | | | plain | |
1443 Child tables: ft2
1445 \d+ ft2
1446 Foreign table "public.ft2"
1447 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1448 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1449 c1 | integer | | not null | | | plain | |
1450 c2 | text | | | | | extended | |
1451 c3 | date | | | | | plain | |
1452 Server: s0
1453 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
1454 Inherits: fd_pt1
1456 CREATE TABLE ct3() INHERITS(ft2);
1457 CREATE FOREIGN TABLE ft3 (
1458 c1 integer NOT NULL,
1459 c2 text,
1460 c3 date
1461 ) INHERITS(ft2)
1462 SERVER s0;
1463 NOTICE: merging column "c1" with inherited definition
1464 NOTICE: merging column "c2" with inherited definition
1465 NOTICE: merging column "c3" with inherited definition
1466 \d+ ft2
1467 Foreign table "public.ft2"
1468 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1469 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1470 c1 | integer | | not null | | | plain | |
1471 c2 | text | | | | | extended | |
1472 c3 | date | | | | | plain | |
1473 Server: s0
1474 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
1475 Inherits: fd_pt1
1476 Child tables: ct3,
1477 ft3
1479 \d+ ct3
1480 Table "public.ct3"
1481 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1482 --------+---------+-----------+----------+---------+----------+--------------+-------------
1483 c1 | integer | | not null | | plain | |
1484 c2 | text | | | | extended | |
1485 c3 | date | | | | plain | |
1486 Inherits: ft2
1488 \d+ ft3
1489 Foreign table "public.ft3"
1490 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1491 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1492 c1 | integer | | not null | | | plain | |
1493 c2 | text | | | | | extended | |
1494 c3 | date | | | | | plain | |
1495 Server: s0
1496 Inherits: ft2
1498 -- add attributes recursively
1499 ALTER TABLE fd_pt1 ADD COLUMN c4 integer;
1500 ALTER TABLE fd_pt1 ADD COLUMN c5 integer DEFAULT 0;
1501 ALTER TABLE fd_pt1 ADD COLUMN c6 integer;
1502 ALTER TABLE fd_pt1 ADD COLUMN c7 integer NOT NULL;
1503 ALTER TABLE fd_pt1 ADD COLUMN c8 integer;
1504 \d+ fd_pt1
1505 Table "public.fd_pt1"
1506 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1507 --------+---------+-----------+----------+---------+----------+--------------+-------------
1508 c1 | integer | | not null | | plain | |
1509 c2 | text | | | | extended | |
1510 c3 | date | | | | plain | |
1511 c4 | integer | | | | plain | |
1512 c5 | integer | | | 0 | plain | |
1513 c6 | integer | | | | plain | |
1514 c7 | integer | | not null | | plain | |
1515 c8 | integer | | | | plain | |
1516 Child tables: ft2
1518 \d+ ft2
1519 Foreign table "public.ft2"
1520 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1521 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1522 c1 | integer | | not null | | | plain | |
1523 c2 | text | | | | | extended | |
1524 c3 | date | | | | | plain | |
1525 c4 | integer | | | | | plain | |
1526 c5 | integer | | | 0 | | plain | |
1527 c6 | integer | | | | | plain | |
1528 c7 | integer | | not null | | | plain | |
1529 c8 | integer | | | | | plain | |
1530 Server: s0
1531 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
1532 Inherits: fd_pt1
1533 Child tables: ct3,
1534 ft3
1536 \d+ ct3
1537 Table "public.ct3"
1538 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1539 --------+---------+-----------+----------+---------+----------+--------------+-------------
1540 c1 | integer | | not null | | plain | |
1541 c2 | text | | | | extended | |
1542 c3 | date | | | | plain | |
1543 c4 | integer | | | | plain | |
1544 c5 | integer | | | 0 | plain | |
1545 c6 | integer | | | | plain | |
1546 c7 | integer | | not null | | plain | |
1547 c8 | integer | | | | plain | |
1548 Inherits: ft2
1550 \d+ ft3
1551 Foreign table "public.ft3"
1552 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1553 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1554 c1 | integer | | not null | | | plain | |
1555 c2 | text | | | | | extended | |
1556 c3 | date | | | | | plain | |
1557 c4 | integer | | | | | plain | |
1558 c5 | integer | | | 0 | | plain | |
1559 c6 | integer | | | | | plain | |
1560 c7 | integer | | not null | | | plain | |
1561 c8 | integer | | | | | plain | |
1562 Server: s0
1563 Inherits: ft2
1565 -- alter attributes recursively
1566 ALTER TABLE fd_pt1 ALTER COLUMN c4 SET DEFAULT 0;
1567 ALTER TABLE fd_pt1 ALTER COLUMN c5 DROP DEFAULT;
1568 ALTER TABLE fd_pt1 ALTER COLUMN c6 SET NOT NULL;
1569 ALTER TABLE fd_pt1 ALTER COLUMN c7 DROP NOT NULL;
1570 ALTER TABLE fd_pt1 ALTER COLUMN c8 TYPE char(10) USING '0'; -- ERROR
1571 ERROR: "ft2" is not a table
1572 ALTER TABLE fd_pt1 ALTER COLUMN c8 TYPE char(10);
1573 ALTER TABLE fd_pt1 ALTER COLUMN c8 SET DATA TYPE text;
1574 ALTER TABLE fd_pt1 ALTER COLUMN c1 SET STATISTICS 10000;
1575 ALTER TABLE fd_pt1 ALTER COLUMN c1 SET (n_distinct = 100);
1576 ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STATISTICS -1;
1577 ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STORAGE EXTERNAL;
1578 \d+ fd_pt1
1579 Table "public.fd_pt1"
1580 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1581 --------+---------+-----------+----------+---------+----------+--------------+-------------
1582 c1 | integer | | not null | | plain | 10000 |
1583 c2 | text | | | | extended | |
1584 c3 | date | | | | plain | |
1585 c4 | integer | | | 0 | plain | |
1586 c5 | integer | | | | plain | |
1587 c6 | integer | | not null | | plain | |
1588 c7 | integer | | | | plain | |
1589 c8 | text | | | | external | |
1590 Child tables: ft2
1592 \d+ ft2
1593 Foreign table "public.ft2"
1594 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1595 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1596 c1 | integer | | not null | | | plain | 10000 |
1597 c2 | text | | | | | extended | |
1598 c3 | date | | | | | plain | |
1599 c4 | integer | | | 0 | | plain | |
1600 c5 | integer | | | | | plain | |
1601 c6 | integer | | not null | | | plain | |
1602 c7 | integer | | | | | plain | |
1603 c8 | text | | | | | external | |
1604 Server: s0
1605 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
1606 Inherits: fd_pt1
1607 Child tables: ct3,
1608 ft3
1610 -- drop attributes recursively
1611 ALTER TABLE fd_pt1 DROP COLUMN c4;
1612 ALTER TABLE fd_pt1 DROP COLUMN c5;
1613 ALTER TABLE fd_pt1 DROP COLUMN c6;
1614 ALTER TABLE fd_pt1 DROP COLUMN c7;
1615 ALTER TABLE fd_pt1 DROP COLUMN c8;
1616 \d+ fd_pt1
1617 Table "public.fd_pt1"
1618 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1619 --------+---------+-----------+----------+---------+----------+--------------+-------------
1620 c1 | integer | | not null | | plain | 10000 |
1621 c2 | text | | | | extended | |
1622 c3 | date | | | | plain | |
1623 Child tables: ft2
1625 \d+ ft2
1626 Foreign table "public.ft2"
1627 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1628 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1629 c1 | integer | | not null | | | plain | 10000 |
1630 c2 | text | | | | | extended | |
1631 c3 | date | | | | | plain | |
1632 Server: s0
1633 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
1634 Inherits: fd_pt1
1635 Child tables: ct3,
1636 ft3
1638 -- add constraints recursively
1639 ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk1 CHECK (c1 > 0) NO INHERIT;
1640 ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk2 CHECK (c2 <> '');
1641 -- connoinherit should be true for NO INHERIT constraint
1642 SELECT relname, conname, contype, conislocal, coninhcount, connoinherit
1643 FROM pg_class AS pc JOIN pg_constraint AS pgc ON (conrelid = pc.oid)
1644 WHERE pc.relname = 'fd_pt1'
1645 ORDER BY 1,2;
1646 relname | conname | contype | conislocal | coninhcount | connoinherit
1647 ---------+------------+---------+------------+-------------+--------------
1648 fd_pt1 | fd_pt1chk1 | c | t | 0 | t
1649 fd_pt1 | fd_pt1chk2 | c | t | 0 | f
1650 (2 rows)
1652 -- child does not inherit NO INHERIT constraints
1653 \d+ fd_pt1
1654 Table "public.fd_pt1"
1655 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1656 --------+---------+-----------+----------+---------+----------+--------------+-------------
1657 c1 | integer | | not null | | plain | 10000 |
1658 c2 | text | | | | extended | |
1659 c3 | date | | | | plain | |
1660 Check constraints:
1661 "fd_pt1chk1" CHECK (c1 > 0) NO INHERIT
1662 "fd_pt1chk2" CHECK (c2 <> ''::text)
1663 Child tables: ft2
1665 \d+ ft2
1666 Foreign table "public.ft2"
1667 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1668 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1669 c1 | integer | | not null | | | plain | 10000 |
1670 c2 | text | | | | | extended | |
1671 c3 | date | | | | | plain | |
1672 Check constraints:
1673 "fd_pt1chk2" CHECK (c2 <> ''::text)
1674 Server: s0
1675 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
1676 Inherits: fd_pt1
1677 Child tables: ct3,
1678 ft3
1680 DROP FOREIGN TABLE ft2; -- ERROR
1681 ERROR: cannot drop foreign table ft2 because other objects depend on it
1682 DETAIL: table ct3 depends on foreign table ft2
1683 foreign table ft3 depends on foreign table ft2
1684 HINT: Use DROP ... CASCADE to drop the dependent objects too.
1685 DROP FOREIGN TABLE ft2 CASCADE;
1686 NOTICE: drop cascades to 2 other objects
1687 DETAIL: drop cascades to table ct3
1688 drop cascades to foreign table ft3
1689 CREATE FOREIGN TABLE ft2 (
1690 c1 integer NOT NULL,
1691 c2 text,
1692 c3 date
1693 ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
1694 -- child must have parent's INHERIT constraints
1695 ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; -- ERROR
1696 ERROR: child table is missing constraint "fd_pt1chk2"
1697 ALTER FOREIGN TABLE ft2 ADD CONSTRAINT fd_pt1chk2 CHECK (c2 <> '');
1698 ALTER FOREIGN TABLE ft2 INHERIT fd_pt1;
1699 -- child does not inherit NO INHERIT constraints
1700 \d+ fd_pt1
1701 Table "public.fd_pt1"
1702 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1703 --------+---------+-----------+----------+---------+----------+--------------+-------------
1704 c1 | integer | | not null | | plain | 10000 |
1705 c2 | text | | | | extended | |
1706 c3 | date | | | | plain | |
1707 Check constraints:
1708 "fd_pt1chk1" CHECK (c1 > 0) NO INHERIT
1709 "fd_pt1chk2" CHECK (c2 <> ''::text)
1710 Child tables: ft2
1712 \d+ ft2
1713 Foreign table "public.ft2"
1714 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1715 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1716 c1 | integer | | not null | | | plain | |
1717 c2 | text | | | | | extended | |
1718 c3 | date | | | | | plain | |
1719 Check constraints:
1720 "fd_pt1chk2" CHECK (c2 <> ''::text)
1721 Server: s0
1722 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
1723 Inherits: fd_pt1
1725 -- drop constraints recursively
1726 ALTER TABLE fd_pt1 DROP CONSTRAINT fd_pt1chk1 CASCADE;
1727 ALTER TABLE fd_pt1 DROP CONSTRAINT fd_pt1chk2 CASCADE;
1728 -- NOT VALID case
1729 INSERT INTO fd_pt1 VALUES (1, 'fd_pt1'::text, '1994-01-01'::date);
1730 ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk3 CHECK (c2 <> '') NOT VALID;
1731 \d+ fd_pt1
1732 Table "public.fd_pt1"
1733 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1734 --------+---------+-----------+----------+---------+----------+--------------+-------------
1735 c1 | integer | | not null | | plain | 10000 |
1736 c2 | text | | | | extended | |
1737 c3 | date | | | | plain | |
1738 Check constraints:
1739 "fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID
1740 Child tables: ft2
1742 \d+ ft2
1743 Foreign table "public.ft2"
1744 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1745 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1746 c1 | integer | | not null | | | plain | |
1747 c2 | text | | | | | extended | |
1748 c3 | date | | | | | plain | |
1749 Check constraints:
1750 "fd_pt1chk2" CHECK (c2 <> ''::text)
1751 "fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID
1752 Server: s0
1753 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
1754 Inherits: fd_pt1
1756 -- VALIDATE CONSTRAINT need do nothing on foreign tables
1757 ALTER TABLE fd_pt1 VALIDATE CONSTRAINT fd_pt1chk3;
1758 \d+ fd_pt1
1759 Table "public.fd_pt1"
1760 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1761 --------+---------+-----------+----------+---------+----------+--------------+-------------
1762 c1 | integer | | not null | | plain | 10000 |
1763 c2 | text | | | | extended | |
1764 c3 | date | | | | plain | |
1765 Check constraints:
1766 "fd_pt1chk3" CHECK (c2 <> ''::text)
1767 Child tables: ft2
1769 \d+ ft2
1770 Foreign table "public.ft2"
1771 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1772 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1773 c1 | integer | | not null | | | plain | |
1774 c2 | text | | | | | extended | |
1775 c3 | date | | | | | plain | |
1776 Check constraints:
1777 "fd_pt1chk2" CHECK (c2 <> ''::text)
1778 "fd_pt1chk3" CHECK (c2 <> ''::text)
1779 Server: s0
1780 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
1781 Inherits: fd_pt1
1783 -- changes name of an attribute recursively
1784 ALTER TABLE fd_pt1 RENAME COLUMN c1 TO f1;
1785 ALTER TABLE fd_pt1 RENAME COLUMN c2 TO f2;
1786 ALTER TABLE fd_pt1 RENAME COLUMN c3 TO f3;
1787 -- changes name of a constraint recursively
1788 ALTER TABLE fd_pt1 RENAME CONSTRAINT fd_pt1chk3 TO f2_check;
1789 \d+ fd_pt1
1790 Table "public.fd_pt1"
1791 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1792 --------+---------+-----------+----------+---------+----------+--------------+-------------
1793 f1 | integer | | not null | | plain | 10000 |
1794 f2 | text | | | | extended | |
1795 f3 | date | | | | plain | |
1796 Check constraints:
1797 "f2_check" CHECK (f2 <> ''::text)
1798 Child tables: ft2
1800 \d+ ft2
1801 Foreign table "public.ft2"
1802 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1803 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1804 f1 | integer | | not null | | | plain | |
1805 f2 | text | | | | | extended | |
1806 f3 | date | | | | | plain | |
1807 Check constraints:
1808 "f2_check" CHECK (f2 <> ''::text)
1809 "fd_pt1chk2" CHECK (f2 <> ''::text)
1810 Server: s0
1811 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
1812 Inherits: fd_pt1
1814 -- TRUNCATE doesn't work on foreign tables, either directly or recursively
1815 TRUNCATE ft2; -- ERROR
1816 ERROR: foreign-data wrapper "dummy" has no handler
1817 TRUNCATE fd_pt1; -- ERROR
1818 ERROR: foreign-data wrapper "dummy" has no handler
1819 DROP TABLE fd_pt1 CASCADE;
1820 NOTICE: drop cascades to foreign table ft2
1821 -- IMPORT FOREIGN SCHEMA
1822 IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR
1823 ERROR: foreign-data wrapper "foo" has no handler
1824 IMPORT FOREIGN SCHEMA s1 LIMIT TO (t1) FROM SERVER s9 INTO public; --ERROR
1825 ERROR: foreign-data wrapper "foo" has no handler
1826 IMPORT FOREIGN SCHEMA s1 EXCEPT (t1) FROM SERVER s9 INTO public; -- ERROR
1827 ERROR: foreign-data wrapper "foo" has no handler
1828 IMPORT FOREIGN SCHEMA s1 EXCEPT (t1, t2) FROM SERVER s9 INTO public
1829 OPTIONS (option1 'value1', option2 'value2'); -- ERROR
1830 ERROR: foreign-data wrapper "foo" has no handler
1831 -- DROP FOREIGN TABLE
1832 DROP FOREIGN TABLE no_table; -- ERROR
1833 ERROR: foreign table "no_table" does not exist
1834 DROP FOREIGN TABLE IF EXISTS no_table;
1835 NOTICE: foreign table "no_table" does not exist, skipping
1836 DROP FOREIGN TABLE foreign_schema.foreign_table_1;
1837 -- REASSIGN OWNED/DROP OWNED of foreign objects
1838 REASSIGN OWNED BY regress_test_role TO regress_test_role2;
1839 DROP OWNED BY regress_test_role2;
1840 ERROR: cannot drop desired object(s) because other objects depend on them
1841 DETAIL: user mapping for regress_test_role on server s5 depends on server s5
1842 HINT: Use DROP ... CASCADE to drop the dependent objects too.
1843 DROP OWNED BY regress_test_role2 CASCADE;
1844 NOTICE: drop cascades to user mapping for regress_test_role on server s5
1845 -- Foreign partition DDL stuff
1846 CREATE TABLE fd_pt2 (
1847 c1 integer NOT NULL,
1848 c2 text,
1849 c3 date
1850 ) PARTITION BY LIST (c1);
1851 CREATE FOREIGN TABLE fd_pt2_1 PARTITION OF fd_pt2 FOR VALUES IN (1)
1852 SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
1853 \d+ fd_pt2
1854 Partitioned table "public.fd_pt2"
1855 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1856 --------+---------+-----------+----------+---------+----------+--------------+-------------
1857 c1 | integer | | not null | | plain | |
1858 c2 | text | | | | extended | |
1859 c3 | date | | | | plain | |
1860 Partition key: LIST (c1)
1861 Partitions: fd_pt2_1 FOR VALUES IN (1)
1863 \d+ fd_pt2_1
1864 Foreign table "public.fd_pt2_1"
1865 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1866 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1867 c1 | integer | | not null | | | plain | |
1868 c2 | text | | | | | extended | |
1869 c3 | date | | | | | plain | |
1870 Partition of: fd_pt2 FOR VALUES IN (1)
1871 Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1))
1872 Server: s0
1873 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
1875 -- partition cannot have additional columns
1876 DROP FOREIGN TABLE fd_pt2_1;
1877 CREATE FOREIGN TABLE fd_pt2_1 (
1878 c1 integer NOT NULL,
1879 c2 text,
1880 c3 date,
1881 c4 char
1882 ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
1883 \d+ fd_pt2_1
1884 Foreign table "public.fd_pt2_1"
1885 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1886 --------+--------------+-----------+----------+---------+-------------+----------+--------------+-------------
1887 c1 | integer | | not null | | | plain | |
1888 c2 | text | | | | | extended | |
1889 c3 | date | | | | | plain | |
1890 c4 | character(1) | | | | | extended | |
1891 Server: s0
1892 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
1894 ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); -- ERROR
1895 ERROR: table "fd_pt2_1" contains column "c4" not found in parent "fd_pt2"
1896 DETAIL: The new partition may contain only the columns present in parent.
1897 DROP FOREIGN TABLE fd_pt2_1;
1898 \d+ fd_pt2
1899 Partitioned table "public.fd_pt2"
1900 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1901 --------+---------+-----------+----------+---------+----------+--------------+-------------
1902 c1 | integer | | not null | | plain | |
1903 c2 | text | | | | extended | |
1904 c3 | date | | | | plain | |
1905 Partition key: LIST (c1)
1906 Number of partitions: 0
1908 CREATE FOREIGN TABLE fd_pt2_1 (
1909 c1 integer NOT NULL,
1910 c2 text,
1911 c3 date
1912 ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
1913 \d+ fd_pt2_1
1914 Foreign table "public.fd_pt2_1"
1915 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1916 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1917 c1 | integer | | not null | | | plain | |
1918 c2 | text | | | | | extended | |
1919 c3 | date | | | | | plain | |
1920 Server: s0
1921 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
1923 -- no attach partition validation occurs for foreign tables
1924 ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1);
1925 \d+ fd_pt2
1926 Partitioned table "public.fd_pt2"
1927 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1928 --------+---------+-----------+----------+---------+----------+--------------+-------------
1929 c1 | integer | | not null | | plain | |
1930 c2 | text | | | | extended | |
1931 c3 | date | | | | plain | |
1932 Partition key: LIST (c1)
1933 Partitions: fd_pt2_1 FOR VALUES IN (1)
1935 \d+ fd_pt2_1
1936 Foreign table "public.fd_pt2_1"
1937 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1938 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1939 c1 | integer | | not null | | | plain | |
1940 c2 | text | | | | | extended | |
1941 c3 | date | | | | | plain | |
1942 Partition of: fd_pt2 FOR VALUES IN (1)
1943 Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1))
1944 Server: s0
1945 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
1947 -- cannot add column to a partition
1948 ALTER TABLE fd_pt2_1 ADD c4 char;
1949 ERROR: cannot add column to a partition
1950 -- ok to have a partition's own constraints though
1951 ALTER TABLE fd_pt2_1 ALTER c3 SET NOT NULL;
1952 ALTER TABLE fd_pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <> '');
1953 \d+ fd_pt2
1954 Partitioned table "public.fd_pt2"
1955 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1956 --------+---------+-----------+----------+---------+----------+--------------+-------------
1957 c1 | integer | | not null | | plain | |
1958 c2 | text | | | | extended | |
1959 c3 | date | | | | plain | |
1960 Partition key: LIST (c1)
1961 Partitions: fd_pt2_1 FOR VALUES IN (1)
1963 \d+ fd_pt2_1
1964 Foreign table "public.fd_pt2_1"
1965 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1966 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1967 c1 | integer | | not null | | | plain | |
1968 c2 | text | | | | | extended | |
1969 c3 | date | | not null | | | plain | |
1970 Partition of: fd_pt2 FOR VALUES IN (1)
1971 Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1))
1972 Check constraints:
1973 "p21chk" CHECK (c2 <> ''::text)
1974 Server: s0
1975 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
1977 -- cannot drop inherited NOT NULL constraint from a partition
1978 ALTER TABLE fd_pt2_1 ALTER c1 DROP NOT NULL;
1979 ERROR: column "c1" is marked NOT NULL in parent table
1980 -- partition must have parent's constraints
1981 ALTER TABLE fd_pt2 DETACH PARTITION fd_pt2_1;
1982 ALTER TABLE fd_pt2 ALTER c2 SET NOT NULL;
1983 \d+ fd_pt2
1984 Partitioned table "public.fd_pt2"
1985 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1986 --------+---------+-----------+----------+---------+----------+--------------+-------------
1987 c1 | integer | | not null | | plain | |
1988 c2 | text | | not null | | extended | |
1989 c3 | date | | | | plain | |
1990 Partition key: LIST (c1)
1991 Number of partitions: 0
1993 \d+ fd_pt2_1
1994 Foreign table "public.fd_pt2_1"
1995 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
1996 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1997 c1 | integer | | not null | | | plain | |
1998 c2 | text | | | | | extended | |
1999 c3 | date | | not null | | | plain | |
2000 Check constraints:
2001 "p21chk" CHECK (c2 <> ''::text)
2002 Server: s0
2003 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
2005 ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); -- ERROR
2006 ERROR: column "c2" in child table must be marked NOT NULL
2007 ALTER FOREIGN TABLE fd_pt2_1 ALTER c2 SET NOT NULL;
2008 ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1);
2009 ALTER TABLE fd_pt2 DETACH PARTITION fd_pt2_1;
2010 ALTER TABLE fd_pt2 ADD CONSTRAINT fd_pt2chk1 CHECK (c1 > 0);
2011 \d+ fd_pt2
2012 Partitioned table "public.fd_pt2"
2013 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2014 --------+---------+-----------+----------+---------+----------+--------------+-------------
2015 c1 | integer | | not null | | plain | |
2016 c2 | text | | not null | | extended | |
2017 c3 | date | | | | plain | |
2018 Partition key: LIST (c1)
2019 Check constraints:
2020 "fd_pt2chk1" CHECK (c1 > 0)
2021 Number of partitions: 0
2023 \d+ fd_pt2_1
2024 Foreign table "public.fd_pt2_1"
2025 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
2026 --------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
2027 c1 | integer | | not null | | | plain | |
2028 c2 | text | | not null | | | extended | |
2029 c3 | date | | not null | | | plain | |
2030 Check constraints:
2031 "p21chk" CHECK (c2 <> ''::text)
2032 Server: s0
2033 FDW options: (delimiter ',', quote '"', "be quoted" 'value')
2035 ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); -- ERROR
2036 ERROR: child table is missing constraint "fd_pt2chk1"
2037 ALTER FOREIGN TABLE fd_pt2_1 ADD CONSTRAINT fd_pt2chk1 CHECK (c1 > 0);
2038 ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1);
2039 -- TRUNCATE doesn't work on foreign tables, either directly or recursively
2040 TRUNCATE fd_pt2_1; -- ERROR
2041 ERROR: foreign-data wrapper "dummy" has no handler
2042 TRUNCATE fd_pt2; -- ERROR
2043 ERROR: foreign-data wrapper "dummy" has no handler
2044 DROP FOREIGN TABLE fd_pt2_1;
2045 DROP TABLE fd_pt2;
2046 -- foreign table cannot be part of partition tree made of temporary
2047 -- relations.
2048 CREATE TEMP TABLE temp_parted (a int) PARTITION BY LIST (a);
2049 CREATE FOREIGN TABLE foreign_part PARTITION OF temp_parted DEFAULT
2050 SERVER s0; -- ERROR
2051 ERROR: cannot create a permanent relation as partition of temporary relation "temp_parted"
2052 CREATE FOREIGN TABLE foreign_part (a int) SERVER s0;
2053 ALTER TABLE temp_parted ATTACH PARTITION foreign_part DEFAULT; -- ERROR
2054 ERROR: cannot attach a permanent relation as partition of temporary relation "temp_parted"
2055 DROP FOREIGN TABLE foreign_part;
2056 DROP TABLE temp_parted;
2057 -- Cleanup
2058 DROP SCHEMA foreign_schema CASCADE;
2059 DROP ROLE regress_test_role; -- ERROR
2060 ERROR: role "regress_test_role" cannot be dropped because some objects depend on it
2061 DETAIL: privileges for foreign-data wrapper foo
2062 privileges for server s4
2063 owner of user mapping for regress_test_role on server s6
2064 DROP SERVER t1 CASCADE;
2065 NOTICE: drop cascades to user mapping for public on server t1
2066 DROP USER MAPPING FOR regress_test_role SERVER s6;
2067 DROP FOREIGN DATA WRAPPER foo CASCADE;
2068 NOTICE: drop cascades to 5 other objects
2069 DETAIL: drop cascades to server s4
2070 drop cascades to user mapping for regress_foreign_data_user on server s4
2071 drop cascades to server s6
2072 drop cascades to server s9
2073 drop cascades to user mapping for regress_unprivileged_role on server s9
2074 DROP SERVER s8 CASCADE;
2075 NOTICE: drop cascades to 2 other objects
2076 DETAIL: drop cascades to user mapping for regress_foreign_data_user on server s8
2077 drop cascades to user mapping for public on server s8
2078 DROP ROLE regress_test_indirect;
2079 DROP ROLE regress_test_role;
2080 DROP ROLE regress_unprivileged_role; -- ERROR
2081 ERROR: role "regress_unprivileged_role" cannot be dropped because some objects depend on it
2082 DETAIL: privileges for foreign-data wrapper postgresql
2083 REVOKE ALL ON FOREIGN DATA WRAPPER postgresql FROM regress_unprivileged_role;
2084 DROP ROLE regress_unprivileged_role;
2085 DROP ROLE regress_test_role2;
2086 DROP FOREIGN DATA WRAPPER postgresql CASCADE;
2087 DROP FOREIGN DATA WRAPPER dummy CASCADE;
2088 NOTICE: drop cascades to server s0
2089 \c
2090 DROP ROLE regress_foreign_data_user;
2091 -- At this point we should have no wrappers, no servers, and no mappings.
2092 SELECT fdwname, fdwhandler, fdwvalidator, fdwoptions FROM pg_foreign_data_wrapper;
2093 fdwname | fdwhandler | fdwvalidator | fdwoptions
2094 ---------+------------+--------------+------------
2095 (0 rows)
2097 SELECT srvname, srvoptions FROM pg_foreign_server;
2098 srvname | srvoptions
2099 ---------+------------
2100 (0 rows)
2102 SELECT * FROM pg_user_mapping;
2103 oid | umuser | umserver | umoptions
2104 -----+--------+----------+-----------
2105 (0 rows)