COPY TO regression with psql -c

Re: Zsolt Parragi

psql -c "SELECT 1; COPY t FROM STDIN;"

The Debian package tests are also tripping over this. The test case
there is (in the encoding test file, hence the weird chars):

printf '���' | psql -qc "set client_encoding='iso-8859-5'; create table t (x varchar); copy t from stdin"
SET
CREATE TABLE
unexpected COPY_IN result, aborting connection

2026-08-12 10:31:46.434 CEST [77760] ERROR: unexpected EOF on client connection with an open transaction
2026-08-12 10:31:46.434 CEST [77760] CONTEXT: COPY t, line 1
2026-08-12 10:31:46.434 CEST [77760] STATEMENT: set client_encoding='iso-8859-5'; create table t (x varchar); copy t from stdin
2026-08-12 10:31:46.434 CEST [77760] LOG: could not send data to client: Broken pipe
2026-08-12 10:31:46.434 CEST [77760] STATEMENT: set client_encoding='iso-8859-5'; create table t (x varchar); copy t from stdin
2026-08-12 10:31:46.434 CEST [77760] FATAL: terminating connection because protocol synchronization was lost

This is probably an uncommon use case, but it has a few legitimate
uses in scripts, such as:

gzip -dc data.csv.gz | psql -c 'TRUNCATE t; COPY t FROM STDIN WITH (FORMAT CSV)'

I think that's a pretty common case. This "create and copy" is another example.

Christoph

Zsolt Parragi <zsolt.parragi@percona.com> writes:

The recent COPY ... FROM STDIN improvement caused a regression in COPY
TO ... FROM STDIN when used together with psql -c: it only considers
the first statement, so the copy fails if multiple commands are
specified. A very simple example is:

Yeah, this is clearly an oversight.

I attached a proposed patch with a tap test case that showcases the issue.

I took a brief look at this. The question the code immediately raises
is "what to do if we get PSCAN_BACKSLASH?". For example, someone
might try
psql postgres -c 'select 1; \echo hello\\ select 2;'
which is syntax that'd work just fine at a command prompt. As things
stand today, we'll ship the whole string to the server, which will
throw a syntax error and do nothing. (You could imagine improving the
-c option parser to split the string into pieces and make this work
like it does at a command prompt, but that's surely not something
we'd back-patch.) Where the rubber meets the road for the current
problem is
psql postgres -c 'select 1; \echo hello\\ copy tab from stdin;'
Should we act as though we expect PGRES_COPY_IN from this? How about
psql postgres -c 'copy tab from stdin; \echo hello'
?

Thinking about it, I think it's probably a non-problem in practice:
all of these forms will result in server errors with no PGRES_COPY_IN
issued, and since these don't attempt to consume data from the rest
of the -c string, there's not really a hazard of failing to skip over
data. But I think the issue deserves explanation in a comment.

Also, I'd drop the resetPQExpBuffer(query_buf); line. That's a false
analogy: since we're not sending the string-so-far to the server,
this situation is more like "\;" than like ";", and we'd not clear
query_buf for that. It probably makes no difference right now, but
perhaps future lexer behavior would notice the difference.

On the test case: I don't love adding a new TAP script for this.
That implies spinning up a new server, making this very expensive
for the amount of actual testing it's doing. Is there a reason not
to fold this into psql/t/001_basic.pl ?

regards, tom lane

I took a brief look at this. The question the code immediately raises
is "what to do if we get PSCAN_BACKSLASH?" ...

Yeah, I forgot to mention this in the email / commit but I checked
this and decided that it's a non-issue because we don't support it. I
added a comment about it, if support for that gets implemented in the
future, we can also extend this logic.

I dropped the reset buffer call and moved the test to the btasic est -
there's no reason to keep it separate. I just keep forgetting that I
should extend existing tests instead of adding new. Attached v2.

After thinking some more about how to handle cases where we stop with
PSCAN_BACKSLASH or PSCAN_INCOMPLETE, I feel that the safest answer is
to set num_copy_from_stdin = 0 in those cases. This is consistent
with the fact that we know we won't get a PGRES_COPY_IN message,
even if there was a valid COPY FROM STDIN in the string before the
syntax error. This might prevent us from skipping following data
in cases where it'd be best to do that, but here are two arguments
against trying to do so:

* The ambition of the security patch extended only to handling
syntactically-valid cases, which these aren't. Trying to do more
leads into a guessing game, eg should we skip data after "COPY
mytable FRPM STDIN"?

* Not trying to skip data ensures that the behavior of such cases
is the same as it was before the security patch, which seems like
the right direction to err in.

So v3 attached does it like that. I also simplified the test
script. The two-COPY-commands case seems like it covers everything
we want to test; the other cases just add cycles and complicate
the script.

regards, tom lane

The changes look good to me.

The two-COPY-commands case seems like it covers everything
we want to test

It does, it's just not as realistic use case as the other two, but that's fine for a test.

Zsolt Parragi <zsolt.parragi@percona.com> writes:

The changes look good to me.

Thanks, pushed.

regards, tom lane