Date: Mon, 22 May 2023 19:43:38 +0200 (CEST)
From: Erik Wienhold <ewie@ewie.name>
To: kaido vaikla <kaido.vaikla@gmail.com>
Cc: pgsql-admin@postgresql.org
Message-ID: <1391613709.939460.1684777418070@office.mailbox.org>
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>
Subject: Re: pg_stat_activity query_id
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Importance: Normal
Archived-At: 
 <https://www.postgresql.org/message-id/1391613709.939460.1684777418070%40office.mailbox.org>
Precedence: bulk

> 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