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