<div dir="ltr">Hi<div><br></div><div>A similar problem was recently reported by a user <a href="http://www.pgpool.net/mantisbt/view.php?id=107">http://www.pgpool.net/mantisbt/view.php?id=107</a></div><div><br></div><div>Fix of that issue was committed for pgpool-II 3.3 and 3.2, on 24th July which has rectified the problem.</div>
<div>Can you please check if the problem is solved in the latest code.</div><div><br></div><div>Thanks and regards,</div><div>Usama</div><div><br></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Mon, Jul 7, 2014 at 8:14 PM, Stefan Warten <span dir="ltr"><<a href="mailto:sw@00101010.org" target="_blank">sw@00101010.org</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Ok, good to know. But if I understand correctly, this only shifts the<br>
problem away from the SET SESSION AUTHORIZATION DEFAULT query to RESET<br>
ALL, which is the next command in my reset_query_list.<br>
<br>
The question is still why isn't the connection properly closed but<br>
idles somewhere in the reset_query_list commands (state 'idle in<br>
transaction')?<br>
<br>
There are currently around 250 processes in this state in my pgpool,<br>
most of them are older than a day, ignoring timeouts like<br>
child_life_time and client_idle_limit. How can this issue be solved?<br>
<br>
Regards, SW.<br>
<div class="HOEnZb"><div class="h5"><br>
On Mon, Jul 7, 2014 at 2:27 PM, Lachezar Dobrev <<a href="mailto:l.dobrev@gmail.com">l.dobrev@gmail.com</a>> wrote:<br>
> There are some changes in the statistics since 9.3 or possibly others.<br>
> Before that connections would display IDLE or IDLE IN TRANSACTION<br>
> when there is no query being executed. Now the pg_stat_activity will<br>
> display the last executed query on the connection (VERY HANDY!). The<br>
> queries are NOT stale or hung. They just happen to be the last<br>
> executed. I get lots of COMMIT or ROLLBACK or SELECT 1. There is a new<br>
> column in the pg_stat_activity called "state" that will display 'idle'<br>
> or 'active' or 'idle in transaction'.<br>
><br>
> 2014-07-04 16:35 GMT+03:00 Stefan Warten <<a href="mailto:sw@00101010.org">sw@00101010.org</a>>:<br>
>> Hi,<br>
>><br>
>> after I have upgraded Postgresql to 9.3, I get lots of "DISCARD ALL"<br>
>> queries in pgpool (state is "idle in transaction") that never finish<br>
>> until I manually kill the processes or detach a node. Since this is<br>
>> not a satisfying situation, I am looking for the reason why they got<br>
>> stuck and how I can fix this.<br>
>><br>
>> Versions in use:<br>
>> Systems are Ubuntu 12.04 LTS<br>
>> pgpool2=3.3.3-2.pgdg12.4+2<br>
>> postgresql-9.3=9.3.4-1.pgdg12.4+1<br>
>> postgresql-9.3-pgpool2=3.3.3-1.pgdg12.4+1<br>
>><br>
>> Pgpool settings:<br>
>> child_life_time = 10<br>
>> client_idle_limit = 1200<br>
>><br>
>> First thing I did to find out where it hangs, was that I changed<br>
>> reset_query_list to the single commands executed by DISCARD ALL<br>
>> according to <a href="http://www.postgresql.org/docs/9.3/static/sql-discard.html" target="_blank">http://www.postgresql.org/docs/9.3/static/sql-discard.html</a><br>
>><br>
>> -reset_query_list = 'ABORT; DISCARD ALL'<br>
>> +reset_query_list = 'ABORT; SET SESSION AUTHORIZATION DEFAULT; RESET<br>
>> ALL; DEALLOCATE ALL; CLOSE ALL; UNLISTEN *; SELECT<br>
>> pg_advisory_unlock_all(); DISCARD PLANS; DISCARD TEMP;'<br>
>><br>
>> It turned out that it actually hangs in SET SESSION AUTHORIZATION<br>
>> DEFAULT, query state is still "idle in transaction". As mentioned<br>
>> before, queries never* finish (*at least not within a week!).<br>
>> According to child_life_time and client_idle_limit, I would expect<br>
>> them to not run much longer than 1200 seconds but they stay for days.<br>
>><br>
>> Another thing I noticed is that the amount of SET processes in pgpool<br>
>> (82) is equal to the SET processes in Postgresql master (82) and equal<br>
>> to the sum of the SET processes in all Postgresql slaves (29+24+29=82)<br>
>> (see commands below).<br>
>><br>
>> Any help or explaination is highly appreciated.<br>
>><br>
>> Regards, SW.<br>
>><br>
>><br>
>> PGPOOL ~ $ sudo -i -u postgres psql -p 9999 -c "select count(*) from<br>
>> pg_stat_activity where query LIKE ' SET%';"<br>
>> count<br>
>> -------<br>
>> 82<br>
>> (1 row)<br>
>><br>
>> PSQL-MASTER ~ $ sudo -i -u postgres psql -c "select count(*) from<br>
>> pg_stat_activity where query LIKE ' SET%' AND<br>
>> client_addr='$pgpool_ipaddr';"<br>
>> count<br>
>> -------<br>
>> 82<br>
>> (1 row)<br>
>><br>
>> PSQL-SLAVE1 ~ $ sudo -i -u postgres psql -c "select count(*) from<br>
>> pg_stat_activity where query LIKE ' SET%' AND<br>
>> client_addr='$pgpool_ipaddr';"<br>
>> count<br>
>> -------<br>
>> 29<br>
>> (1 row)<br>
>><br>
>> PSQL-SLAVE2 ~ $ sudo -i -u postgres psql -c "select count(*) from<br>
>> pg_stat_activity where query LIKE ' SET%' AND<br>
>> client_addr='$pgpool_ipaddr';"<br>
>> count<br>
>> -------<br>
>> 24<br>
>> (1 row)<br>
>><br>
>> PSQL-SLAVE2 ~ $ sudo -i -u postgres psql -c "select count(*) from<br>
>> pg_stat_activity where query LIKE ' SET%' AND<br>
>> client_addr='$pgpool_ipaddr';"<br>
>> count<br>
>> -------<br>
>> 29<br>
>> (1 row)<br>
>> _______________________________________________<br>
>> pgpool-general mailing list<br>
>> <a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
>> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
_______________________________________________<br>
pgpool-general mailing list<br>
<a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
<a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
</div></div></blockquote></div><br></div>