Re: High CPU load on Postgres Server during Peak times!!!!

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