使用jpa进行表分区将引发OptimisticLockException [英] table partitioning with jpa throws OptimisticLockException

问题描述

我的数据库中有一个log_table，它已根据分区文档.我有一个根据日期将记录插入分区表的函数，以及一个调用该函数的触发器，就像在文档中一样.

I have a log_table in my db that is partitioned according to partitioning docs. I have a function that inserts records to partition table depending on date and a trigger that calls that function, like in the documentation.

触发示例

CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement
    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

功能示例

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.logdate >= DATE '2006-02-01' AND
         NEW.logdate < DATE '2006-03-01' ) THEN
        INSERT INTO measurement_y2006m02 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
            NEW.logdate < DATE '2006-04-01' ) THEN
        INSERT INTO measurement_y2006m03 VALUES (NEW.*);
    ...
    ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
            NEW.logdate < DATE '2008-02-01' ) THEN
        INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

我正在使用openjpa 2.3.0作为JPA实现.

I am using openjpa 2.3.0 as the JPA implementation.

在桌子上有触发器

当我尝试将新实体保留在与此log_table的交易中时 使用触发器，我在提交时遇到异常:

When i try to persist new entity in transaction to this log_table with trigger, I get an exception on commiting:

Caused by: <openjpa-2.3.0-r422266:1540826 nonfatal store error> org.apache.openjpa.persistence.OptimisticLockException:
An optimistic lock violation was detected when flushing object instance "...entities.LogTable@67ec8ef4" to the data store. 
This indicates that the object was concurrently modified in another transaction.

当我使用INSERT手动插入一条记录时，它可以工作，但会返回

When I insert one record manualy using INSERT it works, but returns

Query returned successfully: 0 rows affected, 54 ms execution time.

表上没有触发器

使用引发异常的相同Java代码可以正常工作，因此该代码应该可以.对于保留实体，我没有做任何例外的事情. 手动INSERT命令返回

It works OK using the same java code that throwed the exception, so the code should be ok. I do nothing exceptional for persisting the entity. The manual INSERT command returns

Query returned successfully: one row affected, 51 ms execution time.

受影响的行数存在差异是否可以成为openjpa无法正确处理此问题的原因?在异常中提到的代码上，我找到了

Can this difference in number of affected rows be the reason why openjpa cannot handle this correctly? On the code mentioned in the exception, I found this

        try {
            int count = executeUpdate(stmnt, sql, row);
            if (count != 1) {
                logSQLWarnings(stmnt);
                Object failed = row.getFailedObject();
                if (failed != null)
                    _exceptions.add(new OptimisticException(failed));
...

似乎因为触发器使用插入返回受影响的0行而不是1行，所以代码将其视为异常.

It seems that because with the trigger the insert return 0 affected rows instead of 1, the code evaluates it as exceptional.

SUBQUESTION
有没有办法使触发结果的行为相同?我的意思是说它将返回1行受到影响吗?以某种方式传播内部函数的结果?

SUBQUESTION
IS there a way to make the trigger result behave the same? I mean that it would return that 1 row was affected? Somehow propagate the result from the inner function?

推荐答案

我在使用Java Ebean ORM时遇到了同样的问题.在Java端插入，由于rowCount失败而导致优化锁定错误.

I have the same problem using Java Ebean ORM. Inserting on java side causing Optimization lock error because of failing rowCount.

我对这个问题的研究并没有提供解决方案，例如，将行计数从插入子表中传播出来或破解行计数功能.

My Research into this problem did not lend a solution, i.e propogating row count from insert into child table or hacking the row count functionality.

我的解决方案(在您满足条件后，将NEW.id更改为表中的下一个序列，然后返回RETURN NEW，这将导致插入到父表中)，并为您提供需要影响 row_count .

My Solution (After your conditions, change NEW.id to next sequence in table and RETURN NEW that will cause insert into parent table) and give you the tuple response you need to affect row_count.

CREATE OR REPLACE FUNCTION measurement_insert_trigger() 
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.logdate >= DATE '2006-02-01' AND
         NEW.logdate < DATE '2006-03-01' ) THEN
        INSERT INTO measurement_y2006m02 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
            NEW.logdate < DATE '2006-04-01' ) THEN
        INSERT INTO measurement_y2006m03 VALUES (NEW.*);
    ...
    ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
            NEW.logdate < DATE '2008-02-01' ) THEN
        INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;

    --ORIGINAL CODE
    --RETURN NULL

    ------------------NEW CODE-----------------
    NEW.id = nextval('table_id_seq');
    RETURN NEW;

END;
$$
LANGUAGE plpgsql;

是的，我们确实有重复的条目，其中包含2个不同的ID.因此，希望对父表不存在唯一约束.如果这样做，则可能必须将其删除.我们需要删除父表上的重复项.

Yes for a moment we do have duplicate entries that have 2 different ids. So hopefully no unique constraints on parent table exist. If they do you might have to remove them. We need to delete the duplicate on parent table.

创建函数，该函数将返回插入后要使用的触发器.触发器将删除插入到父表度量中的NEW行.

Create Function that will return trigger to be used after insert. Trigger will delete the NEW row inserted into parent table measurement.

CREATE OR REPLACE FUNCTION measurement_after_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    EXECUTE 'DELETE FROM measurement where measurement.id = ' || NEW.id;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

最后 INSERT Trigger之后添加到父表度量中

Lastly Add After INSERT Trigger to parent table measurement

CREATE TRIGGER measurement_after_insert_trigger_delete_entry
AFTER INSERT ON measurement
FOR EACH ROW EXECUTE PROCEDURE measurement_after_insert_trigger();

我确实考虑了另一种可能的解决方案，就是由于插入而仅具有原始sql.只是不喜欢这种方法，因为我必须确保所有将来的构造函数都路由到原始sql.上面的方法将使代码表现出预期的效果，就像这只是我们要插入的常规表一样.

I did consider another possible solution to this was to just have raw sql due the insert. Just did not like that approach as I would have to make sure all future constructors route to the raw sql. Approach above will allow the code to behave as expected as if this was just a regular table we were inserting to.

这篇关于使用jpa进行表分区将引发OptimisticLockException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！