Обсуждение: BUG #10911: pg_upgrade appears to lose the transaction id epoch

Поиск
Список
Период
Сортировка

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

От
gregburek@heroku.com
Дата:
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

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

От
Bruce Momjian
Дата:
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. +

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

От
Greg Stark
Дата:
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

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

От
Greg Stark
Дата:
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.

Вложения