Re: Upgrade to pg and driver version 8.x code compatibilities

[Oliver Jowett <oliver(at)opencloud(dot)com>]

Tom Arthurs wrote:

> ERROR: column "job_experience" is of type smallint but expression is of
> type character varying

Most likely you are using setString() where you should be using setShort().

> 1.  What changed between the driver versions that generate this error?

The driver started to use server-side prepared statements for
parameterization of queries (i.e. the driver translates ? to $n in the
main query string, and sends the actual parameter values out-of-band
from the query itself). One sideeffect of this is that parameters are
more strongly typed than in the 7.4.x versions where the driver would do
literal parameter substitution into the query string before sending it
to the backend. Also, you can use parameters in fewer places (they must
fit the backend's idea of where parameterizable expressions are allowed)
-- e.g. see the recent thread about "ORDER BY ?" changing behaviour with
the newer driver.

> 2.  What is the downside of continuing to use the 7.x version of the
> driver -- or are there better alternatives (patch, new version, etc).  I
> am using build 311 of the driver.

Most active development happens on the 8.0 version; 7.4.x is maintained
for bugfixes but that's about it, you won't get the benefit of any
performance improvements or added features that go into 8.0. Also, the
7.4.x driver won't necessarily work with servers >= 8.0.

In the longer term, the 7.4.x version will eventually become unmaintained.

-O