Skip site navigation (1) Skip section navigation (2)

Strange "Table has type character varying, but query expects character varying" errors

From: Ümit Öztosun <umit(at)ly(dot)com(dot)tr>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Strange "Table has type character varying, but query expects character varying" errors
Date: 2007-02-06 15:57:24
Message-ID: d95439ec0702060757h15ab54f3wd3b64d50c588279c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Today two of our production servers developed the following error(s):

ERROR:  attribute X(semi-random number here) has wrong type
DETAIL:  Table has type character varying, but query expects character varying.

They were running PostgreSQL 8.1.4 on Ubuntu Dapper, dual-core Intel Machines.

Server Info (two are the same):
2.6.12-10-686-smp  #1 SMP Sat Mar 11 16:41:12 UTC 2006 i686 GNU/Linux
Ubuntu Dapper
Locale=tr_TR.UTF-8
PG Version: 8.1.4 (Ubuntu Package)

All errors are caused by UPDATE statements generally spanning a single
table and a single row. Thinking an upgrade may solve the problem
resulted again in the same error. These were running for about a year
without any problems. We tried compiling PG 8.2.2 from sources and
restoring from dumps provided no solution.

A sample table and query is given below:

                                 Table " public.scf_fatura"
            Column           |        Data Type         |
                            Modifiers
-----------------------------+--------------------------+--------------------------------------------------------------------
 _key                        | bigint                   | not null default 0
 _serial                     | integer                  | not null
default nextval('scf_fatura__serial_seq'::regclass)
 _rep                        | character(1)             | not null
default 'n'::bpchar
 _user                       | bigint                   | default 0
 _date                       | timestamp with time zone |
 _site                       | smallint                 | default 0
 turu                        | smallint                 | default 0
 fisno                       | character varying(50)    | default
''::character varying
 tarih                       | date                     |
 saat                        | time without time zone   |
 belgeno                     | character varying(50)    | default
''::character varying
 belgeno2                    | character varying(50)    | default
''::character varying
 _key_scf_irsaliye           | bigint                   | default 0
 _key_sis_ozelkod1           | bigint                   | default 0
 _key_sis_ozelkod2           | bigint                   | default 0
 _key_sis_seviyekodu         | bigint                   | default 0
 _key_scf_satiselemani       | bigint                   | default 0
 _key_sis_sube_source        | bigint                   | default 0
 _key_sis_depo_source        | bigint                   | default 0
 karsifirma                  | character(1)             | default ''::bpchar
 _key_karsi_fatura           | bigint                   | default 0
 _key_scf_carikart           | bigint                   | default 0
 _key_scf_kasa               | bigint                   | default 0
 kasafisno                   | character varying(16)    | default
''::character varying
 sevkadresi1                 | character varying(128)   | default
''::character varying
 sevkadresi2                 | character varying(128)   | default
''::character varying
 sevkadresi3                 | character varying(128)   | default
''::character varying
 _key_sis_firma_dest         | bigint                   | default 0
 _key_sis_sube_dest          | bigint                   | default 0
 _key_sis_depo_dest          | bigint                   | default 0
 _key_sis_doviz              | bigint                   | default 0
 dovizkuru                   | numeric(15,10)           | default 0.0
 aciklama1                   | character varying(128)   | default
''::character varying
 aciklama2                   | character varying(128)   | default
''::character varying
 aciklama3                   | character varying(128)   | default
''::character varying
 toplammasraf                | numeric(20,10)           | default 0.0
 toplamindirim               | numeric(20,10)           | default  0.0
 toplam                      | numeric(20,10)           | default 0.0

  toplamotv                   | numeric(20,10)           | default 0.0
 toplamkdv                   | numeric(20,10)           | default 0.0
 net                         | numeric(20,10)           | default 0.0
 toplammasrafdvz             | numeric(20,10)           | default  0.0
 toplamindirimdvz            | numeric(20,10)           | default 0.0
 toplamdvz                   | numeric(20,10)           | default 0.0
 toplamotvdvz                | numeric(20,10)           | default 0.0
 toplamkdvdvz                | numeric(20,10)           | default 0.0
 netdvz                      | numeric(20,10)           | default 0.0
 iptal                       | character(1)             | default '-'::bpchar
 kilitli                     | character(1)             | default ''::bpchar
 kdvduzenorani               | character(1)             | default '+'::bpchar
 kdvduzentutari              | numeric(10,5)            | default  0.0
 _key_scf_malzeme_baglantisi | bigint                   | default 0
 _key_scf_odeme_plani        | bigint                   | default 0
 _owner                      | bigint                   | default 0
 _key_sis_doviz_raporlama    | bigint                   | default 0::bigint
 raporlamadovizkuru          | numeric(9,5)             | default 1
 ekmaliyet                   | numeric(16,7)            | default 0.0
 _key_muh_masrafmerkezi      | bigint                   | default 0
 ortalamavade                | date                     |
Indexes:
    "scf_fatura_pkey" PRIMARY KEY, btree (_key)
    "scf_fatura_belgeno2_idx" btree (upper(belgeno2::text))
    "scf_fatura_belgeno_idx" btree (upper(belgeno::text))
     "scf_fatura_fisno_idx" btree (upper(fisno::text))
    "scf_fatura_iptal_idx" btree (upper(iptal::text))
    "scf_fatura_key_scf_carikart_idx" btree (_key_scf_carikart)
    "scf_fatura_key_scf_irsaliye_idx" btree (_key_scf_irsaliye)
    "scf_fatura_key_scf_kasa_idx" btree (_key_scf_kasa)
    "scf_fatura_tarih_idx" btree (tarih)
    "scf_fatura_tarih_saat_idx" btree (tarih, saat)
    "scf_fatura_turu_idx" btree (turu)

And an simple *UPDATE* statement on this table such as:

UPDATE scf_fatura
SET karsifirma='C', kilitli='f', kdvduzenorani='+', belgeno='',
saat='14:58:07', turu='1'
WHERE _key = '72339069464736241';

Results in this:
ERROR:  attribute 11 has wrong type
DETAIL:  Table has type character varying, but query expects character varying.


We alos tried VACUUM FULL ANALYZE'ing without success. No errors
except the "Table has type character varying, but query expects
character varying." observed in the logs.

If needed, I can provide problematic full database dumps.

As a quick workaround, changing column types from VARCHAR to CHAR
seems to be working. We are still looking for a more elegant
workaround, since we have hundreds of tables.

Regards,
Ümit Öztosun

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-02-06 16:25:59
Subject: Re: BUG #2971: 8.1.7/8.2.2 break constraint checking for 'update'
Previous:From: RulDate: 2007-02-06 09:55:19
Subject: BUG #2972: Upgrading 8.2.1 to 8.2.2 changes port on some commands

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group