MIME-Version: 1.0
References: <CAM2F1VNmR59qggpGMAeGu34_DCj_7h-3mTE1VP=VRnKE+F5ZJA@mail.gmail.com>
In-Reply-To: <CAM2F1VNmR59qggpGMAeGu34_DCj_7h-3mTE1VP=VRnKE+F5ZJA@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Mon, 3 Feb 2025 06:45:31 +0100
Message-ID: <CAFj8pRAKZBhsFQVcmsfFfvEYgv0SeA7TWgjvLRtzCfdB6ws3JQ@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="000000000000bc3ccf062d36687b"
Archived-At: <https://www.postgresql.org/message-id/CAFj8pRAKZBhsFQVcmsfFfvEYgv0SeA7TWgjvLRtzCfdB6ws3JQ%40mail.gmail.com>
Precedence: bulk

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

Hi

po 3. 2. 2025 v 0:06 odes=C3=ADlatel Marcelo Fernandes <marcefern7@gmail.co=
m>
napsal:

> Hi there,
>
> I have been trying to debug what queries an extension is firing. After
> reading
> the code for the extension, I noticed that all the statements are fired
> via the
> SPI interface, most specifically, using the SPI_execute* family of
> functions.
>
> However, the problem is that these statements don't seem to feature in th=
e
> logfile. It has become a bit of a "dead end" for me to fully analyse what
> queries are fired in the end.
>
> My postgresql.conf is fairly simple, but I have added "log_statement =3D
> 'all'"
> and thus was expecting to see information for these statements too.
>
> Is there anything I am missing? Some configuration or perhaps another way
> to
> find out about these statements?
>
> It may not be relevant, but the extension I am having a look at is
> pg_repack if
> anyone is interested. I am trying to get a deeper understanding of what t=
he
> function repack_apply does.
>

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

Regards

Pavel


> Thanks,
> Marcelo.
>
>
>

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

<div dir=3D"ltr"><div dir=3D"ltr">Hi<br></div><br><div class=3D"gmail_quote=
 gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">po 3. 2. 2025=
 v=C2=A00:06 odes=C3=ADlatel Marcelo Fernandes &lt;<a href=3D"mailto:marcef=
ern7@gmail.com">marcefern7@gmail.com</a>&gt; napsal:<br></div><blockquote c=
lass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px soli=
d rgb(204,204,204);padding-left:1ex">Hi there,<br>
<br>
I have been trying to debug what queries an extension is firing. After read=
ing<br>
the code for the extension, I noticed that all the statements are fired via=
 the<br>
SPI interface, most specifically, using the SPI_execute* family of function=
s.<br>
<br>
However, the problem is that these statements don&#39;t seem to feature in =
the<br>
logfile. It has become a bit of a &quot;dead end&quot; for me to fully anal=
yse what<br>
queries are fired in the end.<br>
<br>
My postgresql.conf is fairly simple, but I have added &quot;log_statement =
=3D &#39;all&#39;&quot;<br>
and thus was expecting to see information for these statements too.<br>
<br>
Is there anything I am missing? Some configuration or perhaps another way t=
o<br>
find out about these statements?<br>
<br>
It may not be relevant, but the extension I am having a look at is pg_repac=
k if<br>
anyone is interested. I am trying to get a deeper understanding of what the=
<br>
function repack_apply does.<br></blockquote><div><br></div><div>The queries=
 executed by SPI are never executed on the top level. These queries are mar=
ked as nested.<br></div><div><br></div><div>So you need to use auto_explain=
=C2=A0<a href=3D"https://www.postgresql.org/docs/current/auto-explain.html"=
>https://www.postgresql.org/docs/current/auto-explain.html</a> with active=
=C2=A0<span class=3D"gmail-term"><code class=3D"gmail-varname">auto_explain=
.log_nested_statements</code></span></div><div><span class=3D"gmail-term"><=
code class=3D"gmail-varname"><br></code></span> </div><div>Regards</div><di=
v><br></div><div>Pavel<br></div><div>=C2=A0</div><blockquote class=3D"gmail=
_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204=
,204);padding-left:1ex">
Thanks,<br>
Marcelo.<br>
<br>
<br>
</blockquote></div></div>

--000000000000bc3ccf062d36687b--