于2012年9月17日 12:32:36,Bruce Momjian写到:
On Sun, Sep 16, 2012 at 06:04:16PM -0400, Tom Lane wrote:
Bruce Momjian <br...@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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
