Re: RFC: Logging plan of the running query

On Wed, May 12, 2021 at 4:54 PM torikoshia <torikoshia(at)oss(dot)nttdata(dot)com> wrote:
>
> Hi,
>
> During the discussion about memory contexts dumping[1], there
> was a comment that exposing not only memory contexts but also
> query plans and untruncated query string would be useful.
>
> I also feel that it would be nice when thinking about situations
> such as troubleshooting a long-running query on production
> environments where we cannot use debuggers.
>
> At that point of the above comment, I was considering exposing
> such information on the shared memory.
> However, since memory contexts are now exposed on the log by
> pg_log_backend_memory_contexts(PID), I'm thinking about
> defining a function that logs the plan of a running query and
> untruncated query string on the specified PID in the same way
> as below.
>
> postgres=# SELECT * FROM pg_log_current_plan(2155192);
> pg_log_current_plan
> ---------------------
> t
> (1 row)
>
> $ tail -f data/log/postgresql-2021-05-12.log
>
> 2021-05-12 17:37:19.481 JST [2155192] LOG: logging the plan of
> running query on PID 2155192
> Query Text: SELECT a.filler FROM pgbench_accounts a JOIN
> pgbench_accounts b ON a.aid = b.aid;
> Merge Join (cost=0.85..83357.85 rows=1000000 width=85)
> Merge Cond: (a.aid = b.aid)
> -> Index Scan using pgbench_accounts_pkey on
> pgbench_accounts a (cost=0.42..42377.43 rows=1000000 width=89)
> -> Index Only Scan using pgbench_accounts_pkey on
> pgbench_accounts b (cost=0.42..25980.42 rows=1000000 width=4)
>
>
> Attached a PoC patch.
>
> Any thoughts?
>
> [1]
> https://www.postgresql.org/message-id/CA%2BTgmobkpFV0UB67kzXuD36--OFHwz1bs%3DL_6PZbD4nxKqUQMw%40mail.gmail.com

+1 for the idea. It looks like pg_log_current_plan is allowed to run
by superusers. Since it also shows up the full query text and the plan
in the server log as plain text, there are chances that the sensitive
information might be logged into the server log which is a risky thing
from security standpoint. There's another thread (see [1] below) which
discusses this issue by having a separate role for all debugging
purposes. Note that final consensus is not reached yet. We may want to
use the same role for this patch as well.

[1] - https://www.postgresql.org/message-id/CA%2BTgmoZz%3DK1bQRp0Ug%3D6uMGFWg-6kaxdHe6VSWaxq0U-YkppYQ%40mail.gmail.com

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com