Wednesday, May 27, 2015

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.


  1. I think I'm OK ;)

    [root@supernews:/usr/local/pgsql/data/pg_multixact/members] # ls | wc -l

    [root@supernews:/usr/local/pgsql/data/pg_multixact/members] # ls -l
    total 8
    -rw------- 1 pgsql pgsql 8192 May 23 22:35 0000

  2. For me - I'm not sure.

    # ls -l | wc -l

    And new file is created each 5 minutes. Also look at this:

    # ls -la [A-F0-9]* | awk '{ print $6 " " $7 }' | sort | uniq -c | sort -n -b -k 3
    113 May 9
    181 May 10
    200 May 11
    196 May 12
    203 May 13
    230 May 14
    177 May 15
    130 May 16
    153 May 17
    166 May 18
    157 May 19
    204 May 20
    60 May 21
    151 May 22
    139 May 23
    144 May 24
    224 May 25
    166 May 26
    191 May 27
    209 May 28
    186 May 29
    132 May 30
    17 May 31

    When this files are going to by recycled/removed/whatever?

    And I'm running 9.3.7 already.

    1. Well, if you're running 9.3.7, it fixes that issue, so you don't really need to worry about it. 9.3.8 will fix some other issues, but it seems likely that 9.3.8 will be out before you hit them.