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

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?

thanks,
Greg

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:

    http://www.postgresql.org/message-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. +

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

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.