Re: postgres doesn't detect other end of the unix socket is gone...

On Wed, May 7, 2008 at 5:28 PM, Darren Reed
<darrenr+postgres(at)fastmail(dot)net> wrote:
> It would appear that sometimes, when a disconnect a unix stream
> (ie perl DBI) from postgres in a forceful manner (^C), postgres
> doesn't appear to notice and keeps processing the transaction
> rather than aborting it.

Well, how much time should postgresql spend polling all it's
connections to see if they're dead? Generally speaking, it's the
client's job to notify the server when it disconnects.

> In this case, it would appear that 586 hasn't noticed anything strange
> happening. Should it have?

Nope, not its job really.

> Will this problem go away if I use TCP rather than unix streams?

tcp connections use keepalive to harvest such connections. the
default setting is 2+hours to notice and drop them. but you can set
tcp_keepalive_time to something like 300 (5 minutes) and they'll be
harvested much more quickly.

> Or is this a bug of the variety that when postgres is deep inside
> an UPDATE, doing lots of disk I/O, it never checks to see if it
> should abort because the client has gone?

It just never checks. Not a bug really.

> Actually, I can see aborting being a bug too...is there a way to
> control the behaviour here?

Once the OS on the pgsql side notices the disconnect, the standard
behaviour is to rollback whatever transaction you're in and exit.