From: Antonin Houska <ah@cybertec.at>
To: Alvaro Herrera <alvherre@kurilemu.de>
cc: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>,
    Fujii Masao <masao.fujii@gmail.com>,
    Nathan Bossart <nathandbossart@gmail.com>,
    pgsql-hackers@postgresql.org
Subject: Re: REPACK (ANALYZE) within transaction block segfaults
In-reply-to: <ap_a_JiFXDFKjrOK@alvherre.pgsql>
References: <ap_a_JiFXDFKjrOK@alvherre.pgsql>
Comments: In-reply-to Alvaro Herrera <alvherre@kurilemu.de>
   message dated "Tue, 08 Sep 2026 12:27:19 +0200."
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <43710.1788871105.1@localhost>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 08 Sep 2026 14:38:25 +0200
Message-ID: <43711.1788871105@localhost>
Archived-At: 
 <https://www.postgresql.org/message-id/43711.1788871105%40localhost>
Precedence: bulk

Alvaro Herrera <alvherre@kurilemu.de> wrote:

> On 2026-Sep-05, Osama Abdul Qader wrote:
> =

> > I understand the distinction now. Allowing REPACK (ANALYZE) in a
> > transaction block in the future would not necessarily mean that it is =
safe
> > to execute it from a function, procedure, or DO block, since ANALYZE m=
ay
> > start a new transaction in process_single_relation() while an SPI sess=
ion
> > is active.
> =

> Well, I think the main point of running REPACK (ANALYZE) inside a
> transaction is to allow it to run in a procedure.  Consider something
> like
> =

> do $$
>         declare r record;
>         begin
>                 for r in
>                 select relname from pg_class where relkind =3D 'r' and
>                         relnamespace =3D (select oid from pg_namespace w=
here nspname =3D 'public')
>                 loop
>                         execute 'repack (verbose) ' || r.relname;
>                         commit;
>                 end loop;
>         end
> $$;
> =

> This works fine today and with the patch, both with REPACK and with
> CLUSTER (good); but not with VACUUM FULL (sad, but we no longer care:
> just use repack.)
> =

> This is useful because it allows server-controlled execution of
> repacking each table in its own transaction.  But as soon as you add the
> ANALYZE option, which would be valuable, this recipe no longer works.
> =

> My point is that just the ability to run REPACK (ANALYZE) in a
> transaction block without allowing it in a function would be, I think,
> rather pointless -- who could possibly be interested in repacking
> multiple tables in the same transaction?  There's just no benefit.
> =

> OTOH I think it may even be useful to implement in-procedure execution
> for CONCURRENTLY, but that's likely a more challenging patch than
> ANALYZE.

An alternative approach: as there are various commands that start their ow=
n
transactions, it could help if we taught the EXECUTE command - when execut=
ed
from pl/pgsql procedure or anonymous block (DO) - to accept this behavior.

That would probably require a new option for EXECUTE to declare that a new
transaction is either started by the statement, or (if the statement actua=
lly
does not do it) by EXECUTE itself.

(Then we might want to enhance the corresponding commands / functions in o=
ther
languages, however it seems most useful in pl/pgsql.)

-- =

Antonin Houska
Web: https://www.cybertec-postgresql.com