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 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.