Use of index and BIGINT under PostgreSQL.

[Terence Haddock <la...@tripi.com>]

(I apologize if this issue has been brought up/dealt with before)

I am using OJB Version 1.0.1 in production, and recently ran into some
serious performance problems. After analyzing the queries sent to the
database, I found OJB was generating code like this:

Jan 20 10:13:33 frodo postgres[22426]: [1314-1] LOG:  duration: 1274.355
ms  statement: SELECT ButtonClickID FROM ButtonsClicked WHERE
ButtonClickID = 5073161

The ButtonsClicked table looks like this:

\d buttonsclicked
                                  Table "public.buttonsclicked"
    Column     |            Type             |
Modifiers 
---------------+-----------------------------
+----------------------------------------------------
 buttonclickid | bigint                      | not null default nextval
('UniqueIdentifier'::text)
 logid         | bigint                      |
 buttonname    | character varying(250)      |
 clickedat     | timestamp without time zone |
 url           | character varying(250)      |
 lastupdated   | timestamp without time zone |
Indexes:
    "buttonsclicked_pkey" primary key, btree (buttonclickid)
    "idx_buttonsclicked_logid" btree (logid)
Foreign-key constraints:
    "ref_buttonsclicked_logid" FOREIGN KEY (logid) REFERENCES
activitylog(logid)

PostgreSQL will ignore the indexes in this case (look at the commends on
this page http://www.postgresql.org/docs/7.4/interactive/indexes.html ).

I made a quick-and-dirty fix, which was to create a custom PostgreSQL
platform implementation which escapes the identifier, that looks like
this:

----------------------------------------------------------
package org.apache.ojb.broker.platforms;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;

public class PlatformPostgreSQLFixImpl extends PlatformPostgreSQLImpl
{
    public void setObjectForStatement(PreparedStatement ps, int index,
Object value, int sqlType) throws SQLException
    {
	if ((value instanceof Long) && (value != null) && (sqlType ==
Types.BIGINT))
	{
		ps.setString(index, ((Long)value).toString());
	} else {
		super.setObjectForStatement(ps, index, value, sqlType);
	}
    }
}
-----------------------------------------------------------

But I am wondering about the more permanent solution, and getting a fix
into OJB. Would it be worthwhile for me to make a patch for these
changes for the CVS version? Or is there some other fix? I did not see
anything in the current CVS which appears to address this issue.

Sincerely,
Terence Haddock



---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org