Inconsistent execution order of INSERT statements using cascading persist

[timfanelli]

Some javascript/style in this post has been disabled (why?)

Hello -

I'm hoping someone can help me out here... I have two related entities using a bi-direction one-to-many relationship, as follows:

@Entity
@Table(name="E_ORDER")
@NamedQueries({...})
public class NewOrder implements Serializable {
	public NewOrder() {
		this.date = new Timestamp(System.currentTimeMillis());
	}
	
	private final static Logger LOG = Logger.getLogger( NewOrder.class.getName() );

	@Id
	@GeneratedValue(strategy=GenerationType.SEQUENCE,generator="ORDERID_SEQ")
	@SequenceGenerator(name="ORDERID_SEQ",sequenceName="ORDER_ID",allocationSize=1)
	@Column(name = "NEWORDER_ORDER_ID",updatable=false,nullable=false)
	private Integer orderIdentification;

	@Basic
	@Temporal(TemporalType.DATE)
	@Column(name = "NEWORDER_DATE")
	private Timestamp date;

	@ManyToOne(fetch = FetchType.EAGER,cascade=ALL)
	@JoinColumn(name = "NEWORDER_CUSTOMER_ID", referencedColumnName = "CUSTOMER_IDENTIFICATION")
	private Customer customer;

	@OneToMany(fetch = FetchType.EAGER,cascade={ALL},mappedBy="newOrder")
	private Set<NewOrderLineItem> items = new HashSet<NewOrderLineItem>();
 
	// Set and get methods here...
}

and

@Entity
@Table(name = "E_NEWORDER_ORDERLINE")
public class NewOrderLineItem implements Serializable {
	@Id
	@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="LINEITEMID_SEQ_GEN")
	@SequenceGenerator(name="LINEITEMID_SEQ_GEN", allocationSize=1, sequenceName="NEWORDERLINE_ID_SEQ")
	@Column(name="NEWORDER_LINEITEM_ID",nullable=false,updatable=false)
	private Integer id;

	@ManyToOne(cascade={CascadeType.ALL})
	@JoinColumn(name="NEWORDER_ORDER_ID", nullable=false)
	private NewOrder newOrder;

	@Column(name="NEWORDER_ITEM_ID",nullable=false)
	private String itemId;
	
	@Column(name = "NEWORDER_QUANTITY")
	private Integer quantity;

	// Set and get methods here...
}

So the NewOrder entity has a one-to-many relationship with the NewOrderLineItem entity, which is the owning side. My application creates a new NewOrder instance and add's several NewOrderLineItem instances to it, via an add method which maintains both sides of the relationship (by setting "this" as the neworder on the neworderlineitem, and then adding that line item to the order's hashset). I then persist my new order, and expect that cascading persistence will take care of the rest.

What I expect to see in my openjpa.jdbc.SQL trace output is something like:

SELECT NEXTVAL FOR ORDERID_SEQ
SELECT NEXTVAL FOR NEWORDERLINE_ID_SEQ
SELECT NEXTVAL FOR NEWORDERLINE_ID_SEQ
SELECT NEXTVAL FOR NEWORDERLINE_ID_SEQ
INSERT INTO E_NEWORDER ( NEWORDER_ORDER_ID, NEWORDER_DATE, NEWORDER_CUSTOMER_ID ) VALUES ( ?, ?, ? )
INSERT INTO E_NEWORDER_LINEITEM ( NEWORDER_LINEITEM_ID, NEWORDER_ORDER_ID, NEWORDER_ITEM_ID, NEWORDER_QUANTITY ) VALUES ( ?, ?, ?, ? )
INSERT INTO E_NEWORDER_LINEITEM ( NEWORDER_LINEITEM_ID, NEWORDER_ORDER_ID, NEWORDER_ITEM_ID, NEWORDER_QUANTITY ) VALUES ( ?, ?, ?, ? )
INSERT INTO E_NEWORDER_LINEITEM ( NEWORDER_LINEITEM_ID, NEWORDER_ORDER_ID, NEWORDER_ITEM_ID, NEWORDER_QUANTITY ) VALUES ( ?, ?, ?, ? )

However, it appears as though MOST of the time, the INSERT into the E_NEWORDER (the parent table) does not occur before the attempted inserts into the E_NEWORDER_ORDERLINE table; thus violating my referential integrity and throwing a foreign key violation SQL Exception. Sometimes though, everything works as it should and nothing goes wrong.

Any idea why OpenJPA (1.1.0) would not properly insert the parent entity first?

Also - I had attempted to have OpenJPA using <property name="openjpa.jdbc.SchemaFactory" value="native(ForeignKeys=true)"/> but we use custom data types in our schema that weren't being properly validated... so that wasn't really an option for me.

Any help would be greatly appreciated!
-Tim Fanelli

[Pinaki Poddar]

Hi,
> using <property name="openjpa.jdbc.SchemaFactory" value="native(ForeignKeys=true)"/> but
> we use custom data types in our schema that weren't being properly validated... so that wasn't really > an option for me.

> Sometimes though, everything works as it should and nothing goes wrong.

Without reading the existing foreign key information, OpenJPA will not be able to reorder the SQL it issues to satisfy the database constraints. The suggestion choice is
1. to instruct OpenJPA *not* to reorder the SQL or rather follow the order in which object operations were performed
    <property name="openjpa.jdbc.UpdateManager" value="operation-order">
2. perform the object operations in a order that satisfy the foreign key constraints (such as inserting a parent before a child)

[timfanelli]

Thanks -

Would using the OpenJPA "ForeignKey" annotation be a sufficient work
around for not being able to use the native schema factory?

Also, if I use the operation-order update manager, would I need to
explicitly insert the parent entity and child entities separately, or
would the cascading persist work in that manner?

-Tim

On Thu, 2008-08-07 at 07:59 -0700, Pinaki Poddar wrote:

> Hi,
> > using <property name="openjpa.jdbc.SchemaFactory"
> > value="native(ForeignKeys=true)"/> but
> > we use custom data types in our schema that weren't being properly
> > validated... so that wasn't really > an option for me.
>
> > Sometimes though, everything works as it should and nothing goes wrong.
>
> Without reading the existing foreign key information, OpenJPA will not be
> able to reorder the SQL it issues to satisfy the database constraints. The
> suggestion choice is
> 1. to instruct OpenJPA *not* to reorder the SQL or rather follow the order
> in which object operations were performed
>     <property name="openjpa.jdbc.UpdateManager" value="operation-order">
> 2. perform the object operations in a order that satisfy the foreign key
> constraints (such as inserting a parent before a child)
>

[Pinaki Poddar]

Hi,
> Would using the OpenJPA "ForeignKey" annotation be a sufficient work
> around for not being able to use the native schema factory?

Good idea. Should work as well as this annotation tells OpenJPA that a 'logical' foreign key exists.
Have not tried/tested though. Let us know if this solves your problem.

> Also, if I use the operation-order update manager, would I need to
> explicitly insert the parent entity and child entities separately, or
> would the cascading persist work in that manner?

The cascade will insert parent first and child later if Parent.children is annotated with CascadeType.PERSIST/ALL. The cascading scheme and sql ordering strategy are not aware of each other but in this case they will be consistent.

[timfanelli]

Sorry it took a while to get this confirmed. I wound up having to do some work to my application server environment and got hung up for a bit.

Adding the @ForeignKey attribute to the column worked perfectly. For now, I'm also sticking with the operation-order update manager... performance is not a big concern of mine at the moment, I'm driving for stability under load and feel better knowing that the database operations will execute in an expected order.

Thanks again for the help!
-Tim

Pinaki Poddar wrote:

Hi,
> Would using the OpenJPA "ForeignKey" annotation be a sufficient work
> around for not being able to use the native schema factory?

Good idea. Should work as well as this annotation tells OpenJPA that a 'logical' foreign key exists.
Have not tried/tested though. Let us know if this solves your problem.

> Also, if I use the operation-order update manager, would I need to
> explicitly insert the parent entity and child entities separately, or
> would the cascading persist work in that manner?

The cascade will insert parent first and child later if Parent.children is annotated with CascadeType.PERSIST/ALL. The cascading scheme and sql ordering strategy are not aware of each other but in this case they will be consistent.