v01-0001-Forbid-usage-of-COPY-in-a-pipeline.patch
application/octet-stream
Filename: v01-0001-Forbid-usage-of-COPY-in-a-pipeline.patch
Type: application/octet-stream
Part: 0
From c89781841d72386c433f9ef3dcab524ad822a303 Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
Date: Wed, 11 Jun 2025 15:48:10 +0200
Subject: Forbid usage of COPY in a pipeline
Running COPY within a pipeline can break protocol synchronisation in multiple
ways.
- In copy mode, the backend process ignores additional Sync messages and
won't send the matching ReadyForQuery expected by the frontend. Doing a
\syncpipeline just after COPY will leave the frontend waiting for a
ReadyForQuery message that won't be sent.
- libq automatically sends a Sync with the Copy message which isn't
tracked in the command queue, creating an unexpected synchronisation point. A
failure during copy will leave the pipeline in an aborted state while the
backend would be in a clean state, ready to process commands.
Fixing those issues would require modifications in how libpq handle
pipeline and COPY. Since this is a new feature and the use case of COPY
in a pipeline has limited usefulness, it was decided to forbid the use
of COPY within a pipeline to avoid possible break of protocol
synchronisation.
---
src/bin/psql/common.c | 27 ++-
src/bin/psql/t/001_basic.pl | 27 ++-
src/test/regress/expected/psql_pipeline.out | 188 +-------------------
src/test/regress/sql/psql_pipeline.sql | 100 -----------
4 files changed, 32 insertions(+), 310 deletions(-)
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 47352b7faed..82ce59fb061 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1867,18 +1867,27 @@ ExecQueryAndProcessResults(const char *query,
{
FILE *copy_stream = NULL;
- if (pset.piped_syncs > 1)
+ if (PQpipelineStatus(pset.db) != PQ_PIPELINE_OFF)
{
/*
- * When reading COPY data, the backend ignores sync messages
- * and will not send a matching ReadyForQuery response. Even
- * if we adjust piped_syncs and requested_results, it is not
- * possible to salvage this as the sync message would still be
- * in libpq's command queue and we would be stuck in a busy
- * pipeline state. Thus, we abort the connection to avoid
- * this state.
+ * Running COPY within a pipeline can break protocol
+ * synchronisation in multiple ways.
+ *
+ * While in copy mode, the backend process ignores additional
+ * Sync messages and won't send the matching ReadyForQuery
+ * expected by the frontend
+ *
+ * libq also automatically sends a Sync with the Copy message,
+ * creating an unexpected synchronisation point. A failure
+ * during copy would leave the pipeline in an aborted state
+ * while the backend would be in a clean state, ready to
+ * process commands.
+ *
+ * Fixing those issues would require modifications in how
+ * libpq handle pipeline and COPY. For the time being, we
+ * forbid the use of COPY within a pipeline.
*/
- pg_log_info("\\syncpipeline after COPY is not supported, aborting connection");
+ pg_log_info("COPY in a pipeline is not supported, aborting connection");
exit(EXIT_BADCONN);
}
diff --git a/src/bin/psql/t/001_basic.pl b/src/bin/psql/t/001_basic.pl
index ae5c1d66405..beab3d7e179 100644
--- a/src/bin/psql/t/001_basic.pl
+++ b/src/bin/psql/t/001_basic.pl
@@ -483,8 +483,8 @@ psql_like($node, "copy (values ('foo'),('bar')) to stdout \\g | $pipe_cmd",
my $c4 = slurp_file($g_file);
like($c4, qr/foo.*bar/s);
-# Tests with pipelines. These trigger FATAL failures in the backend,
-# so they cannot be tested via SQL.
+# Tests COPY within pipelines. Those abort the connection from
+# the frontend so they cannot be tested via SQL.
$node->safe_psql('postgres', 'CREATE TABLE psql_pipeline()');
my $log_location = -s $node->logfile;
psql_fails_like(
@@ -495,8 +495,8 @@ SELECT 'val1';
\\syncpipeline
\\getresults
\\endpipeline},
- qr/server closed the connection unexpectedly/,
- 'protocol sync loss in pipeline: direct COPY, SELECT, sync and getresult'
+ qr/COPY in a pipeline is not supported, aborting connection/,
+ 'COPY in pipeline: direct COPY, SELECT, sync and getresult'
);
$node->wait_for_log(
qr/FATAL: .*terminating connection because protocol synchronization was lost/,
@@ -510,8 +510,8 @@ SELECT 'val1' \\bind \\sendpipeline
\\syncpipeline
\\getresults
\\endpipeline},
- qr/server closed the connection unexpectedly/,
- 'protocol sync loss in pipeline: bind COPY, SELECT, sync and getresult');
+ qr/COPY in a pipeline is not supported, aborting connection/,
+ 'COPY in pipeline: bind COPY, SELECT, sync and getresult');
# This time, test without the \getresults and \syncpipeline.
psql_fails_like(
@@ -520,26 +520,25 @@ psql_fails_like(
COPY psql_pipeline FROM STDIN;
SELECT 'val1';
\\endpipeline},
- qr/server closed the connection unexpectedly/,
- 'protocol sync loss in pipeline: COPY, SELECT and sync');
+ qr/COPY in a pipeline is not supported, aborting connection/,
+ 'COPY in pipeline: COPY, SELECT and sync');
-# Tests sending a sync after a COPY TO/FROM. These abort the connection
-# from the frontend.
+# Tests sending a sync after a COPY TO/FROM.
psql_fails_like(
$node,
qq{\\startpipeline
COPY psql_pipeline FROM STDIN;
\\syncpipeline
\\endpipeline},
- qr/\\syncpipeline after COPY is not supported, aborting connection/,
- 'sending sync after COPY FROM');
+ qr/COPY in a pipeline is not supported, aborting connection/,
+ 'COPY in pipeline: sending sync after COPY FROM');
psql_fails_like(
$node,
qq{\\startpipeline
COPY psql_pipeline TO STDOUT;
\\syncpipeline
\\endpipeline},
- qr/\\syncpipeline after COPY is not supported, aborting connection/,
- 'sending sync after COPY TO');
+ qr/COPY in a pipeline is not supported, aborting connection/,
+ 'COPY in pipeline: sending sync after COPY TO');
done_testing();
diff --git a/src/test/regress/expected/psql_pipeline.out b/src/test/regress/expected/psql_pipeline.out
index a30dec088b9..e78e6bfa0ad 100644
--- a/src/test/regress/expected/psql_pipeline.out
+++ b/src/test/regress/expected/psql_pipeline.out
@@ -228,192 +228,6 @@ BEGIN \bind \sendpipeline
INSERT INTO psql_pipeline VALUES ($1) \bind 1 \sendpipeline
COMMIT \bind \sendpipeline
\endpipeline
--- COPY FROM STDIN
--- with \sendpipeline and \bind
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-COPY psql_pipeline FROM STDIN \bind \sendpipeline
-\endpipeline
- ?column?
-----------
- val1
-(1 row)
-
--- with semicolon
-\startpipeline
-SELECT 'val1';
-COPY psql_pipeline FROM STDIN;
-\endpipeline
- ?column?
-----------
- val1
-(1 row)
-
--- COPY FROM STDIN with \flushrequest + \getresults
--- with \sendpipeline and \bind
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-COPY psql_pipeline FROM STDIN \bind \sendpipeline
-\flushrequest
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-message type 0x5a arrived from server while idle
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-COPY psql_pipeline FROM STDIN;
-\flushrequest
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-message type 0x5a arrived from server while idle
-\endpipeline
--- COPY FROM STDIN with \syncpipeline + \getresults
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-COPY psql_pipeline FROM STDIN \bind \sendpipeline
-\syncpipeline
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-COPY psql_pipeline FROM STDIN;
-\syncpipeline
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-\endpipeline
--- COPY TO STDOUT
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-copy psql_pipeline TO STDOUT \bind \sendpipeline
-\endpipeline
- ?column?
-----------
- val1
-(1 row)
-
-1 \N
-2 test2
-20 test2
-3 test3
-30 test3
-4 test4
-40 test4
--- with semicolon
-\startpipeline
-SELECT 'val1';
-copy psql_pipeline TO STDOUT;
-\endpipeline
- ?column?
-----------
- val1
-(1 row)
-
-1 \N
-2 test2
-20 test2
-3 test3
-30 test3
-4 test4
-40 test4
--- COPY TO STDOUT with \flushrequest + \getresults
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-copy psql_pipeline TO STDOUT \bind \sendpipeline
-\flushrequest
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-1 \N
-2 test2
-20 test2
-3 test3
-30 test3
-4 test4
-40 test4
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-copy psql_pipeline TO STDOUT;
-\flushrequest
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-1 \N
-2 test2
-20 test2
-3 test3
-30 test3
-4 test4
-40 test4
-\endpipeline
--- COPY TO STDOUT with \syncpipeline + \getresults
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-copy psql_pipeline TO STDOUT \bind \sendpipeline
-\syncpipeline
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-1 \N
-2 test2
-20 test2
-3 test3
-30 test3
-4 test4
-40 test4
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-copy psql_pipeline TO STDOUT;
-\syncpipeline
-\getresults
- ?column?
-----------
- val1
-(1 row)
-
-1 \N
-2 test2
-20 test2
-3 test3
-30 test3
-4 test4
-40 test4
-\endpipeline
-- Use \parse and \bind_named
\startpipeline
SELECT $1 \parse ''
@@ -740,7 +554,7 @@ SELECT COUNT(*) FROM psql_pipeline \bind \sendpipeline
count
-------
- 7
+ 1
(1 row)
-- After an error, pipeline is aborted and requires \syncpipeline to be
diff --git a/src/test/regress/sql/psql_pipeline.sql b/src/test/regress/sql/psql_pipeline.sql
index 16e1e1e84cd..5945eca1ef7 100644
--- a/src/test/regress/sql/psql_pipeline.sql
+++ b/src/test/regress/sql/psql_pipeline.sql
@@ -105,106 +105,6 @@ INSERT INTO psql_pipeline VALUES ($1) \bind 1 \sendpipeline
COMMIT \bind \sendpipeline
\endpipeline
--- COPY FROM STDIN
--- with \sendpipeline and \bind
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-COPY psql_pipeline FROM STDIN \bind \sendpipeline
-\endpipeline
-2 test2
-\.
--- with semicolon
-\startpipeline
-SELECT 'val1';
-COPY psql_pipeline FROM STDIN;
-\endpipeline
-20 test2
-\.
-
--- COPY FROM STDIN with \flushrequest + \getresults
--- with \sendpipeline and \bind
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-COPY psql_pipeline FROM STDIN \bind \sendpipeline
-\flushrequest
-\getresults
-3 test3
-\.
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-COPY psql_pipeline FROM STDIN;
-\flushrequest
-\getresults
-30 test3
-\.
-\endpipeline
-
--- COPY FROM STDIN with \syncpipeline + \getresults
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-COPY psql_pipeline FROM STDIN \bind \sendpipeline
-\syncpipeline
-\getresults
-4 test4
-\.
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-COPY psql_pipeline FROM STDIN;
-\syncpipeline
-\getresults
-40 test4
-\.
-\endpipeline
-
--- COPY TO STDOUT
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-copy psql_pipeline TO STDOUT \bind \sendpipeline
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-copy psql_pipeline TO STDOUT;
-\endpipeline
-
--- COPY TO STDOUT with \flushrequest + \getresults
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-copy psql_pipeline TO STDOUT \bind \sendpipeline
-\flushrequest
-\getresults
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-copy psql_pipeline TO STDOUT;
-\flushrequest
-\getresults
-\endpipeline
-
--- COPY TO STDOUT with \syncpipeline + \getresults
--- with \bind and \sendpipeline
-\startpipeline
-SELECT $1 \bind 'val1' \sendpipeline
-copy psql_pipeline TO STDOUT \bind \sendpipeline
-\syncpipeline
-\getresults
-\endpipeline
--- with semicolon
-\startpipeline
-SELECT 'val1';
-copy psql_pipeline TO STDOUT;
-\syncpipeline
-\getresults
-\endpipeline
-
-- Use \parse and \bind_named
\startpipeline
SELECT $1 \parse ''
--
2.49.0