Re: smart shutdown at end of transaction (was: Default mode for shutdown)

On Sun, Apr 29, 2012 at 5:41 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> I think we only need one new mode, "shutdown when transactions are
>> finished" should only shutdown when all types of transaction are
>> complete. For people that don't use prepared transactions the
>> difference is irrelevant. For people that do use prepared
>> transactions, I can't imagine they would want a new setting that ends
>> with aborted transactions, since that isn't any different to a fast
>> shutdown.
>
> That sounds reasonable at first blush.  Implementing it might be
> trickier than you think though, since (despite Peter's opinion) the
> prepared xacts are not associated with any particular session, and the
> postmaster itself doesn't know they are there.  What's more, if
> individual sessions are told to commit hara-kiri as soon as they are not
> in a transaction, there soon won't be any surviving session in which the
> TM could issue a COMMIT PREPARED.
>
> I think the only way this could be made to fly would be if the TM could
> set a session state that indicates "I'm a TM session, don't kill me
> until all prepared transactions are gone".  Which might be problematic
> from a security standpoint, if random users could use it to proof
> themselves against getting kicked out.  We could make it SUSET but then
> TMs would have to run as superuser, which seems a bit less than
> desirable.

I think an explicit state is overkill and has other problems as you say.

> On the whole it is not apparent to me that we really need a mode in
> which shutdown waits for prepared transactions to flush out; and I would
> definitely not be in favor of it being the default.  I think that that
> would make prepared transactions an even bigger foot-gun than they are
> now.  Just think: you say "pg_ctl stop", and the server promptly kicks
> off all your users and won't let any more in, but doesn't actually shut
> down.  You may not know why, and even if you do, you can't connect to do
> something about it.  Eventually you give up and issue shutdown fast,
> cursing whoever designed that misbegotten behavior.

Waiting too long is clearly a foot fun, as you say.

But if you just issued PREPARE on a session, its more than likely that
this will be followed almost immediately by a COMMIT. Simply waiting
is a good indication, and some reasonable time like 10 seconds is fine
in determining whether that COMMIT will arrive, or not.

This only matters on a shutdown. If its a restart, we can shutdown
after a PREPARE because as soon as we are back up again the TM can
issue the COMMIT.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services