There have been periodic reports of pg_upgrade errors related to toast
tables. The most recent one was from May of this year:
http://www.postgresql.org/message-id/flat/20140520202223.GB3701@momjian.us#20140520202223.GB3701@momjian.us
There error was:
Copying user relation files /var/lib/postgresql/8.4/main/base/4275487/4278965Mismatch of relation OID in database
"FNBooking":old OID 4279499, new OID 19792Failure, exiting
and the fix is to add a dummy TEXT column to the table on the old
cluster to force a toast table, then drop the dummy column.
I have had trouble getting a table schema that is causing problems, but
received a report via EDB support recently that had a simple schema
(anonymized):
CREATE TABLE pg_upgrade_toast_test ( x1 numeric(15,0), x2 numeric(15,0), x3 character varying(15), x4
charactervarying(60), x5 numeric(15,0), x6 numeric(15,0), x7 character varying(15), x8 character
varying(60), x9 numeric(15,0), x10 character varying(15), x11 character varying(60), x12 numeric(15,0),
x13numeric(15,0), x14 character varying(15), x15 character varying(60), x16 numeric(15,0), x17 character
varying(15), x18 character varying(60), x19 numeric(15,0), x20 character varying(15), x21 character
varying(60));
needs_toast_table() computes the length of this table as 2024 bytes in
9.0, and 2064 bytes on 9.1, with the TOAST threshold being 2032 bytes.
It turns out it is this commit that causes the difference:
commit 97f38001acc61449f7ac09c539ccc29e40fecd26Author: Robert Haas <rhaas@postgresql.org>Date: Wed Aug 4 17:33:09
2010+0000 Fix numeric_maximum_size() calculation. The old computation can sometimes underestimate the necessary
space by 2 bytes; however we're not back-patching this, because this result isn't used for anything critical. Per
discussionwith Tom Lane, make the typmod test in this function match the ones in numeric() and apply_typmod()
exactly.
It seems the impact of this patch on pg_upgrade wasn't considered, or
even realized until now.
Suggestions on a fix?
My initial idea is to to allow for toast tables in the new cluster that
aren't in the old cluster by skipping over the extra toast tables. This
would only be for pre-9.1 old clusters. It would not involve adding
toast tables to the old cluster as pg_upgrade never modifies the old
cluster. We already handle cases where the old cluster had toast tables
and the new cluster wouldn't ordinarily have them.
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ Everyone has their own god. +
