Re: REPACK (ANALYZE) within transaction block segfaults

Greetings of the day everyone,

Thanks for the explanation. The procedure use cases make sense especially
for running REPACK on multiple tables with each table in its own
transaction.

The idea of teaching EXECUTE to handle statements that manage transactions
themselves also seems interesting, and I can see how that could be useful
beyond REPACK.

For now, I'll keep the current patch scope unchanged and leave those
improvements for future work.

Thanks for running the patches through CI.

On Tue, Sep 8, 2026 at 6:08 PM Antonin Houska <ah(at)cybertec(dot)at> wrote:

> Alvaro Herrera <alvherre(at)kurilemu(dot)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
> may
> > > start a new transaction in process_single_relation() while an SPI
> session
> > > 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 = 'r' and
> > relnamespace = (select oid from pg_namespace
> where nspname = '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 own
> transactions, it could help if we taught the EXECUTE command - when
> executed
> 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
> actually
> does not do it) by EXECUTE itself.
>
> (Then we might want to enhance the corresponding commands / functions in
> other
> languages, however it seems most useful in pl/pgsql.)
>
> --
> Antonin Houska
> Web: https://www.cybertec-postgresql.com
>