pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17389: pg_repack creates race conditions on streaming replicas
5+ messages / 4 participants
[nested] [flat]
* BUG #17389: pg_repack creates race conditions on streaming replicas
@ 2022-01-31 16:11 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 5+ messages in thread
From: PG Bug reporting form @ 2022-01-31 16:11 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: bench@silentmedia.com
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17389: pg_repack creates race conditions on streaming replicas
@ 2022-01-31 21:54 Andres Freund <andres@anarazel.de>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 5+ messages in thread
From: Andres Freund @ 2022-01-31 21:54 UTC (permalink / raw)
To: bench@silentmedia.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17389: pg_repack creates race conditions on streaming replicas
@ 2022-02-01 08:43 Nick Cleaton <nick@cleaton.net>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 5+ messages in thread
From: Nick Cleaton @ 2022-02-01 08:43 UTC (permalink / raw)
To: bench@silentmedia.com; pgsql-bugs@lists.postgresql.org
On Mon, 31 Jan 2022 at 16:11, PG Bug reporting form <noreply@postgresql.org>
wrote:
> 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.
>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17389: pg_repack creates race conditions on streaming replicas
@ 2022-02-01 15:25 Ben Chobot <bench@silentmedia.com>
parent: Nick Cleaton <nick@cleaton.net>
0 siblings, 0 replies; 5+ messages in thread
From: Ben Chobot @ 2022-02-01 15:25 UTC (permalink / raw)
To: Nick Cleaton <nick@cleaton.net>; +Cc: pgsql-bugs@lists.postgresql.org
Nick Cleaton wrote on 2/1/22 12:43 AM:
> 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.
Oh it most certainly did. (We validated it in other testing, and the
test we ran had pg_repack only working on indices anyway.)
It's good to hear you don't see any such issues using SQL commands. Have
you tried using a simple REINDEX CONCURRENTLY?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17389: pg_repack creates race conditions on streaming replicas
@ 2022-02-01 21:28 Ben Chobot <bench@silentmedia.com>
parent: Andres Freund <andres@anarazel.de>
0 siblings, 0 replies; 5+ messages in thread
From: Ben Chobot @ 2022-02-01 21:28 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2022-02-01 21:28 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-01-31 16:11 BUG #17389: pg_repack creates race conditions on streaming replicas PG Bug reporting form <noreply@postgresql.org>
2022-01-31 21:54 ` Andres Freund <andres@anarazel.de>
2022-02-01 21:28 ` Ben Chobot <bench@silentmedia.com>
2022-02-01 08:43 ` Nick Cleaton <nick@cleaton.net>
2022-02-01 15:25 ` Ben Chobot <bench@silentmedia.com>
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