getting pq: unexpected message 'E'; expected ReadyForQuery error

[sameervitian]

for the sake of performance we use prepare statements. At the time when application starts we prepare all the known select statements and keep on reusing those statement to issue queries.
But recently we have observed that there are following errors in our log files and these errors are proportional to traffic.

pq: unexpected message 'E'; expected ReadyForQuery

what is there that is triggering this issue ?

[ironecally]

found this
`// Main logic is here: receive messages from the postgres backend, forward
// notifications and query replies and keep the internal state in sync with the
// protocol state. Returns when the connection has been lost, is about to go
// away or should be discarded because we couldn't agree on the state with the
// server backend.
func (l *ListenerConn) listenerConnLoop() (err error) {
defer errRecover(&err)

for {
    t, r, err := l.cn.recvMessage()
    if err != nil {
        return err
    }

    switch t {
    case 'A':
        // recvNotification copies all the data so we don't need to worry
        // about the scratch buffer being overwritten.
        l.notificationChan <- recvNotification(r)

    case 'E':
        // We might receive an ErrorResponse even when not in a query; it
        // is expected that the server will close the connection after
        // that, but we should make sure that the error we display is the
        // one from the stray ErrorResponse, not io.ErrUnexpectedEOF.
        if !l.setState(connStateExpectReadyForQuery) {
            return parseError(r)
        }
        l.replyChan <- message{t, parseError(r)}

    case 'C', 'I':
        if !l.setState(connStateExpectReadyForQuery) {
            // protocol out of sync
            return fmt.Errorf("unexpected CommandComplete")
        }
        // ExecSimpleQuery doesn't need to know about this message

    case 'Z':
        if !l.setState(connStateIdle) {
            // protocol out of sync
            return fmt.Errorf("unexpected ReadyForQuery")
        }
        l.replyChan <- message{t, nil}

    case 'N', 'S':
        // ignore
    default:
        return fmt.Errorf("unexpected message %q from server in listenerConnLoop", t)
    }
}

panic("not reached")

}`
in https://searchcode.com/codesearch/view/93198139/
might help?

[johto]

But recently we have observed that there are following errors in our log files and these errors are proportional to traffic.

pq: unexpected message 'E'; expected ReadyForQuery

what is there that is triggering this issue ?

Any chance you could do a tcpdump and see what's in that 'E' message?

[jilongs]

Any updates on this issue?

[iamdhrooov]

Facing the same issue when we are reading from replica servers. Any help would be great.

[maxatome]

Just encountered this error. Resolved by using db.SetConnMaxLifetime(x) with x < to the server idle timeout.

[Al2Klimov]

For the record:

A frontend must be prepared to accept ErrorResponse and NoticeResponse messages whenever it is expecting any other type of message. See also Section 55.2.7 concerning messages that the backend might generate due to outside events.

– https://www.postgresql.org/docs/current/protocol-flow.html

[Al2Klimov]

conn#readReadyForQuery()'s structure and usages sound somewhat like just fast-forward this necessary ready-for-query thing so whatever follows can proceed from expected position in TCP stream. Also all usages of it panic() anyway, so the panic() in readReadyForQuery()/errorf() doesn't make a big difference. Except conn#prepareTo(), but all usages of that handle panic() via defer cn.errRecover(&err).

@julianbrost So shall we (I) just add an E case to readReadyForQuery() which throws the received error instead of the generic "protocol error"? As always, it would be up to applications like the ours how to handle which error.

[julianbrost]

I'm not sure what I'm supposed to answer here given that I know neither the PostgreSQL wire protocol nor the lib/pq implementation in much detail.

Returning an error like "got error while waiting for ReadyForQuery: [error message from server]" instead of "unexpected message 'E'; expected ReadyForQuery" sounds like a useful first step, especially if the documentation says that an error response is to be expected at that time. At least it shouldn't make anything worse as it just puts more information in an error that exists anyways.