From: Rural Hunter <ruralhunter@gmail.com>
To: Bruce Momjian <bruce@momjian.us>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [ADMIN] pg_upgrade from 9.1.3 to 9.2 failed
Date: Mon, 17 Sep 2012 13:03:37 +0800
Message-ID: <5056AF29.3040903@gmail.com> (raw)
In-Reply-To: <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>
<20120917043236.GA20123@momjian.us>
于2012年9月17日 12:32:36,Bruce Momjian写到:
> 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?
# select * from pg_tables where tablename='sql_features';
schemaname | tablename | tableowner | tablespace |
hasindexes | hasrules | hastriggers
--------------------+--------------+------------+------------+------------+----------+-------------
information_schema | sql_features | postgres | | f
| f | f
(1 row)
>
> 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.
right. I checked the dump from the old cluster and it's not there.
>
> 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.
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ruralhunter@gmail.com, bruce@momjian.us, pgsql-hackers@postgresql.org, tgl@sss.pgh.pa.us
Subject: Re: [ADMIN] pg_upgrade from 9.1.3 to 9.2 failed
In-Reply-To: <5056AF29.3040903@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox