Re: Pg_upgrade and toast tables bug discovered

The 9.3.5 release notes contain...

-

Fix pg_upgrade for cases where the new server creates a TOAST table but
the old version did not (Bruce Momjian)

This rare situation would manifest as "relation OID mismatch" errors.

...which I thought was this bug, hence my confusion. If anyone else is
experiencing this bug, they may erroneously be led to believe that 9.3.5
contains the fix.

I will attempt to build 9.3 stable head and retry my upgrade.

On Wed, Sep 3, 2014 at 6:03 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Wed, Sep 3, 2014 at 05:12:30PM -0600, Noah Yetter wrote:
> > I'm not sure it's fixed. I am attempting a pg_upgrade from 9.2.8 to
> 9.3.5 and
> > it dies like so:
> >
> > (...many relations restoring successfully snipped...)
> > pg_restore: creating SEQUENCE address_address_id_seq
> > pg_restore: [archiver (db)] Error while PROCESSING TOC:
> > pg_restore: [archiver (db)] Error from TOC entry 1410; 1259 17670
> SEQUENCE
> > address_address_id_seq javaprod
> > pg_restore: [archiver (db)] could not execute query: ERROR: could not
> create
> > file "base/16414/17670": File exists
> >
> > Inspecting a copy of the source cluster, OID 17670 does indeed
> correspond to
> > address_address_id_seq, but inspecting the partially-upgraded cluster
> that OID
> > is taken by pg_toast_202359_index. Again conferring with a copy of the
> source
> > (9.2.8) cluster, the relation corresponding to filenode 202359 does not
> have a
> > toast table.
> >
> > (I know pg-hackers isn't the right place to discuss admin issues, but
> this
> > thread is the only evidence of this bug I can find. If anyone can
> suggest a
> > workaround I would be infinitely grateful.)
>
> Actually, there was a pg_upgrade fix _after_ the release of 9.3.5 which
> explains this failure:
>
> commit 4c6780fd17aa43ed6362aa682499cc2f9712cc8b
> Author: Bruce Momjian <bruce(at)momjian(dot)us>
> Date: Thu Aug 7 14:56:13 2014 -0400
>
> pg_upgrade: prevent oid conflicts with new-cluster TOAST tables
>
> Previously, TOAST tables only required in the new cluster
> could cause
> oid conflicts if they were auto-numbered and a later
> conflicting oid had
> to be assigned.
>
> Backpatch through 9.3
>
> Any chance you can download the 9.3.X source tree and try that? You
> need an entire install, not just a new pg_upgrade binary. I am
> disapointed I could not fix this before 9.3.5 was released.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + Everyone has their own god. +
>