From: | Rob Sargent <robjsargent(at)gmail(dot)com> |
---|---|
To: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Does the postgres jdbc driver (rev 42.3) cache prepared statements |
Date: | 2022-02-14 20:08:42 |
Message-ID: | 37636351-a447-2ecd-6dd1-31a8c4253052@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-02-14 21:44:06 | Re: table not found on publisher |
Previous Message | Bruce Momjian | 2022-02-14 20:04:06 | Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute" |