Re: RFC: Logging plan of the running query

On 2026-09-25 16:08, torikoshia wrote:
> On 2026-09-18 18:46, Andrei Lepikhov wrote:
>> On 08/09/2026 15:17, torikoshia wrote:
>>> On 2026-08-31 22:12, Andrei Lepikhov wrote:
>>>> What if an automation tool, triggered by certain events, requests a
>>>> query plan
>>>> when a query goes over its quota? If we could identify the query
>>>> plan in the
>>>> log, the tool could find the plan, decide whether to request it
>>>> again, and
>>>> choose to either interrupt the query or let it run a bit longer.
>>>
>>> Would the following approach address your concern?
>>>
>>> - When pg_log_query_plan() is called, it generates a request ID,
>>> returns the ID
>>> to the caller, and passes it to the target backend.
>>> - The target backend includes the ID either in the resulting plan log
>>> or in a
>>> log message indicating that the request could not be completed.
>>
>> Yes, I think this is a good way to explore.
>>
>> I also see this code as an initial step toward a future full-fledged
>> feature
>> that might need a shared memory structure for extra parameters.
>> EXPLAIN by
>> itself, even in verbose mode, has limited benefits. In practice, the
>> actual
>> number of rows and loops is way more useful. Because of this, I think
>> the
>> feature should be designed so that future core changes or extensions
>> can pass
>> some flags, such as 'interrupt query execution and calculate actual
>> instrumentation' or 'use specific explain settings'.
>
> I agree that the feature as currently proposed would be useful only in
> limited situations, since it only shows the execution plan. Supporting
> EXPLAIN options, particularly ANALYZE, would make it more useful.
> Before
> we can support those capabilities, though, we first need to establish a
> safe way to obtain the execution plan of a running query. That is the
> focus of the current patch.
>
> I am currently trying to implement the request ID approach to associate
> each call to pg_log_query_plan() with the corresponding log output. I
> think this can be reviewed separately from the mechanism for safely
> obtaining the plan, so I intend to keep the latter in 0001 and put the
> request ID support in a separate patch, 0002.

Added patch 0002 to implement this approach.
It generates request IDs from a cluster-wide counter and includes each
ID in both the return value of pg_log_query_plan() and the corresponding
log message:

=# select pg_log_query_plan(15726);
pg_log_query_plan
-------------------
3

LOG: 00000: query and its plan for queryid -6193732234003165941
requested by request ID 3 running on backend with PID 15726 are:
Query Text: select * from pgbench_accounts a cross join
pgbench_branches b;
Nested Loop (cost=0.00..4111474.17 rows=327839400 width=200)
-> Seq Scan on pgbench_accounts a (cost=0.00..13463.80
rows=520380 width=100)
-> Materialize (cost=0.00..19.45 rows=630 width=100)
-> Seq Scan on pgbench_branches b (cost=0.00..16.30
rows=630 width=100)

The patch allocates one shared-memory request slot per backend slot at
server startup. Each slot holds a request ID, a target PID, a request
state, and a spinlock, requiring roughly two dozen bytes per backend
slot, depending on the platform. There is also a small control structure
for the shared counter, so the overall memory requirement seems modest.

As noted in the code comment, the request structure could also be
extended to carry EXPLAIN options in a future patch.

0001 patch has not been changed from v60, which focuses on obtaining
the execution plan of a running query safely.

What do you think?

--
Thanks,

--
Atsushi Torikoshi
Seconded from NTT DATA CORPORATION to SRA OSS K.K.