On Mon, Jan 2, 2017 at 7:32 PM, Justin Pryzby <pry...@telsasoft.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
