Changing read limits and updating large collection sets

18th August 2016

Filed under: Featured
Comments: None

Read limits determine the number of instances that can be returned during a read() call. By default, the read limit is 4096 instances. Each data source can be configured to have its own limit. NexJ recommends that you do not change the limit unless you have a good reason to change it. You can change the read limit for a data source in the DefaultRelationalDatabase.datasource file directly or through the Development.environment file.

Changing the read limit in the DefaultRelationalDatabase.datasource file

To change the read limit in the DefaultRelationalDatabase.datasource file:

In NexJ Studio, open the datasource file.
Select the Schema
Select the Advanced
Edit the Read Limit

Capture1

Changing the read limit from the Development.environment file

To change the read limit from the Development.environment file:

In NexJ Studio, open the environment file.
Select the Data Source Connections
Select the required data source and select the Advanced
Edit the Read Limit

Capture2

Note: The Read Limit value will override the value from the DefaultRelationalDatabase.datasource file.

Performing work on a collection using the for-each and for-each-page functions

Normally, when you want to iterate through a collection to perform work, your code will be similar to the following:

(for-each
   (lambda (item) ;Assign the currently iterated Person record to the variable "item"
      (item'rebuildACL) ;Call the "rebuildACL" event on the current Person record iterated
   )
   (Person'read '() '() '() -1 0 #f) ;Read all Person records
)

(for-each

(lambda (item) ;Assign the currently iterated Person record to the variable "item"

(item'rebuildACL) ;Call the "rebuildACL" event on the current Person record iterated

)

(Person'read '() '() '() -1 0 #f) ;Read all Person records

)

For more information, about the for-each function see the online code help that is provided in NexJ Studio when you hover your cursor over for-each in a scratchpad.

This example will not work if the returned collection for the Person read returns more than the read limit. If a system uses the default 4,096 read limit, and the system has more than 4,096 Person instances, then the above example fails with the following error:

Error: Too many instances of “Person” returned by a query: limit the count to 4,096 or use a cursor.

1	Error: Too many instances of “Person” returned by a query: limit the count to 4,096 or use a cursor.

To handle more instances than the limit allows, you must use the for-each-page function. This function is similar to the for-each function but it reads and processes records in chunks called “pages” instead of all at once. For more information about the for-each-page function, see the online code help that is provided in NexJ Studio when you hover your cursor over for-each-page in a scratchpad.

;Read for all Person records, but in pages of no more than 1000 at a time
(for-each-page Person '() '() '() '() 1000 #f
   (lambda (page)
      ;For each page of 1000, iterate through one at a time
      (for-each
         (lambda (item)
            (item'rebuildACL)
         )
         page
      )
   )
)

;Read for all Person records, but in pages of no more than 1000 at a time

(for-each-page Person '() '() '() '() 1000 #f

(lambda (page)

;For each page of 1000, iterate through one at a time

(for-each

(lambda (item)

(item'rebuildACL)

)

page

)

Troubleshooting

If the rebuildACL event fails, the process terminates and the entire transaction is rolled back. NexJ recommends that you wrap the work around a try function so that you only discard problematic items. When an error is encountered while processing a page, retry the individual items in the page, log errors for the problematic item, and continue on (or handle the error in a different way if determined by a functional requirement). The following example shows how you can improve the code by refactoring out the action into a standalone function since it must be called more than once. This makes it more maintainable when the action you want to perform is not trivial.

;Function to perform the wanted action
(define (performAction item)
   (item'rebuildACL)
)

(for-each-page Person '() '() '() '() 1000 #f
   (lambda (page)
      (try ;try the entire page
         (for-each
            (lambda (item)
               (performAction item)
            )
            page
         )
         ;catch
         (lambda (e)
            ;Error encountered processing page. Try items individually for the failed page. Then handle the failing items.
            (logger'warn "Failure occurred while processing page size" (page'size) ". Falling back to trying 1 item at a time")
            (logger'debug "Error encountered:" e)
            (for-each
               (lambda (item)
                  (try
                     (performAction item)
                     ;catch
                     (lambda (e)
                        (logger'error "ACL Rebuild failed. Current object" (item':oid) "Skipping due to error:" e)
                     )
                  )
               )
               page
            )
         )
      )
   )
)

;Function to perform the wanted action

(define (performAction item)

(item'rebuildACL)

)

(for-each-page Person '() '() '() '() 1000 #f

(lambda (page)

(try ;try the entire page

(for-each

(lambda (item)

(performAction item)

)

page

)

;catch

(lambda (e)

;Error encountered processing page. Try items individually for the failed page. Then handle the failing items.

(logger'warn "Failure occurred while processing page size" (page'size) ". Falling back to trying 1 item at a time")

(logger'debug "Error encountered:" e)

(for-each

(lambda (item)

(try

(performAction item)

;catch

(lambda (e)

(logger'error "ACL Rebuild failed. Current object" (item':oid) "Skipping due to error:" e)

)

page

)

At this point, you can increase the logging to help with troubleshooting and statistics. The following example shows pre-commit and rollback logic that enable you to undo the unit of work for failed iterations, commit more than 16,384 instances at a time, and avoid the following error:

Too many instances added to the unit of work: limit the count to 16,384 or pre-commit.

1	Too many instances added to the unit of work: limit the count to 16,384 or pre-commit.

;keep track of number of entities processed
(define objectCount 0)
;keep track of the number of errors
(define errorCount 0)

;Function to perform the wanted action
(define (performAction item)
   (item'rebuildACL)
)

(for-each-page Person '() '() '() '() 1000 #f
   (lambda (page)
      (try ;try the entire page
         (begin
            (logger'debug "Processing" (page'size) "objects")
            (for-each
               (lambda (item)
                  (performAction item)
               )
               page
            )
            (pre-commit) ;Commits data, but only visible in this transaction
            ;increment count
            (set! objectCount (+ objectCount (page'size)))
            (logger'info "Done rebuilding ACL for one page of objects" (page'size) ". Total:" objectCount)
         )
         ;catch
         (lambda (e)
            ;Error encountered processing page. Try items individually for the failed page. Then handle the failing items.
            (logger'warn "Failure occurred while processing page size" (page'size) ". Falling back to trying 1 item at a time")
            (logger'debug "Error encountered:" e)
            (((invocation-context)'unitOfWork)'rollback #f) ;Rollback changes to last good pre-commit
            (for-each
               (lambda (item)
                  (try
                     (begin
                        (performAction item)
                        (pre-commit)
                        ;increment count
                        (set! objectCount (+ objectCount 1))
                        (logger'debug "Done rebuilding ACL for individual item. Total:" objectCount)
                     )
                     ;catch
                     (lambda (e)
                        (logger'error "ACL Rebuild failed. Current object" (item':oid) "Skipping due to error:" e)
                        (set! errorCount (+ errorCount 1))  ;keep count of the number of errors encountered
                        (set! objectCount (+ objectCount 1))  ;keep track of number of items processed
                        (((invocation-context)'unitOfWork)'rollback #f) ;Rollback changes to last good pre-commit
                     )
                  )
               )
               page
            )
         )
      )
   )
)

(logger'info "Completed rebuilding of ACL." objectCount "objects rebuilt")
;return:
errorCount

;keep track of number of entities processed

(define objectCount 0)

;keep track of the number of errors

(define errorCount 0)

;Function to perform the wanted action

(define (performAction item)

(item'rebuildACL)

)

(for-each-page Person '() '() '() '() 1000 #f

(lambda (page)

(try ;try the entire page

(begin

(logger'debug "Processing" (page'size) "objects")

(for-each

(lambda (item)

(performAction item)

)

page

)

(pre-commit) ;Commits data, but only visible in this transaction

;increment count

(set! objectCount (+ objectCount (page'size)))

(logger'info "Done rebuilding ACL for one page of objects" (page'size) ". Total:" objectCount)

)

;catch

(lambda (e)

;Error encountered processing page. Try items individually for the failed page. Then handle the failing items.

(logger'warn "Failure occurred while processing page size" (page'size) ". Falling back to trying 1 item at a time")

(logger'debug "Error encountered:" e)

(((invocation-context)'unitOfWork)'rollback #f) ;Rollback changes to last good pre-commit

(for-each

(lambda (item)

(try

(begin

(performAction item)

(pre-commit)

;increment count

(set! objectCount (+ objectCount 1))

(logger'debug "Done rebuilding ACL for individual item. Total:" objectCount)

)

;catch

(lambda (e)

(logger'error "ACL Rebuild failed. Current object" (item':oid) "Skipping due to error:" e)

(set! errorCount (+ errorCount 1)) ;keep count of the number of errors encountered

(set! objectCount (+ objectCount 1)) ;keep track of number of items processed

(((invocation-context)'unitOfWork)'rollback #f) ;Rollback changes to last good pre-commit

)

page

)

(logger'info "Completed rebuilding of ACL." objectCount "objects rebuilt")

;return:

errorCount

Use the pre-commit function instead of commit to ensure that the rest of the system doesn’t see the changes until they are complete, and when you want the changes to be visible to future operations from these transactions.

Note: If #t is used as the argument for the rollback — e.g., (((invocation-context)‘unitOfWork)‘rollback #t) — it will roll back to the last good commit. Which in this case, would undo even the past successful page iterations that have been pre-committed.

Sometimes the performAction event is not trivial and will end up updating many other objects. In those cases, you may need to move the pre-commit function into the inner loop or reduce the page size to avoid reaching the unit of work limit.

Summary of functions

The following list shows a summary of the functions used in the code examples:

(for-each … – Used for iteration. Should only be used for a small collection.
(for-each-page … – Used for paged iteration. Used when the collection can potentially exceed the read limit.
(commit) – Persist to database. Visible to the system.
(pre-commit) – Data held by DBMS implementation specific manner. Visible to the transaction only.
(((invocation-context)’unitOfWork)’rollback #t) – Roll back to the last good committed state. Same as (rollback).
(((invocation-context)’unitOfWork)’rollback #f) – Roll back to the last good pre-committed state.

Changing the read limit in the DefaultRelationalDatabase.datasource file

Changing the read limit from the Development.environment file

Performing work on a collection using the for-each and for-each-page functions

Troubleshooting

Summary of functions

Your feedback