From: Justin Pryzby <pryzby@telsasoft.com>
To: pgsql-hackers@postgresql.org
Subject: Re: ALTER TABLE .. ALTER COLUMN .. ERROR: attribute .. has wrong type
Date: Mon, 2 Jan 2017 18:32:40 -0600
Message-ID: <20170103003240.GM32031@telsasoft.com> (raw)
In-Reply-To: <20170102225618.GA10071@telsasoft.com>
References: <CALcU3kLseKzasSpnAhA7zQgd6Oebhqmb3SS-dPWA2R-K=TbGgw@mail.gmail.com>
<20161012152505.GD12747@telsasoft.com>
<20170102225618.GA10071@telsasoft.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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 ??
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com
Subject: Re: ALTER TABLE .. ALTER COLUMN .. ERROR: attribute .. has wrong type
In-Reply-To: <20170103003240.GM32031@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox