1-- 2-- Test foreign-data wrapper and server management. 3-- 4 5-- Clean up in case a prior regression run failed 6 7-- Suppress NOTICE messages when roles don't exist 8SET client_min_messages TO 'warning'; 9 10DROP ROLE IF EXISTS regress_foreign_data_user, regress_test_role, regress_test_role2, regress_test_role_super, regress_test_indirect, regress_unprivileged_role; 11 12RESET client_min_messages; 13 14CREATE ROLE regress_foreign_data_user LOGIN SUPERUSER; 15SET SESSION AUTHORIZATION 'regress_foreign_data_user'; 16 17CREATE ROLE regress_test_role; 18CREATE ROLE regress_test_role2; 19CREATE ROLE regress_test_role_super SUPERUSER; 20CREATE ROLE regress_test_indirect; 21CREATE ROLE regress_unprivileged_role; 22 23CREATE FOREIGN DATA WRAPPER dummy; 24COMMENT ON FOREIGN DATA WRAPPER dummy IS 'useless'; 25CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator; 26 27-- At this point we should have 2 built-in wrappers and no servers. 28SELECT fdwname, fdwhandler::regproc, fdwvalidator::regproc, fdwoptions FROM pg_foreign_data_wrapper ORDER BY 1, 2, 3; 29SELECT srvname, srvoptions FROM pg_foreign_server; 30SELECT * FROM pg_user_mapping; 31 32-- CREATE FOREIGN DATA WRAPPER 33CREATE FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR 34CREATE FOREIGN DATA WRAPPER foo; 35\dew 36 37CREATE FOREIGN DATA WRAPPER foo; -- duplicate 38DROP FOREIGN DATA WRAPPER foo; 39CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1'); 40\dew+ 41 42DROP FOREIGN DATA WRAPPER foo; 43CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', testing '2'); -- ERROR 44CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2'); 45\dew+ 46 47DROP FOREIGN DATA WRAPPER foo; 48SET ROLE regress_test_role; 49CREATE FOREIGN DATA WRAPPER foo; -- ERROR 50RESET ROLE; 51CREATE FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator; 52\dew+ 53 54-- HANDLER related checks 55CREATE FUNCTION invalid_fdw_handler() RETURNS int LANGUAGE SQL AS 'SELECT 1;'; 56CREATE FOREIGN DATA WRAPPER test_fdw HANDLER invalid_fdw_handler; -- ERROR 57CREATE FOREIGN DATA WRAPPER test_fdw HANDLER test_fdw_handler HANDLER invalid_fdw_handler; -- ERROR 58CREATE FOREIGN DATA WRAPPER test_fdw HANDLER test_fdw_handler; 59DROP FOREIGN DATA WRAPPER test_fdw; 60 61-- ALTER FOREIGN DATA WRAPPER 62ALTER FOREIGN DATA WRAPPER foo; -- ERROR 63ALTER FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR 64ALTER FOREIGN DATA WRAPPER foo NO VALIDATOR; 65\dew+ 66 67ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '1', b '2'); 68ALTER FOREIGN DATA WRAPPER foo OPTIONS (SET c '4'); -- ERROR 69ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP c); -- ERROR 70ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x); 71\dew+ 72 73ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4'); 74\dew+ 75 76ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2'); 77ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR 78\dew+ 79 80SET ROLE regress_test_role; 81ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5'); -- ERROR 82SET ROLE regress_test_role_super; 83ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5'); 84\dew+ 85 86ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR 87ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role_super; 88ALTER ROLE regress_test_role_super NOSUPERUSER; 89SET ROLE regress_test_role_super; 90ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD e '6'); -- ERROR 91RESET ROLE; 92\dew+ 93 94ALTER FOREIGN DATA WRAPPER foo RENAME TO foo1; 95\dew+ 96ALTER FOREIGN DATA WRAPPER foo1 RENAME TO foo; 97 98-- HANDLER related checks 99ALTER FOREIGN DATA WRAPPER foo HANDLER invalid_fdw_handler; -- ERROR 100ALTER FOREIGN DATA WRAPPER foo HANDLER test_fdw_handler HANDLER anything; -- ERROR 101ALTER FOREIGN DATA WRAPPER foo HANDLER test_fdw_handler; 102DROP FUNCTION invalid_fdw_handler(); 103 104-- DROP FOREIGN DATA WRAPPER 105DROP FOREIGN DATA WRAPPER nonexistent; -- ERROR 106DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent; 107\dew+ 108 109DROP ROLE regress_test_role_super; -- ERROR 110SET ROLE regress_test_role_super; 111DROP FOREIGN DATA WRAPPER foo; 112RESET ROLE; 113DROP ROLE regress_test_role_super; 114\dew+ 115 116CREATE FOREIGN DATA WRAPPER foo; 117CREATE SERVER s1 FOREIGN DATA WRAPPER foo; 118COMMENT ON SERVER s1 IS 'foreign server'; 119CREATE USER MAPPING FOR current_user SERVER s1; 120CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR 121CREATE USER MAPPING IF NOT EXISTS FOR current_user SERVER s1; -- NOTICE 122\dew+ 123\des+ 124\deu+ 125DROP FOREIGN DATA WRAPPER foo; -- ERROR 126SET ROLE regress_test_role; 127DROP FOREIGN DATA WRAPPER foo CASCADE; -- ERROR 128RESET ROLE; 129DROP FOREIGN DATA WRAPPER foo CASCADE; 130\dew+ 131\des+ 132\deu+ 133 134-- exercise CREATE SERVER 135CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR 136CREATE FOREIGN DATA WRAPPER foo OPTIONS ("test wrapper" 'true'); 137CREATE SERVER s1 FOREIGN DATA WRAPPER foo; 138CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR 139CREATE SERVER IF NOT EXISTS s1 FOREIGN DATA WRAPPER foo; -- No ERROR, just NOTICE 140CREATE SERVER s2 FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b'); 141CREATE SERVER s3 TYPE 'oracle' FOREIGN DATA WRAPPER foo; 142CREATE SERVER s4 TYPE 'oracle' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b'); 143CREATE SERVER s5 VERSION '15.0' FOREIGN DATA WRAPPER foo; 144CREATE SERVER s6 VERSION '16.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b'); 145CREATE SERVER s7 TYPE 'oracle' VERSION '17.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b'); 146CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (foo '1'); -- ERROR 147CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db'); 148\des+ 149SET ROLE regress_test_role; 150CREATE SERVER t1 FOREIGN DATA WRAPPER foo; -- ERROR: no usage on FDW 151RESET ROLE; 152GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; 153SET ROLE regress_test_role; 154CREATE SERVER t1 FOREIGN DATA WRAPPER foo; 155RESET ROLE; 156\des+ 157 158REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role; 159GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect; 160SET ROLE regress_test_role; 161CREATE SERVER t2 FOREIGN DATA WRAPPER foo; -- ERROR 162RESET ROLE; 163GRANT regress_test_indirect TO regress_test_role; 164SET ROLE regress_test_role; 165CREATE SERVER t2 FOREIGN DATA WRAPPER foo; 166\des+ 167RESET ROLE; 168REVOKE regress_test_indirect FROM regress_test_role; 169 170-- ALTER SERVER 171ALTER SERVER s0; -- ERROR 172ALTER SERVER s0 OPTIONS (a '1'); -- ERROR 173ALTER SERVER s1 VERSION '1.0' OPTIONS (servername 's1'); 174ALTER SERVER s2 VERSION '1.1'; 175ALTER SERVER s3 OPTIONS ("tns name" 'orcl', port '1521'); 176GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role; 177GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION; 178\des+ 179SET ROLE regress_test_role; 180ALTER SERVER s1 VERSION '1.1'; -- ERROR 181ALTER SERVER s1 OWNER TO regress_test_role; -- ERROR 182RESET ROLE; 183ALTER SERVER s1 OWNER TO regress_test_role; 184GRANT regress_test_role2 TO regress_test_role; 185SET ROLE regress_test_role; 186ALTER SERVER s1 VERSION '1.1'; 187ALTER SERVER s1 OWNER TO regress_test_role2; -- ERROR 188RESET ROLE; 189ALTER SERVER s8 OPTIONS (foo '1'); -- ERROR option validation 190ALTER SERVER s8 OPTIONS (connect_timeout '30', SET dbname 'db1', DROP host); 191SET ROLE regress_test_role; 192ALTER SERVER s1 OWNER TO regress_test_indirect; -- ERROR 193RESET ROLE; 194GRANT regress_test_indirect TO regress_test_role; 195SET ROLE regress_test_role; 196ALTER SERVER s1 OWNER TO regress_test_indirect; 197RESET ROLE; 198GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect; 199SET ROLE regress_test_role; 200ALTER SERVER s1 OWNER TO regress_test_indirect; 201RESET ROLE; 202DROP ROLE regress_test_indirect; -- ERROR 203\des+ 204 205ALTER SERVER s8 RENAME to s8new; 206\des+ 207ALTER SERVER s8new RENAME to s8; 208 209-- DROP SERVER 210DROP SERVER nonexistent; -- ERROR 211DROP SERVER IF EXISTS nonexistent; 212\des 213SET ROLE regress_test_role; 214DROP SERVER s2; -- ERROR 215DROP SERVER s1; 216RESET ROLE; 217\des 218ALTER SERVER s2 OWNER TO regress_test_role; 219SET ROLE regress_test_role; 220DROP SERVER s2; 221RESET ROLE; 222\des 223CREATE USER MAPPING FOR current_user SERVER s3; 224\deu 225DROP SERVER s3; -- ERROR 226DROP SERVER s3 CASCADE; 227\des 228\deu 229 230-- CREATE USER MAPPING 231CREATE USER MAPPING FOR regress_test_missing_role SERVER s1; -- ERROR 232CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR 233CREATE USER MAPPING FOR current_user SERVER s4; 234CREATE USER MAPPING FOR user SERVER s4; -- ERROR duplicate 235CREATE USER MAPPING FOR public SERVER s4 OPTIONS ("this mapping" 'is public'); 236CREATE USER MAPPING FOR user SERVER s8 OPTIONS (username 'test', password 'secret'); -- ERROR 237CREATE USER MAPPING FOR user SERVER s8 OPTIONS (user 'test', password 'secret'); 238ALTER SERVER s5 OWNER TO regress_test_role; 239ALTER SERVER s6 OWNER TO regress_test_indirect; 240SET ROLE regress_test_role; 241CREATE USER MAPPING FOR current_user SERVER s5; 242CREATE USER MAPPING FOR current_user SERVER s6 OPTIONS (username 'test'); 243CREATE USER MAPPING FOR current_user SERVER s7; -- ERROR 244CREATE USER MAPPING FOR public SERVER s8; -- ERROR 245RESET ROLE; 246 247ALTER SERVER t1 OWNER TO regress_test_indirect; 248SET ROLE regress_test_role; 249CREATE USER MAPPING FOR current_user SERVER t1 OPTIONS (username 'bob', password 'boo'); 250CREATE USER MAPPING FOR public SERVER t1; 251RESET ROLE; 252\deu 253 254-- ALTER USER MAPPING 255ALTER USER MAPPING FOR regress_test_missing_role SERVER s4 OPTIONS (gotcha 'true'); -- ERROR 256ALTER USER MAPPING FOR user SERVER ss4 OPTIONS (gotcha 'true'); -- ERROR 257ALTER USER MAPPING FOR public SERVER s5 OPTIONS (gotcha 'true'); -- ERROR 258ALTER USER MAPPING FOR current_user SERVER s8 OPTIONS (username 'test'); -- ERROR 259ALTER USER MAPPING FOR current_user SERVER s8 OPTIONS (DROP user, SET password 'public'); 260SET ROLE regress_test_role; 261ALTER USER MAPPING FOR current_user SERVER s5 OPTIONS (ADD modified '1'); 262ALTER USER MAPPING FOR public SERVER s4 OPTIONS (ADD modified '1'); -- ERROR 263ALTER USER MAPPING FOR public SERVER t1 OPTIONS (ADD modified '1'); 264RESET ROLE; 265\deu+ 266 267-- DROP USER MAPPING 268DROP USER MAPPING FOR regress_test_missing_role SERVER s4; -- ERROR 269DROP USER MAPPING FOR user SERVER ss4; 270DROP USER MAPPING FOR public SERVER s7; -- ERROR 271DROP USER MAPPING IF EXISTS FOR regress_test_missing_role SERVER s4; 272DROP USER MAPPING IF EXISTS FOR user SERVER ss4; 273DROP USER MAPPING IF EXISTS FOR public SERVER s7; 274CREATE USER MAPPING FOR public SERVER s8; 275SET ROLE regress_test_role; 276DROP USER MAPPING FOR public SERVER s8; -- ERROR 277RESET ROLE; 278DROP SERVER s7; 279\deu 280 281-- CREATE FOREIGN TABLE 282CREATE SCHEMA foreign_schema; 283CREATE SERVER s0 FOREIGN DATA WRAPPER dummy; 284CREATE FOREIGN TABLE ft1 (); -- ERROR 285CREATE FOREIGN TABLE ft1 () SERVER no_server; -- ERROR 286CREATE FOREIGN TABLE ft1 ( 287 c1 integer OPTIONS ("param 1" 'val1') PRIMARY KEY, 288 c2 text OPTIONS (param2 'val2', param3 'val3'), 289 c3 date 290) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR 291CREATE TABLE ref_table (id integer PRIMARY KEY); 292CREATE FOREIGN TABLE ft1 ( 293 c1 integer OPTIONS ("param 1" 'val1') REFERENCES ref_table (id), 294 c2 text OPTIONS (param2 'val2', param3 'val3'), 295 c3 date 296) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR 297DROP TABLE ref_table; 298CREATE FOREIGN TABLE ft1 ( 299 c1 integer OPTIONS ("param 1" 'val1') NOT NULL, 300 c2 text OPTIONS (param2 'val2', param3 'val3'), 301 c3 date, 302 UNIQUE (c3) 303) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR 304CREATE FOREIGN TABLE ft1 ( 305 c1 integer OPTIONS ("param 1" 'val1') NOT NULL, 306 c2 text OPTIONS (param2 'val2', param3 'val3') CHECK (c2 <> ''), 307 c3 date, 308 CHECK (c3 BETWEEN '1994-01-01'::date AND '1994-01-31'::date) 309) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); 310COMMENT ON FOREIGN TABLE ft1 IS 'ft1'; 311COMMENT ON COLUMN ft1.c1 IS 'ft1.c1'; 312\d+ ft1 313\det+ 314CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR 315SELECT * FROM ft1; -- ERROR 316EXPLAIN SELECT * FROM ft1; -- ERROR 317 318CREATE TABLE lt1 (a INT) PARTITION BY RANGE (a); 319CREATE FOREIGN TABLE ft_part1 320 PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) SERVER s0; 321CREATE INDEX ON lt1 (a); -- skips partition 322CREATE UNIQUE INDEX ON lt1 (a); -- ERROR 323ALTER TABLE lt1 ADD PRIMARY KEY (a); -- ERROR 324DROP TABLE lt1; 325 326CREATE TABLE lt1 (a INT) PARTITION BY RANGE (a); 327CREATE INDEX ON lt1 (a); 328CREATE FOREIGN TABLE ft_part1 329 PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) SERVER s0; 330CREATE FOREIGN TABLE ft_part2 (a INT) SERVER s0; 331ALTER TABLE lt1 ATTACH PARTITION ft_part2 FOR VALUES FROM (1000) TO (2000); 332DROP FOREIGN TABLE ft_part1, ft_part2; 333CREATE UNIQUE INDEX ON lt1 (a); 334ALTER TABLE lt1 ADD PRIMARY KEY (a); 335CREATE FOREIGN TABLE ft_part1 336 PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) SERVER s0; -- ERROR 337CREATE FOREIGN TABLE ft_part2 (a INT NOT NULL) SERVER s0; 338ALTER TABLE lt1 ATTACH PARTITION ft_part2 339 FOR VALUES FROM (1000) TO (2000); -- ERROR 340DROP TABLE lt1; 341DROP FOREIGN TABLE ft_part2; 342 343CREATE TABLE lt1 (a INT) PARTITION BY RANGE (a); 344CREATE INDEX ON lt1 (a); 345CREATE TABLE lt1_part1 346 PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) 347 PARTITION BY RANGE (a); 348CREATE FOREIGN TABLE ft_part_1_1 349 PARTITION OF lt1_part1 FOR VALUES FROM (0) TO (100) SERVER s0; 350CREATE FOREIGN TABLE ft_part_1_2 (a INT) SERVER s0; 351ALTER TABLE lt1_part1 ATTACH PARTITION ft_part_1_2 FOR VALUES FROM (100) TO (200); 352CREATE UNIQUE INDEX ON lt1 (a); 353ALTER TABLE lt1 ADD PRIMARY KEY (a); 354DROP FOREIGN TABLE ft_part_1_1, ft_part_1_2; 355CREATE UNIQUE INDEX ON lt1 (a); 356ALTER TABLE lt1 ADD PRIMARY KEY (a); 357CREATE FOREIGN TABLE ft_part_1_1 358 PARTITION OF lt1_part1 FOR VALUES FROM (0) TO (100) SERVER s0; 359CREATE FOREIGN TABLE ft_part_1_2 (a INT NOT NULL) SERVER s0; 360ALTER TABLE lt1_part1 ATTACH PARTITION ft_part_1_2 FOR VALUES FROM (100) TO (200); 361DROP TABLE lt1; 362DROP FOREIGN TABLE ft_part_1_2; 363 364-- ALTER FOREIGN TABLE 365COMMENT ON FOREIGN TABLE ft1 IS 'foreign table'; 366COMMENT ON FOREIGN TABLE ft1 IS NULL; 367COMMENT ON COLUMN ft1.c1 IS 'foreign column'; 368COMMENT ON COLUMN ft1.c1 IS NULL; 369 370ALTER FOREIGN TABLE ft1 ADD COLUMN c4 integer; 371ALTER FOREIGN TABLE ft1 ADD COLUMN c5 integer DEFAULT 0; 372ALTER FOREIGN TABLE ft1 ADD COLUMN c6 integer; 373ALTER FOREIGN TABLE ft1 ADD COLUMN c7 integer NOT NULL; 374ALTER FOREIGN TABLE ft1 ADD COLUMN c8 integer; 375ALTER FOREIGN TABLE ft1 ADD COLUMN c9 integer; 376ALTER FOREIGN TABLE ft1 ADD COLUMN c10 integer OPTIONS (p1 'v1'); 377 378ALTER FOREIGN TABLE ft1 ALTER COLUMN c4 SET DEFAULT 0; 379ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 DROP DEFAULT; 380ALTER FOREIGN TABLE ft1 ALTER COLUMN c6 SET NOT NULL; 381ALTER FOREIGN TABLE ft1 ALTER COLUMN c7 DROP NOT NULL; 382ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10) USING '0'; -- ERROR 383ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10); 384ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE text; 385ALTER FOREIGN TABLE ft1 ALTER COLUMN xmin OPTIONS (ADD p1 'v1'); -- ERROR 386ALTER FOREIGN TABLE ft1 ALTER COLUMN c7 OPTIONS (ADD p1 'v1', ADD p2 'v2'), 387 ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2'); 388ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1); 389ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 SET STATISTICS 10000; 390ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 SET (n_distinct = 100); 391ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STATISTICS -1; 392ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STORAGE PLAIN; 393\d+ ft1 394-- can't change the column type if it's used elsewhere 395CREATE TABLE use_ft1_column_type (x ft1); 396ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR 397DROP TABLE use_ft1_column_type; 398ALTER FOREIGN TABLE ft1 ADD PRIMARY KEY (c7); -- ERROR 399ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NOT VALID; 400ALTER FOREIGN TABLE ft1 ALTER CONSTRAINT ft1_c9_check DEFERRABLE; -- ERROR 401ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c9_check; 402ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR 403ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const; 404ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role; 405ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@'); 406ALTER FOREIGN TABLE ft1 DROP COLUMN no_column; -- ERROR 407ALTER FOREIGN TABLE ft1 DROP COLUMN IF EXISTS no_column; 408ALTER FOREIGN TABLE ft1 DROP COLUMN c9; 409ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema; 410ALTER FOREIGN TABLE ft1 SET TABLESPACE ts; -- ERROR 411ALTER FOREIGN TABLE foreign_schema.ft1 SET TABLESPACE ts; -- ERROR 412ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1; 413ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1; 414\d foreign_schema.foreign_table_1 415 416-- alter noexisting table 417ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c4 integer; 418ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c6 integer; 419ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c7 integer NOT NULL; 420ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c8 integer; 421ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c9 integer; 422ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c10 integer OPTIONS (p1 'v1'); 423 424ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c6 SET NOT NULL; 425ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c7 DROP NOT NULL; 426ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 TYPE char(10); 427ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 SET DATA TYPE text; 428ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c7 OPTIONS (ADD p1 'v1', ADD p2 'v2'), 429 ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2'); 430ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1); 431 432ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP CONSTRAINT IF EXISTS no_const; 433ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP CONSTRAINT ft1_c1_check; 434ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 OWNER TO regress_test_role; 435ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@'); 436ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP COLUMN IF EXISTS no_column; 437ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP COLUMN c9; 438ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 SET SCHEMA foreign_schema; 439ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 RENAME c1 TO foreign_column_1; 440ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 RENAME TO foreign_table_1; 441 442-- Information schema 443 444SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2; 445SELECT * FROM information_schema.foreign_data_wrapper_options ORDER BY 1, 2, 3; 446SELECT * FROM information_schema.foreign_servers ORDER BY 1, 2; 447SELECT * FROM information_schema.foreign_server_options ORDER BY 1, 2, 3; 448SELECT * FROM information_schema.user_mappings ORDER BY lower(authorization_identifier), 2, 3; 449SELECT * FROM information_schema.user_mapping_options ORDER BY lower(authorization_identifier), 2, 3, 4; 450SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5; 451SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5; 452SELECT * FROM information_schema.foreign_tables ORDER BY 1, 2, 3; 453SELECT * FROM information_schema.foreign_table_options ORDER BY 1, 2, 3, 4; 454SET ROLE regress_test_role; 455SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4; 456SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5; 457SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5; 458DROP USER MAPPING FOR current_user SERVER t1; 459SET ROLE regress_test_role2; 460SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4; 461RESET ROLE; 462 463 464-- has_foreign_data_wrapper_privilege 465SELECT has_foreign_data_wrapper_privilege('regress_test_role', 466 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE'); 467SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE'); 468SELECT has_foreign_data_wrapper_privilege( 469 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 470 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE'); 471SELECT has_foreign_data_wrapper_privilege( 472 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE'); 473SELECT has_foreign_data_wrapper_privilege( 474 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 'foo', 'USAGE'); 475SELECT has_foreign_data_wrapper_privilege('foo', 'USAGE'); 476GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; 477SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE'); 478 479-- has_server_privilege 480SELECT has_server_privilege('regress_test_role', 481 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE'); 482SELECT has_server_privilege('regress_test_role', 's8', 'USAGE'); 483SELECT has_server_privilege( 484 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 485 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE'); 486SELECT has_server_privilege( 487 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE'); 488SELECT has_server_privilege( 489 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 's8', 'USAGE'); 490SELECT has_server_privilege('s8', 'USAGE'); 491GRANT USAGE ON FOREIGN SERVER s8 TO regress_test_role; 492SELECT has_server_privilege('regress_test_role', 's8', 'USAGE'); 493REVOKE USAGE ON FOREIGN SERVER s8 FROM regress_test_role; 494 495GRANT USAGE ON FOREIGN SERVER s4 TO regress_test_role; 496DROP USER MAPPING FOR public SERVER s4; 497ALTER SERVER s6 OPTIONS (DROP host, DROP dbname); 498ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (DROP username); 499ALTER FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator; 500 501-- Privileges 502SET ROLE regress_unprivileged_role; 503CREATE FOREIGN DATA WRAPPER foobar; -- ERROR 504ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR 505ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_unprivileged_role; -- ERROR 506DROP FOREIGN DATA WRAPPER foo; -- ERROR 507GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR 508CREATE SERVER s9 FOREIGN DATA WRAPPER foo; -- ERROR 509ALTER SERVER s4 VERSION '0.5'; -- ERROR 510ALTER SERVER s4 OWNER TO regress_unprivileged_role; -- ERROR 511DROP SERVER s4; -- ERROR 512GRANT USAGE ON FOREIGN SERVER s4 TO regress_test_role; -- ERROR 513CREATE USER MAPPING FOR public SERVER s4; -- ERROR 514ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (gotcha 'true'); -- ERROR 515DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR 516RESET ROLE; 517 518GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO regress_unprivileged_role; 519GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_unprivileged_role WITH GRANT OPTION; 520SET ROLE regress_unprivileged_role; 521CREATE FOREIGN DATA WRAPPER foobar; -- ERROR 522ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR 523DROP FOREIGN DATA WRAPPER foo; -- ERROR 524GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO regress_test_role; -- WARNING 525GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; 526CREATE SERVER s9 FOREIGN DATA WRAPPER postgresql; 527ALTER SERVER s6 VERSION '0.5'; -- ERROR 528DROP SERVER s6; -- ERROR 529GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role; -- ERROR 530GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role; 531CREATE USER MAPPING FOR public SERVER s6; -- ERROR 532CREATE USER MAPPING FOR public SERVER s9; 533ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (gotcha 'true'); -- ERROR 534DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR 535RESET ROLE; 536 537REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_unprivileged_role; -- ERROR 538REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_unprivileged_role CASCADE; 539SET ROLE regress_unprivileged_role; 540GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR 541CREATE SERVER s10 FOREIGN DATA WRAPPER foo; -- ERROR 542ALTER SERVER s9 VERSION '1.1'; 543GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role; 544CREATE USER MAPPING FOR current_user SERVER s9; 545DROP SERVER s9 CASCADE; 546RESET ROLE; 547CREATE SERVER s9 FOREIGN DATA WRAPPER foo; 548GRANT USAGE ON FOREIGN SERVER s9 TO regress_unprivileged_role; 549SET ROLE regress_unprivileged_role; 550ALTER SERVER s9 VERSION '1.2'; -- ERROR 551GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role; -- WARNING 552CREATE USER MAPPING FOR current_user SERVER s9; 553DROP SERVER s9 CASCADE; -- ERROR 554 555-- Check visibility of user mapping data 556SET ROLE regress_test_role; 557CREATE SERVER s10 FOREIGN DATA WRAPPER foo; 558CREATE USER MAPPING FOR public SERVER s10 OPTIONS (user 'secret'); 559CREATE USER MAPPING FOR regress_unprivileged_role SERVER s10 OPTIONS (user 'secret'); 560-- owner of server can see some option fields 561\deu+ 562RESET ROLE; 563-- superuser can see all option fields 564\deu+ 565-- unprivileged user cannot see any option field 566SET ROLE regress_unprivileged_role; 567\deu+ 568RESET ROLE; 569DROP SERVER s10 CASCADE; 570 571-- Triggers 572CREATE FUNCTION dummy_trigger() RETURNS TRIGGER AS $$ 573 BEGIN 574 RETURN NULL; 575 END 576$$ language plpgsql; 577 578CREATE TRIGGER trigtest_before_stmt BEFORE INSERT OR UPDATE OR DELETE 579ON foreign_schema.foreign_table_1 580FOR EACH STATEMENT 581EXECUTE PROCEDURE dummy_trigger(); 582 583CREATE TRIGGER trigtest_after_stmt AFTER INSERT OR UPDATE OR DELETE 584ON foreign_schema.foreign_table_1 585FOR EACH STATEMENT 586EXECUTE PROCEDURE dummy_trigger(); 587 588CREATE TRIGGER trigtest_after_stmt_tt AFTER INSERT OR UPDATE OR DELETE -- ERROR 589ON foreign_schema.foreign_table_1 590REFERENCING NEW TABLE AS new_table 591FOR EACH STATEMENT 592EXECUTE PROCEDURE dummy_trigger(); 593 594CREATE TRIGGER trigtest_before_row BEFORE INSERT OR UPDATE OR DELETE 595ON foreign_schema.foreign_table_1 596FOR EACH ROW 597EXECUTE PROCEDURE dummy_trigger(); 598 599CREATE TRIGGER trigtest_after_row AFTER INSERT OR UPDATE OR DELETE 600ON foreign_schema.foreign_table_1 601FOR EACH ROW 602EXECUTE PROCEDURE dummy_trigger(); 603 604CREATE CONSTRAINT TRIGGER trigtest_constraint AFTER INSERT OR UPDATE OR DELETE 605ON foreign_schema.foreign_table_1 606FOR EACH ROW 607EXECUTE PROCEDURE dummy_trigger(); 608 609ALTER FOREIGN TABLE foreign_schema.foreign_table_1 610 DISABLE TRIGGER trigtest_before_stmt; 611ALTER FOREIGN TABLE foreign_schema.foreign_table_1 612 ENABLE TRIGGER trigtest_before_stmt; 613 614DROP TRIGGER trigtest_before_stmt ON foreign_schema.foreign_table_1; 615DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1; 616DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1; 617DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1; 618 619DROP FUNCTION dummy_trigger(); 620 621-- Table inheritance 622CREATE TABLE fd_pt1 ( 623 c1 integer NOT NULL, 624 c2 text, 625 c3 date 626); 627CREATE FOREIGN TABLE ft2 () INHERITS (fd_pt1) 628 SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); 629\d+ fd_pt1 630\d+ ft2 631DROP FOREIGN TABLE ft2; 632\d+ fd_pt1 633CREATE FOREIGN TABLE ft2 ( 634 c1 integer NOT NULL, 635 c2 text, 636 c3 date 637) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); 638\d+ ft2 639ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; 640\d+ fd_pt1 641\d+ ft2 642CREATE TABLE ct3() INHERITS(ft2); 643CREATE FOREIGN TABLE ft3 ( 644 c1 integer NOT NULL, 645 c2 text, 646 c3 date 647) INHERITS(ft2) 648 SERVER s0; 649\d+ ft2 650\d+ ct3 651\d+ ft3 652 653-- add attributes recursively 654ALTER TABLE fd_pt1 ADD COLUMN c4 integer; 655ALTER TABLE fd_pt1 ADD COLUMN c5 integer DEFAULT 0; 656ALTER TABLE fd_pt1 ADD COLUMN c6 integer; 657ALTER TABLE fd_pt1 ADD COLUMN c7 integer NOT NULL; 658ALTER TABLE fd_pt1 ADD COLUMN c8 integer; 659\d+ fd_pt1 660\d+ ft2 661\d+ ct3 662\d+ ft3 663 664-- alter attributes recursively 665ALTER TABLE fd_pt1 ALTER COLUMN c4 SET DEFAULT 0; 666ALTER TABLE fd_pt1 ALTER COLUMN c5 DROP DEFAULT; 667ALTER TABLE fd_pt1 ALTER COLUMN c6 SET NOT NULL; 668ALTER TABLE fd_pt1 ALTER COLUMN c7 DROP NOT NULL; 669ALTER TABLE fd_pt1 ALTER COLUMN c8 TYPE char(10) USING '0'; -- ERROR 670ALTER TABLE fd_pt1 ALTER COLUMN c8 TYPE char(10); 671ALTER TABLE fd_pt1 ALTER COLUMN c8 SET DATA TYPE text; 672ALTER TABLE fd_pt1 ALTER COLUMN c1 SET STATISTICS 10000; 673ALTER TABLE fd_pt1 ALTER COLUMN c1 SET (n_distinct = 100); 674ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STATISTICS -1; 675ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STORAGE EXTERNAL; 676\d+ fd_pt1 677\d+ ft2 678 679-- drop attributes recursively 680ALTER TABLE fd_pt1 DROP COLUMN c4; 681ALTER TABLE fd_pt1 DROP COLUMN c5; 682ALTER TABLE fd_pt1 DROP COLUMN c6; 683ALTER TABLE fd_pt1 DROP COLUMN c7; 684ALTER TABLE fd_pt1 DROP COLUMN c8; 685\d+ fd_pt1 686\d+ ft2 687 688-- add constraints recursively 689ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk1 CHECK (c1 > 0) NO INHERIT; 690ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk2 CHECK (c2 <> ''); 691-- connoinherit should be true for NO INHERIT constraint 692SELECT relname, conname, contype, conislocal, coninhcount, connoinherit 693 FROM pg_class AS pc JOIN pg_constraint AS pgc ON (conrelid = pc.oid) 694 WHERE pc.relname = 'fd_pt1' 695 ORDER BY 1,2; 696-- child does not inherit NO INHERIT constraints 697\d+ fd_pt1 698\d+ ft2 699DROP FOREIGN TABLE ft2; -- ERROR 700DROP FOREIGN TABLE ft2 CASCADE; 701CREATE FOREIGN TABLE ft2 ( 702 c1 integer NOT NULL, 703 c2 text, 704 c3 date 705) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); 706-- child must have parent's INHERIT constraints 707ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; -- ERROR 708ALTER FOREIGN TABLE ft2 ADD CONSTRAINT fd_pt1chk2 CHECK (c2 <> ''); 709ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; 710-- child does not inherit NO INHERIT constraints 711\d+ fd_pt1 712\d+ ft2 713 714-- drop constraints recursively 715ALTER TABLE fd_pt1 DROP CONSTRAINT fd_pt1chk1 CASCADE; 716ALTER TABLE fd_pt1 DROP CONSTRAINT fd_pt1chk2 CASCADE; 717 718-- NOT VALID case 719INSERT INTO fd_pt1 VALUES (1, 'fd_pt1'::text, '1994-01-01'::date); 720ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk3 CHECK (c2 <> '') NOT VALID; 721\d+ fd_pt1 722\d+ ft2 723-- VALIDATE CONSTRAINT need do nothing on foreign tables 724ALTER TABLE fd_pt1 VALIDATE CONSTRAINT fd_pt1chk3; 725\d+ fd_pt1 726\d+ ft2 727 728-- changes name of an attribute recursively 729ALTER TABLE fd_pt1 RENAME COLUMN c1 TO f1; 730ALTER TABLE fd_pt1 RENAME COLUMN c2 TO f2; 731ALTER TABLE fd_pt1 RENAME COLUMN c3 TO f3; 732-- changes name of a constraint recursively 733ALTER TABLE fd_pt1 RENAME CONSTRAINT fd_pt1chk3 TO f2_check; 734\d+ fd_pt1 735\d+ ft2 736 737-- TRUNCATE doesn't work on foreign tables, either directly or recursively 738TRUNCATE ft2; -- ERROR 739TRUNCATE fd_pt1; -- ERROR 740 741DROP TABLE fd_pt1 CASCADE; 742 743-- IMPORT FOREIGN SCHEMA 744IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR 745IMPORT FOREIGN SCHEMA s1 LIMIT TO (t1) FROM SERVER s9 INTO public; --ERROR 746IMPORT FOREIGN SCHEMA s1 EXCEPT (t1) FROM SERVER s9 INTO public; -- ERROR 747IMPORT FOREIGN SCHEMA s1 EXCEPT (t1, t2) FROM SERVER s9 INTO public 748OPTIONS (option1 'value1', option2 'value2'); -- ERROR 749 750-- DROP FOREIGN TABLE 751DROP FOREIGN TABLE no_table; -- ERROR 752DROP FOREIGN TABLE IF EXISTS no_table; 753DROP FOREIGN TABLE foreign_schema.foreign_table_1; 754 755-- REASSIGN OWNED/DROP OWNED of foreign objects 756REASSIGN OWNED BY regress_test_role TO regress_test_role2; 757DROP OWNED BY regress_test_role2; 758DROP OWNED BY regress_test_role2 CASCADE; 759 760-- Foreign partition DDL stuff 761CREATE TABLE fd_pt2 ( 762 c1 integer NOT NULL, 763 c2 text, 764 c3 date 765) PARTITION BY LIST (c1); 766CREATE FOREIGN TABLE fd_pt2_1 PARTITION OF fd_pt2 FOR VALUES IN (1) 767 SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); 768\d+ fd_pt2 769\d+ fd_pt2_1 770 771-- partition cannot have additional columns 772DROP FOREIGN TABLE fd_pt2_1; 773CREATE FOREIGN TABLE fd_pt2_1 ( 774 c1 integer NOT NULL, 775 c2 text, 776 c3 date, 777 c4 char 778) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); 779\d+ fd_pt2_1 780ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); -- ERROR 781 782DROP FOREIGN TABLE fd_pt2_1; 783\d+ fd_pt2 784CREATE FOREIGN TABLE fd_pt2_1 ( 785 c1 integer NOT NULL, 786 c2 text, 787 c3 date 788) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); 789\d+ fd_pt2_1 790-- no attach partition validation occurs for foreign tables 791ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); 792\d+ fd_pt2 793\d+ fd_pt2_1 794 795-- cannot add column to a partition 796ALTER TABLE fd_pt2_1 ADD c4 char; 797 798-- ok to have a partition's own constraints though 799ALTER TABLE fd_pt2_1 ALTER c3 SET NOT NULL; 800ALTER TABLE fd_pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <> ''); 801\d+ fd_pt2 802\d+ fd_pt2_1 803 804-- cannot drop inherited NOT NULL constraint from a partition 805ALTER TABLE fd_pt2_1 ALTER c1 DROP NOT NULL; 806 807-- partition must have parent's constraints 808ALTER TABLE fd_pt2 DETACH PARTITION fd_pt2_1; 809ALTER TABLE fd_pt2 ALTER c2 SET NOT NULL; 810\d+ fd_pt2 811\d+ fd_pt2_1 812ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); -- ERROR 813ALTER FOREIGN TABLE fd_pt2_1 ALTER c2 SET NOT NULL; 814ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); 815 816ALTER TABLE fd_pt2 DETACH PARTITION fd_pt2_1; 817ALTER TABLE fd_pt2 ADD CONSTRAINT fd_pt2chk1 CHECK (c1 > 0); 818\d+ fd_pt2 819\d+ fd_pt2_1 820ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); -- ERROR 821ALTER FOREIGN TABLE fd_pt2_1 ADD CONSTRAINT fd_pt2chk1 CHECK (c1 > 0); 822ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); 823 824-- TRUNCATE doesn't work on foreign tables, either directly or recursively 825TRUNCATE fd_pt2_1; -- ERROR 826TRUNCATE fd_pt2; -- ERROR 827 828DROP FOREIGN TABLE fd_pt2_1; 829DROP TABLE fd_pt2; 830 831-- foreign table cannot be part of partition tree made of temporary 832-- relations. 833CREATE TEMP TABLE temp_parted (a int) PARTITION BY LIST (a); 834CREATE FOREIGN TABLE foreign_part PARTITION OF temp_parted DEFAULT 835 SERVER s0; -- ERROR 836CREATE FOREIGN TABLE foreign_part (a int) SERVER s0; 837ALTER TABLE temp_parted ATTACH PARTITION foreign_part DEFAULT; -- ERROR 838DROP FOREIGN TABLE foreign_part; 839DROP TABLE temp_parted; 840 841-- Cleanup 842DROP SCHEMA foreign_schema CASCADE; 843DROP ROLE regress_test_role; -- ERROR 844DROP SERVER t1 CASCADE; 845DROP USER MAPPING FOR regress_test_role SERVER s6; 846DROP FOREIGN DATA WRAPPER foo CASCADE; 847DROP SERVER s8 CASCADE; 848DROP ROLE regress_test_indirect; 849DROP ROLE regress_test_role; 850DROP ROLE regress_unprivileged_role; -- ERROR 851REVOKE ALL ON FOREIGN DATA WRAPPER postgresql FROM regress_unprivileged_role; 852DROP ROLE regress_unprivileged_role; 853DROP ROLE regress_test_role2; 854DROP FOREIGN DATA WRAPPER postgresql CASCADE; 855DROP FOREIGN DATA WRAPPER dummy CASCADE; 856\c 857DROP ROLE regress_foreign_data_user; 858 859-- At this point we should have no wrappers, no servers, and no mappings. 860SELECT fdwname, fdwhandler, fdwvalidator, fdwoptions FROM pg_foreign_data_wrapper; 861SELECT srvname, srvoptions FROM pg_foreign_server; 862SELECT * FROM pg_user_mapping; 863