Message-ID: <gh-pgjdbc-pgjdbc-3166@github.com>
From: "goranschwarz (@goranschwarz)" <noreply+goranschwarz@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Fri, 15 Mar 2024 19:50:24 +0000
Subject: [pgjdbc/pgjdbc] issue #3166: query_id in pg_stat_activity is NULL when connecting from JDBC, but not from psql
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Author-Id: 4483245
X-GitHub-Author-Login: goranschwarz
X-GitHub-Issue: 3166
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-State: open
X-GitHub-Type: issue
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/3166
Content-Type: text/plain; charset=utf-8

**Describe the issue**
`query_id` in `pg_stat_activity` is NULL when connecting from JDBC, but not from `psql`

**Driver Version?** 
42.7.2    (postgresql-42.7.2.jar)

**Java Version?**
21.0.1  (or any)

**OS Version?**
Windows 11  (I guess any here as well)

**PostgreSQL Version?**
16.2 on CentOS 9

**To Reproduce**
* from one session do: select pg_sleep(44)
* from another session: select query_id, * from pg_stat_activity where query like '%pg_sleep%'

**Expected behaviour**
* `query_id` should not be NULL

**Logs**
nothing in the logs

**Why I need this**
In my monitoring tool (https://github.com/goranschwarz/DbxTune)    
I get query_plan information from the errorlog using `auto_explain` and then cache that plan in memory.   
If a new query comes with the same `query_id` I can now show the query plan in "Active Statements", with `pev2` or som other Query Plan Viewer   
Note: It would be **even** better if I could retrive the `query plan`at runtime...   
Meaning: `select get_query_plan_for_current_running_pid(###)` , but that isn't around, so have to come up with something...   
But now I trapped over that that all JDBC connections just do not show the `query_id`... bummer :(

**Reproducable code**
```
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class PgQueryIdRepro 
{
	public static void main(String[] args) throws Exception
	{
		String url = "jdbc:postgresql://hostname:5432/postgres";

		Properties props = new Properties();
		props.setProperty("user"    , "dbxtune");
		props.setProperty("password", "some-secret-password");

		// Kick of a 'pg_sleep'
		PgSleep pgSleep = new PgSleep(3, url, props);
		pgSleep.start();

		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, * from pg_stat_activity where query like '%pg_sleep%'"))
				{
					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) + "|");
						}
					}
				}
			}
		}
	}
	
	
	private static class PgSleep
	extends Thread
	{
		private int        _sleepTime;
		private String     _url;
		private Properties _props;

		public PgSleep(int sleepTime, String url, Properties props)
		{
			_sleepTime = sleepTime;
			_url       = url;
			_props     = props;
		}

		@Override
		public void run()
		{
			System.out.println("Starting PgSleep...");
			try
			{
				try (Connection conn = DriverManager.getConnection(_url, _props))
				{
					try (Statement statement = conn.createStatement())
					{
						try (ResultSet rs = statement.executeQuery("select pg_sleep(" + _sleepTime + ")"))
						{
							int colCount = rs.getMetaData().getColumnCount();
							int row = 0;

							while( rs.next() )
							{
								row++;
								for (int c = 1; c <= colCount; c++)
								{
									System.out.println("PG-SLEEP-RS: row[" + row + "], col[" + c + "] = |" + rs.getString(c) + "|");
								}
							}
						}
					}
				}
			}
			catch (SQLException ex)
			{
				ex.printStackTrace();
			}
		}
	}
```