Strange "Table has type character varying, but query expects character varying" errors
От | Ümit Öztosun |
---|---|
Тема | Strange "Table has type character varying, but query expects character varying" errors |
Дата | |
Msg-id | d95439ec0702060757h15ab54f3wd3b64d50c588279c@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Strange "Table has type character varying, but query expects character varying" errors
Re: Strange "Table has type character varying, but query expects character varying" errors |
Список | 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 | 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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Tomas SzepeДата:
Сообщение: 8.2.2 regression with indices on user functions, 8.2.1 works
Следующее
От: "William ZHANG"Дата:
Сообщение: BUG #2970: "FETCH ABSOLUTE -1" from a holdable cursor failed