Обсуждение: Does the postgres jdbc driver (rev 42.3) cache prepared statements
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:
Thanks in advance,
rjs
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