Is there a way to prevent Postgres triggers from triggering before Java/EclipseLink transaction commits

I have a Java application that connects to a Postgres DB using EclipseLink.

My problem is that database triggers are triggered before the Java/EclipseLink transaction is completed causing the data to be incorrect.

Example:

There is a trigger to update a order_logs table every time there is an insert or update on the orders table.

Problem:

When a setter() method is called on an OrderEntity instance, the database logs indicate that the trigger is run for every two setter() method calls within the same transaction. This results in the order_logs table having multiple rows for only one update.

My question is that is there a way to force/ensure that the trigger is triggered only at the end of a transaction. I had a look at this page but deferring looks like it’s just for foreign keys. My methods are wrapped with the @Transactional annotation.

The trigger was created using:

create trigger my_insert_update_trigger after
  insert or update
      on
        public.my_table for each row execute procedure my_insert_update_trigger_function();

Answer

I ended up having to add this line:

<property name="eclipselink.persistence-context.flush-mode" value="COMMIT" />

to the persistence.xml file as documented in persistence-context.flush-mode and this answer.

Advertisement

Answer