> On 22/05/2023 15:44 CEST kaido vaikla <kaido.vaikla@gmail.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
