Subject: [pgjdbc/pgjdbc] PR #3506: feat: Add access for global host status tracker

From: "robert-mirzakhanian (@robert-mirzakhanian)" <[email-masked]>
Date: Sun, 02 Feb 2025 19:40:34 +0000

### All Submissions:

* [V] Have you followed the guidelines in our [Contributing](https://github.com/pgjdbc/pgjdbc/blob/master/CONTRIBUTING.md) document?
* [V] 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. [V] Does your submission pass tests?
2. [V] Does `./gradlew styleCheck` pass ?
3. [V] Have you added your new test classes to an existing test suite in alphabetical order?

### Changes to Existing Features:

* [  ] Does this break existing behaviour? If so please explain.
* [V] Have you added an explanation of what your changes do and why you'd like us to include them?
* [V] Have you written new tests for your core changes, as applicable?
* [V] Have you successfully run tests with your changes locally?

### Explain feature:
When connecting, the driver receives information about hosts in ascending order of delay. If one of the three available servers goes down for maintenance, connections are switched to other free hosts. However, when the first server comes back online, the driver does not notice this and does not reconnect to it.

Our goal is to understand how the driver sees available hosts, and force connections to be closed so that the driver can reconnect to the first \ host.

---

From: "robert-mirzakhanian (@robert-mirzakhanian)" <[email-masked]>
Date: Sun, 02 Feb 2025 19:51:20 +0000

Can someone tell me what's missing [here](https://ci.appveyor.com/project/davecramer/pgjdbc/builds/51438131)?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 02 Feb 2025 20:23:50 +0000

I think you would actually have to try to connect to the hosts to see if they were available.
Also set transaction read only is not really reliable. calling `pg_is_in_recovery()` is

---

From: "robert-mirzakhanian (@robert-mirzakhanian)" <[email-masked]>
Date: Sun, 02 Feb 2025 20:40:57 +0000

My database is hosted in the cloud, and my provider has the API. I use it to get a list of all hosts, their roles, and the current state of. In the background, I periodically check the status of these hosts. As soon as one of the nearest ones becomes available again, I can restore connections, which will reduce the delay in processing\requests. In order not to close the connection just like that, I want to first understand which host the driver is connected to.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 02 Feb 2025 20:44:22 +0000

I'm not sure how a generic driver would be able to do that?

---

From: "robert-mirzakhanian (@robert-mirzakhanian)" <[email-masked]>
Date: Sun, 02 Feb 2025 20:52:06 +0000

I use postgres jdbc driver with HikaryCP. Hikary have methods `softEvictConnections()`. When the program understands that the host has returned to its normal state, and the driver still considers this host to be in the ConnectFail state, programm will call softEvictConnections(). All connections will be created anew starting from the first host, because it will take more time than hostRecheckSeconds.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 02 Feb 2025 21:20:24 +0000

I understand. 
The challenge is figuring out that the host is now available

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 03 Feb 2025 15:34:20 +0000

(on pgjdbc/src/main/java/org/postgresql/hostchooser/GlobalHostStatusTracker.java:83)

What is a "card" ?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 03 Feb 2025 15:37:28 +0000

> Can someone tell me what's missing [here](https://ci.appveyor.com/project/davecramer/pgjdbc/builds/51438131)?

Ignore it. Appveyor isn't particularly stable.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 03 Feb 2025 15:38:00 +0000

so I still don't understand how this is supposed to be used?

---

From: "robert-mirzakhanian (@robert-mirzakhanian)" <[email-masked]>
Date: Mon, 03 Feb 2025 15:41:47 +0000

sorry, i mean map

---

From: "robert-mirzakhanian (@robert-mirzakhanian)" <[email-masked]>
Date: Mon, 03 Feb 2025 21:19:38 +0000

> so I still don't understand how this is supposed to be used?

So, i wrote small example with test. Hope this makes the idea of using it clearer.
Example:
```kotlin
package ru.github.rmirzakhanian

import com.zaxxer.hikari.HikariDataSource
import org.postgresql.hostchooser.GlobalHostStatusTracker
import org.postgresql.hostchooser.HostStatus
import org.postgresql.util.HostSpec

class CloudHandler(
    private val bestHost: String,
    private val client: Client,
    private val secondaryDataSource: HikariDataSource
) {
    enum class Status { ALIVE, DEAD, UNKNOWN }
    enum class Role { Primary, Secondary }
    enum class DataCenter { SSS, VVV, KKK }
    data class HostInfo(val host: String, val status: Status, val role: Role, val dc: DataCenter)

    class Client {
        fun getInfo(): List<HostInfo> {
            return listOf(
                // HostInfo("host1", Status.ALIVE, Role.Primary, DataCenter.SSS),
                // HostInfo("host2", Status.ALIVE, Role.Secondary, DataCenter.VVV),
                // HostInfo("host3", Status.ALIVE, Role.Secondary, DataCenter.KKK)
            )
        }
    }

    private var wasDown = false

    //periodic calls from background thread
    fun checkHosts() {
        val host = client.getInfo().find { it.host == bestHost } ?: return
        if (host.status != Status.ALIVE) {
            wasDown = true
        } else if (wasDown) {
            val hostStatus = GlobalHostStatusTracker.getHostStatusMap()[HostSpec(bestHost, 5432)]
            if (hostStatus == HostStatus.ConnectFail) {
                secondaryDataSource.hikariPoolMXBean.softEvictConnections()
            }
            wasDown = false
        }
    }
}
```
Test
```kotlin
import com.zaxxer.hikari.HikariDataSource
import com.zaxxer.hikari.HikariPoolMXBean
import io.mockk.every
import io.mockk.mockk
import io.mockk.verify
import org.junit.Test
import org.postgresql.hostchooser.GlobalHostStatusTracker
import org.postgresql.hostchooser.HostStatus
import org.postgresql.util.HostSpec
import ru.github.rmirzakhanian.CloudHandler
import ru.github.rmirzakhanian.CloudHandler.DataCenter
import ru.github.rmirzakhanian.CloudHandler.HostInfo
import ru.github.rmirzakhanian.CloudHandler.Role
import ru.github.rmirzakhanian.CloudHandler.Status

class CloudHandlerTest {

    // The microservice stores information about its current data center location and sorts database connections based on low to high latency.
    // About the test: Imagine that the microservice is already running and has established a connection
    // to the database jdbc:postgresql://host2:5433,host1:5432,host3:5434/some_db
    // host2
    @Test
    fun testHostRecovery() {
        // Test client that returns the next status on each call to getInfo().
        val client = mockk<CloudHandler.Client>()

        // Define a sequence of statuses:
        // 1st call: ALIVE, 2nd call: DEAD, 3rd call: ALIVE (return to normal state)
        every { client.getInfo() } returnsMany listOf(
            listOf(
                HostInfo("host1", Status.ALIVE, Role.Primary, DataCenter.SSS),
                HostInfo("host2", Status.ALIVE, Role.Secondary, DataCenter.VVV),
                HostInfo("host3", Status.ALIVE, Role.Secondary, DataCenter.KKK)
            ),
            listOf(
                HostInfo("host1", Status.ALIVE, Role.Primary, DataCenter.SSS),
                HostInfo("host2", Status.DEAD, Role.Secondary, DataCenter.VVV),
                HostInfo("host3", Status.ALIVE, Role.Secondary, DataCenter.KKK)
            ),
            listOf(
                HostInfo("host1", Status.ALIVE, Role.Primary, DataCenter.SSS),
                HostInfo("host2", Status.ALIVE, Role.Secondary, DataCenter.VVV),
                HostInfo("host3", Status.ALIVE, Role.Secondary, DataCenter.KKK)
            )
        )

        val mockkDataSource = mockk<HikariDataSource>()
        val mockkHikariPoolMXBean = mockk<HikariPoolMXBean>()
        every { mockkDataSource.hikariPoolMXBean } returns mockkHikariPoolMXBean
        every { mockkHikariPoolMXBean.softEvictConnections() } returns Unit

        // Create a CloudHandler instance with the test client.
        val handler = CloudHandler("host2", client, mockkDataSource)

        // After create datasource's to primary database and secondary database.
        // Postgres report two records in GlobalHostStatusTracker.
        GlobalHostStatusTracker.reportHostStatus(HostSpec("host1", 5432), HostStatus.Primary)
        GlobalHostStatusTracker.reportHostStatus(HostSpec("host2", 5432), HostStatus.Secondary)

        // Simulate sequential calls to checkHosts(), as if they occur every 30 seconds.
        handler.checkHosts() // 1st call: status is ALIVE, wasDown flag remains false.

        // During operation, host 2 shuts down to service the server.
        // The Postgres driver understands this and switches to the next replica in the list. Now it is host3
        // After switches Postgres driver report 2 rows in GlobalHostStatusTracker.

        GlobalHostStatusTracker.reportHostStatus(HostSpec("host2", 5432), HostStatus.ConnectFail)
        GlobalHostStatusTracker.reportHostStatus(HostSpec("host3", 5432), HostStatus.Secondary)

        handler.checkHosts() // 2nd call: status is DEAD, wasDown flag becomes true.

        // Waiting some calls when host2 return to normal state...

        handler.checkHosts() // 10th call: status is ALIVE, recovery detected, mockkHikariPoolMXBean.softEvictConnections should be called.

        // Checking mockkHikariPoolMXBean.softEvictConnections called exactly once
        verify(exactly = 1) { mockkHikariPoolMXBean.softEvictConnections() }
    }
}

```

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 03 Feb 2025 21:24:15 +0000

So `Client` is some library that checks with an external API to figure out if the host is alive ?

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Tue, 04 Feb 2025 07:10:46 +0000

Frankly speaking, I am leaning towards implementing https://github.com/pgjdbc/pgjdbc/pull/3409 or something like that.
I am afraid if we expose `GlobalHostStatusTracker` as in the current PR, then it might complicate the implementation of 3409. WDYT?

---

From: "robert-mirzakhanian (@robert-mirzakhanian)" <[email-masked]>
Date: Tue, 04 Feb 2025 09:41:31 +0000

> So `Client` is some library that checks with an external API to figure out if the host is alive ?

@davecramer, yep.

> Frankly speaking, I am leaning towards implementing #3409 or something like that. I am afraid if we expose `GlobalHostStatusTracker` as in the current PR, then it might complicate the implementation of 3409. WDYT?

I saw this pr, but I didn't like it. In this solution, as you (@vlsi) already wrote, it duplicates the current functionality and, ideally, it should be done through a listener and sending notifications via the `reportHostStatus` method. But even in this solution, there is a risk that if the developer adds complex logic to the listener, the entire driver operation will begin to slow down.
My solution only provides read access to the Map where the driver's view of hosts is stored. @vlsi, please explain how my solution affects pr [#3304](https://github.com/pgjdbc/pgjdbc/pull/3409)?

If there is another way to get information from the driver about which host currently has a Connection established?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 04 Feb 2025 12:51:52 +0000

> I saw this pr, but I didn't like it. In this solution, as you (@vlsi) already wrote, it duplicates the current functionality and, ideally, it should be done through a listener and sending notifications via the `reportHostStatus` method. But even in this solution, there is a risk that if the developer adds complex logic to the listener, the entire driver operation will begin to slow down. My solution only provides read access to the Map where the driver's view of hosts is stored. @vlsi, please explain how my solution affects pr [#3304](https://github.com/pgjdbc/pgjdbc/pull/3409)?
> 

Yes, generally through some kind of topology function in the database. For instance Aurora has [`aurora_replica_status()`](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora_replica_status.html) I'm not a huge fan of the driver reaching out to another port, or API

I would entertain the pluggable idea in #3409

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Tue, 04 Feb 2025 12:58:19 +0000

>My solution only provides read access to the Map where the driver's view of hosts is stored. @vlsi, please explain how my solution affects pr https://github.com/pgjdbc/pgjdbc/pull/3409?

Your solution/suggestion is to make `GlobalHostStatusTracker` a part of the public API.
If we make load-balancing pluggable, it would be hard to retrofit `GlobalHostStatusTracker` for the cases when the status is stored in the plugin code. At the same time, the plugin might have its own vision on the topology, and so on.

---

From: "robert-mirzakhanian (@robert-mirzakhanian)" <[email-masked]>
Date: Tue, 04 Feb 2025 13:14:20 +0000

> I would entertain the pluggable idea in #3409

@davecramer, @vlsi, I apologize if my explanation in English is unclear.
My goal is not to retrieve topology from the driver, but to determine which host the driver is currently connected to. The only place I could find relevant information is in GlobalHostStatusTracker.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 04 Feb 2025 14:47:44 +0000

Explain it in Russian then, @vlsi can understand it and I can translate it.

---

From: "robert-mirzakhanian (@robert-mirzakhanian)" <[email-masked]>
Date: Tue, 04 Feb 2025 19:12:29 +0000

Позвольте ещё раз объяснить, но только на русском языке.

Когда в подключении к базе данных указываются несколько хостов, возникает проблема. Например, для подключения к базе данных можно использовать следующий адрес: jdbc:postgresql://host2:5433,host1:5432,host3:5434/some_db.

Хосты host1, host2 и host3 расположены в разных датацентрах (dc1, dc2 и dc3 соответственно). Сетевые запросы между этими датацентрами при больших объемах данных могут замедляться, что вполне логично, учитывая трафик между ними.

Мы можем ранжировать хосты по удалённости датацентра от сервиса, но возникает проблема, когда один из хостов отключается для проведения технических работ. В таком случае драйвер PostgreSQL переключается на следующий доступный хост, в данном случае на host1. После восстановления host2 драйвер не возвращается обратно.

Я хочу получать от драйвера информацию о том, к какому хосту он подключен. Если я решу, что это не тот хост, который мне нужен, я смогу закрыть текущие соединения и открыть их заново.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Tue, 04 Feb 2025 19:18:11 +0000

> Я хочу получать от драйвера информацию о том, к какому хосту он подключен.

В каждый момент времени может быть несколько соединений к разным базам. Одно соединение может быть к primary, другое к secondary, да и вообще может быть несколько datasource'ов к разным базам.

Может, вам нужен метод, который вернёт host/port на уровне соединения? Например, `PGConnection#getHost`, `PGConnoction#getPort`?

---

From: "robert-mirzakhanian (@robert-mirzakhanian)" <[email-masked]>
Date: Tue, 04 Feb 2025 19:28:27 +0000

> Одно соединение может быть к primary, другое к secondary

Верно, но в GlobalHostStatusTracker это четко разделено

> да и вообще может быть несколько datasource'ов к разным базам

В целом да, но в моем случае такого нет

> Может, вам нужен метод, который вернёт host/port на уровне соединения? Например, `PGConnection#getHost`, `PGConnoction#getPort`?

В целом да. Такое тоже могло бы подойти. Правда я не вижу этих методов в [PGConnection](https://github.com/pgjdbc/pgjdbc/blob/15596302e6ab9ccdefe25c9bd91284d5f232ee27/pgjdbc/src/main/java/org/postgresql/jdbc/PgConnection.java)

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 04 Feb 2025 19:40:18 +0000

So I don't really see this functionality belonging in the driver. There are wrapper drivers that do this kind of thing.

---

From: "robert-mirzakhanian (@robert-mirzakhanian)" <[email-masked]>
Date: Tue, 04 Feb 2025 20:12:39 +0000

> So I don't really see this functionality belonging in the driver. There are wrapper drivers that do this kind of thing.

Сейчас сам драйвер с помощью [MultiHostChooser](https://github.com/pgjdbc/pgjdbc/blob/15596302e6ab9ccdefe25c9bd91284d5f232ee27/pgjdbc/src/main/java/org/postgresql/hostchooser/MultiHostChooser.java) принимает решение к какому из 3 хостов ему подключиться. Как драйвер оболочка может это понять?

---

From: "robert-mirzakhanian (@robert-mirzakhanian)" <[email-masked]>
Date: Fri, 07 Feb 2025 09:10:31 +0000

@davecramer, @vlsi есть идеи? Что будем делать с ПР?

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Fri, 07 Feb 2025 09:39:29 +0000

@robert-mirzakhanian , предлагаю добавить метод `PGConnection#getHost()` или подобный. Давайте обсудим в https://github.com/pgjdbc/pgjdbc/issues/3514?