Re: pg_stat_activity query_id

[Erik Wienhold <ewie@xxxxxxxxx>]

> On 22/05/2023 15:44 CEST kaido vaikla <kaido.vaikla@xxxxxxxxx> wrote:
>
> I asked from pg jdbc community. Answer was :
> "One thing to note is that the driver uses extended query protocol so the
> queries are not identical.".
> I don't know, is it this now key to understand this issue?
> https://github.com/pgjdbc/pgjdbc/discussions/2902#discussioncomment-5917360

Look's like the extended query protocol is the reason for that.  Testing with
psycopg 3.1 (which added pipeline mode to use the extended query protocol)
confirms this:

	from psycopg import connect
	from psycopg.rows import dict_row
	
	conninfo = 'dbname=postgres'
	query = 'SELECT 1'
	
	with connect(conninfo) as con0:
	    backend_pid = con0.info.backend_pid
	    server_version = con0.info.server_version
	
	    print(f"{backend_pid=} {server_version=}")
	
	    con0.execute('SET compute_query_id = on')
	
	    print("=> simple query")
	
	    con0.execute(query)
	
	    with connect(conninfo, row_factory=dict_row) as con1:
	        with con1.execute('''
	            SELECT pid, query, query_id
	            FROM pg_stat_activity
	            WHERE pid = %s
	        ''', (backend_pid,)) as cur:
	            for row in cur.fetchall():
	                print(row)
	
	    print("=> extended query")
	
	    with con0.pipeline():
	        con0.execute(query)
	
	    with connect(conninfo, row_factory=dict_row) as con1:
	        with con1.execute('''
	            SELECT pid, query, query_id
	            FROM pg_stat_activity
	            WHERE pid = %s
	        ''', (backend_pid,)) as cur:
	            for row in cur.fetchall():
	                print(row)

Gives me:

	backend_pid=800121 server_version=150002
	=> simple query
	{'pid': 800121, 'query': 'SELECT 1', 'query_id': 1147616880456321454}
	=> extended query
	{'pid': 800121, 'query': 'SELECT 1', 'query_id': None}

I wonder if this is a limitation of the extended query protocol.  Computing the
query identifier for a prepared statement with placeholders is not very useful.
But I would think that a useful query identifier can be calculated once the
placeholders are bound to concrete values and the query is executed.

--
Erik