agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: 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: Mon, 20 Apr 2026 13:45:35 +0200
Message-ID: <aeYQwx9WwD-g-Fc9@alvherre.pgsql> (raw)
In-Reply-To: <22338.1776671256@localhost>
On 2026-Apr-20, Antonin Houska wrote:
> Antonin Houska <ah@cybertec.at> wrote:
>
> > It was discussed earlier [1] and the concerns about possibly excessive
> > resource consumptions were addressed by [2]. So I think it the fix was just
> > forgotten. Attached here.
>
> Sorry, I attached wrong patch. This is what I meant.
Yeah, I had also written the same patch a couple of days ago.
BTW I ran into a small problem after adding some tests in cluster.sql
that would exercise this -- that test would die more or less randomly
but frequently in CI (which it never did in my laptop) because of the
size of the snapshot,
ALTER TABLE ptnowner1 REPLICA IDENTITY USING INDEX ptnowner1_i_key;
REPACK (CONCURRENTLY) ptnowner1;
+ERROR: initial slot snapshot too large
+CONTEXT: REPACK decoding worker
RESET SESSION AUTHORIZATION;
I think the solution for this is to move cluster to a separate parallel
test. The one where it is now is a bit too crowded. Maybe the one for
compression is okay? I'll test and push if I see it passing CI.
Another obvious thing after adding tests is that the LOGIN privilege is
required, which is also quite bogus IMO. 0002 here should solve that.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"If you want to have good ideas, you must have many ideas. Most of them
will be wrong, and what you have to learn is which ones to throw away."
(Linus Pauling)
Attachments:
[text/x-diff] 0001-REPACK-do-not-require-the-user-to-have-REPLICATION.patch (6.2K, ../aeYQwx9WwD-g-Fc9@alvherre.pgsql/2-0001-REPACK-do-not-require-the-user-to-have-REPLICATION.patch)
download | inline diff:
From b3d4158356f4914d2b0cba86eef6994c0ee50ab9 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <alvherre@kurilemu.de>
Date: Mon, 20 Apr 2026 11:38:48 +0200
Subject: [PATCH 1/2] REPACK: do not require the user to have REPLICATION
Although REPACK (CONCURRENTLY) uses replication slots, there is no
concern that the slot will leak data of other users, because the
MAINTAIN privilege on the table is required anyway; requiring
REPLICATION is user-unfriendly without providing any actual protection.
A related aspect is that the REPLICATION attribute is not needed to
prevent REPACK from stealing slots from logical replication, since
commit e76d8c749c31 made REPACK use a separate pool of replication
slots.
Because there are now successful concurrent repack runs in the
regression tests, we're forced to run test_plan_advice under
wal_level=replica.
Author: Antonin Houska <ah@cybertec.at>
Reported-by: Justin Pryzby <pryzby@telsasoft.com>
Reviewed-by: Chao Li <lic@highgo.com>
Discussion: https://postgr.es/m/aeJHPNmL4vVy3oPw@pryzbyj2023
---
src/backend/commands/repack_worker.c | 1 -
.../test_plan_advice/t/001_replan_regress.pl | 1 +
src/test/regress/expected/cluster.out | 20 +++++++++++++++++--
src/test/regress/sql/cluster.sql | 11 ++++++++--
4 files changed, 28 insertions(+), 5 deletions(-)
diff --git a/src/backend/commands/repack_worker.c b/src/backend/commands/repack_worker.c
index b17edd771e2..e4a4860805b 100644
--- a/src/backend/commands/repack_worker.c
+++ b/src/backend/commands/repack_worker.c
@@ -214,7 +214,6 @@ repack_setup_logical_decoding(Oid relid)
/*
* Make sure we can use logical decoding.
*/
- CheckSlotPermissions();
CheckLogicalDecodingRequirements(true);
/*
diff --git a/src/test/modules/test_plan_advice/t/001_replan_regress.pl b/src/test/modules/test_plan_advice/t/001_replan_regress.pl
index 38ffa4d11ae..452b179a665 100644
--- a/src/test/modules/test_plan_advice/t/001_replan_regress.pl
+++ b/src/test/modules/test_plan_advice/t/001_replan_regress.pl
@@ -18,6 +18,7 @@ $node->init();
# Set up our desired configuration.
$node->append_conf('postgresql.conf', <<EOM);
shared_preload_libraries='test_plan_advice'
+wal_level=replica
pg_plan_advice.always_explain_supplied_advice=false
pg_plan_advice.feedback_warnings=true
EOM
diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out
index 6127b215a86..e17bc91fae1 100644
--- a/src/test/regress/expected/cluster.out
+++ b/src/test/regress/expected/cluster.out
@@ -543,15 +543,17 @@ ERROR: REPACK (CONCURRENTLY) is not supported for partitioned tables
HINT: Consider running the command on individual partitions.
DROP TABLE clstrpart;
-- Ownership of partitions is checked
-CREATE TABLE ptnowner(i int unique) PARTITION BY LIST (i);
+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 ROLE regress_ptnowner LOGIN;
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;
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
@@ -560,6 +562,11 @@ 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";
@@ -570,6 +577,15 @@ SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a
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
diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sql
index d14063a9683..1f471a8821a 100644
--- a/src/test/regress/sql/cluster.sql
+++ b/src/test/regress/sql/cluster.sql
@@ -254,14 +254,16 @@ REPACK (CONCURRENTLY) clstrpart;
DROP TABLE clstrpart;
-- Ownership of partitions is checked
-CREATE TABLE ptnowner(i int unique) PARTITION BY LIST (i);
+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 ROLE regress_ptnowner LOGIN;
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
@@ -269,7 +271,12 @@ 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;
--
2.47.3
[text/x-diff] 0002-REPACK-do-not-require-LOGIN-privileges.patch (2.6K, ../aeYQwx9WwD-g-Fc9@alvherre.pgsql/3-0002-REPACK-do-not-require-LOGIN-privileges.patch)
download | inline diff:
From 99259ca5410389867228d15bd37ab88e6c8844f2 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <alvherre@kurilemu.de>
Date: Mon, 20 Apr 2026 13:19:49 +0200
Subject: [PATCH 2/2] REPACK: do not require LOGIN privileges
Normally, starting a background worker does require LOGIN, which is
fine. However, the bgworker used for REPACK has no business requiring
it. It's just user-unfriendly and prevents repacking tables
comfortably.
---
src/backend/commands/repack_worker.c | 5 +++--
src/test/regress/expected/cluster.out | 2 +-
src/test/regress/sql/cluster.sql | 2 +-
3 files changed, 5 insertions(+), 4 deletions(-)
diff --git a/src/backend/commands/repack_worker.c b/src/backend/commands/repack_worker.c
index e4a4860805b..c40f8c98e06 100644
--- a/src/backend/commands/repack_worker.c
+++ b/src/backend/commands/repack_worker.c
@@ -106,8 +106,9 @@ RepackWorkerMain(Datum main_arg)
pq_set_parallel_leader(shared->backend_pid,
shared->backend_proc_number);
- /* Connect to the database. */
- BackgroundWorkerInitializeConnectionByOid(shared->dbid, shared->roleid, 0);
+ /* Connect to the database. LOGIN is not required. */
+ BackgroundWorkerInitializeConnectionByOid(shared->dbid, shared->roleid,
+ BGWORKER_BYPASS_ROLELOGINCHECK);
/*
* Transaction is needed to open relation, and it also provides us with a
diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out
index e17bc91fae1..71270134985 100644
--- a/src/test/regress/expected/cluster.out
+++ b/src/test/regress/expected/cluster.out
@@ -546,7 +546,7 @@ DROP TABLE clstrpart;
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 LOGIN;
+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;
diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sql
index 1f471a8821a..6746236ffec 100644
--- a/src/test/regress/sql/cluster.sql
+++ b/src/test/regress/sql/cluster.sql
@@ -257,7 +257,7 @@ DROP TABLE clstrpart;
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 LOGIN;
+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;
--
2.47.3
view thread (415+ messages) latest in thread
Message-ID: <aeYQwx9WwD-g-Fc9@alvherre.pgsql>
Permalink: ../aeYQwx9WwD-g-Fc9@alvherre.pgsql/
Also on: postgresql.org/message-id/aeYQwx9WwD-g-Fc9@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@alvh.no-ip.org, 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: <aeYQwx9WwD-g-Fc9@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