Important PostgreSQL 14 update to avoid silent corruption of indexes

This corruption can only occur _during_ a (re)index with CONCURRENTLY specified, on rows that are modified during the reindex operation, and only for that index.

No other indexes are impacted, and an index can only be impacted when the updates on the table don't update indexed columns.

Nevertheless, if you frequently run CIC, you could be having this issue -- right now you can detect the issue with amcheck, and fix it with a non-concurrent REINDEX of the index (yes, this locks the table).

Alternatively (not listed in the blog post, but possible if you can't afford table locks), you should be able to safely CIC (without corruption) by doing manual snapshot control while concurrently reindexing the index (takes 3 database sessions):

In session 1 start a REPEATABLE READ read-only transaction. In session 2, start the concurrent (re)index. In session 3, monitor pg_stat_progress_create_index for session 2 to get to a 'waiting for ...' phase.

When you detect the (re)index session arrives in a waiting phase, start a REPEATABLE READ transaction in this session, and then halt (commit or rollback doesn't matter) the transaction in session 1. Now, we switch roles of sessions 1 and 3, and repeat this lock-step while the session that is (re)indexing hasn't completed.

Re: Important PostgreSQL 14 update to avoid silent corruption of indexes

#6

Post by msk20 » Thu, Jun 09, 2022, 11:39 PM UTC

Looking at the file with changes https://github.com/postgres/postgres/blob/master/src/backend... , I have to say this source code repository is so well documented/commented and structured, I really gives you a huge trust in postgres to be used in your stack.

Re: Important PostgreSQL 14 update to avoid silent corruption of indexes

#7

Post by mattashii » Thu, Jun 09, 2022, 11:45 PM UTC

mattashii wrote: post #5

It is noted in the post, I'll repeat it to be clear: This corruption can only occur _during_ a (re)index with CONCURRENTLY specified, on rows that are modified during the reindex operation, and only for that index. No other indexes are impacted, and an index can only be impacted when the updates on the table don't update indexed columns. Nevertheless, if you frequently run CIC, you could be having this issue -- right…

I'll note that this process is basically keeping a snapshot that ensures that the data CIC/RIC needs is not cleaned up during the critical phases of the reindex process by keeping the cleanup horizon at a point where CIC/RIC can see all the versions of the rows that it needs to create the full index.

For PG14 the maintaining of that horizon was disabled for backends that run CIC/RIC, resulting in this bug. If you manually keep that horizon from moving using other backends on the same database, the old versions that CIC/RIC expects will not be removed, and thus no corruption will occur.

Re: Important PostgreSQL 14 update to avoid silent corruption of indexes

#8

Post by [deleted] » Thu, Jun 09, 2022, 11:50 PM UTC

[deleted]

Re: Important PostgreSQL 14 update to avoid silent corruption of indexes

#9

Post by boiler_up800 » Fri, Jun 10, 2022, 12:06 AM UTC

It’s impressive that this is a single revert. That speaks to how the development of Postgres is done atomically.

Also not surprised to see it was the EDB team with the expertise to fix it. Their model is a little outdated but they have a lot of experts working there.

Re: Important PostgreSQL 14 update to avoid silent corruption of indexes

#10

Post by colordrops » Fri, Jun 10, 2022, 12:08 AM UTC

msk20 wrote: post #6

Looking at the file with changes https://github.com/postgres/postgres/blob/master/src/backend... , I have to say this source code repository is so well documented/commented and structured, I really gives you a huge trust in postgres to be used in your stack.

File could definitely be broken up a bit. Over 5000 lines! Just a nitpick though.