When a stored procedure call (or any query) is canceled, the execution of the procedure is immediately stopped and the appropriate status code is returned to the application. The cancel cannot be caught by an exception clause in your stored procedure. To handle the case of a user-level cancel and that there may have been unfinalized resources, the client application could detect the cancel and turn around and make another call to the server to do the cleanup explicitly.

If the stored procedure was executing an external procedure call at the time of the cancel request, the external procedure will be notified of the cancel by a call to extfn_cancel() routine provided that the external library is using API version 2 (or higher) and it exports the extfn_cancel() function. Therefore you can use the extfn_cancel() call to free any resources that have been used and stop what ever processing that you are doing... and return as quick as possible. Note that the server will timeout after a few seconds (can't remember the exact amount of time - I think it is 5 seconds?) and will orphan the external thread... and it will be cleaned-up when it eventually does return (if it ever returns?) - at server shutdown time, the external thread is killed after the (5 second) timeout.

However, if your external library maintains state across calls into the library - for example holds files open - and the cancel occurs while a call into the library is not active, the call to extfn_cancel() will not be made and therefore you will not know (within the library) that the procedure has been canceled. (FWIW: I've thought about this problem in the past and we've not done anything about it since no one has ever mentioned it as a limitation - i.e. speak up if you think this is a huge problem for you!).

That being said, when the server process shuts down, the server will unload all external libraries in an orderly fashion and you can cleanup any resources at that time by including a fini routine in your library. You may also choose to force a library to be unloaded at any time - i.e. without shutting down the server - by calling sa_external_library_unload('libname'), but this routine should be used with caution and should only be used if you know for certain that no connections are currently using (calling) any of the routines within the external library.

If a connection is closed or dropped while a statement is executing, an implicit cancel occurs on the connection and it is given the obligatory time (5 seconds) to cleanup and stop.

Resource within the server are managed and cleaned up appropriately when a procedure or query is canceled.