Обсуждение: Re: [HACKERS] 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'
ORDERBY 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).

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
TABLEeric_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
TABLEeric_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 ??

Is this expected behavior ?

ts=# SELECT * FROM t WHERE site_id=32768 LIMIT 1;
(0 rows)

ts=# PREPARE x AS SELECT * FROM t WHERE site_id=$1 LIMIT 1;
PREPARE
ts=# EXECUTE x(32768);
ERROR:  smallint out of range

ts=# PREPARE y AS SELECT * FROM t WHERE site_id::int=$1 LIMIT 1;
PREPARE
ts=# EXECUTE y(32768);
(0 rows)

Note, we also sometimes get small/int out of range when SELECTing from a view,
and we end up as a workaround putting a ::big/int cast into the view or
multiplying by 1.

Thanks,
Justin