Message-ID: <gh-pgjdbc-pgjdbc-3166-c2008137611@github.com>
From: "goranschwarz (@goranschwarz)" <noreply+goranschwarz@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Tue, 19 Mar 2024 21:07:20 +0000
Subject: Re: [pgjdbc/pgjdbc] issue #3166: query_id in pg_stat_activity is NULL when connecting from JDBC, but not from psql
In-Reply-To: <gh-pgjdbc-pgjdbc-3166@github.com>
References: <gh-pgjdbc-pgjdbc-3166@github.com>
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Author-Login: goranschwarz
X-GitHub-Comment-Id: 2008137611
X-GitHub-Comment-Type: issue_comment
X-GitHub-Issue: 3166
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-Type: comment
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/3166#issuecomment-2008137611
Content-Type: text/plain; charset=utf-8

I totally agree with you!
There should be **no** difference if I execute `select query_id, pid, query, version() from pg_stat_activity where pid = pg_backend_pid()` from `psql` or from a `JDBC` Connection!   
And yet I see a difference!

Executing the above SQL from `psql` yields this result (query_id has a value)
```
[postgres@pg-3a-cos9 ~]$ psql --expanded --command="select query_id, pid, query, version() from pg_stat_activity where pid = pg_backend_pid()"
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------
query_id | 6627523495344870092
pid      | 1662866
query    | select query_id, pid, query, version() from pg_stat_activity where pid = pg_backend_pid()
version  | PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Hat 11.4.1-2), 64-bit
```

Executing the same SQL from JDBC yields this result (query_id is NULL)
```
>>>> ERROR: row[1], column 'query_id', shouldn't be NULL, it has a value if the statement was initiated from 'psql'...
PG-STAT-ACTIVITY: row[1], col[1] = |null|
PG-STAT-ACTIVITY: row[1], col[2] = |1664602|
PG-STAT-ACTIVITY: row[1], col[3] = |select query_id, pid, query, version() from pg_stat_activity where pid = pg_backend_pid()|
PG-STAT-ACTIVITY: row[1], col[4] = |PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Hat 11.4.1-2), 64-bit|
```

I simplified my Java repro code in the following way, so if you can try that *simplification* on your server and post the output
```
	public static void main(String[] args) throws Exception
	{
		String url = "jdbc:postgresql://pg-3a-cos9:5432/postgres"; // Postgres 16.2

		Properties props = new Properties();
		props.setProperty("user"    , "postgres");
		props.setProperty("password", "xxxxxxxxxxxxx");

		try (Connection conn = DriverManager.getConnection(url, props))
		{
			// Then get records from pg_stat_activity
			try (Statement statement = conn.createStatement())
			{
				try (ResultSet rs = statement.executeQuery("select query_id, pid, query, version() from pg_stat_activity where pid = pg_backend_pid()"))
				{
					int colCount = rs.getMetaData().getColumnCount();
					int row = 0;

					while( rs.next() )
					{
						row++;
						int     queryId       = rs.getInt(1);
						boolean queryIdIsNull = rs.wasNull();
						
						if (queryIdIsNull)
						{
							System.out.println(">>>> ERROR: row[" + row + "], column 'query_id', shouldn't be NULL, it has a value if the statement was initiated from 'psql'...");
						}

						for (int c = 1; c <= colCount; c++)
						{
							System.out.println("PG-STAT-ACTIVITY: row[" + row + "], col[" + c + "] = |" + rs.getString(c) + "|");
						}
					}
				}
			}
		}
	}
```