From: | Jeff Ross <jeff(at)commandprompt(dot)com> |
---|---|
To: | Andres Freund <andres(at)2ndquadrant(dot)com> |
Cc: | Bruce Momjian <bruce(at)momjian(dot)us>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: pg_upgrade fails: Mismatch of relation OID in database 8.4 -> 9.3 |
Date: | 2014-05-25 22:59:24 |
Message-ID: | 538275CC.9070902@commandprompt.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 5/25/14, 11:44 AM, Andres Freund wrote:
>
> Hi,
>
> On 2014-05-23 08:23:57 -0600, Jeff Ross wrote:
>> UDB=# \x
>> Expanded display is on.
>> UDB=# SELECT attrelid::regclass, attname, attnum, attlen, *
>> FROM pg_attribute
>> WHERE attrelid = 'masterairportlist'::regclass
>> ORDER BY attnum ASC;
>> UDB=#
>> [ RECORD 1 ]-+------------------
>> ...
>
> A quick sum over the returned values seems to indicate that it's too
> large to not have a toast table. Adding up attlen and atttypmod gives a
> value of 1283. Considering that there additionally has to be VARHDRSZ
> space for the varlena header and that the maximum storage size for a
> varchar(n) is n * pg_encoding_max_length(GetDatabaseEncoding()) (usually
> 4) this seems to indicate that bad things[tm] have been done to the
> database.
> I suggest you write a script that does a 'ALTER TABLE $tbl ADD COLUMN
> toastme text; ALTER TABLE $tbl DROP COLUMN toastme' for all tables.
>
> Greetings,
>
> Andres Freund
>
Hi Andres,
Yes, that is exactly what I will do before our next test migration. I'd
already started on the script since I could not see any downside to
adding a column big enough to force a toast table and then dropping it,
exactly as Bruce and you suggest and especially if that will let me use
pg_upgrade rather than the traditional dump/restore.
Could a check like this be added to pg_upgrade? Is there a downside to
adding a column big enough to force a toast table and then dropping it
for any table that is too large not to have a toast table but doesn't?
Jeff
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2014-05-25 23:06:03 | Re: pg_recvlogical not accepting -I to specify start LSN position |
Previous Message | Jeff Janes | 2014-05-25 22:37:03 | Could not finish anti-wraparound VACUUM when stop limit is reached |