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 () SERVER s0 WITH OIDS; -- ERROR 287CREATE FOREIGN TABLE ft1 ( 288 c1 integer OPTIONS ("param 1" 'val1') PRIMARY KEY, 289 c2 text OPTIONS (param2 'val2', param3 'val3'), 290 c3 date 291) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR 292CREATE TABLE ref_table (id integer PRIMARY KEY); 293CREATE FOREIGN TABLE ft1 ( 294 c1 integer OPTIONS ("param 1" 'val1') REFERENCES ref_table (id), 295 c2 text OPTIONS (param2 'val2', param3 'val3'), 296 c3 date 297) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR 298DROP TABLE ref_table; 299CREATE FOREIGN TABLE ft1 ( 300 c1 integer OPTIONS ("param 1" 'val1') NOT NULL, 301 c2 text OPTIONS (param2 'val2', param3 'val3'), 302 c3 date, 303 UNIQUE (c3) 304) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR 305CREATE FOREIGN TABLE ft1 ( 306 c1 integer OPTIONS ("param 1" 'val1') NOT NULL, 307 c2 text OPTIONS (param2 'val2', param3 'val3') CHECK (c2 <> ''), 308 c3 date, 309 CHECK (c3 BETWEEN '1994-01-01'::date AND '1994-01-31'::date) 310) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); 311COMMENT ON FOREIGN TABLE ft1 IS 'ft1'; 312COMMENT ON COLUMN ft1.c1 IS 'ft1.c1'; 313\d+ ft1 314\det+ 315CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR 316SELECT * FROM ft1; -- ERROR 317EXPLAIN SELECT * FROM ft1; -- ERROR 318 319CREATE TABLE lt1 (a INT) PARTITION BY RANGE (a); 320CREATE FOREIGN TABLE ft_part1 321 PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) SERVER s0; 322CREATE INDEX ON lt1 (a); -- skips partition 323CREATE UNIQUE INDEX ON lt1 (a); -- ERROR 324ALTER TABLE lt1 ADD PRIMARY KEY (a); -- ERROR 325DROP TABLE lt1; 326 327CREATE TABLE lt1 (a INT) PARTITION BY RANGE (a); 328CREATE INDEX ON lt1 (a); 329CREATE FOREIGN TABLE ft_part1 330 PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) SERVER s0; 331CREATE FOREIGN TABLE ft_part2 (a INT) SERVER s0; 332ALTER TABLE lt1 ATTACH PARTITION ft_part2 FOR VALUES FROM (1000) TO (2000); 333DROP FOREIGN TABLE ft_part1, ft_part2; 334CREATE UNIQUE INDEX ON lt1 (a); 335ALTER TABLE lt1 ADD PRIMARY KEY (a); 336CREATE FOREIGN TABLE ft_part1 337 PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) SERVER s0; -- ERROR 338CREATE FOREIGN TABLE ft_part2 (a INT NOT NULL) SERVER s0; 339ALTER TABLE lt1 ATTACH PARTITION ft_part2 340 FOR VALUES FROM (1000) TO (2000); -- ERROR 341DROP TABLE lt1; 342DROP FOREIGN TABLE ft_part2; 343 344CREATE TABLE lt1 (a INT) PARTITION BY RANGE (a); 345CREATE INDEX ON lt1 (a); 346CREATE TABLE lt1_part1 347 PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) 348 PARTITION BY RANGE (a); 349CREATE FOREIGN TABLE ft_part_1_1 350 PARTITION OF lt1_part1 FOR VALUES FROM (0) TO (100) SERVER s0; 351CREATE FOREIGN TABLE ft_part_1_2 (a INT) SERVER s0; 352ALTER TABLE lt1_part1 ATTACH PARTITION ft_part_1_2 FOR VALUES FROM (100) TO (200); 353CREATE UNIQUE INDEX ON lt1 (a); 354ALTER TABLE lt1 ADD PRIMARY KEY (a); 355DROP FOREIGN TABLE ft_part_1_1, ft_part_1_2; 356CREATE UNIQUE INDEX ON lt1 (a); 357ALTER TABLE lt1 ADD PRIMARY KEY (a); 358CREATE FOREIGN TABLE ft_part_1_1 359 PARTITION OF lt1_part1 FOR VALUES FROM (0) TO (100) SERVER s0; 360CREATE FOREIGN TABLE ft_part_1_2 (a INT NOT NULL) SERVER s0; 361ALTER TABLE lt1_part1 ATTACH PARTITION ft_part_1_2 FOR VALUES FROM (100) TO (200); 362DROP TABLE lt1; 363DROP FOREIGN TABLE ft_part_1_2; 364 365-- ALTER FOREIGN TABLE 366COMMENT ON FOREIGN TABLE ft1 IS 'foreign table'; 367COMMENT ON FOREIGN TABLE ft1 IS NULL; 368COMMENT ON COLUMN ft1.c1 IS 'foreign column'; 369COMMENT ON COLUMN ft1.c1 IS NULL; 370 371ALTER FOREIGN TABLE ft1 ADD COLUMN c4 integer; 372ALTER FOREIGN TABLE ft1 ADD COLUMN c5 integer DEFAULT 0; 373ALTER FOREIGN TABLE ft1 ADD COLUMN c6 integer; 374ALTER FOREIGN TABLE ft1 ADD COLUMN c7 integer NOT NULL; 375ALTER FOREIGN TABLE ft1 ADD COLUMN c8 integer; 376ALTER FOREIGN TABLE ft1 ADD COLUMN c9 integer; 377ALTER FOREIGN TABLE ft1 ADD COLUMN c10 integer OPTIONS (p1 'v1'); 378 379ALTER FOREIGN TABLE ft1 ALTER COLUMN c4 SET DEFAULT 0; 380ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 DROP DEFAULT; 381ALTER FOREIGN TABLE ft1 ALTER COLUMN c6 SET NOT NULL; 382ALTER FOREIGN TABLE ft1 ALTER COLUMN c7 DROP NOT NULL; 383ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10) USING '0'; -- ERROR 384ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10); 385ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE text; 386ALTER FOREIGN TABLE ft1 ALTER COLUMN xmin OPTIONS (ADD p1 'v1'); -- ERROR 387ALTER FOREIGN TABLE ft1 ALTER COLUMN c7 OPTIONS (ADD p1 'v1', ADD p2 'v2'), 388 ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2'); 389ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1); 390ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 SET STATISTICS 10000; 391ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 SET (n_distinct = 100); 392ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STATISTICS -1; 393ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STORAGE PLAIN; 394\d+ ft1 395-- can't change the column type if it's used elsewhere 396CREATE TABLE use_ft1_column_type (x ft1); 397ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR 398DROP TABLE use_ft1_column_type; 399ALTER FOREIGN TABLE ft1 ADD PRIMARY KEY (c7); -- ERROR 400ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NOT VALID; 401ALTER FOREIGN TABLE ft1 ALTER CONSTRAINT ft1_c9_check DEFERRABLE; -- ERROR 402ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c9_check; 403ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR 404ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const; 405ALTER FOREIGN TABLE ft1 SET WITH OIDS; 406ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role; 407ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@'); 408ALTER FOREIGN TABLE ft1 DROP COLUMN no_column; -- ERROR 409ALTER FOREIGN TABLE ft1 DROP COLUMN IF EXISTS no_column; 410ALTER FOREIGN TABLE ft1 DROP COLUMN c9; 411ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema; 412ALTER FOREIGN TABLE ft1 SET TABLESPACE ts; -- ERROR 413ALTER FOREIGN TABLE foreign_schema.ft1 SET TABLESPACE ts; -- ERROR 414ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1; 415ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1; 416\d foreign_schema.foreign_table_1 417 418-- alter noexisting table 419ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c4 integer; 420ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c6 integer; 421ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c7 integer NOT NULL; 422ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c8 integer; 423ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c9 integer; 424ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c10 integer OPTIONS (p1 'v1'); 425 426ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c6 SET NOT NULL; 427ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c7 DROP NOT NULL; 428ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 TYPE char(10); 429ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 SET DATA TYPE text; 430ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c7 OPTIONS (ADD p1 'v1', ADD p2 'v2'), 431 ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2'); 432ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1); 433 434ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP CONSTRAINT IF EXISTS no_const; 435ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP CONSTRAINT ft1_c1_check; 436ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 OWNER TO regress_test_role; 437ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@'); 438ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP COLUMN IF EXISTS no_column; 439ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP COLUMN c9; 440ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 SET SCHEMA foreign_schema; 441ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 RENAME c1 TO foreign_column_1; 442ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 RENAME TO foreign_table_1; 443 444-- Information schema 445 446SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2; 447SELECT * FROM information_schema.foreign_data_wrapper_options ORDER BY 1, 2, 3; 448SELECT * FROM information_schema.foreign_servers ORDER BY 1, 2; 449SELECT * FROM information_schema.foreign_server_options ORDER BY 1, 2, 3; 450SELECT * FROM information_schema.user_mappings ORDER BY lower(authorization_identifier), 2, 3; 451SELECT * FROM information_schema.user_mapping_options ORDER BY lower(authorization_identifier), 2, 3, 4; 452SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5; 453SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5; 454SELECT * FROM information_schema.foreign_tables ORDER BY 1, 2, 3; 455SELECT * FROM information_schema.foreign_table_options ORDER BY 1, 2, 3, 4; 456SET ROLE regress_test_role; 457SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4; 458SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5; 459SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5; 460DROP USER MAPPING FOR current_user SERVER t1; 461SET ROLE regress_test_role2; 462SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4; 463RESET ROLE; 464 465 466-- has_foreign_data_wrapper_privilege 467SELECT has_foreign_data_wrapper_privilege('regress_test_role', 468 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE'); 469SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE'); 470SELECT has_foreign_data_wrapper_privilege( 471 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 472 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE'); 473SELECT has_foreign_data_wrapper_privilege( 474 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE'); 475SELECT has_foreign_data_wrapper_privilege( 476 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 'foo', 'USAGE'); 477SELECT has_foreign_data_wrapper_privilege('foo', 'USAGE'); 478GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; 479SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE'); 480 481-- has_server_privilege 482SELECT has_server_privilege('regress_test_role', 483 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE'); 484SELECT has_server_privilege('regress_test_role', 's8', 'USAGE'); 485SELECT has_server_privilege( 486 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 487 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE'); 488SELECT has_server_privilege( 489 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE'); 490SELECT has_server_privilege( 491 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 's8', 'USAGE'); 492SELECT has_server_privilege('s8', 'USAGE'); 493GRANT USAGE ON FOREIGN SERVER s8 TO regress_test_role; 494SELECT has_server_privilege('regress_test_role', 's8', 'USAGE'); 495REVOKE USAGE ON FOREIGN SERVER s8 FROM regress_test_role; 496 497GRANT USAGE ON FOREIGN SERVER s4 TO regress_test_role; 498DROP USER MAPPING FOR public SERVER s4; 499ALTER SERVER s6 OPTIONS (DROP host, DROP dbname); 500ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (DROP username); 501ALTER FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator; 502 503-- Privileges 504SET ROLE regress_unprivileged_role; 505CREATE FOREIGN DATA WRAPPER foobar; -- ERROR 506ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR 507ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_unprivileged_role; -- ERROR 508DROP FOREIGN DATA WRAPPER foo; -- ERROR 509GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR 510CREATE SERVER s9 FOREIGN DATA WRAPPER foo; -- ERROR 511ALTER SERVER s4 VERSION '0.5'; -- ERROR 512ALTER SERVER s4 OWNER TO regress_unprivileged_role; -- ERROR 513DROP SERVER s4; -- ERROR 514GRANT USAGE ON FOREIGN SERVER s4 TO regress_test_role; -- ERROR 515CREATE USER MAPPING FOR public SERVER s4; -- ERROR 516ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (gotcha 'true'); -- ERROR 517DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR 518RESET ROLE; 519 520GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO regress_unprivileged_role; 521GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_unprivileged_role WITH GRANT OPTION; 522SET ROLE regress_unprivileged_role; 523CREATE FOREIGN DATA WRAPPER foobar; -- ERROR 524ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR 525DROP FOREIGN DATA WRAPPER foo; -- ERROR 526GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO regress_test_role; -- WARNING 527GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; 528CREATE SERVER s9 FOREIGN DATA WRAPPER postgresql; 529ALTER SERVER s6 VERSION '0.5'; -- ERROR 530DROP SERVER s6; -- ERROR 531GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role; -- ERROR 532GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role; 533CREATE USER MAPPING FOR public SERVER s6; -- ERROR 534CREATE USER MAPPING FOR public SERVER s9; 535ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (gotcha 'true'); -- ERROR 536DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR 537RESET ROLE; 538 539REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_unprivileged_role; -- ERROR 540REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_unprivileged_role CASCADE; 541SET ROLE regress_unprivileged_role; 542GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR 543CREATE SERVER s10 FOREIGN DATA WRAPPER foo; -- ERROR 544ALTER SERVER s9 VERSION '1.1'; 545GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role; 546CREATE USER MAPPING FOR current_user SERVER s9; 547-- We use terse mode to avoid ordering issues in cascade detail output. 548\set VERBOSITY terse 549DROP SERVER s9 CASCADE; 550\set VERBOSITY default 551RESET ROLE; 552CREATE SERVER s9 FOREIGN DATA WRAPPER foo; 553GRANT USAGE ON FOREIGN SERVER s9 TO regress_unprivileged_role; 554SET ROLE regress_unprivileged_role; 555ALTER SERVER s9 VERSION '1.2'; -- ERROR 556GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role; -- WARNING 557CREATE USER MAPPING FOR current_user SERVER s9; 558DROP SERVER s9 CASCADE; -- ERROR 559 560-- Check visibility of user mapping data 561SET ROLE regress_test_role; 562CREATE SERVER s10 FOREIGN DATA WRAPPER foo; 563CREATE USER MAPPING FOR public SERVER s10 OPTIONS (user 'secret'); 564CREATE USER MAPPING FOR regress_unprivileged_role SERVER s10 OPTIONS (user 'secret'); 565-- owner of server can see some option fields 566\deu+ 567RESET ROLE; 568-- superuser can see all option fields 569\deu+ 570-- unprivileged user cannot see any option field 571SET ROLE regress_unprivileged_role; 572\deu+ 573RESET ROLE; 574\set VERBOSITY terse 575DROP SERVER s10 CASCADE; 576\set VERBOSITY default 577 578-- Triggers 579CREATE FUNCTION dummy_trigger() RETURNS TRIGGER AS $$ 580 BEGIN 581 RETURN NULL; 582 END 583$$ language plpgsql; 584 585CREATE TRIGGER trigtest_before_stmt BEFORE INSERT OR UPDATE OR DELETE 586ON foreign_schema.foreign_table_1 587FOR EACH STATEMENT 588EXECUTE PROCEDURE dummy_trigger(); 589 590CREATE TRIGGER trigtest_after_stmt AFTER INSERT OR UPDATE OR DELETE 591ON foreign_schema.foreign_table_1 592FOR EACH STATEMENT 593EXECUTE PROCEDURE dummy_trigger(); 594 595CREATE TRIGGER trigtest_after_stmt_tt AFTER INSERT OR UPDATE OR DELETE -- ERROR 596ON foreign_schema.foreign_table_1 597REFERENCING NEW TABLE AS new_table 598FOR EACH STATEMENT 599EXECUTE PROCEDURE dummy_trigger(); 600 601CREATE TRIGGER trigtest_before_row BEFORE INSERT OR UPDATE OR DELETE 602ON foreign_schema.foreign_table_1 603FOR EACH ROW 604EXECUTE PROCEDURE dummy_trigger(); 605 606CREATE TRIGGER trigtest_after_row AFTER INSERT OR UPDATE OR DELETE 607ON foreign_schema.foreign_table_1 608FOR EACH ROW 609EXECUTE PROCEDURE dummy_trigger(); 610 611CREATE CONSTRAINT TRIGGER trigtest_constraint AFTER INSERT OR UPDATE OR DELETE 612ON foreign_schema.foreign_table_1 613FOR EACH ROW 614EXECUTE PROCEDURE dummy_trigger(); 615 616ALTER FOREIGN TABLE foreign_schema.foreign_table_1 617 DISABLE TRIGGER trigtest_before_stmt; 618ALTER FOREIGN TABLE foreign_schema.foreign_table_1 619 ENABLE TRIGGER trigtest_before_stmt; 620 621DROP TRIGGER trigtest_before_stmt ON foreign_schema.foreign_table_1; 622DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1; 623DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1; 624DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1; 625 626DROP FUNCTION dummy_trigger(); 627 628-- Table inheritance 629CREATE TABLE fd_pt1 ( 630 c1 integer NOT NULL, 631 c2 text, 632 c3 date 633); 634CREATE FOREIGN TABLE ft2 () INHERITS (fd_pt1) 635 SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); 636\d+ fd_pt1 637\d+ ft2 638DROP FOREIGN TABLE ft2; 639\d+ fd_pt1 640CREATE FOREIGN TABLE ft2 ( 641 c1 integer NOT NULL, 642 c2 text, 643 c3 date 644) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); 645\d+ ft2 646ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; 647\d+ fd_pt1 648\d+ ft2 649CREATE TABLE ct3() INHERITS(ft2); 650CREATE FOREIGN TABLE ft3 ( 651 c1 integer NOT NULL, 652 c2 text, 653 c3 date 654) INHERITS(ft2) 655 SERVER s0; 656\d+ ft2 657\d+ ct3 658\d+ ft3 659 660-- add attributes recursively 661ALTER TABLE fd_pt1 ADD COLUMN c4 integer; 662ALTER TABLE fd_pt1 ADD COLUMN c5 integer DEFAULT 0; 663ALTER TABLE fd_pt1 ADD COLUMN c6 integer; 664ALTER TABLE fd_pt1 ADD COLUMN c7 integer NOT NULL; 665ALTER TABLE fd_pt1 ADD COLUMN c8 integer; 666\d+ fd_pt1 667\d+ ft2 668\d+ ct3 669\d+ ft3 670 671-- alter attributes recursively 672ALTER TABLE fd_pt1 ALTER COLUMN c4 SET DEFAULT 0; 673ALTER TABLE fd_pt1 ALTER COLUMN c5 DROP DEFAULT; 674ALTER TABLE fd_pt1 ALTER COLUMN c6 SET NOT NULL; 675ALTER TABLE fd_pt1 ALTER COLUMN c7 DROP NOT NULL; 676ALTER TABLE fd_pt1 ALTER COLUMN c8 TYPE char(10) USING '0'; -- ERROR 677ALTER TABLE fd_pt1 ALTER COLUMN c8 TYPE char(10); 678ALTER TABLE fd_pt1 ALTER COLUMN c8 SET DATA TYPE text; 679ALTER TABLE fd_pt1 ALTER COLUMN c1 SET STATISTICS 10000; 680ALTER TABLE fd_pt1 ALTER COLUMN c1 SET (n_distinct = 100); 681ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STATISTICS -1; 682ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STORAGE EXTERNAL; 683\d+ fd_pt1 684\d+ ft2 685 686-- drop attributes recursively 687ALTER TABLE fd_pt1 DROP COLUMN c4; 688ALTER TABLE fd_pt1 DROP COLUMN c5; 689ALTER TABLE fd_pt1 DROP COLUMN c6; 690ALTER TABLE fd_pt1 DROP COLUMN c7; 691ALTER TABLE fd_pt1 DROP COLUMN c8; 692\d+ fd_pt1 693\d+ ft2 694 695-- add constraints recursively 696ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk1 CHECK (c1 > 0) NO INHERIT; 697ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk2 CHECK (c2 <> ''); 698-- connoinherit should be true for NO INHERIT constraint 699SELECT relname, conname, contype, conislocal, coninhcount, connoinherit 700 FROM pg_class AS pc JOIN pg_constraint AS pgc ON (conrelid = pc.oid) 701 WHERE pc.relname = 'fd_pt1' 702 ORDER BY 1,2; 703-- child does not inherit NO INHERIT constraints 704\d+ fd_pt1 705\d+ ft2 706\set VERBOSITY terse 707DROP FOREIGN TABLE ft2; -- ERROR 708DROP FOREIGN TABLE ft2 CASCADE; 709\set VERBOSITY default 710CREATE FOREIGN TABLE ft2 ( 711 c1 integer NOT NULL, 712 c2 text, 713 c3 date 714) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); 715-- child must have parent's INHERIT constraints 716ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; -- ERROR 717ALTER FOREIGN TABLE ft2 ADD CONSTRAINT fd_pt1chk2 CHECK (c2 <> ''); 718ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; 719-- child does not inherit NO INHERIT constraints 720\d+ fd_pt1 721\d+ ft2 722 723-- drop constraints recursively 724ALTER TABLE fd_pt1 DROP CONSTRAINT fd_pt1chk1 CASCADE; 725ALTER TABLE fd_pt1 DROP CONSTRAINT fd_pt1chk2 CASCADE; 726 727-- NOT VALID case 728INSERT INTO fd_pt1 VALUES (1, 'fd_pt1'::text, '1994-01-01'::date); 729ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk3 CHECK (c2 <> '') NOT VALID; 730\d+ fd_pt1 731\d+ ft2 732-- VALIDATE CONSTRAINT need do nothing on foreign tables 733ALTER TABLE fd_pt1 VALIDATE CONSTRAINT fd_pt1chk3; 734\d+ fd_pt1 735\d+ ft2 736 737-- OID system column 738ALTER TABLE fd_pt1 SET WITH OIDS; 739\d+ fd_pt1 740\d+ ft2 741ALTER TABLE ft2 SET WITHOUT OIDS; -- ERROR 742ALTER TABLE fd_pt1 SET WITHOUT OIDS; 743\d+ fd_pt1 744\d+ ft2 745 746-- changes name of an attribute recursively 747ALTER TABLE fd_pt1 RENAME COLUMN c1 TO f1; 748ALTER TABLE fd_pt1 RENAME COLUMN c2 TO f2; 749ALTER TABLE fd_pt1 RENAME COLUMN c3 TO f3; 750-- changes name of a constraint recursively 751ALTER TABLE fd_pt1 RENAME CONSTRAINT fd_pt1chk3 TO f2_check; 752\d+ fd_pt1 753\d+ ft2 754 755-- TRUNCATE doesn't work on foreign tables, either directly or recursively 756TRUNCATE ft2; -- ERROR 757TRUNCATE fd_pt1; -- ERROR 758 759DROP TABLE fd_pt1 CASCADE; 760 761-- IMPORT FOREIGN SCHEMA 762IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR 763IMPORT FOREIGN SCHEMA s1 LIMIT TO (t1) FROM SERVER s9 INTO public; --ERROR 764IMPORT FOREIGN SCHEMA s1 EXCEPT (t1) FROM SERVER s9 INTO public; -- ERROR 765IMPORT FOREIGN SCHEMA s1 EXCEPT (t1, t2) FROM SERVER s9 INTO public 766OPTIONS (option1 'value1', option2 'value2'); -- ERROR 767 768-- DROP FOREIGN TABLE 769DROP FOREIGN TABLE no_table; -- ERROR 770DROP FOREIGN TABLE IF EXISTS no_table; 771DROP FOREIGN TABLE foreign_schema.foreign_table_1; 772 773-- REASSIGN OWNED/DROP OWNED of foreign objects 774REASSIGN OWNED BY regress_test_role TO regress_test_role2; 775DROP OWNED BY regress_test_role2; 776DROP OWNED BY regress_test_role2 CASCADE; 777 778-- Foreign partition DDL stuff 779CREATE TABLE fd_pt2 ( 780 c1 integer NOT NULL, 781 c2 text, 782 c3 date 783) PARTITION BY LIST (c1); 784CREATE FOREIGN TABLE fd_pt2_1 PARTITION OF fd_pt2 FOR VALUES IN (1) 785 SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); 786\d+ fd_pt2 787\d+ fd_pt2_1 788 789-- partition cannot have additional columns 790DROP FOREIGN TABLE fd_pt2_1; 791CREATE FOREIGN TABLE fd_pt2_1 ( 792 c1 integer NOT NULL, 793 c2 text, 794 c3 date, 795 c4 char 796) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); 797\d+ fd_pt2_1 798ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); -- ERROR 799 800DROP FOREIGN TABLE fd_pt2_1; 801\d+ fd_pt2 802CREATE FOREIGN TABLE fd_pt2_1 ( 803 c1 integer NOT NULL, 804 c2 text, 805 c3 date 806) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); 807\d+ fd_pt2_1 808-- no attach partition validation occurs for foreign tables 809ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); 810\d+ fd_pt2 811\d+ fd_pt2_1 812 813-- cannot add column to a partition 814ALTER TABLE fd_pt2_1 ADD c4 char; 815 816-- ok to have a partition's own constraints though 817ALTER TABLE fd_pt2_1 ALTER c3 SET NOT NULL; 818ALTER TABLE fd_pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <> ''); 819\d+ fd_pt2 820\d+ fd_pt2_1 821 822-- cannot drop inherited NOT NULL constraint from a partition 823ALTER TABLE fd_pt2_1 ALTER c1 DROP NOT NULL; 824 825-- partition must have parent's constraints 826ALTER TABLE fd_pt2 DETACH PARTITION fd_pt2_1; 827ALTER TABLE fd_pt2 ALTER c2 SET NOT NULL; 828\d+ fd_pt2 829\d+ fd_pt2_1 830ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); -- ERROR 831ALTER FOREIGN TABLE fd_pt2_1 ALTER c2 SET NOT NULL; 832ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); 833 834ALTER TABLE fd_pt2 DETACH PARTITION fd_pt2_1; 835ALTER TABLE fd_pt2 ADD CONSTRAINT fd_pt2chk1 CHECK (c1 > 0); 836\d+ fd_pt2 837\d+ fd_pt2_1 838ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); -- ERROR 839ALTER FOREIGN TABLE fd_pt2_1 ADD CONSTRAINT fd_pt2chk1 CHECK (c1 > 0); 840ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); 841 842-- TRUNCATE doesn't work on foreign tables, either directly or recursively 843TRUNCATE fd_pt2_1; -- ERROR 844TRUNCATE fd_pt2; -- ERROR 845 846DROP FOREIGN TABLE fd_pt2_1; 847DROP TABLE fd_pt2; 848 849-- foreign table cannot be part of partition tree made of temporary 850-- relations. 851CREATE TEMP TABLE temp_parted (a int) PARTITION BY LIST (a); 852CREATE FOREIGN TABLE foreign_part PARTITION OF temp_parted DEFAULT 853 SERVER s0; -- ERROR 854CREATE FOREIGN TABLE foreign_part (a int) SERVER s0; 855ALTER TABLE temp_parted ATTACH PARTITION foreign_part DEFAULT; -- ERROR 856DROP FOREIGN TABLE foreign_part; 857DROP TABLE temp_parted; 858 859-- Cleanup 860DROP SCHEMA foreign_schema CASCADE; 861DROP ROLE regress_test_role; -- ERROR 862DROP SERVER t1 CASCADE; 863DROP USER MAPPING FOR regress_test_role SERVER s6; 864\set VERBOSITY terse 865DROP FOREIGN DATA WRAPPER foo CASCADE; 866DROP SERVER s8 CASCADE; 867\set VERBOSITY default 868DROP ROLE regress_test_indirect; 869DROP ROLE regress_test_role; 870DROP ROLE regress_unprivileged_role; -- ERROR 871REVOKE ALL ON FOREIGN DATA WRAPPER postgresql FROM regress_unprivileged_role; 872DROP ROLE regress_unprivileged_role; 873DROP ROLE regress_test_role2; 874DROP FOREIGN DATA WRAPPER postgresql CASCADE; 875DROP FOREIGN DATA WRAPPER dummy CASCADE; 876\c 877DROP ROLE regress_foreign_data_user; 878 879-- At this point we should have no wrappers, no servers, and no mappings. 880SELECT fdwname, fdwhandler, fdwvalidator, fdwoptions FROM pg_foreign_data_wrapper; 881SELECT srvname, srvoptions FROM pg_foreign_server; 882SELECT * FROM pg_user_mapping; 883