flyspray and php 5/postgresql weirdness, help ...

[Jonathan McKeown]

On Monday 15 September 2008 23:49:17 Bill Moran wrote:
> In response to "O. Hartmann" <ohartman at zedat.fu-berlin.de>:
> > hello,
> > for development in our scientific environment I setup
> > /usr/ports/devel/flyspray, running with a postgresql 8.3.3 backend on a
> > FreeBSD 7.1-PRE box.
> > After a successful installation, I get this error while trying to reach
> > the freshly installed server via https://host/flyspray/:
> >
> > Query {SELECT p.*, c.content AS pm_instructions, c.last_updated AS
> > cache_update FROM "flyspray_projects" p LEFT JOIN "flyspray_cache" c ON
> > c.topic = p.project_id AND c.type = 'msg' WHERE p.project_id = ?} with
> > params {1} Failed! (ERROR: operator does not exist: character varying =
> > integer LINE 3: ... LEFT JOIN "flyspray_cache" c ON c.topic =
> > p.projec... ^ HINT: No operator matches the given name and argument
> > type(s). You might need to add explicit type casts.)
> >
> > I do not know whether the shown problem is postgresql-syntactical or a
> > php 5 problem, so I guess it's a kind of both.
>
> The problem is that flyspray does not work on PG 8.3.  Use an older version
> of Postgres (I know that 8.1 works, and I suspect that 8.2 does) or help
> the FS people fix their SQL. :)

The ``problem'' (which I've encountered with RT and PostgreSQL 8.3) is that PG 
8.3 is much stricter about argument types for operators: most of the implicit 
casts have gone away. In RT's case, this manifested itself as a refusal to do 
substr() on a date (which was being used to extract the year and month).

Here, the message indicates that c.topic is a ``character varying'' and 
p.project_id is an integer, and pg is saying there isn't an equality operator 
which compares varchar with integer. It suggests casting one of the arguments 
to the type of the other.

Looking back at the PG mailing lists, this introduction of type-strictness 
seems to have been a contentious decision, but one which the PG developers 
felt was worth the pain it will cause (it will also remove a few corner cases 
where the implicit cast caused surprising results).

Jonathan