Re: Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

On Sat, Mar 17, 2012 at 8:28 PM, Daniel Farina <daniel@heroku.com> wrote:
> This thread evolved out of an attempt to implement
> pg_terminate_backend for non-superusers.  I thought -- probably
> erroneously -- that the major objection to that was the known
> possibility of a PID-cycling race condition, whereby a signal could be
> misdirected, in the case of terminate_backend, SIGTERM.  So now this
> fork of the thread is about fixing these unlikely races, and then
> passing administration requests (such as "query cancel" or "die" ) as
> out-of-band information via SIGUSR1, just like how LISTEN/NOTIFY and
> conflict signals are passed.
>
> To prevent ambiguity, I am using a new special number -- a 'SessionId'
> -- that is guaranteed unique to all backends ever created during the
> uptime of a database.  This number is currently implemented in a way
> that is guessable (so it cannot be accepted from external sources),
> but I actually think it may be even more useful for a number of other
> uses if given a non-guessable form (like cancellation keys).  In this
> respect it would fulfill pretty much the same purposes as the notion
> of a "session" seen on the web.

It's after midnight here so maybe I'm being slow, but I don't
understand what problem the SessionId solves.  ISTM that you could
solve the problem like this:

1. Acquire ProcArrayLock in exclusive mode, to keep the set of PGPROCs
from changing.
2. Search for the target PGPROC by PID; when you find it, set a bit in
the PGPROC indicating that you want it to cancel/die/whatever.
3. Remember the PID.
4. Release ProcArrayLock.
5. Send SIGUSR1.

If the PGPROC gets recycled between 4 and 5, the target backend will
find the relevant bits no longer set, and will do nothing.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company