Re: pg_upgrade fails: Mismatch of relation OID in database 8.4 -> 9.3

On Fri, May 23, 2014 at 03:36:20PM +0200, Andres Freund wrote:
> On 2014-05-22 09:20:38 -0600, Jeff Ross wrote:
> > On 5/21/14, 2:37 PM, Bruce Momjian wrote:
> > >The only item I can think of that would cause this is someone changing
> > >the length of a string. Did someone modify pg_attribute directly to
> > >increase the length of one of the character columns?
>
> > I don't know, sorry.
>
> > >I just tested ALTER TABLE in 8.4 and it does create a toast table for
> > >this case in 9.4:
> > >
> > > CREATE TABLE test (x CHAR(10));
> > > ALTER TABLE test ALTER COLUMN x TYPE CHAR(8000);
> > >
> > I just tried this on the problem table and it did indeed create a toast
> > table.
> >
> > I then retried pg_upgrade and it failed with the same problem on a different
> > table in the same database. Of the 67 databases in the 8.4 cluster, 5 (so
> > far) have had this problem on at least one table.
>
> Any chance you could, *before* you create the toast table, do a:
> SELECT attrelid::regclass, attname, attnum, attlen, *
> FROM pg_attribute
> WHERE attrelid = 'a'::regclass
> ORDER BY attnum ASC;
>
> Where 'a' is replaced by the affected table?

That's an interesting idea. In binary_upgrade mode, if we create a
TOAST table and were _not_ passed in a toast id, we could detect that,
though it just means we detect the failure earlier. Seeing as this is
the first such report, I am not included to see that as a win.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +