0

I am dealing with an Azure function that connects to a DB (Java), i suppose something quite common.

The functions may have cold or warm starts, mine should be warm for most of the time (it is called every 5 minutes). The connection is stored in a pool (JDBCPooledConnectionSource) in a static variable, so theoreticaly the connection should be reused for every warm start, gaining in efficiency.

Is it a good strategy or could cause problems? For example, a fisical connection gets broken, but its reference is still in the heap: when the reference will be used to make a query, and an exception may occur.

To avoid calls to broken connection, I could use a non static variable to store the connection. This should be safer, but less efficient because the connection should be re-created at every call.

Which strategy is the best? I suppose there are many functions that do the same (connecting to DB) so for sure somebody more experienced than me in Azure knows the best strategy or common errors.

fresko
  • 1,890
  • 2
  • 24
  • 25
  • I would try it and see how it works. I opened and closed a database connection on each request in my function so I didn't have to worry about checking for a broken connection and the resources could be freed, but that's my preference. – Dan Wilson Oct 03 '18 at 14:20
  • Thanks, in the same function I see the line: connectionSource.setCheckConnectionsEveryMillis(0); this line disables a thread that else would check the connections and close them if broken. On the other hand i see the line: setTestBeforeGet(true); that will test the connection before to return from the pool. I would say that this seems safe... – fresko Oct 08 '18 at 12:57
  • Sorry, I must say that the connections are never closed. When I query the DB (SELECT * FROM pg_stat_activity) I notice that a new connection is created. As test I tried to run 4 queries during the function using getConnectionSource() 4 times. I expected that 1 or 0 new connections would be created. I was surprised: 4 new connections are created; so if I run twice the function I have 8 new connections... Maybe what i will do, instead of pooling will be: https://stackoverflow.com/questions/12391174/how-to-close-idle-connections-in-postgresql-automatically/30769511 – fresko Oct 08 '18 at 12:58

1 Answers1

0

I write the answer because I found an error about how I was using the connectionSource(). I was executing the query without releasing the connection:

ConnectionSource cs = getConnectionSource();
DatabaseConnection dbc = cs.getReadOnlyConnection("my_table");
dbc.executeStatement("Select count(*) from my_table;", JdbcDatabaseConnection.DEFAULT_RESULT_FLAGS);

and the connection was never released, so it also was not removed/reused. Now I added the following, and it works as expected:

cs.releaseConnection(dbc);
fresko
  • 1,890
  • 2
  • 24
  • 25