Very strange error

-----Original Message-----
From: pgsql-general-owner@postgresql.org

[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ümit Öztosun

Sent: Tuesday, February 06, 2007 2:50 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Very strange error

Hi,

Today suddenly our PostgreSQL 8.1 server started producing strange errors.

Error occurs during simple updates:

"Table has type character varying, but query expects character varying."

We are still trying to figure out the problem. I've googled for this error

but found nothing. Any insight?

Platform: Ubuntu Dapper, Running PostgreSQL 8.1 (vanilla packages from

Ubuntu), UTF-8 and non-US locale.

Regards,
--
Ümit Öztosun

Have you installed any updates for PostgreSQL? The latest security update
fixed something with type checks or so.
I've seen the same error message also on the BUGS mailing list concerning a
broken CHECK constraint on a table row.
Perhaps this is the cause of the error messages.

Greetings,
Matthias

Have you installed any updates for PostgreSQL? The latest security update
fixed something with type checks or so.
I've seen the same error message also on the BUGS mailing list concerning
a
broken CHECK constraint on a table row.
Perhaps this is the cause of the error messages.

Well, I've just dumped old data, installed v8.2.2 from sources, restored
data. Unfortunately the error remains the same and we have no ideas left.
Error is again:

"Table has type character varying, but query expects character varying."

The error is about a varchar column, with no other special attributed. It
was working flawlessly for a long time.

Any help is appreciated.

Regards,
Ümit Öztosun

Have you installed any updates for PostgreSQL? The latest security update
fixed something with type checks or so.
I've seen the same error message also on the BUGS mailing list concerning
a
broken CHECK constraint on a table row.
Perhaps this is the cause of the error messages.

Well, I've just dumped old data, installed v8.2.2 from sources, restored
data. Unfortunately the error remains the same and we have no ideas left.
Error is again:

"Table has type character varying, but query expects character varying."

The error is about a varchar column, with no other special attributed. It
was working flawlessly for a long time.

Any help is appreciated.

Regards,
Ümit Öztosun

When does this error crop up? What is the query? Does this select
involve more than one table, or does it involve any homemade
functions? Or overriden functions?

On Feb 6, 2007, at 9:58 AM, Ümit Öztosun wrote:

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ümit Öztosun
Sent: Tuesday, February 06, 2007 3:59 PM
To: Matthias.Pitzl@izb.de
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Very strange error

Have you installed any updates for PostgreSQL? The latest security update
fixed something with type checks or so.
I've seen the same error message also on the BUGS mailing list concerning a
broken CHECK constraint on a table row.
Perhaps this is the cause of the error messages.

Well, I've just dumped old data, installed v8.2.2 from sources, restored
data. Unfortunately the error remains the same and we have no ideas left.
Error is again:

"Table has type character varying, but query expects character varying."

The error is about a varchar column, with no other special attributed. It
was working flawlessly for a long time.

Any help is appreciated.

Regards,
Ümit Öztosun

Hello there!

I suggest to post this on the BUGS mailing list. As said before, there has
been some other mail with exact the same error message and with the latest
version something concerning data type checks had been fixed.

Greetings,
Matthias

Hello there!

I suggest to post this on the BUGS mailing list. As said before, there has
been some other mail with exact the same error message and with the latest
version something concerning data type checks had been fixed.

Greetings,
Matthias

I'm writing a seperate e-mail to the pgsql-bugs mailing list. Just in case
here is more detailed info:

Tablo "
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.

Server Info:
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) and 8.2.2 (compiled from sources)

Tried dumping and restoring and VACUUM FULL ANALYZE'ing, without success. No
errors except the "Table has type character varying, but query expects
character varying." in the log.

Regards,
Ümit Öztosun

On Tue, Feb 06, 2007 at 10:09:16AM -0500, Michael Slattery wrote:

When does this error crop up? What is the query? Does this select
involve more than one table, or does it involve any homemade
functions? Or overriden functions?

My application broke in a big way with the security update to 8.2.2 so
I hope this is a bug in 8.2.2 and not an intentional breakage of
backwards compatibility in a security update ;).

Actually I'm using the REL8_2_STABLE branch in CVS which may be a bit
more advanced than the plain 8.2.2, but still it's supposedly a stable
branch.

The easiest way for me to reproduce is this:

cpushare=> create table x (x NUMERIC(28,2) CHECK(x >= 0));
CREATE TABLE
cpushare=> insert into x values (0);
INSERT 0 1
cpushare=> update x set x = 0;
ERROR: attribute 1 has wrong type
DETAIL: Table has type numeric, but query expects numeric.
cpushare=>

Comments welcome. Thanks!

Andrea Arcangeli wrote:

On Tue, Feb 06, 2007 at 10:09:16AM -0500, Michael Slattery wrote:

When does this error crop up? What is the query? Does this select
involve more than one table, or does it involve any homemade
functions? Or overriden functions?

My application broke in a big way with the security update to 8.2.2 so
I hope this is a bug in 8.2.2 and not an intentional breakage of
backwards compatibility in a security update ;).

Actually I'm using the REL8_2_STABLE branch in CVS which may be a bit
more advanced than the plain 8.2.2, but still it's supposedly a stable
branch.

The easiest way for me to reproduce is this:

cpushare=> create table x (x NUMERIC(28,2) CHECK(x >= 0));
CREATE TABLE
cpushare=> insert into x values (0);
INSERT 0 1
cpushare=> update x set x = 0;
ERROR: attribute 1 has wrong type
DETAIL: Table has type numeric, but query expects numeric.
cpushare=>

Comments welcome. Thanks!

This is a known bug in 8.2.2 and we are discussing methods of
distributing the fix as quickly as possible.

-- 
  Bruce Momjian   bruce@momjian.us
  Homepage        http://momjian.us
  EnterpriseDB    http://www.enterprisedb.com
  + If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

Andrea Arcangeli wrote:

Actually I'm using the REL8_2_STABLE branch in CVS which may be a bit
more advanced than the plain 8.2.2, but still it's supposedly a stable
branch.

The easiest way for me to reproduce is this:

cpushare=> create table x (x NUMERIC(28,2) CHECK(x >= 0));
CREATE TABLE
cpushare=> insert into x values (0);
INSERT 0 1
cpushare=> update x set x = 0;
ERROR: attribute 1 has wrong type
DETAIL: Table has type numeric, but query expects numeric.
cpushare=>

Comments welcome. Thanks!

This is a known bug in 8.2.2 and we are discussing methods of
distributing the fix as quickly as possible.

The fix is already in the REL8_2_STABLE branch, so Andrea can certainly
update and confirm if his problem is fixed.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Tue, Feb 06, 2007 at 01:19:28PM -0500, Bruce Momjian wrote:

This is a known bug in 8.2.2 and we are discussing methods of
distributing the fix as quickly as possible.

Ok great! Take your time, thanks.

On Tue, Feb 06, 2007 at 03:23:29PM -0300, Alvaro Herrera wrote:

The fix is already in the REL8_2_STABLE branch, so Andrea can certainly
update and confirm if his problem is fixed.

Confirmed, after the last cvs checkout it works fine. thanks!