Re: psql query gets stuck indefinitely

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