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

[Stefan Warten]

Ok, good to know. But if I understand correctly, this only shifts the
problem away from the SET SESSION AUTHORIZATION DEFAULT query to RESET
ALL, which is the next command in my reset_query_list.

The question is still why isn't the connection properly closed but
idles somewhere in the reset_query_list commands (state 'idle in
transaction')?

There are currently around 250 processes in this state in my pgpool,
most of them are older than a day, ignoring timeouts like
child_life_time and client_idle_limit. How can this issue be solved?

Regards, SW.

On Mon, Jul 7, 2014 at 2:27 PM, Lachezar Dobrev <l.dobrev at gmail.com> wrote:
>   There are some changes in the statistics since 9.3 or possibly others.
>   Before that connections would display IDLE or IDLE IN TRANSACTION
> when there is no query being executed. Now the pg_stat_activity will
> display the last executed query on the connection (VERY HANDY!). The
> queries are NOT stale or hung. They just happen to be the last
> executed. I get lots of COMMIT or ROLLBACK or SELECT 1. There is a new
> column in the pg_stat_activity called "state" that will display 'idle'
> or 'active' or 'idle in transaction'.
>
> 2014-07-04 16:35 GMT+03:00 Stefan Warten <sw at 00101010.org>:
>> 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)
>> _______________________________________________
>> pgpool-general mailing list
>> pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general