Database connection timeout when running PEcAn.utils::convert.input

[ashiklom]

When PEcAn.utils::convert.input takes a long time (e.g. processing 100 years of met), the database connection object can close due to a time-out, triggering an error like this:

Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
)
Warning in postgresqlQuickSQL(conn, statement, ...) :
  Could not create execute: UPDATE inputs SET start_date='1901-06-01', end_date='2000-12-31'  WHERE id=99000000001
2019-05-24 21:05:46 SEVERE [PEcAn.DB::db.query] : 
   Error executing db query 'UPDATE inputs SET start_date='1901-06-01', 
   end_date='2000-12-31' WHERE id=99000000001' errorcode=0 message='server 
   closed the connection unexpectedly This probably means the server 
   terminated abnormally before or while processing the request. ' 

I think we should be able to check the validity of database connections with DBI::dbIsValid(con) and then re-open the connection if necessary; i.e.

if (!DBI::dbIsValid(con)) con <- PEcAn.DB::db.open(params)

However, that requires convert.input to be somehow made aware of the database configuration (params), which AFAICT it currently isn't.

[ashiklom]

More generally, how do we feel about shifting our philosophy away from passing around database connections to passing around database connection parameters and using more small, transient connections (e.g. open a separate connection for each query, and close it on query success)?

[robkooper]

There is a cost associated with opening the connection, which is why I was trying to make sure we keep the connection. If the driver can use connection pooling that might be an option. That being said, not sure if there is a simple solution for this problem, however I would like to see a consistent use of this.

Reading up a little bit on OO in R (https://adv-r.hadley.nz/oo.html) to see if we can use this.

[github-actions]

This issue is stale because it has been open 365 days with no activity.