Date: Sun, 30 Mar 2014 00:00:30 -0400
From: Stephen Frost <sfrost@snowman.net>
To: pgsql-hackers@postgresql.org
Subject: MultiXactId error after upgrade to 9.3.4
Message-ID: <20140330040029.GY4582@tamriel.snowman.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature"; boundary="Wi9qHyd/iQw4GpTM"
Content-Disposition: inline
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


--Wi9qHyd/iQw4GpTM
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

  Looks like we might not be entirely out of the woods yet regarding
  MultiXactId's.  After doing an upgrade from 9.2.6 to 9.3.4, we saw the
  following:

  ERROR:  MultiXactId 6849409 has not been created yet -- apparent wraparou=
nd

  The table contents can be select'd out and match the pre-upgrade
  backup, but any attempt to VACUUM / VACUUM FULL / CLUSTER fails,
  unsurprisingly.

  I've just started looking into this, but here's a bit more data-

  The *NEW* (9.3.4) cluster's pg_multixact files:

  pg_multixact/members/

    -rw------- 1 postgres postgres 8192 Mar 30 02:33 0000

  pg_multixact/offsets/

    -rw------- 1 postgres postgres   8192 Mar 28 01:11 0000
    -rw------- 1 postgres postgres 122880 Mar 30 02:33 0018

  The *OLD* (9.2.6) cluster's pg_multixact files:

  pg_multixact/members/

    -rw-rw-r-- 1 postgres postgres 188416 Mar 30 03:07 0044

  pg_multixact/offsets/
 =20
    -rw-rw-r-- 1 postgres postgres 114688 Mar 30 03:07 0018

  txid_current - 40297693
  datfrozenxid - 654

  autovacuum_freeze_max_age, vacuum_freeze_min_age,
  vacuum_freeze_table_age, multixact GUCs are commented out / default
  values.

  The *NEW* (9.3.4) control data shows:

  pg_control version number:            937
  Catalog version number:               201306121

  Latest checkpoint's NextXID:          0/40297704
  Latest checkpoint's NextOID:          53773598

  Latest checkpoint's NextMultiXactId:  1601771
  Latest checkpoint's NextMultiOffset:  1105

  Latest checkpoint's oldestXID:        654
  Latest checkpoint's oldestXID's DB:   12036
  Latest checkpoint's oldestActiveXID:  40297704
  Latest checkpoint's oldestMultiXid:   1601462
  Latest checkpoint's oldestMulti's DB: 0

  The *OLD* (9.2.6) control data had:

  pg_control version number:            922
  Catalog version number:               201204301

  Latest checkpoint's NextXID:          0/40195831
  Latest checkpoint's NextOID:          53757891

  Latest checkpoint's NextMultiXactId:  1601462
  Latest checkpoint's NextMultiOffset:  4503112

  Latest checkpoint's oldestXID:        654
  Latest checkpoint's oldestXID's DB:   12870
  Latest checkpoint's oldestActiveXID:  0

  (It doesn't report the oldestMulti info under 9.2.6).

  The pg_upgrade reported:

  Setting oldest multixact ID on new cluster

  While testing, I discovered that this didn't appear to happen with a
  (earlier) upgrade to 9.3.2.  Don't know if it's indicative of
  anything.

  Here is what pageinspace shows for the record:

  -[ RECORD 1 ]--------
  lp          | 45
  lp_off      | 5896
  lp_flags    | 1
  lp_len      | 50
  t_xmin      | 9663920
  t_xmax      | 6849409
  t_field3    | 26930
  t_ctid      | (0,45)
  t_infomask2 | 5
  t_infomask  | 6528
  t_hoff      | 24
  t_bits      |=20
  t_oid       |=20

  Which shows xmax as 6849409 and HEAP_XMAX_IS_MULTI is set.  This is
  the only tuple in the table which has HEAP_XMAX_IS_MULTI and the xmax
  for all of the other tuples is quite a bit higher (though all are
  visible currently).

  Thoughts?

    Thanks,

        Stephen

--Wi9qHyd/iQw4GpTM
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.14 (GNU/Linux)

iQIcBAEBAgAGBQJTN5bdAAoJEO1sijiDR2RVrksQAJW/ZrJDz877ZlZ2WUuBq8Qw
qoOlV7S94Zo2gxsWyfnr86MAbqECgHXw3K0FQUkkmnt3dxP0yNPVd1Z3OqNua/L+
QnxK8y0WSCcDMsB6oHsF+ckElqfDnn+1oStUQx6ylOhrtxyoUlhXekcwuFbdXS+n
rgtfgE1byfwEIA+ksj6KZvBoBTZJPz0fbXr7BBH/MtaWuWCpfJj1ZwLspV9aXABA
9FO978OiED88HUY51qPV4wYXduDTFxQabbuizQ43j0bU4zTFfKRF6pCxoV0XhaJt
57NsICU/N6fmOmxMhlEIjxVJl+gO4HJzKk7AmwtVca4KIHQexfgLL3mJfmNhVoY6
KGhO6XI13GNzq8ELLhDrct6kBaRruWx/JQ8xAL9tl2IGnveqBn3TlqujedQLabSq
mBGjLiDFaj6xDI/i/aGvonfQosgf8wCymDwlEDsow0oCPFuITMpey6jLYGTIPHf1
K70X6jzFfC88QH+XmuZYfR/1gs4bPzvNFUpv6x0YUN/cHdTUqsSex5vdLLTVJw7Z
/HjCYXc8XG6zEHYInLhoA6ZDPLIFU6wOXzKICWo4koRpXIUUrTDBbAHEhIAz9bEc
VImlRtonS+W257lN+RdEhaSpY1FzGGEsG/gfccVj7URS8gI0e/e5PKa25716Rx02
GrrbjJ279N3daSYlZ0r1
=5V4a
-----END PGP SIGNATURE-----

--Wi9qHyd/iQw4GpTM--