Subject: [pgjdbc/pgjdbc] PR #3012: Support multiple hosts defined with single hostname

From: "Squiry (@Squiry)" <[email-masked]>
Date: Fri, 24 Nov 2023 13:30:47 +0000

The implementation is pretty simple but the test is... well, ugly, but I have no idea how to make it any better.

Resolves #3011

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sat, 25 Nov 2023 11:40:00 +0000

(on build-logic/jvm/src/main/kotlin/build-logic.test-base.gradle.kts)

tests run on java 8 so this won't work

---

From: "Squiry (@Squiry)" <[email-masked]>
Date: Sat, 25 Nov 2023 15:36:32 +0000

Ok, on java8 test should work even without that flag. I will fix it later today.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Mon, 27 Nov 2023 09:37:15 +0000

(on pgjdbc/src/main/java/org/postgresql/hostchooser/HostChooserFactory.java:45)

I am afraid it would break TLS hostname verification.

We already support multi-host. What we miss is IP-based balancing.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 27 Nov 2023 12:47:03 +0000

How does it work for libpq then ?

---

From: "Squiry (@Squiry)" <[email-masked]>
Date: Mon, 27 Nov 2023 12:47:39 +0000

I can put `InetAddress` in `HostSpec` and use it in `PGStream` (I believe this is the only place where actual connection is created), will it be ok?

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Mon, 27 Nov 2023 13:01:09 +0000

Sure we need to remember both "hostname" and "the resolved IP address".

Suppose there's "db1" which resolves to 3 IP addresses and "db2" which resolves to 15 IP addresses.
Does it mean we distribute the load as 3:15? Should we distribute the load as 1:1 and then make even distribution among the IPs behind each hostname?

I suggest we settle on that before adjusting the implementation.

---

From: "Squiry (@Squiry)" <[email-masked]>
Date: Mon, 27 Nov 2023 13:17:09 +0000

Tbh I don't think that this would be the case, more like "db1" that resolves to 18 ip addresses and that's it.
Right now implementation doesn't even bother to detect ip addresses if multiple hosts was defined.

---

From: "Squiry (@Squiry)" <[email-masked]>
Date: Mon, 27 Nov 2023 14:02:02 +0000

Libpq shuffles provided hosts [here](https://github.com/postgres/postgres/blob/master/src/interfaces/libpq/fe-connect.c#L1712) and than shuffles resolved address of each host [here](https://github.com/postgres/postgres/blob/master/src/interfaces/libpq/fe-connect.c#L2617). Looks more like a 1:1 case. This will require a little bit more changes on pgjdbc side.

---

From: "Squiry (@Squiry)" <[email-masked]>
Date: Tue, 28 Nov 2023 16:41:40 +0000

So, [libpq](https://github.com/postgres/postgres/blob/master/src/interfaces/libpq/fe-connect.c#L3967) looks at provided hosts as a single backend per host: if it connects to any address of provided host and finds out it is not in a desired state it rejects the host and doesn't even bother try next addresses.

Golangs [pgx](https://github.com/jackc/pgx/blob/master/pgconn/pgconn.go#L147) treats every address as a separate instance and tries them all, only stops on auth errors. That behavior would be more preferred for me as I have case for single hostname backed with addresses for every db instance.

Error propagation becomes more interesting anyway, because connecting to something like localhost will throw some interesting exceptions like `Could not find a server with specified targetServerType: any` instead of auth errors, but it can be fixed.

---

From: "Squiry (@Squiry)" <[email-masked]>
Date: Mon, 04 Dec 2023 08:40:15 +0000

@vlsi @davecramer so which implementation should I pick as reference.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 04 Dec 2023 12:01:46 +0000

actually, cloud providers do this quite a bit. Where there is 1 writer and N readers. Active-Active is gaining traction in PostgreSQL so having N writers and M readers will be coming. If I had to choose between the two implementations it would probably be libpq only because more interfaces use it.

---

From: "x4m (@x4m)" <[email-masked]>
Date: Sat, 15 Feb 2025 17:08:06 +0000

So, did you choose anything? Right now I'm reviewing patch about it into libpq, and current libpq behavior looks to me like a bug :)
https://www.postgresql.org/message-id/flat/CAKK5BkESSc69sp2TiTWHvvOHCUey0rDWXSrR9pinyRqyfamUYg%40mail.gmail.com

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 16 Feb 2025 12:36:20 +0000

What did you consider a bug?

I read the link and couldn’t figure out the bug part

Dave Cramer

On Sat, Feb 15, 2025 at 12:08 PM Andrey Borodin ***@***.***>
wrote:

> ***@***.**** commented on this pull request.
> ------------------------------
>
> In pgjdbc/src/main/java/org/postgresql/hostchooser/HostChooserFactory.java
> <https://github.com/pgjdbc/pgjdbc/pull/3012#discussion_r1957158530>:
>
> > +      resultHosts[i] = new HostSpec(inetAddress.getHostAddress(), host.getPort(),
> +          host.getLocalSocketAddress());
>
> So, did you choose anything? Right now I'm reviewing patch about it into
> libpq, and current libpq behavior looks to me like a bug :)
>
> —
> Reply to this email directly, view it on GitHub
> <https://github.com/pgjdbc/pgjdbc/pull/3012#discussion_r1957158530>, or
> unsubscribe
> <https://github.com/notifications/unsubscribe-auth/AADDH5W66IUGTYMYRSIJXNL2P5YAXAVCNFSM6AAAAABXGUUWUOVHI2DSMVQWIX3LMV43YUDVNRWFEZLROVSXG5CSMV3GSZLXHMZDMMJZGQ3DGOBVGQ>
> .
> You are receiving this because you were mentioned.Message ID:
> ***@***.***>
>

---

From: "x4m (@x4m)" <[email-masked]>
Date: Mon, 17 Feb 2025 08:29:01 +0000

> What did you consider a bug? I read the link and couldn’t figure out the bug part

If I have a single DNS name that is resolved to all IPs from Highly-available cluster, libpq will probe only first one.