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

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

Browse pgsql-general by date

  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"