Re: RFC: Logging plan of the running query

On 21/07/2026 17:54, torikoshia wrote:
>
>> Hmmm, asynchronous feature that explains an arbitrary part of the query - for
>> example, some query inside an evaluated plpgsql function ... I think it is
>> expected by design.
>
> Even when execution moves to an outer or deeper QueryDesc in this way,
> the LogQueryPlanPending flag remains set, so the plan is logged. [1]
>
> The plan is not logged when there is no subsequent opportunity to call
> ExecProcNode(), including in any outer or deeper QueryDesc, as in the
> example using PREPARE that you showed in your previous message.
>
> My concern is whether, without the pending flag, it would be
> appropriate to log the plan of a different query subsequently
> executed by the same backend.
>
> For example, suppose a user calls pg_log_query_plan() expecting
> the plan of query A to be logged, but there is no opportunity
> to do so. If query B is then executed by the same backend one
> hour later, the plan of query B would be logged at that point.
> This does not seem like behavior that users would expect.
> What do you think?

Hmm, maybe I wasn't clear enough. Let me explain. This feature is asynchronous
by design. Calling it, you never know if the signal will be processed in the
exact query you saw a moment ago in pg_stat_activity. Moreover, it may be an
internal query inside of a massive query - it can be hard for a user to match
this call and the logged query. Just for example:

CREATE OR REPLACE FUNCTION heavy_nested_probe() RETURNS bigint
LANGUAGE plpgsql AS $$
DECLARE
result bigint;
BEGIN
SELECT count(*) INTO result
FROM generate_series(1, 150000000) g(i) WHERE i % 7 = 0; RETURN result;
END; $$; SQL

SELECT heavy_nested_probe();

Calling SELECT pg_log_query_plan() to the backend, executing such a query, you
will probably see 'SELECT heavy_nested_probe ', but the log file contains:

LOG: query and its plan for queryid 0 running on backend with PID 15805 are:
Query Text: SELECT count(*) FROM generate_series(1, 150000000) g(i)
WHERE i % 7 = 0
Aggregate (cost=2251875.00..2251875.01 rows=1 width=8)
-> Function Scan on generate_series g
(cost=0.00..2250000.00 rows=750000 width=0)
Filter: ((i % 7) = 0)

So, my point is Postgres shouldn't pretend that it provides any guarantees: this
function is a direct target for DBA's automation, and it might cause questions.
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.

--
regards, Andrei Lepikhov,
pgEdge