Re: ALTER TABLE .. ALTER COLUMN .. ERROR: attribute .. has wrong type

I originally sent to psql-general some months ago, but it appears it was never
delivered (perhaps I wasn't properly subscribed?).

Failed to alter table eric_umts_rnc_utrancell_metrics: ERROR: attribute 361 has wrong type
DETAIL: Table has type integer, but query expects smallint.

We've seen this at least 4 times now, on PG95 and 9.6; 3 of those times are for
the above table.

Any ideas what I can do to either reproduce it or otherwise avoid it ?

On Wed, Oct 12, 2016 at 10:25:05AM -0500, Justin Pryzby wrote:
> We've seen this happen at least once on a 9.5 server, and twice on (the same)
> server since its upgrade last week to 9.6:
>
> > ALTER TABLE t ALTER column TYPE says: "ERROR: attribute 81 has wrong type".
>
> Just now under 9.6
> DETAIL: Table has type integer, but query expects smallint
> ...
> ts=# SELECT attnum, atttypid, attrelid::regclass FROM pg_attribute WHERE attname='pmnopagingattemptutranrejected' ORDER BY 1 DESC,2,3;
> attnum | atttypid | attrelid
> --------+----------+---------------------------------
> 193 | 21 | eric_umts_rnc_utrancell_metrics
> 193 | 21 | eric_umts_rnc_utrancell_201508
> 179 | 21 | eric_umts_rnc_utrancell_201509
> 179 | 21 | eric_umts_rnc_utrancell_201510
> 179 | 21 | eric_umts_rnc_utrancell_201511
> 179 | 21 | eric_umts_rnc_utrancell_201602
> [...]
> 179 | 21 | eric_umts_rnc_utrancell_201610
> 179 | 21 | eric_umts_rnc_utrancell_201611
> (17 rows)
>
> Last week (same server, same table, still 9.6):
> DETAIL: Table has type real, but query expects smallint
>
> In July (different server) under 9.5
> DETAIL: Table has type real, but query expects smallint
> ...
> SELECT atttypid, attnum, attrelid::regclass FROM pg_attribute WHERE attname='c_84150886'
> atttypid | attnum | attrelid
> ----------+--------+-----------------------------
> 21 | 200 | huawei_msc_trunkgrp_201605
> 21 | 200 | huawei_msc_trunkgrp_201604
> 21 | 200 | huawei_msc_trunkgrp_201603
> 21 | 200 | huawei_msc_trunkgrp_201602
> 21 | 200 | huawei_msc_trunkgrp_201512
> 21 | 200 | huawei_msc_trunkgrp_201511
> 21 | 200 | huawei_msc_trunkgrp_201510
> 21 | 200 | huawei_msc_trunkgrp_201508
> 21 | 200 | huawei_msc_trunkgrp_201507
> 21 | 200 | huawei_msc_trunkgrp_201506
> 21 | 200 | huawei_msc_trunkgrp_201505
> 21 | 200 | huawei_msc_trunkgrp_201607
> 21 | 200 | huawei_msc_trunkgrp_201606
> 21 | 200 | huawei_msc_trunkgrp_201608
> 21 | 201 | huawei_msc_trunkgrp_metrics
> 21 | 200 | huawei_msc_trunkgrp_201509
> 21 | 200 | huawei_msc_trunkgrp_201601
> (17 rows)
>
> I don't have a clear recollection how I solved this in July; possibly by
> restoring the (historic, partition) table from backup.
>
> Last week again again just now (both under 9.6), a colleague found that he was
> able to avoid the error by ALTER TYPE without USING.
>
> Note that we ALTER TABLE .. NO INHERIT the partitions for all but the most
> recent 2 months before ALTERing them (or the parent). The "ALTER NO INHERIT"
> and the ALTER TYPE of historic partitions are done outside of a transaction in
> order to avoid large additional disk use otherwise used when ALTERing a parent
> with many or large children (the sum of the size of the children).