agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedREPACK (ANALYZE) within transaction block segfaults
25+ messages / 5 participants
[nested] [flat]
* REPACK (ANALYZE) within transaction block segfaults
@ 2026-08-27 13:56 Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 2 replies; 25+ messages in thread
From: Nathan Bossart @ 2026-08-27 13:56 UTC (permalink / raw)
To: pgsql-hackers; +Cc: alvherre@kurilemu.de
I didn't see this reported yet:
CREATE TABLE t (a INT PRIMARY KEY, b TEXT);
INSERT INTO t SELECT g, 'v' FROM generate_series(1, 100) g;
DO $$ BEGIN EXECUTE 'REPACK (ANALYZE) t'; END $$;
This produces the following output:
WARNING: transaction left non-empty SPI stack
HINT: Check for missing "SPI_finish" calls.
WARNING: snapshot 0x8ef018100 still active
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
Presumably we need to handle transaction blocks a bit like how vacuum()
does. Or maybe even prevent REPACK (ANALYZE) within a transaction block.
--
nathan
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-08-27 14:23 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
1 sibling, 0 replies; 25+ messages in thread
From: Osama Abdul Qader @ 2026-08-27 14:23 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: pgsql-hackers; alvherre@kurilemu.de
Hello Nathan,
Thanks for reporting this issue.
I haven't investigated this case yet, but I'll try to reproduce it as soon
as possible and look into the transaction/SPI handling around 'REPACK
(ANALYZE)'.
I'll follow up, once I got something concrete.
With best regards
Osama Abdul Qader
On Thu, 27 Aug, 2026, 7:26 pm Nathan Bossart, <nathandbossart@gmail.com>
wrote:
> I didn't see this reported yet:
>
> CREATE TABLE t (a INT PRIMARY KEY, b TEXT);
> INSERT INTO t SELECT g, 'v' FROM generate_series(1, 100) g;
> DO $$ BEGIN EXECUTE 'REPACK (ANALYZE) t'; END $$;
>
> This produces the following output:
>
> WARNING: transaction left non-empty SPI stack
> HINT: Check for missing "SPI_finish" calls.
> WARNING: snapshot 0x8ef018100 still active
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
>
> Presumably we need to handle transaction blocks a bit like how vacuum()
> does. Or maybe even prevent REPACK (ANALYZE) within a transaction block.
>
> --
> nathan
>
>
>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-08-27 16:09 Fujii Masao <masao.fujii@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
1 sibling, 1 reply; 25+ messages in thread
From: Fujii Masao @ 2026-08-27 16:09 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: pgsql-hackers; alvherre@kurilemu.de
On Thu, Aug 27, 2026 at 10:56 PM Nathan Bossart
<nathandbossart@gmail.com> wrote:
> Presumably we need to handle transaction blocks a bit like how vacuum()
> does. Or maybe even prevent REPACK (ANALYZE) within a transaction block.
I looked into this a bit. I think the problem is not ordinary
transaction blocks themselves, but non-top-level execution, such as the
DO block in the reproducer.
So the attached patch rejects only non-top-level REPACK (ANALYZE)
commands.
Regards,
--
Fujii Masao
Attachments:
[application/octet-stream] v1-0001-Prevent-REPACK-ANALYZE-from-functions.patch (4.5K, ../../CAHGQGwEqN80ka_PY__DiPCu+S=h_7KgujnMEU38OU9C1zsGQpA@mail.gmail.com/2-v1-0001-Prevent-REPACK-ANALYZE-from-functions.patch)
download | inline diff:
From a041c4a94bb3549034660b2d02ed9ca2b51afd2a Mon Sep 17 00:00:00 2001
From: Fujii Masao <fujii@postgresql.org>
Date: Thu, 27 Aug 2026 23:33:27 +0900
Subject: [PATCH v1] Prevent REPACK (ANALYZE) from functions
REPACK (ANALYZE) adjusts the transaction command state and active
snapshots between repacking the table and running ANALYZE. This is not
safe when executed from a function-like context such as a DO block,
and can leave SPI and snapshot state behind, eventually causing a
segmentation fault.
Fix this by rejecting REPACK (ANALYZE) when it is executed below the
top level. Plain transaction blocks and subtransactions remain allowed,
as they do not require the same restriction.
---
doc/src/sgml/ref/repack.sgml | 2 ++
src/backend/commands/repack.c | 13 +++++++++++++
src/test/regress/expected/cluster.out | 15 ++++++++++++++-
src/test/regress/sql/cluster.sql | 12 +++++++++++-
4 files changed, 40 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/repack.sgml b/doc/src/sgml/ref/repack.sgml
index 0cb72b6b289..e540917885d 100644
--- a/doc/src/sgml/ref/repack.sgml
+++ b/doc/src/sgml/ref/repack.sgml
@@ -329,6 +329,8 @@ REPACK [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] USING
<para>
Applies <xref linkend="sql-analyze"/> on the table after repacking. This is
currently only supported when a single (non-partitioned) table is specified.
+ This option cannot be used inside a function, procedure, or
+ <command>DO</command> block.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/repack.c b/src/backend/commands/repack.c
index 477c86b2ba6..5b0af42ba7d 100644
--- a/src/backend/commands/repack.c
+++ b/src/backend/commands/repack.c
@@ -315,6 +315,19 @@ ExecRepack(ParseState *pstate, RepackStmt *stmt, bool isTopLevel)
PreventInTransactionBlock(isTopLevel, "REPACK (CONCURRENTLY)");
}
+ if ((params.options & CLUOPT_ANALYZE) != 0)
+ {
+ /*
+ * REPACK (ANALYZE) switches transaction command state and active
+ * snapshots between repacking the table and analyzing it, so it must
+ * not run inside a function.
+ */
+ if (!isTopLevel)
+ ereport(ERROR,
+ (errcode(ERRCODE_ACTIVE_SQL_TRANSACTION),
+ errmsg("REPACK (ANALYZE) cannot be executed from a function or procedure")));
+ }
+
/*
* If a single relation is specified, process it and we're done ... unless
* the relation is a partitioned table, in which case we fall through.
diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out
index d1bc8a13286..597011a4d19 100644
--- a/src/test/regress/expected/cluster.out
+++ b/src/test/regress/expected/cluster.out
@@ -796,9 +796,22 @@ ORDER BY 1;
clstr_tst_pkey
(3 rows)
--- Verify partial analyze works
+-- Verify REPACK (ANALYZE) works, including partial analyze.
REPACK (ANALYZE) clstr_tst (a);
REPACK (ANALYZE) clstr_tst;
+-- Plain REPACK is allowed in a transaction block.
+BEGIN;
+REPACK clstr_tst;
+ROLLBACK;
+-- REPACK (ANALYZE) is also allowed in a transaction block.
+BEGIN;
+REPACK (ANALYZE) clstr_tst;
+ROLLBACK;
+-- REPACK (ANALYZE) is not allowed from a function.
+DO $$ BEGIN EXECUTE 'REPACK (ANALYZE) clstr_tst'; END $$;
+ERROR: REPACK (ANALYZE) cannot be executed from a function or procedure
+CONTEXT: SQL statement "REPACK (ANALYZE) clstr_tst"
+PL/pgSQL function inline_code_block line 1 at EXECUTE
REPACK (VERBOSE) clstr_tst (a);
ERROR: ANALYZE option must be specified when a column list is provided
-- REPACK w/o argument performs no ordering, so we can only check which tables
diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sql
index e7a62367adf..0bfe09460ec 100644
--- a/src/test/regress/sql/cluster.sql
+++ b/src/test/regress/sql/cluster.sql
@@ -380,9 +380,19 @@ INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail');
SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass
ORDER BY 1;
--- Verify partial analyze works
+-- Verify REPACK (ANALYZE) works, including partial analyze.
REPACK (ANALYZE) clstr_tst (a);
REPACK (ANALYZE) clstr_tst;
+-- Plain REPACK is allowed in a transaction block.
+BEGIN;
+REPACK clstr_tst;
+ROLLBACK;
+-- REPACK (ANALYZE) is also allowed in a transaction block.
+BEGIN;
+REPACK (ANALYZE) clstr_tst;
+ROLLBACK;
+-- REPACK (ANALYZE) is not allowed from a function.
+DO $$ BEGIN EXECUTE 'REPACK (ANALYZE) clstr_tst'; END $$;
REPACK (VERBOSE) clstr_tst (a);
-- REPACK w/o argument performs no ordering, so we can only check which tables
--
2.55.0
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-08-27 19:39 Nathan Bossart <nathandbossart@gmail.com>
parent: Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Nathan Bossart @ 2026-08-27 19:39 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: pgsql-hackers; alvherre@kurilemu.de
On Fri, Aug 28, 2026 at 01:09:59AM +0900, Fujii Masao wrote:
> On Thu, Aug 27, 2026 at 10:56 PM Nathan Bossart
> <nathandbossart@gmail.com> wrote:
>> Presumably we need to handle transaction blocks a bit like how vacuum()
>> does. Or maybe even prevent REPACK (ANALYZE) within a transaction block.
>
> I looked into this a bit. I think the problem is not ordinary
> transaction blocks themselves, but non-top-level execution, such as the
> DO block in the reproducer.
>
> So the attached patch rejects only non-top-level REPACK (ANALYZE)
> commands.
Hm. Couldn't we do something like the in_outer_xact/use_own_xacts stuff in
vacuum() to get it working instead?
--
nathan
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-08-28 19:15 Antonin Houska <ah@cybertec.at>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Antonin Houska @ 2026-08-28 19:15 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; alvherre@kurilemu.de
Nathan Bossart <nathandbossart@gmail.com> wrote:
> On Fri, Aug 28, 2026 at 01:09:59AM +0900, Fujii Masao wrote:
> > On Thu, Aug 27, 2026 at 10:56 PM Nathan Bossart
> > <nathandbossart@gmail.com> wrote:
> >> Presumably we need to handle transaction blocks a bit like how vacuum()
> >> does. Or maybe even prevent REPACK (ANALYZE) within a transaction block.
> >
> > I looked into this a bit. I think the problem is not ordinary
> > transaction blocks themselves, but non-top-level execution, such as the
> > DO block in the reproducer.
> >
> > So the attached patch rejects only non-top-level REPACK (ANALYZE)
> > commands.
>
> Hm. Couldn't we do something like the in_outer_xact/use_own_xacts stuff in
> vacuum() to get it working instead?
I think there are just two different concepts (for historical reasons?):
vacuum_rel() expects no active transaction on entry, while cluster_rel()
handles transaction boundaries on its own.
Since REPACK (ANALYZE) is effectively VACUUM (FULL, ANALYZE), I'd prefer the
same behavior, i.e. prohibiting execution both in a transaction block and in a
function:
postgres=# BEGIN; VACUUM (FULL, ANALYZE) t; END;
BEGIN
ERROR: VACUUM cannot run inside a transaction block
ROLLBACK
postgres=# DO $$ BEGIN EXECUTE 'VACUUM (FULL, ANALYZE) t'; END $$;
ERROR: VACUUM cannot be executed from a function or procedure
CONTEXT: SQL statement "VACUUM (FULL, ANALYZE) t"
PL/pgSQL function inline_code_block line 1 at EXECUTE
postgres=# BEGIN; REPACK (ANALYZE) t; END;
BEGIN
ERROR: REPACK (ANALYZE) cannot run inside a transaction block
ROLLBACK
postgres=# DO $$ BEGIN EXECUTE 'REPACK (ANALYZE) t'; END $$;
ERROR: REPACK (ANALYZE) cannot be executed from a function or procedure
CONTEXT: SQL statement "REPACK (ANALYZE) t"
PL/pgSQL function inline_code_block line 1 at EXECUTE
The attached patch does that.
--
Antonin Houska
Web: https://www.cybertec-postgresql.com
Attachments:
[text/x-diff] 0001-Do-not-allow-REPACK-ANALYZE-in-function-and-in-trans.patch (1.7K, ../../49398.1787944525@localhost/2-0001-Do-not-allow-REPACK-ANALYZE-in-function-and-in-trans.patch)
download | inline diff:
From b577bc4b61474b65a80b5ab8b5333c6ccd751e81 Mon Sep 17 00:00:00 2001
From: Antonin Houska <ah@cybertec.at>
Date: Fri, 28 Aug 2026 19:53:26 +0200
Subject: [PATCH] Do not allow REPACK (ANALYZE) in function and in transaction
block.
If REPACK (ANALYZE) is called from a pl/pgsql function, cluster_rel() might
start a new transaction while SPI session is in progress. Use
PreventInTransactionBlock() to avoid that.
That function also raises error if REPACK (ANALYZE) is called in a transaction
block, but that's fine: VACUUM (FULL, ANALYZE) - a synonym of REPACK (ANALYZE)
- also raises ERROR in that case.
---
src/backend/commands/repack.c | 14 ++++++++++++++
1 file changed, 14 insertions(+)
diff --git a/src/backend/commands/repack.c b/src/backend/commands/repack.c
index 477c86b2ba6..81877029199 100644
--- a/src/backend/commands/repack.c
+++ b/src/backend/commands/repack.c
@@ -314,6 +314,20 @@ ExecRepack(ParseState *pstate, RepackStmt *stmt, bool isTopLevel)
*/
PreventInTransactionBlock(isTopLevel, "REPACK (CONCURRENTLY)");
}
+ else if ((params.options & CLUOPT_ANALYZE) != 0)
+ {
+ /*
+ * Technically, transaction block is not a problem for REPACK
+ * (ANALYZE), but if it's called from a pl/pgsql function,
+ * cluster_rel() might start a new transaction while SPI session is in
+ * progress. Make sure ERROR is raised instead.
+ *
+ * This way we also prohibit execution in a transaction block, but
+ * that's just consistent with VACUUM (FULL, ANALYZE), which is a
+ * synonym for REPACK (ANALYZE).
+ */
+ PreventInTransactionBlock(isTopLevel, "REPACK (ANALYZE)");
+ }
/*
* If a single relation is specified, process it and we're done ... unless
--
2.52.0
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-08-28 19:52 Nathan Bossart <nathandbossart@gmail.com>
parent: Antonin Houska <ah@cybertec.at>
0 siblings, 2 replies; 25+ messages in thread
From: Nathan Bossart @ 2026-08-28 19:52 UTC (permalink / raw)
To: Antonin Houska <ah@cybertec.at>; +Cc: Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; alvherre@kurilemu.de
On Fri, Aug 28, 2026 at 09:15:25PM +0200, Antonin Houska wrote:
> I think there are just two different concepts (for historical reasons?):
> vacuum_rel() expects no active transaction on entry, while cluster_rel()
> handles transaction boundaries on its own.
>
> Since REPACK (ANALYZE) is effectively VACUUM (FULL, ANALYZE), I'd prefer the
> same behavior, i.e. prohibiting execution both in a transaction block and in a
> function:
This is probably the way to go for v19. As you note, the analogous VACUUM
command has long ERROR'd, and we could always look into removing this
restriction in the future. I'd rather do it that way than ship an
incorrect fix in v19 that will be tougher to back out.
--
nathan
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-08-30 16:22 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
1 sibling, 1 reply; 25+ messages in thread
From: Osama Abdul Qader @ 2026-08-30 16:22 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Antonin Houska <ah@cybertec.at>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; alvherre@kurilemu.de
Hi,
Just a quick update on the replication slot invalidation durability issue.
I've moved past the initial reproduction and have been investigating the
underlying behavior. I now have a candidate patch which changes the
invalidation flow so that the invalidated slot state is persisted before
the invalidation is published in shared memory. The slot synchronization
path has been updated accordingly, and I've also added TAP coverage for the
durability scenarios, including injected failures during slot persistence.
I was able to get the relevant regression coverage passing. While running
the broader recovery test suite, I encountered a few failures in existing
TAP tests, particularly around 001_stream_rep.pl and 006_logical_decoding.pl.
I'm currently investigating whether these are related to my changes or are
test-environment/intermittent issues.
I'll continue working through these failures and validating the patch. Once
the remaining test issues are understood and the patch is cleaned up, I
expect to have a revised patch ready soon.
Best regards,
Osama Abdul Qader
On Sat, Aug 29, 2026 at 1:22 AM Nathan Bossart <nathandbossart@gmail.com>
wrote:
> On Fri, Aug 28, 2026 at 09:15:25PM +0200, Antonin Houska wrote:
> > I think there are just two different concepts (for historical reasons?):
> > vacuum_rel() expects no active transaction on entry, while cluster_rel()
> > handles transaction boundaries on its own.
> >
> > Since REPACK (ANALYZE) is effectively VACUUM (FULL, ANALYZE), I'd prefer
> the
> > same behavior, i.e. prohibiting execution both in a transaction block
> and in a
> > function:
>
> This is probably the way to go for v19. As you note, the analogous VACUUM
> command has long ERROR'd, and we could always look into removing this
> restriction in the future. I'd rather do it that way than ship an
> incorrect fix in v19 that will be tougher to back out.
>
> --
> nathan
>
>
>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-08-30 17:01 Nathan Bossart <nathandbossart@gmail.com>
parent: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Nathan Bossart @ 2026-08-30 17:01 UTC (permalink / raw)
To: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>; +Cc: Antonin Houska <ah@cybertec.at>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; alvherre@kurilemu.de
On Sun, Aug 30, 2026 at 09:52:46PM +0530, Osama Abdul Qader wrote:
> Just a quick update on the replication slot invalidation durability issue.
I think you may have replied to the wrong thread...
--
nathan
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-08-31 11:10 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 0 replies; 25+ messages in thread
From: Osama Abdul Qader @ 2026-08-31 11:10 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Antonin Houska <ah@cybertec.at>; Fujii Masao <masao.fujii@gmail.com>; pgsql-hackers; alvherre@kurilemu.de
Greetings of the day everyone,
You were right, my intention was to update you about my reproduction of the
bug and the work I was doing yesterday.
I regret for the confusion I caused by replying to the wrong thread. I'll
sure to double check the thread before sending future updates.
With regards,
Osama Abdul Qader.
On Sun, 30 Aug, 2026, 10:31 pm Nathan Bossart, <nathandbossart@gmail.com>
wrote:
> On Sun, Aug 30, 2026 at 09:52:46PM +0530, Osama Abdul Qader wrote:
> > Just a quick update on the replication slot invalidation durability
> issue.
>
> I think you may have replied to the wrong thread...
>
> --
> nathan
>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-09-02 05:26 Fujii Masao <masao.fujii@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
1 sibling, 1 reply; 25+ messages in thread
From: Fujii Masao @ 2026-09-02 05:26 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Antonin Houska <ah@cybertec.at>; pgsql-hackers; alvherre@kurilemu.de
On Sat, Aug 29, 2026 at 4:52 AM Nathan Bossart <nathandbossart@gmail.com> wrote:
> This is probably the way to go for v19. As you note, the analogous VACUUM
> command has long ERROR'd, and we could always look into removing this
> restriction in the future. I'd rather do it that way than ship an
> incorrect fix in v19 that will be tougher to back out.
I'm ok with this.
I have a few review comments on Antonin's patch.
As with my patch, I think it would be better to document the restriction
and add tests covering the following cases:
- plain REPACK is allowed in a transaction block
- REPACK (ANALYZE) is not allowed in a transaction block
- REPACK (ANALYZE) is not allowed from a function
+ * Technically, transaction block is not a problem for REPACK
+ * (ANALYZE), but if it's called from a pl/pgsql function,
Since it can also be called from procedures, functions and DO blocks,
mentioning only a PL/pgSQL function seems too narrow.
+ * cluster_rel() might start a new transaction while SPI session is in
Is this correct? It seems that the new transaction for ANALYZE is
started in process_single_relation(), not in cluster_rel().
+ * that's just consistent with VACUUM (FULL, ANALYZE), which is a
+ * synonym for REPACK (ANALYZE).
Is VACUUM (FULL, ANALYZE) really a synonym for REPACK (ANALYZE)?
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-09-02 08:20 Alvaro Herrera <alvherre@kurilemu.de>
parent: Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Alvaro Herrera @ 2026-09-02 08:20 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: Nathan Bossart <nathandbossart@gmail.com>; Antonin Houska <ah@cybertec.at>; pgsql-hackers
On 2026-Sep-02, Fujii Masao wrote:
> + * that's just consistent with VACUUM (FULL, ANALYZE), which is a
> + * synonym for REPACK (ANALYZE).
>
> Is VACUUM (FULL, ANALYZE) really a synonym for REPACK (ANALYZE)?
That's the intent, at least. If there are things that work differently,
I would strive to change them so that they do work the same. However,
some such changes might be too invasive for pg19, but I would still see
about changing those in pg20.
Now, maybe there are things about VACUUM FULL ANALYZE that we don't like
(perhaps, for instance, they exist solely because of even older
backwards compatibility concerns) that we would prefer not to have in
REPACK. I don't know if anything of that sort exists, but if so, I
would propose to seek decisions for each thing individually.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
Essentially, you're proposing Kevlar shoes as a solution for the problem
that you want to walk around carrying a loaded gun aimed at your foot.
(Tom Lane)
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-09-02 08:47 Antonin Houska <ah@cybertec.at>
parent: Alvaro Herrera <alvherre@kurilemu.de>
0 siblings, 1 reply; 25+ messages in thread
From: Antonin Houska @ 2026-09-02 08:47 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@kurilemu.de>; +Cc: Fujii Masao <masao.fujii@gmail.com>; Nathan Bossart <nathandbossart@gmail.com>; pgsql-hackers
Alvaro Herrera <alvherre@kurilemu.de> wrote:
> On 2026-Sep-02, Fujii Masao wrote:
>
> > + * that's just consistent with VACUUM (FULL, ANALYZE), which is a
> > + * synonym for REPACK (ANALYZE).
> >
> > Is VACUUM (FULL, ANALYZE) really a synonym for REPACK (ANALYZE)?
>
> That's the intent, at least. If there are things that work differently,
> I would strive to change them so that they do work the same. However,
> some such changes might be too invasive for pg19, but I would still see
> about changing those in pg20.
>
> Now, maybe there are things about VACUUM FULL ANALYZE that we don't like
> (perhaps, for instance, they exist solely because of even older
> backwards compatibility concerns) that we would prefer not to have in
> REPACK. I don't know if anything of that sort exists, but if so, I
> would propose to seek decisions for each thing individually.
Maybe the question was about the wording - "synonym" might indicate that both
commands execute the same code. Perhaps the comment should rather say that
REPACK (ANALYZE) is (intended to be) a replacement of VACUUM (FULL, ANALYZE).
--
Antonin Houska
Web: https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-09-02 11:54 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Antonin Houska <ah@cybertec.at>
0 siblings, 1 reply; 25+ messages in thread
From: Osama Abdul Qader @ 2026-09-02 11:54 UTC (permalink / raw)
To: Antonin Houska <ah@cybertec.at>; +Cc: Alvaro Herrera <alvherre@kurilemu.de>; Fujii Masao <masao.fujii@gmail.com>; Nathan Bossart <nathandbossart@gmail.com>; pgsql-hackers
Hi everyone,
I believe I was replying to the wrong thread earlier.
The issue I was looking into is the crash caused by executing REPACK
(ANALYZE) inside a transaction block.
REPACK (ANALYZE) performs transaction management internally, including
committing and starting a new transaction while processing the relation. It
therefore cannot safely be executed from an existing transaction block.
I have prepared a patch that rejects REPACK (ANALYZE) with
PreventInTransactionBlock(), consistent with the existing restriction
for REPACK
(CONCURRENTLY). I also added a regression test covering execution inside a
transaction block.
The patch applies cleanly to the current tree and passes git diff --check.
Patch attached.
Regards,
Osama Abdul Qader
On Wed, Sep 2, 2026 at 2:17 PM Antonin Houska <ah@cybertec.at> wrote:
> Alvaro Herrera <alvherre@kurilemu.de> wrote:
>
> > On 2026-Sep-02, Fujii Masao wrote:
> >
> > > + * that's just consistent with VACUUM (FULL, ANALYZE), which is a
> > > + * synonym for REPACK (ANALYZE).
> > >
> > > Is VACUUM (FULL, ANALYZE) really a synonym for REPACK (ANALYZE)?
> >
> > That's the intent, at least. If there are things that work differently,
> > I would strive to change them so that they do work the same. However,
> > some such changes might be too invasive for pg19, but I would still see
> > about changing those in pg20.
> >
> > Now, maybe there are things about VACUUM FULL ANALYZE that we don't like
> > (perhaps, for instance, they exist solely because of even older
> > backwards compatibility concerns) that we would prefer not to have in
> > REPACK. I don't know if anything of that sort exists, but if so, I
> > would propose to seek decisions for each thing individually.
>
> Maybe the question was about the wording - "synonym" might indicate that
> both
> commands execute the same code. Perhaps the comment should rather say that
> REPACK (ANALYZE) is (intended to be) a replacement of VACUUM (FULL,
> ANALYZE).
>
> --
> Antonin Houska
> Web: https://www.cybertec-postgresql.com
>
>
>
Attachments:
[application/x-patch] 0001-reject-repack-analyze-in-transaction.patch (1.3K, ../../CAC+8b5ha_W8S_qaK1DUj_Z=+gQQX7PfSyfypLafgtXF=zhRrQA@mail.gmail.com/3-0001-reject-repack-analyze-in-transaction.patch)
download | inline diff:
diff --git a/src/backend/commands/repack.c b/src/backend/commands/repack.c
index edff54e734e..3a482a73ceb 100644
--- a/src/backend/commands/repack.c
+++ b/src/backend/commands/repack.c
@@ -314,6 +314,16 @@ ExecRepack(ParseState *pstate, RepackStmt *stmt, bool isTopLevel)
PreventInTransactionBlock(isTopLevel, "REPACK (CONCURRENTLY)");
}
+ else if ((params.options & CLUOPT_ANALYZE) != 0)
+ {
+ /*
+ * REPACK (ANALYZE) performs transaction management internally.
+ * It therefore cannot be executed from a transaction block or
+ * from a function.
+ */
+ PreventInTransactionBlock(isTopLevel, "REPACK (ANALYZE)");
+ }
+
/*
* If a single relation is specified, process it and we're done ... unless
* the relation is a partitioned table, in which case we fall through.
diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sql
index e7a62367adf..cb170731147 100644
--- a/src/test/regress/sql/cluster.sql
+++ b/src/test/regress/sql/cluster.sql
@@ -380,6 +380,10 @@ INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail');
SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass
ORDER BY 1;
+-- REPACK (ANALYZE) must not be executed inside a transaction block
+BEGIN;
+REPACK (ANALYZE) clstr_tst;
+ROLLBACK;
-- Verify partial analyze works
REPACK (ANALYZE) clstr_tst (a);
REPACK (ANALYZE) clstr_tst;
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-09-03 04:42 Antonin Houska <ah@cybertec.at>
parent: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Antonin Houska @ 2026-09-03 04:42 UTC (permalink / raw)
To: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>; +Cc: Alvaro Herrera <alvherre@kurilemu.de>; Fujii Masao <masao.fujii@gmail.com>; Nathan Bossart <nathandbossart@gmail.com>; pgsql-hackers
Osama Abdul Qader <osamaabdulqader.cs@gmail.com> wrote:
> I have prepared a patch that rejects REPACK (ANALYZE) with PreventInTransactionBlock(), consistent with the existing restriction for
> REPACK (CONCURRENTLY). I also added a regression test covering execution inside a transaction block.
>
> The patch applies cleanly to the current tree and passes git diff --check.
Is this a new version of [1]? If so, I'm not sure it addresses all the
problems mentioned in [2]. And regarding regression tests, it misses the
changes in expected/cluster.out.
[1] https://www.postgresql.org/message-id/49398.1787944525%40localhost
[2] https://www.postgresql.org/message-id/CAHGQGwEezdMUixhJ-N0YO0OFUmh0uPaXRDkds5FS-5dmdwz4Bg%40mail.gma...
--
Antonin Houska
Web: https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-09-03 07:13 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Antonin Houska <ah@cybertec.at>
0 siblings, 1 reply; 25+ messages in thread
From: Osama Abdul Qader @ 2026-09-03 07:13 UTC (permalink / raw)
To: Antonin Houska <ah@cybertec.at>; +Cc: Alvaro Herrera <alvherre@kurilemu.de>; Fujii Masao <masao.fujii@gmail.com>; Nathan Bossart <nathandbossart@gmail.com>; pgsql-hackers
Hi Antonin and Everyone,
Greetings of the day,
I'll look into the issues mentioned in [1] and [2], including the missing
regression test changes in 'expected/cluster.out', and prepare an updated
patch.
With best regards,
Osama Abdul Qader
On Thu, Sep 3, 2026 at 10:12 AM Antonin Houska <ah@cybertec.at> wrote:
> Osama Abdul Qader <osamaabdulqader.cs@gmail.com> wrote:
>
> > I have prepared a patch that rejects REPACK (ANALYZE) with
> PreventInTransactionBlock(), consistent with the existing restriction for
> > REPACK (CONCURRENTLY). I also added a regression test covering execution
> inside a transaction block.
> >
> > The patch applies cleanly to the current tree and passes git diff
> --check.
>
> Is this a new version of [1]? If so, I'm not sure it addresses all the
> problems mentioned in [2]. And regarding regression tests, it misses the
> changes in expected/cluster.out.
>
>
> [1] https://www.postgresql.org/message-id/49398.1787944525%40localhost
> [2]
> https://www.postgresql.org/message-id/CAHGQGwEezdMUixhJ-N0YO0OFUmh0uPaXRDkds5FS-5dmdwz4Bg%40mail.gma...
>
> --
> Antonin Houska
> Web: https://www.cybertec-postgresql.com
>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-09-03 08:26 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Osama Abdul Qader @ 2026-09-03 08:26 UTC (permalink / raw)
To: Antonin Houska <ah@cybertec.at>; +Cc: Alvaro Herrera <alvherre@kurilemu.de>; Fujii Masao <masao.fujii@gmail.com>; Nathan Bossart <nathandbossart@gmail.com>; pgsql-hackers
Hi everybody,
Thanks for pointing that out.
Yes, this is an updated version of the patch. I've addressed the missing
regression test changes by updating 'expected/cluster.out' as well.
The patch now includes:
- the 'PreventInTransactionBlock()' check for 'REPACK (ANALYZE)';
- the regression test in 'cluster.sql'; and
- the corresponding expected output in 'expected/cluster.out'
I also verified that the regression tests 'test_setup' and cluster pass,
and that the patch applies cleanly to a clean worktree.
The updated patch is attached.
With Regards,
Osama Abdul Qader
On Thu, Sep 3, 2026 at 12:43 PM Osama Abdul Qader <
osamaabdulqader.cs@gmail.com> wrote:
> Hi Antonin and Everyone,
>
> Greetings of the day,
>
> I'll look into the issues mentioned in [1] and [2], including the missing
> regression test changes in 'expected/cluster.out', and prepare an updated
> patch.
>
> With best regards,
> Osama Abdul Qader
>
> On Thu, Sep 3, 2026 at 10:12 AM Antonin Houska <ah@cybertec.at> wrote:
>
>> Osama Abdul Qader <osamaabdulqader.cs@gmail.com> wrote:
>>
>> > I have prepared a patch that rejects REPACK (ANALYZE) with
>> PreventInTransactionBlock(), consistent with the existing restriction for
>> > REPACK (CONCURRENTLY). I also added a regression test covering
>> execution inside a transaction block.
>> >
>> > The patch applies cleanly to the current tree and passes git diff
>> --check.
>>
>> Is this a new version of [1]? If so, I'm not sure it addresses all the
>> problems mentioned in [2]. And regarding regression tests, it misses the
>> changes in expected/cluster.out.
>>
>>
>> [1] https://www.postgresql.org/message-id/49398.1787944525%40localhost
>> [2]
>> https://www.postgresql.org/message-id/CAHGQGwEezdMUixhJ-N0YO0OFUmh0uPaXRDkds5FS-5dmdwz4Bg%40mail.gma...
>>
>> --
>> Antonin Houska
>> Web: https://www.cybertec-postgresql.com
>>
>
Attachments:
[text/x-patch] 0001-reject-repack-analyze-in-transaction.patch (1.9K, ../../CAC+8b5hZ0d9UNNGSh8LwCFytmuTx0cQszUZo2aJ9kSYRaOSTTg@mail.gmail.com/3-0001-reject-repack-analyze-in-transaction.patch)
download | inline diff:
diff --git a/src/backend/commands/repack.c b/src/backend/commands/repack.c
index edff54e734e..fac4e12c13c 100644
--- a/src/backend/commands/repack.c
+++ b/src/backend/commands/repack.c
@@ -314,6 +314,16 @@ ExecRepack(ParseState *pstate, RepackStmt *stmt, bool isTopLevel)
PreventInTransactionBlock(isTopLevel, "REPACK (CONCURRENTLY)");
}
+ else if ((params.options & CLUOPT_ANALYZE) != 0)
+ {
+ /*
+ * REPACK (ANALYZE) performs transaction management internally.
+ * It therefore cannot be executed inside a transaction block or
+ * from a function or procedure.
+ */
+ PreventInTransactionBlock(isTopLevel, "REPACK (ANALYZE)");
+ }
+
/*
* If a single relation is specified, process it and we're done ... unless
* the relation is a partitioned table, in which case we fall through.
diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out
index d1bc8a13286..36dd1f3804c 100644
--- a/src/test/regress/expected/cluster.out
+++ b/src/test/regress/expected/cluster.out
@@ -796,6 +796,11 @@ ORDER BY 1;
clstr_tst_pkey
(3 rows)
+-- REPACK (ANALYZE) must not be executed inside a transaction block
+BEGIN;
+REPACK (ANALYZE) clstr_tst;
+ERROR: REPACK (ANALYZE) cannot run inside a transaction block
+ROLLBACK;
-- Verify partial analyze works
REPACK (ANALYZE) clstr_tst (a);
REPACK (ANALYZE) clstr_tst;
diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sql
index e7a62367adf..cb170731147 100644
--- a/src/test/regress/sql/cluster.sql
+++ b/src/test/regress/sql/cluster.sql
@@ -380,6 +380,10 @@ INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail');
SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass
ORDER BY 1;
+-- REPACK (ANALYZE) must not be executed inside a transaction block
+BEGIN;
+REPACK (ANALYZE) clstr_tst;
+ROLLBACK;
-- Verify partial analyze works
REPACK (ANALYZE) clstr_tst (a);
REPACK (ANALYZE) clstr_tst;
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-09-03 13:50 Fujii Masao <masao.fujii@gmail.com>
parent: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Fujii Masao @ 2026-09-03 13:50 UTC (permalink / raw)
To: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>; +Cc: Antonin Houska <ah@cybertec.at>; Alvaro Herrera <alvherre@kurilemu.de>; Nathan Bossart <nathandbossart@gmail.com>; pgsql-hackers
On Thu, Sep 3, 2026 at 5:27 PM Osama Abdul Qader
<osamaabdulqader.cs@gmail.com> wrote:
> The updated patch is attached.
Thanks for updating the patch!
I have a few review comments.
As I told upthread, I think the restriction that REPACK (ANALYZE) cannot
be executed inside a transaction block should be documented. For example,
how about adding something like the following to the description of
the ANALYZE option in the REPACK docs?
This option cannot be used inside a transaction block, or from a
function, procedure, or <command>DO</command> block.
+ * It therefore cannot be executed inside a transaction block or
Is this really true? As discussed upthread, I was thinking that it can
be executed even inside a transaction block, but that we decided to
intentionally prevent it from doing so to match the behavior of
VACUUM (FULL, ANALYZE) as the safe behavior for v19. No?
Regarding the tests, as I told upthread, I think it's better to also
cover the following cases:
- plain REPACK is allowed in a transaction block
- REPACK (ANALYZE) is not allowed from a function
For example:
-------------------------
--- Verify partial analyze works
+-- Verify REPACK (ANALYZE) works, including partial analyze.
REPACK (ANALYZE) clstr_tst (a);
REPACK (ANALYZE) clstr_tst;
+-- Plain REPACK is allowed in a transaction block.
+BEGIN;
+REPACK clstr_tst;
+ROLLBACK;
+-- REPACK (ANALYZE) is not allowed in a transaction block.
+BEGIN;
+REPACK (ANALYZE) clstr_tst;
+ROLLBACK;
+-- REPACK (ANALYZE) is not allowed from a function.
+DO $$ BEGIN EXECUTE 'REPACK (ANALYZE) clstr_tst'; END $$;
-------------------------
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-09-03 16:29 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Osama Abdul Qader @ 2026-09-03 16:29 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: Antonin Houska <ah@cybertec.at>; Alvaro Herrera <alvherre@kurilemu.de>; Nathan Bossart <nathandbossart@gmail.com>; pgsql-hackers
Good Evening Masao San
Thanks for the detailed review.
I understood and I'll update the patch to:
- document the transaction-block restriction in repack documentation.
- revise the comment in 'repack.c' to reflect that this is an
intentional restriction for v19, rather than an inherent requirement; and
- expand the regression tests to cover plain REPACK inside a transaction
block, REPACK (ANALYZE) inside a transaction block, and REPACK (ANALYZE)
from a DO block.
I'll send an updated patch once these changes are made.
With regards,
Osama Abdul Qader
On Thu, 3 Sept, 2026, 7:21 pm Fujii Masao, <masao.fujii@gmail.com> wrote:
> On Thu, Sep 3, 2026 at 5:27 PM Osama Abdul Qader
> <osamaabdulqader.cs@gmail.com> wrote:
> > The updated patch is attached.
>
> Thanks for updating the patch!
>
> I have a few review comments.
>
> As I told upthread, I think the restriction that REPACK (ANALYZE) cannot
> be executed inside a transaction block should be documented. For example,
> how about adding something like the following to the description of
> the ANALYZE option in the REPACK docs?
>
> This option cannot be used inside a transaction block, or from a
> function, procedure, or <command>DO</command> block.
>
>
> + * It therefore cannot be executed inside a transaction block or
>
> Is this really true? As discussed upthread, I was thinking that it can
> be executed even inside a transaction block, but that we decided to
> intentionally prevent it from doing so to match the behavior of
> VACUUM (FULL, ANALYZE) as the safe behavior for v19. No?
>
>
> Regarding the tests, as I told upthread, I think it's better to also
> cover the following cases:
>
> - plain REPACK is allowed in a transaction block
> - REPACK (ANALYZE) is not allowed from a function
>
> For example:
>
> -------------------------
> --- Verify partial analyze works
> +-- Verify REPACK (ANALYZE) works, including partial analyze.
> REPACK (ANALYZE) clstr_tst (a);
> REPACK (ANALYZE) clstr_tst;
> +-- Plain REPACK is allowed in a transaction block.
> +BEGIN;
> +REPACK clstr_tst;
> +ROLLBACK;
> +-- REPACK (ANALYZE) is not allowed in a transaction block.
> +BEGIN;
> +REPACK (ANALYZE) clstr_tst;
> +ROLLBACK;
> +-- REPACK (ANALYZE) is not allowed from a function.
> +DO $$ BEGIN EXECUTE 'REPACK (ANALYZE) clstr_tst'; END $$;
> -------------------------
>
> Regards,
>
>
> --
> Fujii Masao
>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-09-03 17:35 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Osama Abdul Qader @ 2026-09-03 17:35 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: Antonin Houska <ah@cybertec.at>; Alvaro Herrera <alvherre@kurilemu.de>; Nathan Bossart <nathandbossart@gmail.com>; pgsql-hackers
Thanks for the review,
I've updated the patch to address your comments:
- Documented that 'REPACK (ANALYZE)' cannot be used inside a transaction
block, or from a function, procedure or 'DO' block.
- Updated the comment in repack.c to clarify that this restriction is
intentional for now, consistently with VACUUM (FULL, ANALYZE).
- Added regression tests covering plain REPACK inside a transaction
block; REPACK (ANALYZE) inside a transaction block; REPACK (ANALYZE)
from a DO block.
- Regenerated 'expected/cluster.out.
The focused 'test_setup' and 'cluster' regression tests pass, and the patch
applies cleanly to the current tree.
The updated patch is attached.
With regards,
Osama Abdul Qader
On Thu, Sep 3, 2026 at 9:59 PM Osama Abdul Qader <
osamaabdulqader.cs@gmail.com> wrote:
> Good Evening Masao San
>
> Thanks for the detailed review.
>
> I understood and I'll update the patch to:
>
>
> - document the transaction-block restriction in repack documentation.
> - revise the comment in 'repack.c' to reflect that this is an
> intentional restriction for v19, rather than an inherent requirement; and
> - expand the regression tests to cover plain REPACK inside a
> transaction block, REPACK (ANALYZE) inside a transaction block, and REPACK
> (ANALYZE) from a DO block.
>
> I'll send an updated patch once these changes are made.
>
> With regards,
> Osama Abdul Qader
>
> On Thu, 3 Sept, 2026, 7:21 pm Fujii Masao, <masao.fujii@gmail.com> wrote:
>
>> On Thu, Sep 3, 2026 at 5:27 PM Osama Abdul Qader
>> <osamaabdulqader.cs@gmail.com> wrote:
>> > The updated patch is attached.
>>
>> Thanks for updating the patch!
>>
>> I have a few review comments.
>>
>> As I told upthread, I think the restriction that REPACK (ANALYZE) cannot
>> be executed inside a transaction block should be documented. For example,
>> how about adding something like the following to the description of
>> the ANALYZE option in the REPACK docs?
>>
>> This option cannot be used inside a transaction block, or from a
>> function, procedure, or <command>DO</command> block.
>>
>>
>> + * It therefore cannot be executed inside a transaction block or
>>
>> Is this really true? As discussed upthread, I was thinking that it can
>> be executed even inside a transaction block, but that we decided to
>> intentionally prevent it from doing so to match the behavior of
>> VACUUM (FULL, ANALYZE) as the safe behavior for v19. No?
>>
>>
>> Regarding the tests, as I told upthread, I think it's better to also
>> cover the following cases:
>>
>> - plain REPACK is allowed in a transaction block
>> - REPACK (ANALYZE) is not allowed from a function
>>
>> For example:
>>
>> -------------------------
>> --- Verify partial analyze works
>> +-- Verify REPACK (ANALYZE) works, including partial analyze.
>> REPACK (ANALYZE) clstr_tst (a);
>> REPACK (ANALYZE) clstr_tst;
>> +-- Plain REPACK is allowed in a transaction block.
>> +BEGIN;
>> +REPACK clstr_tst;
>> +ROLLBACK;
>> +-- REPACK (ANALYZE) is not allowed in a transaction block.
>> +BEGIN;
>> +REPACK (ANALYZE) clstr_tst;
>> +ROLLBACK;
>> +-- REPACK (ANALYZE) is not allowed from a function.
>> +DO $$ BEGIN EXECUTE 'REPACK (ANALYZE) clstr_tst'; END $$;
>> -------------------------
>>
>> Regards,
>>
>>
>> --
>> Fujii Masao
>>
>
Attachments:
[text/x-patch] 0001-reject-repack-analyze-in-transaction.patch (3.5K, ../../CAC+8b5iYeoG2+8u3Wdz7N4qJFK3PXr5t1wjDu2UbmP2As=VM1Q@mail.gmail.com/3-0001-reject-repack-analyze-in-transaction.patch)
download | inline diff:
diff --git a/doc/src/sgml/ref/repack.sgml b/doc/src/sgml/ref/repack.sgml
index 0cb72b6b289..d9c7c9e6b95 100644
--- a/doc/src/sgml/ref/repack.sgml
+++ b/doc/src/sgml/ref/repack.sgml
@@ -329,6 +329,8 @@ REPACK [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] USING
<para>
Applies <xref linkend="sql-analyze"/> on the table after repacking. This is
currently only supported when a single (non-partitioned) table is specified.
+ This option cannot be used inside a transaction block, or from a function,
+ procedure, or <command>DO</command> block.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/repack.c b/src/backend/commands/repack.c
index edff54e734e..c1ee5cdd37f 100644
--- a/src/backend/commands/repack.c
+++ b/src/backend/commands/repack.c
@@ -314,6 +314,15 @@ ExecRepack(ParseState *pstate, RepackStmt *stmt, bool isTopLevel)
PreventInTransactionBlock(isTopLevel, "REPACK (CONCURRENTLY)");
}
+ else if ((params.options & CLUOPT_ANALYZE) != 0)
+ {
+ /*
+ * REPACK (ANALYZE) is not allowed in a transaction block for now,
+ * consistently with VACUUM (FULL, ANALYZE).
+ */
+ PreventInTransactionBlock(isTopLevel, "REPACK (ANALYZE)");
+ }
+
/*
* If a single relation is specified, process it and we're done ... unless
* the relation is a partitioned table, in which case we fall through.
diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out
index d1bc8a13286..9ad7e2e21c0 100644
--- a/src/test/regress/expected/cluster.out
+++ b/src/test/regress/expected/cluster.out
@@ -796,9 +796,23 @@ ORDER BY 1;
clstr_tst_pkey
(3 rows)
--- Verify partial analyze works
+-- Verify REPACK (ANALYZE) works, including partial analyze.
REPACK (ANALYZE) clstr_tst (a);
REPACK (ANALYZE) clstr_tst;
+-- Plain REPACK is allowed in a transaction block.
+BEGIN;
+REPACK clstr_tst;
+ROLLBACK;
+-- REPACK (ANALYZE) is not allowed in a transaction block.
+BEGIN;
+REPACK (ANALYZE) clstr_tst;
+ERROR: REPACK (ANALYZE) cannot run inside a transaction block
+ROLLBACK;
+-- REPACK (ANALYZE) is not allowed from a function.
+DO $$ BEGIN EXECUTE 'REPACK (ANALYZE) clstr_tst'; END $$;
+ERROR: REPACK (ANALYZE) cannot be executed from a function or procedure
+CONTEXT: SQL statement "REPACK (ANALYZE) clstr_tst"
+PL/pgSQL function inline_code_block line 1 at EXECUTE
REPACK (VERBOSE) clstr_tst (a);
ERROR: ANALYZE option must be specified when a column list is provided
-- REPACK w/o argument performs no ordering, so we can only check which tables
diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sql
index e7a62367adf..dcc91e73698 100644
--- a/src/test/regress/sql/cluster.sql
+++ b/src/test/regress/sql/cluster.sql
@@ -380,9 +380,23 @@ INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail');
SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass
ORDER BY 1;
--- Verify partial analyze works
+-- Verify REPACK (ANALYZE) works, including partial analyze.
REPACK (ANALYZE) clstr_tst (a);
REPACK (ANALYZE) clstr_tst;
+
+-- Plain REPACK is allowed in a transaction block.
+BEGIN;
+REPACK clstr_tst;
+ROLLBACK;
+
+-- REPACK (ANALYZE) is not allowed in a transaction block.
+BEGIN;
+REPACK (ANALYZE) clstr_tst;
+ROLLBACK;
+
+-- REPACK (ANALYZE) is not allowed from a function.
+DO $$ BEGIN EXECUTE 'REPACK (ANALYZE) clstr_tst'; END $$;
+
REPACK (VERBOSE) clstr_tst (a);
-- REPACK w/o argument performs no ordering, so we can only check which tables
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-09-04 12:54 Antonin Houska <ah@cybertec.at>
parent: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Antonin Houska @ 2026-09-04 12:54 UTC (permalink / raw)
To: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>; +Cc: Fujii Masao <masao.fujii@gmail.com>; Alvaro Herrera <alvherre@kurilemu.de>; Nathan Bossart <nathandbossart@gmail.com>; pgsql-hackers
Osama Abdul Qader <osamaabdulqader.cs@gmail.com> wrote:
> I've updated the patch to address your comments:
>
> * Documented that 'REPACK (ANALYZE)' cannot be used inside a transaction block, or from a function, procedure or 'DO' block.
> * Updated the comment in repack.c to clarify that this restriction is intentional for now, consistently with VACUUM (FULL, ANALYZE).
In [1] I added a comment explaining why it's a problem to run REPACK (ANALYZE)
from function. I thought it's important so that, when we conclude (in the
future) that running in block is fine, we still keep checking for execution
from a function. (PreventInTransactionBlock() checks both at the moment.)
In [2] I was advised to make the comment more precise, but as you appear to
have taken the patch over, I expected that you'll do that. However, you simply
removed that part of the comment. Can you please explain why?
BTW, "top posting" is not the preferred style in this mailing list [3].
[1] https://www.postgresql.org/message-id/49398.1787944525%40localhost
[2] https://www.postgresql.org/message-id/CAHGQGwEezdMUixhJ-N0YO0OFUmh0uPaXRDkds5FS-5dmdwz4Bg%40mail.gma...
[3] https://wiki.postgresql.org/wiki/Mailing_Lists
--
Antonin Houska
Web: https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-09-04 13:30 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Antonin Houska <ah@cybertec.at>
0 siblings, 1 reply; 25+ messages in thread
From: Osama Abdul Qader @ 2026-09-04 13:30 UTC (permalink / raw)
To: Antonin Houska <ah@cybertec.at>; +Cc: Fujii Masao <masao.fujii@gmail.com>; Alvaro Herrera <alvherre@kurilemu.de>; Nathan Bossart <nathandbossart@gmail.com>; pgsql-hackers
I removed that path because I interpreted the earlier discussion as asking
me to avoid claiming that REPACK (ANALYZE) inherently performs transaction
management, and I replaced it with a shorter comment explaining the current
restriction.
I now understand your point that the comment should also explain the
separate restriction on execution from a function/procedure/DO block. In
particular, even if running REPACK (ANALYZE) inside a transaction block is
reconsidered in the future, the restriction on execution from a function
may still need to remain.
I'll update the comment to make that distinction explicit and will also
follow the mailing-list preferred inline-posting style in future replies.
Thanks for pointing this out.
With Regards,
Osama Abdul Qader
On Fri, 4 Sept, 2026, 6:24 pm Antonin Houska, <ah@cybertec.at> wrote:
> Osama Abdul Qader <osamaabdulqader.cs@gmail.com> wrote:
>
> > I've updated the patch to address your comments:
> >
> > * Documented that 'REPACK (ANALYZE)' cannot be used inside a transaction
> block, or from a function, procedure or 'DO' block.
> > * Updated the comment in repack.c to clarify that this restriction is
> intentional for now, consistently with VACUUM (FULL, ANALYZE).
>
> In [1] I added a comment explaining why it's a problem to run REPACK
> (ANALYZE)
> from function. I thought it's important so that, when we conclude (in the
> future) that running in block is fine, we still keep checking for execution
> from a function. (PreventInTransactionBlock() checks both at the moment.)
>
> In [2] I was advised to make the comment more precise, but as you appear to
> have taken the patch over, I expected that you'll do that. However, you
> simply
> removed that part of the comment. Can you please explain why?
>
>
> BTW, "top posting" is not the preferred style in this mailing list [3].
>
> [1] https://www.postgresql.org/message-id/49398.1787944525%40localhost
> [2]
> https://www.postgresql.org/message-id/CAHGQGwEezdMUixhJ-N0YO0OFUmh0uPaXRDkds5FS-5dmdwz4Bg%40mail.gma...
> [3] https://wiki.postgresql.org/wiki/Mailing_Lists
>
> --
> Antonin Houska
> Web: https://www.cybertec-postgresql.com
>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-09-05 09:32 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Osama Abdul Qader @ 2026-09-05 09:32 UTC (permalink / raw)
To: Antonin Houska <ah@cybertec.at>; +Cc: Fujii Masao <masao.fujii@gmail.com>; Alvaro Herrera <alvherre@kurilemu.de>; Nathan Bossart <nathandbossart@gmail.com>; pgsql-hackers
>
> In [1] I added a comment explaining why it's a problem to run REPACK
> (ANALYZE)
> from function. I thought it's important so that, when we conclude (in the
> future) that running in block is fine, we still keep checking for execution
> from a function. (PreventInTransactionBlock() checks both at the moment.)
I removed that part because I interpreted the earlier request to make the
comment more precise as a request to remove the explanation and retain only
the current transaction-block restriction.
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.
I've restored this explanation in the comment and kept the
transaction-block restriction explicitly as a current restriction for now.
In [2] I was advised to make the comment more precise, but as you appear to
> have taken the patch over, I expected that you'll do that. However, you
> simply
> removed that part of the comment. Can you please explain why?
The removal was due to my misunderstanding of that feedback. I should have
made the explanation more precise rather than removing it. Sorry about that.
BTW, "top posting" is not the preferred style in this mailing list [3].
Understood. I'll use inline replies going forward.
Also, the regenerated patch has been attached.
On Fri, Sep 4, 2026 at 7:00 PM Osama Abdul Qader <
osamaabdulqader.cs@gmail.com> wrote:
> I removed that path because I interpreted the earlier discussion as asking
> me to avoid claiming that REPACK (ANALYZE) inherently performs transaction
> management, and I replaced it with a shorter comment explaining the current
> restriction.
>
> I now understand your point that the comment should also explain the
> separate restriction on execution from a function/procedure/DO block. In
> particular, even if running REPACK (ANALYZE) inside a transaction block is
> reconsidered in the future, the restriction on execution from a function
> may still need to remain.
>
> I'll update the comment to make that distinction explicit and will also
> follow the mailing-list preferred inline-posting style in future replies.
>
> Thanks for pointing this out.
>
> With Regards,
> Osama Abdul Qader
>
> On Fri, 4 Sept, 2026, 6:24 pm Antonin Houska, <ah@cybertec.at> wrote:
>
>> Osama Abdul Qader <osamaabdulqader.cs@gmail.com> wrote:
>>
>> > I've updated the patch to address your comments:
>> >
>> > * Documented that 'REPACK (ANALYZE)' cannot be used inside a
>> transaction block, or from a function, procedure or 'DO' block.
>> > * Updated the comment in repack.c to clarify that this restriction is
>> intentional for now, consistently with VACUUM (FULL, ANALYZE).
>>
>> In [1] I added a comment explaining why it's a problem to run REPACK
>> (ANALYZE)
>> from function. I thought it's important so that, when we conclude (in the
>> future) that running in block is fine, we still keep checking for
>> execution
>> from a function. (PreventInTransactionBlock() checks both at the moment.)
>>
>> In [2] I was advised to make the comment more precise, but as you appear
>> to
>> have taken the patch over, I expected that you'll do that. However, you
>> simply
>> removed that part of the comment. Can you please explain why?
>>
>>
>> BTW, "top posting" is not the preferred style in this mailing list [3].
>>
>> [1] https://www.postgresql.org/message-id/49398.1787944525%40localhost
>> [2]
>> https://www.postgresql.org/message-id/CAHGQGwEezdMUixhJ-N0YO0OFUmh0uPaXRDkds5FS-5dmdwz4Bg%40mail.gma...
>> [3] https://wiki.postgresql.org/wiki/Mailing_Lists
>>
>> --
>> Antonin Houska
>> Web: https://www.cybertec-postgresql.com
>>
>
Attachments:
[text/x-patch] 0001-reject-repack-analyze-in-transaction.patch (3.6K, ../../CAC+8b5jK_5QegTpjRDchR55_x+raEfBoQKUXK5c3ceW6OsK4Mw@mail.gmail.com/3-0001-reject-repack-analyze-in-transaction.patch)
download | inline diff:
diff --git a/doc/src/sgml/ref/repack.sgml b/doc/src/sgml/ref/repack.sgml
index 0cb72b6b289..d9c7c9e6b95 100644
--- a/doc/src/sgml/ref/repack.sgml
+++ b/doc/src/sgml/ref/repack.sgml
@@ -329,6 +329,8 @@ REPACK [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] USING
<para>
Applies <xref linkend="sql-analyze"/> on the table after repacking. This is
currently only supported when a single (non-partitioned) table is specified.
+ This option cannot be used inside a transaction block, or from a function,
+ procedure, or <command>DO</command> block.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/repack.c b/src/backend/commands/repack.c
index edff54e734e..05ad3ebece7 100644
--- a/src/backend/commands/repack.c
+++ b/src/backend/commands/repack.c
@@ -314,6 +314,18 @@ ExecRepack(ParseState *pstate, RepackStmt *stmt, bool isTopLevel)
PreventInTransactionBlock(isTopLevel, "REPACK (CONCURRENTLY)");
}
+ else if ((params.options & CLUOPT_ANALYZE) != 0)
+ {
+ /*
+ * ANALYZE may start a new transaction in process_single_relation(),
+ * which is not safe while an SPI session is active. Prevent execution
+ * from a function, procedure, or DO block. For now, also prohibit
+ * execution in a transaction block, consistently with VACUUM (FULL,
+ * ANALYZE).
+ */
+ PreventInTransactionBlock(isTopLevel, "REPACK (ANALYZE)");
+ }
+
/*
* If a single relation is specified, process it and we're done ... unless
* the relation is a partitioned table, in which case we fall through.
diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out
index d1bc8a13286..9ad7e2e21c0 100644
--- a/src/test/regress/expected/cluster.out
+++ b/src/test/regress/expected/cluster.out
@@ -796,9 +796,23 @@ ORDER BY 1;
clstr_tst_pkey
(3 rows)
--- Verify partial analyze works
+-- Verify REPACK (ANALYZE) works, including partial analyze.
REPACK (ANALYZE) clstr_tst (a);
REPACK (ANALYZE) clstr_tst;
+-- Plain REPACK is allowed in a transaction block.
+BEGIN;
+REPACK clstr_tst;
+ROLLBACK;
+-- REPACK (ANALYZE) is not allowed in a transaction block.
+BEGIN;
+REPACK (ANALYZE) clstr_tst;
+ERROR: REPACK (ANALYZE) cannot run inside a transaction block
+ROLLBACK;
+-- REPACK (ANALYZE) is not allowed from a function.
+DO $$ BEGIN EXECUTE 'REPACK (ANALYZE) clstr_tst'; END $$;
+ERROR: REPACK (ANALYZE) cannot be executed from a function or procedure
+CONTEXT: SQL statement "REPACK (ANALYZE) clstr_tst"
+PL/pgSQL function inline_code_block line 1 at EXECUTE
REPACK (VERBOSE) clstr_tst (a);
ERROR: ANALYZE option must be specified when a column list is provided
-- REPACK w/o argument performs no ordering, so we can only check which tables
diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sql
index e7a62367adf..dcc91e73698 100644
--- a/src/test/regress/sql/cluster.sql
+++ b/src/test/regress/sql/cluster.sql
@@ -380,9 +380,23 @@ INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail');
SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass
ORDER BY 1;
--- Verify partial analyze works
+-- Verify REPACK (ANALYZE) works, including partial analyze.
REPACK (ANALYZE) clstr_tst (a);
REPACK (ANALYZE) clstr_tst;
+
+-- Plain REPACK is allowed in a transaction block.
+BEGIN;
+REPACK clstr_tst;
+ROLLBACK;
+
+-- REPACK (ANALYZE) is not allowed in a transaction block.
+BEGIN;
+REPACK (ANALYZE) clstr_tst;
+ROLLBACK;
+
+-- REPACK (ANALYZE) is not allowed from a function.
+DO $$ BEGIN EXECUTE 'REPACK (ANALYZE) clstr_tst'; END $$;
+
REPACK (VERBOSE) clstr_tst (a);
-- REPACK w/o argument performs no ordering, so we can only check which tables
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-09-08 10:27 Alvaro Herrera <alvherre@kurilemu.de>
parent: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Alvaro Herrera @ 2026-09-08 10:27 UTC (permalink / raw)
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
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)
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-09-08 12:38 Antonin Houska <ah@cybertec.at>
parent: Alvaro Herrera <alvherre@kurilemu.de>
0 siblings, 1 reply; 25+ messages in thread
From: Antonin Houska @ 2026-09-08 12:38 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@kurilemu.de>; +Cc: Osama Abdul Qader <osamaabdulqader.cs@gmail.com>; Fujii Masao <masao.fujii@gmail.com>; Nathan Bossart <nathandbossart@gmail.com>; pgsql-hackers
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: REPACK (ANALYZE) within transaction block segfaults
@ 2026-09-10 10:14 Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
parent: Antonin Houska <ah@cybertec.at>
0 siblings, 0 replies; 25+ messages in thread
From: Osama Abdul Qader @ 2026-09-10 10:14 UTC (permalink / raw)
To: Antonin Houska <ah@cybertec.at>; +Cc: Alvaro Herrera <alvherre@kurilemu.de>; Fujii Masao <masao.fujii@gmail.com>; Nathan Bossart <nathandbossart@gmail.com>; pgsql-hackers
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@cybertec.at> wrote:
> 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
>
^ permalink raw reply [nested|flat] 25+ messages in thread
end of thread, other threads:[~2026-09-10 10:14 UTC | newest]
Thread overview: 25+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-27 13:56 REPACK (ANALYZE) within transaction block segfaults Nathan Bossart <nathandbossart@gmail.com>
2026-08-27 14:23 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
2026-08-27 16:09 ` Fujii Masao <masao.fujii@gmail.com>
2026-08-27 19:39 ` Nathan Bossart <nathandbossart@gmail.com>
2026-08-28 19:15 ` Antonin Houska <ah@cybertec.at>
2026-08-28 19:52 ` Nathan Bossart <nathandbossart@gmail.com>
2026-08-30 16:22 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
2026-08-30 17:01 ` Nathan Bossart <nathandbossart@gmail.com>
2026-08-31 11:10 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
2026-09-02 05:26 ` Fujii Masao <masao.fujii@gmail.com>
2026-09-02 08:20 ` Alvaro Herrera <alvherre@kurilemu.de>
2026-09-02 08:47 ` Antonin Houska <ah@cybertec.at>
2026-09-02 11:54 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
2026-09-03 04:42 ` Antonin Houska <ah@cybertec.at>
2026-09-03 07:13 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
2026-09-03 08:26 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
2026-09-03 13:50 ` Fujii Masao <masao.fujii@gmail.com>
2026-09-03 16:29 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
2026-09-03 17:35 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
2026-09-04 12:54 ` Antonin Houska <ah@cybertec.at>
2026-09-04 13:30 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
2026-09-05 09:32 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
2026-09-08 10:27 ` Alvaro Herrera <alvherre@kurilemu.de>
2026-09-08 12:38 ` Antonin Houska <ah@cybertec.at>
2026-09-10 10:14 ` Osama Abdul Qader <osamaabdulqader.cs@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox