0

As a database we use PostgreSQL. Application uses spring libraries, and transaction is managed by spring. Transactional annotation is used correctly, where it is needed as read-only, and where it is used with write abilities. But in logs we occasinally see exception

2020-12-30 11:39:13.513 [jmsContainer-8] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ERROR: cannot execute nextval() in a read-only transaction
2020-12-30 11:39:13.515 [jmsContainer-8] ERROR o.s.t.s.TransactionSynchronizationUtils - TransactionSynchronization.afterCompletion threw exception
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not extract ResultSet
        at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:147)
        at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155)
        at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:162)
        at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:780)
        at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:758)
        at jdk.internal.reflect.GeneratedMethodAccessor2794.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:301)
        at com.sun.proxy.$Proxy226.persist(Unknown Source)

Connections to PostgreSQL is provided through pg_bouncer, and pool_mode=sesssion, the server_reset_query=DISCARD ALL.

We tried restarting the pg_bouncer, though maybe some connection by default set read-only and being kept alive, but it did not help. Any thoughts?

ernestk
  • 72
  • 6
  • Also tried to randomly get connection from pg_bouncer and check the setting column from pg_settings where name=transaction_read_only. Run 5000 times, and all show **off**. Maybe we could not came across such of a connection. – ernestk Dec 30 '20 at 05:59
  • In postgresql.conf the default_transaction_read_only is set to off – ernestk Dec 30 '20 at 06:00

0 Answers0