0

Environment: Java + Hibernate + Postgres 10 + on-premise + Pgbouncer

The usage scenario is that created a role with the following settings for the development team to test the application:

pg_roles:

rolsuperuser = t
rolinherit = t
rolcreaterole = f
rolcreatedb = f
rolcanlogin = t
rolreplication = f

Problem: When tests with write : Intermittent "ERROR: 25006: cannot execute UPDATE in a read-only transaction" errors in Postgres logs on the master node

Situation: No replication is configured for the affected node. Therefore, database operations can be performed regularly without the error. However, the error occurs occasionally without a clear explanation.

Verified and Tested Solutions:

select pg_is_in_recovery();

 pg_is_in_recovery
f

postgresql.conf

settings for transaction isolation and read-only behavior are set correctly.

default_transaction_deferrable = off 
default_transaction_isolation = read committed 
default_transaction_read_only = off 
transaction_deferrable = off 
transaction_isolation = read committed 
transaction_read_only = off

Pgbouncer pool mode is set to session.

No failover or maintenance tasks (VACUUM, ANALYZE) were identified before or after the error.

Additional Information:

The error occurs in both DBeaver and the application.

5
  • Hi, and welcome to dba.se! Have you tried using the psql client? That should always be your first recourse if there's an issue - frameworks/non-standard clients (esp. for some reason, Java-based ones) can have all sorts of autocommit and/or batch size or whatever issues. Go back to basics and work from there! If you're on Windows, you could try HeidiSQL - a Delphi client (ah... those were the days...) - used to be a Delphi programmer and for some reason, Delphi clients seem to "just work" (personal opinion).
    – Vérace
    Commented Jun 29 at 16:35
  • If pg_is_in_recovery() returns FALSE and transaction_read_only is off, it is impossible that you get that error message. Commented Jul 1 at 6:23
  • @Vérace, Thank you for your reply. Yes, I'm using the psql client to manage the database. However, the end users won't be using psql directly. I created the role with the permissions listed above for them to access the application. Commented Jul 2 at 12:35
  • Hi @LaurenzAlbe, It's entirely possible! I've double-checked all the basic role configuration settings several times to ensure they're correct. Is there anything else I can do to troubleshoot this issue? Commented Jul 2 at 12:35
  • Sorry, I cannot help you with that. In the PostgreSQL I know, that doesn't happen. Commented Jul 2 at 13:01

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.