Re: pg_stat_activity query_id

> On 22/05/2023 15:44 CEST kaido vaikla <kaido(dot)vaikla(at)gmail(dot)com> 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