Subject: [pgjdbc/pgjdbc] PR #3509: Add back application name setting

From: "joejensen (@joejensen)" <[email-masked]>
Date: Tue, 04 Feb 2025 22:47:31 +0000

Fixes #3508, by adding back the application_name startup parameters during the initial connection.

---

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

this https://github.com/pgjdbc/pgjdbc/pull/3491 doesn't fix it ?

---

From: "joejensen (@joejensen)" <[email-masked]>
Date: Tue, 04 Feb 2025 22:59:44 +0000

> this #3491 doesn't fix it ?

No, on line #906 it's actually returning immediately if the version >= 9.0 so it never reaches that code.  Also, the postgres logging doen't include the application name in the initial connection logs if it is not included at startup.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 04 Feb 2025 23:02:59 +0000

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

The problem is we really don't know the server version here. We don't know if we are on version 9 or not at this point

---

From: "joejensen (@joejensen)" <[email-masked]>
Date: Tue, 04 Feb 2025 23:09:21 +0000

Maybe I'm missing something but isn't the purpose of the minServerVersion, so that we can infer what we expect the minimum version to be?

I'd be happy to add another way(property) of indicating this should happen but it seems redundant.  Not having this occur at startup causes a regression from the last several driver versions for my use case.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 04 Feb 2025 23:24:03 +0000

I'd like to see the regression. 
FYI, there is no information in the startup telling use the version, we have to wait until we are connected to find out which is why it is moved later in the code

---

From: "joejensen (@joejensen)" <[email-masked]>
Date: Tue, 04 Feb 2025 23:41:18 +0000

The regression is for the issue described on #2111, I can provide more up to date log examples if needed.  

Essentially the initial application logging in the postgresql logs do not include the application name unless it is set in the startup request.

---

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

As I said the problem is we do not know the version number of the server until after the startup packet.

---

From: "joejensen (@joejensen)" <[email-masked]>
Date: Wed, 05 Feb 2025 00:12:28 +0000

I really would like to understand what I am misunderstanding.  Anticipating what the server version will be before connecting seems to be the entire purpose of the assumeMinServerVersion property.  If we remove this use case it does not appear to be used anywhere else.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 05 Feb 2025 00:14:33 +0000

In order to do that you would have to set that property. Are you doing that now ?

---

From: "joejensen (@joejensen)" <[email-masked]>
Date: Wed, 05 Feb 2025 00:15:23 +0000

yes

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 05 Feb 2025 00:17:24 +0000

In which case we would have to remove the setting of it later if it gets set here

---

From: "joejensen (@joejensen)" <[email-masked]>
Date: Wed, 05 Feb 2025 00:23:27 +0000

It isn't getting called later already  Nothing in runInitialQueries is occurring if you set a minimum server version >= 9.0

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 05 Feb 2025 00:24:28 +0000

My point is that I don't want it set 2x

---

From: "joejensen (@joejensen)" <[email-masked]>
Date: Wed, 05 Feb 2025 00:25:39 +0000

definitely agree with that.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 05 Feb 2025 00:25:56 +0000

we set application name in runInitialQueries

---

From: "joejensen (@joejensen)" <[email-masked]>
Date: Wed, 05 Feb 2025 00:31:09 +0000

But run initial queries is not getting executed if the assumeMinServerVersion is set to a value >= 9.0.  

https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/core/v3/ConnectionFactoryImpl.java#L911-L914

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 05 Feb 2025 00:42:09 +0000

Well that's just wrong. The other things need to be set as well.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 05 Feb 2025 00:46:03 +0000

Interesting as someone is setting APPLICATION_NAME
```
16385 | test    | 10879 |            |    16384 | test    | PostgreSQL JDBC Driver | 127.0.0.1   |                 |       52308 | 2025-02-04 19:43:53.65606-05  |                               | 2025-02-04 19:44:03.301573-05 | 2025-02-04 19:44:03.301968-05 | Client          | ClientRead          | idle   |             |              |          | SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname, a.attnotnull OR (t.typtype = 'd' AND t.typnotnull), a.attidentity != '' OR pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = a.attrelid AND d.adnum = a.attnum) JOIN (SELECT 16386 AS oid , 1 AS attnum UNION ALL SELECT 16386, 2 UNION ALL SELECT 16386, 3) vals ON (c.oid = vals.oid AND a.attnum = vals.attnum) where c.oid in (16386) | client backend
```

---

From: "joejensen (@joejensen)" <[email-masked]>
Date: Wed, 05 Feb 2025 00:54:09 +0000

I don't know your connection string, for me it works unless I set the assumeMinServerVersion connection property to 9.0 or greater, if I omit that property the name gets set, just too late for the initial connection logging.

It does appear that you could still skip the initial queries if the assume version was >= 12.  Everything else would already be set correctly.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 05 Feb 2025 01:00:13 +0000

OK, I need to look at this some more. It would be useful to write tests for this.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 05 Feb 2025 01:01:09 +0000

> It does appear that you could still skip the initial queries if the assume version was >= 12. Everything else would already be set correctly.

Why do you need this at initial connection logging ?

---

From: "joejensen (@joejensen)" <[email-masked]>
Date: Wed, 05 Feb 2025 03:29:20 +0000

I have log monitoring tools that track which applications are connecting and when.  It might be possible to parse this out of subsequent statements but it wouldn't be simple.  I know some common tools, like pgBadger, do the same thing.  Without this being set at startup all new connections report as "unknown".

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 05 Feb 2025 10:44:56 +0000

Got it, thanks!

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 06 Feb 2025 22:32:25 +0000

OK, I think we need to add it back. I think the runInitialQueries needs to be tightened up and we need to document this better.
`runInitialQueries should still set `extra_float_digits=3` for versions 9-12 and  `extra_float_digits=2` for versions less than 9

Thanks!

---

From: "joejensen (@joejensen)" <[email-masked]>
Date: Tue, 18 Feb 2025 22:27:10 +0000

I believe the latest changes will run the initial queries only when necessary. 
On PG 12 and later no initial queries are required assuming the assumMinServerVersion is set correctly.
On PG 9-11.x only the extra digits query is required if assumeMinServerVersion is set correctly.

I was able to add a unit test that fails for the original code, when assumeMinServerVersion was set to >= 9.0 resulting in the application name being blank.  However, I don't see a straight-forward way to verify that it's being set in the startup parameters rather than via the initial queries (in the test harness at least).  If you have any ideas, I would appreciate it.

I also added a note about this use-case into the documentation, not sure if it belongs anywhere else.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 19 Feb 2025 15:20:43 +0000

(on pgjdbc/src/main/java/org/postgresql/core/v3/ConnectionFactoryImpl.java:926)

what happens here if assumeVersion.getVersionNum() returns `null` ?

---

From: "joejensen (@joejensen)" <[email-masked]>
Date: Wed, 19 Feb 2025 16:21:28 +0000

It can't.  ServerVersion.from(String) converts a null assumeMinServerVersion to a 0, which is what is returned if no min server version was provided.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 19 Feb 2025 23:32:10 +0000

Can you just confirm by adding a test without the `PGProperty.ASSUME_MIN_SERVER_VERSION.getOrDefault(info)` set ?

---

From: "joejensen (@joejensen)" <[email-masked]>
Date: Fri, 28 Feb 2025 19:55:18 +0000

I can but that scenario is already tested by ParameterStatusTest.expectedInitialParameters.

---

From: "joejensen (@joejensen)" <[email-masked]>
Date: Mon, 17 Mar 2025 15:36:27 +0000

Do we want to add additional test for this same scenario?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 02 Apr 2025 16:15:00 +0000

when in doubt add tests

---

From: "joejensen (@joejensen)" <[email-masked]>
Date: Tue, 15 Apr 2025 17:45:16 +0000

Added the additional test and rebased over master.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 28 Apr 2025 22:37:55 +0000

(on pgjdbc/src/main/java/org/postgresql/core/v3/ConnectionFactoryImpl.java:923)

I wouldn't mind seeing some more explanation as to how we used the following tests to come to that conclusion.