[pgpool-general: 3005] Hanging DISCARD ALL in pgpool after PSQL upgrade to 9.3

[Stefan Warten]

Hi,

after I have upgraded Postgresql to 9.3, I get lots of "DISCARD ALL"
queries in pgpool (state is "idle in transaction") that never finish
until I manually kill the processes or detach a node. Since this is
not a satisfying situation, I am looking for the reason why they got
stuck and how I can fix this.

Versions in use:
Systems are Ubuntu 12.04 LTS
pgpool2=3.3.3-2.pgdg12.4+2
postgresql-9.3=9.3.4-1.pgdg12.4+1
postgresql-9.3-pgpool2=3.3.3-1.pgdg12.4+1

Pgpool settings:
child_life_time = 10
client_idle_limit = 1200

First thing I did to find out where it hangs, was that I changed
reset_query_list to the single commands executed by DISCARD ALL
according to http://www.postgresql.org/docs/9.3/static/sql-discard.html

-reset_query_list = 'ABORT; DISCARD ALL'
+reset_query_list = 'ABORT; SET SESSION AUTHORIZATION DEFAULT; RESET
ALL; DEALLOCATE ALL; CLOSE ALL; UNLISTEN *; SELECT
pg_advisory_unlock_all(); DISCARD PLANS; DISCARD TEMP;'

It turned out that it actually hangs in SET SESSION AUTHORIZATION
DEFAULT, query state is still "idle in transaction". As mentioned
before, queries never* finish (*at least not within a week!).
According to child_life_time and client_idle_limit, I would expect
them to not run much longer than 1200 seconds but they stay for days.

Another thing I noticed is that the amount of SET processes in pgpool
(82) is equal to the SET processes in Postgresql master (82) and equal
to the sum of the SET processes in all Postgresql slaves (29+24+29=82)
(see commands below).

Any help or explaination is highly appreciated.

Regards, SW.


PGPOOL ~ $ sudo -i -u postgres psql -p 9999 -c "select count(*) from
pg_stat_activity where query LIKE ' SET%';"
 count
-------
    82
(1 row)

PSQL-MASTER ~ $ sudo -i -u postgres psql -c "select count(*) from
pg_stat_activity where query LIKE ' SET%'  AND
client_addr='$pgpool_ipaddr';"
 count
-------
    82
(1 row)

PSQL-SLAVE1 ~ $ sudo -i -u postgres psql -c "select count(*) from
pg_stat_activity where query LIKE ' SET%' AND
client_addr='$pgpool_ipaddr';"
 count
-------
    29
(1 row)

PSQL-SLAVE2 ~ $ sudo -i -u postgres psql -c "select count(*) from
pg_stat_activity where query LIKE ' SET%' AND
client_addr='$pgpool_ipaddr';"
 count
-------
    24
(1 row)

PSQL-SLAVE2 ~ $ sudo -i -u postgres psql -c "select count(*) from
pg_stat_activity where query LIKE ' SET%' AND
client_addr='$pgpool_ipaddr';"
 count
-------
    29
(1 row)