Weird behavior in psql with \copy

Folks,

following example shows a weird behavior with the transaction status
identifier
in psql's prompt and the \copy command:

bernd(at)[local]:bernd #= SELECT version();
version

------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.2beta3 on x86_64-unknown-linux-gnu, compiled by GCC gcc-4.0
(GCC) 4.0.2 20050808 (prerelease) (Ubuntu 4.0.1-4ubuntu9)
(1 row)

bernd(at)[local]:bernd #= \set
AUTOCOMMIT = 'on'
PROMPT1 = '%n(at)%m:%/ %#%x%R '

[...]

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 do the COMMIT; just to show that i'm really not in a transaction.

I see the same in PostgreSQL 8.1.4, too. It looks like get_prompt() seems
to fail to set the
correct prompt because PQtransactionStatus() still reports PGASYNC_BUSY,
but
i'm not that deep into libpq to nail that really down.

--
Thanks

Bernd

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)