Re: query against pg_locks leads to large memory alloc

On Tue, Aug 19, 2014 at 9:40 AM, Kevin Grittner <kgrittn(at)ymail(dot)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)