Обсуждение: RE: PostgreSQL and Unicode

> Regarding the "postmaster processes not going away" problem...
> 
> We're developing a Java application that connects to a PostgreSQL db. During
> our development process we debug the code and sometimes kill the program in
> the middle of the run. Sometimes this means that an open Connection to the
> database is not properly closed. Now, I realize that this is an unfriendly
> thing to do to PG but I would think it would eventually recover. What
> happens instead is that the postmaster/postgres process that was handling
> that connection never terminates. I have seen processes that are more than 2
> weeks old before we noticed and restarted postmaster manually.
> 
> The problem is that eventually PG runs out of connections and stops allowing
> new ones.
> 
> So, is there a way to tell PG to timeout unused connections after some
> specified time? I've looked through all the docs and could not find anything
> like this. I realize that this is a difficult issue because if there is an
> unresolved transaction what do you do with it. I guess all you could do is
> roll it back.
> 
> Any other suggestions? If not, can I request this as a future feature?
> Although our problems are happening during debugging, they could happen
> during deployment given a hardware problem or, *gasp*, a bug in our code.

What about adding KEEPALIVE option to the socket? This would take a
while to detect orphaned socket, though.
--
Tatsuo Ishii

Tatsuo Ishii <t-ishii@sra.co.jp> writes:
>> Regarding the "postmaster processes not going away" problem...

> What about adding KEEPALIVE option to the socket?

Of course, since whatever OS he's using on the client side is too broken
to notice that the socket is orphaned and close it, it might be so
broken as to respond to the keepalive pings :-(.  Still, it'd be an easy
thing to try...

Even though the stated case sounds more like an OS bug than anything
else, setting KEEPALIVE on our TCP connections is probably still a good
idea.  If the client machine were to crash completely then it wouldn't
be reasonable to expect it to close the connection, and we'd want to
have some method of ensuring that the connected backend shuts down
eventually.  KEEPALIVE seems sufficiently low-overhead (and easy to
implement) to be the right answer for this scenario.
        regards, tom lane

> > What about adding KEEPALIVE option to the socket?
> 
> Of course, since whatever OS he's using on the client side is too broken
> to notice that the socket is orphaned and close it, it might be so
> broken as to respond to the keepalive pings :-(.  Still, it'd be an easy
> thing to try...
> 
> Even though the stated case sounds more like an OS bug than anything
> else, setting KEEPALIVE on our TCP connections is probably still a good
> idea.  If the client machine were to crash completely then it wouldn't
> be reasonable to expect it to close the connection, and we'd want to
> have some method of ensuring that the connected backend shuts down
> eventually.  KEEPALIVE seems sufficiently low-overhead (and easy to
> implement) to be the right answer for this scenario.

Ok. Here are patches against 7.0. BTW, does this break some platforms
such as Windows NT or QUNX4?

*** postgresql-7.0/src/backend/libpq/pqcomm.c.orig    Tue May 16 18:06:42 2000
--- postgresql-7.0/src/backend/libpq/pqcomm.c    Wed May 17 08:23:09 2000
***************
*** 375,381 ****         if (setsockopt(port->sock, pe->p_proto, TCP_NODELAY,                        &on, sizeof(on)) <
0)        {
 
!             perror("postmaster: StreamConnection: setsockopt");             return STATUS_ERROR;         }     }
--- 375,387 ----         if (setsockopt(port->sock, pe->p_proto, TCP_NODELAY,                        &on, sizeof(on)) <
0)        {
 
!             perror("postmaster: StreamConnection: setsockopt(TCP_NODELAY)");
!             return STATUS_ERROR;
!         }
!         if (setsockopt(port->sock, SOL_SOCKET, SO_KEEPALIVE,
!                        &on, sizeof(on)) < 0)
!         {
!             perror("postmaster: StreamConnection: setsockopt(SO_KEEPALIVE)");             return STATUS_ERROR;
}     }
 

This should be save. Both NT and QNX4 support SO_KEEPALIVE.

-----Ursprüngliche Nachricht-----
Von: Tatsuo Ishii <t-ishii@sra.co.jp>
An: <tgl@sss.pgh.pa.us>
Cc: <t-ishii@sra.co.jp>; <rmager@vgkk.co.jp>; <ishii@postgresql.org>;
<hackers@postgresql.org>
Gesendet: Mittwoch, 17. Mai 2000 03:07
Betreff: Re: [HACKERS] RE: PostgreSQL and Unicode


> > > What about adding KEEPALIVE option to the socket?
>
> Ok. Here are patches against 7.0. BTW, does this break some platforms
> such as Windows NT or QUNX4?

> This should be save. Both NT and QNX4 support SO_KEEPALIVE.

Thanks for the info. The fix will apear in 7.0.1.
--
Tatsuo Ishii

Seems this is applied.


> > > What about adding KEEPALIVE option to the socket?
> > 
> > Of course, since whatever OS he's using on the client side is too broken
> > to notice that the socket is orphaned and close it, it might be so
> > broken as to respond to the keepalive pings :-(.  Still, it'd be an easy
> > thing to try...
> > 
> > Even though the stated case sounds more like an OS bug than anything
> > else, setting KEEPALIVE on our TCP connections is probably still a good
> > idea.  If the client machine were to crash completely then it wouldn't
> > be reasonable to expect it to close the connection, and we'd want to
> > have some method of ensuring that the connected backend shuts down
> > eventually.  KEEPALIVE seems sufficiently low-overhead (and easy to
> > implement) to be the right answer for this scenario.
> 
> Ok. Here are patches against 7.0. BTW, does this break some platforms
> such as Windows NT or QUNX4?
> 
> *** postgresql-7.0/src/backend/libpq/pqcomm.c.orig    Tue May 16 18:06:42 2000
> --- postgresql-7.0/src/backend/libpq/pqcomm.c    Wed May 17 08:23:09 2000
> ***************
> *** 375,381 ****
>           if (setsockopt(port->sock, pe->p_proto, TCP_NODELAY,
>                          &on, sizeof(on)) < 0)
>           {
> !             perror("postmaster: StreamConnection: setsockopt");
>               return STATUS_ERROR;
>           }
>       }
> --- 375,387 ----
>           if (setsockopt(port->sock, pe->p_proto, TCP_NODELAY,
>                          &on, sizeof(on)) < 0)
>           {
> !             perror("postmaster: StreamConnection: setsockopt(TCP_NODELAY)");
> !             return STATUS_ERROR;
> !         }
> !         if (setsockopt(port->sock, SOL_SOCKET, SO_KEEPALIVE,
> !                        &on, sizeof(on)) < 0)
> !         {
> !             perror("postmaster: StreamConnection: setsockopt(SO_KEEPALIVE)");
>               return STATUS_ERROR;
>           }
>       }
> 


--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026