MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8;
 format=flowed
Content-Transfer-Encoding: 8bit
Date: Tue, 24 Oct 2023 21:30:49 +0900
From: torikoshia <torikoshia@oss.nttdata.com>
To: =?UTF-8?Q?=C3=89tienne_BERSAC?= <etienne.bersac@dalibo.com>
Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, Andres Freund
 <andres@anarazel.de>, jtc331@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: RFC: Logging plan of the running query
In-Reply-To: <9c36599db62ef7466a297843fe812fc60e827cd2.camel@dalibo.com>
References: <af8b4c4752dcee3ca7284e7c15922d5d@oss.nttdata.com>
 <CAAaqYe9TPyWe9yoOm5Jz_39gSnhCAYXozc698jkyegM1x74Qjg@mail.gmail.com>
 <CAAaqYe9Mse7JC78DgT97sXoB6U+E5OL3zJAYRSsgy-TPP9PXmw@mail.gmail.com>
 <4e1e4e5d022964953e84468fe4e511b0@oss.nttdata.com>
 <CAAaqYe_5p_MkBCgSYX=S7G3ZKi=2=_6bZQFzTgVYyv=1NRikpw@mail.gmail.com>
 <4af7712833d4ce989cf5e658106e42da@oss.nttdata.com>
 <CAAaqYe_hyWLs-ObFQfu54yA-ZOjNCd-L_Z=1TnYjYNqrLuGiXQ@mail.gmail.com>
 <cbe6eb783affb3e9b5068f5cf019ee62@oss.nttdata.com>
 <CAAaqYe_p-849MtsVgN9_y-YL-uFzLFMpGw38T3Omb11gw-=K1w@mail.gmail.com>
 <7ab57ab0ba3416e2dbf54927752bc310@oss.nttdata.com>
 <20231006155850.kjjb2vwbkawj7moq@alap3.anarazel.de>
 <0e0e7ca08dff077a625c27a5e0c2ef0a@oss.nttdata.com>
 <CAExHW5ujL=mzbjxf6xtyU-Ft6LMBMEmt8H4ZSDiiZre0Fef0jg@mail.gmail.com>
 <2d6d7846b6b794c0e727c431852a113c@oss.nttdata.com>
 <CAExHW5s+T0Ds-h85y92MsryLqup8fuAT8bovgGGaNnLt=OrQOg@mail.gmail.com>
 <0eb66e8c72f23695957466db5c76c69c@oss.nttdata.com>
 <9c36599db62ef7466a297843fe812fc60e827cd2.camel@dalibo.com>
User-Agent: Roundcube Webmail/1.4.11
Message-ID: <66d29d2c10ca9181c7e183eeeb4525f8@oss.nttdata.com>
Archived-At: <https://www.postgresql.org/message-id/66d29d2c10ca9181c7e183eeeb4525f8%40oss.nttdata.com>
Precedence: bulk

On 2023-10-24 16:12, Étienne BERSAC wrote:
>> Hi,
>> 
>> Yeah, and when we have a situation where we want to run
>> pg_log_query_plan(), we can run it in any environment as long as it
>> is bundled with the core.
> 
> Is it possible to get the plan of a backend programmatically without
> access to the logs?
> 
> Something like pg_get_query_plan(pid, format) which output would be the
> same as EXPLAIN.

Do you imagine a function like below?

   =# select pg_get_query_plan(100, 'plain');
                             QUERY PLAN
   -------------------------------------------------------------------
    Limit  (cost=0.00..0.04 rows=1 width=273)
      ->  Seq Scan on pg_class  (cost=0.00..17.14 rows=414 width=273)

If so, we once tried to implement such function for getting memory 
contexts.
However, this attempt didn't succeed because it could lead dead lock 
situation[1].

I think the same problem can occur when implementing 
pg_get_query_plan().

[1] 
https://www.postgresql.org/message-id/9a50371e15e741e295accabc72a41df1%40oss.nttdata.com
> 
> Regards,
> Étienne BERSAC
> Dalibo

-- 
Regards,

--
Atsushi Torikoshi
NTT DATA Group Corporation