Date: Tue, 3 Jan 2017 13:46:04 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>,
	"pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: ALTER TABLE .. ALTER COLUMN .. ERROR: attribute .. has
 wrong type
Message-ID: <20170103194604.GR32031@telsasoft.com>
References: 
 <CALcU3kLseKzasSpnAhA7zQgd6Oebhqmb3SS-dPWA2R-K=TbGgw@mail.gmail.com>
 <20161012152505.GD12747@telsasoft.com>
 <20170102225618.GA10071@telsasoft.com>
 <20170103003240.GM32031@telsasoft.com>
 <CA+TgmoY9W99m3cW9-4Kjnw4KO+PRqF8Lh_AqWh8Qoop9ZhnUdw@mail.gmail.com>
 <20170103165903.GP32031@telsasoft.com>
 <CA+TgmobAT992nxV=6kqnpF=LK493oNdf2dzXJX-Yz+=LWZ-_AA@mail.gmail.com>
 <20170103185742.GQ32031@telsasoft.com>
 <28476.1483471956@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <28476.1483471956@sss.pgh.pa.us>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On Tue, Jan 03, 2017 at 02:32:36PM -0500, Tom Lane wrote:
> Justin Pryzby <pryzby@telsasoft.com> writes:
> >>>> I can cause the error at will on the existing table,
> 
> That's good news, at least.
> 
> 1. Please trigger it with "\set VERBOSITY verbose" enabled, so we can see
> the exact source location --- there are a couple of instances of that
> text.

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:  42804: attribute 424 has wrong type
DETAIL:  Table has type smallint, but query expects integer.
LOCATION:  ExecEvalScalarVar, execQual.c:660

> 2. Even better would be a stack trace for the call to errfinish,
> https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

#1  0x00000000006dd39f in exec_simple_query (query_string=0x1fc5fb0 "begin;")
    at postgres.c:932
        dest = DestRemote
        oldcontext = 0x1f3b100
        parsetree_list = 0x1fc69f0
        save_log_statement_stats = 0 '\000'
        was_logged = 0 '\000'
        msec_str = "\360:\235\213\377\177\000\000`<\235\213\377\177\000\000\260_\374\001", '\000' <repeats 11 times>
        __func__ = "exec_simple_query"

and then

#1  0x00000000006dd39f in exec_simple_query (
    query_string=0x1fc5fb0 "ALTER TABLE eric_umts_rnc_utrancell_metrics ALTER COLUMN PMSUMPACKETLATENCY_000 TYPE BIGINT USING PMSUMPACKETLATENCY_000::BIGINT;")
    at postgres.c:932
        dest = DestRemote
        oldcontext = 0x1f3b100
        parsetree_list = 0x1fc6fc8
        save_log_statement_stats = 0 '\000'
        was_logged = 0 '\000'
        msec_str = "\360:\235\213\377\177\000\000`<\235\213\377\177\000\000\260_\374\001", '\000' <repeats 11 times>
        __func__ = "exec_simple_query"

then

#1  0x00000000005d0e30 in ExecEvalScalarVar (exprstate=<value optimized out>, 
    econtext=<value optimized out>, isNull=<value optimized out>, 
    isDone=<value optimized out>) at execQual.c:655
        attnum = 424
        __func__ = "ExecEvalScalarVar"

> 3. It's pretty hard to see how you'd reach any of these places for an
> ALTER COLUMN TYPE on a simple table.  Has the table got rules, triggers,
> default values?  Could we see "\d+" output for it?

triggers and defaults, yes.

 sect_id                                     | integer                  | not null
 start_time                                  | timestamp with time zone | not null
 site_id                                     | integer                  | not null
 interval_seconds                            | smallint                 | not null
 utrancell                                   | text                     | not null
 nedn                                        | text                     | not null
 rnc_id                                      | integer                  | not null
 device_id                                   | integer                  | not null
 pmcelldowntimeauto                          | smallint                 | 
 pmcelldowntimeman                           | smallint                 | 
 pmchswitchattemptfachura                    | smallint                 | 
 pmchswitchattempturafach                    | smallint                 | 
...
Triggers:
    eric_umts_rnc_utrancell_insert_trigger BEFORE INSERT ON eric_umts_rnc_utrancell_metrics FOR EACH ROW EXECUTE PROCEDURE eric_umts_rnc_utrancell_insert_function()
Number of child tables: 3 (Use \d+ to list them.)

I'll send the rest of \d if you really want but:

ts=# SELECT COUNT(1) FROM pg_attribute WHERE attrelid='eric_umts_rnc_utrancell_metrics'::regclass;
count | 1116

Justin


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