Node-oracledb: ALTER SESSION once for new session

[marlic7]

Is it possible to execute ALTER SESSION not for each connection fetch from pool (pool.getConnection) but once when real new session is created?

I did try to mark connection with custom property for eg: connection.isSessionReused to indicate if connection is new session or not, but with not success (custom properties on connection object seems not work). Are there any other solution to achieve the goal?

[cjbj]

Try using a logon trigger.

Currently there is no support for session tagging in node-oracledb https://docs.oracle.com/database/121/LNOCI/oci09adv.htm#LNOCI16620

[marlic7]

Thx for hint with logon trigger.

Is there any plan to implement "session tagging" feature in node-oracledb?

[cjbj]

@marlic7 There are many things on the ToDo list. Do you want to submit a PR with a patch?!

[marlic7]

I do not know well c++, but if I find some free time I will experiment with this.
thx @cjbj

[spali]

tagging would be a great feature, especially because as I guess that pooling already is based on the oci session pool that can handle that native.
Currently to prevent expensive initializing of sessions, I have to use generic-pool module for pooling and tagging connections, which is not a lot but still some unnecessary boiler-plat around the driver and also bypass the native available pooling.

@marlic7 tried that too to "hack" the connection object... but I stopped as I realized that always a new object is returned, also for reused connections. I think the native connection object is always wrapped in a new connection object on every getConnection().
My plan was to somehow access the native connection object to push it into an array and just check on getConnection() if the returned object is already in the array. But I couldn't figure out how to unwrap the native connection object to keep it in an array for quick comparison. This doesn't replace the power of native connection tagging, but at least it could have worked for simple session initializing.

[cjbj]

@spali thanks for the upvote.

What client & DB versions are you targeting?

Do you want to toss around some basic ideas of what the node-oracledb API should look like for multi-property tags - simple string attribute, JS object, etc?

[spali]

Currently using 12.1 client and DB. But 12.2 or even 18c will be a topic soon.
My current use case is only to do initial session settings per connection. So create connection event/callback or simple tag on the connection object would be enough.
If you want to stick on the native functionality of session pooling, then tagging would be the way to go. I know this feature from java where it is (if I reminded right) a simple string map. And if I got your Link right from 12.2 on even more.

But from the flexibility aspect, I would highly welcome the event/callback solution (to handle new connections and even clean disconnects would be great if a connection gets clean down) including to have at least some string property tagging.
I mean, as soon it's possible to save any kind of identifier on a connection, it's possible to identify it again and maintain any information in a custom js map.

[cjbj]

Brainstorm thoughts around using the OCI session pooling features:

pool.getConnection() would take:

• a string or array (needs to be ordered for tag priority) holding multi-property tags. It could have a single tag prior to 12.2.
• take a PL/SQL Callback function name for 'Session State Fix Up' that would be invoked when a session doesn't have all tags. This would have no effect prior to 12.2.

Session release is handled inside the OCI layer and isn't exposed. If it was available, what would you use it for?

[spali]

Sounds perfect to me.

A session release could be used to clean shutdown the session like logging, but is more a cosmetic thing.
But didn't know it's not exposed by OCI, so ignore this feature request. I would stay as much on the native functionality as possible.

[cjbj]

@anthony-tuininga pointed out to me that ODPI-C already supports some of this, including knowing the actual tag(s) that a session returned from getSession() has (remember the tags are hints and sessions may be returned to the app that have none to all of the tags actually set). This means node-oracledb can expose those tags. Whether this list is established before or after the 'Session State Fix Up' PL/SQL function is called, I don't know. But the values will be useful for anyone not able to use the 'Session State Fix Up' feature.

[spali]

Yep, the 'Session State Fix Up' feature does just eliminate some checks, which otherwise the user has to make after getting the connection by checking the tags. So no requirement.

[cjbj]

@marlic7 @spali I've pushed an early drop of connection pool tagging and session state fixup to the master branch. Compile it and let us know how it works. The development doc is at https://github.com/oracle/node-oracledb/blob/master/doc/api.md#connpooltagging

[cjbj]

I've pushed an update to the master branch to aid pooled connection re-tagging. This replaces the conn.close({tag:"abc"}) option with a connection.tag = "abc" property.

For people who care, when conn.close() occurs, the current value of connection.tag is passed down to the OCI Session pool as the session's tag. For people who don't care, you can just think of connection.tag as the connection's current tag. After a pool.getConnection({tag: "def"}) call, the connection will have connection.tag set. This is a read/write value, that you can set or reset it as you like. RTM and let us know your comments soon before we freeze node-oracledb 3.1.

[cjbj]

Node-oracledb 3.1 is now available with session tagging: https://blogs.oracle.com/opal/use-node-oracledb-31-connection-tagging-to-efficiently-set-session-state

[spali]

@cjbj
Im so sorry for answering that late... had a lot of other priorities.
But finally tested your solution now and it works great.
Thank you so much.

Just one feedback/question, if I understand the documentation right, a tag is always a string.
So what really changes for an implementation with 12.2 client. I assume the initSession callback always gets a string. So parsing is anyway to be done by the callback itself and can be used before 12.2 client that way. Or does oracle client 12.2 then prioritize the selected connection differently i.e. based on the amount of matching tags?
Maybe this could be made a bit more clear in the documentation?

Edit: ok got the answer by myself: https://blogs.oracle.com/opal/use-node-oracledb-31-connection-tagging-to-efficiently-set-session-state

[cjbj]

I'll review the doc (again!) - thanks.

In the recent Office Hours recording I go over the features. See https://www.youtube.com/watch?v=OSSDZlWEDTI starting at the 8:50 mark. In this, I identify three use cases:

1. When all connections in the pool should have the same state (Solution: use a callback without tagging)
2. When connections in the pool require different state for different users (Solution: use a callback and tagging)
3. When using DRCP in conjunction with the connection pool (Solution: use a PL/SQL callback and tagging)

Check out the video for details.

There are two things going on in our sessionCallback implementation:

1. Oracle 12.2's underlying "Oracle Call Interface" library Session Pool implementation which requires the "a=b;c=d" multi property tag syntax. The OCI Session Pool also supports only a PL/SQL callback.
2. A layer above that in node-oracledb providing the optional ability to call a Node.js function, usable with any Oracle version, and usable with or without OCI Session Pool tags.

In older Oracle versions tags can be simple strings, but with 12.2 they must be multi-property so the doc is trying to encourage multi-property syntax.

In Oracle 12.2 the OCI session pool will do some parsing on the requested tag "a=b;c=d" properties and apply some heuristics about which connection to select for use. For example it may select a connection with tag "c=d;a=b" since that would be considered a match by the session pool. And if matchAny is used, the pool may decide to select connections with additional (or other) properties too. After the connection is selected for use by the pool, a Node.js callback will be invoked if the whole tag strings aren't identical (because it uses a quick string compare). A PL/SQL callback will be invoked if the properties values don't match, regardless of property key order in the tag. Finally, of course, the getConnection() call returns to the application.

The heuristics aren't worth worrying about, to be honest, and the OCI doc is going to be the best reference. In summary, a connection with the best match will be selected and the callback invoked if needed.