diff options
Diffstat (limited to 'src/test/regress/expected/foreign_data.out')
| -rw-r--r-- | src/test/regress/expected/foreign_data.out | 2102 |
1 files changed, 2102 insertions, 0 deletions
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out new file mode 100644 index 0000000..1376bfe --- /dev/null +++ b/src/test/regress/expected/foreign_data.out @@ -0,0 +1,2102 @@ +-- +-- Test foreign-data wrapper and server management. +-- +-- 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, regress_test_role_super, regress_test_indirect, regress_unprivileged_role; +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_data_wrapper ORDER BY 1, 2, 3; + 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_handler; -- ERROR +ERROR: conflicting or redundant options +CREATE FOREIGN DATA WRAPPER test_fdw HANDLER test_fdw_handler; +DROP FOREIGN DATA WRAPPER test_fdw; +-- ALTER FOREIGN DATA WRAPPER +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 +ALTER FOREIGN DATA WRAPPER foo HANDLER test_fdw_handler; +WARNING: changing the foreign-data wrapper handler can change behavior of existing foreign tables +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", skipping +\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 | Description +------+---------------------------+----------------------+-------------------+------+---------+-------------+---------------- + 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 | Description +------+-------+----------------------+-------------------+------+---------+-------------+------------- +(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', dbname 'b'); +CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (foo '1'); -- ERROR +ERROR: invalid option "foo" +HINT: Valid options in this context are: authtype, service, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib +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 | Description +------+---------------------------+----------------------+-------------------+--------+---------+-----------------------------------+------------- + 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 | Description +------+---------------------------+----------------------+-------------------+--------+---------+-----------------------------------+------------- + 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 | Description +------+---------------------------+----------------------+-------------------+--------+---------+-----------------------------------+------------- + 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 | Description +------+---------------------------+----------------------+-------------------------------------------------------+--------+---------+-----------------------------------+------------- + s1 | regress_foreign_data_user | foo | regress_foreign_data_user=U/regress_foreign_data_user+| | 1.0 | (servername 's1') | + | | | 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_data_user+| | 16.0 | (host 'a', dbname 'b') | + | | | 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" +HINT: Valid options in this context are: authtype, service, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib +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 member of 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 | Description +------+---------------------------+----------------------+-------------------------------------------------------+--------+---------+--------------------------------------+------------- + s1 | regress_test_indirect | foo | regress_test_indirect=U/regress_test_indirect | | 1.1 | (servername 's1') | + 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_data_user+| | 16.0 | (host 'a', dbname 'b') | + | | | 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 | Description +-------+---------------------------+----------------------+-------------------------------------------------------+--------+---------+--------------------------------------+------------- + s1 | regress_test_indirect | foo | regress_test_indirect=U/regress_test_indirect | | 1.1 | (servername 's1') | + 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_data_user+| | 16.0 | (host 'a', dbname 'b') | + | | | 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: Valid options in this context are: user, password +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'); -- ERROR +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: Valid options in this context are: user, password +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 | Description +--------+---------+-----------+----------+---------+--------------------------------+----------+--------------+------------- + 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) +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 foreign table "ft1" +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: 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_part1" +DETAIL: 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 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 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 | Description +--------+---------+-----------+----------+---------+--------------------------------+----------+--------------+------------- + 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 | | +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: (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 type +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: "ft1" is not a table +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 SET SCHEMA foreign_schema; +ALTER FOREIGN TABLE ft1 SET TABLESPACE ts; -- ERROR +ERROR: relation "ft1" does not exist +ALTER FOREIGN TABLE foreign_schema.ft1 SET TABLESPACE ts; -- ERROR +ERROR: "ft1" is not a table, materialized view, index, or partitioned index +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') +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 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 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 escape '@'); +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 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 | library_name | foreign_data_wrapper_language +------------------------------+---------------------------+---------------------------+--------------+------------------------------- + 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_data_wrapper_name | foreign_server_type | foreign_server_version | authorization_identifier +------------------------+---------------------+------------------------------+---------------------------+---------------------+------------------------+--------------------------- + 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), 2, 3; + 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_identifier), 2, 3, 4; + authorization_identifier | foreign_server_catalog | foreign_server_name | option_name | option_value +---------------------------+------------------------+---------------------+--------------+-------------- + 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 object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5; + grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable +---------------------------+---------------------------+----------------+---------------+-------------+----------------------+----------------+-------------- + regress_foreign_data_user | regress_foreign_data_user | regression | | foo | FOREIGN DATA WRAPPER | USAGE | YES + regress_foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO + regress_test_indirect | regress_test_indirect | regression | | s6 | FOREIGN SERVER | USAGE | YES + 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 object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5; + grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable +---------------------------+---------------------------+----------------+---------------+-------------+----------------------+----------------+-------------- + regress_foreign_data_user | regress_foreign_data_user | regression | | foo | FOREIGN DATA WRAPPER | USAGE | YES + regress_foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO + regress_test_indirect | regress_test_indirect | regression | | s6 | FOREIGN SERVER | USAGE | YES + 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_catalog | foreign_server_name +-----------------------+----------------------+--------------------+------------------------+--------------------- + 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_value +-----------------------+----------------------+--------------------+-------------+-------------- + 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 | option_value +--------------------------+------------------------+---------------------+-------------+-------------- + 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 object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5; + grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable +---------------------------+-----------------------+----------------+---------------+-------------+----------------------+----------------+-------------- + regress_foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO + regress_test_indirect | regress_test_indirect | regression | | s6 | FOREIGN SERVER | USAGE | YES + 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 object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5; + grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable +---------------------------+-----------------------+----------------+---------------+-------------+----------------------+----------------+-------------- + regress_foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO + regress_test_indirect | regress_test_indirect | regression | | s6 | FOREIGN SERVER | USAGE | YES + 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 | option_value +--------------------------+------------------------+---------------------+-------------+-------------- + 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 objects to become invalid +-- 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 | | +Child tables: ft2 + +\d+ ft2 + Foreign table "public.ft2" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+---------+-----------+----------+---------+-------------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | +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 | | + +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 | Description +--------+---------+-----------+----------+---------+-------------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | +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 | | +Child tables: ft2 + +\d+ ft2 + Foreign table "public.ft2" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+---------+-----------+----------+---------+-------------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | +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 | Description +--------+---------+-----------+----------+---------+-------------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | +Server: s0 +FDW options: (delimiter ',', quote '"', "be quoted" 'value') +Inherits: fd_pt1 +Child tables: ct3, + ft3 + +\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 | | +Inherits: ft2 + +\d+ ft3 + Foreign table "public.ft3" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+---------+-----------+----------+---------+-------------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | +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 | | +Child tables: ft2 + +\d+ ft2 + Foreign table "public.ft2" + Column | Type | Collation | Nullable | Default | FDW options | 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 | | +Server: s0 +FDW options: (delimiter ',', quote '"', "be quoted" 'value') +Inherits: fd_pt1 +Child tables: ct3, + ft3 + +\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 | | +Inherits: ft2 + +\d+ ft3 + Foreign table "public.ft3" + Column | Type | Collation | Nullable | Default | FDW options | 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 | | +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 | | +Child tables: ft2 + +\d+ ft2 + Foreign table "public.ft2" + Column | Type | Collation | Nullable | Default | FDW options | 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 | | +Server: s0 +FDW options: (delimiter ',', quote '"', "be quoted" 'value') +Inherits: fd_pt1 +Child tables: ct3, + ft3 + +-- 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 | | +Child tables: ft2 + +\d+ ft2 + Foreign table "public.ft2" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+---------+-----------+----------+---------+-------------+----------+--------------+------------- + c1 | integer | | not null | | | plain | 10000 | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | +Server: s0 +FDW options: (delimiter ',', quote '"', "be quoted" 'value') +Inherits: fd_pt1 +Child tables: ct3, + ft3 + +-- 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_pt1chk1 | c | t | 0 | t + fd_pt1 | fd_pt1chk2 | c | t | 0 | f +(2 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) +Child tables: ft2 + +\d+ ft2 + Foreign table "public.ft2" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+---------+-----------+----------+---------+-------------+----------+--------------+------------- + c1 | integer | | not null | | | plain | 10000 | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | +Check constraints: + "fd_pt1chk2" CHECK (c2 <> ''::text) +Server: s0 +FDW options: (delimiter ',', quote '"', "be quoted" 'value') +Inherits: fd_pt1 +Child tables: ct3, + ft3 + +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) +Child tables: ft2 + +\d+ ft2 + Foreign table "public.ft2" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+---------+-----------+----------+---------+-------------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | +Check constraints: + "fd_pt1chk2" CHECK (c2 <> ''::text) +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 +Child tables: ft2 + +\d+ ft2 + Foreign table "public.ft2" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+---------+-----------+----------+---------+-------------+----------+--------------+------------- + 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 +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) +Child tables: ft2 + +\d+ ft2 + Foreign table "public.ft2" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+---------+-----------+----------+---------+-------------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | +Check constraints: + "fd_pt1chk2" CHECK (c2 <> ''::text) + "fd_pt1chk3" CHECK (c2 <> ''::text) +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) +Child tables: ft2 + +\d+ ft2 + Foreign table "public.ft2" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+---------+-----------+----------+---------+-------------+----------+--------------+------------- + f1 | integer | | not null | | | plain | | + f2 | text | | | | | extended | | + f3 | date | | | | | plain | | +Check constraints: + "f2_check" CHECK (f2 <> ''::text) + "fd_pt1chk2" CHECK (f2 <> ''::text) +Server: s0 +FDW options: (delimiter ',', quote '"', "be quoted" 'value') +Inherits: fd_pt1 + +-- TRUNCATE doesn't work on foreign tables, either directly or recursively +TRUNCATE ft2; -- ERROR +ERROR: "ft2" is not a table +TRUNCATE fd_pt1; -- ERROR +ERROR: "ft2" is not a table +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) +Partitions: fd_pt2_1 FOR VALUES IN (1) + +\d+ fd_pt2_1 + Foreign table "public.fd_pt2_1" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+---------+-----------+----------+---------+-------------+----------+--------------+------------- + 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)) +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 | Description +--------+--------------+-----------+----------+---------+-------------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | + c4 | character(1) | | | | | extended | | +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) +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 | Description +--------+---------+-----------+----------+---------+-------------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | | | | plain | | +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) +Partitions: fd_pt2_1 FOR VALUES IN (1) + +\d+ fd_pt2_1 + Foreign table "public.fd_pt2_1" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+---------+-----------+----------+---------+-------------+----------+--------------+------------- + 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)) +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) +Partitions: fd_pt2_1 FOR VALUES IN (1) + +\d+ fd_pt2_1 + Foreign table "public.fd_pt2_1" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+---------+-----------+----------+---------+-------------+----------+--------------+------------- + 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) +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) +Number of partitions: 0 + +\d+ fd_pt2_1 + Foreign table "public.fd_pt2_1" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+---------+-----------+----------+---------+-------------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | | | | extended | | + c3 | date | | not null | | | plain | | +Check constraints: + "p21chk" CHECK (c2 <> ''::text) +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 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) +Number of partitions: 0 + +\d+ fd_pt2_1 + Foreign table "public.fd_pt2_1" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+---------+-----------+----------+---------+-------------+----------+--------------+------------- + c1 | integer | | not null | | | plain | | + c2 | text | | not null | | | extended | | + c3 | date | | not null | | | plain | | +Check constraints: + "p21chk" CHECK (c2 <> ''::text) +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); +-- TRUNCATE doesn't work on foreign tables, either directly or recursively +TRUNCATE fd_pt2_1; -- ERROR +ERROR: "fd_pt2_1" is not a table +TRUNCATE fd_pt2; -- ERROR +ERROR: "fd_pt2_1" is not a table +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) + |