diff options
Diffstat (limited to 'contrib/postgres_fdw/expected/postgres_fdw.out')
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 208 |
1 files changed, 203 insertions, 5 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index b4a04d2c143..e33c92d7f1c 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -17,7 +17,10 @@ DO $d$ OPTIONS (dbname '$$||current_database()||$$', port '$$||current_setting('port')||$$' )$$; - + EXECUTE $$CREATE SERVER loopback4 FOREIGN DATA WRAPPER postgres_fdw + OPTIONS (dbname '$$||current_database()||$$', + port '$$||current_setting('port')||$$' + )$$; END; $d$; CREATE USER MAPPING FOR public SERVER testserver1 @@ -25,6 +28,7 @@ CREATE USER MAPPING FOR public SERVER testserver1 CREATE USER MAPPING FOR CURRENT_USER SERVER loopback; CREATE USER MAPPING FOR CURRENT_USER SERVER loopback2; CREATE USER MAPPING FOR public SERVER loopback3; +CREATE USER MAPPING FOR public SERVER loopback4; -- =================================================================== -- create objects used through FDW loopback server -- =================================================================== @@ -140,6 +144,11 @@ CREATE FOREIGN TABLE ft7 ( c2 int NOT NULL, c3 text ) SERVER loopback3 OPTIONS (schema_name 'S 1', table_name 'T 4'); +CREATE FOREIGN TABLE ft8 ( + c1 int NOT NULL, + c2 int NOT NULL, + c3 text +) SERVER loopback4 OPTIONS (schema_name 'S 1', table_name 'T 4'); -- =================================================================== -- tests for validator -- =================================================================== @@ -211,7 +220,8 @@ ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1'); public | ft5 | loopback | (schema_name 'S 1', table_name 'T 4') | public | ft6 | loopback2 | (schema_name 'S 1', table_name 'T 4') | public | ft7 | loopback3 | (schema_name 'S 1', table_name 'T 4') | -(6 rows) + public | ft8 | loopback4 | (schema_name 'S 1', table_name 'T 4') | +(7 rows) -- Test that alteration of server options causes reconnection -- Remote's errors might be non-English, so hide them to ensure stable results @@ -9053,9 +9063,9 @@ ERROR: 08006 COMMIT; -- Clean up DROP PROCEDURE terminate_backend_and_wait(text); --- =================================================================== --- test connection invalidation cases --- =================================================================== +-- ============================================================================= +-- test connection invalidation cases and postgres_fdw_get_connections function +-- ============================================================================= -- This test case is for closing the connection in pgfdw_xact_callback BEGIN; -- List all the existing cached connections. Only loopback2 should be output. @@ -9118,6 +9128,194 @@ SELECT * FROM postgres_fdw_get_connections() ORDER BY 1; loopback2 | t (1 row) +-- ======================================================================= +-- test postgres_fdw_disconnect and postgres_fdw_disconnect_all functions +-- ======================================================================= +-- Return true as all cached connections are closed. +SELECT postgres_fdw_disconnect_all(); + postgres_fdw_disconnect_all +----------------------------- + t +(1 row) + +-- Ensure to cache loopback connection. +SELECT 1 FROM ft1 LIMIT 1; + ?column? +---------- + 1 +(1 row) + +BEGIN; +-- Ensure to cache loopback2 connection. +SELECT 1 FROM ft6 LIMIT 1; + ?column? +---------- + 1 +(1 row) + +-- List all the existing cached connections. loopback and loopback2 should be +-- output. +SELECT * FROM postgres_fdw_get_connections() ORDER BY 1; + server_name | valid +-------------+------- + loopback | t + loopback2 | t +(2 rows) + +-- Issue a warning and return false as loopback2 connection is still in use and +-- can not be closed. +SELECT postgres_fdw_disconnect('loopback2'); +WARNING: cannot close connection for server "loopback2" because it is still in use + postgres_fdw_disconnect +------------------------- + f +(1 row) + +-- Close loopback connection, return true and issue a warning as loopback2 +-- connection is still in use and can not be closed. +SELECT postgres_fdw_disconnect_all(); +WARNING: cannot close connection for server "loopback2" because it is still in use + postgres_fdw_disconnect_all +----------------------------- + t +(1 row) + +-- List all the existing cached connections. loopback2 should be output. +SELECT * FROM postgres_fdw_get_connections() ORDER BY 1; + server_name | valid +-------------+------- + loopback2 | t +(1 row) + +-- Ensure to cache loopback connection. +SELECT 1 FROM ft1 LIMIT 1; + ?column? +---------- + 1 +(1 row) + +-- Ensure to cache loopback4 connection. +SELECT 1 FROM ft8 LIMIT 1; + ?column? +---------- + 1 +(1 row) + +-- List all the existing cached connections. loopback, loopback2, loopback4 +-- should be output. +SELECT * FROM postgres_fdw_get_connections() ORDER BY 1; + server_name | valid +-------------+------- + loopback | t + loopback2 | t + loopback4 | t +(3 rows) + +DROP SERVER loopback4 CASCADE; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to user mapping for public on server loopback4 +drop cascades to foreign table ft8 +-- Return false as connections are still in use, warnings are issued. +SELECT postgres_fdw_disconnect_all(); +WARNING: cannot close dropped server connection because it is still in use +WARNING: cannot close connection for server "loopback" because it is still in use +WARNING: cannot close connection for server "loopback2" because it is still in use + postgres_fdw_disconnect_all +----------------------------- + f +(1 row) + +COMMIT; +-- Close loopback2 connection and return true. +SELECT postgres_fdw_disconnect('loopback2'); + postgres_fdw_disconnect +------------------------- + t +(1 row) + +-- List all the existing cached connections. loopback should be output. +SELECT * FROM postgres_fdw_get_connections() ORDER BY 1; + server_name | valid +-------------+------- + loopback | t +(1 row) + +-- Return false as loopback2 connectin is closed already. +SELECT postgres_fdw_disconnect('loopback2'); + postgres_fdw_disconnect +------------------------- + f +(1 row) + +-- Return an error as there is no foreign server with given name. +SELECT postgres_fdw_disconnect('unknownserver'); +ERROR: server "unknownserver" does not exist +-- Close loopback connection and return true. +SELECT postgres_fdw_disconnect_all(); + postgres_fdw_disconnect_all +----------------------------- + t +(1 row) + +-- List all the existing cached connections. No connection exists, so NULL +-- should be output. +SELECT * FROM postgres_fdw_get_connections() ORDER BY 1; + server_name | valid +-------------+------- +(0 rows) + +-- ============================================================================= +-- test case for having multiple cached connections for a foreign server +-- ============================================================================= +CREATE ROLE multi_conn_user1 SUPERUSER; +CREATE ROLE multi_conn_user2 SUPERUSER; +CREATE USER MAPPING FOR multi_conn_user1 SERVER loopback; +CREATE USER MAPPING FOR multi_conn_user2 SERVER loopback; +-- Will cache loopback connection with user mapping for multi_conn_user1 +SET ROLE multi_conn_user1; +SELECT 1 FROM ft1 LIMIT 1; + ?column? +---------- + 1 +(1 row) + +RESET ROLE; +-- Will cache loopback connection with user mapping for multi_conn_user2 +SET ROLE multi_conn_user2; +SELECT 1 FROM ft1 LIMIT 1; + ?column? +---------- + 1 +(1 row) + +RESET ROLE; +-- Should output two connections for loopback server +SELECT * FROM postgres_fdw_get_connections() ORDER BY 1; + server_name | valid +-------------+------- + loopback | t + loopback | t +(2 rows) + +-- Close loopback connections and return true. +SELECT postgres_fdw_disconnect('loopback'); + postgres_fdw_disconnect +------------------------- + t +(1 row) + +-- List all the existing cached connections. No connection exists, so NULL +-- should be output. +SELECT * FROM postgres_fdw_get_connections() ORDER BY 1; + server_name | valid +-------------+------- +(0 rows) + +-- Clean up +DROP USER MAPPING FOR multi_conn_user1 SERVER loopback; +DROP USER MAPPING FOR multi_conn_user2 SERVER loopback; +DROP ROLE multi_conn_user1; +DROP ROLE multi_conn_user2; -- =================================================================== -- batch insert -- =================================================================== |