I'm having some strange RDBMS N+1 calls most likely due to my mappings, but am not sure if this is a DN Persistence configuration issue or a mapping issue.  I'm using MSSQL.

My problem is that I have an Object "Person" which has some audit fields "addPerson" and "modPerson".  Both "addPerson" and "modPerson" are simple N-1 relations.  Conceptually like the following:

       //  InheritanceStrategy=New-table


When I retrieve the Person (using a datastore identity), I see an SQL Query which selects the primitive fields but not the embedded Objects.  Something like:
    "Select name, age FROM Person where ID = 123".

When I try to convert to a DTO and read the `addPerson`, and `modPerson` fields, DN then launches the next queries:

• select addPerson FROM Person where ID =123
• select username FROM AuditPerson where ID = <>

• select modPerson FROM Person where ID =123
• select username FROM AuditPerson where ID = <>

So, essentially, because of the "lazy loading", it needs to requery the DB for the 2 FK values in the Person table before it can actually query the AuditPerson table.


Clearly, I suspect that this is due to issues in my mapping definitions.  I am working with old mapping files from Kodo, so I suspect there is something I haven't done correctly.

Questions:
1) Is this normal/expected behaviour?  Is this due to the inheriteance model/hierarchy?  
2) Is this controllable via mapping configurations?
3) Is there a way to reconfigure the model such that I do not need the additonal independent queries to retrieve the FK values for the modPerson and addPerson.


I realize that this is difficult to diagnose without exact details and sample test case, but thought I would ask in the meantime while I try to reproduce the exact behaviour in a test app in case someone has seen/experienced this before.

Thanks,

Eric

Audit fields "addPerson" and "modPerson" aren't embedded (exist in the same table), they are related (have their own table), using JDO terminology ... or at least without seeing your mapping (XML/annotations) that's what it looks like.

Your mapping definition controls where things are stored, and what is in the default fetch group. Your runtime API usage defines what is in the active fetch group.
What is in the fetch group defines what is fetched ... clue in the name. Kodo maybe didn't have JDO (2) fetch groups.

You rectify it by specifying the appropriate fetching (group) for the appropriate moment in the API usage.

Thanks Andy.  Fair enough (embedded vs related).

I forgot to mention the fetch-groups.  Indeed, I have no fetch groups explicity defined - either in the mapping file nor in the API usage.  Everything is using the default fetch group.
But based on your explanation and rereading the DN docs for the N-teenth time, https://www.datanucleus.org/products/accessplatform_5_2/jdo/persistence.html#fetch_groups it clearly identifies that the default fetch group is essentially primitive+ values (ie: int, String, BigDecimal, Date, etc).  So I didn't have any expectation that the Related Objects will be loaded as part of the default fetch group (particularly with maxDepth =1), but was hoping/expecting that at least the FK references would be loaded at the same time to avoid the extra +1 calls just to retrieve the FK values.  

Reading the Kodo docs for the same background, I now see the difference.  In Kodo, they clearly indicate that the object will be left out of the fetch-group, but the FK will be loaded for it.  Whereas in DN, it seems that if the object isn't explicitly added to the fetch-group, the FK will be skipped as well.

I have now added the "addPerson" and "modPerson" to the default-fetch-group, and defined my maxDepth as 1.  And seems to work more inline with expectations.

Thanks,

 

Eric



I'm okay with the extra queries to retrieve the "addPerson" and "modPerson", but don't want the extra DB hit to retrieve their FK values independently.

Is there a way I can configure the defaultFetchGroup to "preload" the FKs during the initial object fetch, without identifying that I want the full objects loaded each time?

Thanks,

Eric

With a single object relation with the FK at the selected object side there are only 3 things that would be feasible.

1. Load the related object, instantiating it. Put it in the fetch plan for this
2. Don't load the related object and leave for lazy loading. JDO default.
3. Include the PK (FK) field(s) in the SQL, and just store them some where and maybe use them later if required.

DataNucleus (and the JDO API also for that matter) allows options 1 and 2. Specification is explicit.

If you always want to load the related object with just the PK field(s) then you can always mark it as "fetch-fk-only" on that field, in the metadata.
If you only sometimes want to load the related object with just the PK fields, then put the field in the fetch plan for the SELECT, but also remove all other fields of the related object from the fetch plan (so it doesn't add a join to the related object).

I've tried to add the extension definition to my package jdo file but either it isn't working as I expect, or I've defined it incorrectly.  This is what I added:

<field name="addPerson" column="ADDPERSON" default-fetch-group="true">
    <extension vendor-name="datanucleus" key="fetch-fk-only" value="true"/>
</field>

With this definition, I was expecting that the object retrieval would basically retrieve the FK value for addPerson from my Manufacturer object.  However, it seems to be trying to retrieve the full object, and puts itself in an endless loop for some reason, causing a StackOverflow.  The datamodel isn't exactly what I listed above, but rather has a bit of a circular design (ie: the AuditPerson actually contains an instance of Audit).   When trying to debug the issue further, I see that DN is calling `dnReplaceFields()` method on my Manufacturer object, but it is difficult to identify which field it is.   I can see the field index value which is causing the endless loop, but can't determine which fieldname the index maps to.  I cannot find the metadata/mapping information where the fieldname is mapped to the field number.  In my case, when I put the debugger on the dnReplaceField in the call stack, I see that it is trying to load field 56, and that this field is causing the infinite loop.  How/where can I identify which fieldname field 56 is mapped to?

Similarly, the DN logs show me what I think is a list of fields that are loaded/not loaded when the object is retreived from cache (loadedFlags) (ex: (id="zzzz" taken from Level 1 cache (loadedFlags="[YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYNNNNNNNNNNN]").  But how do I identify which index is which field?





I would have expected that setting datanucleus.maxFetchDepth=1 would prevent any recursion from happening, but it is still endlessly trying to reload that same field #56.

Removing the `addPerson/modPerson` from the default-fetch-group stops the infinite loop.

That metadata extension works for me in this sample. How you apply it is up to you

Thanks; will look into that more closely.

Can you give me a pointer where to find the fieldindex -> fieldname mapping?  I was am unable to identify for a certainty which index is which field.   I'm not sure where to look for it.

Thanks,

Eric

Alphabetical IIRC, as shown here and here, clearly the absolute field number would have to allow for inheritance, so starting at the root class.

On Fri, Apr 30, 2021 at 05:03 AM, Andy wrote:

Alphabetical IIRC

Strange; that doesn't seem to align with my "stuck" index. I tried taking the field list and sorting them alphabetically, but the ~56th alphabetical fieldname doesn't align at with the problematic field.

Do you know if there is a map stored in a MetaData structure somewhere generated by the Enhancer or at load time?  I couldn't find something in the CMD, but figured it has to be floating around somewhere in a structure somewhere?


Thanks,

Eric

Hi @Andy,

I have finally been able to reproduce my error in a test case for you,using the sample app that you provided.  I have created a branch in github repo illustrate the problem. https://github.com/benze/test-jdo/tree/implicit-transaction-errors
Similarly, I have opened an issue in the rdbms module as I suspect it is something specific with the rdbms module (although I may be mistaken): https://github.com/datanucleus/datanucleus-rdbms/issues/369.

I am using an Abstract class with 2 subclass tables as my N-1 relation.  I do see a warning from DN that this is not fully supported so I don't know if that is the root cause, however, oddly enough, it works fine with transaction boundaries explicitly defined.

If, however, I use implicit transactions (and remove the `tx.begin()` and `tx.commit()`), the test fails with an endless loop and generates a StackOverflow issue.  I have traced down the issue into something in the `org.datanucleus.store.rdbms.ConnectionFactoryImpl.ManagedConnectionImpl#release()` as it tries to commitOnRelease, but get a little bit lost in the logic as to why the release is trying to iterate to the end of an open result set instead of just closing the result set as such.

I would be happy to help provide a fix for this, however, I am not able to fully grasp the complete theory behind the existing logic.

Thanks,

Eric