From: Erik Wienhold <ewie@ewie.name>
To: kaido vaikla <kaido.vaikla@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: pg_stat_activity query_id
Date: Mon, 22 May 2023 19:43:38 +0200 (CEST)
Message-ID: <1391613709.939460.1684777418070@office.mailbox.org> (raw)
In-Reply-To: <CA+427g_CsqGRJOb2QT66CoR-S9tC7snUFJ3tpN5COkE6f3gbKQ@mail.gmail.com>
References: <CA+427g8qBArPuqwZOuYVhdoCQUsdbutR18fG7Gt7+3JunuHsjA@mail.gmail.com>
<472115375.225506.1683812791906@office.mailbox.org>
<CA+427g81dsn_5QyFr-zduuAYZ_0H4=xWOBnT4fT79pbzLCiwUg@mail.gmail.com>
<CA+427g-4EgyNBpQtZ60CKEryzq4p_ixv8xVBygFhMiNk5rAiUg@mail.gmail.com>
<1279536236.296763.1683901055904@office.mailbox.org>
<CA+427g_CsqGRJOb2QT66CoR-S9tC7snUFJ3tpN5COkE6f3gbKQ@mail.gmail.com>
> 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
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: pgsql-admin@postgresql.org
Cc: ewie@ewie.name, kaido.vaikla@gmail.com
Subject: Re: pg_stat_activity query_id
In-Reply-To: <1391613709.939460.1684777418070@office.mailbox.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox