Disposing resources allocated in an extension

Disposing resources allocated in an extension

  
Hi,

We have implemented a .NET extension that connects to an external database, and we use the following flow to perform actions. The OpenConnection .NET action creates a connection object and returns it as an Object that is then passed to the ExecuteCalculation and CloseConnection actions.



This works mostly fine, but recently we noticed that the exception handling is not bullet proof and sometimes we leak connections. One scenario is the following:
  • We start a Process that runs external database queries in an automatic activity
  • The activity lasts over 5 minutes and is terminated by Outsystems platform
  • The thread is aborted and the database connection is never closed. It seems that the exception handler is not executed in this case.
  • After a few minutes the platform automatically starts the terminated activity again and new connections are created until the database hits the connection limit or the process is suspended from Service Center
Questions:
  1. Is there a cleaner way to dispose resources allocated in an extension or is the above one the usual solution?
  2. Would it be possible to create an extension action OpenConnectionAndRunAction that would open the connection object inside .NET's "using" structure and somehow call a caller-defined action inside the using scope?

Teemu
Why not open/close the connection inside the execute-calculation?

If we execute just one statement, it is indeed possible to open and close the connection inside the .NET execute action. However, often we need to execute several statements inside the Outsystems action logic, and inspect the results between the queries. Then we want to utilize the same connection throughout the logic in order to use transactions and avoid overhead of opening a new connection for each statement.
Btw,

why not simply use the connections you are using by the platform itself?
(check the OracleConnector for example)

We are connecting to an HP Vertica database that is not supported by the platform. That is why we have a separate extension.
I see.

first of all, I'm curious why the exceptionhandler is not hit?
can you reproduce it easily? (and add an audit-action to the exception-handler?)

second, when you handle the connection yourself, it should be in an pool?
so even when you have dangling connections, they shoulde be able to be re-used?
otherwise you end up with the same problem for other stuff?

going out on a limb here.
did you check this thread?http://www.outsystems.com/forums/discussion/14992/the-postgresql-database-connector-by-ardoric/

ok, it's postgress. but you could "rework" it for HP, so the connection is being "held" by outsystems?






Now that I try to reproduce it in a test application, the exception seems to be hit properly and connections are closed almost always. A few times I got the following behaviour, but I can not reproduce that consistently.
  1. The activity is terminated after 300 seconds
  2. Platform restarts the activity
  3. I suspend and terminate the process from Service Center
  4. The process shows as terminated state, but the activity continues running and produces audit messages, and the platform does not terminate the activity anymore.
In service center I get the messages shown below. The platform notices that the activity is running longer that 300 seconds, but it does not terminate the process.

04-08-2015 10:00:10    (System) 1    Front-end Server win-q618pcr3a8i OutSystems Scheduler Service Status: Warning: Activity Processor (Activity 'JustSleep', id 57211, Espace 'UtilyticsTester', Tenant 'Master') is in status Processing for 2835 seconds which exceeds the 270 seconds warning threshold (90% of the 300 seconds timer timeout definition).    ALARM

04-08-2015 09:45:17    (System) 1    Front-end Server win-q618pcr3a8i OutSystems Scheduler Service Status: Warning: Activity Processor (Activity 'JustSleep', id 57211, Espace 'UtilyticsTester', Tenant 'Master') is in status Processing for 1942 seconds which exceeds the 270 seconds warning threshold (90% of the 300 seconds timer timeout definition).    ALARM

04-08-2015 09:30:03    (System) 1    Front-end Server win-q618pcr3a8i OutSystems Scheduler Service Status: Warning: Activity Processor (Activity 'JustSleep', id 57211, Espace 'UtilyticsTester', Tenant 'Master') is in status Processing for 1028 seconds which exceeds the 270 seconds warning threshold (90% of the 300 seconds timer timeout definition).    ALARM

Restarting IIS killed the process. I have to see if I can reproduce it more consistently in the real application.
About the connection pool: Our database extension does not use connection pooling yet, but I think it does not solve the original problem since the caller of OpenConnection needs to anyway call CloseConnection to inform the pool that the connection can be reused by other callers, and the original problem was that for some reason the CloseConnection call is not done at all.

Thanks for the postgres link. I will check that.