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

On Fri, Apr 27, 2012 at 19:42, Robert Haas <robertmhaas@gmail.com> wrote:

On Wed, Dec 15, 2010 at 10:11 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

It occurs to me that we may need a new mode, which disconnects sessions
that are not in a transaction (or as soon as they are) but leaves
in-progress transactions alone; this could be the new default.  Of
course, this is much more difficult to implement than the current modes.

This idea appeared to have some support.  I'd like to suggest that we
take this a step further.  Instead of adding a fourth mode, I'd like
to suggest that we redefine "smart" to have the behavior described

+1762329!

above.  This is based on the theory that (1) people who like smart
shutdown like it because it allows currently-running transactions to
complete without error, and will find it acceptable to have idle
transactions terminated immediately and other sessions terminated

Uh, I don't think it's ok to terminate an idle transaction
immediately. An idle *session* is ok, though - maybe that's what you
mean?

Because every transaction that's *doing* multiple things will be idle
for milliseconds every now and then.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Fri, Apr 27, 2012 at 1:46 PM, Magnus Hagander <magnus@hagander.net> wrote:

On Fri, Apr 27, 2012 at 19:42, Robert Haas <robertmhaas@gmail.com> wrote:

On Wed, Dec 15, 2010 at 10:11 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

It occurs to me that we may need a new mode, which disconnects sessions
that are not in a transaction (or as soon as they are) but leaves
in-progress transactions alone; this could be the new default.  Of
course, this is much more difficult to implement than the current modes.

This idea appeared to have some support.  I'd like to suggest that we
take this a step further.  Instead of adding a fourth mode, I'd like
to suggest that we redefine "smart" to have the behavior described

+1762329!

Thanks. :-)

above.  This is based on the theory that (1) people who like smart
shutdown like it because it allows currently-running transactions to
complete without error, and will find it acceptable to have idle
transactions terminated immediately and other sessions terminated

Uh, I don't think it's ok to terminate an idle transaction
immediately. An idle *session* is ok, though - maybe that's what you
mean?

Yes, exactly. What the patch does is arrange things so that, when
smart shutdown is requested, we terminate each session as soon as it
is both (1) idle and (2) not in a transaction.

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

Robert Haas <robertmhaas@gmail.com> writes:

On Wed, Dec 15, 2010 at 10:11 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

It occurs to me that we may need a new mode, which disconnects sessions
that are not in a transaction (or as soon as they are) but leaves
in-progress transactions alone; this could be the new default. Of
course, this is much more difficult to implement than the current modes.

This idea appeared to have some support. I'd like to suggest that we
take this a step further. Instead of adding a fourth mode, I'd like
to suggest that we redefine "smart" to have the behavior described
above.

No, I'm not happy with that. Smart shutdown is defined to not affect
current sessions. I'm fine with having a fourth mode that acts as you
suggest (and, probably, even with making it the default); but not with
taking away a behavior that people may well be relying on.

This is based on the theory that (1) people who like smart
shutdown like it because it allows currently-running transactions to
complete without error,

I think they like it because it allows currently-running *sessions*
to complete without error. You have no real basis for asserting that
relocating that goalpost won't change the game.

regards, tom lane

On Fri, Apr 27, 2012 at 7:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Wed, Dec 15, 2010 at 10:11 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

It occurs to me that we may need a new mode, which disconnects sessions
that are not in a transaction (or as soon as they are) but leaves
in-progress transactions alone; this could be the new default.  Of
course, this is much more difficult to implement than the current modes.

This idea appeared to have some support.  I'd like to suggest that we
take this a step further.  Instead of adding a fourth mode, I'd like
to suggest that we redefine "smart" to have the behavior described
above.

No, I'm not happy with that.  Smart shutdown is defined to not affect
current sessions.  I'm fine with having a fourth mode that acts as you
suggest (and, probably, even with making it the default); but not with
taking away a behavior that people may well be relying on.

Agreed, but not sure what to call the new mode: "smarter"?

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

Hi,

On Friday, April 27, 2012 07:42:59 PM Robert Haas wrote:

On Wed, Dec 15, 2010 at 10:11 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

It occurs to me that we may need a new mode, which disconnects sessions
that are not in a transaction (or as soon as they are) but leaves
in-progress transactions alone; this could be the new default. Of
course, this is much more difficult to implement than the current modes.

This idea appeared to have some support. I'd like to suggest that we
take this a step further. Instead of adding a fourth mode, I'd like
to suggest that we redefine "smart" to have the behavior described
above. This is based on the theory that (1) people who like smart
shutdown like it because it allows currently-running transactions to
complete without error, and will find it acceptable to have idle
transactions terminated immediately and other sessions terminated
after the command completes; and (2) people who dislike smart shutdown
(such as me) dislike it primarily because a completely idle session
that someone's forgotten to close can prevent shutdown indefinitely.
Either part of this theory could be wrong, of course, although I'm
pretty sure #2 holds for me personally at the least.

I think the current smart mode is rather useful. There is quite some stuff
that you cannot do inside a transaction - or it doesn't make sense - which
still needs to shutdown gracefully. E.g. transaction managers.

Andres

On Friday, April 27, 2012 08:38:10 PM Simon Riggs wrote:

On Fri, Apr 27, 2012 at 7:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Wed, Dec 15, 2010 at 10:11 AM, Alvaro Herrera

<alvherre@commandprompt.com> wrote:

It occurs to me that we may need a new mode, which disconnects sessions
that are not in a transaction (or as soon as they are) but leaves
in-progress transactions alone; this could be the new default. Of
course, this is much more difficult to implement than the current
modes.

This idea appeared to have some support. I'd like to suggest that we
take this a step further. Instead of adding a fourth mode, I'd like
to suggest that we redefine "smart" to have the behavior described
above.

No, I'm not happy with that. Smart shutdown is defined to not affect
current sessions. I'm fine with having a fourth mode that acts as you
suggest (and, probably, even with making it the default); but not with
taking away a behavior that people may well be relying on.

Agreed, but not sure what to call the new mode: "smarter"?

graceful?

Andres

Simon Riggs <simon@2ndQuadrant.com> writes:

On Fri, Apr 27, 2012 at 7:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

No, I'm not happy with that. Smart shutdown is defined to not affect
current sessions. I'm fine with having a fourth mode that acts as you
suggest (and, probably, even with making it the default); but not with
taking away a behavior that people may well be relying on.

Agreed, but not sure what to call the new mode: "smarter"?

I'm not necessarily opposed to commandeering the name "smart" for the
new behavior, so that what we have to find a name for is the old "smart"
behavior. How about

slow - allow existing sessions to finish (old "smart")
smart - allow existing transactions to finish (new)
fast - kill active queries
immediate - unclean shutdown

regards, tom lane

On Fri, Apr 27, 2012 at 20:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

On Fri, Apr 27, 2012 at 7:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

No, I'm not happy with that.  Smart shutdown is defined to not affect
current sessions.  I'm fine with having a fourth mode that acts as you
suggest (and, probably, even with making it the default); but not with
taking away a behavior that people may well be relying on.

Agreed, but not sure what to call the new mode: "smarter"?

I'm not necessarily opposed to commandeering the name "smart" for the
new behavior, so that what we have to find a name for is the old "smart"
behavior.  How about

       slow    - allow existing sessions to finish (old "smart")

How about "wait" instead of "slow"?

       smart   - allow existing transactions to finish (new)

and still default, right?

       fast    - kill active queries
       immediate - unclean shutdown

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Magnus Hagander <magnus@hagander.net> writes:

On Fri, Apr 27, 2012 at 20:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I'm not necessarily opposed to commandeering the name "smart" for the
new behavior, so that what we have to find a name for is the old "smart"
behavior. How about

slow - allow existing sessions to finish (old "smart")

How about "wait" instead of "slow"?

I kinda liked "slow" vs "fast", but if you think that's too cute ...
("wait" doesn't seem very good, though, since all these except immediate
are waiting, just for different things.)

smart - allow existing transactions to finish (new)

and still default, right?

Right.

fast - kill active queries
immediate - unclean shutdown

regards, tom lane

On Fri, Apr 27, 2012 at 2:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

This idea appeared to have some support.  I'd like to suggest that we
take this a step further.  Instead of adding a fourth mode, I'd like
to suggest that we redefine "smart" to have the behavior described
above.

No, I'm not happy with that.  Smart shutdown is defined to not affect
current sessions.  I'm fine with having a fourth mode that acts as you
suggest (and, probably, even with making it the default); but not with
taking away a behavior that people may well be relying on.

I think there is no point at all in having a discussion about this
unless we can first agree that the overwhelming majority of people who
have commented on this issue on this list are unhappy with the current
default behavior. If we are not going to change the default behavior,
then there is zero point in talking about this. So I am nervous about
your use of the word "probably", because I do not want to do a bunch
of work on this just to add a fourth shutdown mode without changing
the default to something that does not suck. I would like to get some
agreement that we ARE going to change the default behavior, and then
we can argue about what exactly we're going to change it to.

This is based on the theory that (1) people who like smart
shutdown like it because it allows currently-running transactions to
complete without error,

I think they like it because it allows currently-running *sessions*
to complete without error.  You have no real basis for asserting that
relocating that goalpost won't change the game.

I'm not asserting that. What I am asserting is that the vast majority
of users will consider the revised game to be more fun than the
original one.

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

On Fri, Apr 27, 2012 at 2:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I'm not necessarily opposed to commandeering the name "smart" for the
new behavior, so that what we have to find a name for is the old "smart"
behavior.  How about

       slow    - allow existing sessions to finish (old "smart")
       smart   - allow existing transactions to finish (new)
       fast    - kill active queries
       immediate - unclean shutdown

I could live with that. Really, I'd like to have fast just be the
default. But the above compromise would still be a big improvement
over what we have now, assuming the new smart becomes the default.

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

On 27.04.2012 21:56, Tom Lane wrote:

Magnus Hagander<magnus@hagander.net> writes:

On Fri, Apr 27, 2012 at 20:48, Tom Lane<tgl@sss.pgh.pa.us> wrote:

I'm not necessarily opposed to commandeering the name "smart" for the
new behavior, so that what we have to find a name for is the old "smart"
behavior. How about

slow - allow existing sessions to finish (old "smart")

How about "wait" instead of "slow"?

I kinda liked "slow" vs "fast", but if you think that's too cute ...
("wait" doesn't seem very good, though, since all these except immediate
are waiting, just for different things.)

All the modes indeed wait (except for immediate), so I think it would
make sense to define the modes in terms of *what* they wait for.

wait sessions - allow existing sessions to finish (old "smart")
wait transactions - allow existing transactions to finish (new)
wait checkpoint - kill active queries
wait none - unclean shutdown

Hmm, the latter two are perhaps a bit confusing. So maybe:

wait_sessions - allow existing sessions to finish (old "smart")
wait_transactions - allow existing transactions to finish (new)
fast - kill active queries
immediate - unclean shutdown

Just thinking out loud here..

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Fri, Apr 27, 2012 at 3:00 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Fri, Apr 27, 2012 at 2:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I'm not necessarily opposed to commandeering the name "smart" for the
new behavior, so that what we have to find a name for is the old "smart"
behavior.  How about

       slow    - allow existing sessions to finish (old "smart")
       smart   - allow existing transactions to finish (new)
       fast    - kill active queries
       immediate - unclean shutdown

I could live with that.  Really, I'd like to have fast just be the
default.  But the above compromise would still be a big improvement
over what we have now, assuming the new smart becomes the default.

So right now, we have a mapping from signals to shutdown types that
looks like this:

[Current] SIGTERM -> smart, SIGINT -> fast, SIGQUIT -> immediate

It seems we need another signal for the new mode, and the obvious
candidate is SIGUSR2. But what shall the mapping look like?

[Choice #1] SIGUSR2 -> slow, SIGTERM -> smart, SIGINT -> fast, SIGQUIT
-> immediate
[Choice #2] SIGTERM -> slow, SIGUSR2 -> smart, SIGINT -> fast, SIGQUIT
-> immediate

In other words, should we retain the existing behavior for SIGTERM and
make SIGUSR2 have the new behavior (choice #2)? Or shall we preserve
the invariant that SIGTERM invokes the default shutdown mode, and move
the current default behavior off into SIGUSR2 land (choice #1)?

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

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:

Just thinking out loud here..

In the spirit of kicking around ideas...

For those writing service scripts where you want a time limit on how
long a stop can take, so that the service script doesn't prevent OS
shutdown within a bounded time, it would also be nice to add an
escalation time limit; so if there isn't a shutdown withing k
seconds at one level it goes to the next. If the building is on
fire and you need to power down all equipment before the fire
department cuts power and starts spraying water (a situation we had
at a courthouse a year or two ago), you really don't want the OS
waiting for anything for more than a limited number of seconds
before escalating to immediate. We do that in our sh scripts now,
by using kill and sleep instead of trusting pg_ctl, but it seems
like it would be better to have pg_ctl know how to do that.

maybe?:

--escalate-after=seconds

-Kevin

On fre, 2012-04-27 at 20:39 +0200, Andres Freund wrote:

I think the current smart mode is rather useful. There is quite some
stuff that you cannot do inside a transaction - or it doesn't make
sense - which still needs to shutdown gracefully. E.g. transaction
managers.

Could you elaborate on that? What would happen to the transaction
manager if you terminate any idle, not-in-a-transaction database backend
sessions it has established?

On Friday, April 27, 2012 10:17:59 PM Peter Eisentraut wrote:

On fre, 2012-04-27 at 20:39 +0200, Andres Freund wrote:

I think the current smart mode is rather useful. There is quite some
stuff that you cannot do inside a transaction - or it doesn't make
sense - which still needs to shutdown gracefully. E.g. transaction
managers.

Could you elaborate on that? What would happen to the transaction
manager if you terminate any idle, not-in-a-transaction database backend
sessions it has established?

In the few cases where I investigated it TMs don't use transactions themselves
(which I think is correct, they don't need them), so terminating any idle
session - which the TM would appear as, as its not using txns - would leave
prepared transactions in a limbo state till the database is up again, instead
of waiting till all prepared transactions are either aborted or committed. It
may also choose to coordinate to abort all transactions, but all that is hard
if the database shuts you out.
I actually also have co-maintained other software where some processes have an
idle connection open on which some shutdown stuff will happen. Obviously all
those will need to handle the case where the connection was aborted, but that
may result in suboptimal behaviour. Requiring such processes to keep open a
transaction doesn't seem to be a good design choice in the pg world.

Andres

On Fri, Apr 27, 2012 at 1:57 PM, Robert Haas <robertmhaas@gmail.com> wrote:

I think there is no point at all in having a discussion about this
unless we can first agree that the overwhelming majority of people who
have commented on this issue on this list are unhappy with the current
default behavior.

count me in. the current behavior sucks.

merlin

Robert Haas <robertmhaas@gmail.com> writes:

It seems we need another signal for the new mode, and the obvious
candidate is SIGUSR2. But what shall the mapping look like?

[Choice #1] SIGUSR2 -> slow, SIGTERM -> smart, SIGINT -> fast, SIGQUIT
-> immediate
[Choice #2] SIGTERM -> slow, SIGUSR2 -> smart, SIGINT -> fast, SIGQUIT
-> immediate

SIGTERM needs to correspond to a fairly aggressive shutdown mode,
since (at least on some systems) init will send that during the system
shutdown sequence, shortly before escalating to SIGKILL. So I think
choice #2 is not sensible at all.

If we were willing to consider wholesale breakage of any scripts that
send these signals directly, I'd almost consider that it should be
SIGUSR2, SIGINT, SIGTERM, SIGQUIT. But that might be more churn than
we want. Keeping SIGTERM attached to the default/"smart" shutdown mode
seems like a reasonable compromise.

regards, tom lane

On fre, 2012-04-27 at 22:30 +0200, Andres Freund wrote:

In the few cases where I investigated it TMs don't use transactions
themselves (which I think is correct, they don't need them), so
terminating any idle session - which the TM would appear as, as its
not using txns - would leave prepared transactions in a limbo state
till the database is up again, instead of waiting till all prepared
transactions are either aborted or committed. It may also choose to
coordinate to abort all transactions, but all that is hard if the
database shuts you out.

This would lead to another shutdown mode, one that terminates idle
sessions unless they have prepared transactions. That could be useful.