Hi,
I’m running RT 3.0.8 on Debian Linux. DBMS is Postgres. Recently i received
this error:
Feb 15 22:27:17 nero postgres[25682]: [3] ERROR: value too long for type
character varying(200)
Feb 15 23:27:17 nero RT: DBD::Pg::st execute failed: ERROR: value too long
for type character varying(200) at
/usr/share/perl5/DBIx/SearchBuilder/Handle.pm line 410.
(/usr/share/perl5/RT.pm:247)
Feb 15 23:27:17 nero RT: Create failed: 0 / 0 / Ticket could not be created
due to an internal error (/usr/share/perl5/RT/Interface/Email.pm:684)
I traced it down to the subject field of the incoming e-mail being > 200
bytes, and therefore Postgres erroring out. I did a quick hack to fix this
in /usr/share/perl5/RT/Interface/Email.pm (see below patch - against 3.0.8).
I didn’t see anything of this kind in 3.0.9, so I assume the bug is still
there. Can this be included, or fixed in an other (better?) way?
Thanks,
— Email.pm.orig 2004-02-17 15:40:36.000000000 +0100
+++ Email.pm 2004-02-17 15:44:43.000000000 +0100
@@ -667,6 +667,9 @@
);
}
-
# fix for subjects that are too long
-
if (length($Subject) > 200) { $Subject = substr($Subject,0,200); }
my ( $id, $Transaction, $ErrStr ) = $Ticket->Create(
Queue => $SystemQueueObj->Id,
Subject => $Subject,
Kouprie, Robbert wrote:
I traced it down to the subject field of the incoming e-mail being > 200
bytes, and therefore Postgres erroring out.
This is a known problem and affects other fields as well. You should
fix it by changing the type of table columns (change all occurences of
type VARCHAR to type TEXT).
On PostgreSQL, there is no performance difference between VARCHAR and
TEXT; operations on both types are implemented by the same code, and
PostgreSQL can’t exploit fixed-length records (unlike other databases).
fix it by changing the type of table columns (change all occurences of
type VARCHAR to type TEXT).
Or just remove the size and leave them as VARCHAR of unspecified
length. Only way to do that is drop/reload database – I know of no
ALTER command to accomplish that.
Hi Florian,
Thanks for the explanation.
Someone from the RT dev team might want to fix the shipped Postgres database
schema?
Regards,
– Robbert
Hi Florian,
Thanks for the explanation.
Someone from the RT dev team might want to fix the shipped Postgres database
schema?
I’m more tempted to go for a truncation solution, unless someone can
explain to me what varchars without specified lengths do to index
performance 
Jesse
Jesse Vincent wrote:> On Wed, Feb 18, 2004 at 02:49:54PM +0100, Kouprie, Robbert wrote:
Hi Florian,
Thanks for the explanation.
Someone from the RT dev team might want to fix the shipped Postgres database
schema?
I’m more tempted to go for a truncation solution, unless someone can
explain to me what varchars without specified lengths do to index
performance
There’s no index on tickets.subject. 
But I see your point,
users.name is such a case.
Last time I looked at the PostgreSQL code, VARCHAR was implemented as
TEXT plus length checking. Shall I recheck?
(Unfortunately, I don’t have an RT3 instance sufficiently large for
benchmarking.)
But all other schema’s also use fixed varchar’s. So truncation is definately
needed, I’d say.
So, to start somewhere, may I vote for inclusion of my original patch for
the case where Tickets.Subject overflows?
– Robbert
-----Oorspronkelijk bericht-----
Van: Florian Weimer [mailto:fw@deneb.enyo.de]
Verzonden: woensdag 18 februari 2004 20:47
Aan: Jesse Vincent
CC: Kouprie, Robbert; ‘rt-devel@lists.bestpractical.com’
Onderwerp: Re: [rt-devel] [BUG] Email bounces when subject > 200 bytes
Jesse Vincent wrote:
Hi Florian,
Thanks for the explanation.
Someone from the RT dev team might want to fix the shipped Postgres
database
schema?
I’m more tempted to go for a truncation solution, unless someone can
explain to me what varchars without specified lengths do to index
performance
There’s no index on tickets.subject. But I see your point,
users.name is such a case.
Last time I looked at the PostgreSQL code, VARCHAR was implemented as
TEXT plus length checking. Shall I recheck?
(Unfortunately, I don’t have an RT3 instance sufficiently large for
benchmarking.)
But all other schema’s also use fixed varchar’s. So truncation is
definately
needed, I’d say.
So, to start somewhere, may I vote for inclusion of my original patch
for
the case where Tickets.Subject overflows?
I agree that truncation is the right answer, but I think that we should
be doing it at a lower layer (searchbuilder) so as to catch all the
other cases where a varchar might overflow. SearchBuilder::Record has
access to the length of each column through the _Accessible routine.
What do you think about doing this in the generic insert/update code?
Jesse
PGP.sig (186 Bytes)
Jesse Vincent wrote:
I agree that truncation is the right answer, but I think that we should
be doing it at a lower layer (searchbuilder) so as to catch all the
other cases where a varchar might overflow. SearchBuilder::Record has
access to the length of each column through the _Accessible routine.
What do you think about doing this in the generic insert/update code?
Sounds good. It won’t apply to TEXT columns, so I still can use them if
I want to.
Current mail filters: many dial-up/DSL/cable modem hosts, and the
following domains: atlas.cz, bigpond.com, freenet.de, hotmail.com,
libero.it, netscape.net, postino.it, tiscali.co.uk, tiscali.cz,
tiscali.it, voila.fr, wanadoo.fr, yahoo.com.