MIME-Version: 1.0
References: <CAM2F1VNmR59qggpGMAeGu34_DCj_7h-3mTE1VP=VRnKE+F5ZJA@mail.gmail.com>
 <CAFj8pRAKZBhsFQVcmsfFfvEYgv0SeA7TWgjvLRtzCfdB6ws3JQ@mail.gmail.com>
In-Reply-To: <CAFj8pRAKZBhsFQVcmsfFfvEYgv0SeA7TWgjvLRtzCfdB6ws3JQ@mail.gmail.com>
From: Marcelo Fernandes <marcefern7@gmail.com>
Date: Tue, 4 Feb 2025 17:08:48 +1300
Message-ID: <CAM2F1VPz8qw2S-ND1xHWqaDw+wZ6cpeXErsONYCJLob=mc_Rqg@mail.gmail.com>
Subject: Re: Logging queries executed by SPI_execute
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAM2F1VPz8qw2S-ND1xHWqaDw%2BwZ6cpeXErsONYCJLob%3Dmc_Rqg%40mail.gmail.com>
Precedence: bulk

On Mon, Feb 3, 2025 at 6:46=E2=80=AFPM Pavel Stehule <pavel.stehule@gmail.c=
om> wrote:
> The queries executed by SPI are never executed on the top level. These qu=
eries are marked as nested.
>
> So you need to use auto_explain https://www.postgresql.org/docs/current/a=
uto-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

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 woul=
d be
more handy.

Best,
Marcelo