postgresql locks up over the net

Started by Peter T. Breueralmost 19 years ago2 messagesbugs
Jump to latest
#1Peter T. Breuer
ptb@inv.it.uc3m.es

When talking across a net connection, the 8.1.8-1 client eventually hangs
waiting for a reply to a query that the 7.4.7-6 server misses:

CLIENT:

1) send(3, "Q\0\0\0009SELECT key FROM records WHE"..., 58, 0) = 58
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
poll([{fd=3, events=POLLIN|POLLERR, revents=POLLIN}], 1, -1) = 1
2) recv(3, "T\0\0\0\34\0\1key\0\0\1\22e\0\1\0\0\0\27\0\4\377\377\377"..., 16384, 0) = 65
rt_sigprocmask(SIG_BLOCK, [PIPE], [], 8) = 0
3) send(3, "Q\0\0\1\232UPDATE records SET magic = "..., 411, 0) = 411
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
poll([{fd=3, events=POLLIN|POLLERR, revents=POLLIN}], 1, -1) = 1
4) recv(3, "C\0\0\0\rUPDATE 1\0Z\0\0\0\5I", 16384, 0) = 20
rt_sigprocmask(SIG_BLOCK, [PIPE], [], 8) = 0
5) send(3, "Q\0\0\0008SELECT name from names WHER"..., 57, 0) = 57
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
poll( ....STUCK...

SERVER:

1) recv(8, "Q\0\0\0009SELECT key FROM records WHE"..., 8192, 0) = 58
gettimeofday({1179180003, 769584}, NULL) = 0
send(5, "\4\0\0\0\254\0\0\0\2\0\0\0\215V\0\0\276I\0\0d\0\0\0\2\0"..., 172, 0) = 172
2) send(8, "T\0\0\0\34\0\1key\0\0\1\22e\0\1\0\0\0\27\0\4\377\377\377"..., 65, 0) = 65
3) recv(8, "Q\0\0\1\232UPDATE records SET magic = "..., 8192, 0) = 411
gettimeofday({1179180003, 839197}, NULL) = 0
time(NULL) = 1179180003
_llseek(42, 8142848, [8142848], SEEK_SET) = 0
write(42, "Z\320\1\0\22\0\0\0\0\0\0\0\0@|\2\234\0\0\0\315\253\315"..., 8192) = 8 192
fdatasync(0x2a) = 0
send(5, "\4\0\0\0\254\0\0\0\2\0\0\0\215V\0\0\276I\0\0d\0\0\0\2\0"..., 172, 0) = 172
4) send(8, "C\0\0\0\rUPDATE 1\0Z\0\0\0\5I", 20, 0) = 20
recv(8, ... STUCK...

The server never received the client's send (5) on client channel 3,
server channel 8. It's waiting for a communication that it missed.

This works fine to localhost (i.e. 8.1 to 8.1). How can the server miss
a send from a client? What received the send from the client? The
client thinks it went out! Surely this is tcp? Apparently not! Is
there some way of saying to use tcp? I suppose insisting on ssl would
do.

Peter

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Peter T. Breuer (#1)
Re: postgresql locks up over the net

"Peter T. Breuer" <ptb@inv.it.uc3m.es> writes:

When talking across a net connection, the 8.1.8-1 client eventually hangs
waiting for a reply to a query that the 7.4.7-6 server misses:

What platforms are the client and server running on? What sort of
networking junk is in between? Are there any significant delays between
the queries? (I'm wondering in particular about NAT routers dropping
connections due to idle timeouts...)

regards, tom lane