Re: RFC: Logging plan of the running query

On 2026-08-31 22:12, Andrei Lepikhov wrote:
> On 27/08/2026 15:22, torikoshia wrote:
>> On 2026-08-24 16:29, Andrei Lepikhov wrote:
>>> Just make it an asynchronous function and provide users with a way to
>>> know
>>> whether it has already been logged and to match the call to the
>>> specific log record.
>>
>> I'm still not sure if it's reasonable to add a dedicated
>> request tracking mechanism for pg_log_query_plan().
>>
>> For example, pg_log_backend_memory_contexts() is a similar
>> asynchronous
>> diagnostic function. It requests another process to write information
>> to
>> the server log, but it does not provide a separate way to determine
>> whether the request has already been processed or to associate an
>> individual call with a particular log record. It also cannot guarantee
>> that the logged memory contexts represent the exact state that the
>> caller observed before sending the request.
>
> I see the pg_log_backend_memory_contexts routine as having a different
> purpose
> and behavior.
> 1. It runs whenever interruption signals are processed. The
> pg_log_query_plan,
> on the other hand, should wait until a tuple is produced.
> 2. It logs memory contexts that carry cross-session semantics and data
> and helps
> track memory use in the backend over its lifetime. In contrast, query
> plan
> information is specific to a single query. Users usually want to see
> this when a
> query takes longer than expected.
>
> Query plan snapshots are costly to create and often hard to interpret.
> In my
> experience, users turn to them when something has already gone wrong,
> expect a
> slow response, and typically deal with complex, unusual queries. It
> would be
> helpful if we could match the generated plan to the query in
> pg_stat_activity.
>
>>
>> More generally, diagnostic tools that inspect a changing execution
>> state are often used as sampling tools. For example, when
>> investigating
>> a process with a stack-tracing tool such as pstack, it is common to
>> take
>> several stack traces at once.
>>
>> I view pg_log_query_plan() similarly.
>> A DBA may issue the request several times to obtain useful diagnostic
>> samples, rather than expecting one call to identify the exact
>> state observed earlier in pg_stat_activity.
>
> 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.

For example:

=# SELECT pg_log_query_plan(12345);
pg_log_query_plan
-------------------
42

The corresponding log message would contain the same ID:

LOG: query plan request 42 for queryid ... running on backend with
PID 12345 is:
...

Thanks,

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