Cancelling idle in transaction state
Currently SIGINT is ignored during <IDLE> in transaction, but we have
recently agreed to allow this to cancel the transaction. We said we
would do this in all cases, so this is a separate feature/patch (though
Hot Standby requires it).
A simple change allows the transaction to be cancelled, but there are
some loose ends that I wish to discuss.
If we are running a statement and a cancel is received, then we return
the ERROR to the client, who is expecting it. If we cancel a transaction
while the connection is idle, we have no way of signalling to the client
program this has occurred. So the client finds out about this much
later, not in fact until the next message is sent.
Is there a mechanism for communicating the state back to the client?
Will this be handled correctly with existing code? psql appears to be
confused by a cancelled backend.
I'm not familiar with these aspects of the code, so some clear
suggestions are needed to allow me to work this out. I'm worried that
this will delay things further otherwise.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
Added to TODO:
Allow administrators to cancel multi-statement idle
transactions
This allows locks to be released, but it is complex to report the
cancellation back to the client.
* http://archives.postgresql.org/pgsql-hackers/2008-12/msg01340.php
---------------------------------------------------------------------------
Simon Riggs wrote:
Currently SIGINT is ignored during <IDLE> in transaction, but we have
recently agreed to allow this to cancel the transaction. We said we
would do this in all cases, so this is a separate feature/patch (though
Hot Standby requires it).A simple change allows the transaction to be cancelled, but there are
some loose ends that I wish to discuss.If we are running a statement and a cancel is received, then we return
the ERROR to the client, who is expecting it. If we cancel a transaction
while the connection is idle, we have no way of signalling to the client
program this has occurred. So the client finds out about this much
later, not in fact until the next message is sent.Is there a mechanism for communicating the state back to the client?
Will this be handled correctly with existing code? psql appears to be
confused by a cancelled backend.I'm not familiar with these aspects of the code, so some clear
suggestions are needed to allow me to work this out. I'm worried that
this will delay things further otherwise.--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
On Wed, 2009-01-21 at 15:22 -0500, Bruce Momjian wrote:
Added to TODO:
Allow administrators to cancel multi-statement idle
transactionsThis allows locks to be released, but it is complex to report the
cancellation back to the client.* http://archives.postgresql.org/pgsql-hackers/2008-12/msg01340.php
This is part of Hot Standby.
The bug is on the TODO list.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
Simon Riggs wrote:
On Wed, 2009-01-21 at 15:22 -0500, Bruce Momjian wrote:
Added to TODO:
Allow administrators to cancel multi-statement idle
transactionsThis allows locks to be released, but it is complex to report the
cancellation back to the client.* http://archives.postgresql.org/pgsql-hackers/2008-12/msg01340.php
This is part of Hot Standby.
The bug is on the TODO list.
Well, if it gets done for 8.4 then we can mark it completed; it not it
will be there for 8.5. The behavior is useful independent of hot
standby.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
On Wed, 2009-01-21 at 15:46 -0500, Bruce Momjian wrote:
Simon Riggs wrote:
On Wed, 2009-01-21 at 15:22 -0500, Bruce Momjian wrote:
Added to TODO:
Allow administrators to cancel multi-statement idle
transactionsThis allows locks to be released, but it is complex to report the
cancellation back to the client.* http://archives.postgresql.org/pgsql-hackers/2008-12/msg01340.php
This is part of Hot Standby.
The bug is on the TODO list.
Well, if it gets done for 8.4 then we can mark it completed; it not it
will be there for 8.5. The behavior is useful independent of hot
standby.
At one time there was also a positive discussion on having something
like:
idle_in_transaction_timeout
Does this play along with that?
Joshua D.D rake
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com+ If your life is a hard drive, Christ can be your backup. +
--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997
Joshua D. Drake wrote:
On Wed, 2009-01-21 at 15:46 -0500, Bruce Momjian wrote:
Simon Riggs wrote:
On Wed, 2009-01-21 at 15:22 -0500, Bruce Momjian wrote:
Added to TODO:
Allow administrators to cancel multi-statement idle
transactionsThis allows locks to be released, but it is complex to report the
cancellation back to the client.* http://archives.postgresql.org/pgsql-hackers/2008-12/msg01340.php
This is part of Hot Standby.
The bug is on the TODO list.
Well, if it gets done for 8.4 then we can mark it completed; it not it
will be there for 8.5. The behavior is useful independent of hot
standby.At one time there was also a positive discussion on having something
like:idle_in_transaction_timeout
Yep, and already a TODO:
Add idle_in_transaction_timeout GUC so locks are not held for
long periods of time
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
I'd be very grateful to any hackers out there who are looking for a
project before close of 8.5 to consider working on this. It's dang
useful, both for Hot Standby and normal processing.
(You'll have the added bonus of proving you're smarter than me!)
On Wed, 2009-01-21 at 15:22 -0500, Bruce Momjian wrote:
Added to TODO:
Allow administrators to cancel multi-statement idle
transactionsThis allows locks to be released, but it is complex to report the
cancellation back to the client.* http://archives.postgresql.org/pgsql-hackers/2008-12/msg01340.php
---------------------------------------------------------------------------
Simon Riggs wrote:
Currently SIGINT is ignored during <IDLE> in transaction, but we have
recently agreed to allow this to cancel the transaction. We said we
would do this in all cases, so this is a separate feature/patch (though
Hot Standby requires it).A simple change allows the transaction to be cancelled, but there are
some loose ends that I wish to discuss.If we are running a statement and a cancel is received, then we return
the ERROR to the client, who is expecting it. If we cancel a transaction
while the connection is idle, we have no way of signalling to the client
program this has occurred. So the client finds out about this much
later, not in fact until the next message is sent.Is there a mechanism for communicating the state back to the client?
Will this be handled correctly with existing code? psql appears to be
confused by a cancelled backend.I'm not familiar with these aspects of the code, so some clear
suggestions are needed to allow me to work this out. I'm worried that
this will delay things further otherwise.--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com+ If your life is a hard drive, Christ can be your backup. +
--
Simon Riggs www.2ndQuadrant.com
On Dec 5, 2009, at 12:25 PM, Simon Riggs wrote:
...
I'm not volunteering here, but having worked with the protocol, I do have a suggestion:
This allows locks to be released, but it is complex to report the
cancellation back to the client.
I think the answer here is that the server should *not* report the cancellation.
Rather, it should mark the transaction as failed and let the client eventually sync its state on subsequent requests that will result in InFailedTransaction ERRORs.
With such a solution, COMMITs issued to administrator cancelled transactions should result in an ERROR. Well, I suppose that would only be a requirement when:
BEGIN;
... some work ...
<idle>
<admin zapped this transaction>
<more idle>
COMMIT; <-- client needs to know that this failed,
and it should be something louder than
a "ROLLBACK" tag. :P
So, if a command were issued to a cancelled transaction prior to a COMMIT:
BEGIN;
... some work ...
<idle>
<admin zapped this transaction>
SELECT * FROM something; -- fails, IFX ERROR emitted to client
COMMIT; <-- client was already notified of
the xact failure by a prior command's error,
so the normal "ROLLBACK" would be fine.
Also, if immediate notification is seen as a necessity, a WARNING with a special code could be leveraged. Oh, or maybe use a dedicated LISTEN/NOTIFY channel? "LISTEN pg_darn_admin_zapped_my_xact;" to opt-in for transaction cancellation events that occur in *this* backend.. [Note: this is in addition to COMMITs emitting ERRORs]
I can't see immediate notification being useful excepting some rather strange situations where the client left the transaction idle to go do other expensive operations that "should" be immediately interrupted if this particular transaction were to be cancelled for some reason.. Such a situation might even make sense if those "expensive operations" somehow depended on the locks held by the transaction, but I think that's a stretch. Not to mention that the client could just occasionally poll the transaction with 'SELECT 1's; no special WARNING or NOTIFY's would be necessary.
On Sat, Dec 5, 2009 at 8:13 PM, James Pye <lists@jwp.name> wrote:
I think the answer here is that the server should *not* report the cancellation.
Rather, it should mark the transaction as failed and let the client eventually sync its state on subsequent requests that will result in InFailedTransaction ERRORs.
[...]
Also, if immediate notification is seen as a necessity, a WARNING with a special code could be leveraged. Oh, or maybe use a dedicated LISTEN/NOTIFY channel? "LISTEN pg_darn_admin_zapped_my_xact;" to opt-in for transaction cancellation events that occur in *this* backend.. [Note: this is in addition to COMMITs emitting ERRORs]
I think this line of thinking is on the right track. The server
should certainly not send back an immediate ERROR response, because
that will definitely confuse the client. Of course, any subsequent
commands will report ERRORs until the client rolls back. But it also
seems highly desirable for the server to send some sort of immediate,
asynchronous notification, so that a sufficiently smart client can
immediately report the error back to the user or take such other
action as it deems appropriate.
Currently, it appears that the only messages that the server can send
back asynchronously are ParameterStatus and NotificationResponse. So
we need to decide whether it's feasible/better to shoehorn this
functionality into one of those message types, or whether we should
bump the protocol version and add a new message type (cue: panic in
the streets). On first examination (and I am not an expert in this
area), ParameterStatus would seem to be the better choice, because it
appears to me that all clients must be prepared to cope with such
messages, whereas in theory a client might be unprepared for a
NotificationResponse if it never executes LISTEN. (It seems clearly
preferable not to require clients to issue an explicit LISTEN in order
to enable this feature.)
Going with that theory, we could pick a "magical" parameter status
value, either something like __transaction_cancelled, or maybe even
something that contains a character that isn't even legal in a normal
parameter, like $transaction_cancelled, if we don't think that will
break any clients. Then we could just report a value change for this
whenever an idle transaction is cancelled. Clients who ignore this
will find out when they next issue a query; others will know
immediately.
...Robert
Robert Haas <robertmhaas@gmail.com> writes:
I think this line of thinking is on the right track. The server
should certainly not send back an immediate ERROR response, because
that will definitely confuse the client. Of course, any subsequent
commands will report ERRORs until the client rolls back. But it also
seems highly desirable for the server to send some sort of immediate,
asynchronous notification, so that a sufficiently smart client can
immediately report the error back to the user or take such other
action as it deems appropriate.
If you must have that, send a NOTICE. I don't actually see the point
though. If the client was as smart and well-coded as all that, it
wouldn't be sitting on an open transaction in the first place.
Currently, it appears that the only messages that the server can send
back asynchronously are ParameterStatus and NotificationResponse.
Using either of those is completely inappropriate.
regards, tom lane
On Sat, Dec 5, 2009 at 10:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
I think this line of thinking is on the right track. The server
should certainly not send back an immediate ERROR response, because
that will definitely confuse the client. Of course, any subsequent
commands will report ERRORs until the client rolls back. But it also
seems highly desirable for the server to send some sort of immediate,
asynchronous notification, so that a sufficiently smart client can
immediately report the error back to the user or take such other
action as it deems appropriate.If you must have that, send a NOTICE.
Ah ha! I missed that one. That's perfect.
I don't actually see the point
though. If the client was as smart and well-coded as all that, it
wouldn't be sitting on an open transaction in the first place.
Think about an interactive client. It's not the client's fault that
the user has chosen to begin a transaction and then sit there
cogitating, but the client would like to let the user know right away
that their current transaction is defunct.
...Robert
On Sat, 2009-12-05 at 18:13 -0700, James Pye wrote:
On Dec 5, 2009, at 12:25 PM, Simon Riggs wrote:
...
I'm not volunteering here, but having worked with the protocol, I do have a suggestion:
Thanks. Looks like good input. With the further clarification that we
use NOTIFY it seems a solution is forming.
Any other takers?
--
Simon Riggs www.2ndQuadrant.com
On Sun, 2009-12-06 at 07:58 +0000, Simon Riggs wrote:
On Sat, 2009-12-05 at 18:13 -0700, James Pye wrote:
On Dec 5, 2009, at 12:25 PM, Simon Riggs wrote:
...
I'm not volunteering here, but having worked with the protocol, I do have a suggestion:
Thanks. Looks like good input. With the further clarification that we
use NOTIFY it seems a solution is forming.
If we use notify, then "the sufficiently smart client" (tm) should
probably declared that it is waiting for such notify , no ?
That would mean, that it should have issued either
"LISTEN CANCEL_IDLE_TRX_<pid>"
or with the new payload enabled NOTIFY just
"LISTEN CANCEL_IDLE_TRX"
and then the NOTIFY would include the pid of rolled back backend and
possibly some other extra info.
Otoh, we could also come up with something that looks like a NOTIFY from
client end, but is sent only to one connection that is canceled instead
of all listeners.
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
On Dec 6, 2009, at 2:58 AM, Simon Riggs <simon@2ndQuadrant.com> wrote:
On Sat, 2009-12-05 at 18:13 -0700, James Pye wrote:
On Dec 5, 2009, at 12:25 PM, Simon Riggs wrote:
...
I'm not volunteering here, but having worked with the protocol, I
do have a suggestion:Thanks. Looks like good input. With the further clarification that we
use NOTIFY it seems a solution is forming.
Notice, not NOTIFY.
...Robert
Hannu Krosing <hannu@2ndquadrant.com> writes:
On Sun, 2009-12-06 at 07:58 +0000, Simon Riggs wrote:
Thanks. Looks like good input. With the further clarification that we
use NOTIFY it seems a solution is forming.
If we use notify, then "the sufficiently smart client" (tm) should
probably declared that it is waiting for such notify , no ?
We are using NOTICE, not NOTIFY, assuming that we use anything at all
(which I still regard as unnecessary). Please stop injecting confusion
into the discussion.
regards, tom lane
On Sun, Dec 6, 2009 at 4:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
We are using NOTICE, not NOTIFY, assuming that we use anything at all
(which I still regard as unnecessary). Please stop injecting confusion
into the discussion.
Attached is a minimal POC patch that allows to cancel an idle
transaction with SIGINT. The HS patch also allows this in its current
form but as Simon points out the client gets out of sync with it.
The proposal is to send an additional NOTICE to the client and abort
all open transactions and subtransactions (this is what I got from the
previous discussion).
I had to write an additional function AbortAnyTransaction() which
aborts all transactions and subtransactions and leaves the transaction
in the aborted state, is there an existing function to do this?
We'd probably want to add a timeout for idle transactions also (which
is a wishlist item since quite some time) and could also offer user
functions like pg_cancel_idle_transaction(). Along this we might need
to add internal reasons like we do for SIGUSR1 because we are now
multiplexing different functionality onto the SIGINT signal. One might
want to cancel an idle transaction only and not a running query,
without keeping track of internal reasons one risks to cancel a
legitimate query if that backend has started to work on a query again.
Comments?
Joachim
Attachments:
idletxn.difftext/x-diff; charset=US-ASCII; name=idletxn.diffDownload+70-6
On Thu, 2009-12-24 at 21:38 +0100, Joachim Wieland wrote:
On Sun, Dec 6, 2009 at 4:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
We are using NOTICE, not NOTIFY, assuming that we use anything at all
(which I still regard as unnecessary). Please stop injecting confusion
into the discussion.Attached is a minimal POC patch that allows to cancel an idle
transaction with SIGINT. The HS patch also allows this in its current
form but as Simon points out the client gets out of sync with it.
Thanks for working on this.
The proposal is to send an additional NOTICE to the client and abort
all open transactions and subtransactions (this is what I got from the
previous discussion).
(Adding Kris into the discussion here.)
Would this work with JDBC driver and/or general protocol clients?
I had to write an additional function AbortAnyTransaction() which
aborts all transactions and subtransactions and leaves the transaction
in the aborted state, is there an existing function to do this?
AbortOutOfAnyTransaction()
We'd probably want to add a timeout for idle transactions also (which
is a wishlist item since quite some time) and could also offer user
functions like pg_cancel_idle_transaction(). Along this we might need
to add internal reasons like we do for SIGUSR1 because we are now
multiplexing different functionality onto the SIGINT signal. One might
want to cancel an idle transaction only and not a running query,
without keeping track of internal reasons one risks to cancel a
legitimate query if that backend has started to work on a query again.
Next project, not both at once.
--
Simon Riggs www.2ndQuadrant.com
On Tue, 29 Dec 2009, Simon Riggs wrote:
The proposal is to send an additional NOTICE to the client and abort
all open transactions and subtransactions (this is what I got from the
previous discussion).Would this work with JDBC driver and/or general protocol clients?
A Notice would be easy to overlook. The JDBC driver wraps that as a
SQLWarning which callers need to explicitly check for (and rarely do in my
experience). So when they run their next statement they'll get an error
saying that the current transaction is aborted, but they'll have no idea
why as the warning was silently eaten. I'd prefer the transaction
cancellation to come as an Error because that's what it really is.
The only downside I can see is that a client would get confused if:
1) Transaction starts.
2) Idle transaction is killed and error message is given.
3) Client issues rollback
4) Client gets error message from saying the transaction was cancelled.
Kris Jurka
On Wed, Dec 30, 2009 at 12:28 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
I had to write an additional function AbortAnyTransaction() which
aborts all transactions and subtransactions and leaves the transaction
in the aborted state, is there an existing function to do this?AbortOutOfAnyTransaction()
But this would clean up completely and not leave the transaction in
the aborted state. Subsequent commands will be executed just fine
instead of being refused with the error message that the transaction
is already aborted... Right?
Joachim
On Wed, 2009-12-30 at 05:02 -0500, Kris Jurka wrote:
On Tue, 29 Dec 2009, Simon Riggs wrote:
The proposal is to send an additional NOTICE to the client and abort
all open transactions and subtransactions (this is what I got from the
previous discussion).Would this work with JDBC driver and/or general protocol clients?
A Notice would be easy to overlook. The JDBC driver wraps that as a
SQLWarning which callers need to explicitly check for (and rarely do in my
experience). So when they run their next statement they'll get an error
saying that the current transaction is aborted, but they'll have no idea
why as the warning was silently eaten. I'd prefer the transaction
cancellation to come as an Error because that's what it really is.
I'm not certain of all of these points, but here goes:
AFAIK, NOTICE was suggested because it can be sent at any time, whereas
ERRORs are only associated with statements.
http://developer.postgresql.org/pgdocs/postgres/protocol-flow.html#PROTOCOL-ASYNC
"It is possible for NoticeResponse messages to be generated due to
outside activity; for example, if the database administrator commands a
"fast" database shutdown, the backend will send a NoticeResponse
indicating this fact before closing the connection. Accordingly,
frontends should always be prepared to accept and display NoticeResponse
messages, even when the connection is nominally idle."
Can JDBC accept a NOTICE, yet throw an error? NOTICEs have a SQLState
field just like ERRORs do, so you should be able to special case that.
I understand that this will mean that we are enhancing the protocol for
this release, but I don't have a better suggestion.
The only downside I can see is that a client would get confused if:
1) Transaction starts.
2) Idle transaction is killed and error message is given.
3) Client issues rollback
4) Client gets error message from saying the transaction was cancelled.
Are you saying that the client should send rollback and that it should
generate no message?
--
Simon Riggs www.2ndQuadrant.com
