**URGENT** PostgreSQL drivers broken for PG 8.1

[David Pollak]

Folks,

We changed the way that the PostgreSQL retrieved primary keys from INSERTs.  Turns out that the new code does not work with PG 8.1.

You will see something like:

 ERROR: syntax error at or near "RETURNING"
org.postgresql.util.PSQLException: ERROR: syntax error at or near "RETURNING"
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1559)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:193)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:255)
    at net.liftweb.mapper.PostgreSqlDriver$.performInsertWithPK(Driver.scala:207)
    at net.liftweb.mapper.DriverType.performInsert(Driver.scala:68)
    at net.liftweb.mapper.MetaMapper$$anonfun$12.apply(MetaMapper.scala:676)
    at net.liftweb.mapper.MetaMapper$$anonfun$12.apply(MetaMapper.scala:622)

If you are running PG 8.1 and Lift.  We will be fixing the problem ASAP.

Thanks,

David

--
Lift, the simply functional web framework http://liftweb.net
Beginning Scala http://www.apress.com/book/view/1430219890
Follow me: http://twitter.com/dpp
Git some: http://github.com/dpp

[Derek Chen-Becker]

A fix has been pushed to master and 1.0_maint. Give hudson some time to build 1.1-SNAPSHOT and I'll push a 1.0.1-SNAPSHOT release from here. Apologies for the issue.

Derek

[David Pollak]

On Thu, Aug 20, 2009 at 2:31 PM, Derek Chen-Becker <dchen...@gmail.com> wrote:

A fix has been pushed to master and 1.0_maint. Give hudson some time to build 1.1-SNAPSHOT and I'll push a 1.0.1-SNAPSHOT release from here. Apologies for the issue.

Thanks for your wicked fast response!
 

[Derek Chen-Becker]

I should have a real fix out pretty soon that will take into account the server version. Actually, the "current" behavior of calling lastval() is broken if columns other than the primary key are auto-generated. I think that this is pretty rare but I'd still like to look into fixing it.

Derek

[Derek Chen-Becker]

It gets even better. I put together some code to determine driver behavior and it seems to work. The problem is that now I'm testing against PG 8.0, which I just found out doesn't support the "lastval()" function :(. This begs the question, can we document somewhere what versions of the databases that we support? I could do it in Scaladoc on the DriverTypes classes, but I'd at least like to write somewhere that we only support versions X, Y and Z. If we want to support PG 8.0 I'm going to have to figure out how to get the generated value some other way...

Derek

[Derek Chen-Becker]

I hate talking over myself, but it seems like I could kill two birds with one stone by uzing the index names to fire off currval calls instead. That would fix this for all PG server 8.0-8.1 and it would also solve the problem of retrieving values when there is more than one generated key.

Derek

[David Pollak]

On Fri, Aug 21, 2009 at 9:02 AM, Derek Chen-Becker <dchen...@gmail.com> wrote:

It gets even better. I put together some code to determine driver behavior and it seems to work. The problem is that now I'm testing against PG 8.0, which I just found out doesn't support the "lastval()" function :(. This begs the question, can we document somewhere what versions of the databases that we support?

That would be good.  Also, if we can fail early by throwing an exception with a very detailed message, that'd be great!
 

I could do it in Scaladoc on the DriverTypes classes, but I'd at least like to write somewhere that we only support versions X, Y and Z. If we want to support PG 8.0 I'm going to have to figure out how to get the generated value some other way...

Maybe on a wiki page.

Thanks for your diligent work on this!

David
 

[Derek Chen-Becker]

OK, this is pushed into 1.1-SNAPSHOT and 1.0.1-SNAPSHOT.