problems upgrading using a criteria with postgresql 7.4

savaki · **Joined:** Sun Jan 04, 2004 4:26 am **Posts:** 3

Using Hibernate 2.1.1 and PostgreSQL 7.4, I'm trying to use the Criteria API to do a SELECT ... FOR UPDATE on a given table. If I include a

Code:

criteria.setLockMode(LockMode.UPGRADE);

I get the following Exception

Code:

132531 [tcpConnection-8080-2] WARN util.JDBCExceptionReporter  - SQL Error: 0, SQLState: 0A000
132531 [tcpConnection-8080-2] ERROR util.JDBCExceptionReporter  - ERROR: DECLARE CURSOR ... FOR UPDATE is not supported

132531 [tcpConnection-8080-2] WARN util.JDBCExceptionReporter  - SQL Error: 0, SQLState: 0A000
132531 [tcpConnection-8080-2] ERROR util.JDBCExceptionReporter  - ERROR: DECLARE CURSOR ... FOR UPDATE is not supported

However, if I remove the criteria.setLockMode() statement, life is good. I used p6spy to check the SQL generated when the setLockMode(LockMode.UPGRADE) is used and it looks fine. I copied it into a SquirrelSQL window and the SQL executes fine. The problem appears to occur after the response comes back.

Any thoughts?

savaki · **Joined:** Sun Jan 04, 2004 4:26 am **Posts:** 3

Here's a small sample of code which will reproduce the problem:

Code:

        Criteria criteria = session.createCriteria(Account.class);
        criteria.setLockMode(LockMode.UPGRADE);
        criteria.add(Expression.eq("name", "Matt"));
        List accountList = criteria.list();
        assertNotNull(accountList);

And here's the hibernate mapping:

Code:

<hibernate-mapping>

    <class name="test.Account" table="TEST_ACCOUNT" lazy="true">
        <id name="accountId" type="long" column="ACCOUNT_ID" unsaved-value="null">
            <generator class="native"/>
        </id>

        <property name="name" column="NAME" length="255" type="string"/>
    </class>

</hibernate-mapping>

savaki · **Joined:** Sun Jan 04, 2004 4:26 am **Posts:** 3

Ah, and the Account class itself

Code:

package test;

public class Account {
    private Long accountId;
    private String name;

    public Long getAccountId() {
        return accountId;
    }

    public void setAccountId(Long accountId) {
        this.accountId = accountId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

emmanuel · **Posted:** Mon Jan 05, 2004 12:12 pm

Sounds more like a driver / DB limitation, but I'm not very good on such a subject and I've never used postgresql 7.4. Can you pack your simple testcase and post it into JIRA ?

kastor · **Joined:** Tue Jan 04, 2005 4:52 pm **Posts:** 13

Hello,

Postgresql 7.4.x and 8.x do not support updateable cursors which is the cause of the exception.

Hibernate trys to declare an updateable cursor when you specify the hibernate.jdbc.fetch_size configuration parameter.

This bug showed up for me with Hql queries when setting the lock mode manuallly.

Simply commenting out the parameter made things work.

Kastor