pgjdbc/pgjdbc GitHub issues and pull requests (mirror)  

help / color / mirror / Atom feed

From: goranschwarz (@goranschwarz) <[email protected]>
To: pgjdbc/pgjdbc <[email protected]>
Subject: [pgjdbc/pgjdbc] issue #3166: query_id in pg_stat_activity is NULL when connecting from JDBC, but not from psql
Date: Fri, 15 Mar 2024 19:50:24 +0000
Message-ID: <[email protected]> (raw)

**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();
			}
		}
	}
```

view thread (17+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: github://pgjdbc/pgjdbc
  Cc: [email protected], [email protected]
  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: <<[email protected]>>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox