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

[Muhammad Usama]

Hi

A similar problem was recently reported by a user
http://www.pgpool.net/mantisbt/view.php?id=107

Fix of that issue was committed for pgpool-II 3.3 and 3.2, on 24th July
which has rectified the problem.
Can you please check if the problem is solved in the latest code.

Thanks and regards,
Usama



On Mon, Jul 7, 2014 at 8:14 PM, Stefan Warten <sw at 00101010.org> wrote:

> 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
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20140815/c0cd7846/attachment.html>