Thread: psql COPY vs. ON_ERROR_ROLLBACK, multi-command strings

It has bothered me that psql's \copy ignores the ON_ERROR_ROLLBACK setting.
Only SendQuery() takes note of ON_ERROR_ROLLBACK, and \copy, like all
backslash commands, does not route through SendQuery().  Looking into this
turned up several other weaknesses in psql's handling of COPY.  For example,
SendQuery() does not release the savepoint after an ordinary COPY:

[local] test=# set log_statement = 'all'; set client_min_messages = 'log'; copy (select 0) to stdout;
SET
SET
LOG:  statement: RELEASE pg_psql_temporary_savepoint
LOG:  statement: SAVEPOINT pg_psql_temporary_savepoint
LOG:  statement: copy (select 0) to stdout;
0

When psql sends a command string containing more than one command, at least
one of which is a COPY, we stop processing results at the first COPY:

[local] test=# set log_statement = 'all'; set client_min_messages = 'log'; copy (select 0) to stdout\; select 1/0;
select1; 
SET
SET
LOG:  statement: RELEASE pg_psql_temporary_savepoint
LOG:  statement: SAVEPOINT pg_psql_temporary_savepoint
LOG:  statement: copy (select 0) to stdout; select 1/0;
0
LOG:  statement: select 1;
ERROR:  current transaction is aborted, commands ignored until end of transaction block

To make the above work normally, this patch improves SendQuery()-based COPY
command handling to process the entire queue of results whenever we've
processed a COPY.  It also brings sensible handling in the face of multiple
COPY commands in a single command string.  See the included test cases for
some examples.

We must prepare for COPY to fail for a local reason, like client-side malloc()
failure or network I/O problems.  The server will still have the connection in
a COPY mode, and we must get it out of that mode.  The \copy command was
prepared for the COPY FROM case with this code block:

    /*
     * 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));
        /* if still in COPY IN state, try to get out of it */
        if (PQresultStatus(result) == PGRES_COPY_IN)
            PQputCopyEnd(pset.db, _("trying to exit copy mode"));
        PQclear(result);
    }

It arose from these threads:
http://archives.postgresql.org/pgsql-hackers/2006-11/msg00694.php
http://archives.postgresql.org/pgsql-general/2009-08/msg00268.php

However, psql still enters an infinite loop when COPY TO STDOUT encounters a
client-side failure, such as malloc() failure.  I've merged the above
treatment into lower-level routines, granting plain COPY commands similar
benefits, and fixed the COPY TO handling.  To help you test the corner cases
involved here, I've attached a gdb script that will inject client side
failures into both kinds of COPY commands.  Attach gdb to your psql process,
source the script, and compare the behavior of commands like these with and
without the patch:

\copy (select 0) to pstdout

create table t (c int);
\copy t from stdin
1
\.

With plain COPY handled thoroughly, I fixed \copy by having it override the
source or destination stream, then call SendQuery().  This gets us support for
ON_ERROR_ROLLBACK, \timing, \set ECHO and \set SINGLESTEP for free.  I found
it reasonable to treat \copy's SQL commmand more like a user-entered command
than an internal command, because \copy is such a thin wrapper around COPY
FROM STDIN/COPY TO STDOUT.

This patch makes superfluous the second argument of PSQLexec(), but I have not
removed that argument.

Incidentally, psql's common.c had several switches on PQresultStatus(res) that
did not include a case for PGRES_COPY_BOTH and also silently assumed any
unlisted status was some kind of error.  I tightened these to distinguish all
known statuses and give a diagnostic upon receiving an unknown status.

Thanks,
nm

Excerpts from Noah Misch's message of sáb ene 14 12:40:02 -0300 2012:
> It has bothered me that psql's \copy ignores the ON_ERROR_ROLLBACK setting.
> Only SendQuery() takes note of ON_ERROR_ROLLBACK, and \copy, like all
> backslash commands, does not route through SendQuery().  Looking into this
> turned up several other weaknesses in psql's handling of COPY.

Interesting.

Committed, thanks.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Wed, Jan 25, 2012 at 06:25:46PM -0300, Alvaro Herrera wrote:
> Excerpts from Noah Misch's message of s??b ene 14 12:40:02 -0300 2012:
> > It has bothered me that psql's \copy ignores the ON_ERROR_ROLLBACK setting.
> > Only SendQuery() takes note of ON_ERROR_ROLLBACK, and \copy, like all
> > backslash commands, does not route through SendQuery().  Looking into this
> > turned up several other weaknesses in psql's handling of COPY.
>
> Interesting.
>
> Committed, thanks.

Thanks.  While testing a crashing function, I noticed that my above patch
added some noise to psql output when the server crashes:

[local] test=# select crashme();
The connection to the server was lost. Attempting reset: Failed.
The connection to the server was lost. Attempting reset: Failed.
unexpected transaction status (4)
Time: 6.681 ms
 !> \q

Status 4 is PQTRANS_UNKNOWN, which is expected when the connection is not
CONNECTION_OK.  The double message arrives because ProcessResult() now calls
CheckConnection() at least twice, for the benefit of COPY.  (Incidentally, the
reconnect fails because the server has not yet finished recovering; that part
is nothing new.)

The attached small patch has SendQuery() keep quiet about PQTRANS_UNKNOWN when
the connection is down.  It makes ProcessResult() skip the second
CheckConnection() when the command string had no COPY results.  This restores
the pre-08146775acd8bfe0fcc509c71857abb928697171 psql output:

[local] test=# select crashme();
The connection to the server was lost. Attempting reset: Failed.
Time: 4.798 ms
 !> \q

Thanks,
nm

On Sat, Feb 25, 2012 at 12:57 AM, Noah Misch <noah@leadboat.com> wrote:
> Thanks.  While testing a crashing function, I noticed that my above patch
> added some noise to psql output when the server crashes:
>
> [local] test=# select crashme();
> The connection to the server was lost. Attempting reset: Failed.
> The connection to the server was lost. Attempting reset: Failed.
> unexpected transaction status (4)
> Time: 6.681 ms
>  !> \q
>
> Status 4 is PQTRANS_UNKNOWN, which is expected when the connection is not
> CONNECTION_OK.  The double message arrives because ProcessResult() now calls
> CheckConnection() at least twice, for the benefit of COPY.  (Incidentally, the
> reconnect fails because the server has not yet finished recovering; that part
> is nothing new.)
>
> The attached small patch has SendQuery() keep quiet about PQTRANS_UNKNOWN when
> the connection is down.  It makes ProcessResult() skip the second
> CheckConnection() when the command string had no COPY results.  This restores
> the pre-08146775acd8bfe0fcc509c71857abb928697171 psql output:
>
> [local] test=# select crashme();
> The connection to the server was lost. Attempting reset: Failed.
> Time: 4.798 ms
>  !> \q

Committed, but... why do we EVER need to call CheckConnection() at the
bottom of ProcessResult(), after exiting that function's main loop?  I
don't see any way to get out of that loop without first calling
AcceptResult on every PGresult we've seen, and that function already
calls CheckConnection() if any failure is observed.

As a side note, the documentation for PQexec() is misleading about
what will happen if COPY is present in a multi-command string.  It
says: "Note however that the returned PGresult structure describes
only the result of the last command executed from the string. Should
one of the commands fail, processing of the string stops with it and
the returned PGresult describes the error condition.  It does not
explain that it also stops if it hits a COPY.  I had to read the
source code for libpq to understand why this psql logic was coded the
way it is.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, Mar 07, 2012 at 04:57:12PM -0500, Robert Haas wrote:
> On Sat, Feb 25, 2012 at 12:57 AM, Noah Misch <noah@leadboat.com> wrote:
> > Thanks. ?While testing a crashing function, I noticed that my above patch
> > added some noise to psql output when the server crashes:
> >
> > [local] test=# select crashme();
> > The connection to the server was lost. Attempting reset: Failed.
> > The connection to the server was lost. Attempting reset: Failed.
> > unexpected transaction status (4)
> > Time: 6.681 ms
> > ?!> \q
> >
> > Status 4 is PQTRANS_UNKNOWN, which is expected when the connection is not
> > CONNECTION_OK. ?The double message arrives because ProcessResult() now calls
> > CheckConnection() at least twice, for the benefit of COPY. ?(Incidentally, the
> > reconnect fails because the server has not yet finished recovering; that part
> > is nothing new.)
> >
> > The attached small patch has SendQuery() keep quiet about PQTRANS_UNKNOWN when
> > the connection is down. ?It makes ProcessResult() skip the second
> > CheckConnection() when the command string had no COPY results. ?This restores
> > the pre-08146775acd8bfe0fcc509c71857abb928697171 psql output:
> >
> > [local] test=# select crashme();
> > The connection to the server was lost. Attempting reset: Failed.
> > Time: 4.798 ms
> > ?!> \q
> 
> Committed, but... why do we EVER need to call CheckConnection() at the
> bottom of ProcessResult(), after exiting that function's main loop?  I
> don't see any way to get out of that loop without first calling
> AcceptResult on every PGresult we've seen, and that function already
> calls CheckConnection() if any failure is observed.

You can reproduce a case where it helps by sending SIGKILL to a backend
serving a long-running COPY TO, such as this:

copy (select n, pg_sleep(case when n > 1000 then 1 else 0 end)     from generate_series(1,1030) t(n)) to stdout;

The connection dies during handleCopyOut().  By the time control returns to
ProcessResult(), there's no remaining PGresult to check.  Only that last-ditch
CheckConnection() notices the lost connection.

[I notice more examples of poor error reporting cosmetics in some of these
COPY corner cases, so I anticipate another patch someday.]

> As a side note, the documentation for PQexec() is misleading about
> what will happen if COPY is present in a multi-command string.  It
> says: "Note however that the returned PGresult structure describes
> only the result of the last command executed from the string. Should
> one of the commands fail, processing of the string stops with it and
> the returned PGresult describes the error condition.  It does not
> explain that it also stops if it hits a COPY.  I had to read the
> source code for libpq to understand why this psql logic was coded the
> way it is.

Agreed; I went through a similar process.  Awhile after reading the code, I
found the behavior documented in section "Functions Associated with the COPY
Command":
 If a COPY command is issued via PQexec in a string that could contain additional commands, the application must
continuefetching results via PQgetResult after completing the COPY sequence. Only when PQgetResult returns NULL is it
certainthat the PQexec command string is done and it is safe to issue more commands.
 

I'm not quite sure what revision would help most here -- a cross reference,
moving that content, duplicating that content.  Offhand, I'm inclined to move
it to the PQexec() documentation with some kind of reference back from its
original location.  Thoughts?

Thanks,
nm

Excerpts from Noah Misch's message of jue mar 08 12:11:37 -0300 2012:
>
> On Wed, Mar 07, 2012 at 04:57:12PM -0500, Robert Haas wrote:

> > As a side note, the documentation for PQexec() is misleading about
> > what will happen if COPY is present in a multi-command string.  It
> > says: "Note however that the returned PGresult structure describes
> > only the result of the last command executed from the string. Should
> > one of the commands fail, processing of the string stops with it and
> > the returned PGresult describes the error condition.  It does not
> > explain that it also stops if it hits a COPY.  I had to read the
> > source code for libpq to understand why this psql logic was coded the
> > way it is.
>
> Agreed; I went through a similar process.  Awhile after reading the code, I
> found the behavior documented in section "Functions Associated with the COPY
> Command":
>
>   If a COPY command is issued via PQexec in a string that could contain
>   additional commands, the application must continue fetching results via
>   PQgetResult after completing the COPY sequence. Only when PQgetResult
>   returns NULL is it certain that the PQexec command string is done and it is
>   safe to issue more commands.
>
> I'm not quite sure what revision would help most here -- a cross reference,
> moving that content, duplicating that content.  Offhand, I'm inclined to move
> it to the PQexec() documentation with some kind of reference back from its
> original location.  Thoughts?

I would vote for moving it and adding a reference in the COPY functions
section.  That way, the PQexec doc is complete by itself without having
to duplicate anything.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support