Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4

On Mon, May 13, 2013 at 09:36:51AM -0400, Steve Singer wrote:
> On 05/11/2013 04:58 PM, Bruce Momjian wrote:
> >On Fri, May 10, 2013 at 08:03:38PM -0400, Bruce Momjian wrote:
> >>OK, this verifies that the table had a lot of DDL churn. I have no idea
> >>how to pursue this further because I am unsure how we are going to
> >>replicate the operations performed on this table in the past, as you
> >>mentioned much of this was before your time on the job.
> >>
> >>Evan, I suggest you force a toast table on the table by doing:
> >>
> >> ALTER TABLE bpm.setupinfo ADD COLUMN dummy TEXT;
> >>
> >>Then drop the column. That will create a toast table and will allow
> >>pg_upgrade to succeed.
> >FYI, I did test adding a TEXT column and altering a column to TEXT on
> >Postgres 9.1, and both created a toast table. I am still have no clues
> >about what would have caused the missing toast table.
> >
>
> I once saw a case where a varchar(x) column was changed to something
> larger by manually updating the catalog with an UPDATE statement on
> pg_attribute.atttypmod. Everything was fine until they tried
> pg_upgrade which failed because the DDL to create the table from
> pg_dump with the larger column creates a table that had a toast
> table but the original table in the 8.3 cluster did not have a toast
> table.

That is a good point. We used to tell users they could manually update
pg_attribute to increase the length of a column --- that obviously will
not work anymore with pg_upgrade, and now that we have an ALTER TABLE
that can handle it without a table rewrite since PG 9.1, there is no
reason for users to need to adjust pg_attribute.

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

+ It's impossible for everything to be true. +