Thread: Re: [GENERAL] \copy: unexpected response (4)

I wrote:
> Hmm, so it looks like the connection dropped and libpq failed to
> recognize that, or maybe libpq was okay but psql needs to check a bit
> more carefully here.  I'll take a look.

I could not reproduce this problem in testing, but after eyeballing
the code awhile I have a theory.  It looks like it is possible for
PQputCopyEnd to fail and leave the PGconn in COPY_IN state, but this
only happens (1) if the output buffer contained at least 8K already,
causing pqSendSome to be invoked from pqPutMsgEnd, and (2) pqSendSome
returned failure.  In that situation the loop in copy.c becomes
infinite, since there's no mechanism for getting out of COPY_IN state.
This case would be relatively difficult to trigger, but it seems to fit
all the facts, if we assume that the connection had failed for some
reason just at that point.  BTW, the "SSL renegotiation failure" bit
suggests that it could have been an OpenSSL bug not a real network
lossage, so you might want to see how up-to-date your openssl libraries
are.

Anyway, it seems to me that the most appropriate fix is to add some
code to that loop, along the lines of

    /*
     * 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(conn, _("trying to exit copy mode"));
        PQclear(result);
    }

This would cover this particular case and perhaps others as well.

            regards, tom lane

On Fri, Aug 7, 2009 at 12:33 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
> BTW, the "SSL renegotiation failure" bit
> suggests that it could have been an OpenSSL bug not a real network
> lossage, so you might want to see how up-to-date your openssl libraries
> are.

Thanks for your comments, Tom.  The operation seems more reliable if I
move the data to the server and do it across a local connection, which
I presume does not involve SSL, so that may be the weak link as you
surmise.  Would you expect the SSL library problem more likely to be
on the server or the client, or is it just hard to say?  Does either
of them have a facility that exposes the SSL version information or do
I have to go to the OS for that?  Incidentally, I have not experienced
any sort of instability in my interactive sessions over an SSL
connection, so is it related to the \copy operation itself, the higher
volume of data across the connection, or the fact that I am asking it
to do multiple \copies in rapid succession, would you say?  I expect
it's hard to say definitively, but maybe you or someone else can say
something about likelihoods.  I appreciate the information.

Neil Best <nbest@ci.uchicago.edu> writes:
> On Fri, Aug 7, 2009 at 12:33 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
>> BTW, the "SSL renegotiation failure" bit
>> suggests that it could have been an OpenSSL bug not a real network
>> lossage, so you might want to see how up-to-date your openssl libraries
>> are.

> Thanks for your comments, Tom.  The operation seems more reliable if I
> move the data to the server and do it across a local connection, which
> I presume does not involve SSL, so that may be the weak link as you
> surmise.  Would you expect the SSL library problem more likely to be
> on the server or the client, or is it just hard to say?

You're talking like you've found this to be repeatable.  Is it?

            regards, tom lane