Skip to Content
Aug 26, 2017 at 09:19 AM

XSOData POST creates too many local temporary tables



I've built 2 xsodata services with a 'create events before' modification exit to autogenerate the ID for an INSERT statement based on a sequence. My implementation is based on this tutorial: This is one of them:

function beforeCreate(param){
    let after = param.afterTableName;
    let pStmt = param.connection.prepareStatement('update "' + after + '" set ID = ID_SEQ.NEXTVAL, DATETIME = CURRENT_TIMESTAMP' );

My understanding is that every time I do a POST to these services which would trigger the modification exit, it creates a new local temporary table to do the modification on before the data ends up in the 'actual table' linked to the service.

After doing a bit more than 32.000 posts to the services (13.000+ and 19.000+ respectively), they suddenly stop working with an error 500. Executing a standard SQL INSERT statement into the target table still works, so the table is fine, it's just the service which is having a problem. This is the error given:

- POSTMAN error: "Service exception: [132] transaction rolled back due to unavailable resource"

- Trace XS Engine Alerts: "RS_Container : MM: exceed max number (32768) of containers."

- Trace XS Engine Executed Statements: "#create local temporary table "#NEW_MarketType140046384061216". Transaction rolled back due to unavailable resource: maximum number of row store containers (32768) exceeded"

I assume that too many local temporary tables have been created by the OData services and now 'the limit' is reached. My 3 questions:

- How can I reset/drop the temporary tables? I read that they should be dropped automatically at the end of the session, but I'm not sure how to 'end' my session? I had a look in "SYS"."M_TEMPORARY_TABLES" but there were only a few (1.100) in there, unrelated to my services.

- What should I change so that the modification exit drops that local table after it used it?

- Are there other/cleaner ways of achieving a POST to an OData service with autogenerated ID's so that they won't need/use any local temp tables? A table without an ID can't be used for a service to my understanding.

My HANA is on version