pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 1165125080@qq.com
Subject: BUG #19895: dblink() never returns and spins at 100% CPU when the remote  command is COPY ... TO STDOUT;
Date: Thu, 12 Nov 2026 02:47:16 +0000
Message-ID: <19895-7cc84a032390a6d9@postgresql.org> (raw)

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:        

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.

inline patch 1: contrib/dblink/dblink.c (+0/-2 lines) download inline-1.patch--- 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) {

Message-ID: <19895-7cc84a032390a6d9@postgresql.org>
Permalink:  ../19895-7cc84a032390a6d9@postgresql.org/
Also on:    postgresql.org/message-id/19895-7cc84a032390a6d9@postgresql.org

 ·  · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, 1165125080@qq.com
  Subject: Re: BUG #19895: dblink() never returns and spins at 100% CPU when the remote  command is COPY ... TO STDOUT;
  In-Reply-To: <19895-7cc84a032390a6d9@postgresql.org>
  References: <19895-7cc84a032390a6d9@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox