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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
