hibernate mapping for postgresql "timestamp without time zone"?

Question

I'm trying to map java.util.Date to postgresql timestamp without time zone type. I'm using a custom sql-insert statement (because the table is partitioned and postgresql won't return number of rows updated on a normal insert), and I keep getting an error saying the function does not exist. I suspect this is due to a problem mapping these Date fields.

From the hibernate mapping:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"&gt;
<hibernate-mapping default-lazy="false">
    <class ...snip...
        <property name="dateCreated" type="timestamp">
            <column name="DATE_CREATED"/>
        </property>
        <property name="dateUpdated" type="timestamp">
            <column name="DATE_UPDATED"/>
        </property>
...snip...
        <sql-insert callable="true">{call insert_wal (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}</sql-insert>
    </class>
</hibernate-mapping>

From the application log:

Hibernate: 
    {call insert_wal (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}
12/06/09 17:22:15.409  WARN hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: null
12/06/09 17:22:15.425 ERROR hibernate.util.JDBCExceptionReporter - Batch entry 0 select * from insert_wal (foo, 439, ...snip... 2009-06-12 17:22:15.315000 -07:00:00, 2009-06-12 17:22:15.378000 -07:00:00, ...snip...) as result was aborted.  Call getNextException to see the cause.
12/06/09 17:22:15.425  WARN hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 42883
12/06/09 17:22:15.425 ERROR hibernate.util.JDBCExceptionReporter - ERROR: function insert_wal(character varying, integer, ...snip... unknown, unknown, ...snip...) does not exist
12/06/09 17:22:15.425 ERROR event.def.AbstractFlushingEventListener - Could not synchronize database state with session

(The Exception returned from getNextException referenced in the first ERROR line simply repeats the second ERROR line with a stack trace.) As you can see, for some reason the function signature JDBC is looking for has "unknown" as the data types where the timestamps go. I've tried every combination of property type and column sql-type I can think of, but it shows up as "unknown" every time. Here's the signature of the function definition in the DB:

CREATE OR REPLACE FUNCTION insert_wal(p_action character varying, p_partner_id numeric, ...snip... p_date_created timestamp without time zone, p_date_updated timestamp without time zone, ...snip...

The only other differences are that where there's a "numeric" in the function definition, the ERROR line shows various types like "integer", "double precision", and "bigint"; and one parameter in the function definition is type "text", where the error shows "character varying".

I can produce a similar error by calling the function in pgAdminIII, except that it's the string values (e.g. 'foo' with quotes) that are "unknown", while the date values (I just used now()) are treated as "timestamp with time zone".

So: am I doing something wrong in my hibernate mapping? Is it a problem with the function?

Answer 1

I don't know Hibernate enough to comment on that part, but you could perhaps work around the error by overriding the datatypes in the INSERT statement:

<sql-insert callable="true">{call insert_wal (?::text, ?::numeric, <snip>?::timestamp, )::timestamp, <snip>)}</sql-insert>

You probably don't need to override all the fields, of course, just those causing problems.