Date: Mon, 2 Jan 2017 16:56:18 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: pgsql-hackers@postgresql.org
Subject: Re: ALTER TABLE .. ALTER COLUMN .. ERROR: attribute .. has wrong type
Message-ID: <20170102225618.GA10071@telsasoft.com>
References: 
 <CALcU3kLseKzasSpnAhA7zQgd6Oebhqmb3SS-dPWA2R-K=TbGgw@mail.gmail.com>
 <20161012152505.GD12747@telsasoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20161012152505.GD12747@telsasoft.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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).


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers