MIME-Version: 1.0
References: <CAM2F1VNmR59qggpGMAeGu34_DCj_7h-3mTE1VP=VRnKE+F5ZJA@mail.gmail.com>
 <CAFj8pRAKZBhsFQVcmsfFfvEYgv0SeA7TWgjvLRtzCfdB6ws3JQ@mail.gmail.com> <CAM2F1VPz8qw2S-ND1xHWqaDw+wZ6cpeXErsONYCJLob=mc_Rqg@mail.gmail.com>
In-Reply-To: <CAM2F1VPz8qw2S-ND1xHWqaDw+wZ6cpeXErsONYCJLob=mc_Rqg@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Tue, 4 Feb 2025 06:06:55 +0100
Message-ID: <CAFj8pRAZWSDyw9PzcU7Ju=2UbqZEsJqC=EECwFDk_-g+6qR_pw@mail.gmail.com>
Subject: Re: Logging queries executed by SPI_execute
To: Marcelo Fernandes <marcefern7@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000007385e2062d49fc26"
Archived-At: <https://www.postgresql.org/message-id/CAFj8pRAZWSDyw9PzcU7Ju%3D2UbqZEsJqC%3DEECwFDk_-g%2B6qR_pw%40mail.gmail.com>
Precedence: bulk

--0000000000007385e2062d49fc26
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

=C3=BAt 4. 2. 2025 v 5:09 odes=C3=ADlatel Marcelo Fernandes <marcefern7@gma=
il.com>
napsal:

> On Mon, Feb 3, 2025 at 6:46=E2=80=AFPM Pavel Stehule <pavel.stehule@gmail=
.com>
> wrote:
> > The queries executed by SPI are never executed on the top level. These
> queries are marked as nested.
> >
> > So you need to use auto_explain
> https://www.postgresql.org/docs/current/auto-explain.html with active
> auto_explain.log_nested_statements
>
> Thanks Pavel, I have tried that but however couldn't succeed.
>
> I have added this to my postgres.conf file:
>
>     session_preload_libraries =3D 'auto_explain'
>     auto_explain.log_nested_statements =3D true
>

you are missing setting  auto_explain.log_min_duration

>
> After running pg_repack, which calls SPI_execute* functions, I can't
> really see
> those functions being propagated to the logs.
>
> I have a workaround in place, which is to add elog calls such as
>
>     elog(LOG, "...")
>
> to capture the queries. But of course, ideally a postgres.conf setting
> would be
> more handy.
>
> Best,
> Marcelo
>

--0000000000007385e2062d49fc26
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">=C3=BAt 4. 2. 2=
025 v=C2=A05:09 odes=C3=ADlatel Marcelo Fernandes &lt;<a href=3D"mailto:mar=
cefern7@gmail.com">marcefern7@gmail.com</a>&gt; napsal:<br></div><blockquot=
e class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px s=
olid rgb(204,204,204);padding-left:1ex">On Mon, Feb 3, 2025 at 6:46=E2=80=
=AFPM Pavel Stehule &lt;<a href=3D"mailto:pavel.stehule@gmail.com" target=
=3D"_blank">pavel.stehule@gmail.com</a>&gt; wrote:<br>
&gt; The queries executed by SPI are never executed on the top level. These=
 queries are marked as nested.<br>
&gt;<br>
&gt; So you need to use auto_explain <a href=3D"https://www.postgresql.org/=
docs/current/auto-explain.html" rel=3D"noreferrer" target=3D"_blank">https:=
//www.postgresql.org/docs/current/auto-explain.html</a> with active auto_ex=
plain.log_nested_statements<br>
<br>
Thanks Pavel, I have tried that but however couldn&#39;t succeed.<br>
<br>
I have added this to my postgres.conf file:<br>
<br>
=C2=A0 =C2=A0 session_preload_libraries =3D &#39;auto_explain&#39;<br>
=C2=A0 =C2=A0 auto_explain.log_nested_statements =3D true<br></blockquote><=
div><br></div><div>you are missing setting=C2=A0 <span class=3D"gmail-term"=
><code class=3D"gmail-varname">auto_explain.log_min_duration</code></span> =
</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;b=
order-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
After running pg_repack, which calls SPI_execute* functions, I can&#39;t re=
ally see<br>
those functions being propagated to the logs.<br>
<br>
I have a workaround in place, which is to add elog calls such as<br>
<br>
=C2=A0 =C2=A0 elog(LOG, &quot;...&quot;)<br>
<br>
to capture the queries. But of course, ideally a postgres.conf setting woul=
d be<br>
more handy.<br>
<br>
Best,<br>
Marcelo<br>
</blockquote></div></div>

--0000000000007385e2062d49fc26--