🌐 AI搜索 & 代理 主页
Skip to content

Commit f77717b

Browse files
MasaoFujiiMasaoFujii
committed
postgres_fdw: Fix tests for CLOBBER_CACHE_ALWAYS.
The regression tests added in commits 708d165 and 411ae64 caused buildfarm failures when CLOBBER_CACHE_ALWAYS was enabled. This commit stabilizes those tests. The foreign server connections established by postgres_fdw behaves differently depending on whether CLOBBER_CACHE_ALWAYS is enabled or not. If it's not enabled, those connections are cached. On the other hand, if it's enabled, when the connections are established outside transaction block, they are not cached (i.e., they are immediately closed at the end of query that established them). So the subsequent postgres_fdw_get_connections() cannot list those connections and postgres_fdw_disconnect() cannot close them (because they are already closed). When the connections are established inside transaction block, they are cached whether CLOBBER_CACHE_ALWAYS was enabled or not. But if it's enabled, they are immediately marked as invalid, otherwise not. This causes the subsequent postgres_fdw_get_connections() to return different result in "valid" column depending on whether CLOBBER_CACHE_ALWAYS was enabled or not. This commit prevents the above differences of behavior from affecting the regression tests. Per buildfarm failure on trilobite. Original patch by Bharath Rupireddy. I (Fujii Masao) extracted the regression test fix from that and revised it a bit. Reported-by: Tom Lane Author: Bharath Rupireddy Reviewed-by: Fujii Masao Discussion: https://postgr.es/m/2688508.1611865371@sss.pgh.pa.us
1 parent f743a2b commit f77717b

File tree

2 files changed

+121
-199
lines changed

2 files changed

+121
-199
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out‎

Lines changed: 85 additions & 138 deletions
Original file line numberDiff line numberDiff line change
@@ -17,18 +17,13 @@ DO $d$
1717
OPTIONS (dbname '$$||current_database()||$$',
1818
port '$$||current_setting('port')||$$'
1919
)$$;
20-
EXECUTE $$CREATE SERVER loopback4 FOREIGN DATA WRAPPER postgres_fdw
21-
OPTIONS (dbname '$$||current_database()||$$',
22-
port '$$||current_setting('port')||$$'
23-
)$$;
2420
END;
2521
$d$;
2622
CREATE USER MAPPING FOR public SERVER testserver1
2723
OPTIONS (user 'value', password 'value');
2824
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
2925
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback2;
3026
CREATE USER MAPPING FOR public SERVER loopback3;
31-
CREATE USER MAPPING FOR public SERVER loopback4;
3227
-- ===================================================================
3328
-- create objects used through FDW loopback server
3429
-- ===================================================================
@@ -144,11 +139,6 @@ CREATE FOREIGN TABLE ft7 (
144139
c2 int NOT NULL,
145140
c3 text
146141
) SERVER loopback3 OPTIONS (schema_name 'S 1', table_name 'T 4');
147-
CREATE FOREIGN TABLE ft8 (
148-
c1 int NOT NULL,
149-
c2 int NOT NULL,
150-
c3 text
151-
) SERVER loopback4 OPTIONS (schema_name 'S 1', table_name 'T 4');
152142
-- ===================================================================
153143
-- tests for validator
154144
-- ===================================================================
@@ -220,8 +210,7 @@ ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
220210
public | ft5 | loopback | (schema_name 'S 1', table_name 'T 4') |
221211
public | ft6 | loopback2 | (schema_name 'S 1', table_name 'T 4') |
222212
public | ft7 | loopback3 | (schema_name 'S 1', table_name 'T 4') |
223-
public | ft8 | loopback4 | (schema_name 'S 1', table_name 'T 4') |
224-
(7 rows)
213+
(6 rows)
225214

226215
-- Test that alteration of server options causes reconnection
227216
-- Remote's errors might be non-English, so hide them to ensure stable results
@@ -9066,15 +9055,21 @@ DROP PROCEDURE terminate_backend_and_wait(text);
90669055
-- =============================================================================
90679056
-- test connection invalidation cases and postgres_fdw_get_connections function
90689057
-- =============================================================================
9069-
-- This test case is for closing the connection in pgfdw_xact_callback
9070-
BEGIN;
9071-
-- List all the existing cached connections. Only loopback2 should be output.
9072-
SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
9073-
server_name | valid
9074-
-------------+-------
9075-
loopback2 | t
9058+
-- Let's ensure to close all the existing cached connections.
9059+
SELECT 1 FROM postgres_fdw_disconnect_all();
9060+
?column?
9061+
----------
9062+
1
90769063
(1 row)
90779064

9065+
-- No cached connections, so no records should be output.
9066+
SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1;
9067+
server_name
9068+
-------------
9069+
(0 rows)
9070+
9071+
-- This test case is for closing the connection in pgfdw_xact_callback
9072+
BEGIN;
90789073
-- Connection xact depth becomes 1 i.e. the connection is in midst of the xact.
90799074
SELECT 1 FROM ft1 LIMIT 1;
90809075
?column?
@@ -9088,19 +9083,18 @@ SELECT 1 FROM ft7 LIMIT 1;
90889083
1
90899084
(1 row)
90909085

9091-
-- List all the existing cached connections. loopback and loopback3
9092-
-- also should be output as valid connections.
9093-
SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
9094-
server_name | valid
9095-
-------------+-------
9096-
loopback | t
9097-
loopback2 | t
9098-
loopback3 | t
9099-
(3 rows)
9086+
-- List all the existing cached connections. loopback and loopback3 should be
9087+
-- output.
9088+
SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1;
9089+
server_name
9090+
-------------
9091+
loopback
9092+
loopback3
9093+
(2 rows)
91009094

9101-
-- Connection is not closed at the end of the alter statement in
9102-
-- pgfdw_inval_callback. That's because the connection is in midst of this
9103-
-- xact, it is just marked as invalid.
9095+
-- Connections are not closed at the end of the alter and drop statements.
9096+
-- That's because the connections are in midst of this xact,
9097+
-- they are just marked as invalid in pgfdw_inval_callback.
91049098
ALTER SERVER loopback OPTIONS (ADD use_remote_estimate 'off');
91059099
DROP SERVER loopback3 CASCADE;
91069100
NOTICE: drop cascades to 2 other objects
@@ -9113,39 +9107,29 @@ SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
91139107
server_name | valid
91149108
-------------+-------
91159109
loopback | f
9116-
loopback2 | t
91179110
| f
9118-
(3 rows)
9111+
(2 rows)
91199112

9120-
-- The invalid connection gets closed in pgfdw_xact_callback during commit.
9113+
-- The invalid connections get closed in pgfdw_xact_callback during commit.
91219114
COMMIT;
9122-
-- List all the existing cached connections. loopback and loopback3
9123-
-- should not be output because they should be closed at the end of
9124-
-- the above transaction.
9125-
SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
9126-
server_name | valid
9127-
-------------+-------
9128-
loopback2 | t
9129-
(1 row)
9115+
-- All cached connections were closed while committing above xact, so no
9116+
-- records should be output.
9117+
SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1;
9118+
server_name
9119+
-------------
9120+
(0 rows)
91309121

91319122
-- =======================================================================
91329123
-- test postgres_fdw_disconnect and postgres_fdw_disconnect_all functions
91339124
-- =======================================================================
9134-
-- Return true as all cached connections are closed.
9135-
SELECT postgres_fdw_disconnect_all();
9136-
postgres_fdw_disconnect_all
9137-
-----------------------------
9138-
t
9139-
(1 row)
9140-
9125+
BEGIN;
91419126
-- Ensure to cache loopback connection.
91429127
SELECT 1 FROM ft1 LIMIT 1;
91439128
?column?
91449129
----------
91459130
1
91469131
(1 row)
91479132

9148-
BEGIN;
91499133
-- Ensure to cache loopback2 connection.
91509134
SELECT 1 FROM ft6 LIMIT 1;
91519135
?column?
@@ -9155,66 +9139,31 @@ SELECT 1 FROM ft6 LIMIT 1;
91559139

91569140
-- List all the existing cached connections. loopback and loopback2 should be
91579141
-- output.
9158-
SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
9159-
server_name | valid
9160-
-------------+-------
9161-
loopback | t
9162-
loopback2 | t
9142+
SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1;
9143+
server_name
9144+
-------------
9145+
loopback
9146+
loopback2
91639147
(2 rows)
91649148

9165-
-- Issue a warning and return false as loopback2 connection is still in use and
9149+
-- Issue a warning and return false as loopback connection is still in use and
91669150
-- can not be closed.
9167-
SELECT postgres_fdw_disconnect('loopback2');
9168-
WARNING: cannot close connection for server "loopback2" because it is still in use
9151+
SELECT postgres_fdw_disconnect('loopback');
9152+
WARNING: cannot close connection for server "loopback" because it is still in use
91699153
postgres_fdw_disconnect
91709154
-------------------------
91719155
f
91729156
(1 row)
91739157

9174-
-- Close loopback connection, return true and issue a warning as loopback2
9175-
-- connection is still in use and can not be closed.
9176-
SELECT postgres_fdw_disconnect_all();
9177-
WARNING: cannot close connection for server "loopback2" because it is still in use
9178-
postgres_fdw_disconnect_all
9179-
-----------------------------
9180-
t
9181-
(1 row)
9182-
9183-
-- List all the existing cached connections. loopback2 should be output.
9184-
SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
9185-
server_name | valid
9186-
-------------+-------
9187-
loopback2 | t
9188-
(1 row)
9189-
9190-
-- Ensure to cache loopback connection.
9191-
SELECT 1 FROM ft1 LIMIT 1;
9192-
?column?
9193-
----------
9194-
1
9195-
(1 row)
9196-
9197-
-- Ensure to cache loopback4 connection.
9198-
SELECT 1 FROM ft8 LIMIT 1;
9199-
?column?
9200-
----------
9201-
1
9202-
(1 row)
9203-
9204-
-- List all the existing cached connections. loopback, loopback2, loopback4
9205-
-- should be output.
9206-
SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
9207-
server_name | valid
9208-
-------------+-------
9209-
loopback | t
9210-
loopback2 | t
9211-
loopback4 | t
9212-
(3 rows)
9158+
-- List all the existing cached connections. loopback and loopback2 should be
9159+
-- output.
9160+
SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1;
9161+
server_name
9162+
-------------
9163+
loopback
9164+
loopback2
9165+
(2 rows)
92139166

9214-
DROP SERVER loopback4 CASCADE;
9215-
NOTICE: drop cascades to 2 other objects
9216-
DETAIL: drop cascades to user mapping for public on server loopback4
9217-
drop cascades to foreign table ft8
92189167
-- Return false as connections are still in use, warnings are issued.
92199168
-- But disable warnings temporarily because the order of them is not stable.
92209169
SET client_min_messages = 'ERROR';
@@ -9226,21 +9175,19 @@ SELECT postgres_fdw_disconnect_all();
92269175

92279176
RESET client_min_messages;
92289177
COMMIT;
9229-
-- Close loopback2 connection and return true.
9230-
SELECT postgres_fdw_disconnect('loopback2');
9231-
postgres_fdw_disconnect
9232-
-------------------------
9233-
t
9178+
-- Ensure that loopback2 connection is closed.
9179+
SELECT 1 FROM postgres_fdw_disconnect('loopback2');
9180+
?column?
9181+
----------
9182+
1
92349183
(1 row)
92359184

9236-
-- List all the existing cached connections. loopback should be output.
9237-
SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
9238-
server_name | valid
9239-
-------------+-------
9240-
loopback | t
9241-
(1 row)
9185+
SELECT server_name FROM postgres_fdw_get_connections() WHERE server_name = 'loopback2';
9186+
server_name
9187+
-------------
9188+
(0 rows)
92429189

9243-
-- Return false as loopback2 connectin is closed already.
9190+
-- Return false as loopback2 connection is closed already.
92449191
SELECT postgres_fdw_disconnect('loopback2');
92459192
postgres_fdw_disconnect
92469193
-------------------------
@@ -9250,18 +9197,17 @@ SELECT postgres_fdw_disconnect('loopback2');
92509197
-- Return an error as there is no foreign server with given name.
92519198
SELECT postgres_fdw_disconnect('unknownserver');
92529199
ERROR: server "unknownserver" does not exist
9253-
-- Close loopback connection and return true.
9254-
SELECT postgres_fdw_disconnect_all();
9255-
postgres_fdw_disconnect_all
9256-
-----------------------------
9257-
t
9200+
-- Let's ensure to close all the existing cached connections.
9201+
SELECT 1 FROM postgres_fdw_disconnect_all();
9202+
?column?
9203+
----------
9204+
1
92589205
(1 row)
92599206

9260-
-- List all the existing cached connections. No connection exists, so NULL
9261-
-- should be output.
9262-
SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
9263-
server_name | valid
9264-
-------------+-------
9207+
-- No cached connections, so no records should be output.
9208+
SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1;
9209+
server_name
9210+
-------------
92659211
(0 rows)
92669212

92679213
-- =============================================================================
@@ -9271,6 +9217,7 @@ CREATE ROLE regress_multi_conn_user1 SUPERUSER;
92719217
CREATE ROLE regress_multi_conn_user2 SUPERUSER;
92729218
CREATE USER MAPPING FOR regress_multi_conn_user1 SERVER loopback;
92739219
CREATE USER MAPPING FOR regress_multi_conn_user2 SERVER loopback;
9220+
BEGIN;
92749221
-- Will cache loopback connection with user mapping for regress_multi_conn_user1
92759222
SET ROLE regress_multi_conn_user1;
92769223
SELECT 1 FROM ft1 LIMIT 1;
@@ -9290,25 +9237,25 @@ SELECT 1 FROM ft1 LIMIT 1;
92909237

92919238
RESET ROLE;
92929239
-- Should output two connections for loopback server
9293-
SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
9294-
server_name | valid
9295-
-------------+-------
9296-
loopback | t
9297-
loopback | t
9240+
SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1;
9241+
server_name
9242+
-------------
9243+
loopback
9244+
loopback
92989245
(2 rows)
92999246

9300-
-- Close loopback connections and return true.
9301-
SELECT postgres_fdw_disconnect('loopback');
9302-
postgres_fdw_disconnect
9303-
-------------------------
9304-
t
9247+
COMMIT;
9248+
-- Let's ensure to close all the existing cached connections.
9249+
SELECT 1 FROM postgres_fdw_disconnect_all();
9250+
?column?
9251+
----------
9252+
1
93059253
(1 row)
93069254

9307-
-- List all the existing cached connections. No connection exists, so NULL
9308-
-- should be output.
9309-
SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
9310-
server_name | valid
9311-
-------------+-------
9255+
-- No cached connections, so no records should be output.
9256+
SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1;
9257+
server_name
9258+
-------------
93129259
(0 rows)
93139260

93149261
-- Clean up

0 commit comments

Comments
 (0)