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

On Tue, Jan 3, 2017 at 11:59 AM, Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:
> On Tue, Jan 03, 2017 at 11:45:33AM -0500, Robert Haas wrote:
>> > 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
>
> I can cause the error at will on the existing table, but I wouldn't know how to
> reproduce the problem on a new table/database. I'm guessing it has something
> to do with dropped columns or historic alters (which I mentioned are typically
> done separately on child tables vs their parent).
>
> Since it's happened 3 times now on this table, but not others on this database,
> I would guess it's an "data issue", possibly related to pg_upgrades. IOW it
> may be impossible to get into this state from a fresh initdb from a current
> version.
>
> I considered that perhaps it only affected our oldest tables, and would stop
> happening once they were dropped, but note this ALTER is only of a parent and
> its 3 most recent children. So only the empty parent could be described as
> "old".

Just for kicks, could you try running pg_catcheck on the affected system?

https://github.com/EnterpriseDB/pg_catcheck

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