Re: Fast distinct not working as expected

On Fri, Apr 18, 2014 at 3:07 AM, Franck Routier
<franck(dot)routier(at)axege(dot)com> wrote:
> I have found the problem, using this query (found here
> http://stackoverflow.com/questions/3312929/postgresql-idle-in-transaction-diagnosis-and-reading-pg-locks)
>
> select pg_class.relname, pg_locks.transactionid, pg_locks.mode,
> pg_locks.granted as "g", pg_stat_activity.current_query,
> pg_stat_activity.query_start,
> age(now(),pg_stat_activity.query_start) as "age",
> pg_stat_activity.procpid
> from pg_stat_activity,pg_locks
> left outer join pg_class on (pg_locks.relation = pg_class.oid)
> where pg_locks.pid=pg_stat_activity.procpid
> and pg_stat_activity.procpid = <AN IDLE TRANSACTION PROCESS>
> order by query_start;
>
>
> And indeed, we constantly have idle transcations. They all use the same
> dummy table, a dual table substitute containing only one column, and one
> row.
> We use this table with tomcat-jdbc-pool to check connections health with
> 'select 1 from dual' (we don't use 'select 1' for portability reasons, to
> work with oracle also).
> And these transactions are never commited. So we have a bunch of running
> transactions, constantly running and recreated by tomcat-jdbc-pool. Some of
> them run for hours.
> This seems to impact significally the ability of postgresql to vacuum... and
> thus to keep efficient indexes!

It affects a lot of other things too. All locks held by those
transactions are still held. Failure to release transactions is a
major application error that can and will explode the database. It's
similar in severity to a memory leak. The basic rule of thumb is that
transactions should be held for the shortest possible duration --
especially those that write to the database.

merlin