agora inbox for pgsql-hackers@postgresql.org  

help / color / mirror / Atom feed

From: Alvaro Herrera <alvherre@kurilemu.de>
To: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
Cc: Antonin Houska <ah@cybertec.at>
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, 8 Sep 2026 12:27:19 +0200
Message-ID: <ap_a_JiFXDFKjrOK@alvherre.pgsql> (raw)
In-Reply-To: <CAC+8b5jK_5QegTpjRDchR55_x+raEfBoQKUXK5c3ceW6OsK4Mw@mail.gmail.com>

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)

view thread (25+ messages)  latest in thread

Message-ID: <ap_a_JiFXDFKjrOK@alvherre.pgsql>
Permalink:  ../ap_a_JiFXDFKjrOK@alvherre.pgsql/
Also on:    postgresql.org/message-id/ap_a_JiFXDFKjrOK@alvherre.pgsql

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: alvherre@kurilemu.de, osamaabdulqader.cs@gmail.com, ah@cybertec.at, masao.fujii@gmail.com, nathandbossart@gmail.com
  Subject: Re: REPACK (ANALYZE) within transaction block segfaults
  In-Reply-To: <ap_a_JiFXDFKjrOK@alvherre.pgsql>

* 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