agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Antonin Houska <ah@cybertec.at>
To: Alvaro Herrera <alvherre@kurilemu.de>
Cc: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
Cc: Fujii Masao <masao.fujii@gmail.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: REPACK (ANALYZE) within transaction block segfaults
Date: Tue, 08 Sep 2026 14:38:25 +0200
Message-ID: <43711.1788871105@localhost> (raw)
In-Reply-To: <ap_a_JiFXDFKjrOK@alvherre.pgsql>
References: <ap_a_JiFXDFKjrOK@alvherre.pgsql>
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 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
view thread (25+ messages) latest in thread
Message-ID: <43711.1788871105@localhost>
Permalink: ../43711.1788871105@localhost/
Also on: postgresql.org/message-id/43711.1788871105@localhost
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: ah@cybertec.at, alvherre@kurilemu.de, osamaabdulqader.cs@gmail.com, masao.fujii@gmail.com, nathandbossart@gmail.com
Subject: Re: REPACK (ANALYZE) within transaction block segfaults
In-Reply-To: <43711.1788871105@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox