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

On Mon, Jan 2, 2017 at 7:32 PM, Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:
> On Wed, Oct 12, 2016 at 10:25:05AM -0500, Justin Pryzby wrote:
>> > 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).
>
> Here's DETAILs for a 2nd such error which has shown up today:
>
> (EricssonUtranXmlParser): Failed to alter table eric_umts_rnc_utrancell_metrics: ERROR: attribute 424 has wrong type
> DETAIL: Table has type smallint, but query expects integer.
>
> (EricssonUtranXmlParser): Failed to alter table eric_umts_rnc_utrancell_metrics: ERROR: attribute 361 has wrong type
> DETAIL: Table has type integer, but query expects smallint.
>
> Also, note both alters really do work without "USING":
>
> ts=# begin; drop view umts_eric_ch_switch_view, eric_umts_rnc_utrancell_view, umts_eric_cell_integrity_view; ALTER TABLE eric_umts_rnc_utrancell_metrics ALTER COLUMN PMSUMPACKETLATENCY_000 TYPE BIGINT USING PMSUMPACKETLATENCY_000::BIGINT;
> BEGIN
> DROP VIEW
> ERROR: attribute 424 has wrong type
> DETAIL: Table has type smallint, but query expects integer.
> ts=#
>
> ts=# begin; drop view umts_eric_ch_switch_view, eric_umts_rnc_utrancell_view, umts_eric_cell_integrity_view; ALTER TABLE eric_umts_rnc_utrancell_metrics ALTER COLUMN PMSUMPACKETLATENCY_000 TYPE BIGINT ;
> BEGIN
> DROP VIEW
> ALTER TABLE
> ts=#
>
> Is it useful to send something from pg_attribute, or other clues ??

So, are these errors reproducible? Like, if you create a brand new
cluster with initdb and a brand new database with createdb and you use
CREATE VIEW to recreate the tables and views and then do this, does
the error reliably happen? Or is this problem unique to your existing
database but it doesn't happen on a new one? If it doesn't reproduce
on a new database, does it reproduce consistently on the existing
database or is that also intermittent?

If nothing else, I'd say the error message is very poor. But there
might be an actual bug here, too.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company