Re: Cancelling idle in transaction state

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
thatwill result in InFailedTransaction ERRORs. 

With such a solution, COMMITs issued to administrator cancelled transactions should result in an ERROR. Well, I suppose
thatwould 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
usea dedicated LISTEN/NOTIFY channel? "LISTEN pg_darn_admin_zapped_my_xact;" to opt-in for transaction cancellation
eventsthat 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
transactionidle to go do other expensive operations that "should" be immediately interrupted if this particular
transactionwere 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
clientcould just occasionally poll the transaction with 'SELECT 1's; no special WARNING or NOTIFY's would be necessary.