Does the postgres jdbc driver (rev 42.3) cache prepared statements

[Rob Sargent <robjsargent@xxxxxxxxx>]

    I have an embedded
      tomcat talking directly to postgres server via sql generated by
      jOOQ.  By default jOOQ creates a prepared statement for all
      selects, and does so on each invocation from the client (client in
      this case is a servlet in tomcat).  I've been using jOOQ-to-db for
      a while but the servlet part in new - not run in production until
      now. Tomcat(9.0.54) is using it's internal pooling and I'm not
      ruling out that as point of attack but thought I would ask here
      first.
      
      Is there a prepared statement cache within the jdbc driver?  If
      so, what is the mechanism for generating the key ("S_1" perhaps)
      and what controls do I have on that?
      
      The actual clients are genetic analysis software running same code
      on multiple machines, all hitting db (sparingly) through tomcat. 
      They will all run the example "select people" on start up. 
      There's a matching "select markers".  There could be a day between
      the one call and another (- batched of jobs started).  DB
      interaction is either at initiation or completion, hours to days
      apart.  The same "people" and "marker" calls happen at start and
      finish of jobs (for different usages).
      
      Seems I'm getting hit from both sides:
    
> "damned if you do":
>       SEVERE: Servlet.service() for servlet [Pedfile] in context with
>       path [/sgs] threw exception
>       org.jooq.exception.DataAccessException: SQL [select
>       "base"."people"."id" as "peopleId", "base"."people"."name" as
>       "pedname", "ego"."name", coalesce("pa"."name", ?) as "paname",
>       coalesce("ma"."name", ?) as "m$
>               at org.jooq_3.14.7.POSTGRES.debug(Unknown Source)
>               at org.jooq.impl.Tools.translate(Tools.java:2880)
>               at
> org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:757)
>               at
>       org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:389)
>               at
>       org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:333)
>               at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2880)
>               at
> edu.utah.camplab.servlet.PedfileServlet.readPedData(PedfileServlet.java:124)
>               at
>       edu.utah.camplab.servlet.PedfileServlet.doGet(PedfileServlet.java:58)
>               at
>       javax.servlet.http.HttpServlet.service(HttpServlet.java:687)
>               at
>       javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
>               at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
>               at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
>               at
> org.apache.catalina.filters.CorsFilter.handleNonCORS(CorsFilter.java:357)
>               at
>       org.apache.catalina.filters.CorsFilter.doFilter(CorsFilter.java:176)
>               at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
>               at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
>               at
> org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
>               at
> org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
>               at
> org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
>               at
> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
>               at
> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
>               at
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
>               at
> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
>               at
> org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
>               at
> org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
>               at
> org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)
>               at
> org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1722)
>               at
> org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
>               at
> org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
>               at
> org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
>               at
> org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
>               at java.base/java.lang.Thread.run(Thread.java:833)
>       Caused by: org.postgresql.util.PSQLException: ERROR: prepared
>       statement "S_1" already exists
>               at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
>               at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
>               at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
>               at
>       org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
>               at
>       org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
>               at
> org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166)
>               at
> org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:155)
>               at
>       jdk.internal.reflect.GeneratedMethodAccessor49.invoke(Unknown
>       Source)
>               at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>               at
>       java.base/java.lang.reflect.Method.invoke(Method.java:568)
>               at
> org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
>               at jdk.proxy3/jdk.proxy3.$Proxy15.execute(Unknown Source)
>               at
> org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:214)
>               at
> org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:4178)
>               at
>       org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:279)
>               at
>       org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:375)
>               ... 28 more
>       
>       "damned if you don't"
>       SEVERE: Servlet.service() for servlet [PayloadSave] in context
>       with path [/sgs] threw exception
>       org.jooq.exception.DataAccessException: Cannot commit transaction
>               at
> org.jooq.impl.DefaultConnectionProvider.commit(DefaultConnectionProvider.java:116)
>               at
> org.jooq.impl.DefaultTransactionProvider.commit(DefaultTransactionProvider.java:193)
>               at
> org.jooq.impl.DefaultDSLContext.lambda$transactionResult0$0(DefaultDSLContext.java:547)
>               at org.jooq.impl.Tools$35$1.block(Tools.java:5203)
>               at
> java.base/java.util.concurrent.ForkJoinPool.unmanagedBlock(ForkJoinPool.java:3463)
>               at
> java.base/java.util.concurrent.ForkJoinPool.managedBlock(ForkJoinPool.java:3434)
>               at org.jooq.impl.Tools$35.get(Tools.java:5200)
>               at
> org.jooq.impl.DefaultDSLContext.transactionResult0(DefaultDSLContext.java:595)
>               at
> org.jooq.impl.DefaultDSLContext.transactionResult(DefaultDSLContext.java:512)
>               at
>       org.jooq.impl.DefaultDSLContext.transaction(DefaultDSLContext.java:612)
>               at
>       edu.utah.camplab.jx.PayloadFromMux.writedb(PayloadFromMux.java:34)
>               at
>       edu.utah.camplab.jx.AbstractPayload.write(AbstractPayload.java:67)
>               at
> edu.utah.camplab.servlet.PayloadSaveServlet.doPost(PayloadSaveServlet.java:59)
>               at
>       javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
>               at
>       javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
>               at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
>               at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
>               at
> org.apache.catalina.filters.CorsFilter.handleNonCORS(CorsFilter.java:357)
>               at
>       org.apache.catalina.filters.CorsFilter.doFilter(CorsFilter.java:176)
>               at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
>               at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
>               at
> org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
>               at
> org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
>               at
> org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
>               at
> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
>               at
> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
>               at
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
>               at
> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
>               at
> org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
>               at
> org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
>               at
> org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)
>               at
> org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1722)
>               at
> org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
>               at
> org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
>               at
> org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
>               at
> org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
>               at java.base/java.lang.Thread.run(Thread.java:833)
>       Caused by: org.postgresql.util.PSQLException: ERROR: prepared
>       statement "S_1" does not exist
>               at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
>               at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
>               at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
>               at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:315)
>               at
> org.postgresql.jdbc.PgConnection.executeTransactionCommand(PgConnection.java:855)
>               at
>       org.postgresql.jdbc.PgConnection.commit(PgConnection.java:877)
>               at
>       jdk.internal.reflect.GeneratedMethodAccessor78.invoke(Unknown
>       Source)
>               at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>               at
>       java.base/java.lang.reflect.Method.invoke(Method.java:568)
>               at
> org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:131)
>               at
> org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
>               at
> org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:79)
>               at
> org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
>               at
> org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
>               at jdk.proxy3/jdk.proxy3.$Proxy4.commit(Unknown Source)
>               at
> org.jooq.impl.DefaultConnectionProvider.commit(DefaultConnectionProvider.java:113)
>     
    
    Thanks in advance,
    rjs