Re: query_id, pg_stat_activity, extended query protocol

On 4/23/24 12:49, Michael Paquier wrote:
> On Tue, Apr 23, 2024 at 11:42:41AM +0700, Andrei Lepikhov wrote:
>> On 23/4/2024 11:16, Imseih (AWS), Sami wrote:
>>> + pgstat_report_query_id(linitial_node(Query, psrc->query_list)->queryId, true);
>>> set_ps_display("BIND");
>>> @@ -2146,6 +2147,7 @@ exec_execute_message(const char *portal_name, long max_rows)
>>> debug_query_string = sourceText;
>>> pgstat_report_activity(STATE_RUNNING, sourceText);
>>> + pgstat_report_query_id(portal->queryDesc->plannedstmt->queryId, true);
>>> cmdtagname = GetCommandTagNameAndLen(portal->commandTag, &cmdtaglen);
>>
>> In exec_bind_message, how can you be sure that queryId exists in query_list
>> before the call of GetCachedPlan(), which will validate and lock the plan?
>> What if some OIDs were altered in the middle?
>
> I am also a bit surprised with the choice of using the first Query
> available in the list for the ID, FWIW.
>
> Did you consider using \bind to show how this behaves in a regression
> test?
I'm not sure how to invent a test based on the \bind command - we need
some pause in the middle.
But simplistic case with a prepared statement shows how the value of
queryId can be changed if you don't acquire all the objects needed for
the execution:

CREATE TABLE test();
PREPARE name AS SELECT * FROM test;
EXPLAIN (ANALYSE, VERBOSE, COSTS OFF) EXECUTE name;
DROP TABLE test;
CREATE TABLE test();
EXPLAIN (ANALYSE, VERBOSE, COSTS OFF) EXECUTE name;

/*
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on public.test (actual time=0.002..0.004 rows=0 loops=1)
Query Identifier: 6750745711909650694

QUERY PLAN
-------------------------------------------------------------------
Seq Scan on public.test (actual time=0.004..0.004 rows=0 loops=1)
Query Identifier: -2597546769858730762
*/

We have different objects which can be changed - I just have invented
the most trivial example to discuss.

--
regards, Andrei Lepikhov