[jira] [Commented] (OPENJPA-2639) OptimisticLock on Partitioned tables

["senthil (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/OPENJPA-2639?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15902650#comment-15902650 ] 

senthil commented on OPENJPA-2639:
----------------------------------

Trigger invokes a function which involves in insertion of record in partition table should have the return statement a value instead of NULL.


See the sample procedure below 

/** procedure starts **/
CREATE or REPLACE FUNCTION <FUNCTION_NAME>
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
BEGIN
   EXECUTE STATEMENT
   EXECUTE 'INSERT INTO ' || TABLE_SCHEMA||'.'|| v_partition_name || ' SELECT(' || TABLE_SCHEMA||'.'|| TG_RELNAME || ' ' || quote_literal(NEW) || ').* ;';

   RETURN NULL; // needs to be RETURN NEW
END;
$BODY$;

/** procedure ends **/
Try this fix and let us know .


> OptimisticLock on Partitioned tables
> ------------------------------------
>
>                 Key: OPENJPA-2639
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2639
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: jdbc
>    Affects Versions: 2.3.0, 2.4.0, 2.4.1
>            Reporter: Andrej Badinka
>
> We created partition tables as it is described in the guide on http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html .
> I have a function that inserts records to partition table depending on date and a trigger that calls that function, like in the documentation.
> When i try to persist new entity in transaction to this partitioned log_table, I get an exception on commiting:
> {quote}
> 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@4a6e9cd6" to the data store.  This indicates that the object was concurrently modified in another transaction.
> FailedObject: ...entities.LogTable@4a6e9cd6
>         at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:124)
> {quote}
> Near that lines there is a code
> {quote}
> try {
>            int count = executeUpdate(stmnt, sql, row);
>            if (count != 1) {
>                logSQLWarnings(stmnt);
>                Object failed = row.getFailedObject();
>                if (failed != null)
>                    _exceptions.add(new OptimisticException(failed));
> {quote}
> that is causing this exception I assume. On Postgres page there is an issue regarding this http://www.postgresql.org/message-id/56F191B0.5050505@consistentstate.com and they state that the behaviour of returning 0 is common.
> The difference in inserting on normal table and partitioned table is:
> When I insert one record manualy using INSERT on partitioned table it works, but returns "Query returned successfully: *0 rows affected*, 54 ms execution time."
> Without the trigger, the manual INSERT command returns "Query returned successfully: *one row affected*, 51 ms execution time."
> Without the trigger, it works OK in java code, so the java code should be ok. I do nothing exceptional for persisting the entity.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)