BUG #10911: pg_upgrade appears to lose the transaction id epoch

On Tue, Jul 8, 2014 at 09:17:05PM +0000, gregburek@heroku.com wrote:

The following bug has been logged on the website:

Bug reference: 10911
Logged by: Greg Burek
Email address: gregburek@heroku.com
PostgreSQL version: 9.0.17
Operating system: Ubuntu Linux
Description:

When upgrading a 9.0.17 db to 9.3.4 via pg_upgrade, the transaction ids
appear to fall backward in time:

From before the upgrade:

=> SELECT txid_snapshot_xmax(txid_current_snapshot());
txid_snapshot_xmax
--------------------
66329538555
(1 row)

After the upgrade:

=> SELECT txid_snapshot_xmax(txid_current_snapshot());
txid_snapshot_xmax
--------------------
1905029395
(1 row)

Looking at pg_control before the upgrade:
$ pg_controldata /database/ | grep -i nextxid
Latest checkpoint's NextXID: 15/1905728256

After the upgrade:
$ pg_controldata /database/ | grep -i nextxid
Latest checkpoint's NextXID: 0/1905029398

This is an unexpected loss of the higher order bits when the upgrade is
performed. Is it possible to preserve the epoch across the upgrade?

Yes, this was reported in April 2014:

/messages/by-id/CAJ2ymdgTEQ8AETJnQUeEuGjk-OGPWQn_MXgEY5urJpXbJNcohg@mail.gmail.com

I will try to fix it for 9.5, due in 2015.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Tue, Jul 8, 2014 at 10:17 PM, <gregburek@heroku.com> wrote:

Looking at pg_control before the upgrade:
$ pg_controldata /database/ | grep -i nextxid
Latest checkpoint's NextXID: 15/1905728256

After the upgrade:
$ pg_controldata /database/ | grep -i nextxid
Latest checkpoint's NextXID: 0/1905029398

You could just do "pg_resetxlog -e 15" to work around the problem
(after a clean shutdown).

--
greg

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

I think all that's required is to read the epoch at the same time as
the xid and set it at the same time.

I tested this only using the harness so that's a 9.4->9.4 pg_upgrade.
It would be worth testing on older versions but I don't see any
changes to pg_controldata that would be relevant since 8.2 when the
epoch was added.