Re: psql query gets stuck indefinitely

Hi All

Please help me .

Thanks...
Tamanna

On Mon, Dec 5, 2011 at 12:45 PM, tamanna madaan <
tamanna(dot)madaan(at)globallogic(dot)com> wrote:

> Hi Tomas
>
> I tried it on the system having postgres-8.4.0 . And the behavior is same
> .
>
> Cluster means a group of machines having postgres installed on all of them
> .
>
> Same database is created on all the machines one of which working as
> master DB
>
> on which operation (like insert/delete/update) will be performed and
> others working
>
> as Slave Db which will get data replicated to them from master DB by slony
> . In my
>
> cluster setup there are only two machines ( A and B ) one having master Db
> and other
>
> being slave . I execute the below query from system A to system B :
>
> psql -U<db name> -h<host ip of B> -c "select sleep(300);"
>
> This query can be seen running on system B in `ps -eaf | grep postgres`
> output .
>
> Now, while this query is going on, execute below command on system A which
> will block any packet coming to this machine :
>
> iptables -I INPUT -i eth0 -j DROP .
>
> Afer 5 mins (which is the sleep period) , the above query will finish on
> system B . But it can still be seen
>
> running on system A . This may be because of the reason that the message
> (that the query is finished)
>
> have not been received by system A .
>
> Still I would assume that after (tcp_keepalive_time +
> tcp_keepalive_probes*tcp_keepalive_intvl) , the above
>
> psql query should return on system A as well. But, this query doesn't
> return until it is killed manually .
>
> What could be the reason of that ??
>
>
> Well , I learnt below from the release notes of postgres :
>
>
> ==
> =========================================================================================
>
>
>
> postgres 8.1
>
>
> server side chnages :
>
>
> Add configuration parameters to control TCP/IP keep-alive times for idle,
> interval, and count (Oliver Jowett)
>
> These values can be changed to allow more rapid detection of lost client
> connections.
>
>
> postgres 9.0
>
>
> E.8.3.9. Development Tools
>
> E.8.3.9.1. libpq
>
>
> Add TCP keepalive settings in libpq (Tollef Fog Heen, Fujii Masao, Robert
> Haas)
>
> Keepalive settings were already supported on the server end of TCP
> connections.
>
>
>
> ==============================================================================================
>
>
> Does this mean that TCP keep alive settings(that are provided in postgres
> 8.1 onwards) would only work for lost connections to server and
>
> won't work in the case above as above case requires psql (which is client
> ) to be returned ?? And for the above case the TCP keepalive settings in
> libpq ( that are provided in postgres 9.0 onwards) would work ??
>
>
> kernel version on my system is 2.6.27.7-9-default and potstgres-8.4.0.
> keepalive setting are as below :
>
>
> postgresql.conf
>
>
> #tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
>
> # 0 selects the system default
>
> #tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
>
> # 0 selects the system default
>
> #tcp_keepalives_count = 0 # TCP_KEEPCNT;
>
> # 0 selects the system default
>
> system level setiing :
>
> net.ipv4.tcp_keepalive_time = 7200
>
> net.ipv4.tcp_keepalive_probes = 9
>
> net.ipv4.tcp_keepalive_intvl = 75
>
> Regards
>
> Tamanna
>
>
>
> On Thu, Dec 1, 2011 at 7:28 PM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
>
>> On 1 Prosinec 2011, 12:57, tamanna madaan wrote:
>> > Hi Craig
>> > I am able to reproduce the issue now . I have postgres-8.1.2 installed
>> in
>> > cluster setup.
>>
>> Well, the first thing you should do is to upgrade, at least to the last
>> 8.1 minor version, which is 8.1.22. It may very well be an already fixed
>> bug (haven't checked). BTW the 8.1 branch is not supported for a long
>> time, so upgrade to a more recent version if possible.
>>
>> Second - what OS are you using, what version? The keep-alive needs support
>> at OS level, and if the OS is upgraded as frequently as the database (i.e.
>> not at all), this might be already fixed.
>>
>> And finally - what do you mean by 'cluster setup'?
>>
>> Tomas
>>
>>
>
>
> --
> Tamanna Madaan | Associate Consultant | GlobalLogic Inc.
> Leaders in Software R&D Services
> ARGENTINA | CHILE | CHINA | GERMANY | INDIA | ISRAEL | UKRAINE | UK | USA
>
> Office: +0-120-406-2000 x 2971
>
> www.globallogic.com
>
>
>

--
Tamanna Madaan | Associate Consultant | GlobalLogic Inc.
Leaders in Software R&D Services
ARGENTINA | CHILE | CHINA | GERMANY | INDIA | ISRAEL | UKRAINE | UK | USA

Office: +0-120-406-2000 x 2971

www.globallogic.com