1

Since moving from 9.5 to 12 we are seeing alot of errors in the database log. The query that is being run is a basic update statement which changes the date of when a user is logged in.

2021-03-03 09:23:43.235 CET [18820] ERROR: cannot execute UPDATE in a read-only transaction

We did not see these errors when we were on an older version of postgres.

I have checked the default_transaction_read_only and thats set to off which is correct.

Why would I be seeing this now?

rdbmsNoob
  • 121
  • 1
  • 12

1 Answers1

7

Please verify that you are not in a read-only transaction:

SHOW default_transaction_read_only;
SHOW transaction_read_only;

If the first is off and the second is on, you must have explicitly started a read-only transaction with

BEGIN READ ONLY;

If you are not inside a read-only transaction, the only explanation is that the database is in recovery mode.

Perhaps you connected to a streaming replication standby server.

To verify, see if the following returns “true”:

SELECT pg_is_in_recovery();
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • We only have 1 postgres database server. Suspecting an application side issue. Is there anything else I could check as to why we see this? Thanks @laurenz-albe – rdbmsNoob Mar 08 '21 at 16:32
  • No. I am certain that is the issue. See my extended answer. – Laurenz Albe Mar 08 '21 at 17:25
  • Hi @laurenz-albe ran the query in the extended answer, the database is not in recovery mode. – rdbmsNoob Mar 10 '21 at 08:58
  • I have added more diagnostic material to the answer. Perhaps you started a read-only transaction without realizing it. – Laurenz Albe Mar 10 '21 at 09:14
  • Thanks @laurenz-albe when I run the same query from my local to the same database the application is running against it works fine. The results of both default_transaction_read_only and transaction_read_only both came back as off. I am leaning more towards the way the applicaiton is executing. – rdbmsNoob Mar 10 '21 at 10:05
  • Of course you should not check these things from the command line, but from the actual application code. I thought that went without saying. Add some diagnostic code. – Laurenz Albe Mar 10 '21 at 10:23