BUG #19895: dblink() never returns and spins at 100% CPU when the remote command is COPY ... TO STDOUT;

The following bug has been logged on the website:

Bug reference: 19895
Logged by: ji xiaohang
Email address: 1165125080(at)qq(dot)com
PostgreSQL version: 18.6
Operating system: centos8
Description:

Calling dblink() with a remote command that starts a COPY (e.g. COPY ... TO
STDOUT) makes the calling backend loop forever. The backend remains in
state
'active' with wait_event NULL and keeps one CPU core at 100%. The remote
end
of the connection has already finished the COPY and is idle; the hang is
entirely client-side.

pg_cancel_backend() and pg_terminate_backend() both return true but have no
effect. The backend can only be removed by killing it at the OS level (or
restarting the instance). While stuck, it holds an old snapshot for the
lifetime of the hang, preventing vacuum from cleaning up recently-dead
tuples.

Reproduction
------------
1) CREATE EXTENSION dblink;

2) SELECT * FROM dblink('host=/tmp port=5432 dbname=postgres user=postgres',
'COPY (select oid, relname from pg_class limit 3)
TO STDOUT WITH CSV')
AS t(a text);

The statement never returns. The remote server can be a different instance;
a self-connection is not required. The remote session's last query is the
COPY command and it is already idle.

From another session, while the backend spins:
SELECT pg_cancel_backend(<pid>); -- returns t, no effect
SELECT pg_terminate_backend(<pid>); -- returns t, no effect
gdb shows InterruptPending = QueryCancelPending = ProcDiePending = 1 while
the process continues to spin at ~100% CPU.

Expected behavior
-----------------
A clean error, e.g. "COPY is not supported by dblink", and a backend that
remains cancellable. (Handling COPY data is probably out of scope for
dblink, but hanging and ignoring interrupts is clearly wrong.)

Analysis
--------
* The remote COPY command puts libpq into PGASYNC_COPY_OUT. In this state
PQgetResult() never returns NULL: getCopyResult() keeps manufacturing an
empty PGresult with the COPY status, and pqParseInput3() returns early for
non-BUSY states without consuming the buffered CopyData/CopyDone messages.
* dblink's synchronous result loop (storeQueryResult()) is a "for(;;)" that
only terminates when PQgetResult() returns NULL, so it spins at 100% CPU
forever.
* When a cancel/terminate arrives, CHECK_FOR_INTERRUPTS() in
storeQueryResult()
raises an ERROR, which is caught by materializeQueryResult()'s PG_CATCH
cleanup:
while ((res = libpqsrv_get_result(conn, ...)) != NULL)
PQclear(res);
In a COPY state this loop never terminates either, and it runs entirely
inside libpq without any CHECK_FOR_INTERRUPTS(). The backend therefore
spins in user space forever, and pending SIGINT/SIGTERM are never
processed: this is why cancel/terminate appear to succeed but do nothing.
* GDB stack of a stuck backend (16.9 build):
#0 pqGetInt / pqParseInput3
#1 parseInput
#2 PQgetResult -> getCopyResult -> PQmakeEmptyPGresult(PGRES_COPY_OUT)
#3 libpqsrv_get_result
#4 materializeQueryResult (PG_CATCH cleanup loop)
#5 dblink_record_internal

Proposed fix (patch attached; also inline below)
------------------------------------------------
1) In storeQueryResult(), if a result has status PGRES_COPY_IN,
PGRES_COPY_OUT
or PGRES_COPY_BOTH, clear it and raise ERRCODE_FEATURE_NOT_SUPPORTED
("COPY is not supported by dblink"). The error path then discards the
connection, which is still in COPY state and cannot be reused without
consuming a possibly unbounded COPY stream.
2) Make the PG_CATCH cleanup loop in materializeQueryResult() defensive:
stop draining if a COPY status is still encountered and drop the
connection (named connection: remove it from remoteConnHash and close it;
unnamed connection: dblink_record_internal()'s PG_FINALLY closes it).
This guarantees that the error path can never loop forever, regardless of
where the interrupt/error arrived.

--- a/contrib/dblink/dblink.c
+++ b/contrib/dblink/dblink.c
@@ materializeQueryResult PG_CATCH
- /* and clear out any pending data in libpq */
- while ((res = libpqsrv_get_result(conn,
dblink_we_get_result)) !=
- NULL)
- PQclear(res);
+ /*
+ * not guaranteed to terminate if the connection is stuck in
a COPY
+ * state: PQgetResult() will then return empty PGRES_COPY_*
results
+ * forever instead of NULL. Normally storeQueryResult() has
already
+ * prevented that by erroring out as soon as it sees a COPY
result,
+ * but be defensive here: drop the connection rather than
spin.
+ */
+ for (;;)
+ {
+ ExecStatusType status;
+
+ res = libpqsrv_get_result(conn,
dblink_we_get_result);
+ if (res == NULL)
+ break;
+
+ status = PQresultStatus(res);
+ if (status == PGRES_COPY_IN ||
+ status == PGRES_COPY_OUT ||
+ status == PGRES_COPY_BOTH)
+ {
+ PQclear(res);
+
+ /*
+ * If this is a named connection, remove it
from the hash and
+ * close it; it cannot be used for anything
anymore. Unnamed
+ * connections are closed by
dblink_record_internal()'s
+ * PG_FINALLY block.
+ */
+ if (conname != NULL)
+ {
+ remoteConn *rconn =
getConnectionByName(conname);
+
+ if (rconn != NULL)
+ {
+ PGconn *oldconn =
rconn->conn;
+
+ deleteConnection(conname);
+
libpqsrv_disconnect(oldconn);
+ }
+ }
+ break;
+ }
+ PQclear(res);
+ }
PG_RE_THROW();

@@ storeQueryResult
if (!sinfo->cur_res)
break;

+ /*
+ * dblink cannot handle COPY. Besides having no way to
return the
+ * COPY data, leaving the connection in a COPY state is
dangerous:
+ * while in that state, PQgetResult() never returns NULL but
keeps
+ * manufacturing empty results with the COPY status, so a
loop that
+ * runs until PQgetResult() returns NULL (such as this one,
or the
+ * cleanup loop in materializeQueryResult()) would spin
forever and
+ * peg the backend at 100% CPU. Error out immediately; the
cleanup
+ * code will discard the connection.
+ */
+ if (PQresultStatus(sinfo->cur_res) == PGRES_COPY_IN ||
+ PQresultStatus(sinfo->cur_res) == PGRES_COPY_OUT ||
+ PQresultStatus(sinfo->cur_res) == PGRES_COPY_BOTH)
+ {
+ PQclear(sinfo->cur_res);
+ sinfo->cur_res = NULL;
+ ereport(ERROR,
+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("COPY is not supported by
dblink")));
+ }
+
if (PQresultStatus(sinfo->cur_res) == PGRES_SINGLE_TUPLE)
{