Jira (PDB-5483) PuppetDB ERROR: cannot set transaction read-write mode during recovery After Upgrade to 7.10

81 views
Skip to first unread message

Jon-Paul Lindquist (Jira)

unread,
Apr 13, 2022, 4:40:02 PM4/13/22
to puppe...@googlegroups.com
Jon-Paul Lindquist updated an issue
 
PuppetDB / Bug PDB-5483
PuppetDB ERROR: cannot set transaction read-write mode during recovery After Upgrade to 7.10
Change By: Jon-Paul Lindquist
Summary: PuppetDB Error ERROR:  cannot set transaction read-write mode during recovery After Upgrade > to 7. 9 10
Add Comment Add Comment
 
This message was sent by Atlassian Jira (v8.20.2#820002-sha1:829506d)
Atlassian logo

Jon-Paul Lindquist (Jira)

unread,
Apr 13, 2022, 4:46:02 PM4/13/22
to puppe...@googlegroups.com
Jon-Paul Lindquist updated an issue
h2. Who found the bug?

[~jplindquist] 
h2. Where was the bug found?
* System: Open Source Puppet Server with PuppetDB
* Version: CentOS 7, PostgreSQL 14
* Operating system(s):  CentOS 7
* Puppet version: 7.15

h2. What is malfunctioning?

Unable to upgrade PuppetDB from 7.9.2-1.el7 7.10.0-1.el7 due to error on the read database

 
{noformat}2022-04-13 13:20:29.295 MST [25657] ERROR:  cannot set transaction read-write mode during recovery
2022-04-13 13:20:29.295 MST [25657] STATEMENT:  set transaction read write{noformat}
 

It seems to be coming from here ([https://github.com/puppetlabs/puppetdb/ compare blob / 7 1423988e4a6e097f4eb1b1a95b43b62dc3d39dd4/src/puppetlabs/puppetdb/jdbc . 9.2...7.10.0 clj # diff-2c460cab69fb9759dca82e8ddcb35006c25939e621c2fdbcd9fe97be96e7692fR651 L651 ]  I'm no Clojure expert, but I'm wondering if that {color:#e8912d}when read-only?{color} is correct when it should not be setting read write on a read only replica.  I've downgraded back to 7.9 in the meantime to restore service, but we're unable to update past this for the time being.
h2. What does success look like?

Successfully upgrading to the latest PuppetDB 7.10+
h2. How will success be validated?

yum upgrade puppetdb && systemctl restart puppetdb

without failure or logged errors on the database server
h2. Should anyone be contacted after this is fixed?

[~jplindquist] 

Jon-Paul Lindquist (Jira)

unread,
Apr 13, 2022, 4:46:03 PM4/13/22
to puppe...@googlegroups.com
Jon-Paul Lindquist updated an issue
h2. Who found the bug?

[~jplindquist] 
h2. Where was the bug found?
* System: Open Source Puppet Server with PuppetDB
* Version: CentOS 7, PostgreSQL 14
* Operating system(s):  CentOS 7
* Puppet version: 7.15

h2. What is malfunctioning?

Unable to upgrade PuppetDB from 7.9.2-1.el7 7.10.0-1.el7 due to error on the read database

 
{noformat}2022-04-13 13:20:29.295 MST [25657] ERROR:  cannot set transaction read-write mode during recovery
2022-04-13 13:20:29.295 MST [25657] STATEMENT:  set transaction read write{noformat}
 

It seems to be coming from here ([https://github.com/puppetlabs/puppetdb/compare/7.9.2...7.10.0#diff-2c460cab69fb9759dca82e8ddcb35006c25939e621c2fdbcd9fe97be96e7692fR651 ). ]  I'm no Clojure expert, but I'm wondering if that {color:#e8912d}when read-only?{color} is correct when it should not be setting read write on a read only replica.  I've downgraded back to 7.9 in the meantime to restore service, but we're unable to update past this for the time being.

h2. What does success look like?

Successfully upgrading to the latest PuppetDB 7.10+
h2. How will success be validated?

yum upgrade puppetdb && systemctl restart puppetdb

without failure or logged errors on the database server
h2. Should anyone be contacted after this is fixed?

[~jplindquist] 

Jon-Paul Lindquist (Jira)

unread,
Apr 13, 2022, 4:48:02 PM4/13/22
to puppe...@googlegroups.com
Jon-Paul Lindquist updated an issue
h2. Who found the bug?

[~jplindquist] 
h2. Where was the bug found?
* System: Open Source Puppet Server with PuppetDB
* Version: CentOS 7, PostgreSQL 14
* Operating system(s):  CentOS 7
* Puppet version: 7.15

h2. What is malfunctioning?

Unable to upgrade PuppetDB from 7.9.2-1.el7 7.10.0-1.el7 due to error on the read database

 
{noformat}2022-04-13 13:20:29.295 MST [25657] ERROR:  cannot set transaction read-write mode during recovery
2022-04-13 13:20:29.295 MST [25657] STATEMENT:  set transaction read write{noformat}
 

It seems to be coming from here ([https://github.com/puppetlabs/puppetdb/blob/1423988e4a6e097f4eb1b1a95b43b62dc3d39dd4/src/puppetlabs/puppetdb/jdbc.clj#L651] ).   I'm no Clojure expert, but I'm wondering if that {color:#e8912d}when read-only?{color} is correct when it should not be setting read write on a read only replica.  I've downgraded back to 7.9 in the meantime to restore service, but we're unable to update past this for the time being.

h2. What does success look like?

Successfully upgrading to the latest PuppetDB 7.10+
h2. How will success be validated?

yum upgrade puppetdb && systemctl restart puppetdb

without failure or logged errors on the database server
h2. Should anyone be contacted after this is fixed?

[~jplindquist] 

Jon-Paul Lindquist (Jira)

unread,
Apr 13, 2022, 5:19:02 PM4/13/22
to puppe...@googlegroups.com
Jon-Paul Lindquist updated an issue
h2. Who found the bug?

[~jplindquist] 
h2. Where was the bug found?
* System: Open Source Puppet Server with PuppetDB
* Version: CentOS 7, PostgreSQL 14
* Operating system(s):  CentOS 7
* Puppet version: 7.15

h2. What is malfunctioning?

Unable to upgrade PuppetDB from 7.9.2-1.el7 to 7.10.0-1.el7 due to error on the read database


 
{noformat}2022-04-13 13:20:29.295 MST [25657] ERROR:  cannot set transaction read-write mode during recovery
2022-04-13 13:20:29.295 MST [25657] STATEMENT:  set transaction read write{noformat}
 

It seems to be coming from here ([https://github.com/puppetlabs/puppetdb/blob/1423988e4a6e097f4eb1b1a95b43b62dc3d39dd4/src/puppetlabs/puppetdb/jdbc.clj#L651]).  I'm no Clojure expert, but I'm wondering if that {color:#e8912d}when read-only?{color} is correct when it should not be setting read write on a read only replica.  I've downgraded back to 7.9 in the meantime to restore service, but we're unable to update past this for the time being.
h2. What does success look like?

Successfully upgrading to the latest PuppetDB 7.10+
h2. How will success be validated?

yum upgrade puppetdb && systemctl restart puppetdb

without failure or logged errors on the database server
h2. Should anyone be contacted after this is fixed?

[~jplindquist] 

Rob Browning (Jira)

unread,
Apr 13, 2022, 9:45:02 PM4/13/22
to puppe...@googlegroups.com
Rob Browning commented on Bug PDB-5483
 
Re: PuppetDB ERROR: cannot set transaction read-write mode during recovery After Upgrade to 7.10

Hmm, might be helpful to understand a bit more about the environment. Initially I wondered if there could have been a change in pg 14 that was incompatible with the approach that code takes, but I tested it here, and it still appears to work, (though so far, I've only tested via psql).  I'll plan to investigate further (likely on Friday):

psql (14.2 (Debian 14.2-1+b2))
 
Type "help" for help.
 
 
 
puppetdb=> begin read only;
 
BEGIN
 
puppetdb=*> set transaction read write;
 
SET
 
puppetdb=*> update pg_settings set setting = false where name = 'jit';
 
 set_config
 
------------
 
 off
 
(1 row)
 
 
 
UPDATE 0
 
puppetdb=*> set transaction read only;
 
SET
 
puppetdb=*>

In any case, what does your broader arrangement look like?  For example, you mentioned a "read only replica", how is that arranged?

Oh, and with respect to the read-only? code, that just allows us to turn off the jit, even on connections coming from the read-only pool, which we use for all the incoming queries (not commands), in part, to provide additional security.

Jon-Paul Lindquist (Jira)

unread,
Apr 14, 2022, 1:16:03 PM4/14/22
to puppe...@googlegroups.com

We have a primary server and replica server configured as a hot standby using a replication slot running Postgresql 14

//  postgresql.conf (standby)

primary_slot_name = standby
 
hot_standby = on
 
hot_standby_feedback = on

with a standby.signal file to trigger replication and read-only

https://www.postgresql.org/docs/current/warm-standby.html#FILE-STANDBY-SIGNAL

There is no replication lag between the 2, but the standby server is always in recovery essentially because it is simply replaying wal segments from the primary regularly

 

postgres=# select pg_is_in_recovery();
 
 pg_is_in_recovery
 
-------------------
 
 t
 
postgres=# begin read only;
 
BEGIN
 
postgres=*# set transaction read write;
 
ERROR:  cannot set transaction read-write mode during recovery
 
postgres=!#

PuppetDB is configured with both database.ini (pointed to the primary write server), and read_database.ini (pointed to the read-only replica).

I think I did find a way to get the same outcome by updating that setting while setup as a hot standby in read-only though, so hopefully this helps as well:

https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-USERS

Transaction management commands that explicitly set non-read-only state: (SET TRANSACTION READ WRITE) are explicitly denied while configured as hot standby, but you can change a run time parameter settings using SET

 

postgres=# begin read only;
 
BEGIN
 
postgres=*# select setting from pg_settings where name = 'jit';
 
 setting
 
---------
 
 on
 
(1 row)
 
postgres=*# SET jit TO false;
 
SET
 
postgres=*# select setting from pg_settings where name = 'jit';
 
 setting
 
---------
 
 off
 
(1 row)
 
postgres=*# end;
 
COMMIT
 
postgres=# select setting from pg_settings where name = 'jit';
 
 setting
 
---------
 
 off
 
(1 row)

 

 

Pim M (Jira)

unread,
Apr 20, 2022, 10:35:03 AM4/20/22
to puppe...@googlegroups.com
Pim M commented on Bug PDB-5483

We have the same error on our end. Running PuppetDB 7.10 in a Docker container together with postgres 14 as primary - secondary setup.

Postgres logs:

 

2022-04-20 14:16:56.137 UTC [169] STATEMENT:  set transaction read write
 
2022-04-20 14:16:56.545 UTC [170] ERROR:  cannot set transaction read-write mode during recovery

 

 

Puppetdb logs:

Caused by: com.zaxxer.hikari.pool.PoolBase$ConnectionSetupException: org.postgresql.util.PSQLException: ERROR: cannot set transaction read-write mode during recovery
 
        at com.zaxxer.hikari.pool.PoolBase.setupConnection(PoolBase.java:441)
 
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:380)
 
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:204)
 
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:459)
 
        at com.zaxxer.hikari.pool.HikariPool.access$200(HikariPool.java:70)
 
        at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:696)
 
        at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:682)
 
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
 
        ... 3 common frames omitted
 
Caused by: org.postgresql.util.PSQLException: ERROR: cannot set transaction read-write mode during recovery
 
        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.PgStatement.executeWithFlags(PgStatement.java:329)
 
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315)
 
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291)
 
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:286)
 
        at com.zaxxer.hikari.pool.PoolBase.executeSql(PoolBase.java:568)
 
        at com.zaxxer.hikari.pool.PoolBase.setupConnection(PoolBase.java:436)
 
        ... 10 common frames omitted
 
2022-04-20 14:16:58,797 INFO  [p.t.internal] Beginning shutdown sequence

Running commands on master:

psql (14.2)
 
Type "help" for help.
 
 
 
postgres=# begin read only;
 
BEGIN
 
 
 
postgres=*# set transaction read write;
 
SET
 
 
 
postgres=*# update pg_settings set setting = false where name = 'jit';
 
 set_config 
 
------------
 
 off
 
(1 row)
 
UPDATE 0
 
 
 
postgres=*# set transaction read only;
 
SET
 
 
 
postgres=*# 

Running commands on replica:

psql (14.2)
 
Type "help" for help.
 
 
 
postgres=# begin read only;
 
BEGIN
 
 
 
postgres=*# set transaction read write;
 
ERROR:  cannot set transaction read-write mode during recovery
 
 
 
postgres=!# update pg_settings set setting = false where name = 'jit';
 
ERROR:  current transaction is aborted, commands ignored until end of transaction block
 
 
 
postgres=!# set transaction read only;
 
ERROR:  current transaction is aborted, commands ignored until end of transaction block
 
 
 
postgres=!# 

 

David McTavish (Jira)

unread,
Apr 25, 2022, 10:19:02 AM4/25/22
to puppe...@googlegroups.com
David McTavish updated an issue
 
Change By: David McTavish
Component/s: PuppetDB

Jon-Paul Lindquist (Jira)

unread,
Apr 27, 2022, 8:16:02 PM4/27/22
to puppe...@googlegroups.com
Jon-Paul Lindquist commented on Bug PDB-5483
 
Re: PuppetDB ERROR: cannot set transaction read-write mode during recovery After Upgrade to 7.10

Did some more testing, and it seems like the session set was the original intention, so I put this together in hopes that it helps

https://github.com/puppetlabs/puppetdb/pull/3670 (Hopefully that's formatted properly and everything). I am not terribly familiar with the clojure language again), but I made an attempt smile.png

 

 

-bash-4.2$ psql -d puppetdb
 
psql (14.2)
 
Type "help" for help.
 
puppetdb=# \x
 
Expanded display is on.
 
puppetdb=# select * from pg_settings where name = 'jit';
 
-[ RECORD 1 ]---+-------------------------------------
 
name            | jit
 
setting         | on
 
unit            |
 
category        | Query Tuning / Other Planner Options
 
short_desc      | Allow JIT compilation.
 
extra_desc      |
 
context         | user
 
vartype         | bool
 
source          | default
 
min_val         |
 
max_val         |
 
enumvals        |
 
boot_val        | on
 
reset_val       | on
 
sourcefile      |
 
sourceline      |
 
pending_restart | f
 
puppetdb=# set session jit = off;
 
SET
 
puppetdb=# select * from pg_settings where name = 'jit';
 
-[ RECORD 1 ]---+-------------------------------------
 
name            | jit
 
setting         | off
 
unit            |
 
category        | Query Tuning / Other Planner Options
 
short_desc      | Allow JIT compilation.
 
extra_desc      |
 
context         | user
 
vartype         | bool
 
source          | session
 
min_val         |
 
max_val         |
 
enumvals        |
 
boot_val        | on
 
reset_val       | on
 
sourcefile      |
 
sourceline      |
 
pending_restart | f

 

Jon-Paul Lindquist (Jira)

unread,
Apr 28, 2022, 12:03:01 PM4/28/22
to puppe...@googlegroups.com

Looks like this jit  setting doesn't exist in pg 9.6 which is causing some integration test failures for 6.x.  Not sure if there's some way to conditionally run that if pgsql > 9.6

Rob Browning (Jira)

unread,
May 19, 2022, 3:35:03 PM5/19/22
to puppe...@googlegroups.com
Rob Browning updated an issue
 
Change By: Rob Browning
Affects Version/s: PDB 6.21.0

Jonathan Newman (Jira)

unread,
May 24, 2022, 1:40:01 PM5/24/22
to puppe...@googlegroups.com
Jonathan Newman updated an issue
Change By: Jonathan Newman
Sprint: Dumpling 6-7-22

Jonathan Newman (Jira)

unread,
May 24, 2022, 1:40:03 PM5/24/22
to puppe...@googlegroups.com
Jonathan Newman updated an issue
Change By: Jonathan Newman
Story Points: 1

Jonathan Newman (Jira)

unread,
May 24, 2022, 1:40:03 PM5/24/22
to puppe...@googlegroups.com
Jonathan Newman updated an issue
Change By: Jonathan Newman
Team: Dumpling

Rob Browning (Jira)

unread,
May 24, 2022, 1:41:02 PM5/24/22
to puppe...@googlegroups.com
Rob Browning assigned an issue to Rob Browning
Change By: Rob Browning
Assignee: Rob Browning

Rob Browning (Jira)

unread,
May 25, 2022, 3:26:01 PM5/25/22
to puppe...@googlegroups.com
Rob Browning updated an issue
Change By: Rob Browning
Fix Version/s: PDB 6.21.1
Fix Version/s: PDB 7.11.0

Rob Browning (Jira)

unread,
May 25, 2022, 3:29:01 PM5/25/22
to puppe...@googlegroups.com
Rob Browning updated an issue
Change By: Rob Browning
Release Notes: Bug Fix
Release Notes Summary: The approach taken in the previous changes (cf. PDB-5483) to disable the jit was incompatible with PostgreSQL hot standbys: https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-USERS

The method of disabling the jit has been changed to avoid the problem.

Stel Abrego (Jira)

unread,
Jul 18, 2022, 3:12:03 PM7/18/22
to puppe...@googlegroups.com
Stel Abrego updated an issue
Change By: Stel Abrego
Fix Version/s: PDB 6.21.1
Fix Version/s: PDB 6.22.0
Reply all
Reply to author
Forward
0 new messages