Re: REPACK (ANALYZE) within transaction block segfaults

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