Thread: Strange "Table has type character varying, but query expects character varying" errors
Strange "Table has type character varying, but query expects character varying" errors
From
"Ümit Öztosun"
Date:
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 |
timestampwith time zone |_site | smallint | default 0turu
|smallint | default 0fisno | character varying(50) | default
''::character varyingtarih | date |saat | time without
timezone |belgeno | character varying(50) | default
''::character varyingbelgeno2 | 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 0karsifirma | character(1) | default ''::bpchar_key_karsi_fatura
| bigint | default 0_key_scf_carikart | bigint | default
0_key_scf_kasa | bigint | default 0kasafisno | character varying(16)
| default
''::character varyingsevkadresi1 | character varying(128) | default
''::character varyingsevkadresi2 | character varying(128) | default
''::character varyingsevkadresi3 | 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 0dovizkuru | numeric(15,10) | default
0.0aciklama1 | character varying(128) | default
''::character varyingaciklama2 | character varying(128) | default
''::character varyingaciklama3 | character varying(128) | default
''::character varyingtoplammasraf | numeric(20,10) | default 0.0toplamindirim |
numeric(20,10) | default 0.0toplam | numeric(20,10) | default 0.0
toplamotv | numeric(20,10) | default 0.0toplamkdv | numeric(20,10)
| default 0.0net | numeric(20,10) | default 0.0toplammasrafdvz |
numeric(20,10) | default 0.0toplamindirimdvz | numeric(20,10) | default 0.0toplamdvz
| numeric(20,10) | default 0.0toplamotvdvz | numeric(20,10) | default
0.0toplamkdvdvz | numeric(20,10) | default 0.0netdvz | numeric(20,10)
| default 0.0iptal | character(1) | default '-'::bpcharkilitli
| character(1) | default ''::bpcharkdvduzenorani | character(1) | default
'+'::bpcharkdvduzentutari | 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::bigintraporlamadovizkuru | numeric(9,5) | default 1ekmaliyet | numeric(16,7)
| default 0.0_key_muh_masrafmerkezi | bigint | default 0ortalamavade |
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
Re: Strange "Table has type character varying, but query expects character varying" errors
From
Tom Lane
Date:
"Ümit Öztosun" <umit@ly.com.tr> writes:
> 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.
No they're not ... at least not anything I would label 8.1.4. You
should have a word with Ubuntu about their labeling practices.
regards, tom lane
Re: Strange "Table has type character varying, but query expects character varying" errors
From
"Ümit Öztosun"
Date:
> No they're not ... at least not anything I would label 8.1.4. You > should have a word with Ubuntu about their labeling practices. > > regards, tom lane select VERSION(); version ------------------------------------------------------------------------------------------------------------PostgreSQL 8.1.4on i486-pc-linux-gnu, compiled by GCC gcc-4.0.gcc-opt (GCC) 4.0.3 (Ubuntu 4.0.3-1ubuntu5) (1 lines) IIRC, they were installed from packages named "postgresql-8.1", which does not seem to be vanilla postgresql packages. Regards -- Ümit Öztosun
Re: Strange "Table has type character varying, but query expects character varying" errors
From
Martin Pitt
Date:
Hi =DCmit, =DCmit =D6ztosun [2007-02-06 17:57 +0200]: > Today two of our production servers developed the following error(s): >=20 > ERROR: attribute X(semi-random number here) has wrong type > DETAIL: Table has type character varying, but query expects character=20 > varying. >=20 > They were running PostgreSQL 8.1.4 on Ubuntu Dapper, dual-core Intel=20 > Machines. Right, that is the same issue I already reported to this list, and is already reported in Launchpad, too: https://launchpad.net/bugs/83505 Martin --=20 Martin Pitt http://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org
Re: Strange "Table has type character varying, but query expects character varying" errors
From
Martin Pitt
Date:
Hi =DCmit, =DCmit =D6ztosun [2007-02-06 17:57 +0200]: > Today two of our production servers developed the following error(s): >=20 > ERROR: attribute X(semi-random number here) has wrong type > DETAIL: Table has type character varying, but query expects character=20 > varying. >=20 > They were running PostgreSQL 8.1.4 on Ubuntu Dapper, dual-core Intel=20 > Machines. Right, that is the same issue I already reported to this list, and is already reported in Launchpad, too: https://launchpad.net/bugs/83505 Martin --=20 Martin Pitt http://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org