Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xRYdy-000000014uo-0ick for pgsql-bugs@arkaria.postgresql.org; Thu, 12 Nov 2026 17:31:26 +0000 Received: from localhost ([127.0.0.1]:47730 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xRYds-0000000451F-1frA for pgsql-bugs@arkaria.postgresql.org; Thu, 12 Nov 2026 17:31:20 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:34438) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xRKrB-0000000ERJl-2De2 for pgsql-bugs@lists.postgresql.org; Thu, 12 Nov 2026 02:48:09 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xRKr9-00000000TSs-3bPU for pgsql-bugs@lists.postgresql.org; Thu, 12 Nov 2026 02:48:08 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=LmbBTqDW7nSFe2jHgl+jEJ73briaARfHGfb3sXTx2C0=; b=XJHMCd33TXWWJz156m1LWPfniG 81xYzN8WOYE7bfN7iYyuzKcuKUKdPKJWYK0evzKna1/Wmg2nAp+Culdl8FB/WamV0foRaP3z/qgE8 pbOgfWtG6eXw+pmlflQ+hFxITrpWspOO7gcozX2lEpRo/7bHTdV8tIH6HQc3OgymjRu8cv9qkeLpx eeqilA90oldZEWydh67fA6Tv5yXQ2yQuUEKaZC2y4F+qODChuN141boC29kCFNyNguEwhdZ6KxQ+l hUcQyGIfIa7aQ4j6+4SAmVJiX+k7LXX6VUX2AVYP+OUKGkz+eBM7iD2T+R/ImGNkd05m0d1rzAlKR 2IL9IMJQ==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xRKr6-00000001J2K-49Sm for pgsql-bugs@lists.postgresql.org; Thu, 12 Nov 2026 02:48:07 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xRKr5-00000003Gyx-3S8R for pgsql-bugs@lists.postgresql.org; Thu, 12 Nov 2026 02:48:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19895: dblink() never returns and spins at 100% CPU when the remote command is COPY ... TO STDOUT; To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: 1165125080@qq.com Reply-To: 1165125080@qq.com, pgsql-bugs@lists.postgresql.org Date: Thu, 12 Nov 2026 02:47:16 +0000 Message-ID: <19895-7cc84a032390a6d9@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The following bug has been logged on the website: Bug reference: 19895 Logged by: ji xiaohang Email address: 1165125080@qq.com PostgreSQL version: 18.6 Operating system: centos8 Description: =20 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=3D/tmp port=3D5432 dbname=3Dpostgres user=3Dp= ostgres', '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(); -- returns t, no effect SELECT pg_terminate_backend(); -- returns t, no effect gdb shows InterruptPending =3D QueryCancelPending =3D ProcDiePending =3D 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 =3D libpqsrv_get_result(conn, ...)) !=3D 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 =3D libpqsrv_get_result(conn, dblink_we_get_result)) !=3D - 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 =3D libpqsrv_get_result(conn, dblink_we_get_result); + if (res =3D=3D NULL) + break; + + status =3D PQresultStatus(res); + if (status =3D=3D PGRES_COPY_IN || + status =3D=3D PGRES_COPY_OUT || + status =3D=3D 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 !=3D NULL) + { + remoteConn *rconn =3D getConnectionByName(conname); + + if (rconn !=3D NULL) + { + PGconn *oldconn =3D 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) =3D=3D PGRES_COPY_IN || + PQresultStatus(sinfo->cur_res) =3D=3D PGRES_COPY_OU= T || + PQresultStatus(sinfo->cur_res) =3D=3D PGRES_COPY_BO= TH) + { + PQclear(sinfo->cur_res); + sinfo->cur_res =3D NULL; + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("COPY is not supported by dblink"))); + } + if (PQresultStatus(sinfo->cur_res) =3D=3D PGRES_SINGLE_TUPL= E) {