PostgreSQL – Proper Way to Reset or Cleanup a Connection

postgresqlpostgresql-10

I have a very simple connection pooler in my application. When handing back a connection I need to ensure that no variables are set and no transaction is still on going. I understand that ROLLBACK; DISCARD ALL; should be enough.

In most cases, if the transaction was successful, it will be committed before handing it over to the pooler and at this point, I will have a warning such as WARNING: there is no transaction in progress.

Is there any way to execute a ROLLBACK only if we are still in a transaction?
Or is there an other preferred method to cleanup a connection?

Best Answer

Assuming libpq, you can call PQtransactionStatus to find out if there 's a transaction open.

If you only have SQL then you can call 

select xact_start <> query_start from pg_catalog.pg_stat_activity where pid=pg_backend_pid();

That round-trip will add latency to the cleanup process, but this will not be a problem so long as there's no process blocked waiting for the freed connection.

Related Question