Re: pg_stat_activity query_id - Mailing list pgsql-admin

> 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