Tracking: Known issues with SODA

[morgiyan]

This is to track known SODA issues.

Client-libraries required for SODA production use: 18.5 and above.
Oracle Database: 18 and above.

Known issues affecting SODA node.js are:

• (1) Bug 28533043 - this is an issue at OCI level affecting performance of "finds" that return multiple documents. Due to lack of reliance on OCI array fetch, which allows fetching multiple documents in one round-trip, fetching each document requires a round-trip to the database. Status: fixed, now getting backported to 18.x and 19.x DBRU.

• (2) Bug 29607136 - this is an RDBMS-side bug is in index specification processing: when multiple varchar2 fields are indexed, an error is produced (instead of a successful index create). The error is in automatic computation of maxLength field of the index spec. There's an easy workaround for this bug, which is to specify the maxLength field explicitly for each varchar2 field of the index spec. Status: fixed in DB 18.8 and 19.5.

• (3) sodaCollection.metaData should be an Object (as documented) but is a String. Status: fixed in node-oracledb 4.0.

• (4) getOne() will fail if Oracle Client is < 19.5. This broke in node-oracledb 5.0.0. The workaround (if you can't upgrade) is to explicitly set the fetch arraysize to 0: e.g. doc = await collection.find().fetchArraySize(0).key(key).getOne(); Status: Fixed in node-oracledb 5.1.

• (5) if you are using Oracle Database 21 (or later) and you create new collections, then check out the notes in Node-oracledb SODA Requirements.

Enhancement Requests

• Case insensitive sorting, see Case Insensitive Sorting using SODA #1099
• Need more examples.

[cjbj]

I'll start: one issue is that sodaCollection.metaData should be an Object (as documented) but is a String. This will change to an Object in node-oracledb 4.0

[charleaux]

Any idea when SODA will be ready for use in production?

[morgiyan]

Let me check with @cjbj, but I believe we should be removing the "preview" status any day now (it's just a matter of when Chris will be able to update the doc).

For known issues affecting SODA, see the comment at the top (we'll maintain a list of such issues, including status, there).

[charleaux]

Awesome 👍 I can't wait

[morgiyan]

Charles (@charleaux), the preview status has been removed. Please see the list of known issues above (in particular issue 1, the other two are minor). If you need the fix for issue 1 to go production, let me know. Thanks!

[charleaux]

Is there any reason SODA isn't supported with node prior to OracleDB 18.3?

I keep seeing this in the examples:

• Requires Oracle Database and Client 18.3, or higher.

[cjbj]

@charleaux there are some bugs in the OCI layer not fixed until the 18.5 & 19.3 clients. In particular a big memory leak. While SODA APIs in node-oracledb work with 18.3 client, you will hit these bugs. Hence we want you to upgrade before going production. Note these bugs do not impact the SODA Java API.

[morgiyan]

@cjbj, I think @charleaux might be asking about the database itself (as opposed to instant client). @charleaux, is that right? If that's what's being asked, I can reply to that.

[morgiyan]

Basically the new SODA impls (node.js/python/ODPI-C) can be supported on 12.1 and above Oracle Database, just like the old impls (Java/REST).

But it requires an RDBMS-side fix to be backported to these releases. It's doable, and then the programmatic check that prohibits running SODA node.js on releases earlier than 18 can be removed.

The only issue is that if we do that, users that do not have this backport installed and try to create a collection, will end up with a wrong default collection shape on releases earlier than 18 (it'll only store key/content for each document, as opposed to key/content/version/last-mod timestamp/created-on timestamp, which is what the default collection is expected to store). So this can be a source of potential confusion. Thoughts?

Of course, this comment is regarding the backend (Oracle Database) version. The client needs to be 18.5 and above, as @cjbj points out.

[charleaux]

Thank you for clarifying, you're right I was more referring to the database server side. I was more curious than anything.

[charleaux]

Thanks for making this happen!

I was able to shrink my code in many of my routes by ~40% in some cases switching to the soda api.

Maybe at some point we can get more examples around SODA? I'd like to see more in the different kinds of indexing as well as pagination. It's not critical for me since I've been able to dig into the other non-node SODA documents to figure out how things work but the examples have been useful for me when learning and it's very likely there are others who would benefit from it.

[cjbj]

@charleaux PR's for new examples are welcome

[morgiyan]

Great to hear that @charleaux!

As @cjbj say PRs are welcome, and I'll also make a note for us to add examples as soon we have some free cycles.

[charleaux]

@morgiyan @cjbj
Thanks guys, I know you're probably super busy as it is already. When things slow down on my end I'll see if if you still need any PRs for this also and come up with any I can think of.

[charleaux]

Just FYI in case anyone else was having these issues.

I was troubleshooting an issue where node would crash randomly and when it did crash sometimes this would show up in the logs (sometimes nothing would show up):
[Error: ORA-40736: Query-By-Example (QBE) filter is not valid JSON
JZN-00049: Input to JSON parser is empty] errorNum: 40736, offset: 0 }
�[31m[nodemon] app crashed - waiting for file changes before starting...�[39m

The filter wasn't anything complicated:

let filter = {
    $and: [
        { measureId: { $eq: `${performanceDataItem.measureId}` } },
        { period: { $eq: `${performanceDataItem.period}` } }
    ]
};

What resolved the issue for me was upgrading the instant client from 18_5 to 19_3.

[cjbj]

Tagging @morgiyan for comment on the ORA-40736.

[morgiyan]

@charleaux somehow SODA thought the JSON that was passed in for the QBE is invalid.

Could you post a small complete reproducer so that we can check that the syntax of the QBE is correct?

The above, for example, seems to be missing backticks for the template literals, and I can't tell what values the template literals evaluate to.

Will look into it once you post a reproducer. Thank you!

[cjbj]

@morgiyan I edited @charleaux's post and used triple backticks around the whole block; you can see the embedded ticks now.

[morgiyan]

Thanks @cjbj, still would like to get the values template literals would evaluate to.

[charleaux]

@morgiyan
The measureID was just something like this: an ID field "00476D3241D64F6EBF203AEBDE285F00"
and the period i was using something simple to rule out special characters "Jan00"

I only posted for informational purposes, since my issue was resolved by upgrading the client.

[morgiyan]

Thanks @charleaux, we'll check (since we are not aware of any such issues with 18.5). And thanks for posting this of course!

[morgiyan]

@charleaux we ran this exact QBE with 18.5 IC, and cannot reproduce the issue. Runs fine for us. Maybe you could double check that you're trying this exact QBE with 18.5 IC. Thanks.

[cjbj]

Oracle DB 20c is now available as a preview.

By default in the preview, Oracle Database 20c SODA collections use a new JSON storage type. Since older Oracle Client libraries do not understand the new type, if you are using an older Oracle client library to access Oracle Database 20c, then use a work around:

• Set the database's COMPATIBLE initialization parameter to < 20 so the default type remains unchanged.
• Alternatively, if you want to set COMPATIBLE to 20 in order to take advantage of 20c features, then you will need to do one of the following:
  - never create any new collections
  - explicitly use metadata and choose the storage type when creating collections

There is no problem if you are using Oracle Client libraries 20.

[cjbj]

getOne() etc will fail if Oracle Client is < 19.5. This broke in node-oracledb 5.0.0. The workaround (i.e for macOS users stuck with 19.3 client libraries) is to explicitly set the fetch arraysize to 0: e.g. doc = await collection.find().fetchArraySize(0).key(key).getOne(); We'll fix this in a future node-oracledb release.

[cjbj]

I've updated the original post with a link to info for users moving to Oracle DB 21.

[cjbj]

Playing with the SODA metadata cache feature of node-oracledb 5.2.0-dev shows a huge reduction in round-trips when repeatedly opening collections. This equates to a reduced load on the DB and faster, more scalable applications. The cache also needs Oracle Client 19.11 (or later) or Oracle Client 21.3+ (which isn't available yet), so to test it out, grab Oracle Client 19.11 and set sodaMetaDataCache to true when opening a connection pool.

Node-oracledb 5.2.0-dev also avoids a round-trip needed if a pooled connection has to check the DB version - which is done when using SODA (or AQ). This optimization is for Oracle Client 19 and earlier. Oracle Client 21.1 libraries already have an equivalent optimization.

Bottom line: if you're a SODA user, grab Instant Client 19.11 (even if you have 21.1) and try the SODA metadata cache in node-oracledb 5.2.0-dev installed from the master branch.