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

[Lachezar Dobrev]

  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