[pgpool-general: 5285] stuck in active & idle in transaction

[Rolan Yadlin]

Hi,
in many setups in our labs we have "stuck" connections:
some fit the FAQ, but they seem to remain in "active" state forever (until
we restart pgpool):
this is reproducing all the time but the scenario is unknown.

examples the output was copied today a few minutes ago from a few setups:
*setup 1:*
node #1:
 psql -Upostgres -h172.18.255.41 -p5432 -c "SELECT
pid,state,query_start,query,waiting FROM pg_catalog.pg_stat_activity WHERE
state !='idle' AND query_start < current_timestamp - interval '30 minutes'
ORDER BY query_start;"
 pid  | state  |          query_start          | query  | waiting
------+--------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+---------
 3083 | active | 2017-01-29 15:16:16.907158+00 | SELECT count(*) FROM
pg_class AS c, pg_namespace AS n WHERE c.oid =
to_regclass('"pg_description"') AND c.relnamespace = n.oid AND n.nspname =
'pg_catalog' | f
 6526 | active | 2017-01-29 15:17:28.657637+00 | SELECT count(*) FROM
pg_class AS c, pg_namespace AS n WHERE c.oid =
to_regclass('"pg_description"') AND c.relnamespace = n.oid AND n.nspname =
'pg_catalog' | f
 6527 | active | 2017-01-29 15:17:28.657663+00 | SELECT count(*) FROM
pg_class AS c, pg_namespace AS n WHERE c.oid =
to_regclass('"pg_description"') AND c.relnamespace = n.oid AND n.nspname =
'pg_catalog' | f
(3 rows)

*node #2:*
 psql -Upostgres -h172.18.255.42 -p5432 -c "SELECT
pid,state,query_start,query,waiting FROM pg_catalog.pg_stat_activity WHERE
state !='idle' AND query_start < current_timestamp - interval '30 minutes'
ORDER BY query_start;"
  pid  |        state        |          query_start          |    query  |
waiting
-------+---------------------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 26340 | idle in transaction | 2017-01-29 21:56:29.025673+00 | select
count(*) as cnt from (select this_.id as id1_4_0_, this_.version as
version2_4_0_, this_.update_date as update_d3_4_0_, this_.trigger_date as
trigger_4_4_0_, this_.occurrence_date as occurren5_4_0_,
this_.state_user_id as state_us6_4
_0_, this_.action_user_id as action_u7_4_0_, this_.resource_id as
resource8_4_0_, this_.parameter01 a
s paramete9_4_0_, this_.parameter02 as paramet10_4_0_, this_.parameter03 as
paramet11_4_0_, this_.par
ameter04 as paramet12_4_0_, this_.parameter05 as paramet13_4_0_, this_.note
as note14_4_0_, this_.ala
rm_definition_id as alarm_d15_4_0_, this_.severity as severit16_4_0_,
this_.domain_id as domain_17_4_
0_, this_.state as state18_4_0_, this_.invoking_controller as
invokin19_4_0_, this_.invoking_element
as invokin20_4_0_, this_.sub_module_id as sub_mod21_4_0_,
this_.sub_instance_id as sub_ins22_4_0_, th
is_.resource_element_id as resourc23_4_0_, this_.resource_parent_element_id
as resourc24_4_0_ from al
arms this_ where this_.resource_id in ($1, $2, $3, $4, $5, $6, $7, $8, $9,
$ | f (1 row)


*setup 2 (currently has only 1 postgres/pgpool node):*
psql -Upostgres -h172.18.255.41 -p5432 -c "SELECT
pid,state,query_start,query,waiting FROM pg_catalog.pg_stat_activity WHERE
state !='idle' AND query_start < current_timestamp - interval '30 minutes'
ORDER BY query_start;"
  pid  |        state        |          query_start          |
          query
 | waiting
-------+---------------------+-------------------------------+---------------------------------------------------------------------------------------------------------------------------------+---------
 10143 | idle in transaction | 2017-01-26 13:34:48.460062+00 | select
submodule0_.id as id1_22_0_, submodule0_.name as name2_22_0_ from
events_sub_modules submodule0_ where submodule0_.id=$1 | f
 10134 | idle in transaction | 2017-01-26 14:15:46.345789+00 | select
submodule0_.id as id1_22_0_, submodule0_.name as name2_22_0_ from
events_sub_modules submodule0_ where submodule0_.id=$1 | f
(2 rows)


*setup 3:*
node1:
psql -Upostgres -h172.18.255.41 -p5432 -c "SELECT
pid,state,query_start,query,waiting FROM pg_catalog.pg_stat_activity WHERE
state != 'idle' AND query_start < current_timestamp - interval '30 minutes'
ORDER BY query_start;"
 pid  | state  |          query_start          |
query   | waiting
------+--------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+---------
 5506 | active | 2017-01-31 00:04:39.128256+00 | SELECT count(*) FROM
pg_class AS c, pg_namespace AS n WHERE c.oid =
to_regclass('"pg_description"') AND c.relnamespace = n.oid AND n.nspname =
'pg_catalog' | f
 5466 | active | 2017-01-31 00:04:39.12857+00  | SELECT count(*) FROM
pg_class AS c, pg_namespace AS n WHERE c.oid =
to_regclass('"pg_description"') AND c.relnamespace = n.oid AND n.nspname =
'pg_catalog' | f
 5465 | active | 2017-01-31 00:04:39.129839+00 | SELECT count(*) FROM
pg_class AS c, pg_namespace AS n WHERE c.oid =
to_regclass('"pg_description"') AND c.relnamespace = n.oid AND n.nspname =
'pg_catalog' | f
(3 rows)

unfortunately, there are more examples, some with more than 10 stuck
connections, sometimes it gets autovacuum "stuck" in active state,
we also had instances in which stuck connections were doing updates and
caused many transaction waiting for them to end and release locks, but we
haven't encountered this in a dew days.

if you need us to provide more information, just let us know.
Please help us :-)

we are using:
Postgres 9.5.5
PG-Pool 3.6.1
Hibernate/JDBC

Rolan.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170131/89e70b11/attachment.html>