Re: RFC: Logging plan of the running query

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.

Patch 0002 will need a way to pass the request ID from the backend
calling pg_log_query_plan(pid) to the target backend. Once that is in
place, I expect it should be relatively straightforward to pass some
EXPLAIN options, such as VERBOSE, COSTS, SETTINGS, and SUMMARY, as well.
I would tentatively put support for those options in 0003.

ANALYZE and some other options would require additional work.
To obtain meaningful execution statistics, such as actual row counts,
instrumentation would need to be enabled from the start of query
execution. This might require a new GUC or a similar mechanism to
enable instrumentation in advance.
I would put that work in another patch.

BTW since 0001 no longer applied cleanly to HEAD, I have attached a
rebased version.

--
Thanks,

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