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

Started by Ümit Öztosunabout 19 years ago5 messagesbugs
Jump to latest
#1Ümit Öztosun
umit@ly.com.tr

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Ümit Öztosun (#1)
Re: Strange "Table has type character varying, but query expects character varying" errors

"=?ISO-8859-1?Q?=DCmit_=D6ztosun?=" <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

#3Ümit Öztosun
umit@ly.com.tr
In reply to: Tom Lane (#2)
Re: Strange "Table has type character varying, but query expects character varying" errors

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.4 on 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

#4Martin Pitt
martin.pitt@ubuntu.com
In reply to: Ümit Öztosun (#1)
Re: Strange "Table has type character varying, but query expects character varying" errors

Hi Ümit,

Ümit Öztosun [2007-02-06 17:57 +0200]:

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.

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
--
Martin Pitt http://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.org

#5Martin Pitt
martin@piware.de
In reply to: Martin Pitt (#4)
Re: Strange "Table has type character varying, but query expects character varying" errors

Hi Ümit,

Ümit Öztosun [2007-02-06 17:57 +0200]:

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.

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
--
Martin Pitt http://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.org