Обсуждение: postgres doesn't detect other end of the unix socket is gone...

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.

As an example, I currently see the following open files:
postgres postgres   28780    7* unix stream c3653d80 <-> c2d6d7c0
postgres postgres   23751    7* unix stream c2aeda80 <-> c3615540
postgres postgres   12631    7* unix stream c35e3640 <-> c2ac1000
postgres postgres     586    7* unix stream c35ce400
postgres postgres   29011    5* unix stream c2adb900

The last two are:
  586 ?      Ds   0:00.00 postgres: postgres postgres [local] UPDATE
29011 ttyp0- IW   0:00.00 /usr/pkg/bin/postgres -D /home/postgres/db

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

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

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?

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

Darren

On Wed, May 7, 2008 at 5:28 PM, Darren Reed
<darrenr+postgres@fastmail.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.