Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux