> Tom Lane wrote:
> > "Magnus Hagander" <mha@sollentuna.net> writes:
> > >> If we are going to allow session kill then of course we need
> > >> PID for that.
> > 
> > > I still say we need this.
> > 
> > Well, that seems to be the consensus, so I won't stand in the way.
> > 
> > If you like the cancel-by-XID idea then I'd suggest providing two
> > functions: pg_cancel_query(XID) to send SIGINT and 
> > pg_kill_session(PID) to send SIGTERM.  I'm not sure if the 
> other two 
> > combinations (SIGINT by PID and SIGTERM by XID) are worth providing.
> 
> Agreed.  Two functions, cancel by xid (skips cancel if xid 
> changes during command), and kill by pid for remote admin 
> apps, is great.

This sounds good to me, too. (Though I think SIGINT by PID might be
good, I see no need for SIGTERM by XID).

But are you saying it *is* safe with SIGTERM to a backend? I always
thought it was before, but after what you said earlier in this thread, I
changed my mind... (Unfort, I'm not well enough into the deep ends of
the code to say which is right...)

//Magnus

Tue, 6 Apr 2004 17:32:41 +0200
"Magnus Hagander" <mha@sollentuna.net>
Re: Function to kill backend
"Magnus Hagander" <mha@sollentuna.net> writes:
> But are you saying it *is* safe with SIGTERM to a backend?

I'm saying I'm not happy about promoting that to the status of a
supported feature.  Up to now it's always been "if it breaks you
get to keep both pieces", but if there's a built-in function to do it
then people will naturally expect a certain level of reliability.

Maybe it works fine, maybe it doesn't.  But I foresee having to expend
work down the road because of this addition.

			regards, tom lane

Tue, 06 Apr 2004 11:36:21 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Function to kill backend 
Tom Lane wrote:
> "Magnus Hagander" <mha@sollentuna.net> writes:
> > But are you saying it *is* safe with SIGTERM to a backend?
> 
> I'm saying I'm not happy about promoting that to the status of a
> supported feature.  Up to now it's always been "if it breaks you
> get to keep both pieces", but if there's a built-in function to do it
> then people will naturally expect a certain level of reliability.
> 
> Maybe it works fine, maybe it doesn't.  But I foresee having to expend
> work down the road because of this addition.

I assume admins are already doing this (via kill), so whether it is
supported or not, we should give folks a safe way to do this.  This
seems like basic functionality we should be giving admins, even if it
does take some work on our part.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 6 Apr 2004 11:41:28 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Function to kill backend
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I assume admins are already doing this (via kill), so whether it is
> supported or not, we should give folks a safe way to do this.

I don't think it's an open-and-shut decision as to whether people
actually *need* to do session kills (as opposed to query/transaction
kills).  The arguments presented so far are not convincing to my mind,
certainly not convincing enough to buy into a commitment to do whatever
it takes to support that.

The fact that people do it doesn't make it a good idea.

			regards, tom lane

Tue, 06 Apr 2004 11:48:25 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Function to kill backend 
Tom,

> I don't think it's an open-and-shut decision as to whether people
> actually *need* to do session kills (as opposed to query/transaction
> kills).  The arguments presented so far are not convincing to my mind,
> certainly not convincing enough to buy into a commitment to do whatever
> it takes to support that.

Hmmm ... well, I can make a real-world case from my supported apps for 
transaction/statement kills.   But my support for session kills is just 
hypothetical; any  time I've had to kill off sessions, it's because I had to 
shut the database down, and that's better done from the command line.

My web apps which need to manage the number of connections do it through their 
connection pool.

So I would vote for Yes on SIGINT by XID, but No on SIGTERM by PID, if Tom 
thinks there will be any significant support & troubleshooting involved for 
the latter.

Unless, of course, someone can give us a real business case that they have 
actually encountered in production.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Tue, 6 Apr 2004 09:22:19 -0700
Josh Berkus <josh@agliodbs.com>
Re: Function to kill backend
Josh Berkus wrote:
> Tom,
> 
> > I don't think it's an open-and-shut decision as to whether people
> > actually *need* to do session kills (as opposed to query/transaction
> > kills).  The arguments presented so far are not convincing to my mind,
> > certainly not convincing enough to buy into a commitment to do whatever
> > it takes to support that.
> 
> Hmmm ... well, I can make a real-world case from my supported apps for 
> transaction/statement kills.   But my support for session kills is just 
> hypothetical; any  time I've had to kill off sessions, it's because I had to 
> shut the database down, and that's better done from the command line.
> 
> My web apps which need to manage the number of connections do it through their 
> connection pool.
> 
> So I would vote for Yes on SIGINT by XID, but No on SIGTERM by PID, if Tom 
> thinks there will be any significant support & troubleshooting involved for 
> the latter.
> 
> Unless, of course, someone can give us a real business case that they have 
> actually encountered in production.

Someone already posted some pseudocode where they wanted to kill idle
backends, perhaps as part of connection pooling.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 6 Apr 2004 14:12:08 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Function to kill backend
Bruce,

> Someone already posted some pseudocode where they wanted to kill idle
> backends, perhaps as part of connection pooling.

I'm not talking about code.  I'm talking about a *reason*.

i.e.:  "I'm administrator of the blah-blah project.   We had a lot of trouble 
managing idle connections to PG because of blah-blah.   A function to kill 
off idle connctions would really help us becuase blah-blah-blah."

So far, all we've heard in favor of SIGTERM-by-PID are *hypothetical* cases.   
Now Tom's telling us that there is a real cost attached to having this 
feature.   Before we do it anyway, I want to be convinced that someone really 
needs it.  It is *not* our practice to add features "just because we can."

Otherwise, I'll stick by my assertion that idle connection management should 
be done in the middleware and NOT by psql.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Tue, 6 Apr 2004 11:48:28 -0700
Josh Berkus <josh@agliodbs.com>
Re: Function to kill backend
Josh Berkus wrote:
> Bruce,
> 
> > Someone already posted some pseudocode where they wanted to kill idle
> > backends, perhaps as part of connection pooling.
> 
> I'm not talking about code.  I'm talking about a *reason*.
> 
> i.e.:  "I'm administrator of the blah-blah project.   We had a lot of trouble 
> managing idle connections to PG because of blah-blah.   A function to kill 
> off idle connctions would really help us becuase blah-blah-blah."
> 
> So far, all we've heard in favor of SIGTERM-by-PID are *hypothetical* cases.   
> Now Tom's telling us that there is a real cost attached to having this 
> feature.   Before we do it anyway, I want to be convinced that someone really 
> needs it.  It is *not* our practice to add features "just because we can."
> 
> Otherwise, I'll stick by my assertion that idle connection management should 
> be done in the middleware and NOT by psql.

OK, you have a runaway report. You want to stop it.  Query cancel is
only going to stop the current query, and once you do that the next
query is fed in so there is no way to actually stop the report,
especially if the report is not being run from the same machine as the
server (you can't kill the report process).  How do you stop it without
SIGTERM?  You don't want to shut down the postmaster.

In fact, query cancel is actually more dangerous in this case because
unless the report is designed to handle statement failures, it might
just keep running and produce incorrect results because you canceled
some random queries in the report.  (I am thinking specifically of a
psql batch job here.)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 6 Apr 2004 14:55:32 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Function to kill backend
Bruce,

> OK, you have a runaway report. You want to stop it.  Query cancel is
> only going to stop the current query, and once you do that the next
> query is fed in so there is no way to actually stop the report,
> especially if the report is not being run from the same machine as the
> server (you can't kill the report process).  How do you stop it without
> SIGTERM?  You don't want to shut down the postmaster.

Hmmm ... but, at least in the case of my apps, killing the PG connection 
wouldn't fix things.   Most apps I work on are designed to detect connection 
failure and reconnect.   I suspect that most platforms that use connection 
pooling are the same.  So your case would only work if you actually blocked 
all connections from that host -- not a capability we'd discussed.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Tue, 6 Apr 2004 12:10:10 -0700
Josh Berkus <josh@agliodbs.com>
Re: Function to kill backend
Josh Berkus <josh@agliodbs.com> writes:
> So I would vote for Yes on SIGINT by XID, but No on SIGTERM by PID, if Tom 
> thinks there will be any significant support & troubleshooting involved for 
> the latter.

Quite honestly, I don't know.  We know that some people have done manual
SIGTERMs and not been burnt; and I'm not aware of any reason why it
wouldn't work; but I don't think it's well enough tested to be sure that
it will work.  The sort of problem that I fear could arise is analogous
to the problem with kill -9'ing the postmaster: sure, the process is
gone and the database on disk is okay, but there might be resource leaks
or other problems left behind in shared memory.  We would not see such
problems in normal use because SIGTERM is associated with complete
database shutdown and release of shared memory.  With retail SIGTERM and
leaving the database up, though, it's a whole new ballgame and the
cleanup requirements become much stricter.

So like I say, I'm hesitant to buy into supporting this without a fairly
convincing argument that it's really needed.

			regards, tom lane

Tue, 06 Apr 2004 15:23:21 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Function to kill backend 
Josh Berkus wrote:
> Bruce,
> 
> > OK, you have a runaway report. You want to stop it.  Query cancel is
> > only going to stop the current query, and once you do that the next
> > query is fed in so there is no way to actually stop the report,
> > especially if the report is not being run from the same machine as the
> > server (you can't kill the report process).  How do you stop it without
> > SIGTERM?  You don't want to shut down the postmaster.
> 
> Hmmm ... but, at least in the case of my apps, killing the PG connection 
> wouldn't fix things.   Most apps I work on are designed to detect connection 
> failure and reconnect.   I suspect that most platforms that use connection 
> pooling are the same.  So your case would only work if you actually blocked 
> all connections from that host -- not a capability we'd discussed.

I don't think most apps reconnect on disconnect, except maybe pooled
connections where you don't expect your state to be stable between
connections.  Certainly most reports can't just reconnect and keep
going.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 6 Apr 2004 15:36:35 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Function to kill backend
> Otherwise, I'll stick by my assertion that idle connection management should 
> be done in the middleware and NOT by psql.

Perhaps it should be, but as PostgreSQL picks up more and more vendor
applications this is difficult for the person administrating the
database.

Consider a 3rd party application which has the issue but the support
contract is such that you cannot affect the application itself (their
support staff deals with it).

If you need connections in the database for other applications and this
3rd party program is idling on several slots...

-- 
Rod Taylor <rbt [at] rbt [dot] ca>

Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.asc
Tue, 06 Apr 2004 15:38:26 -0400
Rod Taylor <rbt@rbt.ca>
Re: Function to kill backend
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> OK, you have a runaway report. You want to stop it.  Query cancel is
> only going to stop the current query, and once you do that the next
> query is fed in so there is no way to actually stop the report,
> especially if the report is not being run from the same machine as the
> server (you can't kill the report process).

> I don't think most apps reconnect on disconnect, except maybe pooled
> connections where you don't expect your state to be stable between
> connections.  Certainly most reports can't just reconnect and keep
> going.

You're hypothecating a report generator that can recover from failed
queries, but not a failed connection?  Seems a rather contrived case.
Stupid apps are most likely gonna curl up and die on any unexpected
error (which is what the query cancel would look like to them).  Smart
apps may try harder to recover than you think.

			regards, tom lane

Tue, 06 Apr 2004 15:39:59 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Function to kill backend 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > OK, you have a runaway report. You want to stop it.  Query cancel is
> > only going to stop the current query, and once you do that the next
> > query is fed in so there is no way to actually stop the report,
> > especially if the report is not being run from the same machine as the
> > server (you can't kill the report process).
> 
> > I don't think most apps reconnect on disconnect, except maybe pooled
> > connections where you don't expect your state to be stable between
> > connections.  Certainly most reports can't just reconnect and keep
> > going.
> 
> You're hypothecating a report generator that can recover from failed
> queries, but not a failed connection?  Seems a rather contrived case.
> Stupid apps are most likely gonna curl up and die on any unexpected
> error (which is what the query cancel would look like to them).  Smart
> apps may try harder to recover than you think.

I figured some reports just continue on failed queries.  Is that
accurate?  I don't know, but I know a psql script will continue, no?
Will psql return an error code on exit from cancel?  I think it does but
am not sure.

First people objected to this on security grounds, now that those were
beat down, we have use-case complaints.  Not having a way to kill
backends is like having no way to kill a process except rebooting the
server.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 6 Apr 2004 15:43:58 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Function to kill backend
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Not having a way to kill backends is like having no way to kill a
> process except rebooting the server.

Some people think that making a database hard to kill is a good thing.

			regards, tom lane

Tue, 06 Apr 2004 15:49:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Function to kill backend 
On Tue, 2004-04-06 at 15:23, Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
> > So I would vote for Yes on SIGINT by XID, but No on SIGTERM by PID, if Tom 
> > thinks there will be any significant support & troubleshooting involved for 
> > the latter.
> 
> So like I say, I'm hesitant to buy into supporting this without a fairly
> convincing argument that it's really needed.

It doesn't necessarily have to be a SIGTERM. The goal is to get rid of
unwanted idlers (connections). Could SIGINT be extended with a command
telling the daemon to shutdown or rollback the transaction as requested?



Tue, 06 Apr 2004 15:58:28 -0400
Rod Taylor <pg@rbt.ca>
Re: Function to kill backend
On Tue, 2004-04-06 at 15:10, Josh Berkus wrote:
> Bruce,
> 
> > OK, you have a runaway report. You want to stop it.  Query cancel is
> > only going to stop the current query, and once you do that the next
> > query is fed in so there is no way to actually stop the report,
> > especially if the report is not being run from the same machine as the
> > server (you can't kill the report process).  How do you stop it without
> > SIGTERM?  You don't want to shut down the postmaster.
> 
> Hmmm ... but, at least in the case of my apps, killing the PG connection 
> wouldn't fix things.   Most apps I work on are designed to detect connection 
> failure and reconnect.   I suspect that most platforms that use connection 
> pooling are the same.  So your case would only work if you actually blocked 
> all connections from that host -- not a capability we'd discussed.

That would be a likely second step (go into pg_hba to block). But you
still have to get rid of the idlers at some point.


Tue, 06 Apr 2004 15:59:50 -0400
Rod Taylor <pg@rbt.ca>
Re: Function to kill backend
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Not having a way to kill backends is like having no way to kill a
> > process except rebooting the server.
> 
> Some people think that making a database hard to kill is a good thing.

I can't argue with that.  :-)

I am researching the SIGTERM processing right now and will post in a few
minutes.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 6 Apr 2004 16:02:39 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Function to kill backend
Rod Taylor wrote:
> On Tue, 2004-04-06 at 15:23, Tom Lane wrote:
> > Josh Berkus <josh@agliodbs.com> writes:
> > > So I would vote for Yes on SIGINT by XID, but No on SIGTERM by PID, if Tom 
> > > thinks there will be any significant support & troubleshooting involved for 
> > > the latter.
> > 
> > So like I say, I'm hesitant to buy into supporting this without a fairly
> > convincing argument that it's really needed.
> 
> It doesn't necessarily have to be a SIGTERM. The goal is to get rid of
> unwanted idlers (connections). Could SIGINT be extended with a command
> telling the daemon to shutdown or rollback the transaction as requested?

Nope, a signal is just a signal with no other info passed.  We could add
it, but it would be more code.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 6 Apr 2004 16:03:21 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Function to kill backend

The "function to kill backend" seems no longer in doubt, but the
*reason* is still blurred, other than we don't want to bring down the
postmaster to do this.
So far, reasons given have been:
1. to kill idlers
2. to kill runaway queries/statements, which has transaction
implications

I'd like to be able to do both of those, though they are fairly
different situations, perhaps with different implementations.

>Tom Lane writes
> Some people think that making a database hard to kill is a good thing.

I'm with Tom on this. I'm working on PITR...what happens when you shut
down backends uncleanly...how will we test all the possible event/states
to ensure xlog history doesn't get broken? Please lets work on a
"humane" approach to handling offending sessions...IMHO the various
signals mentioned are merely blunt instruments; we have been exhorted
daily not to "kill the postmaster", now we seem to be suggesting that we
allow that to happen to backends.  Isn't there a better way...?

Other RDBMSs support the ability to terminate specific sessions, but
none of them to my knowledge offer this facility by means of an external
executable, which has many implications - how would you know (prove) it
had been used? what security protection does the DBA have over its use?

The way forward seems safest if this is a command, not an external
executable. e.g. ALTER SYSTEM STOP BACKEND <x>. That way we have control
over the implementation/porting, security, logging/audit. Anybody that
wants to can then wrap that in a script if they choose.

Internally, we could then implement it however we chose.

Best Regards, Simon Riggs


Tue, 6 Apr 2004 22:09:27 +0100
"Simon Riggs" <simon@2ndquadrant.com>
Re: Function to kill backend 
Simon Riggs wrote:
> 
> The "function to kill backend" seems no longer in doubt, but the
> *reason* is still blurred, other than we don't want to bring down the
> postmaster to do this.
> So far, reasons given have been:
> 1. to kill idlers
> 2. to kill runaway queries/statements, which has transaction
> implications
> 
> I'd like to be able to do both of those, though they are fairly
> different situations, perhaps with different implementations.
> 
> >Tom Lane writes
> > Some people think that making a database hard to kill is a good thing.
> 
> I'm with Tom on this. I'm working on PITR...what happens when you shut
> down backends uncleanly...how will we test all the possible event/states
> to ensure xlog history doesn't get broken? Please lets work on a
> "humane" approach to handling offending sessions...IMHO the various
> signals mentioned are merely blunt instruments; we have been exhorted
> daily not to "kill the postmaster", now we seem to be suggesting that we
> allow that to happen to backends.  Isn't there a better way...?
> 
> Other RDBMSs support the ability to terminate specific sessions, but
> none of them to my knowledge offer this facility by means of an external
> executable, which has many implications - how would you know (prove) it
> had been used? what security protection does the DBA have over its use?
> 
> The way forward seems safest if this is a command, not an external
> executable. e.g. ALTER SYSTEM STOP BACKEND <x>. That way we have control
> over the implementation/porting, security, logging/audit. Anybody that
> wants to can then wrap that in a script if they choose.
> 
> Internally, we could then implement it however we chose.

The current plan is to create server-side functions to do this.  We
believe sending a SIGTERM to a backend via kill already has this effect.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 6 Apr 2004 18:00:16 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Function to kill backend

>Bruce Momjian
> Simon Riggs wrote:
> > The "function to kill backend" seems no longer in doubt, but the
> > *reason* is still blurred, other than we don't want to
> bring down the
> > postmaster to do this.
> > So far, reasons given have been:
> > 1. to kill idlers
> > 2. to kill runaway queries/statements, which has transaction
> > implications
> >
> > I'd like to be able to do both of those, though they are fairly
> > different situations, perhaps with different implementations.
> >
> > >Tom Lane writes
> > > Some people think that making a database hard to kill is
> a good thing.
> >
> > I'm with Tom on this. I'm working on PITR...what happens
> when you shut
> > down backends uncleanly...how will we test all the possible
> event/states
> > to ensure xlog history doesn't get broken? Please lets work on a
> > "humane" approach to handling offending sessions...IMHO the various
> > signals mentioned are merely blunt instruments; we have
> been exhorted
> > daily not to "kill the postmaster", now we seem to be
> suggesting that we
> > allow that to happen to backends.  Isn't there a better way...?
> >
> > Other RDBMSs support the ability to terminate specific sessions, but
> > none of them to my knowledge offer this facility by means
> of an external
> > executable, which has many implications - how would you
> know (prove) it
> > had been used? what security protection does the DBA have
> over its use?
> >
> > The way forward seems safest if this is a command, not an external
> > executable. e.g. ALTER SYSTEM STOP BACKEND <x>. That way we
> have control
> > over the implementation/porting, security, logging/audit.
> Anybody that
> > wants to can then wrap that in a script if they choose.
> >
> > Internally, we could then implement it however we chose.
>
> The current plan is to create server-side functions to do this.  We
> believe sending a SIGTERM to a backend via kill already has
> this effect.

If I understand you then, you believe you have found a way to short cut
implementing the server-side function.

Even if the primary behaviour is indeed exactly similar, are the
secondary and subsequent behaviours similar also?

Does your plan allow for:
i) logging the activity (both the send and the effect of receiving it)
ii) full security control of the facility
other secondary behaviours...

Forgive me if I become over-protective on robustness issues...

Best Regards, Simon


Tue, 6 Apr 2004 23:19:21 +0100
"Simon Riggs" <simon@2ndquadrant.com>
Re: Function to kill backend
Simon Riggs wrote:
> > > The way forward seems safest if this is a command, not an external
> > > executable. e.g. ALTER SYSTEM STOP BACKEND <x>. That way we
> > have control
> > > over the implementation/porting, security, logging/audit.
> > Anybody that
> > > wants to can then wrap that in a script if they choose.
> > >
> > > Internally, we could then implement it however we chose.
> >
> > The current plan is to create server-side functions to do this.  We
> > believe sending a SIGTERM to a backend via kill already has
> > this effect.
> 
> If I understand you then, you believe you have found a way to short cut
> implementing the server-side function.

No, we are going to create server-side function that mimick SIGINT
(though using xid, we can ignore if the xid change), and SIGTERM. 
Server-side functions are required for clients not running on the
server.


> Even if the primary behaviour is indeed exactly similar, are the
> secondary and subsequent behaviours similar also?
> 
You mean calling them several times?  Well, SIGTERM will terminate, and
SIGINT will keep canceling xid's, though you have to update the xid to
the newer xid because you canceled the old one.

> Does your plan allow for:
> i) logging the activity (both the send and the effect of receiving it)
> ii) full security control of the facility
> other secondary behaviours...
> 
> Forgive me if I become over-protective on robustness issues...

No, these are super-user-only commands.  Not much more we can do on that
front.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 7 Apr 2004 00:05:09 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Function to kill backend
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Not having a way to kill backends is like having no way to kill a
> > process except rebooting the server.
> 
> Some people think that making a database hard to kill is a good thing.

Sure.  But we're not talking about taking down the whole database, we're
talking about taking down a connection.

Killing the database is the equivalent of killing the OS.  It should be
hard to do either.  But it should be easy to kill a process on an OS if
you have the right permissions, and similiarly it should be easy to kill
a connection to the database if you have the right permissions.


With respect to nested transactions and other things that might make
properly shutting down difficult, it seems to me that the SIGINT case is
actually a harder case to deal with.  Why?  Because for the SIGTERM case,
you basically have to do whatever is done whenever the connection itself
drops.  If we can't handle the connection itself dropping out arbitrarily
then we have more serious problems than just how to handle SIGTERM.  :-)
But for SIGINT you have to decide whether to just abort the innermost
transaction or the outermost one, and if it's the outermost one you have
to abort then you have to provide the mechanism for it -- something that
you might not have to deal with otherwise.

So it seems that handling SIGTERM might actually be easy: you have the
signal handler close the backend's side of the connection and let the
connection-dropping logic kick in automatically, no?


Thoughts?  Am I completely off my rocker here?  :-)



-- 
Kevin Brown					      kevin@sysexperts.com

Thu, 8 Apr 2004 18:52:59 -0700
Kevin Brown <kevin@sysexperts.com>
Re: Function to kill backend