Re: Weird behavior in psql with \copy

Bernd Helmle <mailings(at)oopsware(dot)de> writes:
> following example shows a weird behavior with the transaction status
> identifier
> in psql's prompt and the \copy command:

> ... \set PROMPT1 '%/%R%x%# '
> bernd(at)[local]:bernd #= \copy foo TO '/home/bernd/tmp/test.dat'
> bernd(at)[local]:bernd #*= COMMIT;
> WARNING: there is no transaction in progress
> COMMIT

I looked into this a bit. The cause is that the \copy path of control
does not cycle libpq's PQgetResult() until dry, whereas PQexec() does
do so internally ... whenever it's not forced to stop by a COPY command.
I propose the attached patch.

Interestingly, the case with a SQL "COPY TO STDOUT" command does not
show this behavior. I believe that's because that path invokes
PrintNotifications(), and PQnotifies() internally eats the remaining
input buffer ... except for corner cases where there's a bufferload
boundary in the last few bytes of the backend's response string. This
doesn't really affect the validity of the proposed patch, but it does
make me wonder if backslash commands shouldn't be checking for notify
responses before they exit, too.

regards, tom lane

Index: copy.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/copy.c,v
retrieving revision 1.70
diff -c -r1.70 copy.c
*** copy.c 6 Oct 2006 17:14:00 -0000 1.70
--- copy.c 24 Nov 2006 20:54:02 -0000
***************
*** 598,603 ****
--- 598,615 ----

PQclear(result);

+ /*
+ * Make sure we have pumped libpq dry of results; else it may still be
+ * in ASYNC_BUSY state, leading to false readings in, eg, get_prompt().
+ */
+ while ((result = PQgetResult(pset.db)) != NULL)
+ {
+ success = false;
+ psql_error("\\copy: unexpected response (%d)\n",
+ PQresultStatus(result));
+ PQclear(result);
+ }
+
if (options->file != NULL)
{
if (fclose(copystream) != 0)