Thread: BUG #17389: pg_repack creates race conditions on streaming replicas

The following bug has been logged on the website:

Bug reference:      17389
Logged by:          Ben Chobot
Email address:      bench@silentmedia.com
PostgreSQL version: 12.9
Operating system:   Linux (Ubuntu)
Description:

We've noticed that at least since 9.5, running pg_repack causes a race
conditions on our streaming replicas, but _not_ on the primary where
pg_repack is running. This manifests itself as a client briefly unable to
open the relation getting repacked - but, in our testing and experience,
only on the replica. I would blame pg_repack - its whole purpose for being
is to transparently remake tables, and quite possibly it got some of the
details wrong - except that if its behavior appears atomic to clients on the
primary, then surely it should on the replicas too?

Using these steps below, I can reliably get the client on the replica to
have an OID error within 30 minutes. The same steps fail to generate an
error when I query in a loop on the primary.

1. Put some data in a table with a single btree index on a primary db:
create table public.simple_test (id int primary key);
insert into public.simple_test(id) (select generate_series(1,1000));

2. Set up streaming replication to a secondary db.

3. Add pg_repack
create extension pg_repack ;

4. In a loop on the primary, have pg_repack repack the indices of that
table:
while `true`; do psql -d canvas -tAc "select now(),relfilenode from pg_class
where relname='simple_test_pkey'" >> log;
/usr/lib/postgresql/12/bin/pg_repack -d canvas -x -t public.simple_test;
done

5. In a loop on the secondary, have psql query the secondary db for an
indexed value of that table:
while `true`; do psql -tAc "select count(*),now() from simple_test where
id='3'" || break; done; date

Hi,

On 2022-01-31 16:11:15 +0000, PG Bug reporting form wrote:
> We've noticed that at least since 9.5, running pg_repack causes a race
> conditions on our streaming replicas, but _not_ on the primary where
> pg_repack is running. This manifests itself as a client briefly unable to
> open the relation getting repacked - but, in our testing and experience,
> only on the replica.

pg_repack is not part of postgres, so you're going to have to report this to
the developers of pg_repack.


> I would blame pg_repack - its whole purpose for being
> is to transparently remake tables, and quite possibly it got some of the
> details wrong - except that if its behavior appears atomic to clients on the
> primary, then surely it should on the replicas too?

pg_repack modifies catalogs in an unsupported way, so it's not surprising
you'd see some odd effects. Postgres locking and visibility logic doesn't know
about what pg_repack is doing, so it can't enforce correct semantics. I'd
assume that the repack developers haven't gotten the locking logic quite right
someplace.

Greetings,

Andres Freund

We've very occasionally seen something similar with a script that did CREATE INDEX CONCURRENTLY, RENAME INDEX and DROP INDEX CONCURRENTLY on the primary, but not since we upgraded from 9.4 to 12 and switched to using REINDEX CONCURRENTLY. In our case the OID in the error belonged to the index that was dropped, not the table.

I think It'd be worth noting the OIDs of the table and its indexes before each run, so that you can tell if it belongs to an index in your case.

Andres Freund wrote on 1/31/22 1:54 PM:
> pg_repack modifies catalogs in an unsupported way, so it's not surprising
> you'd see some odd effects. Postgres locking and visibility logic doesn't know
> about what pg_repack is doing, so it can't enforce correct semantics. I'd
> assume that the repack developers haven't gotten the locking logic quite right
> someplace.

So that all make sense to me, except that - surprise! - it turns out 
we're seeing the same behavior if we leave pg_repack out of it and just 
do a REINDEX TABLE CONCURRENTLY in a loop on the primary node. Again, 
reliably within 30 minutes, the select on the secondary will fail with 
an OID error, but not on the primary.

Sorry, I should have led with this more vanilla test case, but I didn't 
think to try it till Nick Cleaton reminded me that PG12 brought us 
REINDEX TABLE CONCURRENTLY.