Request: pg_cancel_backend variant that handles 'idle in transaction' sessions

On Sat, Oct 31, 2015 at 2:50 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

Idle hanging transactions from poorly written applications are the
bane of my existence. Several months back one of them took down one
of hour production websites for several hours.

Unfortunately, the only way to deal with them is to terminate the
backend which is heavy handed and in some cases causes further damage.
Something like pg_cancel_transaction(pid) would be nice; it would
end the transaction regardless if in an actual statement or not.

Why pg_cancel_backend(pid) is not sufficient for the above use case?
Basically you want to rollback current transaction, I think that can be
achieved by pg_cancel_backend.

Similarly, transaction_timeout would be a lot more effective than
statement_timeout.

I think here by transaction_timeout you mean to say cancel all
transactions that are idle for transaction_timeout time. So it is better
to call it as transaction_idle_timeout. Having said that I am not sure
if holding such a connection is meaningful either because I think there
is high probablity that user of such a session might not perform any further
action for a long time, so why not have idle_timeout to indicate the
termination
of session if it is idle for idle_timeout time.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Sat, Oct 31, 2015 at 5:50 AM, Amit Kapila <amit.kapila16@gmail.com>
wrote:

On Sat, Oct 31, 2015 at 2:50 AM, Merlin Moncure <mmoncure@gmail.com>
wrote:

Idle hanging transactions from poorly written applications are the
bane of my existence. Several months back one of them took down one
of hour production websites for several hours.

Unfortunately, the only way to deal with them is to terminate the
backend which is heavy handed and in some cases causes further damage.
Something like pg_cancel_transaction(pid) would be nice; it would
end the transaction regardless if in an actual statement or not.

Why pg_cancel_backend(pid) is not sufficient for the above use case?
Basically you want to rollback current transaction, I think that can be
achieved by pg_cancel_backend.

Not when the session is idle in transaction, only when it's actually doing
something.

IIRC one of the reasons is that when idle in transaction, the client is not
expecting any response, and would get out of sync. I know this has been
discussed a number of times, so a better explanation can probably be found
in the archives :)

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

Magnus Hagander <magnus@hagander.net> writes:

On Sat, Oct 31, 2015 at 5:50 AM, Amit Kapila <amit.kapila16@gmail.com>
wrote:

Why pg_cancel_backend(pid) is not sufficient for the above use case?
Basically you want to rollback current transaction, I think that can be
achieved by pg_cancel_backend.

Not when the session is idle in transaction, only when it's actually doing
something.

IIRC one of the reasons is that when idle in transaction, the client is not
expecting any response, and would get out of sync. I know this has been
discussed a number of times, so a better explanation can probably be found
in the archives :)

I think in principle it could be done by transitioning the backend into
a new xact.c state, wherein we know that the active transaction has been
canceled (at least to the extent of releasing externally visible resources
such as locks and snapshots), but this fact hasn't been reported to the
connected client. Then the next command submitted by the client would get
a "transaction cancelled" error and we'd go into the normal transaction-
failed state.

I don't think this would be exactly trivial, but it's probably doable.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sun, Nov 1, 2015 at 11:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Magnus Hagander <magnus@hagander.net> writes:

On Sat, Oct 31, 2015 at 5:50 AM, Amit Kapila <amit.kapila16@gmail.com>
wrote:

Why pg_cancel_backend(pid) is not sufficient for the above use case?
Basically you want to rollback current transaction, I think that can be
achieved by pg_cancel_backend.

Not when the session is idle in transaction, only when it's actually

doing

something.

Okay, thats right and the reason is that while reading message from client,
if an error occurs, it can loose track of previous and next messages and
that
could lead to an unrecoverable state.

I think in principle it could be done by transitioning the backend into
a new xact.c state, wherein we know that the active transaction has been
canceled (at least to the extent of releasing externally visible resources
such as locks and snapshots), but this fact hasn't been reported to the
connected client. Then the next command submitted by the client would get
a "transaction cancelled" error and we'd go into the normal transaction-
failed state.

That sounds to be a solution for this problem or otherwise for such a case
can't we completely abort the active transaction and set a flag like
PrevCommandFailed/PrevTransFailed and on receiving next message if
such a flag is set, then throw an appropriate error.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

2015-11-02 5:23 GMT+01:00 Amit Kapila <amit.kapila16@gmail.com>:

On Sun, Nov 1, 2015 at 11:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Magnus Hagander <magnus@hagander.net> writes:

On Sat, Oct 31, 2015 at 5:50 AM, Amit Kapila <amit.kapila16@gmail.com>
wrote:

Why pg_cancel_backend(pid) is not sufficient for the above use case?
Basically you want to rollback current transaction, I think that can

be

achieved by pg_cancel_backend.

Not when the session is idle in transaction, only when it's actually

doing

something.

Okay, thats right and the reason is that while reading message from client,
if an error occurs, it can loose track of previous and next messages and
that
could lead to an unrecoverable state.

I think in principle it could be done by transitioning the backend into
a new xact.c state, wherein we know that the active transaction has been
canceled (at least to the extent of releasing externally visible

resources

such as locks and snapshots), but this fact hasn't been reported to the
connected client. Then the next command submitted by the client would

get

a "transaction cancelled" error and we'd go into the normal transaction-
failed state.

That sounds to be a solution for this problem or otherwise for such a case
can't we completely abort the active transaction and set a flag like
PrevCommandFailed/PrevTransFailed and on receiving next message if
such a flag is set, then throw an appropriate error.

This is only partial solution - when some application is broken, then there
will be orphaned sessions. It is less wrong, than orphaned connections, but
it can enforce some issues too. The solution of this problem should to work
well with session pool sw like pgbouncer and similar.

Regards

Pavel

On Mon, Nov 2, 2015 at 1:28 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2015-11-02 5:23 GMT+01:00 Amit Kapila <amit.kapila16@gmail.com>:

On Sun, Nov 1, 2015 at 11:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Magnus Hagander <magnus@hagander.net> writes:

On Sat, Oct 31, 2015 at 5:50 AM, Amit Kapila <amit.kapila16@gmail.com>
wrote:

Why pg_cancel_backend(pid) is not sufficient for the above use case?
Basically you want to rollback current transaction, I think that can
be
achieved by pg_cancel_backend.

Not when the session is idle in transaction, only when it's actually
doing
something.

Okay, thats right and the reason is that while reading message from
client,
if an error occurs, it can loose track of previous and next messages and
that
could lead to an unrecoverable state.

I think in principle it could be done by transitioning the backend into
a new xact.c state, wherein we know that the active transaction has been
canceled (at least to the extent of releasing externally visible
resources
such as locks and snapshots), but this fact hasn't been reported to the
connected client. Then the next command submitted by the client would
get
a "transaction cancelled" error and we'd go into the normal transaction-
failed state.

That sounds to be a solution for this problem or otherwise for such a case
can't we completely abort the active transaction and set a flag like
PrevCommandFailed/PrevTransFailed and on receiving next message if
such a flag is set, then throw an appropriate error.

This is only partial solution - when some application is broken, then there
will be orphaned sessions. It is less wrong, than orphaned connections, but
it can enforce some issues too. The solution of this problem should to work
well with session pool sw like pgbouncer and similar.

Sure. Unfortunately it's not always practical to do so when you have
100's of applications running against 100's of databases, all written
by teams of variable quality, some of whom have been ejected for
overseas devlopment or vice versa. This is the world I live in.

The point stands that neither pg_cancel_backend or statement_timeout
(especially) provide *any* kind of safety guarantees because they only
work if execution is in the database. All the locks they hold and
other long running issues pertaining to long running transactions
(say, advancing xmin) are silent killers with no automatic way of
detecting or destroying. I understand the challenges here -- not
griping in any way -- the workaround is to cron up an executioner.
Just pointing out we have an issue.

merlin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

That sounds to be a solution for this problem or otherwise for such a

case

can't we completely abort the active transaction and set a flag like
PrevCommandFailed/PrevTransFailed and on receiving next message if
such a flag is set, then throw an appropriate error.

This is only partial solution - when some application is broken, then

there

will be orphaned sessions. It is less wrong, than orphaned connections,

but

it can enforce some issues too. The solution of this problem should to

work

well with session pool sw like pgbouncer and similar.

I wrote a nonsense - should be "It is less wrong, than orphaned transaction"

Sure. Unfortunately it's not always practical to do so when you have
100's of applications running against 100's of databases, all written
by teams of variable quality, some of whom have been ejected for
overseas devlopment or vice versa. This is the world I live in.

I would to say so the breaking transaction is not enough - it needs some
protocol enhancing. There is a advantage of terminate_session, because if
keep_alive packets are used, then client can to know so session is broken
in few seconds.

The point stands that neither pg_cancel_backend or statement_timeout
(especially) provide *any* kind of safety guarantees because they only
work if execution is in the database. All the locks they hold and
other long running issues pertaining to long running transactions
(say, advancing xmin) are silent killers with no automatic way of
detecting or destroying. I understand the challenges here -- not
griping in any way -- the workaround is to cron up an executioner.
Just pointing out we have an issue.

It is 100% true. But the users can do strange things. If we solve idle
transactions and not idle session, then they are able to increase
max_connections to thousands with happy smile in face.

I have not strong idea about how to solve it well - maybe introduce
transaction_idle_timeout and session_idle_timeout?

Regards

Pavel

On 11/2/15 11:15 AM, Pavel Stehule wrote:

I have not strong idea about how to solve it well - maybe introduce
transaction_idle_timeout and session_idle_timeout?

Yes, please. This is a very common problem. I would love a better way to
detect (or prevent) clients from being brain-dead about how they're
using transactions, but short of that this is the next best thing.

Actually, one other thing that would help is to have the ability to turn
this into an ERROR:

begin;
WARNING: there is already a transaction in progress
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Nov 2, 2015 at 10:45 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

It is 100% true. But the users can do strange things. If we solve idle
transactions and not idle session, then they are able to increase
max_connections to thousands with happy smile in face.

I have not strong idea about how to solve it well - maybe introduce
transaction_idle_timeout and session_idle_timeout?

What exactly do we want to define session_idle_timeout? Some
possibilities:
a. Reset the session related variables like transaction, prepared
statements, etc. and retain it for connection pool kind of stuff
b. Exit from the session

If we want something on lines of option (a), then I think it is better
to have just a single time out (session_idle_timeout/idle_timeout)

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Mon, Nov 2, 2015 at 8:42 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Mon, Nov 2, 2015 at 10:45 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

It is 100% true. But the users can do strange things. If we solve idle
transactions and not idle session, then they are able to increase
max_connections to thousands with happy smile in face.

I have not strong idea about how to solve it well - maybe introduce
transaction_idle_timeout and session_idle_timeout?

What exactly do we want to define session_idle_timeout? Some
possibilities:
a. Reset the session related variables like transaction, prepared
statements, etc. and retain it for connection pool kind of stuff
b. Exit from the session

If we want something on lines of option (a), then I think it is better
to have just a single time out (session_idle_timeout/idle_timeout)

I'm not thrilled about the prefix 'session_': most .conf variables
apply to the session (like statement_timeout) and we don't use the
session prefix for any of those.

"transaction_idle_timeout" is ok, if you want the timeout to apply as
an expiration for a transaction going idle.

"idle_timeout" doesn't make much sense to me. It's the responsibility
of the pooler to mange idle-but-not-in-transaction sessions and we
already have machinery to support that (DISCARD).

"transaction_timeout" is the best, and simplest, hypothetical setting
IMNSHO. It gives you a well defined upper bound guarantee of
transaction time regardless of application behavior, which neither
statement_timeout or transaction_idle_timeout give, even when used in
conjunction as I understand them. It would completely displace
statement_timeout in all servers I manage.

merlin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-11-03 3:42 GMT+01:00 Amit Kapila <amit.kapila16@gmail.com>:

On Mon, Nov 2, 2015 at 10:45 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

It is 100% true. But the users can do strange things. If we solve idle
transactions and not idle session, then they are able to increase
max_connections to thousands with happy smile in face.

I have not strong idea about how to solve it well - maybe introduce
transaction_idle_timeout and session_idle_timeout?

What exactly do we want to define session_idle_timeout? Some
possibilities:
a. Reset the session related variables like transaction, prepared
statements, etc. and retain it for connection pool kind of stuff
b. Exit from the session

b is safe state - and currently it is only one state, that we can forward
to client side (with keep_alive packets) - so I prefer b

Regards

Pavel

On Mon, Nov 2, 2015 at 1:23 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

On 11/2/15 11:15 AM, Pavel Stehule wrote:

I have not strong idea about how to solve it well - maybe introduce
transaction_idle_timeout and session_idle_timeout?

Yes, please. This is a very common problem. I would love a better way to
detect (or prevent) clients from being brain-dead about how they're using
transactions, but short of that this is the next best thing.

Actually, one other thing that would help is to have the ability to turn
this into an ERROR:

begin;
WARNING: there is already a transaction in progress

curious: does the SQL standard define this behavior?

Anyways, we've pretty studiously avoided (minus a couple of
anachronisms) .conf setting thats control behavior of SQL commands in
a non performance way.

IMO, this as yet another case for 'stored procedures' that can manage
transaction state: you could rig up your own procedure: CALL
begin_tx_safe(); which would test transaction state and fail if
already in one. This doesn't help you if you're not in direct control
of application generated SQL but it's a start. Barring that, at least
warnings tend to stand out in the database log.

merlin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Nov 3, 2015 at 7:53 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Mon, Nov 2, 2015 at 8:42 PM, Amit Kapila <amit.kapila16@gmail.com>

wrote:

What exactly do we want to define session_idle_timeout? Some
possibilities:
a. Reset the session related variables like transaction, prepared
statements, etc. and retain it for connection pool kind of stuff
b. Exit from the session

If we want something on lines of option (a), then I think it is better
to have just a single time out (session_idle_timeout/idle_timeout)

I'm not thrilled about the prefix 'session_': most .conf variables
apply to the session (like statement_timeout) and we don't use the
session prefix for any of those.

"transaction_idle_timeout" is ok, if you want the timeout to apply as
an expiration for a transaction going idle.

"idle_timeout" doesn't make much sense to me. It's the responsibility
of the pooler to mange idle-but-not-in-transaction sessions and we
already have machinery to support that (DISCARD).

I think if transaction is idle for long time, then the chances that someone
will use that session is less, so idle_timeout seems to me the right tool
for such sessions. I have checked that databases like Oracle also has
such a variable to help out users for such situations.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Tue, Nov 3, 2015 at 7:56 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2015-11-03 3:42 GMT+01:00 Amit Kapila <amit.kapila16@gmail.com>:

On Mon, Nov 2, 2015 at 10:45 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

It is 100% true. But the users can do strange things. If we solve idle
transactions and not idle session, then they are able to increase
max_connections to thousands with happy smile in face.

I have not strong idea about how to solve it well - maybe introduce
transaction_idle_timeout and session_idle_timeout?

What exactly do we want to define session_idle_timeout? Some
possibilities:
a. Reset the session related variables like transaction, prepared
statements, etc. and retain it for connection pool kind of stuff
b. Exit from the session

b is safe state - and currently it is only one state, that we can forward
to client side (with keep_alive packets) - so I prefer b

Okay, I think one more point to consider is that it would be preferable to
have such an option for backend sessions and not for other processes
like WalSender.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Tue, Nov 3, 2015 at 10:33 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Tue, Nov 3, 2015 at 7:56 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2015-11-03 3:42 GMT+01:00 Amit Kapila <amit.kapila16@gmail.com>:

On Mon, Nov 2, 2015 at 10:45 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

It is 100% true. But the users can do strange things. If we solve idle
transactions and not idle session, then they are able to increase
max_connections to thousands with happy smile in face.

I have not strong idea about how to solve it well - maybe introduce
transaction_idle_timeout and session_idle_timeout?

What exactly do we want to define session_idle_timeout? Some
possibilities:
a. Reset the session related variables like transaction, prepared
statements, etc. and retain it for connection pool kind of stuff
b. Exit from the session

b is safe state - and currently it is only one state, that we can forward
to client side (with keep_alive packets) - so I prefer b

Okay, I think one more point to consider is that it would be preferable to
have such an option for backend sessions and not for other processes
like WalSender.

All right...I see the usage.. I withdraw my objection to 'session'
prefix then now that I understand the case. So, do you agree that:

*) session_idle_timeout: dumps the backend after X time in 'idle' state
and
*) transaction_timeout: cancels transaction after X time, regardless of state

sounds good?

merlin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Nov 4, 2015 at 8:06 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Tue, Nov 3, 2015 at 10:33 PM, Amit Kapila <amit.kapila16@gmail.com>

wrote:

It is 100% true. But the users can do strange things. If we solve

idle

transactions and not idle session, then they are able to increase
max_connections to thousands with happy smile in face.

I have not strong idea about how to solve it well - maybe introduce
transaction_idle_timeout and session_idle_timeout?

What exactly do we want to define session_idle_timeout? Some
possibilities:
a. Reset the session related variables like transaction, prepared
statements, etc. and retain it for connection pool kind of stuff
b. Exit from the session

b is safe state - and currently it is only one state, that we can

forward

to client side (with keep_alive packets) - so I prefer b

Okay, I think one more point to consider is that it would be preferable

to

have such an option for backend sessions and not for other processes
like WalSender.

All right...I see the usage.. I withdraw my objection to 'session'
prefix then now that I understand the case. So, do you agree that:

*) session_idle_timeout: dumps the backend after X time in 'idle' state

Agreed.

and
*) transaction_timeout: cancels transaction after X time, regardless of

state

I am not sure about this, let us see if any body else has opinion about
this parameter.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Okay, I think one more point to consider is that it would be preferable to

have such an option for backend sessions and not for other processes
like WalSender.

All right...I see the usage.. I withdraw my objection to 'session'
prefix then now that I understand the case. So, do you agree that:

*) session_idle_timeout: dumps the backend after X time in 'idle' state
and
*) transaction_timeout: cancels transaction after X time, regardless of
state

sounds good?

Not too much

*) transaction_timeout: cancels transaction after X time, regardless of
state

This is next level of statement_timeout. I can't to image sense. What is a
issue solved by this property?

Pavel

On Wed, Nov 4, 2015 at 8:42 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Okay, I think one more point to consider is that it would be preferable
to
have such an option for backend sessions and not for other processes
like WalSender.

All right...I see the usage.. I withdraw my objection to 'session'
prefix then now that I understand the case. So, do you agree that:

*) session_idle_timeout: dumps the backend after X time in 'idle' state
and
*) transaction_timeout: cancels transaction after X time, regardless of
state

sounds good?

Not too much

*) transaction_timeout: cancels transaction after X time, regardless of
state

This is next level of statement_timeout. I can't to image sense. What is a
issue solved by this property?

That's the entire point of the thread (or so I thought): cancel
transactions 'idle in transaction'. This is entirely different than
killing idle sessions. BTW, I would never configure
session_idle_timeout, because I have no idea what that would do to
benign cases where connection poolers have grabbed a few extra
connections during a load spike. It's pretty common not to have
those applications have coded connection retry properly and it would
cause issues.

The problem at hand is idle *transactions*, not sessions, and a
configuration setting that deals with transaction time. I do not
understand the objection to setting an upper bound on transaction
time. I'm ok with cancelling or dumping the session with a slight
preference on cancel.

merlin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-11-04 15:50 GMT+01:00 Merlin Moncure <mmoncure@gmail.com>:

On Wed, Nov 4, 2015 at 8:42 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Okay, I think one more point to consider is that it would be

preferable

to
have such an option for backend sessions and not for other processes
like WalSender.

All right...I see the usage.. I withdraw my objection to 'session'
prefix then now that I understand the case. So, do you agree that:

*) session_idle_timeout: dumps the backend after X time in 'idle' state
and
*) transaction_timeout: cancels transaction after X time, regardless of
state

sounds good?

Not too much

*) transaction_timeout: cancels transaction after X time, regardless of
state

This is next level of statement_timeout. I can't to image sense. What is

a

issue solved by this property?

That's the entire point of the thread (or so I thought): cancel
transactions 'idle in transaction'. This is entirely different than
killing idle sessions. BTW, I would never configure
session_idle_timeout, because I have no idea what that would do to
benign cases where connection poolers have grabbed a few extra
connections during a load spike. It's pretty common not to have
those applications have coded connection retry properly and it would
cause issues.

you wrote "transaction_timeout: cancels transaction after X time,
regardless of

state" - I understand if text is "cancels transaction after X time if

state is "idle in tramsaction"

Pavel