Bruce Momjian wrote:
>pgman wrote:
>
>
>>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.
>>
>>
>
>Tom, if you have concerns about SIGTERM while other backends keep
>running, would you share those. (Holding locks, shared memory?) I
>looked at die(), and it seemed pretty safe to me. It just sets some
>variables and returns. It is not like quickdie that calls exit().
>
>If there is a problem, maybe we can fix it, or perhap have the kill
>function use SIGINT, then wait for the query to cancel, then SIGTERM.
>
>
>
One other option to consider while your looking at killing off children
as an admin is the ability to switch the database to single user mode
for the admin via psql or other connection interface. In essence
shutdown all other transactions and session and limit it to only one so
that maintenance issues (reindexing, etc) can be done remotely and do
not require shell access on the host machine.
I think, ultimately, this may be what is being asked for: a way to
manage postgresql internally via SQL or other functions rather than
relying on user access to the machine (ie sending signals to
processes). This is what some people have wanted to do with managing
the connection settings in pg_hba.conf via a system table rather than or
in addition to a file.