Re: Beating Oracle

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Postgres doesn't have 6000 full-time developers engaged in inventing
> bizarre features (and a substitute for something that should be done
> at the network level is bizarre in my book).

May be, though, it's just a matter of not setting SO_KEEPALIVE? In my
experience, the difference between always keeping and spontaneously
losing a connection during a network outage is often this option. In
src/backend/libpq/pqcomm.c, we find (reformatted slightly here):

/* select NODELAY and KEEPALIVE options if it's a TCP connection */
if (port->laddr.sa.sa_family == AF_INET)
{
int on = 1;
if (setsockopt(port->sock, IPPROTO_TCP, TCP_NODELAY,
(char *) &on, sizeof(on)) < 0)
{
perror("postmaster: StreamConnection: setsockopt(TCP_NODELAY)");
return STATUS_ERROR;
}
if (setsockopt(port->sock, SOL_SOCKET, SO_KEEPALIVE,
(char *) &on, sizeof(on)) < 0)
{
perror("postmaster: StreamConnection: setsockopt(SO_KEEPALIVE)");
return STATUS_ERROR;
}
}

And in my nearest manual entry on setsockopt(), I find:

SO_KEEPALIVE enables the periodic transmission of messages on a
connected socket. Should the connected party fail to respond to
these messages, the connection is considered broken and processes
using the socket are notified via a SIGPIPE signal when attempting
to send data.

Betcha if Bruce commented out that second setsockopt() block, he'd
suddenly see his PostgreSQL connections stay up, just like the Oracle
ones! :-)

> Non-broken TCP stacks are normally quite tenacious about surviving
> transmission glitches in already-open connections.

...but you can turn part of that off by turning on SO_KEEPALIVE.

-tih
--
Puritanism -- the haunting fear that someone, somewhere, may be happy.