On Wed, Oct 6, 2021 at 2:28 PM Pavel Borisov <pashkin.e...@gmail.com> wrote: >> It is the most consistent with the general design of the system, for >> reasons that are pretty deeply baked into the system. I'm reminded of >> the fact that REINDEX CONCURRENTLY's completion became blocked due to >> similar trepidations. Understandably so. > > > I may mistake, but I recall the fact that all indexes builds started during > some other (long) index build do not finish with indexes usable for selects > until that long index is built. This may and may not be a source of amcheck > misbehavior. Just a note what could be possibly considered.
I may have been unclear. I meant that work on the REINDEX CONCURRENTLY feature (several years ago) was very difficult. It seemed to challenge what "Postgres relation" really means. Various community members had concerns about the definition at the time. Remember, plain REINDEX actually gets a full AccessExclusiveLock on the target index relation. This is practically as bad as getting the same lock on the table itself for most users -- which is very disruptive indeed. It's much more disruptive than plain CREATE INDEX -- CREATE INDEX generally only blocks write DML. Whereas REINDEX tends to block both writes and reads (in practice, barring some narrow cases with prepared statements that are too confusing to users to be worth discussing). Which is surprising in itself to users. Why should plain REINDEX be so different to plain CREATE INDEX? The weird (but also helpful) thing about the implementation of REINDEX CONCURRENTLY is that we can have *two* pg_class entries for what the user thinks of as one index/relation. Having two pg_class entries is also why plain REINDEX had problems that plain CREATE INDEX never had -- having only one pg_class entry was actually the true underlying problem, all along. Sometimes we have to make a difficult choice between "weird rules but nice behavior" (as with REINDEX CONCURRENTLY), and "nice rules but weird behavior" (as with plain REINDEX). -- Peter Geoghegan
