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
