| From: | Thom Brown <thom(at)linux(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | REPACK (CONCURRENTLY) can lose data in pg_dump output |
| Date: | 2026-09-25 16:28:12 |
| Message-ID: | CAA-aLv57B5g3aiGodzkuePa8-7KSQz7_5rspbPDY-7XPCBz0Tw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi,
If pg_dump starts while REPACK (CONCURRENTLY) is waiting for its final
lock on a table, it dumps that table with no rows, and still exits
successfully:
CREATE TABLE dumptest (id int PRIMARY KEY, v text);
INSERT INTO dumptest SELECT x, 'row' || x FROM generate_series(1,1000) x;
Session 1:
BEGIN;
SELECT count(*) FROM dumptest;
Session 2:
REPACK (CONCURRENTLY) dumptest; -- waits for session 1
Terminal:
pg_dump -t dumptest --data-only -- waits behind REPACK
Session 1:
COMMIT;
The dump then contains:
COPY public.dumptest (id, v) FROM stdin;
\.
Using VACUUM FULL or plain REPACK in session 2 instead dumps all 1000 rows.
I realise REPACK (CONCURRENTLY) is documented as not being MVCC-safe,
and this is just demonstrating that, but the pg_dump docs don't
mention it, and as REPACK (CONCURRENTLY) is intended to be run while
the database is in use, it seems quite likely to coincide with a
backup at some point, and nothing indicates that the backup is missing
the table's data.
Should this at least be documented on the pg_dump page, or could
pg_dump detect that a table has been rewritten since its snapshot was
taken and fail instead?
Regards
Thom
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Rui Zhao | 2026-09-25 16:43:19 | Re: updates for handling optional argument in system functions |
| Previous Message | Rui Zhao | 2026-09-25 16:18:00 | Re: Persist slot invalidations before publishing them |