Dave Dutcher wrote:
From: Shiva Raman
Subject: Re: [PERFORM] High CPU load on Postgres Server during Peak
not explicitly committed.
We have started updating the code on this.
You need a COMMIT for every BEGIN. If you just run a SELECT statement
without first beginning a transaction, then you should not end up with a
connection that is Idle in Transaction. If you are beginning a transaction,
doing a select, and then not committing, then yes that is a bug.
Dave
Dave is correct. A SELECT without a BEGIN in front of it will not begin a transaction. Atomic SELECTs (that is, those not intended to return rows that will then be updated or deleted, etc.) does not need and should NOT have a BEGIN in front of it.
Any block of statements that must act in an atomic fashion must have a BEGIN/COMMIT or BEGIN/ROLLBACK block around them to guarantee atomic results across statements; any time you issue a BEGIN you MUST issue either a ROLLBACK or COMMIT. Exiting SOUNDS safe (and if the connection is truly dropped it is as that will implicitly roll back any uncommitted transaction) BUT in a pooled connection environment it leads to exactly what you're seeing here.
It is a serious mistake to leave open transactions active in a session as that leaves multiple copies of rows and the support data necessary to handle them either in memory, on disk or both. When the working set of all postgresql instances reaches the physical memory limit and the system starts to page performance will go straight in the toilet.
-- Karl