Interrupting a scan reads the rest of its COPY before the query ends

Open
  • Is something wrong?
#638 1 comment 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
42/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Active
Tech stack
cpp, postgres
Domain
databases

Research direction

The issue itself maps the path: on teardown of an interrupted scan, ConnectionPool::Return calls CheckConnectionHealthy and ResetConnection, which drain the in-progress COPY via libpq. Read src/ for the scan's local state and the binary/text COPY readers, plus PostgresPoolConnection/ConnectionPool::Return, and find where interrupt state is propagated into the scanner. 'Done' means an interrupted scan stops within about a round trip — a PQcancelBlocking (or marking the pooled connection invalid so the socket closes) followed by draining only the short error tail — verified with the provided repro_pg_interrupt_drain.py showing sub-second interrupt-to-next-statement for the view. Requires comfort with DuckDB extension internals and libpq, so not for a first-timer despite the excellent write-up.

Written by the indexing model from the issue text.

Description

What happens

When a query that is scanning a Postgres relation gets interrupted (con.interrupt(), Ctrl-C, or a remote
cancel), the query does not stop until Postgres has sent the whole rest of the COPY ... TO STDOUT. libpq reads
that rest and throws it away. Interrupting a scan of a large view therefore takes as long as reading the whole
view. The connection, and the DuckDB connection that runs the query, stay busy for all of that time.

A table with relpages > 0 is less affected: its ctid ranges bound the drain to pg_pages_per_task pages per
connection. A view, a table that has never been analysed (relpages = 0), and any other single-COPY scan drain
the whole relation.

Reproduce

DuckDB 2.0.0, postgres_scanner c0f5a3c0c4 (the v2.0.0 extension), Postgres 17. macOS arm64 here, but the
behaviour is platform independent.

pip install duckdb==2.0.0 'psycopg[binary]'
docker run -d --rm -p 5432:5432 -e POSTGRES_PASSWORD=pg postgres:17
python repro_pg_interrupt_drain.py postgresql://postgres:[email protected]:5432/postgres

repro_pg_interrupt_drain.py (below) makes a 20M-row table, about 1.9 GB, and a view big_v over it. Then,
for the table and for the view, it does this:

result = con.execute("SELECT * FROM pg.public.big_v")
result.fetchmany(1000)          # the first rows arrive at once
con.interrupt()
result.fetchall()               # raises INTERRUPT, but only after the drain
con.execute("SELECT 42")

Output against a local Postgres:

  big: first rows after 0.08 s; interrupt to next statement: 0.48 s
  big_v: still busy after 1 s, COPY backends on Postgres: 1
  ...
  big_v: interrupted: INTERRUPT Error: Interrupted!
  big_v: first rows after 0.03 s; interrupt to next statement: 7.41 s

The same run with 90 ms of round-trip latency between DuckDB and Postgres (a cross-region RDS):

  big_v: first rows after 0.34 s; interrupt to next statement: 19.75 s

With the link capped at about 11 MB/s, the time from the interrupt to the next statement was 70 s. While the
query waits, pg_stat_activity shows the COPY as active until its last row is sent, and the network carries
the rest of the relation at full speed.

Expected

The interrupt stops the scan within about a round trip. The COPY on the Postgres side is cancelled
(PQcancel / PQcancelBlocking), or the connection is closed instead of drained. The next statement can run
right away.

Actual

The interrupt only takes effect after the whole remaining COPY has been transferred and discarded. That takes
O(remaining bytes / bandwidth).

Where

There is no PQcancel, PQgetCancel or interrupt check anywhere in src/ or in database-connector. When
the interrupted scan's local state is destroyed, its PostgresPoolConnection goes back through
ConnectionPool::Return and runs these steps:

  1. CheckConnectionHealthy → PostgresConnection::PingServer → PQexec(conn, "SELECT 1"). The connection is
    still in PGASYNC_COPY_OUT, so PQexecStart switches it to busy and lets "the remaining COPY data be
    dropped on the floor". In practice libpq reads every remaining CopyData message.
  2. ResetConnection → PostgresConnection::Reset → PQexec("DISCARD ALL"). That would drain too, if step 1
    had not already done so.

Suggested fix: in the scan's local state (or the binary/text reader), if a COPY or query is still in progress
on teardown, send a cancel request (PQcancelBlocking, or PQcancel on older libpq) and then drain the short
error tail. Another option is to mark the PooledConnection invalid, so ReturnToPool discards it, which
closes the socket. Postgres then ends the COPY at its next write.

repro_pg_interrupt_drain.py 
"""duckdb-postgres: an interrupted scan reads the rest of its COPY before the connection is freed.

    pip install duckdb==2.0.0 psycopg[binary]
    docker run -d --rm -p 5432:5432 -e POSTGRES_PASSWORD=pg postgres:17
    python repro_pg_interrupt_drain.py postgresql://postgres:[email protected]:5432/postgres [rows]

Reads the first rows of a view (one COPY, no ctid ranges), then interrupts and cleans up the query.
The cleanup takes as long as reading the whole view: Postgres keeps sending the rest of the COPY,
and libpq reads it to the end before the connection can run anything else.
"""

import sys
import threading
import time

import duckdb
import psycopg

DSN = sys.argv[1]
ROWS = int(sys.argv[2]) if len(sys.argv) > 2 else 20_000_000


def seed():
    with psycopg.connect(DSN, autocommit=True) as pg:
        have = pg.execute("SELECT to_regclass('public.big') IS NOT NULL").fetchone()[0]
        if not have or pg.execute("SELECT count(*) FROM big").fetchone()[0] != ROWS:
            pg.execute("DROP TABLE IF EXISTS big CASCADE")
            pg.execute(
                "CREATE TABLE big AS SELECT g AS id, md5(g::text) || md5((g + 1)::text) AS pad"
                f" FROM generate_series(1, {ROWS}) g"
            )
            pg.execute("ANALYZE big")
        pg.execute("CREATE OR REPLACE VIEW big_v AS SELECT * FROM big")
        size = pg.execute("SELECT pg_size_pretty(pg_relation_size('big'))").fetchone()[0]
    print(f"big: {ROWS} rows, {size}")


def copy_backends():
    with psycopg.connect(DSN, autocommit=True) as pg:
        return pg.execute(
            "SELECT count(*) FROM pg_stat_activity WHERE query LIKE 'COPY (SELECT%big%' AND pid <> pg_backend_pid()"
        ).fetchone()[0]


def trial(relation):
    con = duckdb.connect()
    con.execute("LOAD postgres")
    con.execute(f"ATTACH '{DSN}' AS pg (TYPE postgres, READ_ONLY)")
    con.execute("SELECT 1 FROM pg.public.big LIMIT 1").fetchall()  # warm the catalog

    started = time.monotonic()
    result = con.execute(f"SELECT * FROM pg.public.{relation}")
    result.fetchmany(1000)
    first = time.monotonic() - started
    time.sleep(1)

    stopped = time.monotonic()
    con.interrupt()

    def close():
        # The next statement cleans up the interrupted one (as a client's next query would).
        try:
            result.fetchall()
        except duckdb.Error as error:
            print(f"  {relation}: interrupted: {str(error)[:60]}")
        con.execute("SELECT 42").fetchall()

    worker = threading.Thread(target=close)
    worker.start()
    while worker.is_alive():
        worker.join(1)
        if worker.is_alive():
            print(f"  {relation}: still busy after {time.monotonic() - stopped:.0f} s,"
                  f" COPY backends on Postgres: {copy_backends()}")
    print(f"  {relation}: first rows after {first:.2f} s; interrupt to next statement: {time.monotonic() - stopped:.2f} s")
    con.close()


seed()
print(duckdb.__version__, duckdb.execute(
    "SELECT extension_version FROM duckdb_extensions() WHERE extension_name = 'postgres_scanner'"
).fetchone())
trial("big")    # a table: ctid ranges of pg_pages_per_task pages each, a short COPY to drain
trial("big_v")  # a view: one COPY of the whole relation, all of it drained
Dominant language
C++
Stars
375
Forks
115
Avg merge
5h 17m
Merged PRs (30d)
38

Getting set up

This project ships no dev container, Dockerfile or contributing guide, so setting up is up to you: start from its README, and see our first-contribution guide for the general steps.

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from duckdb/duckdb-postgres

All issues in duckdb/duckdb-postgres

Similar issues

More C++ issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.