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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
