Re: pg_stat_activity query_id

> 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