pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedon 19beta3: repack (concurrently) affects sessions with transaction isolation level repeatable read
3+ messages / 2 participants
[nested] [flat]
* on 19beta3: repack (concurrently) affects sessions with transaction isolation level repeatable read
@ 2026-08-22 07:29 Jochen Bandhauer <jb@jbitc.de>
0 siblings, 1 reply; 3+ messages in thread
From: Jochen Bandhauer @ 2026-08-22 07:29 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hello,
while using repack (concurrently) I found a behavior that I didn't
expect. When running a "repack (concurrently) t;" in one session,
another session with "transaction isolation level repeatable read"
reports sometimes 0 for "select count(*) from t;".
PostgreSQL Version: PostgreSQL 19beta3 on x86_64-pc-linux-gnu, compiled
by gcc (GCC) 14.3.1 20251022 (Red Hat 14.3.1-4), 64-bit
configure command: ./configure --prefix=/home/postgres/pginstall
OS: Red Hat Enterprise Linux 10.2 (Coughlan)
uname -a: Linux lin8.fritz.box 6.12.0-211.44.1.el10_2.x86_64 #1 SMP
PREEMPT_DYNAMIC Tue Aug 4 03:10:19 EDT 2026 x86_64 GNU/Linux
Test Case:
-- create table
drop table if exists t;
create table t (
col1 bigint primary key,
col2 text default 'default text'
);
insert into t (col1) select g from generate_series(1, 10000) as g;
Session 1 runs from time to time: repack (concurrently) t;
Session 2 runs the following script with: while true; do psql mydb -f
./s2.sql; sleep 0.1; done| grep -v "^$"
Script s2.sql:
\pset tuples_only on
\o /dev/null
begin;
set transaction isolation level repeatable read;
select 1;
select pg_sleep(1);
\o
select count(*) from t;
\o /dev/null
end;
Output Session 2:
10000
10000
0
10000
10000
0
10000
10000
10000
0
10000
10000
0
10000
0 always when Session 1 runs: repack (concurrently) t;
Expected Output: Always 10000
Only the (concurrently) option shows this behavior. Wehen using "repack
t;" or "repack t using index t_pkey;" the output is always 10000.
Thanks
Jochen
--
Mit freundlichen Grüßen
Jochen Bandhauer
+49 (0)176 29390012
www.jbitc.de
Attachments:
jb.vcf (text/vcard, 202B) download
BEGIN:VCARD
VERSION:4.0
N:Bandhauer;Jochen;;;
FN:Jochen Bandhauer
EMAIL;PREF=1;TYPE=work:jb@jbitc.de
URL:https://www.jbitc.de
TEL;TYPE=work;VALUE=TEXT:+4917629390012
TZ:Europe/Berlin
END:VCARD
* Re: on 19beta3: repack (concurrently) affects sessions with transaction isolation level repeatable read
@ 2026-08-22 19:45 Peter Geoghegan <pg@bowt.ie>
parent: Jochen Bandhauer <jb@jbitc.de>
0 siblings, 1 reply; 3+ messages in thread
From: Peter Geoghegan @ 2026-08-22 19:45 UTC (permalink / raw)
To: Jochen Bandhauer <jb@jbitc.de>; +Cc: pgsql-bugs@lists.postgresql.org
On Sat, Aug 22, 2026 at 3:29 AM Jochen Bandhauer <jb@jbitc.de> wrote:
> while using repack (concurrently) I found a behavior that I didn't
> expect. When running a "repack (concurrently) t;" in one session,
> another session with "transaction isolation level repeatable read"
> reports sometimes 0 for "select count(*) from t;".
This is a documented behavior; "repack concurrently" is allowed to
violate MVCC. There's a warning box about this in the docs for the
repack command [1], which notes "REPACK with the CONCURRENTLY option
is not MVCC-safe, see Section 13.6 for details".
[1] https://www.postgresql.org/docs/devel/sql-repack.html
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: on 19beta3: repack (concurrently) affects sessions with transaction isolation level repeatable read
@ 2026-08-23 09:31 Jochen Bandhauer <jb@jbitc.de>
parent: Peter Geoghegan <pg@bowt.ie>
0 siblings, 0 replies; 3+ messages in thread
From: Jochen Bandhauer @ 2026-08-23 09:31 UTC (permalink / raw)
To: Peter Geoghegan <pg@bowt.ie>; +Cc: pgsql-bugs@lists.postgresql.org
On 22.08.2026 21:45, Peter Geoghegan wrote:
> On Sat, Aug 22, 2026 at 3:29 AM Jochen Bandhauer <jb@jbitc.de> wrote:
>> while using repack (concurrently) I found a behavior that I didn't
>> expect. When running a "repack (concurrently) t;" in one session,
>> another session with "transaction isolation level repeatable read"
>> reports sometimes 0 for "select count(*) from t;".
> This is a documented behavior; "repack concurrently" is allowed to
> violate MVCC. There's a warning box about this in the docs for the
> repack command [1], which notes "REPACK with the CONCURRENTLY option
> is not MVCC-safe, see Section 13.6 for details".
>
> [1] https://www.postgresql.org/docs/devel/sql-repack.html
Hello,
thanks, I haven't seen that. I also read that this might change in the
future [1]. After some further reading I applied patch [2] that worked
for the test case (The second session always showed the correct number
of rows).
Jochen
[1]
https://www.postgresql.org/message-id/202504040733.ysuy5gad55md%40alvherre.pgsql
[2] https://commitfest.postgresql.org/patch/6957/
--
Mit freundlichen Grüßen
Jochen Bandhauer
+49 (0)176 29390012
www.jbitc.de
Attachments:
jb.vcf (text/vcard, 202B) download
BEGIN:VCARD
VERSION:4.0
N:Bandhauer;Jochen;;;
FN:Jochen Bandhauer
EMAIL;PREF=1;TYPE=work:jb@jbitc.de
URL:https://www.jbitc.de
TEL;TYPE=work;VALUE=TEXT:+4917629390012
TZ:Europe/Berlin
END:VCARD
end of thread, other threads:[~2026-08-23 09:31 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-22 07:29 on 19beta3: repack (concurrently) affects sessions with transaction isolation level repeatable read Jochen Bandhauer <jb@jbitc.de>
2026-08-22 19:45 ` Peter Geoghegan <pg@bowt.ie>
2026-08-23 09:31 ` Jochen Bandhauer <jb@jbitc.de>
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