Bruce Momjian wrote:
> Peter Eisentraut wrote:
> > 8.4 -> master upgrade fails like this:
> >
> > Restoring user relation files
> > Mismatch of relation names in database "regression": old name
> > "pg_toast.pg_toast_27437", new name "pg_toast.pg_toast_27309"
> > Failure, exiting
> >
> > This has been 100% reproducible for me.
>
> I can now reproduce this failure and will research the cause, probably
> not before next week though. :-( What is interesting is that loading
> the regression tests from an SQL dump does not show the failure, but
> running the regression tests and then upgrading does.
OK, I found time to research this and I think I have a fix. The problem
is caused by an ALTER TABLE in 8.4 not preserving a toast table name
that matches the heap oid. Below you can see that 8.4 does not preserve
this, while 9.0 does:
8.4
---
test=> CREATE TABLE test5(aa TEXT, bb TEXT);
CREATE TABLE
test=> INSERT INTO test5 VALUES ('123', '323');
INSERT 0 1
test=> ALTER TABLE test5 ALTER COLUMN aa TYPE INTEGER USING
bit_length(aa);
ALTER TABLE
test=> SELECT oid, reltoastrelid FROM pg_class WHERE relname = 'test5';
oid | reltoastrelid
-------+---------------
---> 16406 | 16415
(1 row)
test=> SELECT relname FROM pg_class WHERE oid = 16415;
relname
----------------
pg_toast_16412 <---
(1 row)
9.0
---
test=> CREATE TABLE test5(aa TEXT, bb TEXT);
CREATE TABLE
test=> INSERT INTO test5 VALUES ('123', '323');
INSERT 0 1
test=> ALTER TABLE test5 ALTER COLUMN aa TYPE INTEGER USING
bit_length(aa);
ALTER TABLE
test=> SELECT oid, reltoastrelid FROM pg_class WHERE relname = 'test5';
oid | reltoastrelid
-------+---------------
---> 16409 | 16418
(1 row)
test=> SELECT relname FROM pg_class WHERE oid = 16418;
relname
----------------
pg_toast_16409 <---
(1 row)
We must have fixed this in 9.0 and I missed it. Anyway, the pg_upgrade
code already assumes pre-8.4 doesn't have stable toast names:
/*
* In pre-8.4, TOAST table names change during CLUSTER; in >= 8.4
* TOAST relation names always use heap table oids, hence we cannot
* check relation names when upgrading from pre-8.4.
*/
if (strcmp(old_rel->nspname, new_rel->nspname) != 0 ||
((GET_MAJOR_VERSION(old_cluster.major_version) >= 804 ||
strcmp(old_rel->nspname, "pg_toast") != 0) &&
strcmp(old_rel->relname, new_rel->relname) != 0))
pg_log(PG_FATAL, "Mismatch of relation names in database \"%s\": "
"old name \"%s.%s\", new name \"%s.%s\"\n",
old_db->db_name, old_rel->nspname, old_rel->relname,
new_rel->nspname, new_rel->relname);
Looking at this code now, I realize it is wrong even without the 8.4
ALTER issue. If someone uses pg_upgrade to go from 8.3 to 8.4, they
would then still have the toast table name mismatch when going to 9.0,
so the test in itself is wrong anyway. I propose I just remove the 8.4
test and always allow toast table names not to match --- the oids are
still checked and are preserved.
The current code is just too conservative and throws an error during
upgrade (but not during check mode) when it shouldn't. This code only
exists in 9.1 and HEAD.
--
Bruce Momjian <br...@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
