Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

Designer Studio query runner errors: "the connection is closed"

SA-56721

Summary



Error occurs when using Designer Studio Query Runner wizard. This occurs despite adding the correct PegaRULES:DatabaseAdministrator role to the access group and restarting the system.




Error Messages



There was a problem performing a database query: Database-General Problem encountered when getting connection for database pegadata 0 08003 This connection has been closed.
DatabaseException caused by prior exception: org.postgresql.util.PSQLException: This connection has been closed.
| SQL Code: 0 | SQL State: 08003



Steps to Reproduce



Run the Query Runner.


Root Cause



A defect or configuration issue in the operating environment. 
The connection pool elements in the context.xml were insufficient to guarantee that the connection provided to the application was valid. An explicit set of connection pool parameters had to be provided for this.



Resolution



Perform the following local-change:

Insert the following attributes to the Resource element for the jdbc/PegaRULES connection.

In the context.xml, add the following:


testOnBorrow="true"
testOnConnect="true"
testOnReturn="true"
testWhileIdle="true"
validationQuery="select 1"


This causes the validation query to excute at specific points in time as follows:

testOnBorrow: If true, connections are validated before being returned from the pool. If the validation fails, the connection is destroyed and a new connection is retrieved from the pool (and validated).

testOnConnect: This indicates if objects are validated when a connection is first created. If an object fails to validate, it display an SQLException. 

testOnReturn: If true, connections are validated before being returned to the pool. If the validation fails, the connection is destroyed instead of being returned to the pool.

testWhileIdle: If true, connections are validated by the idle connection evictor (if any). If the validation fails, the connection is destroyed and removed from the pool.

The validation query above is valid for Postgres.

Consult the specific database vendor for appropriate validation query to use.

For long running queries that are being 'disconnected', modify the following:


removeAbandoned="true"
removeAbandonedTimeout="some large number greater than the longest expected query"

 
Additionally, consider setting  removeAbandoned="false"

To log abandoned connections, set

logAbandoned="true"

Published December 29, 2018 - Updated October 8, 2020

Was this useful?

0% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice