PostgreSQL Howto

Turbine comes with a default User implementation which utilizes some libraries, and code that are not 100% compatible with PostgreSQL . This document will explain what you need to get the defaut Turbine User Implementation running with PostgreSQL. The user management code is only a small part of Turbine, and the default implementation is easily replacable. Rather than following the steps in this document, you can always create your own User implementation which does not require large object support.

JDBC Driver

First of all, the TurbineUser class which comes with Turbine uses a hashtable to store data relevant to the user. Any data in this hashtable which does not map to one of the columns in the visitor table is written to a large object field in the database. Database actions in the TurbineUserPeer and BasePeer classes are done through the Village API . The village API uses the ResultSetMetaData returned by your JDBC driver to determine the types of the columns in a SQL result set. Unfortunately, in PostgreSQL large objects are referenced using an OID column (which is a pointer to the data), and the metadata in the JDBC driver says that columns of type OID are java.sql.Types.INTEGER.

There is also a bug in the released versions of the PostgreSQL which causes problems when reading and writing Timestamps. This is fixed in CVS. So to run Turbine with PostgreSQL you need to get the latest JDBC driver code from the PostgreSQL CVS server: :pserver:anoncvs@postgresql.org:/home/projects/pgsql/cvsroot the password is postgresql. Before compiling the driver though, you must apply a patch so the metadata says that OID columns are java.sql.Types.VARBINARY, not java.sql.Types.INTEGER. Important: If you need the metadata to report that OID columns are integers for another application, this patch will break that app. If you've never heard of an OID before, or you only use OID's to reference large objects, you don't need to worry about it.

Patch

This patch may or may not be necessary. If you are having problems with the driver, please try it out. You may also wish to try upgrading to a more recent version of the Postgrest driver and also make sure that you are using the latest released version of Village.

/home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/Field.java,v
retrieving revision 1.1
diff -r1.1 Field.java
118c118
<     "int4","oid",
---
>     "int4",
129c129,130
<     "abstime","timestamp"
---
>     "abstime","timestamp",
>     "oid"
141c142
<     Types.INTEGER,Types.INTEGER,
---
>     Types.INTEGER,
152c153,154
<     Types.TIMESTAMP,Types.TIMESTAMP
---
>     Types.TIMESTAMP,Types.TIMESTAMP,
>     Types.VARBINARY

After applying that patch, compile the driver, and you should be ready to go. One more problem though is that all actions involving large objects in PostgreSQL require transactions. There is code in CVS now which automatically sets up the transaction if necessary, so make sure you're running the recent snapshots.