Re: query against pg_locks leads to large memory alloc

On Tue, Aug 19, 2014 at 9:40 AM, Kevin Grittner <kgrittn@ymail.com> wrote:
> Hmm, that's not outrageous.  How about long-running transactions?
> Please check pg_stat_activity and pg_prepared_xacts for xact_start
> or prepared (respectively) values older than a few minutes.  Since
> predicate locks may need to be kept until an overlapping
> transaction completes, a single long-running transaction can bloat
> the lock count.

I do see a handful of backends that like to stay IDLE in transaction
for minutes at a time.  We are refactoring the application responsible
for these long IDLE times, which will hopefully reduce the duration of
their connections.

# select backend_start, xact_start, query_start, waiting,
current_query from pg_stat_activity where xact_start < now() -
interval '3 minutes';
         backend_start         |          xact_start           |
   query_start          | waiting |     current_query

-------------------------------+-------------------------------+-------------------------------+---------+-----------------------
 2014-08-19 09:48:00.398498-07 | 2014-08-19 09:49:19.157478-07 |
2014-08-19 10:03:04.99303-07  | f       | <IDLE> in transaction
 2014-08-19 09:38:00.493924-07 | 2014-08-19 09:53:47.00614-07  |
2014-08-19 10:03:05.003496-07 | f       | <IDLE> in transaction
(2 rows)

... now() was 2014-08-19 10:03 in the above query.  I do not see
anything in pg_prepared_xacts, we do not use two-phase commit.


> Also, could you show use the output from?:
>
>   SELECT version();
                                                    version
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.13 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit
(1 row)