Get connection status to avoid wasting clients

[rkaw92]

Hi,

Currently, your transaction example checks if the ROLLBACK failed (by looking at the error flag) or if the COMMIT failed (indirectly - the "return client to pool" callback is passed as a callback to the COMMIT query) and returns the client to the pool for re-use iff its state is clean, which is currently defined as lack of error passed to the client release callback.

This is not necessary, and could harm the performance of applications that get a lot of COMMIT failures (serialization failures?). It should be sufficient to check whether the transaction has really ended, and make sure that network errors are all handled properly.

PostgreSQL is supposed to return a transaction status update with each executed query, so our client library should be able to determine whether the transaction has finished. I think this is related to this C interface: http://www.postgresql.org/docs/9.4/static/libpq-status.html - though it may not be very relevant when it comes to a native JS implementation.

Consider the following turn of events, all on a single Client obtained from a pool:

1. BEGIN
2. some_statement_that_succeeds
3. COMMIT (this fails)

Now, if the COMMIT has failed, but the transaction has still finished (perhaps because it was turned into a ROLLBACK upon detecting a serialization failure), we can safely return the client to the pool for re-use, because it essentially behaves the same as a successful ROLLBACK. The same should be true for failed ROLLBACKS, provided that the server correctly indicates the transaction status. What I'm getting at is that only clients which are left in the middle of a transaction should be removed from the pool - other clients are just fine to use if I understand the semantics of Postgres.

I've skimmed through this module's codebase and have not found a single variable to look at that would let us determine the transaction state. Is there any? If not, it might be reasonable to keep track of it and export via some public property of the Client.

[yoonghm]

I support this great feature. Serializable Snapshot Isolation (SSI) is the only way to make concurrent update to the database safe.
However, for node.js app, i use new to instantiate a client to postgres instead of from client pool, as i could not find a way to pass the client (from client pool) around. It would not be a problem, unless you use multiple node apps.