Unexpected row count when using PostgreSQL trigger and hibernate

Question

I have an application which uses Hibernate and I have to include a trigger to copy to a history table all rows modified or deleted in a table. After including the PostgreSQL trigger the application doesn't work properly and is giving this error:

org.springframework.orm.hibernate3.HibernateOptimisticLockingFailureException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; nested exception is org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1

After googling for a while I've discovered that this error comes because Hibernate does a checking of the affected rows by the sql update and the returned rows aren't the expected ones because the trigger has also done an update. I've seen that this could be fixed by turning off rowcount on the trigger. But PostgreSQL doesn't seem to have an alternative for 'set nocount on'.

How can I solve the problem in a trigger of PostgreSQL like the shown below?

Thanks

CREATE OR REPLACE FUNCTION my_trigger_fnc() RETURNS TRIGGER AS $my_trigger_fnc$
    DECLARE
        nowDate timestamp := now();
    BEGIN

        INSERT INTO historial_table (
                id,
                date_now,
                id_mytable,
                --some other fields
                ...
                ) 
        VALUES (              
                nextVal('historial_table_seq'),
                nowDate,
                OLD.id_mytable
                --some other fields
                ...
               );
        RETURN NEW;
    END;
$my_trigger_fnc$ LANGUAGE plpgsql;

CREATE TRIGGER my_trigger BEFORE UPDATE OR DELETE 
    ON my_table FOR EACH ROW 
    EXECUTE PROCEDURE my_trigger_fnc();

UPDATE: the tables are like this one:

CREATE TABLE historial_table(
    id integer,
    date_now timestamp NOT NULL,
    id_mytable integer NOT NULL,
    nserie character varying(255),
    idstore integer,
    idmodel integer,
    automatic boolean,
    broken boolean,
    idAlb integer,
    idInc integer,
    id_item integer,
    date_assign timestamp,
    PRIMARY KEY (id)  
);

CREATE TABLE my_table(
    id_mytable integer NOT NULL,
    nserie character varying(255),
    idstore integer,
    idmodel integer,
    automatic boolean,
    broken boolean,
    idAlb integer,
    idInc integer,
    id_item integer,
    date_assign timestamp,
    PRIMARY KEY (id_mytable)  
);

What is the query that fired the trigger? Dooes the history-table have a unique secondary index on {original_key, date} ? BTW: "date" is a bad name for a column, since it is a typ name. BTW: Maybe you should add the table-definitions (including constraints) to your question. — wildplasser, Nov 15 '11 at 10:35
@wildplasser indeed the real table that field isn't called in this way. I changed it here so you could understand it better because names are not in english. If I do an update/delete directly over the table the trigger works perfectly. The problem is when I call delete() method on EntityManager over the entity saved in my_table. — Javi, Nov 15 '11 at 10:43
Thanks. There is no secondary index on historial_table {id_mytable,date_now}? — wildplasser, Nov 15 '11 at 11:02

wildplasser · Accepted Answer · 2014-12-02T00:16:30.440

5

The "return NEW;" is suspicious on DELETE. Might confuse the rowcount (NULL==zero?) http://developer.postgresql.org/pgdocs/postgres/plpgsql-trigger.html (very generic; not intended to insult you ...) The switch on TG_OP is probably what you need.

edited Dec 02 '14 at 00:16

answered Nov 15 '11 at 11:20

wildplasser

43,142
8
66
109

You're welcome. At first I was confused by the history: now() is frozen at transaction begin, and multiple triggers can be fired within on transaction, creating duplicate {timestamp, original_key} pairs in the history file. It needs additional logic if you would want to keep only one entry per transaction in the history. – wildplasser Nov 15 '11 at 12:52

Unexpected row count when using PostgreSQL trigger and hibernate

1 Answers1