Open aristocrates opened 5 months ago
I have not noticed this on all indexes, just on a handful. I would be surprised if this happened every time an update, insert, or delete replicated over while the concurrent index creation was happening, since I do not recall running into this on other DB upgrades between the same postgres versions, but the behavior replicated when I retried index creation on these particular indexes, even after re-initializing the subscriber from scratch on a new machine.
I don't have a minimal repro for this right now, but the symptom I have observed on a subscriber that I am syncing for an upgrade is:
CREATE UNIQUE INDEX CONCURRENTLY [options];
on the subscriber results in the subscription goingstatus: down
with a log line similar to the following in the CSV log:In this particular example, 105657687 is indeed the result of calling
pg_relation_filenode($INDEX_NAME)
on the currently-INVALID$INDEX_NAME
that is being created concurrently at the time the subscription goes down:The subscriber is on postgres 15.6 and pglogical 2.4.4
A workaround that seems to work fine is to just create the index non-concurrently, in which case pglogical apply on the subscriber blocks on obtaining a RowExclusiveLock until the create index finishes, and the replication slot stays put on the provider.
Based on https://github.com/2ndQuadrant/pglogical/issues/344#issuecomment-927420890 (and other guides around using pglogical on DBs with large tables) the expected behavior is for
CREATE INDEX CONCURRENTLY
to work fine on the subscriber: