Feedback-ID: ie3de48e3:Fastmail
Date: Tue, 8 Sep 2026 12:27:19 +0200
From: Alvaro Herrera <alvherre@kurilemu.de>
To: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
Cc: Antonin Houska <ah@cybertec.at>, Fujii Masao <masao.fujii@gmail.com>,
	Nathan Bossart <nathandbossart@gmail.com>, pgsql-hackers@postgresql.org
Subject: Re: REPACK (ANALYZE) within transaction block segfaults
Message-ID: <ap_a_JiFXDFKjrOK@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAC+8b5jK_5QegTpjRDchR55_x+raEfBoQKUXK5c3ceW6OsK4Mw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/ap_a_JiFXDFKjrOK%40alvherre.pgsql>
Precedence: bulk

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.

Anyway, anything beyond the patch as currently presented would be for
pg20 or beyond.

I'm running the current patch through CI and will push as soon as I get
a green.

Thanks!

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"La libertad es como el dinero; el que no la sabe emplear la pierde" (Alvarez)