Subject: [pgjdbc/pgjdbc] PR #2741: feat: add transaction isolation mode

From: "prokop7 (@prokop7)" <[email-masked]>
Date: Tue, 31 Jan 2023 12:43:12 +0000

add transaction isolation mode to be able to work with transaction pooling tools like pgbouncer

### All Submissions:

* [x] Have you followed the guidelines in our [Contributing](https://github.com/pgjdbc/pgjdbc/blob/master/CONTRIBUTING.md) document?
* [x] Have you checked to ensure there aren't other open [Pull Requests](../../pulls) for the same update/change?

<!-- You can erase any parts of this template not applicable to your Pull Request. -->

### New Feature Submissions:

1. [x] Does your submission pass tests?
2. [x] Does `./gradlew autostyleCheck checkstyleAll` pass ?
3. [x] Have you added your new test classes to an existing test suite in alphabetical order?

### Transaction isolation mode
Adding this functionality discussed here https://github.com/pgjdbc/pgjdbc/issues/1880. Currently isolation level is set on connection level. This solution doesn't work with transaction pooling because the connection can be replaced after setting isolation.

Proposed solution adds transaction isolation mode, which can be on of `transaction` or `session`, by default it's session. 
If the mode set to `transaction` then all `BEGIN` statements appended with isolation level like `BEGIN ISOLATION LEVEL SERIALIZABLE`

The solution also works with READ ONLY modifier which was added in earlier versions.

Implementation requires 3 flags, one for mode (0 - session mode, 1 - transaction mode) and two for isolation level (00 - READ UNCOMMITTED, 01 - READ COMMITTED, 10 - REPEATABLE READ, 11 - SERIALIZABLE)

--- 

Solves https://github.com/pgjdbc/pgjdbc/issues/1880
Related to this PR, but it's already outdated and solved https://github.com/pgjdbc/pgjdbc/issues/848

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 31 Jan 2023 13:26:52 +0000

Thanks for the PR. 
We will have to add tests to confirm behaviour

---

From: "prokop7 (@prokop7)" <[email-masked]>
Date: Mon, 27 Feb 2023 16:48:05 +0000

@davecramer, I have managed to add test to confirm intended behaviour

---

From: "prokop7 (@prokop7)" <[email-masked]>
Date: Tue, 28 Mar 2023 09:38:59 +0000

Is there any chance of getting it into the main branch?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 28 Mar 2023 10:54:49 +0000

(on pgjdbc/src/main/java/org/postgresql/PGProperty.java:810)

I'm unclear what 'setting readOnly to setting isolation level' is trying to convey ?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 28 Mar 2023 10:57:48 +0000

(on pgjdbc/src/main/java/org/postgresql/core/v3/QueryExecutorImpl.java)

using shift right is probably clearer as to what you are really doing.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 28 Mar 2023 12:17:17 +0000

(on pgjdbc/src/main/java/org/postgresql/jdbc/PgConnection.java)

Does this change the current behaviour? Currently we throw an exception if the leveName is null. https://github.com/pgjdbc/pgjdbc/blob/3c3e5b62349c42d9d3dfe1fe0102776d4de8c353/pgjdbc/src/main/java/org/postgresql/jdbc/PgConnection.java#L1061

---

From: "prokop7 (@prokop7)" <[email-masked]>
Date: Thu, 30 Mar 2023 14:21:46 +0000

This method wan't used at all, removed it

---

From: "prokop7 (@prokop7)" <[email-masked]>
Date: Thu, 30 Mar 2023 14:22:12 +0000

Wrong copy-paste, thanks for catching it

---

From: "prokop7 (@prokop7)" <[email-masked]>
Date: Thu, 13 Apr 2023 09:25:47 +0000

May I kindly remind about this PR?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 13 Apr 2023 12:11:51 +0000

probably worth adding & 3 in case this ever becomes a negative number

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 13 Apr 2023 12:12:39 +0000

Need to fix checker framework errors .

---

From: "prokop7 (@prokop7)" <[email-masked]>
Date: Mon, 02 Oct 2023 12:10:07 +0000

@davecramer can you please revisit this PR, I've fixed all issues?

---

From: "vdharam18 (@vdharam18)" <[email-masked]>
Date: Wed, 25 Sep 2024 16:07:15 +0000

Hey Folks, Thanks for raising the fix for this issue. I also observe a similar data inconsistency issue with PgBouncer and transaction pooling mode. It would be better if this fix could be prioritised and more people can use it.

---

From: "vdharam18 (@vdharam18)" <[email-masked]>
Date: Wed, 25 Sep 2024 16:08:06 +0000

In the meantime, has anyone found any alternatives to solve this problem?

---

From: "hyragano (@hyragano)" <[email-masked]>
Date: Fri, 22 Nov 2024 20:35:32 +0000

> Hey Folks, Thanks for raising the fix for this issue. I also observe a similar data inconsistency issue with PgBouncer and transaction pooling mode. It would be better if this fix could be prioriti…

Actually we use it in production about 2 years from this branch however we cannot find some time to fix requested changes.

---

From: "prokop7 (@prokop7)" <[email-masked]>
Date: Mon, 02 Dec 2024 10:16:07 +0000

@davecramer I've updated the branch. Now it's fully compatible with new tests
Could you please recheck this?

---

From: "Waldemared (@Waldemared)" <[email-masked]>
Date: Tue, 05 Aug 2025 13:16:17 +0000

@davecramer Any news on when this feature might be available?