Re: pg_stat_activity query_id

Hi
Is it now bug or expected behave. If it is expected, can it mentioned in
manual too?
br
Kaido

On Mon, 22 May 2023 at 20:43, Erik Wienhold <ewie(at)ewie(dot)name> wrote:

> > 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
>