pg-promise: Recover from idle_in_transaction_session_timeout

Expected behavior

After an idle_in_transaction_session_timeout, the used connection will be working properly when used again from the pool.

Actual behavior

After a statement timeout in transaction, the used connection will be “broken” and new queries will not work. The error-message is Client has encountered a connection error and is not queryable

Steps to reproduce

I’ve added a test-case to my project fork

Environment

Version of pg-promise: 10.2.1
OS type (Linux/Windows/Mac): Windows 10
Version of Node.js: 12.12.0

Stackoverflow: How to handle idle_in_transaction_session_timeout?
similar issue: #566

Notes

I thought that adding another check to isConnectivityError for code 25P03 (see postgres error codes) should work, but it does not. In this case the err parameter has no code property, but a message string.
A nasty workaround for now is to check this message string:

    if (!code && err.message && typeof err.message === 'string') {
        if (err.message.includes('is not queryable')) return true;
    }

About this issue

Original URL
State: closed
Created 5 years ago
Comments: 27 (14 by maintainers)

Commits related to this issue

fixing #680 — committed to vitaly-t/pg-promise by vitaly-t 4 years ago

Most upvoted comments

@vitaly-t still seeing the issue on 10.11.0 Anything we can do to help?

flovilmart on Dec 17, 2021

@tmtron The underlying driver just took in the recommended change for dumping non-queryable connections.

This means the functionality added here will be removed in a new official release that starts using that branch. That probably will be version 11.

vitaly-t on Jan 29, 2020

@vitaly-t I’ve just updated to 10.3.5 and it works for me. All our tests are okay. I think using the “private” _queryable property of postgres-node is okay for now, since we have a test-case that will fail, if this property will ever change.
And we will keep an eye on node-postgres #2026
Thanks again!

tmtron on Jan 12, 2020

After checking some code and issues of node-postgres, I think my initial assumption was wrong. We will never receive error-code 25P03 from the database. Instead we only get the error-string from node-postgres::

query.handleError(new Error('Client has encountered a connection error and is not queryable'), this.connection)

I think whenever this error happens (for whatever reason - not only when idle_in_transaction_session_timeout is used), pg-promise must remove this connection from the pool because it is broken.

Since node-postgres only returns an error string, and no error-code or error-class that can be checken, the only way to handle this case is to check the text of the error-message.

There are many related issues in node-postgres:

and in node-pg-pool:

#673 Connection terminated due to connection timeout / Connection terminated

tmtron on Dec 12, 2019