Re: postgresql-jdbc driver not respecting prepareThreshold=0

Hi Malcolm,

I've posted this as an issue https://github.com/pgjdbc/pgjdbc/issues/742

I hope to have some time to look at this today

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 2 February 2017 at 17:22, Malcolm Studd <malcolm(dot)studd(at)tradingcentral(dot)com
> wrote:

> I am running an application in wildfly 9.0.2 using postgres 9.2.19
> databases accessed through pgBouncer 1.7.2 in transaction pooling mode.
> With recent versions of postgresql-jdbc, the application is failing with
> prepared statement does not exist errors.
>
> The database connections have preparedThreshold set to 0 as documented in
> the pgBouncer FAQ (https://pgbouncer.github.io/f
> aq.html#how-to-use-prepared-statements-with-transaction-pooling).
>
> This works with postgresql-jdbc versions 9.4-1205 and 9.4.1208. It does
> not work in any version since 1208.
>
> I would like to keep packages up-to-date, so any help is much appreciated.
> Packages are centos 6, pgdg 9.2, and wildfly .tar.gz.
>
> Stack traces and configuration follow below.
>
> Thanks,
>
> Malcolm Studd
>
>
>
> The stack trace from 9.4.1212. The reported error is the same in 1211, and
> I think 1210.
>
> 2017-02-02 11:36:11,284 INFO [org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl]
> (Batch Thread - 3) HHH000010: On release of batch it still contained JDBC
> statements
> 2017-02-02 11:36:11,286 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper]
> (Batch Thread - 3) SQL Error: 0, SQLState: 26000
> 2017-02-02 11:36:11,287 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper]
> (Batch Thread - 3) Batch entry 0 update instrument001.t0050instrument_symbol
> set symbol_format_id=1 where instrument_id='FI-144471' and
> symbol_format_id=1 w
> as aborted: ERROR: prepared statement "S_2" does not exist Call
> getNextException to see other errors in the batch.
> 2017-02-02 11:36:11,288 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper]
> (Batch Thread - 3) SQL Error: 0, SQLState: 26000
> 2017-02-02 11:36:11,288 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper]
> (Batch Thread - 3) ERROR: prepared statement "S_2" does not exist
> 2017-02-02 11:36:11,293 ERROR [org.hibernate.engine.jdbc.batch.internal.BatchingBatch]
> (Batch Thread - 3) HHH000315: Exception executing batch [could not execute
> batch]
> 2017-02-02 11:36:11,298 ERROR [exchange-sync-FI(4695877)
> com.recognia.batch.listener.TaskChunkListener] (Batch Thread - 3) Batch
> chunk exception: javax.transaction.RollbackException: ARJUNA016053: Could
> not commit transaction.
> at com.arjuna.ats.internal.jta.transaction.arjunacore.Transacti
> onImple.commitAndDisassociate(TransactionImple.java:1212)
> at com.arjuna.ats.internal.jta.transaction.arjunacore.BaseTrans
> action.commit(BaseTransaction.java:126)
> at com.arjuna.ats.jbossatx.BaseTransactionManagerDelegate.
> commit(BaseTransactionManagerDelegate.java:75)
> at org.jberet.runtime.runner.ChunkRunner.doCheckpoint(ChunkRunn
> er.java:529)
> at org.jberet.runtime.runner.ChunkRunner.readProcessWriteItems(
> ChunkRunner.java:307)
> at org.jberet.runtime.runner.ChunkRunner.run(ChunkRunner.java:192)
> at org.jberet.runtime.runner.StepExecutionRunner.runBatchletOrC
> hunk(StepExecutionRunner.java:212)
> at org.jberet.runtime.runner.StepExecutionRunner.run(StepExecut
> ionRunner.java:139)
> at org.jberet.runtime.runner.CompositeExecutionRunner.runStep(C
> ompositeExecutionRunner.java:164)
> at org.jberet.runtime.runner.CompositeExecutionRunner.runJobEle
> ment(CompositeExecutionRunner.java:128)
> at org.jberet.runtime.runner.CompositeExecutionRunner.runDecisi
> on(CompositeExecutionRunner.java:176)
> at org.jberet.runtime.runner.CompositeExecutionRunner.runJobEle
> ment(CompositeExecutionRunner.java:134)
> at org.jberet.runtime.runner.StepExecutionRunner.run(StepExecut
> ionRunner.java:191)
> at org.jberet.runtime.runner.CompositeExecutionRunner.runStep(C
> ompositeExecutionRunner.java:164)
> at org.jberet.runtime.runner.CompositeExecutionRunner.runFromHe
> adOrRestartPoint(CompositeExecutionRunner.java:88)
> at org.jberet.runtime.runner.JobExecutionRunner.run(JobExecutio
> nRunner.java:54)
> at org.wildfly.jberet.services.BatchEnvironmentService$WildFlyB
> atchEnvironment$1.run(BatchEnvironmentService.java:139)
> at java.util.concurrent.Executors$RunnableAdapter.call(
> Executors.java:511)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPool
> Executor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoo
> lExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> at org.jboss.threads.JBossThread.run(JBossThread.java:320)
> Caused by: javax.persistence.PersistenceException:
> org.hibernate.exception.GenericJDBCException: could not execute batch
> at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(Abst
> ractEntityManagerImpl.java:1763)
> at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(Abst
> ractEntityManagerImpl.java:1677)
> at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(Abst
> ractEntityManagerImpl.java:1683)
> at org.hibernate.jpa.spi.AbstractEntityManagerImpl$CallbackExce
> ptionMapperImpl.mapManagedFlushFailure(AbstractEntityManager
> Impl.java:1882)
> at org.hibernate.engine.transaction.synchronization.internal.Sy
> nchronizationCallbackCoordinatorNonTrackingImpl.beforeComple
> tion(SynchronizationCallbackCoordinatorNonTrackingImpl.java:119)
> at org.hibernate.engine.transaction.synchronization.internal.Re
> gisteredSynchronization.beforeCompletion(RegisteredSynchroni
> zation.java:50)
> at org.jboss.as.txn.service.internal.tsr.JCAOrderedLastSynchron
> izationList.beforeCompletion(JCAOrderedLastSynchronizationList.java:116)
> at com.arjuna.ats.internal.jta.resources.arjunacore.Synchroniza
> tionImple.beforeCompletion(SynchronizationImple.java:76)
> at com.arjuna.ats.arjuna.coordinator.TwoPhaseCoordinator.before
> Completion(TwoPhaseCoordinator.java:371)
> at com.arjuna.ats.arjuna.coordinator.TwoPhaseCoordinator.end(Tw
> oPhaseCoordinator.java:91)
> at com.arjuna.ats.arjuna.AtomicAction.commit(AtomicAction.java:
> 162)
> at com.arjuna.ats.internal.jta.transaction.arjunacore.Transacti
> onImple.commitAndDisassociate(TransactionImple.java:1200)
> ... 22 more
> Caused by: org.hibernate.exception.GenericJDBCException: could not
> execute batch
> at org.hibernate.exception.internal.StandardSQLExceptionConvert
> er.convert(StandardSQLExceptionConverter.java:54)
> at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(Sql
> ExceptionHelper.java:126)
> at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.perfo
> rmExecution(BatchingBatch.java:136)
> at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.addTo
> Batch(BatchingBatch.java:98)
> at org.hibernate.persister.collection.OneToManyPersister.writeI
> ndex(OneToManyPersister.java:251)
> at org.hibernate.persister.collection.OneToManyPersister.insert
> Rows(OneToManyPersister.java:192)
> at org.hibernate.action.internal.CollectionUpdateAction.execute
> (CollectionUpdateAction.java:102)
> at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQu
> eue.java:465)
> at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQu
> eue.java:351)
> at org.hibernate.event.internal.AbstractFlushingEventListener.p
> erformExecutions(AbstractFlushingEventListener.java:350)
> at org.hibernate.event.internal.DefaultFlushEventListener.onFlu
> sh(DefaultFlushEventListener.java:56)
> at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1258)
> at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.
> java:425)
> at org.hibernate.engine.transaction.synchronization.internal.Sy
> nchronizationCallbackCoordinatorNonTrackingImpl.beforeComple
> tion(SynchronizationCallbackCoordinatorNonTrackingImpl.java:114)
> ... 29 more
> Caused by: java.sql.BatchUpdateException: Batch entry 0 update
> instrument001.t0050instrument_symbol set symbol_format_id=1 where
> instrument_id='FI-144471' and symbol_format_id=1 was aborted: ERROR:
> prepared statement "S_2" does not exist Call getNextException to see other
> errors in the batch.
> at org.postgresql.jdbc.BatchResultHandler.handleCompletion(Batc
> hResultHandler.java:163)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecut
> orImpl.java:471)
> at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.
> java:793)
> at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPrepa
> redStatement.java:1659)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(Wr
> appedStatement.java:1174)
> at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.perfo
> rmExecution(BatchingBatch.java:127)
> ... 40 more
> Caused by: org.postgresql.util.PSQLException: ERROR: prepared statement
> "S_2" does not exist
> at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorRespons
> e(QueryExecutorImpl.java:2455)
> at org.postgresql.core.v3.QueryExecutorImpl.processResults(Quer
> yExecutorImpl.java:2155)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecut
> orImpl.java:460)
> ... 44 more
>
>
>
>
> The stack trace from 9.4.1209
>
> 2017-02-02T12:02:26.883: ERROR: [com.recognia.batch.writer.ins
> trument.InstrumentActionCollectionWriter] Item writer exception:
> javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException:
> could not execute batch
> at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(Abst
> ractEntityManagerImpl.java:1763)
> at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(Abst
> ractEntityManagerImpl.java:1677)
> at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(Abst
> ractEntityManagerImpl.java:1683)
> at org.hibernate.jpa.spi.AbstractEntityManagerImpl.flush(Abstra
> ctEntityManagerImpl.java:1338)
> at org.hibernate.jpa.internal.QueryImpl.beforeQuery(QueryImpl.
> java:484)
> at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl
> .java:447)
> at com.recognia.common.data.ClearInstrumentData.clearDatabaseDa
> ta(ClearInstrumentData.java:47)
> at com.recognia.common.data.ClearInstrumentData.clearData(Clear
> InstrumentData.java:33)
> at com.recognia.batch.writer.instrument.InstrumentActionCollect
> ionWriter.deleteInstrument(InstrumentActionCollectionWriter.java:116)
> at com.recognia.batch.writer.instrument.InstrumentActionCollect
> ionWriter.doWriteItems(InstrumentActionCollectionWriter.java:69)
> at com.recognia.batch.writer.AbstractItemWriter.writeItems(Abst
> ractItemWriter.java:125)
> at org.jberet.runtime.runner.ChunkRunner.doCheckpoint(ChunkRunn
> er.java:512)
> at org.jberet.runtime.runner.ChunkRunner.readProcessWriteItems(
> ChunkRunner.java:307)
> at org.jberet.runtime.runner.ChunkRunner.run(ChunkRunner.java:192)
> at org.jberet.runtime.runner.StepExecutionRunner.runBatchletOrC
> hunk(StepExecutionRunner.java:212)
> at org.jberet.runtime.runner.StepExecutionRunner.run(StepExecut
> ionRunner.java:139)
> at org.jberet.runtime.runner.CompositeExecutionRunner.runStep(C
> ompositeExecutionRunner.java:164)
> at org.jberet.runtime.runner.CompositeExecutionRunner.runJobEle
> ment(CompositeExecutionRunner.java:128)
> at org.jberet.runtime.runner.CompositeExecutionRunner.runDecisi
> on(CompositeExecutionRunner.java:176)
> at org.jberet.runtime.runner.CompositeExecutionRunner.runJobEle
> ment(CompositeExecutionRunner.java:134)
> at org.jberet.runtime.runner.StepExecutionRunner.run(StepExecut
> ionRunner.java:191)
> at org.jberet.runtime.runner.CompositeExecutionRunner.runStep(C
> ompositeExecutionRunner.java:164)
> at org.jberet.runtime.runner.CompositeExecutionRunner.runFromHe
> adOrRestartPoint(CompositeExecutionRunner.java:88)
> at org.jberet.runtime.runner.JobExecutionRunner.run(JobExecutio
> nRunner.java:54)
> at org.wildfly.jberet.services.BatchEnvironmentService$WildFlyB
> atchEnvironment$1.run(BatchEnvironmentService.java:139)
> at java.util.concurrent.Executors$RunnableAdapter.call(
> Executors.java:511)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPool
> Executor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoo
> lExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> at org.jboss.threads.JBossThread.run(JBossThread.java:320)
> Caused by: org.hibernate.exception.GenericJDBCException: could not
> execute batch
> at org.hibernate.exception.internal.StandardSQLExceptionConvert
> er.convert(StandardSQLExceptionConverter.java:54)
> at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(Sql
> ExceptionHelper.java:126)
> at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.perfo
> rmExecution(BatchingBatch.java:136)
> at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.doExe
> cuteBatch(BatchingBatch.java:114)
> at org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.
> execute(AbstractBatchImpl.java:163)
> at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.getBa
> tch(JdbcCoordinatorImpl.java:215)
> at org.hibernate.persister.entity.AbstractEntityPersister.inser
> t(AbstractEntityPersister.java:3102)
> at org.hibernate.persister.entity.AbstractEntityPersister.inser
> t(AbstractEntityPersister.java:3581)
> at org.hibernate.action.internal.EntityInsertAction.execute(Ent
> ityInsertAction.java:104)
> at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQu
> eue.java:465)
> at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQu
> eue.java:351)
> at org.hibernate.event.internal.AbstractFlushingEventListener.p
> erformExecutions(AbstractFlushingEventListener.java:350)
> at org.hibernate.event.internal.DefaultFlushEventListener.onFlu
> sh(DefaultFlushEventListener.java:56)
> at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1258)
> at org.hibernate.jpa.spi.AbstractEntityManagerImpl.flush(Abstra
> ctEntityManagerImpl.java:1335)
> ... 27 more
> Caused by: java.sql.BatchUpdateException: Batch entry 0 /* insert
> com.recognia.model.eventdb.instrument.Symbol */ insert into
> instrument001.t0050instrument_symbol (symbol, instrument_id,
> symbol_format_id) values ('S0152', 'ES-4385622', 1) was aborted. Call
> getNextException to see the cause.
> at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResu
> ltHandler.java:137)
> at org.postgresql.core.v3.QueryExecutorImpl.processResults(Quer
> yExecutorImpl.java:2024)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecut
> orImpl.java:377)
> at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.
> java:1026)
> at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPrepa
> redStatement.java:1661)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(Wr
> appedStatement.java:1174)
> at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.perfo
> rmExecution(BatchingBatch.java:127)
> ... 39 more
>
>
>
> a datasource from wildfly domain.xml
> <datasource jta="false" jndi-name="java:jboss/datasources/eventdb001"
> pool-name="eventdb001" use-ccm="false">
>
> <connection-url>jdbc:postgresql://${recognia.datasource.
> eventdb001.host}:${recognia.datasource.eventdb001.port}/${recognia.
> datasource.eventdb001.name.work}</connection-url>
> <driver-class>org.postgresql.Driver</driver-class>
> <connection-property name="prepareThreshold">
> 0
> </connection-property>
> <driver>postgres</driver>
>
> <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
> <pool>
> <min-pool-size>1</min-pool-size>
> <max-pool-size>300</max-pool-size>
> <prefill>true</prefill>
>
> <flush-strategy>IdleConnections</flush-strategy>
> </pool>
> <security>
>
> <user-name>${recognia.datasource.eventdb001.user}</user-name>
>
> <password>${recognia.datasource.eventdb001.password}</password>
> </security>
> <validation>
> <valid-connection-checker
> class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.
> PostgreSQLValidConnectionChecker"/>
> <check-valid-connection-sql>select
> 1</check-valid-connection-sql>
> <validate-on-match>true</validate-on-match>
>
> <background-validation>true</background-validation>
>
> <background-validation-millis>5000</background-validation-millis>
> <stale-connection-checker
> class-name="org.jboss.jca.adapters.jdbc.extensions.novendor.
> NullStaleConnectionChecker"/>
> <exception-sorter
> class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.
> PostgreSQLExceptionSorter"/>
> </validation>
> <timeout>
> <idle-timeout-minutes>1</idle-timeout-minutes>
> </timeout>
> <statement>
> <track-statements>true</track-statements>
>
> <share-prepared-statements>false</share-prepared-statements>
> </statement>
> </datasource>
>
> --
>
>
> [image: Recognia] <http://www.recognia.com/> W www.recognia.com W
> www.tradingcentral.com [image: twitter] <https://twitter.com/Recognia>
> [image: facebook] <https://www.facebook.com/recognia/> [image: linkedin] <
> https://www.linkedin.com/company/recognia-inc->
>
> insightful analytics. expert analyst views. moving forward together.
>
> Ottawa
> 200-301 Moodie Drive
> Ottawa, ON, K2H 9C4
> T +1 613 789 2267 Hong Kong
> 60 Wyndham Street, 2003B
> Central, Hong Kong
> T +852 2522 3988 Paris
> 11 bis rue Scribe​
> 75009 Paris​
> T +33 1 5528 8040 London
> 36 Old Jewry
> London. EC2R 8DD
> T +44 203 440 7615 New York
> 60 Broad Street, Suite 3502
> New York, NY 10004
> T +1 212 847 2387
>
> *© TRADING CENTRALTM* [image: 1999-2016] The information contained in this
> email is for informational purposes only. It does not constitute advice or
> a recommendation by any of the TRADING CENTRAL group of companies,
> including Recognia Inc. ("TRADING CENTRAL") in respect of the investment in
> financial instruments. It is not intended as an offer or solicitation for
> the purchase or sale of any financial instrument. The investment in
> financial instruments is characterized by a certain degree of uncertainty.
> Any such investment involves risks for which TRADING CENTRAL is not liable.
> Any information offered herein reflects the current judgment of TRADING
> CENTRAL, which is not guaranteed, may be incomplete and may change without
> notice. Services in the U.S. are offered through TRADING CENTRAL Americas,
> Inc.. This message may contain confidential and proprietary information of
> TRADING CENTRAL and is intended only for the designated recipient(s). If
> you are not the intended recipient of this message you are hereby notified
> that any review, dissemination, distribution or copying of this message is
> strictly prohibited.
> Click here to unsubscribe <unsubscribe(at)recognia(dot)com>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>