Problems with PostgreSQL 7.3.1

I noticed in the mailing list archives that many people are having
problems with PostgreSQL 7.3.1 and RT 2.0.15. Notwithstanding the
DBIx::SearchBuilder problems (with the 0.7x series – downgrading to the
0.6x series helps solve the “Can’t bind reference” errors) there are
still difficulties with using Postgres 7.3. I think I see what the
problem is.

It is that the “SELECT … LIMIT x,y” syntax is deprecated in the 7.3.x
series of PostgreSQL. The correct syntax should be “SELECT … LIMIT x
OFFSET y”. This causes the comment recording to fail.

I am not really familiar with the RT code but I can poke around and see
if I can cough up a patch. Others who are more familiar with it, feel
free to beat me to it :slight_smile:

  • Julian

– Julian C. Dunn, B.A.Sc. jdunn@verticalscope.com
– Senior Software Developer / UNIX Systems Administrator
– VerticalScope Inc.
– 111 Peter St., Suite 700, Toronto, ON
– Tel: (416) 341-8950 x236 Fax: (416) 341-8959
– PGP Key: 0x4EE2041F

I noticed in the mailing list archives that many people are having
problems with PostgreSQL 7.3.1 and RT 2.0.15. Notwithstanding the
DBIx::SearchBuilder problems (with the 0.7x series – downgrading to the
0.6x series helps solve the “Can’t bind reference” errors) there are
still difficulties with using Postgres 7.3. I think I see what the
problem is.

See a problem I had with DBIx::SearchBuilder:
http://fsck.com/rt2/Ticket/Display.html?id=1624

As far as I am aware this hasn’t been integrated; I don’t know if
development is now on Aegis or what, but I haven’t seen any changes in
CVS for a long time.

I am not really familiar with the RT code but I can poke around and see
if I can cough up a patch. Others who are more familiar with it, feel
free to beat me to it :slight_smile:

It might be an idea to just do an RT search on things, and watch the SQL
come out the other end (I turned Postgres debugging on). This is how I
found the above problem, anyway.

Robie.
Robie Basak robie@principle.co.uk
Northern Principle Limited

I noticed in the mailing list archives that many people are having
problems with PostgreSQL 7.3.1 and RT 2.0.15. Notwithstanding the
DBIx::SearchBuilder problems (with the 0.7x series – downgrading to the
0.6x series helps solve the “Can’t bind reference” errors) there are
still difficulties with using Postgres 7.3. I think I see what the
problem is.

See a problem I had with DBIx::SearchBuilder:
http://fsck.com/rt2/Ticket/Display.html?id=1624

It seems the problem I’m having is different than what you were having.
In WebRT’s error log I see this:

[Mon Feb 10 16:08:14 2003] [error] [client 209.82.14.131] FastCGI: server “/usr/
local/rt2.0.15/bin/mason_handler.fcgi” stderr: DBD::Pg::st execute failed: ERROR
: pg_atoi: zero-length string at /usr/local/lib/perl5/site_perl/5.6.1/DBIx/Sear
chBuilder/Handle.pm line 365, line 47.
[Mon Feb 10 16:08:14 2003] [error] [client 209.82.14.131] FastCGI: server “/usr/
local/rt2.0.15/bin/mason_handler.fcgi” stderr: RT::Handle=HASH(0x8a64d38) couldn
’t execute the query 'INSERT INTO Transactions (Creator, OldValue, TimeTaken, Ti
cket, Data, NewValue, Field, Created, Type) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)'E
RROR: pg_atoi: zero-length string

I did some digging and it looks like somewhere along the line,
“TimeTaken” is being set to undef (or is never defined in the first
place) so the whole thing falls apart.

I will try your patch, but somehow I’m not sure it will solve the
problem…

  • Julian

– Julian C. Dunn, B.A.Sc. jdunn@verticalscope.com
– Senior Software Developer / UNIX Systems Administrator
– VerticalScope Inc.
– 111 Peter St., Suite 700, Toronto, ON
– Tel: (416) 341-8950 x236 Fax: (416) 341-8959
– PGP Key: 0x4EE2041F

It seems the problem I’m having is different than what you were having.
In WebRT’s error log I see this:

[Mon Feb 10 16:08:14 2003] [error] [client 209.82.14.131] FastCGI: server “/usr/
local/rt2.0.15/bin/mason_handler.fcgi” stderr: DBD::Pg::st execute failed: ERROR
: pg_atoi: zero-length string at /usr/local/lib/perl5/site_perl/5.6.1/DBIx/Sear
chBuilder/Handle.pm line 365, line 47.
[Mon Feb 10 16:08:14 2003] [error] [client 209.82.14.131] FastCGI: server “/usr/
local/rt2.0.15/bin/mason_handler.fcgi” stderr: RT::Handle=HASH(0x8a64d38) couldn
’t execute the query 'INSERT INTO Transactions (Creator, OldValue, TimeTaken, Ti
cket, Data, NewValue, Field, Created, Type) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)'E
RROR: pg_atoi: zero-length string

I did some digging and it looks like somewhere along the line,
“TimeTaken” is being set to undef (or is never defined in the first
place) so the whole thing falls apart.

I will try your patch, but somehow I’m not sure it will solve the
problem…

It looks to me like the Postgres interface doesn’t like undef being
given as a value. No idea why, I’d have thought that’d be interpreted as
an SQL NULL. You could try something like:

$TimeTaken = 0 unless defined $TimeTaken;

At the appropriate place (I haven’t seen the code, so know nothing about
variable names in use though).

HTH,
Robie.
Robie Basak robie@principle.co.uk
Northern Principle Limited

“RB” == Robie Basak robie@principle.co.uk writes:

I did some digging and it looks like somewhere along the line,
“TimeTaken” is being set to undef (or is never defined in the first
place) so the whole thing falls apart.

I will try your patch, but somehow I’m not sure it will solve the
problem…

RB> It looks to me like the Postgres interface doesn’t like undef
RB> being given as a value. No idea why, I’d have thought that’d be
RB> interpreted as an SQL NULL. You could try something like:

No, postgres’ DBI handles that just fine. The problem is that
starting with Postgres 7.3, it is more pedantic about ANSI SQL
compliance, and RT tries to pass an empty string for a numeric field
and assume it gets converted to zero, where in fact that does not
happen.

You need to stick with Postgres 7.2 for now. This is the official
word from Jesse as posted to this list a while back.