Date: Mon, 17 Sep 2012 00:32:36 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Rural Hunter <ruralhunter@gmail.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [ADMIN] pg_upgrade from 9.1.3 to 9.2 failed
Message-ID: <20120917043236.GA20123@momjian.us>
References: <5052C402.8020003@gmail.com> <20120914142616.GA11265@momjian.us>
	<50535302.8080000@gmail.com> <20120914183915.GA5933@momjian.us>
	<5053F896.60005@gmail.com> <20120915180603.GA20907@momjian.us>
	<505557CD.8090304@gmail.com> <20120916171746.GA24907@momjian.us>
	<28666.1347833056@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <28666.1347833056@sss.pgh.pa.us>
User-Agent: Mutt/1.5.20 (2009-06-14)

On Sun, Sep 16, 2012 at 06:04:16PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > On Sun, Sep 16, 2012 at 12:38:37PM +0800, Rural Hunter wrote:
> >> I ran the pg_upgrade with the patch and found the problematic object
> >> is a toast object.
> 
> > OK, this is exactly what I wanted to see, and it explains why pg_dump
> > didn't show it.  Can you find out what table references this toast
> > table?  Try this query on the old cluster:
> 
> > 	select oid, * from pg_class WHERE reltoastrelid = 16439148;
> 
> > I believe it will have an oid of 16439145, or it might not exist.
> 
> Most likely what's happened is that the table has a toast table that
> it doesn't need, as a result of having dropped the only wide column(s)
> in it.  So when the table is recreated in the new cluster, there's no
> toast table for it.
> 
> So what you need to do is get rid of that check, or relax it so that it
> doesn't insist on toast tables matching up exactly.  It seems possible
> that there could be discrepancies in the other direction too, ie,
> new cluster created a toast table when old cluster didn't have one.

pg_dump.c already has this code:

        if (OidIsValid(pg_class_reltoastrelid))
        {
            /*
             * One complexity is that the table definition might not require
             * the creation of a TOAST table, and the TOAST table might have
             * been created long after table creation, when the table was
             * loaded with wide data.  By setting the TOAST oid we force
             * creation of the TOAST heap and TOAST index by the backend so we
             * can cleanly copy the files during binary upgrade.
             */

            appendPQExpBuffer(upgrade_buffer,
                              "SELECT binary_upgrade.set_next_toast_pg_class_oid('%u'::pg_catalog.oid);\n",
                              pg_class_reltoastrelid);

            /* every toast table has an index */
            appendPQExpBuffer(upgrade_buffer,
                              "SELECT binary_upgrade.set_next_index_pg_class_oid('%u'::pg_catalog.oid);\n",
                              pg_class_reltoastidxid);
        }

As you can see, we look at the existing TOAST usage and force the new
cluster to match.  As I remember we replay the DROP COLUMN in binary
upgrade mode so the new cluster always matches the old cluster's TOAST
usage.  I certainly have never seen this bug reported before.

I think the big question is why did this case fail?  I can say that the
query that pulls details from each cluster skips information_schema or
oid < FirstNormalObjectId.  I wonder if there is a mismatch between what
pg_dump filters out and pg_upgrade.  Can you tell us the schema of the
'sql_features' table?

Also, does it appear in the pg_dump --schema-only output?  I don't think
it does because it wasn't reported in the pg_dump --schema-only diff I
requested, and pg_dump wouldn't have dumped it from the new cluster. 

What that means is that 'sql_features' got a TOAST table in the old
cluster but while 'sql_features' also has a TOAST table in the new
cluster, it isn't processed by pg_upgrade because it is in the
information schema and has an oid < FirstNormalObjectId.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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