9.5.2 update release and corrupt indexes

We've released an off-schedule update release today, because of a bug in one of 9.5's features which has forced us to partially disable the feature.  This is, obviously, not the sort of thing we do lightly.

One of the performance features in 9.5 was an optimization which speeded up sorts across the board for text and numeric values, contributed by Peter Geoghegan.  This was an awesome feature which speeded up sorts across the board by 50% to 2000%, and since databases do a lot of sorting, was an overall speed increase for PostgreSQL.  It was especially effective in speeding up index builds.

That feature depends on a built-in function in glibc, strxfrm(), which could be used to create a sortable hash of strings.  Now, the POSIX standard says that strxfrm() + strcmp() should produce sorting results identical to the strcoll() function.  And in our general tests, it did.

However, there are dozens of versions of glibc in the field, and hundreds of collations, and it's computationally unreasonable to test all combinations.  Which is how we missed the problem until a user reported it.  It turns out that for certain releases of glibc (particularly anything before 2.22 on Linux or BSD), with certain collations, strxfrm() and strcoll() return different results due to bugs.  Which can result in an index lookup failing to find rows which are actually there.  In the bug report, for example, an index on a German text column on RedHat Enterprise Linux 6.5 would fail to find many rows in a "between" search.

As a result, we've disabled the feature in 9.5.2 for all indexes which are on collations other than the simplified "C" collation.  This sucks.

Also, if you're on 9.5.0 or 9.5.1 and you have indexes on columns with real collations (i.e. not "C" collation), then you should REINDEX (or CREATE CONCURRENTLY + DROP CONCURRENTLY) each of those indexes.  Which really sucks.

Of course we're discussing ways to bring back the feature, but nobody has a solution yet. In the meantime, you can read more about the problem on the wiki page.

Determining your danger of multixact wraparound corruption

The PostgreSQL Project released an update on May 22, and the only really important fix in that update was a patch to prevent data corruption due to "Multixact member wraparound".  However,  it turns out that there are some issues with that update release, including one with file permissions, and potentially another one with Multixact truncation.  As a result, users are feeling justifiably reluctant to apply our last update release.

If there's a lesson for the project in this, it's "don't wrap an update release the same week as Feature Freeze".

Anyway, given this it would be good for users to determine more definitively if they are in actual danger of Multixact member wraparound (MMW hereafter), so that they know if they need to apply the update right away despite issues.  Here's how to do that:

1. navigate to the PostgreSQL data directory on your server(s), e.g. "/var/lib/postgresql/9.3/main"
2. switch to the subdirectory "pg_multixact/members" 
3. count the number of files in this directory, e.g. "ls -l | wc -l"

If you have a few dozen, hundred, or even a few thousand multixact member files, you're fine.  If you have over 10,000, and that number increases rapidly, then you are potentially in danger of MMW, and should apply the update sooner rather than later. 

Of course, there are other fixes in this latest update, and if one of them specifically affects you, you may have updated already.

Thank you to Thomas Munro for pointing out the simple way to determine this and giving me background material on the fixes.

Note: the above is my personal advice, and has not been approved by the PostgreSQL project, core team, or PostgreSQL Experts Inc. The PostgreSQL project generally advises applying all updates promptly.

Postgres Update Release, Data Loss, and a Million Monkeys

One of the major bugfixes in the most recent PostgreSQL update release (9.1.3 et. al.) is one of those annoying timing-based issues which you'd expect to be very rare in production.  And it is rare, as measured against the total number of inserts you do in a database.  Unfortunately, it's also a "million monkeys" issue, which means that it's not that rare as measured by the number of databases in which it occurs.   For that reason, I'd urge all users to update their PostgreSQL releases as soon as they can.  Let me explain.

The issue, as I understand it, was that autovaccum has been (since 8.3.0) re-using a pointer for multiple passes over the same leaf page while pruning dead nodes in b-tree indexes.  If this occurred at the same time a concurrent process was inserting an new index pointer into dead space on the same leaf page at the same time, autovacuum could cause that index pointer to be flagged dead and removed.  As a result, a row would exist in the table but not in the index -- a form of mild data loss or data corruption.

The above depends on split-millesecond timing to occur, which is how we were able to miss the issue for so long.  The odds of it happening for any particular insert, even on a busy system, were miniscule, maybe 1 in a million.  Further, it required frequent inserts into a table which also had heavy updates and deletes. Not much to worry about overall, yes?  Well, no.

This is where the million monkeys come in.  Just as the apochryphal mega-primate literary club will, eventually, due to random typewriter-key-mashing, produce War and Peace, with enough database activity your chances of hitting this bug move from "highly improbable" to "nearly certain".  Every high-traffic production database has at least one table which receives heavy inserts, updates, and deletes (think queue table), and given a year or so might have a couple billion writes and a million autovacuum runs.  Suddenly "one in a million" timing becomes 99.5% probability.

Indeed, on examination, we've found this kind of index corruption on four production customer databases.  And we haven't checked nearly all of them.

Obviously this means you should update your production databases as soon as you can (downtime this weekend, perhaps?).  Folks have also asked me about how to check for this form of index corruption.  It's actually quite simple:

First, do a count of the rows in the table by full table scan:

staffos=# SELECT count(*) FROM candidates;

 count

-------

  1449

(1 row)

Then, force an index scan, and do a count(*) using a condition you know to be universally true for an indexed column in the table:

staffos=# set enable_seqscan = false;

SET

staffos=# select count(*) from candidates where id > 0;

 count

-------

  1448

(1 row)

Results like the above would indicate that you've encountered the index corruption bug.  Note that you need to test each index to be sure.  You fix it by reindexing:

staffos=# REINDEX INDEX candidates_pkey;

REINDEX

staffos=# select count(*) from candidates where id > 0;

 count

-------

  1449

(1 row)

Hopefully this helps with the bug.  Our apologies for not catching this issue earlier, but in our defense, PostgreSQL's record on subtle bugs like this is much better than competing databases.  This does show you why it's vital to stay up to date with the most recent update releases, though!