From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Antonin Houska <ah@cybertec.at>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: Mihail Nikalayeu <mihailnikalayeu@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: Amit Kapila <amit.kapila16@gmail.com>
Cc: Srinath Reddy Sadipiralla <srinath2133@gmail.com>
Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org, Robert Treat <rob@xzilla.net>
Subject: Re: Adding REPACK [concurrently]
Date: Wed, 22 Apr 2026 11:30:26 +0200
Message-ID: <aeiU2CZgM4j7zg8N@alvherre.pgsql> (raw)
In-Reply-To: <aeZG8QZKMDGdcnK_@alvherre.pgsql>
On 2026-Apr-20, Alvaro Herrera wrote:
> There's also contrib/test_decoding. It's somewhat vaguely adjacent, and
> the tests aren't _really_ about the decoding part, but of all these
> options, it seems the least bad one.
Here's a patch that does this.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/Attachments:
[text/x-diff] 0001-Move-REPACK-CONCURRENTLY-test-out-of-stock-regressio.patch (8.6K, ../aeiU2CZgM4j7zg8N@alvherre.pgsql/2-0001-Move-REPACK-CONCURRENTLY-test-out-of-stock-regressio.patch)
download | inline diff:
From a64043f31950efa5f63274d6a3e498082ad2b064 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <alvherre@kurilemu.de>
Date: Wed, 22 Apr 2026 11:22:50 +0200
Subject: [PATCH] Move REPACK (CONCURRENTLY) test out of stock regression tests
These tests sometimes run with wal_level=minimal, which does not allow
to run REPACK (CONCURRENTLY). Move them to test_decoding, which is
ensured to run with high enough wal_level.
Discussion: https://postgr.es/m/260901.1776696126@sss.pgh.pa.us
---
contrib/test_decoding/Makefile | 2 +-
contrib/test_decoding/expected/repack.out | 30 +++++++++++++++++++++++
contrib/test_decoding/meson.build | 1 +
contrib/test_decoding/sql/repack.sql | 25 +++++++++++++++++++
src/test/regress/expected/cluster.out | 29 +++-------------------
src/test/regress/sql/cluster.sql | 13 +++-------
6 files changed, 65 insertions(+), 35 deletions(-)
create mode 100644 contrib/test_decoding/expected/repack.out
create mode 100644 contrib/test_decoding/sql/repack.sql
diff --git a/contrib/test_decoding/Makefile b/contrib/test_decoding/Makefileindex acbcaed2feb..0111124399a 100644--- a/contrib/test_decoding/Makefile+++ b/contrib/test_decoding/Makefile@@ -5,7 +5,7 @@ PGFILEDESC = "test_decoding - example of a logical decoding output plugin"
REGRESS = ddl xact rewrite toast permissions decoding_in_xact \
decoding_into_rel binary prepared replorigin time messages \
- spill slot truncate stream stats twophase twophase_stream+ repack spill slot truncate stream stats twophase twophase_stream
ISOLATION = mxact delayed_startup ondisk_startup concurrent_ddl_dml \
oldest_xmin snapshot_transfer subxact_without_top concurrent_stream \
twophase_snapshot slot_creation_error catalog_change_snapshot \
diff --git a/contrib/test_decoding/expected/repack.out b/contrib/test_decoding/expected/repack.outnew file mode 100644index 00000000000..70039d824af--- /dev/null+++ b/contrib/test_decoding/expected/repack.out@@ -0,0 +1,30 @@+-- Test REPACK (CONCURRENTLY).+-- This isn't strictly about decoding, but it involves logical decoding+-- and requires to be run under higher than minimal wal_level, so we can't+-- have it in the main regression test suite.+-- Ownership of partitions is checked+CREATE TABLE ptnowner(i int unique not null) PARTITION BY LIST (i);+CREATE INDEX ptnowner_i_idx ON ptnowner(i);+CREATE TABLE ptnowner1 PARTITION OF ptnowner FOR VALUES IN (1);+CREATE ROLE regress_ptnowner;+CREATE TABLE ptnowner2 PARTITION OF ptnowner FOR VALUES IN (2);+ALTER TABLE ptnowner1 OWNER TO regress_ptnowner;+SET SESSION AUTHORIZATION regress_ptnowner;+ALTER TABLE ptnowner1 REPLICA IDENTITY USING INDEX ptnowner1_i_key;+REPACK (CONCURRENTLY) ptnowner1;+RESET SESSION AUTHORIZATION;+ALTER TABLE ptnowner OWNER TO regress_ptnowner;+CREATE TEMP TABLE ptnowner_oldnodes AS+ SELECT oid, relname, relfilenode FROM pg_partition_tree('ptnowner') AS tree+ JOIN pg_class AS c ON c.oid=tree.relid;+SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a+ JOIN ptnowner_oldnodes b USING (oid) ORDER BY a.relname COLLATE "C";+ relname | ?column? +-----------+----------+ ptnowner | t+ ptnowner1 | t+ ptnowner2 | t+(3 rows)++DROP TABLE ptnowner;+DROP ROLE regress_ptnowner;diff --git a/contrib/test_decoding/meson.build b/contrib/test_decoding/meson.buildindex cf5b74cf1ab..ac655853d26 100644--- a/contrib/test_decoding/meson.build+++ b/contrib/test_decoding/meson.build@@ -34,6 +34,7 @@ tests += {
'replorigin',
'time',
'messages',
+ 'repack',
'spill',
'slot',
'truncate',
diff --git a/contrib/test_decoding/sql/repack.sql b/contrib/test_decoding/sql/repack.sqlnew file mode 100644index 00000000000..bf66bb441ac--- /dev/null+++ b/contrib/test_decoding/sql/repack.sql@@ -0,0 +1,25 @@+-- Test REPACK (CONCURRENTLY).+-- This isn't strictly about decoding, but it involves logical decoding+-- and requires to be run under higher than minimal wal_level, so we can't+-- have it in the main regression test suite.+++-- Ownership of partitions is checked+CREATE TABLE ptnowner(i int unique not null) PARTITION BY LIST (i);+CREATE INDEX ptnowner_i_idx ON ptnowner(i);+CREATE TABLE ptnowner1 PARTITION OF ptnowner FOR VALUES IN (1);+CREATE ROLE regress_ptnowner;+CREATE TABLE ptnowner2 PARTITION OF ptnowner FOR VALUES IN (2);+ALTER TABLE ptnowner1 OWNER TO regress_ptnowner;+SET SESSION AUTHORIZATION regress_ptnowner;+ALTER TABLE ptnowner1 REPLICA IDENTITY USING INDEX ptnowner1_i_key;+REPACK (CONCURRENTLY) ptnowner1;+RESET SESSION AUTHORIZATION;+ALTER TABLE ptnowner OWNER TO regress_ptnowner;+CREATE TEMP TABLE ptnowner_oldnodes AS+ SELECT oid, relname, relfilenode FROM pg_partition_tree('ptnowner') AS tree+ JOIN pg_class AS c ON c.oid=tree.relid;+SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a+ JOIN ptnowner_oldnodes b USING (oid) ORDER BY a.relname COLLATE "C";+DROP TABLE ptnowner;+DROP ROLE regress_ptnowner;diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.outindex 71270134985..b767316cf6b 100644--- a/src/test/regress/expected/cluster.out+++ b/src/test/regress/expected/cluster.out@@ -552,8 +552,6 @@ ALTER TABLE ptnowner1 OWNER TO regress_ptnowner;
SET SESSION AUTHORIZATION regress_ptnowner;
CLUSTER ptnowner USING ptnowner_i_idx;
ERROR: permission denied for table ptnowner
-ALTER TABLE ptnowner1 REPLICA IDENTITY USING INDEX ptnowner1_i_key;-REPACK (CONCURRENTLY) ptnowner1;
RESET SESSION AUTHORIZATION;
ALTER TABLE ptnowner OWNER TO regress_ptnowner;
CREATE TEMP TABLE ptnowner_oldnodes AS
@@ -562,30 +560,7 @@ CREATE TEMP TABLE ptnowner_oldnodes AS
SET SESSION AUTHORIZATION regress_ptnowner;
CLUSTER ptnowner USING ptnowner_i_idx;
WARNING: permission denied to execute CLUSTER on "ptnowner2", skipping it
--- still can't repack without a replica identity-ALTER TABLE ptnowner1 REPLICA IDENTITY DEFAULT;-REPACK (CONCURRENTLY) ptnowner1;-ERROR: cannot process relation "ptnowner1"-HINT: Relation "ptnowner1" has no identity index.
RESET SESSION AUTHORIZATION;
-SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a- JOIN ptnowner_oldnodes b USING (oid) ORDER BY a.relname COLLATE "C";- relname | ?column? ------------+----------- ptnowner | t- ptnowner1 | f- ptnowner2 | t-(3 rows)--SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a- JOIN ptnowner_oldnodes b USING (oid) ORDER BY a.relname COLLATE "C";- relname | ?column? ------------+----------- ptnowner | t- ptnowner1 | f- ptnowner2 | t-(3 rows)-
DROP TABLE ptnowner;
DROP ROLE regress_ptnowner;
-- Test CLUSTER with external tuplesorting
@@ -731,6 +706,10 @@ COMMIT;
--
-- REPACK
--
+-- Note we cannot test working REPACK (CONCURRENTLY) here, because the+-- tests could be run with wal_level=minimal, so those tests are+-- elsewhere.+--
----------------------------------------------------------------------
-- REPACK handles individual tables identically to CLUSTER, but it's worth
-- checking if it handles table hierarchies identically as well.
diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sqlindex 6746236ffec..a22c75282ee 100644--- a/src/test/regress/sql/cluster.sql+++ b/src/test/regress/sql/cluster.sql@@ -262,8 +262,6 @@ CREATE TABLE ptnowner2 PARTITION OF ptnowner FOR VALUES IN (2);
ALTER TABLE ptnowner1 OWNER TO regress_ptnowner;
SET SESSION AUTHORIZATION regress_ptnowner;
CLUSTER ptnowner USING ptnowner_i_idx;
-ALTER TABLE ptnowner1 REPLICA IDENTITY USING INDEX ptnowner1_i_key;-REPACK (CONCURRENTLY) ptnowner1;
RESET SESSION AUTHORIZATION;
ALTER TABLE ptnowner OWNER TO regress_ptnowner;
CREATE TEMP TABLE ptnowner_oldnodes AS
@@ -271,14 +269,7 @@ CREATE TEMP TABLE ptnowner_oldnodes AS
JOIN pg_class AS c ON c.oid=tree.relid;
SET SESSION AUTHORIZATION regress_ptnowner;
CLUSTER ptnowner USING ptnowner_i_idx;
--- still can't repack without a replica identity-ALTER TABLE ptnowner1 REPLICA IDENTITY DEFAULT;-REPACK (CONCURRENTLY) ptnowner1;
RESET SESSION AUTHORIZATION;
-SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a- JOIN ptnowner_oldnodes b USING (oid) ORDER BY a.relname COLLATE "C";-SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a- JOIN ptnowner_oldnodes b USING (oid) ORDER BY a.relname COLLATE "C";
DROP TABLE ptnowner;
DROP ROLE regress_ptnowner;
@@ -346,6 +337,10 @@ COMMIT;
--
-- REPACK
--
+-- Note we cannot test working REPACK (CONCURRENTLY) here, because the+-- tests could be run with wal_level=minimal, so those tests are+-- elsewhere.+--
----------------------------------------------------------------------
-- REPACK handles individual tables identically to CLUSTER, but it's worth
--
2.47.3
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@alvh.no-ip.org, tgl@sss.pgh.pa.us, ah@cybertec.at, pryzby@telsasoft.com, mihailnikalayeu@gmail.com, andres@anarazel.de, amit.kapila16@gmail.com, srinath2133@gmail.com, boekewurm+postgres@gmail.com, rob@xzilla.net
Subject: Re: Adding REPACK [concurrently]
In-Reply-To: <aeiU2CZgM4j7zg8N@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox