| Quellcodebibliothek Statistik Leitseite products/Sources/formale Sprachen/C/Postgres/src/test/regress/expected/ (Postgres Database Version 18.4©) Datei vom 11.4.2026 mit Größe 123 kB |
|
Quelle foreign_data.out Sprache: unbekannt |
|
|
Spracherkennung für: .out vermutete Sprache: Unknown {[0] [0] [0]} [Methode: Schwerpunktbildung, einfache Gewichte, sechs Dimensionen] --
-- Test foreign-data wrapper and server management. -- -- directory paths and dlsuffix are passed to us in environment variables \getenv libdir PG_LIBDIR \getenv dlsuffix PG_DLSUFFIX \set regresslib :libdir '/regress' :dlsuffix CREATE FUNCTION test_fdw_handler() RETURNS fdw_handler AS :'regresslib', 'test_fdw_handler' LANGUAGE C; -- Clean up in case a prior regression run failed -- Suppress NOTICE messages when roles don't exist SET client_min_messages TO 'warning'; DROP ROLE IF EXISTS regress_foreign_data_user, regress_test_role, regress_test_role2, r RESET client_min_messages; CREATE ROLE regress_foreign_data_user LOGIN SUPERUSER; SET SESSION AUTHORIZATION 'regress_foreign_data_user'; CREATE ROLE regress_test_role; CREATE ROLE regress_test_role2; CREATE ROLE regress_test_role_super SUPERUSER; CREATE ROLE regress_test_indirect; CREATE ROLE regress_unprivileged_role; CREATE FOREIGN DATA WRAPPER dummy; COMMENT ON FOREIGN DATA WRAPPER dummy IS 'useless'; CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator; -- At this point we should have 2 built-in wrappers and no servers. SELECT fdwname, fdwhandler::regproc, fdwvalidator::regproc, fdwoptions FROM pg_foreign fdwname | fdwhandler | fdwvalidator | fdwoptions ------------+------------+--------------------------+------------ dummy | - | - | postgresql | - | postgresql_fdw_validator | (2 rows) SELECT srvname, srvoptions FROM pg_foreign_server; srvname | srvoptions ---------+------------ (0 rows) SELECT * FROM pg_user_mapping; oid | umuser | umserver | umoptions -----+--------+----------+----------- (0 rows) -- CREATE FOREIGN DATA WRAPPER CREATE FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR ERROR: function bar(text[], oid) does not exist CREATE FOREIGN DATA WRAPPER foo; \dew List of foreign-data wrappers Name | Owner | Handler | Validator ------------+---------------------------+---------+-------------------------- dummy | regress_foreign_data_user | - | - foo | regress_foreign_data_user | - | - postgresql | regress_foreign_data_user | - | postgresql_fdw_validator (3 rows) CREATE FOREIGN DATA WRAPPER foo; -- duplicate ERROR: foreign-data wrapper "foo" already exists DROP FOREIGN DATA WRAPPER foo; CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1'); \dew+ List of foreign-data wrappers Name | Owner | Handler | Validator | Access privileges | FDW options | Description ------------+---------------------------+---------+--------------------------+-- dummy | regress_foreign_data_user | - | - | | | useless foo | regress_foreign_data_user | - | - | | (testing '1') | postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) DROP FOREIGN DATA WRAPPER foo; CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', testing '2'); -- ERROR ERROR: option "testing" provided more than once CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2'); \dew+ List of foreign-data wrappers Name | Owner | Handler | Validator | Access privileges | FDW options | Description ------------+---------------------------+---------+--------------------------+-- dummy | regress_foreign_data_user | - | - | | | useless foo | regress_foreign_data_user | - | - | | (testing '1', another '2') | postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) DROP FOREIGN DATA WRAPPER foo; SET ROLE regress_test_role; CREATE FOREIGN DATA WRAPPER foo; -- ERROR ERROR: permission denied to create foreign-data wrapper "foo" HINT: Must be superuser to create a foreign-data wrapper. RESET ROLE; CREATE FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator; \dew+ List of foreign-data wrappers Name | Owner | Handler | Validator | Access privileges | FDW options | Description ------------+---------------------------+---------+--------------------------+-- dummy | regress_foreign_data_user | - | - | | | useless foo | regress_foreign_data_user | - | postgresql_fdw_validator | | | postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) -- HANDLER related checks CREATE FUNCTION invalid_fdw_handler() RETURNS int LANGUAGE SQL AS 'SELECT 1;'; CREATE FOREIGN DATA WRAPPER test_fdw HANDLER invalid_fdw_handler; -- ERROR ERROR: function invalid_fdw_handler must return type fdw_handler CREATE FOREIGN DATA WRAPPER test_fdw HANDLER test_fdw_handler HANDLER invalid_fdw_handle ERROR: conflicting or redundant options LINE 1: ...GN DATA WRAPPER test_fdw HANDLER test_fdw_handler HANDLER in... ^ CREATE FOREIGN DATA WRAPPER test_fdw HANDLER test_fdw_handler; -- should preserve dependency on test_fdw_handler ALTER FOREIGN DATA WRAPPER test_fdw VALIDATOR postgresql_fdw_validator; WARNING: changing the foreign-data wrapper validator can cause the options for dependent obj DROP FUNCTION test_fdw_handler(); -- ERROR ERROR: cannot drop function test_fdw_handler() because other objects depend on it DETAIL: foreign-data wrapper test_fdw depends on function test_fdw_handler() HINT: Use DROP ... CASCADE to drop the dependent objects too. DROP FOREIGN DATA WRAPPER test_fdw; -- ALTER FOREIGN DATA WRAPPER ALTER FOREIGN DATA WRAPPER foo OPTIONS (nonexistent 'fdw'); -- ERROR ERROR: invalid option "nonexistent" HINT: There are no valid options in this context. ALTER FOREIGN DATA WRAPPER foo; -- ERROR ERROR: syntax error at or near ";" LINE 1: ALTER FOREIGN DATA WRAPPER foo; ^ ALTER FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR ERROR: function bar(text[], oid) does not exist ALTER FOREIGN DATA WRAPPER foo NO VALIDATOR; \dew+ List of foreign-data wrappers Name | Owner | Handler | Validator | Access privileges | FDW options | Description ------------+---------------------------+---------+--------------------------+-- dummy | regress_foreign_data_user | - | - | | | useless foo | regress_foreign_data_user | - | - | | | postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '1', b '2'); ALTER FOREIGN DATA WRAPPER foo OPTIONS (SET c '4'); -- ERROR ERROR: option "c" not found ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP c); -- ERROR ERROR: option "c" not found ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x); \dew+ List of foreign-data wrappers Name | Owner | Handler | Validator | Access privileges | FDW options | Description ------------+---------------------------+---------+--------------------------+-- dummy | regress_foreign_data_user | - | - | | | useless foo | regress_foreign_data_user | - | - | | (a '1', b '2') | postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4'); \dew+ List of foreign-data wrappers Name | Owner | Handler | Validator | Access privileges | FDW options | Description ------------+---------------------------+---------+--------------------------+-- dummy | regress_foreign_data_user | - | - | | | useless foo | regress_foreign_data_user | - | - | | (b '3', c '4') | postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2'); ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR ERROR: option "b" provided more than once \dew+ List of foreign-data wrappers Name | Owner | Handler | Validator | Access privileges | FDW options | Description ------------+---------------------------+---------+--------------------------+-- dummy | regress_foreign_data_user | - | - | | | useless foo | regress_foreign_data_user | - | - | | (b '3', c '4', a '2') | postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) SET ROLE regress_test_role; ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5'); -- ERROR ERROR: permission denied to alter foreign-data wrapper "foo" HINT: Must be superuser to alter a foreign-data wrapper. SET ROLE regress_test_role_super; ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5'); \dew+ List of foreign-data wrappers Name | Owner | Handler | Validator | Access privileges | FDW options | Description ------------+---------------------------+---------+--------------------------+-- dummy | regress_foreign_data_user | - | - | | | useless foo | regress_foreign_data_user | - | - | | (b '3', c '4', a '2', d '5') | postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR ERROR: permission denied to change owner of foreign-data wrapper "foo" HINT: The owner of a foreign-data wrapper must be a superuser. ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role_super; ALTER ROLE regress_test_role_super NOSUPERUSER; SET ROLE regress_test_role_super; ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD e '6'); -- ERROR ERROR: permission denied to alter foreign-data wrapper "foo" HINT: Must be superuser to alter a foreign-data wrapper. RESET ROLE; \dew+ List of foreign-data wrappers Name | Owner | Handler | Validator | Access privileges | FDW options | Description ------------+---------------------------+---------+--------------------------+-- dummy | regress_foreign_data_user | - | - | | | useless foo | regress_test_role_super | - | - | | (b '3', c '4', a '2', d '5') | postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) ALTER FOREIGN DATA WRAPPER foo RENAME TO foo1; \dew+ List of foreign-data wrappers Name | Owner | Handler | Validator | Access privileges | FDW options | Description ------------+---------------------------+---------+--------------------------+-- dummy | regress_foreign_data_user | - | - | | | useless foo1 | regress_test_role_super | - | - | | (b '3', c '4', a '2', d '5') | postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) ALTER FOREIGN DATA WRAPPER foo1 RENAME TO foo; -- HANDLER related checks ALTER FOREIGN DATA WRAPPER foo HANDLER invalid_fdw_handler; -- ERROR ERROR: function invalid_fdw_handler must return type fdw_handler ALTER FOREIGN DATA WRAPPER foo HANDLER test_fdw_handler HANDLER anything; -- ERROR ERROR: conflicting or redundant options LINE 1: ...FOREIGN DATA WRAPPER foo HANDLER test_fdw_handler HANDLER an... ^ ALTER FOREIGN DATA WRAPPER foo HANDLER test_fdw_handler; WARNING: changing the foreign-data wrapper handler can change behavior of existing foreign t DROP FUNCTION invalid_fdw_handler(); -- DROP FOREIGN DATA WRAPPER DROP FOREIGN DATA WRAPPER nonexistent; -- ERROR ERROR: foreign-data wrapper "nonexistent" does not exist DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent; NOTICE: foreign-data wrapper "nonexistent" does not exist, skipping \dew+ List of foreign-data wrappers Name | Owner | Handler | Validator | Access privileges | FDW options | Description ------------+---------------------------+------------------+-------------------- dummy | regress_foreign_data_user | - | - | | | useless foo | regress_test_role_super | test_fdw_handler | - | | (b '3', c '4', a '2', d '5') | postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) DROP ROLE regress_test_role_super; -- ERROR ERROR: role "regress_test_role_super" cannot be dropped because some objects depend on it DETAIL: owner of foreign-data wrapper foo SET ROLE regress_test_role_super; DROP FOREIGN DATA WRAPPER foo; RESET ROLE; DROP ROLE regress_test_role_super; \dew+ List of foreign-data wrappers Name | Owner | Handler | Validator | Access privileges | FDW options | Description ------------+---------------------------+---------+--------------------------+-- dummy | regress_foreign_data_user | - | - | | | useless postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | | (2 rows) CREATE FOREIGN DATA WRAPPER foo; CREATE SERVER s1 FOREIGN DATA WRAPPER foo; COMMENT ON SERVER s1 IS 'foreign server'; CREATE USER MAPPING FOR current_user SERVER s1; CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR ERROR: user mapping for "regress_foreign_data_user" already exists for server "s1" CREATE USER MAPPING IF NOT EXISTS FOR current_user SERVER s1; -- NOTICE NOTICE: user mapping for "regress_foreign_data_user" already exists for server "s1", skipp \dew+ List of foreign-data wrappers Name | Owner | Handler | Validator | Access privileges | FDW options | Description ------------+---------------------------+---------+--------------------------+-- dummy | regress_foreign_data_user | - | - | | | useless foo | regress_foreign_data_user | - | - | | | postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | | (3 rows) \des+ List of foreign servers Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Descript ------+---------------------------+----------------------+-------------------+-- s1 | regress_foreign_data_user | foo | | | | | foreign server (1 row) \deu+ List of user mappings Server | User name | FDW options --------+---------------------------+------------- s1 | regress_foreign_data_user | (1 row) DROP FOREIGN DATA WRAPPER foo; -- ERROR ERROR: cannot drop foreign-data wrapper foo because other objects depend on it DETAIL: server s1 depends on foreign-data wrapper foo user mapping for regress_foreign_data_user on server s1 depends on server s1 HINT: Use DROP ... CASCADE to drop the dependent objects too. SET ROLE regress_test_role; DROP FOREIGN DATA WRAPPER foo CASCADE; -- ERROR ERROR: must be owner of foreign-data wrapper foo RESET ROLE; DROP FOREIGN DATA WRAPPER foo CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to server s1 drop cascades to user mapping for regress_foreign_data_user on server s1 \dew+ List of foreign-data wrappers Name | Owner | Handler | Validator | Access privileges | FDW options | Description ------------+---------------------------+---------+--------------------------+-- dummy | regress_foreign_data_user | - | - | | | useless postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | | (2 rows) \des+ List of foreign servers Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Descript ------+-------+----------------------+-------------------+------+---------+----- (0 rows) \deu+ List of user mappings Server | User name | FDW options --------+-----------+------------- (0 rows) -- exercise CREATE SERVER CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR ERROR: foreign-data wrapper "foo" does not exist CREATE FOREIGN DATA WRAPPER foo OPTIONS ("test wrapper" 'true'); CREATE SERVER s1 FOREIGN DATA WRAPPER foo; CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR ERROR: server "s1" already exists CREATE SERVER IF NOT EXISTS s1 FOREIGN DATA WRAPPER foo; -- No ERROR, just NOTICE NOTICE: server "s1" already exists, skipping CREATE SERVER s2 FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b'); CREATE SERVER s3 TYPE 'oracle' FOREIGN DATA WRAPPER foo; CREATE SERVER s4 TYPE 'oracle' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b'); CREATE SERVER s5 VERSION '15.0' FOREIGN DATA WRAPPER foo; CREATE SERVER s6 VERSION '16.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b'); CREATE SERVER s7 TYPE 'oracle' VERSION '17.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbna CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (foo '1'); -- ERROR ERROR: invalid option "foo" CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db'); \des+ List of foreign servers Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Descript ------+---------------------------+----------------------+-------------------+-- s1 | regress_foreign_data_user | foo | | | | | s2 | regress_foreign_data_user | foo | | | | (host 'a', dbname 'b') | s3 | regress_foreign_data_user | foo | | oracle | | | s4 | regress_foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') | s5 | regress_foreign_data_user | foo | | | 15.0 | | s6 | regress_foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') | s7 | regress_foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') | s8 | regress_foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') | (8 rows) SET ROLE regress_test_role; CREATE SERVER t1 FOREIGN DATA WRAPPER foo; -- ERROR: no usage on FDW ERROR: permission denied for foreign-data wrapper foo RESET ROLE; GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; SET ROLE regress_test_role; CREATE SERVER t1 FOREIGN DATA WRAPPER foo; RESET ROLE; \des+ List of foreign servers Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Descript ------+---------------------------+----------------------+-------------------+-- s1 | regress_foreign_data_user | foo | | | | | s2 | regress_foreign_data_user | foo | | | | (host 'a', dbname 'b') | s3 | regress_foreign_data_user | foo | | oracle | | | s4 | regress_foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') | s5 | regress_foreign_data_user | foo | | | 15.0 | | s6 | regress_foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') | s7 | regress_foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') | s8 | regress_foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') | t1 | regress_test_role | foo | | | | | (9 rows) REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role; GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect; SET ROLE regress_test_role; CREATE SERVER t2 FOREIGN DATA WRAPPER foo; -- ERROR ERROR: permission denied for foreign-data wrapper foo RESET ROLE; GRANT regress_test_indirect TO regress_test_role; SET ROLE regress_test_role; CREATE SERVER t2 FOREIGN DATA WRAPPER foo; \des+ List of foreign servers Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Descript ------+---------------------------+----------------------+-------------------+-- s1 | regress_foreign_data_user | foo | | | | | s2 | regress_foreign_data_user | foo | | | | (host 'a', dbname 'b') | s3 | regress_foreign_data_user | foo | | oracle | | | s4 | regress_foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') | s5 | regress_foreign_data_user | foo | | | 15.0 | | s6 | regress_foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') | s7 | regress_foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') | s8 | regress_foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') | t1 | regress_test_role | foo | | | | | t2 | regress_test_role | foo | | | | | (10 rows) RESET ROLE; REVOKE regress_test_indirect FROM regress_test_role; -- ALTER SERVER ALTER SERVER s0; -- ERROR ERROR: syntax error at or near ";" LINE 1: ALTER SERVER s0; ^ ALTER SERVER s0 OPTIONS (a '1'); -- ERROR ERROR: server "s0" does not exist ALTER SERVER s1 VERSION '1.0' OPTIONS (servername 's1'); ALTER SERVER s2 VERSION '1.1'; ALTER SERVER s3 OPTIONS ("tns name" 'orcl', port '1521'); GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role; GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION; \des+ List of foreign servers Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Descript ------+---------------------------+----------------------+---------------------- s1 | regress_foreign_data_user | foo | regress_foreign_data_user=U/regress_foreign_dat | | | regress_test_role=U/regress_foreign_data_user | | | | s2 | regress_foreign_data_user | foo | | | 1.1 | (host 'a', dbname 'b') | s3 | regress_foreign_data_user | foo | | oracle | | ("tns name" 'orcl', port '1521') | s4 | regress_foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') | s5 | regress_foreign_data_user | foo | | | 15.0 | | s6 | regress_foreign_data_user | foo | regress_foreign_data_user=U/regress_foreign_dat | | | regress_test_role2=U*/regress_foreign_data_user | | | | s7 | regress_foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') | s8 | regress_foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') | t1 | regress_test_role | foo | | | | | t2 | regress_test_role | foo | | | | | (10 rows) SET ROLE regress_test_role; ALTER SERVER s1 VERSION '1.1'; -- ERROR ERROR: must be owner of foreign server s1 ALTER SERVER s1 OWNER TO regress_test_role; -- ERROR ERROR: must be owner of foreign server s1 RESET ROLE; ALTER SERVER s1 OWNER TO regress_test_role; GRANT regress_test_role2 TO regress_test_role; SET ROLE regress_test_role; ALTER SERVER s1 VERSION '1.1'; ALTER SERVER s1 OWNER TO regress_test_role2; -- ERROR ERROR: permission denied for foreign-data wrapper foo RESET ROLE; ALTER SERVER s8 OPTIONS (foo '1'); -- ERROR option validation ERROR: invalid option "foo" ALTER SERVER s8 OPTIONS (connect_timeout '30', SET dbname 'db1', DROP host); SET ROLE regress_test_role; ALTER SERVER s1 OWNER TO regress_test_indirect; -- ERROR ERROR: must be able to SET ROLE "regress_test_indirect" RESET ROLE; GRANT regress_test_indirect TO regress_test_role; SET ROLE regress_test_role; ALTER SERVER s1 OWNER TO regress_test_indirect; RESET ROLE; GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect; SET ROLE regress_test_role; ALTER SERVER s1 OWNER TO regress_test_indirect; RESET ROLE; DROP ROLE regress_test_indirect; -- ERROR ERROR: role "regress_test_indirect" cannot be dropped because some objects depend on it DETAIL: privileges for foreign-data wrapper foo owner of server s1 \des+ List of foreign servers Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Descript ------+---------------------------+----------------------+---------------------- s1 | regress_test_indirect | foo | regress_test_indirect=U/regress_test_indirect | | 1.1 | s2 | regress_foreign_data_user | foo | | | 1.1 | (host 'a', dbname 'b') | s3 | regress_foreign_data_user | foo | | oracle | | ("tns name" 'orcl', port '1521') | s4 | regress_foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') | s5 | regress_foreign_data_user | foo | | | 15.0 | | s6 | regress_foreign_data_user | foo | regress_foreign_data_user=U/regress_foreign_dat | | | regress_test_role2=U*/regress_foreign_data_user | | | | s7 | regress_foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') | s8 | regress_foreign_data_user | postgresql | | | | (dbname 'db1', connect_timeout '30') | t1 | regress_test_role | foo | | | | | t2 | regress_test_role | foo | | | | | (10 rows) ALTER SERVER s8 RENAME to s8new; \des+ List of foreign servers Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Descript -------+---------------------------+----------------------+--------------------- s1 | regress_test_indirect | foo | regress_test_indirect=U/regress_test_indirect | | 1.1 | s2 | regress_foreign_data_user | foo | | | 1.1 | (host 'a', dbname 'b') | s3 | regress_foreign_data_user | foo | | oracle | | ("tns name" 'orcl', port '1521') | s4 | regress_foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') | s5 | regress_foreign_data_user | foo | | | 15.0 | | s6 | regress_foreign_data_user | foo | regress_foreign_data_user=U/regress_foreign_dat | | | regress_test_role2=U*/regress_foreign_data_user | | | | s7 | regress_foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') | s8new | regress_foreign_data_user | postgresql | | | | (dbname 'db1', connect_timeout '30') | t1 | regress_test_role | foo | | | | | t2 | regress_test_role | foo | | | | | (10 rows) ALTER SERVER s8new RENAME to s8; -- DROP SERVER DROP SERVER nonexistent; -- ERROR ERROR: server "nonexistent" does not exist DROP SERVER IF EXISTS nonexistent; NOTICE: server "nonexistent" does not exist, skipping \des List of foreign servers Name | Owner | Foreign-data wrapper ------+---------------------------+---------------------- s1 | regress_test_indirect | foo s2 | regress_foreign_data_user | foo s3 | regress_foreign_data_user | foo s4 | regress_foreign_data_user | foo s5 | regress_foreign_data_user | foo s6 | regress_foreign_data_user | foo s7 | regress_foreign_data_user | foo s8 | regress_foreign_data_user | postgresql t1 | regress_test_role | foo t2 | regress_test_role | foo (10 rows) SET ROLE regress_test_role; DROP SERVER s2; -- ERROR ERROR: must be owner of foreign server s2 DROP SERVER s1; RESET ROLE; \des List of foreign servers Name | Owner | Foreign-data wrapper ------+---------------------------+---------------------- s2 | regress_foreign_data_user | foo s3 | regress_foreign_data_user | foo s4 | regress_foreign_data_user | foo s5 | regress_foreign_data_user | foo s6 | regress_foreign_data_user | foo s7 | regress_foreign_data_user | foo s8 | regress_foreign_data_user | postgresql t1 | regress_test_role | foo t2 | regress_test_role | foo (9 rows) ALTER SERVER s2 OWNER TO regress_test_role; SET ROLE regress_test_role; DROP SERVER s2; RESET ROLE; \des List of foreign servers Name | Owner | Foreign-data wrapper ------+---------------------------+---------------------- s3 | regress_foreign_data_user | foo s4 | regress_foreign_data_user | foo s5 | regress_foreign_data_user | foo s6 | regress_foreign_data_user | foo s7 | regress_foreign_data_user | foo s8 | regress_foreign_data_user | postgresql t1 | regress_test_role | foo t2 | regress_test_role | foo (8 rows) CREATE USER MAPPING FOR current_user SERVER s3; \deu List of user mappings Server | User name --------+--------------------------- s3 | regress_foreign_data_user (1 row) DROP SERVER s3; -- ERROR ERROR: cannot drop server s3 because other objects depend on it DETAIL: user mapping for regress_foreign_data_user on server s3 depends on server s3 HINT: Use DROP ... CASCADE to drop the dependent objects too. DROP SERVER s3 CASCADE; NOTICE: drop cascades to user mapping for regress_foreign_data_user on server s3 \des List of foreign servers Name | Owner | Foreign-data wrapper ------+---------------------------+---------------------- s4 | regress_foreign_data_user | foo s5 | regress_foreign_data_user | foo s6 | regress_foreign_data_user | foo s7 | regress_foreign_data_user | foo s8 | regress_foreign_data_user | postgresql t1 | regress_test_role | foo t2 | regress_test_role | foo (7 rows) \deu List of user mappings Server | User name --------+----------- (0 rows) -- CREATE USER MAPPING CREATE USER MAPPING FOR regress_test_missing_role SERVER s1; -- ERROR ERROR: role "regress_test_missing_role" does not exist CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR ERROR: server "s1" does not exist CREATE USER MAPPING FOR current_user SERVER s4; CREATE USER MAPPING FOR user SERVER s4; -- ERROR duplicate ERROR: user mapping for "regress_foreign_data_user" already exists for server "s4" CREATE USER MAPPING FOR public SERVER s4 OPTIONS ("this mapping" 'is public'); CREATE USER MAPPING FOR user SERVER s8 OPTIONS (username 'test', password 'secret'); -- ERROR ERROR: invalid option "username" HINT: Perhaps you meant the option "user". CREATE USER MAPPING FOR user SERVER s8 OPTIONS (user 'test', password 'secret'); ALTER SERVER s5 OWNER TO regress_test_role; ALTER SERVER s6 OWNER TO regress_test_indirect; SET ROLE regress_test_role; CREATE USER MAPPING FOR current_user SERVER s5; CREATE USER MAPPING FOR current_user SERVER s6 OPTIONS (username 'test'); CREATE USER MAPPING FOR current_user SERVER s7; -- ERROR ERROR: permission denied for foreign server s7 CREATE USER MAPPING FOR public SERVER s8; -- ERROR ERROR: must be owner of foreign server s8 RESET ROLE; ALTER SERVER t1 OWNER TO regress_test_indirect; SET ROLE regress_test_role; CREATE USER MAPPING FOR current_user SERVER t1 OPTIONS (username 'bob', password 'boo'); CREATE USER MAPPING FOR public SERVER t1; RESET ROLE; \deu List of user mappings Server | User name --------+--------------------------- s4 | public s4 | regress_foreign_data_user s5 | regress_test_role s6 | regress_test_role s8 | regress_foreign_data_user t1 | public t1 | regress_test_role (7 rows) -- ALTER USER MAPPING ALTER USER MAPPING FOR regress_test_missing_role SERVER s4 OPTIONS (gotcha 'true'); -- ERRO ERROR: role "regress_test_missing_role" does not exist ALTER USER MAPPING FOR user SERVER ss4 OPTIONS (gotcha 'true'); -- ERROR ERROR: server "ss4" does not exist ALTER USER MAPPING FOR public SERVER s5 OPTIONS (gotcha 'true'); -- ERROR ERROR: user mapping for "public" does not exist for server "s5" ALTER USER MAPPING FOR current_user SERVER s8 OPTIONS (username 'test'); -- ERROR ERROR: invalid option "username" HINT: Perhaps you meant the option "user". ALTER USER MAPPING FOR current_user SERVER s8 OPTIONS (DROP user, SET password 'public'); SET ROLE regress_test_role; ALTER USER MAPPING FOR current_user SERVER s5 OPTIONS (ADD modified '1'); ALTER USER MAPPING FOR public SERVER s4 OPTIONS (ADD modified '1'); -- ERROR ERROR: must be owner of foreign server s4 ALTER USER MAPPING FOR public SERVER t1 OPTIONS (ADD modified '1'); RESET ROLE; \deu+ List of user mappings Server | User name | FDW options --------+---------------------------+---------------------------------- s4 | public | ("this mapping" 'is public') s4 | regress_foreign_data_user | s5 | regress_test_role | (modified '1') s6 | regress_test_role | (username 'test') s8 | regress_foreign_data_user | (password 'public') t1 | public | (modified '1') t1 | regress_test_role | (username 'bob', password 'boo') (7 rows) -- DROP USER MAPPING DROP USER MAPPING FOR regress_test_missing_role SERVER s4; -- ERROR ERROR: role "regress_test_missing_role" does not exist DROP USER MAPPING FOR user SERVER ss4; ERROR: server "ss4" does not exist DROP USER MAPPING FOR public SERVER s7; -- ERROR ERROR: user mapping for "public" does not exist for server "s7" DROP USER MAPPING IF EXISTS FOR regress_test_missing_role SERVER s4; NOTICE: role "regress_test_missing_role" does not exist, skipping DROP USER MAPPING IF EXISTS FOR user SERVER ss4; NOTICE: server "ss4" does not exist, skipping DROP USER MAPPING IF EXISTS FOR public SERVER s7; NOTICE: user mapping for "public" does not exist for server "s7", skipping CREATE USER MAPPING FOR public SERVER s8; SET ROLE regress_test_role; DROP USER MAPPING FOR public SERVER s8; -- ERROR ERROR: must be owner of foreign server s8 RESET ROLE; DROP SERVER s7; \deu List of user mappings Server | User name --------+--------------------------- s4 | public s4 | regress_foreign_data_user s5 | regress_test_role s6 | regress_test_role s8 | public s8 | regress_foreign_data_user t1 | public t1 | regress_test_role (8 rows) -- CREATE FOREIGN TABLE CREATE SCHEMA foreign_schema; CREATE SERVER s0 FOREIGN DATA WRAPPER dummy; CREATE FOREIGN TABLE ft1 (); -- ERROR ERROR: syntax error at or near ";" LINE 1: CREATE FOREIGN TABLE ft1 (); ^ CREATE FOREIGN TABLE ft1 () SERVER no_server; -- ERROR ERROR: server "no_server" does not exist CREATE FOREIGN TABLE ft1 ( c1 integer OPTIONS ("param 1" 'val1') PRIMARY KEY, c2 text OPTIONS (param2 'val2', param3 'val3'), c3 date ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR ERROR: primary key constraints are not supported on foreign tables LINE 2: c1 integer OPTIONS ("param 1" 'val1') PRIMARY KEY, ^ CREATE TABLE ref_table (id integer PRIMARY KEY); CREATE FOREIGN TABLE ft1 ( c1 integer OPTIONS ("param 1" 'val1') REFERENCES ref_table (id), c2 text OPTIONS (param2 'val2', param3 'val3'), c3 date ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR ERROR: foreign key constraints are not supported on foreign tables LINE 2: c1 integer OPTIONS ("param 1" 'val1') REFERENCES ref_table ... ^ DROP TABLE ref_table; CREATE FOREIGN TABLE ft1 ( c1 integer OPTIONS ("param 1" 'val1') NOT NULL, c2 text OPTIONS (param2 'val2', param3 'val3'), c3 date, UNIQUE (c3) ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR ERROR: unique constraints are not supported on foreign tables LINE 5: UNIQUE (c3) ^ CREATE FOREIGN TABLE ft1 ( c1 integer OPTIONS ("param 1" 'val1') NOT NULL, c2 text OPTIONS (param2 'val2', param3 'val3') CHECK (c2 <> ''), c3 date, CHECK (c3 BETWEEN '1994-01-01'::date AND '1994-01-31'::date) ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); COMMENT ON FOREIGN TABLE ft1 IS 'ft1'; COMMENT ON COLUMN ft1.c1 IS 'ft1.c1'; \d+ ft1 Foreign table "public.ft1" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+---------------------------- c1 | integer | | not null | | ("param 1" 'val1') | plain | | ft1.c1 c2 | text | | | | (param2 'val2', param3 'val3') | extended | | c3 | date | | | | | plain | | Check constraints: "ft1_c2_check" CHECK (c2 <> ''::text) "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date) Not-null constraints: "ft1_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') \det+ List of foreign tables Schema | Table | Server | FDW options | Description --------+-------+--------+-------------------------------------------------+---- public | ft1 | s0 | (delimiter ',', quote '"', "be quoted" 'value') | ft1 (1 row) CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR ERROR: cannot create index on relation "ft1" DETAIL: This operation is not supported for foreign tables. SELECT * FROM ft1; -- ERROR ERROR: foreign-data wrapper "dummy" has no handler EXPLAIN SELECT * FROM ft1; -- ERROR ERROR: foreign-data wrapper "dummy" has no handler CREATE TABLE lt1 (a INT) PARTITION BY RANGE (a); CREATE FOREIGN TABLE ft_part1 PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) SERVER s0; CREATE INDEX ON lt1 (a); -- skips partition CREATE UNIQUE INDEX ON lt1 (a); -- ERROR ERROR: cannot create unique index on partitioned table "lt1" DETAIL: Table "lt1" contains partitions that are foreign tables. ALTER TABLE lt1 ADD PRIMARY KEY (a); -- ERROR ERROR: cannot create unique index on partitioned table "lt1" DETAIL: Table "lt1" contains partitions that are foreign tables. DROP TABLE lt1; CREATE TABLE lt1 (a INT) PARTITION BY RANGE (a); CREATE INDEX ON lt1 (a); CREATE FOREIGN TABLE ft_part1 PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) SERVER s0; CREATE FOREIGN TABLE ft_part2 (a INT) SERVER s0; ALTER TABLE lt1 ATTACH PARTITION ft_part2 FOR VALUES FROM (1000) TO (2000); DROP FOREIGN TABLE ft_part1, ft_part2; CREATE UNIQUE INDEX ON lt1 (a); ALTER TABLE lt1 ADD PRIMARY KEY (a); CREATE FOREIGN TABLE ft_part1 PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) SERVER s0; -- ERROR ERROR: cannot create foreign partition of partitioned table "lt1" DETAIL: Table "lt1" contains indexes that are unique. CREATE FOREIGN TABLE ft_part2 (a INT NOT NULL) SERVER s0; ALTER TABLE lt1 ATTACH PARTITION ft_part2 FOR VALUES FROM (1000) TO (2000); -- ERROR ERROR: cannot attach foreign table "ft_part2" as partition of partitioned table "lt1" DETAIL: Partitioned table "lt1" contains unique indexes. DROP TABLE lt1; DROP FOREIGN TABLE ft_part2; CREATE TABLE lt1 (a INT) PARTITION BY RANGE (a); CREATE INDEX ON lt1 (a); CREATE TABLE lt1_part1 PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) PARTITION BY RANGE (a); CREATE FOREIGN TABLE ft_part_1_1 PARTITION OF lt1_part1 FOR VALUES FROM (0) TO (100) SERVER s0; CREATE FOREIGN TABLE ft_part_1_2 (a INT) SERVER s0; ALTER TABLE lt1_part1 ATTACH PARTITION ft_part_1_2 FOR VALUES FROM (100) TO (200); CREATE UNIQUE INDEX ON lt1 (a); ERROR: cannot create unique index on partitioned table "lt1" DETAIL: Table "lt1" contains partitions that are foreign tables. ALTER TABLE lt1 ADD PRIMARY KEY (a); ERROR: cannot create unique index on partitioned table "lt1_part1" DETAIL: Table "lt1_part1" contains partitions that are foreign tables. DROP FOREIGN TABLE ft_part_1_1, ft_part_1_2; CREATE UNIQUE INDEX ON lt1 (a); ALTER TABLE lt1 ADD PRIMARY KEY (a); CREATE FOREIGN TABLE ft_part_1_1 PARTITION OF lt1_part1 FOR VALUES FROM (0) TO (100) SERVER s0; ERROR: cannot create foreign partition of partitioned table "lt1_part1" DETAIL: Table "lt1_part1" contains indexes that are unique. CREATE FOREIGN TABLE ft_part_1_2 (a INT NOT NULL) SERVER s0; ALTER TABLE lt1_part1 ATTACH PARTITION ft_part_1_2 FOR VALUES FROM (100) TO (200); ERROR: cannot attach foreign table "ft_part_1_2" as partition of partitioned table "lt1_par DETAIL: Partitioned table "lt1_part1" contains unique indexes. DROP TABLE lt1; DROP FOREIGN TABLE ft_part_1_2; -- ALTER FOREIGN TABLE COMMENT ON FOREIGN TABLE ft1 IS 'foreign table'; COMMENT ON FOREIGN TABLE ft1 IS NULL; COMMENT ON COLUMN ft1.c1 IS 'foreign column'; COMMENT ON COLUMN ft1.c1 IS NULL; ALTER FOREIGN TABLE ft1 ADD COLUMN c4 integer; ALTER FOREIGN TABLE ft1 ADD COLUMN c5 integer DEFAULT 0; ALTER FOREIGN TABLE ft1 ADD COLUMN c6 integer; ALTER FOREIGN TABLE ft1 ADD COLUMN IF NOT EXISTS c6 integer; NOTICE: column "c6" of relation "ft1" already exists, skipping ALTER FOREIGN TABLE ft1 ADD COLUMN c7 integer NOT NULL; ALTER FOREIGN TABLE ft1 ADD COLUMN c8 integer; ALTER FOREIGN TABLE ft1 ADD COLUMN c9 integer; ALTER FOREIGN TABLE ft1 ADD COLUMN c10 integer OPTIONS (p1 'v1'); ALTER FOREIGN TABLE ft1 ADD c11 integer; ALTER FOREIGN TABLE ft1 ALTER COLUMN c4 SET DEFAULT 0; ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 DROP DEFAULT; ALTER FOREIGN TABLE ft1 ALTER COLUMN c6 SET NOT NULL; ALTER FOREIGN TABLE ft1 ALTER COLUMN c7 DROP NOT NULL; ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10) USING '0'; -- ERROR ERROR: "ft1" is not a table ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10); ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE text; ALTER FOREIGN TABLE ft1 ALTER COLUMN xmin OPTIONS (ADD p1 'v1'); -- ERROR ERROR: cannot alter system column "xmin" ALTER FOREIGN TABLE ft1 ALTER COLUMN c7 OPTIONS (ADD p1 'v1', ADD p2 'v2'), ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2'); ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1); ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 SET STATISTICS 10000; ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 SET (n_distinct = 100); ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STATISTICS -1; ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STORAGE PLAIN; \d+ ft1 Foreign table "public.ft1" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+---------------------------- c1 | integer | | not null | | ("param 1" 'val1') | plain | 10000 | c2 | text | | | | (param2 'val2', param3 'val3') | extended | | c3 | date | | | | | plain | | c4 | integer | | | 0 | | plain | | c5 | integer | | | | | plain | | c6 | integer | | not null | | | plain | | c7 | integer | | | | (p1 'v1', p2 'v2') | plain | | c8 | text | | | | (p2 'V2') | plain | | c9 | integer | | | | | plain | | c10 | integer | | | | (p1 'v1') | plain | | c11 | integer | | | | | plain | | Check constraints: "ft1_c2_check" CHECK (c2 <> ''::text) "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date) Not-null constraints: "ft1_c1_not_null" NOT NULL "c1" "ft1_c6_not_null" NOT NULL "c6" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') -- can't change the column type if it's used elsewhere CREATE TABLE use_ft1_column_type (x ft1); ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR ERROR: cannot alter foreign table "ft1" because column "use_ft1_column_type.x" uses its row DROP TABLE use_ft1_column_type; ALTER FOREIGN TABLE ft1 ADD PRIMARY KEY (c7); -- ERROR ERROR: primary key constraints are not supported on foreign tables LINE 1: ALTER FOREIGN TABLE ft1 ADD PRIMARY KEY (c7); ^ ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NOT VALID; ALTER FOREIGN TABLE ft1 ALTER CONSTRAINT ft1_c9_check DEFERRABLE; -- ERROR ERROR: ALTER action ALTER CONSTRAINT cannot be performed on relation "ft1" DETAIL: This operation is not supported for foreign tables. ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c9_check; ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR ERROR: constraint "no_const" of relation "ft1" does not exist ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const; NOTICE: constraint "no_const" of relation "ft1" does not exist, skipping ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role; ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@'); ALTER FOREIGN TABLE ft1 DROP COLUMN no_column; -- ERROR ERROR: column "no_column" of relation "ft1" does not exist ALTER FOREIGN TABLE ft1 DROP COLUMN IF EXISTS no_column; NOTICE: column "no_column" of relation "ft1" does not exist, skipping ALTER FOREIGN TABLE ft1 DROP COLUMN c9; ALTER FOREIGN TABLE ft1 DROP c11; ALTER FOREIGN TABLE ft1 ADD COLUMN c11 serial; ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema; ALTER FOREIGN TABLE ft1 SET TABLESPACE ts; -- ERROR ERROR: relation "ft1" does not exist ALTER SEQUENCE foreign_schema.ft1_c11_seq SET SCHEMA public; -- ERROR ERROR: cannot move an owned sequence into another schema DETAIL: Sequence "ft1_c11_seq" is linked to table "ft1". ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1; ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1; \d foreign_schema.foreign_table_1 Foreign table "foreign_schema.foreign_table_1" Column | Type | Collation | Nullable | Default | FDW options ------------------+---------+-----------+----------+---------------------------- foreign_column_1 | integer | | not null | | ("param 1" 'val1') c2 | text | | | | (param2 'val2', param3 'val3') c3 | date | | | | c4 | integer | | | 0 | c5 | integer | | | | c6 | integer | | not null | | c7 | integer | | | | (p1 'v1', p2 'v2') c8 | text | | | | (p2 'V2') c10 | integer | | | | (p1 'v1') c11 | integer | | not null | nextval('foreign_schema.ft1_c11_seq'::regclass) | Check constraints: "ft1_c2_check" CHECK (c2 <> ''::text) "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date) Server: s0 FDW options: (quote '~', "be quoted" 'value', escape '@') -- alter noexisting table ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c4 integer; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c6 integer; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN IF NOT EXISTS c6 integer; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c7 integer NOT NULL; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c8 integer; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c9 integer; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c10 integer OPTIONS (p1 'v1'); NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD c11 integer; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c6 SET NOT NULL; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c7 DROP NOT NULL; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 TYPE char(10); NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 SET DATA TYPE text; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c7 OPTIONS (ADD p1 'v1', ADD p2 'v2 ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2'); NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1); NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP CONSTRAINT IF EXISTS no_const; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP CONSTRAINT ft1_c1_check; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 OWNER TO regress_test_role; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 OPTIONS (DROP delimiter, SET quote '~', ADD es NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP COLUMN no_column; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP COLUMN IF EXISTS no_column; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP COLUMN c9; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP c11; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 SET SCHEMA foreign_schema; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 RENAME c1 TO foreign_column_1; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 RENAME TO foreign_table_1; NOTICE: relation "doesnt_exist_ft1" does not exist, skipping -- Information schema SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2; foreign_data_wrapper_catalog | foreign_data_wrapper_name | authorization_identifier | ------------------------------+---------------------------+--------------------- regression | dummy | regress_foreign_data_user | | c regression | foo | regress_foreign_data_user | | c regression | postgresql | regress_foreign_data_user | | c (3 rows) SELECT * FROM information_schema.foreign_data_wrapper_options ORDER BY 1, 2, 3; foreign_data_wrapper_catalog | foreign_data_wrapper_name | option_name | option_value ------------------------------+---------------------------+--------------+------ regression | foo | test wrapper | true (1 row) SELECT * FROM information_schema.foreign_servers ORDER BY 1, 2; foreign_server_catalog | foreign_server_name | foreign_data_wrapper_catalog | foreign_ ------------------------+---------------------+------------------------------+-- regression | s0 | regression | dummy | | | regress_foreign_data_user regression | s4 | regression | foo | oracle | | regress_foreign_data_user regression | s5 | regression | foo | | 15.0 | regress_test_role regression | s6 | regression | foo | | 16.0 | regress_test_indirect regression | s8 | regression | postgresql | | | regress_foreign_data_user regression | t1 | regression | foo | | | regress_test_indirect regression | t2 | regression | foo | | | regress_test_role (7 rows) SELECT * FROM information_schema.foreign_server_options ORDER BY 1, 2, 3; foreign_server_catalog | foreign_server_name | option_name | option_value ------------------------+---------------------+-----------------+-------------- regression | s4 | dbname | b regression | s4 | host | a regression | s6 | dbname | b regression | s6 | host | a regression | s8 | connect_timeout | 30 regression | s8 | dbname | db1 (6 rows) SELECT * FROM information_schema.user_mappings ORDER BY lower(authorization_identifier authorization_identifier | foreign_server_catalog | foreign_server_name ---------------------------+------------------------+--------------------- PUBLIC | regression | s4 PUBLIC | regression | s8 PUBLIC | regression | t1 regress_foreign_data_user | regression | s4 regress_foreign_data_user | regression | s8 regress_test_role | regression | s5 regress_test_role | regression | s6 regress_test_role | regression | t1 (8 rows) SELECT * FROM information_schema.user_mapping_options ORDER BY lower(authorization_ide authorization_identifier | foreign_server_catalog | foreign_server_name | option_name | ---------------------------+------------------------+---------------------+----- PUBLIC | regression | s4 | this mapping | is public PUBLIC | regression | t1 | modified | 1 regress_foreign_data_user | regression | s8 | password | public regress_test_role | regression | s5 | modified | 1 regress_test_role | regression | s6 | username | test regress_test_role | regression | t1 | password | boo regress_test_role | regression | t1 | username | bob (7 rows) SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' AND o grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_t ---------------------------+---------------------------+----------------+------- regress_foreign_data_user | regress_foreign_data_user | regression | | foo | FOREIGN DATA W regress_foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPP regress_test_indirect | regress_test_indirect | regression | | s6 | FOREIGN SERVER | USAGE | Y regress_test_indirect | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES (4 rows) SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' AND grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_t ---------------------------+---------------------------+----------------+------- regress_foreign_data_user | regress_foreign_data_user | regression | | foo | FOREIGN DATA W regress_foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPP regress_test_indirect | regress_test_indirect | regression | | s6 | FOREIGN SERVER | USAGE | Y regress_test_indirect | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES (4 rows) SELECT * FROM information_schema.foreign_tables ORDER BY 1, 2, 3; foreign_table_catalog | foreign_table_schema | foreign_table_name | foreign_server_cat -----------------------+----------------------+--------------------+------------ regression | foreign_schema | foreign_table_1 | regression | s0 (1 row) SELECT * FROM information_schema.foreign_table_options ORDER BY 1, 2, 3, 4; foreign_table_catalog | foreign_table_schema | foreign_table_name | option_name | option -----------------------+----------------------+--------------------+------------ regression | foreign_schema | foreign_table_1 | be quoted | value regression | foreign_schema | foreign_table_1 | escape | @ regression | foreign_schema | foreign_table_1 | quote | ~ (3 rows) SET ROLE regress_test_role; SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4; authorization_identifier | foreign_server_catalog | foreign_server_name | option_name | --------------------------+------------------------+---------------------+------ PUBLIC | regression | t1 | modified | 1 regress_test_role | regression | s5 | modified | 1 regress_test_role | regression | s6 | username | test regress_test_role | regression | t1 | password | boo regress_test_role | regression | t1 | username | bob (5 rows) SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' AND o grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_t ---------------------------+-----------------------+----------------+----------- regress_foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPP regress_test_indirect | regress_test_indirect | regression | | s6 | FOREIGN SERVER | USAGE | Y regress_test_indirect | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES (3 rows) SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' AND grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_t ---------------------------+-----------------------+----------------+----------- regress_foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPP regress_test_indirect | regress_test_indirect | regression | | s6 | FOREIGN SERVER | USAGE | Y regress_test_indirect | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES (3 rows) DROP USER MAPPING FOR current_user SERVER t1; SET ROLE regress_test_role2; SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4; authorization_identifier | foreign_server_catalog | foreign_server_name | option_name | --------------------------+------------------------+---------------------+------ regress_test_role | regression | s6 | username | (1 row) RESET ROLE; -- has_foreign_data_wrapper_privilege SELECT has_foreign_data_wrapper_privilege('regress_test_role', (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE'); has_foreign_data_wrapper_privilege ------------------------------------ t (1 row) SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE'); has_foreign_data_wrapper_privilege ------------------------------------ t (1 row) SELECT has_foreign_data_wrapper_privilege( (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE'); has_foreign_data_wrapper_privilege ------------------------------------ t (1 row) SELECT has_foreign_data_wrapper_privilege( (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE'); has_foreign_data_wrapper_privilege ------------------------------------ t (1 row) SELECT has_foreign_data_wrapper_privilege( (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 'foo', 'USAGE'); has_foreign_data_wrapper_privilege ------------------------------------ t (1 row) SELECT has_foreign_data_wrapper_privilege('foo', 'USAGE'); has_foreign_data_wrapper_privilege ------------------------------------ t (1 row) GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE'); has_foreign_data_wrapper_privilege ------------------------------------ t (1 row) -- has_server_privilege SELECT has_server_privilege('regress_test_role', (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE'); has_server_privilege ---------------------- f (1 row) SELECT has_server_privilege('regress_test_role', 's8', 'USAGE'); has_server_privilege ---------------------- f (1 row) SELECT has_server_privilege( (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE'); has_server_privilege ---------------------- f (1 row) SELECT has_server_privilege( (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE'); has_server_privilege ---------------------- t (1 row) SELECT has_server_privilege( (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 's8', 'USAGE'); has_server_privilege ---------------------- f (1 row) SELECT has_server_privilege('s8', 'USAGE'); has_server_privilege ---------------------- t (1 row) GRANT USAGE ON FOREIGN SERVER s8 TO regress_test_role; SELECT has_server_privilege('regress_test_role', 's8', 'USAGE'); has_server_privilege ---------------------- t (1 row) REVOKE USAGE ON FOREIGN SERVER s8 FROM regress_test_role; GRANT USAGE ON FOREIGN SERVER s4 TO regress_test_role; DROP USER MAPPING FOR public SERVER s4; ALTER SERVER s6 OPTIONS (DROP host, DROP dbname); ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (DROP username); ALTER FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator; WARNING: changing the foreign-data wrapper validator can cause the options for dependent obj -- Privileges SET ROLE regress_unprivileged_role; CREATE FOREIGN DATA WRAPPER foobar; -- ERROR ERROR: permission denied to create foreign-data wrapper "foobar" HINT: Must be superuser to create a foreign-data wrapper. ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR ERROR: permission denied to alter foreign-data wrapper "foo" HINT: Must be superuser to alter a foreign-data wrapper. ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_unprivileged_role; -- ERROR ERROR: permission denied to change owner of foreign-data wrapper "foo" HINT: Must be superuser to change owner of a foreign-data wrapper. DROP FOREIGN DATA WRAPPER foo; -- ERROR ERROR: must be owner of foreign-data wrapper foo GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR ERROR: permission denied for foreign-data wrapper foo CREATE SERVER s9 FOREIGN DATA WRAPPER foo; -- ERROR ERROR: permission denied for foreign-data wrapper foo ALTER SERVER s4 VERSION '0.5'; -- ERROR ERROR: must be owner of foreign server s4 ALTER SERVER s4 OWNER TO regress_unprivileged_role; -- ERROR ERROR: must be owner of foreign server s4 DROP SERVER s4; -- ERROR ERROR: must be owner of foreign server s4 GRANT USAGE ON FOREIGN SERVER s4 TO regress_test_role; -- ERROR ERROR: permission denied for foreign server s4 CREATE USER MAPPING FOR public SERVER s4; -- ERROR ERROR: must be owner of foreign server s4 ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (gotcha 'true'); -- ERROR ERROR: must be owner of foreign server s6 DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR ERROR: must be owner of foreign server s6 RESET ROLE; GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO regress_unprivileged_role; GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_unprivileged_role WITH GRANT OPTION; SET ROLE regress_unprivileged_role; CREATE FOREIGN DATA WRAPPER foobar; -- ERROR ERROR: permission denied to create foreign-data wrapper "foobar" HINT: Must be superuser to create a foreign-data wrapper. ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR ERROR: permission denied to alter foreign-data wrapper "foo" HINT: Must be superuser to alter a foreign-data wrapper. DROP FOREIGN DATA WRAPPER foo; -- ERROR ERROR: must be owner of foreign-data wrapper foo GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO regress_test_role; -- WARNING WARNING: no privileges were granted for "postgresql" GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; CREATE SERVER s9 FOREIGN DATA WRAPPER postgresql; ALTER SERVER s6 VERSION '0.5'; -- ERROR ERROR: must be owner of foreign server s6 DROP SERVER s6; -- ERROR ERROR: must be owner of foreign server s6 GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role; -- ERROR ERROR: permission denied for foreign server s6 GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role; CREATE USER MAPPING FOR public SERVER s6; -- ERROR ERROR: must be owner of foreign server s6 CREATE USER MAPPING FOR public SERVER s9; ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (gotcha 'true'); -- ERROR ERROR: must be owner of foreign server s6 DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR ERROR: must be owner of foreign server s6 RESET ROLE; REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_unprivileged_role; -- ERROR ERROR: dependent privileges exist HINT: Use CASCADE to revoke them too. REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_unprivileged_role CASCADE; SET ROLE regress_unprivileged_role; GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR ERROR: permission denied for foreign-data wrapper foo CREATE SERVER s10 FOREIGN DATA WRAPPER foo; -- ERROR ERROR: permission denied for foreign-data wrapper foo ALTER SERVER s9 VERSION '1.1'; GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role; CREATE USER MAPPING FOR current_user SERVER s9; DROP SERVER s9 CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to user mapping for public on server s9 drop cascades to user mapping for regress_unprivileged_role on server s9 RESET ROLE; CREATE SERVER s9 FOREIGN DATA WRAPPER foo; GRANT USAGE ON FOREIGN SERVER s9 TO regress_unprivileged_role; SET ROLE regress_unprivileged_role; ALTER SERVER s9 VERSION '1.2'; -- ERROR ERROR: must be owner of foreign server s9 GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role; -- WARNING WARNING: no privileges were granted for "s9" CREATE USER MAPPING FOR current_user SERVER s9; DROP SERVER s9 CASCADE; -- ERROR ERROR: must be owner of foreign server s9 -- Check visibility of user mapping data SET ROLE regress_test_role; CREATE SERVER s10 FOREIGN DATA WRAPPER foo; CREATE USER MAPPING FOR public SERVER s10 OPTIONS (user 'secret'); CREATE USER MAPPING FOR regress_unprivileged_role SERVER s10 OPTIONS (user 'secret'); -- owner of server can see some option fields \deu+ List of user mappings Server | User name | FDW options --------+---------------------------+------------------- s10 | public | ("user" 'secret') s10 | regress_unprivileged_role | s4 | regress_foreign_data_user | s5 | regress_test_role | (modified '1') s6 | regress_test_role | s8 | public | s8 | regress_foreign_data_user | s9 | regress_unprivileged_role | t1 | public | (modified '1') (9 rows) RESET ROLE; -- superuser can see all option fields \deu+ List of user mappings Server | User name | FDW options --------+---------------------------+--------------------- s10 | public | ("user" 'secret') s10 | regress_unprivileged_role | ("user" 'secret') s4 | regress_foreign_data_user | s5 | regress_test_role | (modified '1') s6 | regress_test_role | s8 | public | s8 | regress_foreign_data_user | (password 'public') s9 | regress_unprivileged_role | t1 | public | (modified '1') (9 rows) -- unprivileged user cannot see any option field SET ROLE regress_unprivileged_role; \deu+ List of user mappings Server | User name | FDW options --------+---------------------------+------------- s10 | public | s10 | regress_unprivileged_role | s4 | regress_foreign_data_user | s5 | regress_test_role | s6 | regress_test_role | s8 | public | s8 | regress_foreign_data_user | s9 | regress_unprivileged_role | t1 | public | (9 rows) RESET ROLE; DROP SERVER s10 CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to user mapping for public on server s10 drop cascades to user mapping for regress_unprivileged_role on server s10 -- Triggers CREATE FUNCTION dummy_trigger() RETURNS TRIGGER AS $$ BEGIN RETURN NULL; END $$ language plpgsql; CREATE TRIGGER trigtest_before_stmt BEFORE INSERT OR UPDATE OR DELETE ON foreign_schema.foreign_table_1 FOR EACH STATEMENT EXECUTE PROCEDURE dummy_trigger(); CREATE TRIGGER trigtest_after_stmt AFTER INSERT OR UPDATE OR DELETE ON foreign_schema.foreign_table_1 FOR EACH STATEMENT EXECUTE PROCEDURE dummy_trigger(); CREATE TRIGGER trigtest_after_stmt_tt AFTER INSERT OR UPDATE OR DELETE -- ERROR ON foreign_schema.foreign_table_1 REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE PROCEDURE dummy_trigger(); ERROR: "foreign_table_1" is a foreign table DETAIL: Triggers on foreign tables cannot have transition tables. CREATE TRIGGER trigtest_before_row BEFORE INSERT OR UPDATE OR DELETE ON foreign_schema.foreign_table_1 FOR EACH ROW EXECUTE PROCEDURE dummy_trigger(); CREATE TRIGGER trigtest_after_row AFTER INSERT OR UPDATE OR DELETE ON foreign_schema.foreign_table_1 FOR EACH ROW EXECUTE PROCEDURE dummy_trigger(); CREATE CONSTRAINT TRIGGER trigtest_constraint AFTER INSERT OR UPDATE OR DELETE ON foreign_schema.foreign_table_1 FOR EACH ROW EXECUTE PROCEDURE dummy_trigger(); ERROR: "foreign_table_1" is a foreign table DETAIL: Foreign tables cannot have constraint triggers. ALTER FOREIGN TABLE foreign_schema.foreign_table_1 DISABLE TRIGGER trigtest_before_stmt; ALTER FOREIGN TABLE foreign_schema.foreign_table_1 ENABLE TRIGGER trigtest_before_stmt; DROP TRIGGER trigtest_before_stmt ON foreign_schema.foreign_table_1; DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1; DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1; DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1; DROP FUNCTION dummy_trigger(); -- Table inheritance CREATE TABLE fd_pt1 ( c1 integer NOT NULL, c2 text, c3 date ); CREATE FOREIGN TABLE ft2 () INHERITS (fd_pt1) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); \d+ fd_pt1 Table "public.fd_pt1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN \d+ ft2 Foreign table "public.ft2" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" (inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 DROP FOREIGN TABLE ft2; \d+ fd_pt1 Table "public.fd_pt1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" CREATE FOREIGN TABLE ft2 ( c1 integer NOT NULL, c2 text, c3 date ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); \d+ ft2 Foreign table "public.ft2" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; \d+ fd_pt1 Table "public.fd_pt1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN \d+ ft2 Foreign table "public.ft2" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" (local, inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 CREATE TABLE ct3() INHERITS(ft2); CREATE FOREIGN TABLE ft3 ( c1 integer NOT NULL, c2 text, c3 date ) INHERITS(ft2) SERVER s0; NOTICE: merging column "c1" with inherited definition NOTICE: merging column "c2" with inherited definition NOTICE: merging column "c3" with inherited definition \d+ ft2 Foreign table "public.ft2" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" (local, inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 Child tables: ct3, ft3, FOREIGN \d+ ct3 Table "public.ct3" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" (inherited) Inherits: ft2 \d+ ft3 Foreign table "public.ft3" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | Not-null constraints: "ft3_c1_not_null" NOT NULL "c1" (local, inherited) Server: s0 Inherits: ft2 -- add attributes recursively ALTER TABLE fd_pt1 ADD COLUMN c4 integer; ALTER TABLE fd_pt1 ADD COLUMN c5 integer DEFAULT 0; ALTER TABLE fd_pt1 ADD COLUMN c6 integer; ALTER TABLE fd_pt1 ADD COLUMN c7 integer NOT NULL; ALTER TABLE fd_pt1 ADD COLUMN c8 integer; \d+ fd_pt1 Table "public.fd_pt1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | c4 | integer | | | | plain | | c5 | integer | | | 0 | plain | | c6 | integer | | | | plain | | c7 | integer | | not null | | plain | | c8 | integer | | | | plain | | Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" "fd_pt1_c7_not_null" NOT NULL "c7" Child tables: ft2, FOREIGN \d+ ft2 Foreign table "public.ft2" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | c4 | integer | | | | | plain | | c5 | integer | | | 0 | | plain | | c6 | integer | | | | | plain | | c7 | integer | | not null | | | plain | | c8 | integer | | | | | plain | | Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" (local, inherited) "fd_pt1_c7_not_null" NOT NULL "c7" (inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 Child tables: ct3, ft3, FOREIGN \d+ ct3 Table "public.ct3" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | c4 | integer | | | | plain | | c5 | integer | | | 0 | plain | | c6 | integer | | | | plain | | c7 | integer | | not null | | plain | | c8 | integer | | | | plain | | Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" (inherited) "fd_pt1_c7_not_null" NOT NULL "c7" (inherited) Inherits: ft2 \d+ ft3 Foreign table "public.ft3" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | c4 | integer | | | | | plain | | c5 | integer | | | 0 | | plain | | c6 | integer | | | | | plain | | c7 | integer | | not null | | | plain | | c8 | integer | | | | | plain | | Not-null constraints: "ft3_c1_not_null" NOT NULL "c1" (local, inherited) "fd_pt1_c7_not_null" NOT NULL "c7" (inherited) Server: s0 Inherits: ft2 -- alter attributes recursively ALTER TABLE fd_pt1 ALTER COLUMN c4 SET DEFAULT 0; ALTER TABLE fd_pt1 ALTER COLUMN c5 DROP DEFAULT; ALTER TABLE fd_pt1 ALTER COLUMN c6 SET NOT NULL; ALTER TABLE fd_pt1 ALTER COLUMN c7 DROP NOT NULL; ALTER TABLE fd_pt1 ALTER COLUMN c8 TYPE char(10) USING '0'; -- ERROR ERROR: "ft2" is not a table ALTER TABLE fd_pt1 ALTER COLUMN c8 TYPE char(10); ALTER TABLE fd_pt1 ALTER COLUMN c8 SET DATA TYPE text; ALTER TABLE fd_pt1 ALTER COLUMN c1 SET STATISTICS 10000; ALTER TABLE fd_pt1 ALTER COLUMN c1 SET (n_distinct = 100); ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STATISTICS -1; ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STORAGE EXTERNAL; \d+ fd_pt1 Table "public.fd_pt1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- c1 | integer | | not null | | plain | 10000 | c2 | text | | | | extended | | c3 | date | | | | plain | | c4 | integer | | | 0 | plain | | c5 | integer | | | | plain | | c6 | integer | | not null | | plain | | c7 | integer | | | | plain | | c8 | text | | | | external | | Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" "fd_pt1_c6_not_null" NOT NULL "c6" Child tables: ft2, FOREIGN \d+ ft2 Foreign table "public.ft2" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- c1 | integer | | not null | | | plain | 10000 | c2 | text | | | | | extended | | c3 | date | | | | | plain | | c4 | integer | | | 0 | | plain | | c5 | integer | | | | | plain | | c6 | integer | | not null | | | plain | | c7 | integer | | | | | plain | | c8 | text | | | | | external | | Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" (local, inherited) "fd_pt1_c6_not_null" NOT NULL "c6" (inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 Child tables: ct3, ft3, FOREIGN -- drop attributes recursively ALTER TABLE fd_pt1 DROP COLUMN c4; ALTER TABLE fd_pt1 DROP COLUMN c5; ALTER TABLE fd_pt1 DROP COLUMN c6; ALTER TABLE fd_pt1 DROP COLUMN c7; ALTER TABLE fd_pt1 DROP COLUMN c8; \d+ fd_pt1 Table "public.fd_pt1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- c1 | integer | | not null | | plain | 10000 | c2 | text | | | | extended | | c3 | date | | | | plain | | Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN \d+ ft2 Foreign table "public.ft2" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- c1 | integer | | not null | | | plain | 10000 | c2 | text | | | | | extended | | c3 | date | | | | | plain | | Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" (local, inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 Child tables: ct3, ft3, FOREIGN -- add constraints recursively ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk1 CHECK (c1 > 0) NO INHERIT; ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk2 CHECK (c2 <> ''); -- connoinherit should be true for NO INHERIT constraint SELECT relname, conname, contype, conislocal, coninhcount, connoinherit FROM pg_class AS pc JOIN pg_constraint AS pgc ON (conrelid = pc.oid) WHERE pc.relname = 'fd_pt1' ORDER BY 1,2; relname | conname | contype | conislocal | coninhcount | connoinherit ---------+--------------------+---------+------------+-------------+------------ fd_pt1 | fd_pt1_c1_not_null | n | t | 0 | f fd_pt1 | fd_pt1chk1 | c | t | 0 | t fd_pt1 | fd_pt1chk2 | c | t | 0 | f (3 rows) -- child does not inherit NO INHERIT constraints \d+ fd_pt1 Table "public.fd_pt1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- c1 | integer | | not null | | plain | 10000 | c2 | text | | | | extended | | c3 | date | | | | plain | | Check constraints: "fd_pt1chk1" CHECK (c1 > 0) NO INHERIT "fd_pt1chk2" CHECK (c2 <> ''::text) Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN \d+ ft2 Foreign table "public.ft2" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- c1 | integer | | not null | | | plain | 10000 | c2 | text | | | | | extended | | c3 | date | | | | | plain | | Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" (local, inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 Child tables: ct3, ft3, FOREIGN DROP FOREIGN TABLE ft2; -- ERROR ERROR: cannot drop foreign table ft2 because other objects depend on it DETAIL: table ct3 depends on foreign table ft2 foreign table ft3 depends on foreign table ft2 HINT: Use DROP ... CASCADE to drop the dependent objects too. DROP FOREIGN TABLE ft2 CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table ct3 drop cascades to foreign table ft3 CREATE FOREIGN TABLE ft2 ( c1 integer NOT NULL, c2 text, c3 date ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- child must have parent's INHERIT constraints ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; -- ERROR ERROR: child table is missing constraint "fd_pt1chk2" ALTER FOREIGN TABLE ft2 ADD CONSTRAINT fd_pt1chk2 CHECK (c2 <> ''); ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; -- child does not inherit NO INHERIT constraints \d+ fd_pt1 Table "public.fd_pt1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- c1 | integer | | not null | | plain | 10000 | c2 | text | | | | extended | | c3 | date | | | | plain | | Check constraints: "fd_pt1chk1" CHECK (c1 > 0) NO INHERIT "fd_pt1chk2" CHECK (c2 <> ''::text) Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN \d+ ft2 Foreign table "public.ft2" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" (local, inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 -- drop constraints recursively ALTER TABLE fd_pt1 DROP CONSTRAINT fd_pt1chk1 CASCADE; ALTER TABLE fd_pt1 DROP CONSTRAINT fd_pt1chk2 CASCADE; -- NOT VALID case INSERT INTO fd_pt1 VALUES (1, 'fd_pt1'::text, '1994-01-01'::date); ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk3 CHECK (c2 <> '') NOT VALID; \d+ fd_pt1 Table "public.fd_pt1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- c1 | integer | | not null | | plain | 10000 | c2 | text | | | | extended | | c3 | date | | | | plain | | Check constraints: "fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN \d+ ft2 Foreign table "public.ft2" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) "fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" (local, inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 -- VALIDATE CONSTRAINT need do nothing on foreign tables ALTER TABLE fd_pt1 VALIDATE CONSTRAINT fd_pt1chk3; \d+ fd_pt1 Table "public.fd_pt1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- c1 | integer | | not null | | plain | 10000 | c2 | text | | | | extended | | c3 | date | | | | plain | | Check constraints: "fd_pt1chk3" CHECK (c2 <> ''::text) Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN \d+ ft2 Foreign table "public.ft2" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) "fd_pt1chk3" CHECK (c2 <> ''::text) Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" (local, inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 -- changes name of an attribute recursively ALTER TABLE fd_pt1 RENAME COLUMN c1 TO f1; ALTER TABLE fd_pt1 RENAME COLUMN c2 TO f2; ALTER TABLE fd_pt1 RENAME COLUMN c3 TO f3; -- changes name of a constraint recursively ALTER TABLE fd_pt1 RENAME CONSTRAINT fd_pt1chk3 TO f2_check; \d+ fd_pt1 Table "public.fd_pt1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- f1 | integer | | not null | | plain | 10000 | f2 | text | | | | extended | | f3 | date | | | | plain | | Check constraints: "f2_check" CHECK (f2 <> ''::text) Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "f1" Child tables: ft2, FOREIGN \d+ ft2 Foreign table "public.ft2" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- f1 | integer | | not null | | | plain | | f2 | text | | | | | extended | | f3 | date | | | | | plain | | Check constraints: "f2_check" CHECK (f2 <> ''::text) "fd_pt1chk2" CHECK (f2 <> ''::text) Not-null constraints: "ft2_c1_not_null" NOT NULL "f1" (local, inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 DROP TABLE fd_pt1 CASCADE; NOTICE: drop cascades to foreign table ft2 -- IMPORT FOREIGN SCHEMA IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR ERROR: foreign-data wrapper "foo" has no handler IMPORT FOREIGN SCHEMA s1 LIMIT TO (t1) FROM SERVER s9 INTO public; --ERROR ERROR: foreign-data wrapper "foo" has no handler IMPORT FOREIGN SCHEMA s1 EXCEPT (t1) FROM SERVER s9 INTO public; -- ERROR ERROR: foreign-data wrapper "foo" has no handler IMPORT FOREIGN SCHEMA s1 EXCEPT (t1, t2) FROM SERVER s9 INTO public OPTIONS (option1 'value1', option2 'value2'); -- ERROR ERROR: foreign-data wrapper "foo" has no handler -- DROP FOREIGN TABLE DROP FOREIGN TABLE no_table; -- ERROR ERROR: foreign table "no_table" does not exist DROP FOREIGN TABLE IF EXISTS no_table; NOTICE: foreign table "no_table" does not exist, skipping DROP FOREIGN TABLE foreign_schema.foreign_table_1; -- REASSIGN OWNED/DROP OWNED of foreign objects REASSIGN OWNED BY regress_test_role TO regress_test_role2; DROP OWNED BY regress_test_role2; ERROR: cannot drop desired object(s) because other objects depend on them DETAIL: user mapping for regress_test_role on server s5 depends on server s5 HINT: Use DROP ... CASCADE to drop the dependent objects too. DROP OWNED BY regress_test_role2 CASCADE; NOTICE: drop cascades to user mapping for regress_test_role on server s5 -- Foreign partition DDL stuff CREATE TABLE fd_pt2 ( c1 integer NOT NULL, c2 text, c3 date ) PARTITION BY LIST (c1); CREATE FOREIGN TABLE fd_pt2_1 PARTITION OF fd_pt2 FOR VALUES IN (1) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); \d+ fd_pt2 Partitioned table "public.fd_pt2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN \d+ fd_pt2_1 Foreign table "public.fd_pt2_1" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | Partition of: fd_pt2 FOR VALUES IN (1) Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1)) Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" (inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') -- partition cannot have additional columns DROP FOREIGN TABLE fd_pt2_1; CREATE FOREIGN TABLE fd_pt2_1 ( c1 integer NOT NULL, c2 text, c3 date, c4 char ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); \d+ fd_pt2_1 Foreign table "public.fd_pt2_1" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+--------------+-----------+----------+---------+-------------+--------- c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | c4 | character(1) | | | | | extended | | Not-null constraints: "fd_pt2_1_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); -- ERROR ERROR: table "fd_pt2_1" contains column "c4" not found in parent "fd_pt2" DETAIL: The new partition may contain only the columns present in parent. DROP FOREIGN TABLE fd_pt2_1; \d+ fd_pt2 Partitioned table "public.fd_pt2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" Number of partitions: 0 CREATE FOREIGN TABLE fd_pt2_1 ( c1 integer NOT NULL, c2 text, c3 date ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); \d+ fd_pt2_1 Foreign table "public.fd_pt2_1" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | Not-null constraints: "fd_pt2_1_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') -- no attach partition validation occurs for foreign tables ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); \d+ fd_pt2 Partitioned table "public.fd_pt2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN \d+ fd_pt2_1 Foreign table "public.fd_pt2_1" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | Partition of: fd_pt2 FOR VALUES IN (1) Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1)) Not-null constraints: "fd_pt2_1_c1_not_null" NOT NULL "c1" (inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') -- cannot add column to a partition ALTER TABLE fd_pt2_1 ADD c4 char; ERROR: cannot add column to a partition -- ok to have a partition's own constraints though ALTER TABLE fd_pt2_1 ALTER c3 SET NOT NULL; ALTER TABLE fd_pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <> ''); \d+ fd_pt2 Partitioned table "public.fd_pt2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN \d+ fd_pt2_1 Foreign table "public.fd_pt2_1" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | not null | | | plain | | Partition of: fd_pt2 FOR VALUES IN (1) Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1)) Check constraints: "p21chk" CHECK (c2 <> ''::text) Not-null constraints: "fd_pt2_1_c1_not_null" NOT NULL "c1" (inherited) "fd_pt2_1_c3_not_null" NOT NULL "c3" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') -- cannot drop inherited NOT NULL constraint from a partition ALTER TABLE fd_pt2_1 ALTER c1 DROP NOT NULL; ERROR: column "c1" is marked NOT NULL in parent table -- partition must have parent's constraints ALTER TABLE fd_pt2 DETACH PARTITION fd_pt2_1; ALTER TABLE fd_pt2 ALTER c2 SET NOT NULL; \d+ fd_pt2 Partitioned table "public.fd_pt2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- c1 | integer | | not null | | plain | | c2 | text | | not null | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" "fd_pt2_c2_not_null" NOT NULL "c2" Number of partitions: 0 \d+ fd_pt2_1 Foreign table "public.fd_pt2_1" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | not null | | | plain | | Check constraints: "p21chk" CHECK (c2 <> ''::text) Not-null constraints: "fd_pt2_1_c1_not_null" NOT NULL "c1" "fd_pt2_1_c3_not_null" NOT NULL "c3" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); -- ERROR ERROR: column "c2" in child table "fd_pt2_1" must be marked NOT NULL ALTER FOREIGN TABLE fd_pt2_1 ALTER c2 SET NOT NULL; ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); ALTER TABLE fd_pt2 DETACH PARTITION fd_pt2_1; ALTER TABLE fd_pt2 ADD CONSTRAINT fd_pt2chk1 CHECK (c1 > 0); \d+ fd_pt2 Partitioned table "public.fd_pt2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-- c1 | integer | | not null | | plain | | c2 | text | | not null | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) Check constraints: "fd_pt2chk1" CHECK (c1 > 0) Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" "fd_pt2_c2_not_null" NOT NULL "c2" Number of partitions: 0 \d+ fd_pt2_1 Foreign table "public.fd_pt2_1" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Descriptio --------+---------+-----------+----------+---------+-------------+----------+--- c1 | integer | | not null | | | plain | | c2 | text | | not null | | | extended | | c3 | date | | not null | | | plain | | Check constraints: "p21chk" CHECK (c2 <> ''::text) Not-null constraints: "fd_pt2_1_c1_not_null" NOT NULL "c1" "fd_pt2_1_c2_not_null" NOT NULL "c2" "fd_pt2_1_c3_not_null" NOT NULL "c3" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); -- ERROR ERROR: child table is missing constraint "fd_pt2chk1" ALTER FOREIGN TABLE fd_pt2_1 ADD CONSTRAINT fd_pt2chk1 CHECK (c1 > 0); ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); DROP FOREIGN TABLE fd_pt2_1; DROP TABLE fd_pt2; -- foreign table cannot be part of partition tree made of temporary -- relations. CREATE TEMP TABLE temp_parted (a int) PARTITION BY LIST (a); CREATE FOREIGN TABLE foreign_part PARTITION OF temp_parted DEFAULT SERVER s0; -- ERROR ERROR: cannot create a permanent relation as partition of temporary relation "temp_parted" CREATE FOREIGN TABLE foreign_part (a int) SERVER s0; ALTER TABLE temp_parted ATTACH PARTITION foreign_part DEFAULT; -- ERROR ERROR: cannot attach a permanent relation as partition of temporary relation "temp_parted" DROP FOREIGN TABLE foreign_part; DROP TABLE temp_parted; -- Cleanup DROP SCHEMA foreign_schema CASCADE; DROP ROLE regress_test_role; -- ERROR ERROR: role "regress_test_role" cannot be dropped because some objects depend on it DETAIL: privileges for foreign-data wrapper foo privileges for server s4 owner of user mapping for regress_test_role on server s6 DROP SERVER t1 CASCADE; NOTICE: drop cascades to user mapping for public on server t1 DROP USER MAPPING FOR regress_test_role SERVER s6; DROP FOREIGN DATA WRAPPER foo CASCADE; NOTICE: drop cascades to 5 other objects DETAIL: drop cascades to server s4 drop cascades to user mapping for regress_foreign_data_user on server s4 drop cascades to server s6 drop cascades to server s9 drop cascades to user mapping for regress_unprivileged_role on server s9 DROP SERVER s8 CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to user mapping for regress_foreign_data_user on server s8 drop cascades to user mapping for public on server s8 DROP ROLE regress_test_indirect; DROP ROLE regress_test_role; DROP ROLE regress_unprivileged_role; -- ERROR ERROR: role "regress_unprivileged_role" cannot be dropped because some objects depend on it DETAIL: privileges for foreign-data wrapper postgresql REVOKE ALL ON FOREIGN DATA WRAPPER postgresql FROM regress_unprivileged_role; DROP ROLE regress_unprivileged_role; DROP ROLE regress_test_role2; DROP FOREIGN DATA WRAPPER postgresql CASCADE; DROP FOREIGN DATA WRAPPER dummy CASCADE; NOTICE: drop cascades to server s0 \c DROP ROLE regress_foreign_data_user; -- At this point we should have no wrappers, no servers, and no mappings. SELECT fdwname, fdwhandler, fdwvalidator, fdwoptions FROM pg_foreign_data_wrapper; fdwname | fdwhandler | fdwvalidator | fdwoptions ---------+------------+--------------+------------ (0 rows) SELECT srvname, srvoptions FROM pg_foreign_server; srvname | srvoptions ---------+------------ (0 rows) SELECT * FROM pg_user_mapping; oid | umuser | umserver | umoptions -----+--------+----------+----------- (0 rows) [Dauer der Verarbeitung: 0.54 Sekunden, vorverarbeitet 2026-08-08] |
2026-08-25