Thread: database shutdown with persistent client connections

database shutdown with persistent client connections

From
g.hintermayer@inode.at (Gerhard Hintermayer)
Date:
I have a database server with multiple databases (~5) and some
persistent connections using the pgtcl interface.
If I want to shutdown the database server (smart mode) my persistant
connections prevent the postmaster from shuting down (as documented),
if I user fast mode, the shutdown get's done, but without any
notification to the clients. An error is only triggered, if I issue
sql commands, but unfortunately some applications send  commands at a
very low rate, but i'd like to get the shutdown information
immediatly.
using LISTEN gives me an error message immediately when issuing the
shutdown

> FATAL 1:  This connection has been terminated by the administrator.
>no connection to the server

obviously there seems to be some notification, but I do not know it's
name :-(

I thought of sending notifications by hand before invoking "pg_ctl
stop -m fast" but I don't want to reinvent the wheel.

Is there a notification sen't out in either smart or fast shutdown
mode ?

Gerhard

Re: database shutdown with persistent client connections

From
Tom Lane
Date:
g.hintermayer@inode.at (Gerhard Hintermayer) writes:
> Is there a notification sen't out in either smart or fast shutdown
> mode ?

Sure: the backend sends an error message
  FATAL:  This connection has been terminated by the administrator.
before closing the connection.

The problem you're describing is that the client-side code isn't looking
for any communication from the server except when it's involved in a SQL
command.  I'm not sure what you can do about that except restructure the
client.

            regards, tom lane

Re: database shutdown with persistent client connections

From
g.hintermayer@inode.at (Gerhard Hintermayer)
Date:
tgl@sss.pgh.pa.us (Tom Lane) wrote in message news:<27612.1028124591@sss.pgh.pa.us>...
> g.hintermayer@inode.at (Gerhard Hintermayer) writes:
> > Is there a notification sen't out in either smart or fast shutdown
> > mode ?
>
> Sure: the backend sends an error message
>   FATAL:  This connection has been terminated by the administrator.
> before closing the connection.
>
> The problem you're describing is that the client-side code isn't looking
> for any communication from the server except when it's involved in a SQL
> command.  I'm not sure what you can do about that except restructure the
> client.
>

What I tried is (for libpgtcl):
Everytime if I do PQconsumeInput (when the backend channel gets
readable) I check for the return value. (0 == error) and generate a
notification manually, e.g. connection_closed) and pass it to the TCL
event queue. The only bad thing I had to do is to comment out removing
all pending events in PgStopNotifyEventSource. Could there be any
sideeffects ? Maybe I should do that only if the connection was
unexpectedly closed (i.e. called from within PgNotifyTransferEvents) ?

Gerhard