PuppetDB and PostgreSQL - Transaction isolation level 4 not supported

Question

Scenario: My old configuration was SLES11 PuppetMaster 3.7.5 server and a SLES11 PuppetDB 2.3.3 Server (PostgreSQL 9.4, JDBC 9.1 and JDK 1.7) which works with no problem.

I've also installed a new test SLES12 to see if PostgreSQL 10 works with our PuppetDB. When PuppetDB tries to remotely connect/write, it gives the error "transaction isolation level 4 not supported" .

According to the link, postgresql Transaction isolation level 4 not supported my JDBC drivers are old.

I've tried to update them to JBDC 42.2 but it still gives the same error, both with the remote PostgreSQL 10 and with the local PostgreSQL 9.4 (which has no problem with the JDBC 9.1).

Please provide the full stacktrace of the exception. And I recommend that you double-check the class path if your application to be sure you don't still have an old version of the driver on the path. — Mark Rotteveel, Oct 07 '19 at 12:25
`"2019-09-05 12:34:42,679 ERROR [c.p.p.command] [54390b551-8549a-4455-erbb-cer8ere19a25] [replace catalog] Retrying after attempt 4, due to: org.postgresql.util.PSQLException: Transaction isolation level 4 not supported."` My "application" is PuppetDB. Wouldn't know how and where to easily check it — dandan, Oct 07 '19 at 13:52
It appears that the exception is being thrown by the JDBC driver, though a stack trace would make that clearer. I'm inclined to believe that whatever update you performed did not affect the copy of the driver that PuppetDB is using. — John Bollinger, Oct 07 '19 at 23:27
TO be more precise. I tried both to actually update the driver on the old machine with JDBC 9.4, and as well to install everything from scratch as for PuppetDB, PostgreSQL10 and JDBC42.2 on a new blank SLES12. Therefor I highly doubt that "old driver" are the issue. Can a combination of other stuff raise this error? I have no clue how to edit the PuppetDB.jar or JDBC to add any debug. — dandan, Oct 08 '19 at 06:31

score 0 · Answer 1 · answered Oct 07 '19 at 13:31

java.sql.Connection.TRANSACTION_REPEATABLE_READ is 4.

Support for REPEATABLE READ was introduced in commit 67ee14e879d on Jan 13 2004, unless I am mistaken:

                else
                {
-                       isolationLevelSQL = "SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL ";
-                       switch (isolationLevel)
-                       {
-                               case Connection.TRANSACTION_READ_COMMITTED:
-                                       isolationLevelSQL += "READ COMMITTED";
-                                       break;
-                               case Connection.TRANSACTION_SERIALIZABLE:
-                                       isolationLevelSQL += "SERIALIZABLE";
-                                       break;
-                               default:
-                                       throw new PSQLException("postgresql.con.isolevel", PSQLState.TRANSACTION_STATE_INVALID,
-                                                                                       new Integer(isolationLevel));
-                       }
+                       isolationLevelSQL = "SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL " + getIsolationLevelName(level);
                }
                execSQL(isolationLevelSQL);
+               isolationLevel = level;
+       }
+
+       protected String getIsolationLevelName(int level) throws SQLException
+       {
+               boolean pg75 = haveMinimumServerVersion("7.5");
+
+               if (level == Connection.TRANSACTION_READ_COMMITTED) {
+                       return " READ COMMITTED";
+               } else if (level == Connection.TRANSACTION_SERIALIZABLE) {
+                       return " SERIALIZABLE";
+               } else if (pg75 && level == Connection.TRANSACTION_READ_UNCOMMITTED) {
+                       return " READ UNCOMMITTED";
+               } else if (pg75 && level == Connection.TRANSACTION_REPEATABLE_READ) {
+                       return " REPEATABLE READ";
+               }
+               throw new PSQLException("postgresql.con.isolevel", PSQLState.TRANSACTION_STATE_INVALID, new Integer(level));
        }

So I'd assume that you are inadvertently using some very old JDBC driver.

As I've clearly written in my post, I've already read that it could be the reason, and therefore I'm using the latest JDBC drivers and are the only one installed on the server. — dandan, Oct 07 '19 at 13:54
yes thank you. I have a clean installation with only JDBC 42.2, therefor I'm wondering what else could it be. RIght now the drivers are only on the PuppetDB which has locally installed also PostgreSQL, not on the puppet master nor the agent node — dandan, Oct 07 '19 at 21:22

score 0 · Answer 2 · answered Oct 10 '19 at 14:15

0

Once again, I did a clean new installation. PuppetDB 2.3.3, PostreSQL10 and JDBC 42.2.2 and I have the transaction level 4 issue.

answered Oct 10 '19 at 14:15

dandan

121
1
8

PuppetDB and PostgreSQL - Transaction isolation level 4 not supported

2 Answers2