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