pgjdbc/pgjdbc GitHub issues and pull requests (mirror)  

help / color / mirror / Atom feed

From: goranschwarz (@goranschwarz) <[email protected]>
To: pgjdbc/pgjdbc <[email protected]>
Subject: Re: [pgjdbc/pgjdbc] issue #3166: query_id in pg_stat_activity is NULL when connecting from JDBC, but not from psql
Date: Tue, 19 Mar 2024 21:07:20 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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) + "|");
						}
					}
				}
			}
		}
	}
```

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