on 19beta3: repack (concurrently) affects sessions with transaction isolation level repeatable read

pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed

on 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>
  2026-08-22 19:45 ` Re: on 19beta3: repack (concurrently) affects sessions with transaction isolation level repeatable read Peter Geoghegan <pg@bowt.ie>
  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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: on 19beta3: repack (concurrently) affects sessions with transaction isolation level repeatable read
  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   ` Re: on 19beta3: repack (concurrently) affects sessions with transaction isolation level repeatable read 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-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 ` Re: on 19beta3: repack (concurrently) affects sessions with transaction isolation level repeatable read Peter Geoghegan <pg@bowt.ie>
@ 2026-08-23 09:31   ` Jochen Bandhauer <jb@jbitc.de>
  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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

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