Eclipse Community Forums: EclipseLink » NOT NULL and NOT NULL FK constraints

Home » Eclipse Projects » EclipseLink » NOT NULL and NOT NULL FK constraints(Eclipselink appears to not honor these)

Show: Today's Messages :: Show Polls :: Message Navigator

NOT NULL and NOT NULL FK constraints [message #807518]

Sun, 26 February 2012 16:13

Hal Hildebrand

Messages: 12
Registered: February 2012

Junior Member

I have attached a simple test case showing a rather odd error. The test case has a single table, which has a primary key, a non null constrained string name, and a non null constrained foreign key back to the table itself. The test creates two entities, assigns them to each other, and then commits the transaction.

Two errors happen here. The first is that the non null column which *isn't* a foreign key is causing an exception as Eclipselink is writing out a null instead of the initialized value that it has in the object. The second error, if you remove the null constraint on the name column, is that the same thing happens with the foreign keys - i.e. Eclipselink sets them to null before writing them out.

Now, please note that the IDs of both of these objects have been assigned by Eclipselink BEFORE the transaction commits. Thus, it actually has the values necessary to insert these rows into the table correctly. Further, I am already doing this with Hibernate (non JPA version) from some old code. So it's not like it's a fundamental impossibility to have this done. Even further, I can perform the correct insert with the following SQL:

INSERT INTO test.relationship(id, name, inverse)VALUES (1, 'foo', 2);
INSERT INTO test.relationship(id, name, inverse)VALUES (2, 'bar', 1);

So this is entirely possible to accomplish in SQL.

Color me completely baffled. I must be doing something wrong in my test case, as this doesn't seem like either of the two problems I have in the test case should be showing up.

Attachment: eclipselink-bug.zip
(Size: 8.53KB, Downloaded 258 times)

[Updated on: Sun, 26 February 2012 16:13]

Report message to a moderator

Re: NOT NULL and NOT NULL FK constraints [message #807538 is a reply to message #807518]

Sun, 26 February 2012 16:58

Doug Clarke

Messages: 155
Registered: July 2009

Senior Member

Hal,

Someone will look into this in more detail on Monday but one comment I have is about your recommended SQL:

INSERT INTO test.relationship(id, name, inverse)VALUES (1, 'foo', 2);
INSERT INTO test.relationship(id, name, inverse)VALUES (2, 'bar', 1);

While this may work on some databases the SQL's success is dependent upon the database vendor and how its configured to enforce FK constraints. Some databases will fail on the first INSERT since ro row with id = 2 exists in the database.

When EclipseLink detects the existence of a circular FK reference it uses a shallow INSERT approach putting null values in for te FK columns and then after the ids all exist it does an update to set the FK values.

Doug

Report message to a moderator

Re: NOT NULL and NOT NULL FK constraints [message #807543 is a reply to message #807538]

Sun, 26 February 2012 17:07

Hal Hildebrand

Messages: 12
Registered: February 2012

Junior Member

Certainly, I understand that there's a lot of vendor dependent issues with JPA. However, in this case, all the information is held by Eclipselink. If you turn up logging to FINEST, you'll see that Eclipselink assigns the IDs before the txn even commits. If this is an issue with an implementation choice, conservatively dealing with disparate databases, that's one thing. But this particular issue should be something that the majority of databases will allow, certainly with pre-allocated primary keys.

But the real stumper for me is why Eclipselink decides to put NULL in the NOT NULL column which isn't a foreign key. That's a real mystery to me and something I can't figure out what I'm doing wrong in that case.

I think I can work around the creation issue through some native SQL queries, but this other issue seems like a complete blocker. I must be doing something completely boneheaded...

Report message to a moderator

Re: NOT NULL and NOT NULL FK constraints [message #807572 is a reply to message #807543]

Sun, 26 February 2012 17:59

Hal Hildebrand

Messages: 12
Registered: February 2012

Junior Member

In fact, if I change the test to eliminate the FK column, and simply use a NOT NULL column of type string, the test fails on insert due to the constraint violation.

Report message to a moderator

Re: NOT NULL and NOT NULL FK constraints [message #807703 is a reply to message #807572]

Sun, 26 February 2012 22:47

Hal Hildebrand

Messages: 12
Registered: February 2012

Junior Member

So, that's my mistake. I didn't set the inverse name, I substituted r instead of i. <sigh>

Report message to a moderator

Re: NOT NULL and NOT NULL FK constraints [message #807756 is a reply to message #807703]

Mon, 27 February 2012 00:53

Hal Hildebrand

Messages: 12
Registered: February 2012

Junior Member

Here's another test case demonstrating the failure to honor the NOT NULL constraint on a foreign key. Entity A has a foreign key column to another entity B, constrained to be non null. Create an instance of entity B, persist it, commit the txn. Retrieve the instance of entity B, create instances of entity A, persist the new entities, set the pointer to entity B in entity A, commit the transaction. The test fails with the message:

Internal Exception: org.postgresql.util.PSQLException: ERROR: null value in column "updated_by" violates not-null constraint
Error Code: 0
Call: INSERT INTO test.relationship (ID, NAME, updated_by, inverse) VALUES (1, 'foo', NULL, NULL)
Query: WriteObjectQuery(test.Relationship@87e9ce2)

Test case attached.

Attachment: eclipseling-not-null-fk.zip
(Size: 9.29KB, Downloaded 264 times)

[Updated on: Mon, 27 February 2012 00:54]

Report message to a moderator

Re: NOT NULL and NOT NULL FK constraints [message #808280 is a reply to message #807756]

Mon, 27 February 2012 15:53

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

Its a moot point since you did not set it on the mapping, but the Not Null constraint is something that is used for DDL generation and not something that affects EclipseLink behavior. As Doug mentioned, EclipseLink will set nulls in relationships when it must, for instance when there is a circular reference. But that does not seem to be the case here.

Can you turn on logging (set persistence property "eclipselink.logging.level" to "FINEST"), disable the constraint and upload the log? This should show why it is inserting null.

Regards,
Chris

[Updated on: Mon, 27 February 2012 15:54]

Report message to a moderator

Re: NOT NULL and NOT NULL FK constraints [message #808307 is a reply to message #808280]

Mon, 27 February 2012 16:33

Hal Hildebrand

Messages: 12
Registered: February 2012

Junior Member

Sure, I'll provide you with that output. But please be aware there are two issues here. The first is the non null FK reference back to the table itself. The other is that even when I remove the non null constraint for this particular FK, if I have another FK to another table in the same row, Eclipselink will also violate the constraint by inserting NULL for that column as well. (test case in message http://www.eclipse.org/forums/index.php/mv/msg/298970/807756/#msg_807756)

The circular constraint I can kinda sorta understand. The second violation of the constraint makes no sense at all to me.

[Updated on: Mon, 27 February 2012 16:34]

Report message to a moderator

Re: NOT NULL and NOT NULL FK constraints [message #808318 is a reply to message #808280]

Mon, 27 February 2012 16:49

Hal Hildebrand

Messages: 12
Registered: February 2012

Junior Member

I think I misread you. Here's the FINEST log result for the case where the recursive FK non null constraint removed, but there table still has a non null FK column to another table. This fails because Eclipselink is setting the second FK to null as well.

[EL Finest]: 2012-02-27 08:48:18.716--ServerSession(2092063645)--Thread(Thread[main,5,main])--Begin predeploying Persistence Unit test; session file:/Users/hhildebrand/git/eclipseling-not-null-fk/target/classes/_test_url=jdbc:postgresql://localhost:5432/test_user=postgres; state Initial; factoryCount 0
[EL Finest]: 2012-02-27 08:48:18.738--ServerSession(2092063645)--Thread(Thread[main,5,main])--property=eclipselink.orm.throw.exceptions; default value=true
[EL Finest]: 2012-02-27 08:48:18.739--ServerSession(2092063645)--Thread(Thread[main,5,main])--property=eclipselink.multitenant.tenants-share-emf; default value=true
[EL Finest]: 2012-02-27 08:48:18.739--ServerSession(2092063645)--Thread(Thread[main,5,main])--property=eclipselink.multitenant.tenants-share-cache; default value=false
[EL Finest]: 2012-02-27 08:48:18.759--ServerSession(2092063645)--Thread(Thread[main,5,main])--property=eclipselink.metadata-source; default value=null
[EL Finer]: 2012-02-27 08:48:18.76--ServerSession(2092063645)--Thread(Thread[main,5,main])--Searching for default mapping file in file:/Users/hhildebrand/git/eclipseling-not-null-fk/target/classes/
[EL Finer]: 2012-02-27 08:48:18.765--ServerSession(2092063645)--Thread(Thread[main,5,main])--Searching for default mapping file in file:/Users/hhildebrand/git/eclipseling-not-null-fk/target/classes/
[EL Config]: 2012-02-27 08:48:18.905--ServerSession(2092063645)--Thread(Thread[main,5,main])--The access type for the persistent class [class test.Relationship] is set to [FIELD].
[EL Config]: 2012-02-27 08:48:18.951--ServerSession(2092063645)--Thread(Thread[main,5,main])--The target entity (reference) class for the one to one mapping element [field inverse] is being defaulted to: class test.Relationship.
[EL Config]: 2012-02-27 08:48:18.953--ServerSession(2092063645)--Thread(Thread[main,5,main])--The target entity (reference) class for the many to one mapping element [field updatedBy] is being defaulted to: class test.Resource.
[EL Config]: 2012-02-27 08:48:18.953--ServerSession(2092063645)--Thread(Thread[main,5,main])--The access type for the persistent class [class test.Resource] is set to [FIELD].
[EL Config]: 2012-02-27 08:48:18.954--ServerSession(2092063645)--Thread(Thread[main,5,main])--The alias name for the entity class [class test.Relationship] is being defaulted to: Relationship.
[EL Config]: 2012-02-27 08:48:18.992--ServerSession(2092063645)--Thread(Thread[main,5,main])--The column name for element [id] is being defaulted to: ID.
[EL Config]: 2012-02-27 08:48:18.994--ServerSession(2092063645)--Thread(Thread[main,5,main])--The column name for element [name] is being defaulted to: NAME.
[EL Config]: 2012-02-27 08:48:18.995--ServerSession(2092063645)--Thread(Thread[main,5,main])--The alias name for the entity class [class test.Resource] is being defaulted to: Resource.
[EL Config]: 2012-02-27 08:48:18.995--ServerSession(2092063645)--Thread(Thread[main,5,main])--The column name for element [id] is being defaulted to: ID.
[EL Config]: 2012-02-27 08:48:18.996--ServerSession(2092063645)--Thread(Thread[main,5,main])--The column name for element [name] is being defaulted to: NAME.
[EL Config]: 2012-02-27 08:48:18.997--ServerSession(2092063645)--Thread(Thread[main,5,main])--The sequence name for the sequence generator named [resource_id_seq] defined on [class test.Resource] from [class test.Resource] is being defaulted to: resource_id_seq.
[EL Config]: 2012-02-27 08:48:18.997--ServerSession(2092063645)--Thread(Thread[main,5,main])--The sequence name for the sequence generator named [relationship_id_seq] defined on [class test.Relationship] from [class test.Relationship] is being defaulted to: relationship_id_seq.
[EL Config]: 2012-02-27 08:48:19.028--ServerSession(2092063645)--Thread(Thread[main,5,main])--The primary key column name for the mapping element [field inverse] is being defaulted to: ID.
[EL Config]: 2012-02-27 08:48:19.029--ServerSession(2092063645)--Thread(Thread[main,5,main])--The primary key column name for the mapping element [field updatedBy] is being defaulted to: ID.
[EL Finest]: 2012-02-27 08:48:19.03--ServerSession(2092063645)--Thread(Thread[main,5,main])--End predeploying Persistence Unit test; session file:/Users/hhildebrand/git/eclipseling-not-null-fk/target/classes/_test_url=jdbc:postgresql://localhost:5432/test_user=postgres; state Predeployed; factoryCount 0
[EL Finer]: 2012-02-27 08:48:19.03--Thread(Thread[main,5,main])--JavaSECMPInitializer - transformer is null.
[EL Finest]: 2012-02-27 08:48:19.03--ServerSession(2092063645)--Thread(Thread[main,5,main])--Begin predeploying Persistence Unit test; session file:/Users/hhildebrand/git/eclipseling-not-null-fk/target/classes/_test_url=jdbc:postgresql://localhost:5432/test_user=postgres; state Predeployed; factoryCount 0
[EL Finest]: 2012-02-27 08:48:19.03--ServerSession(2092063645)--Thread(Thread[main,5,main])--End predeploying Persistence Unit test; session file:/Users/hhildebrand/git/eclipseling-not-null-fk/target/classes/_test_url=jdbc:postgresql://localhost:5432/test_user=postgres; state Predeployed; factoryCount 1
[EL Finest]: 2012-02-27 08:48:19.034--ServerSession(2092063645)--Thread(Thread[main,5,main])--Begin deploying Persistence Unit test; session file:/Users/hhildebrand/git/eclipseling-not-null-fk/target/classes/_test_url=jdbc:postgresql://localhost:5432/test_user=postgres; state Predeployed; factoryCount 1
[EL Finer]: 2012-02-27 08:48:19.045--ServerSession(2092063645)--Thread(Thread[main,5,main])--Could not initialize Validation Factory. Encountered following exception: java.lang.NoClassDefFoundError: javax/validation/Validation
[EL Finest]: 2012-02-27 08:48:19.074--ServerSession(2092063645)--Thread(Thread[main,5,main])--property=eclipselink.logging.level; value=FINEST; translated value=FINEST
[EL Finest]: 2012-02-27 08:48:19.074--ServerSession(2092063645)--Thread(Thread[main,5,main])--property=eclipselink.logging.level; value=FINEST; translated value=FINEST
[EL Finest]: 2012-02-27 08:48:19.074--ServerSession(2092063645)--Thread(Thread[main,5,main])--property=eclipselink.jdbc.bind-parameters; value=false
[EL Finest]: 2012-02-27 08:48:19.074--ServerSession(2092063645)--Thread(Thread[main,5,main])--property=javax.persistence.jdbc.user; value=postgres
[EL Finest]: 2012-02-27 08:48:19.075--ServerSession(2092063645)--Thread(Thread[main,5,main])--property=javax.persistence.jdbc.password; value=xxxxxx
[EL Finest]: 2012-02-27 08:48:19.324--ServerSession(2092063645)--Thread(Thread[main,5,main])--property=eclipselink.target-database; value=POSTGRESQL; translated value=org.eclipse.persistence.platform.database.PostgreSQLPlatform
[EL Finest]: 2012-02-27 08:48:19.325--ServerSession(2092063645)--Thread(Thread[main,5,main])--property=javax.persistence.jdbc.url; value=jdbc:postgresql://localhost:5432/test
[EL Finest]: 2012-02-27 08:48:19.326--ServerSession(2092063645)--Thread(Thread[main,5,main])--property=eclipselink.jdbc.uppercase-columns; value=true
[EL Info]: 2012-02-27 08:48:19.327--ServerSession(2092063645)--Thread(Thread[main,5,main])--EclipseLink, version: Eclipse Persistence Services - 2.3.2.v20111125-r10461
[EL Config]: 2012-02-27 08:48:19.334--ServerSession(2092063645)--Connection(1227469025)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(
platform=>PostgreSQLPlatform
user name=> "postgres"
datasource URL=> "jdbc:postgresql://localhost:5432/test"
))
[EL Config]: 2012-02-27 08:48:19.389--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--Connected: jdbc:postgresql://localhost:5432/test
User: postgres
Database: PostgreSQL Version: 9.1.2
Driver: PostgreSQL Native Driver Version: PostgreSQL 9.0 JDBC4 (build 801)
[EL Finest]: 2012-02-27 08:48:19.39--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--Connection acquired from connection pool [default].
[EL Finest]: 2012-02-27 08:48:19.39--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--Connection released to connection pool [default].
[EL Finest]: 2012-02-27 08:48:19.396--ServerSession(2092063645)--Thread(Thread[main,5,main])--sequencing connected, state is Preallocation_NoTransaction_State
[EL Finest]: 2012-02-27 08:48:19.396--ServerSession(2092063645)--Thread(Thread[main,5,main])--sequence relationship_id_seq: preallocation size 1
[EL Finest]: 2012-02-27 08:48:19.396--ServerSession(2092063645)--Thread(Thread[main,5,main])--sequence resource_id_seq: preallocation size 1
[EL Info]: 2012-02-27 08:48:19.434--ServerSession(2092063645)--Thread(Thread[main,5,main])--file:/Users/hhildebrand/git/eclipseling-not-null-fk/target/classes/_test_url=jdbc:postgresql://localhost:5432/test_user=postgres login successful
[EL Finer]: 2012-02-27 08:48:19.449--ServerSession(2092063645)--Thread(Thread[main,5,main])--Canonical Metamodel class [test.Relationship_] not found during initialization.
[EL Finer]: 2012-02-27 08:48:19.449--ServerSession(2092063645)--Thread(Thread[main,5,main])--Canonical Metamodel class [test.Resource_] not found during initialization.
[EL Finest]: 2012-02-27 08:48:19.449--ServerSession(2092063645)--Thread(Thread[main,5,main])--End deploying Persistence Unit test; session file:/Users/hhildebrand/git/eclipseling-not-null-fk/target/classes/_test_url=jdbc:postgresql://localhost:5432/test_user=postgres; state Deployed; factoryCount 1
[EL Finer]: 2012-02-27 08:48:19.468--ServerSession(2092063645)--Thread(Thread[main,5,main])--client acquired: 2123673391
[EL Finer]: 2012-02-27 08:48:19.479--ClientSession(2123673391)--Thread(Thread[main,5,main])--acquire unit of work: 820201254
[EL Finest]: 2012-02-27 08:48:19.479--UnitOfWork(820201254)--Thread(Thread[main,5,main])--persist() operation called on: test.Resource@195428dd.
[EL Finest]: 2012-02-27 08:48:19.48--ClientSession(2123673391)--Thread(Thread[main,5,main])--Execute query ValueReadQuery(sql="select nextval('test.resource_id_seq')")
[EL Finest]: 2012-02-27 08:48:19.481--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--Connection acquired from connection pool [default].
[EL Fine]: 2012-02-27 08:48:19.481--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--select nextval(test.resource_id_seq)
[EL Finest]: 2012-02-27 08:48:19.5--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--Connection released to connection pool [default].
[EL Finest]: 2012-02-27 08:48:19.5--UnitOfWork(820201254)--Thread(Thread[main,5,main])--assign sequence to the object (2 -> test.Resource@195428dd)
[EL Finer]: 2012-02-27 08:48:19.501--UnitOfWork(820201254)--Thread(Thread[main,5,main])--begin unit of work commit
[EL Finest]: 2012-02-27 08:48:19.506--UnitOfWork(820201254)--Thread(Thread[main,5,main])--Execute query InsertObjectQuery(test.Resource@195428dd)
[EL Finest]: 2012-02-27 08:48:19.507--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--Connection acquired from connection pool [default].
[EL Finer]: 2012-02-27 08:48:19.508--ClientSession(2123673391)--Connection(1301353792)--Thread(Thread[main,5,main])--begin transaction
[EL Fine]: 2012-02-27 08:48:19.508--ClientSession(2123673391)--Connection(1301353792)--Thread(Thread[main,5,main])--INSERT INTO test.resource (ID, NAME) VALUES (2, Owner)
[EL Finer]: 2012-02-27 08:48:19.521--ClientSession(2123673391)--Connection(1301353792)--Thread(Thread[main,5,main])--commit transaction
[EL Finest]: 2012-02-27 08:48:19.523--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--Connection released to connection pool [default].
[EL Finer]: 2012-02-27 08:48:19.526--UnitOfWork(820201254)--Thread(Thread[main,5,main])--end unit of work commit
[EL Finer]: 2012-02-27 08:48:19.526--UnitOfWork(820201254)--Thread(Thread[main,5,main])--resume unit of work
[EL Finest]: 2012-02-27 08:48:19.543--UnitOfWork(820201254)--Thread(Thread[main,5,main])--Execute query ReadObjectQuery(name="readObject" referenceClass=Resource sql="SELECT ID, NAME FROM test.resource WHERE (ID = ?)")
[EL Finest]: 2012-02-27 08:48:19.544--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--Connection acquired from connection pool [default].
[EL Fine]: 2012-02-27 08:48:19.545--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--SELECT ID, NAME FROM test.resource WHERE (ID = 1)
[EL Finest]: 2012-02-27 08:48:19.547--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--Connection released to connection pool [default].
[EL Finest]: 2012-02-27 08:48:19.548--UnitOfWork(820201254)--Thread(Thread[main,5,main])--persist() operation called on: test.Relationship@2e257f1b.
[EL Finest]: 2012-02-27 08:48:19.549--ClientSession(2123673391)--Thread(Thread[main,5,main])--Execute query ValueReadQuery(sql="select nextval('test.relationship_id_seq')")
[EL Finest]: 2012-02-27 08:48:19.549--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--Connection acquired from connection pool [default].
[EL Fine]: 2012-02-27 08:48:19.549--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--select nextval(test.relationship_id_seq)
[EL Finest]: 2012-02-27 08:48:19.55--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--Connection released to connection pool [default].
[EL Finest]: 2012-02-27 08:48:19.55--UnitOfWork(820201254)--Thread(Thread[main,5,main])--assign sequence to the object (3 -> test.Relationship@2e257f1b)
[EL Finest]: 2012-02-27 08:48:19.551--UnitOfWork(820201254)--Thread(Thread[main,5,main])--persist() operation called on: test.Relationship@1fbbdd48.
[EL Finest]: 2012-02-27 08:48:19.551--ClientSession(2123673391)--Thread(Thread[main,5,main])--Execute query ValueReadQuery(sql="select nextval('test.relationship_id_seq')")
[EL Finest]: 2012-02-27 08:48:19.551--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--Connection acquired from connection pool [default].
[EL Fine]: 2012-02-27 08:48:19.551--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--select nextval(test.relationship_id_seq)
[EL Finest]: 2012-02-27 08:48:19.552--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--Connection released to connection pool [default].
[EL Finest]: 2012-02-27 08:48:19.552--UnitOfWork(820201254)--Thread(Thread[main,5,main])--assign sequence to the object (4 -> test.Relationship@1fbbdd48)
[EL Finer]: 2012-02-27 08:48:19.553--UnitOfWork(820201254)--Thread(Thread[main,5,main])--begin unit of work commit
[EL Finest]: 2012-02-27 08:48:19.553--UnitOfWork(820201254)--Thread(Thread[main,5,main])--Execute query InsertObjectQuery(test.Relationship@2e257f1b)
[EL Finest]: 2012-02-27 08:48:19.553--UnitOfWork(820201254)--Thread(Thread[main,5,main])--Execute query WriteObjectQuery(test.Relationship@1fbbdd48)
[EL Finest]: 2012-02-27 08:48:19.554--UnitOfWork(820201254)--Thread(Thread[main,5,main])--Execute query WriteObjectQuery(test.Relationship@2e257f1b)
[EL Finest]: 2012-02-27 08:48:19.554--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--Connection acquired from connection pool [default].
[EL Finer]: 2012-02-27 08:48:19.554--ClientSession(2123673391)--Connection(1301353792)--Thread(Thread[main,5,main])--begin transaction
[EL Fine]: 2012-02-27 08:48:19.554--ClientSession(2123673391)--Connection(1301353792)--Thread(Thread[main,5,main])--INSERT INTO test.relationship (ID, NAME, updated_by, inverse) VALUES (3, foo, NULL, NULL)
[EL Fine]: 2012-02-27 08:48:19.557--ClientSession(2123673391)--Thread(Thread[main,5,main])--SELECT 1
[EL Warning]: 2012-02-27 08:48:19.559--UnitOfWork(820201254)--Thread(Thread[main,5,main])--Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: null value in column "updated_by" violates not-null constraint
Error Code: 0
Call: INSERT INTO test.relationship (ID, NAME, updated_by, inverse) VALUES (3, 'foo', NULL, NULL)
Query: WriteObjectQuery(test.Relationship@2e257f1b)
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.processExceptionForCommError(DatabaseAccessor.java:1494)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:838)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:906)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:592)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:535)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1717)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:253)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:342)
at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:162)
at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:177)
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:472)
at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:77)
at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:90)
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.performUserDefinedWrite(DatabaseQueryMechanism.java:564)
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.performUserDefinedInsert(DatabaseQueryMechanism.java:532)
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:402)
at org.eclipse.persistence.queries.WriteObjectQuery.executeCommitWithChangeSet(WriteObjectQuery.java:117)
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:287)
at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:844)
at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:743)
at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2871)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1516)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1498)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1449)
at org.eclipse.persistence.mappings.ObjectReferenceMapping.insert(ObjectReferenceMapping.java:1098)
at org.eclipse.persistence.mappings.ObjectReferenceMapping.preInsert(ObjectReferenceMapping.java:547)
at org.eclipse.persistence.descriptors.DescriptorQueryManager.preInsert(DescriptorQueryManager.java:1089)
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:425)
at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:80)
at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:90)
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.performUserDefinedWrite(DatabaseQueryMechanism.java:564)
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.performUserDefinedInsert(DatabaseQueryMechanism.java:532)
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:402)
at org.eclipse.persistence.queries.WriteObjectQuery.executeCommitWithChangeSet(WriteObjectQuery.java:121)
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:287)
at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:844)
at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:743)
at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2871)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1516)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1498)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1449)
at org.eclipse.persistence.mappings.ObjectReferenceMapping.insert(ObjectReferenceMapping.java:1098)
at org.eclipse.persistence.mappings.ObjectReferenceMapping.preInsert(ObjectReferenceMapping.java:547)
at org.eclipse.persistence.descriptors.DescriptorQueryManager.preInsert(DescriptorQueryManager.java:1089)
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:425)
at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:80)
at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:90)
at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:287)
at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:844)
at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:743)
at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2871)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1516)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1498)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1449)
at org.eclipse.persistence.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:224)
at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:123)
at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:3799)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1415)
at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitToDatabase(RepeatableWriteUnitOfWork.java:636)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1505)
at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:267)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:1143)
at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commitInternal(EntityTransactionImpl.java:84)
at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:63)
at test.TestRelationship.testFunction(TestRelationship.java:61)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:263)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:68)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222)
at org.junit.runners.ParentRunner.run(ParentRunner.java:300)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:49)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: org.postgresql.util.PSQLException: ERROR: null value in column "updated_by" violates not-null constraint
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:302)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:829)
... 97 more

[EL Finer]: 2012-02-27 08:48:19.564--ClientSession(2123673391)--Connection(1301353792)--Thread(Thread[main,5,main])--rollback transaction
[EL Finest]: 2012-02-27 08:48:19.565--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--Connection released to connection pool [default].
[EL Config]: 2012-02-27 08:48:19.565--ServerSession(2092063645)--Connection(1301353792)--Thread(Thread[main,5,main])--disconnect
[EL Finer]: 2012-02-27 08:48:19.566--UnitOfWork(820201254)--Thread(Thread[main,5,main])--release unit of work
[EL Finer]: 2012-02-27 08:48:19.566--ClientSession(2123673391)--Thread(Thread[main,5,main])--client released

Report message to a moderator

Re: NOT NULL and NOT NULL FK constraints [message #808396 is a reply to message #808318]

Mon, 27 February 2012 18:45

Doug Clarke

Messages: 155
Registered: July 2009

Senior Member

Hal,

The EclipseLink runtime is not aware of database defined NOT NULL constraints as it does not read these from the database. EclipseLink is aware of not null column configurations defined through JPA annotations and XML but only uses these for schema generation. EclipseLink relies on the object model developer to ensure that all constraints are met in the objects that are persisted (consider JSR 303 to add additional automated validation checking).

Some cases I am aware of where EclipseLink could insert a NULL where the persisted entity did not have a null value include:
- circular FKs when a shallow INSERT followed by UPDATE
- read-only mappings

In your second test zip you still have the cyclic relationship mapped so EclipseLink is following the shallow INSERT approach.

Running it I get:

[EL Fine]: INSERT INTO test.relationship (NAME, updated_by, inverse) VALUES (?, ?, ?)
bind => [foo, null, null]
[EL Fine]: SELECT LAST_INSERT_ID()
[EL Fine]: INSERT INTO test.relationship (NAME, updated_by, inverse) VALUES (?, ?, ?)
bind => [bar, 1, 1]
[EL Fine]: SELECT LAST_INSERT_ID()
[EL Fine]: UPDATE test.relationship SET inverse = ?, NAME = ?, updated_by = ? WHERE (ID = ?)
bind => [2, foo, 1, 1]

Then when I make the inverse relationship @Transient I get:

[EL Fine]: INSERT INTO test.resource (NAME) VALUES (?)
bind => [Owner]
[EL Fine]: SELECT LAST_INSERT_ID()
[EL Fine]: INSERT INTO test.relationship (NAME, updated_by) VALUES (?, ?)
bind => [bar, 1]
[EL Fine]: SELECT LAST_INSERT_ID()
[EL Fine]: INSERT INTO test.relationship (NAME, updated_by) VALUES (?, ?)
bind => [foo, 1]
[EL Fine]: SELECT LAST_INSERT_ID()

The ability to force the FK to be populated on INSERT when there are circular FKs and a FK constraint is setup as deferred is a feature we can look at adding.

Doug

Report message to a moderator

Re: NOT NULL and NOT NULL FK constraints [message #808405 is a reply to message #808396]

Mon, 27 February 2012 18:55

Doug Clarke

Messages: 155
Registered: July 2009

Senior Member

One additional note for clarity.

If EclipseLink detect a circular FK situation it does write out all FK values as null and then updates them all later. That is why the 'updatedBy' attribute is being inserted as NULL when the 'inverse' relationship attribute is mapped.

Doug

Report message to a moderator

Re: NOT NULL and NOT NULL FK constraints [message #808411 is a reply to message #808405]

Mon, 27 February 2012 19:01

Hal Hildebrand

Messages: 12
Registered: February 2012

Junior Member

Okay, then. That explains it.

Seems kind of harsh for perfectly valid schema, but I can understand the tradeoffs.

Any idea when this will be fixed so these schemas can be supported?

And btw, thanks for helping out with this Wink

[Updated on: Mon, 27 February 2012 19:25]

Report message to a moderator

Previous Topic:	Native Query using function not finding primary key
Next Topic:	Query to filter collection

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 00:43:34 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter