Thread: postgresql locks up over the net

postgresql locks up over the net

From
"Peter T. Breuer"
Date:
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

Re: postgresql locks up over the net

From
Tom Lane
Date:
"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