FWD » Core Development » Database

This task extracts the issue from #8752-3 and discussion from #6667-1054 to #6667-1087.

def temp-table tt field f1 as int index idx f1 desc.

create tt.
tt.f1 = 1.
create tt.
tt.f1 = 2.

open query q2 for each tt by f1.
get first q2.

open query q for each tt by f1.
get next q.
message tt.f1. // 4GL 1 (It is the first record found by q by f1 asc - not affected by q2)

find next tt.
message tt.f1. // 4GL 2 (It is the first record found by the FIND by f1 desc - not affected by q or q2)

open query q3 for each tt by f1.
get first q3.
get next q3.
message tt.f1. // 4GL 2 (It is the second record found by q3 by f1 asc - not affected by previous queries)

find next tt.
message tt.f1. // 4GL 1 (!!!! Important: It is the second record found by FIND by f1 desc)

The issue is that 4GL seems to use a buffer-level cursor to resolve the FIND queries, while FWD is honoring some offEnd and sortIndex properties of the RecordBuffer that can be spoiled by other queries that are run in parallel. Also, the cursor is not stateful in FWD, in the sense that FindQuery is simply honoring the current record loaded in the buffer (which can be loaded by another query).

Alexandru, this may not be a query, but a cursor on the index. For example, you can have FIND statements with different WHERE clauses (which can trigger different indexes, depending on the WHERE clause?).

Also, the BUFFER:FIND- methods do not affect this cursor:

def temp-table tt field f1 as int index idx f1 desc.

create tt.
tt.f1 = 1.
create tt.
tt.f1 = 2.
create tt.
tt.f1 = 3.
create tt.
tt.f1 = 4.
release tt.

find first tt.
message tt.f1. // 4

def var hb as handle.
hb = buffer tt:handle.
hb:find-unique("where tt.f1 = 2").
message tt.f1. // 2
find next tt.
message tt.f1. // 3

And not even release - FIND will still know on which record is positioned:

def temp-table tt field f1 as int index idx f1 desc.

create tt.
tt.f1 = 1.
create tt.
tt.f1 = 2.
create tt.
tt.f1 = 3.
create tt.
tt.f1 = 4.
release tt.

find first tt.
message tt.f1. // 4

release tt.

find next tt.
message tt.f1. // 3

Another issue is the AdaptiveFind, which is a performance improvement in FWD for looped FIND statements. This in turn can create a RandomAccessQuery, which needs to act like a FIND, and not as an internal RandomAccessQuery which can be used for AdaptiveQuery in FOR EACH blocks, for example.

I have some changes from trying to solve this for #9015 (I didn't need them in the end for that task), and my problem in those changes is that I'm using a record as the 'cursor' for the FIND statements. And this record can get deleted or evicted from the cache.

Alexandru, this may not be a query, but a cursor on the index. For example, you can have FIND statements with different WHERE clauses (which can trigger different indexes, depending on the WHERE clause?).

I see your point. Tested:

def temp-table tt field f1 as int field f2 as int index idx1 as unique f1 index idx2 as unique f2.
do transaction:
    create tt.
    tt.f1 = 1.
    tt.f2 = 1.
    create tt.
    tt.f1 = 2.
    tt.f2 = 2.
    create tt.
    tt.f1 = 3.
    tt.f2 = 3.
    release tt.
end.

find first tt where f1 > 0.
message tt.f1. // 1
find next tt where f1 > 0.
message tt.f1. // 2
find next tt where f2 > 0.
message tt.f2. // 3

This shows that the last NEXT is still honoring the current cursor and not creating another one. So this is not a cursor on an index, but neither a query with a fixed WHERE clause.
I am rather thinking this does a "snapshot" of the last loaded record by a FIND and uses that as a reference record.
So this is basically what we have now in the FWD run-time, but instead of using the currently loaded record in the buffer as reference, it should use the "last loaded record by a FIND".

Did we recently make changes that caused this issue to be a problem? Surely this has not always been an issue.

Trunk has a change to invalidate AdaptiveQuery right from the start if there are records in the dirty database. This caused some FOR EACH (which were doing large database SQLs) to work as a sequence of FIND NEXT (doing one-by-one retriaval from the database).

I suspect that this latent problem with offEnd and sortIndex is now more visible, considering that more queries behave like a sequence of FIND NEXT, requiring the offEnd and sortIndex to work properly.

The regression in #9015-29 is from trunk rev 15325, for #8433, where a FIND NEXT without the strong scope needs to stop finding records on the second iteration, like this:

do transaction:
   for each book: delete book. end.
   create book.
   book.book-id = 1.
   book.book-title = "abc".
   book.isbn = "1".
   release book.
   create book.
   book.book-id = 2.
   book.book-title = "abc".
   book.isbn = "2".
   release book.
end.

def buffer bbook for book.
def var i as int.
def var k as int.

do while true:
   // do for bbook while true:
    do while true:
      find next bbook where bbook.book-id > i no-error.
      if not avail bbook then leave.
      message k bbook.book-id.
   end.
   pause.
   k = k + 1.
end.

The strong scope makes the buffer re-open its most outer scope, and this requires its offend to reset (at least).

Otherwise, the findings in #9030-1 is new behavior which was not observed until now - I don't think this ever worked properly in FWD.

Alexandru, I've created task branch 9030a from trunk rev 15372. This is the first attempt to solve the buffer's internal cursor on which FIND statements use as a reference to navigate.

Note that the buffer's internal offend I think needs to be limited only to FIND statements, and not internal RandomAccessQuery's used for i.e. FOR EACH/FIRST loops.

BTW, in this attempt, the buffer's offend still had to be reset on outermost query scope. So it may be that this change is required, but not the complete fix.

Alexandru, I've created task branch 9030a from trunk rev 15372. This is the first attempt to solve the buffer's internal cursor on which FIND statements use as a reference to navigate.

I am deferring some of the discussions on #9241 to this task.

def temp-table tt field f1 as int index idx f1.

do transaction:
    create tt. tt.f1 = 1.
    create tt. tt.f1 = 3.
end.

message "example 1".
do transaction:
    find next tt where tt.f1 = 5 no-error.
    message avail(tt). // no
    find next tt where tt.f1 = 3 no-error. // find after 5
    message avail(tt). // no
end.

message "example 2".
do transaction:
    define buffer buf for tt.
    find next buf where buf.f1 = 2 no-error.
    message avail(buf). // no
    find next buf where buf.f1 = 3 no-error. // find after 2
    message avail(buf) buf.f1. // yes 3
end.

This shows that the reference used by FIND NEXT is not a record. In example 1, the reference is a virtual location in the index that has f1 = 5. In the second example, the virtual location is on f1 = 3.
I am checking out 9030a to review the current implementation. I also have some changes to FqlPreprocessor to detect the indexed properties used in range index queries.
The idea is to store a bit of information on the RecordBuffer reflecting the last FIND NEXT executed on it, so the next FIND NEXT will use it as reference. Instead of using a reference record, it will use a reference virtual record if that makes sense.

• attempt to analyze the where similar to the OE DB planner, identifying the parts used for the index range query and the parts used for filtering.
  • I am starting light: the where should formed out of AND clauses. We are interested only on the flat property matches over indexed fields - these are used for the index range query. Other clauses can be considered part of the filtering.
  • The virtual record will be formed out of the r-values from the property matches that resemble a prefix of the index (first N-1 should be EQUAL, Nth can be either equal or />= comparison for DESC).
  • I am planning to extend collectPropertyMatches to do that.
• after the execution of FIND NEXT, if no record is found, store the virtual record at the Record Buffer level.
• each subsequent FIND NEXT will augment with an active bundle based on this virtual record.
• what triggers the reset of the virtual record? still to be investigated.

Last interesting finding:

def temp-table tt field f1 as int index idx f1.

do transaction:
    create tt. tt.f1 = 1.
    create tt. tt.f1 = 3.
end.

message "example 1".
do transaction:
    def buffer buf for tt.
    find next tt where tt.f1 > 10 no-error.
    message avail(tt). // no
    create buf. buf.f1 = 100. release buf.
    find next tt no-error. // find after 10
    message avail(tt) tt.f1. // yes 100
end.

FIND NEXT stores the virtual record with f1 set on 10 and so the upcoming FIND NEXT will find the new record with f1 = 100.

• If the buffer is sent as parameter, it keeps it state in regard to FIND NEXT.
• If the table is sent as parameter (even input-output), the default buffer won't share the state of the buffer (expected, as the callee has its own buffers defined).
• If the table is by-reference, the buffer keeps its state.
• If the FIND NEXT operations are done in separate siblings do transaction blocks, the buffer still keeps its state.

I was trying to confirm that this is a buffer things and not a scope thing. I was having a wild thought that maybe the FIND NEXT was updating a scoped-state instead of a buffer-state. I think this can be denied by my findings.

Extra findings:

• doing a FIND NEXT on another index doesn't seem to reset the cursor
  

  def temp-table tt field f1 as int index idx f1 index idx2 f2.
  
  do transaction:
      create tt. tt.f1 = 1. tt.f2 = 1.
      create tt. tt.f1 = 5. tt.f2 = 5.
      create tt. tt.f1 = 10. tt.f2 = 10.
  end.
  
  message "example 4".
  do transaction:
     find next tt where tt.f1 = 4 no-error.
     message avail(tt). // no
     find next tt no-error. 
     message avail(tt) tt.f1. // yes 5
     find next tt where tt.f1 > -1 use-index idx2 no-error.
     message avail(tt) tt.f1. // yes 10
  end.

On the last message I would have expected to see yes 1 as the index was reset. I suppose that the first FIND is setting the sort index on the buffer. Changing the where/index doesn't reset that sort index.

FIND FIRST is not resetting the cursor:

def temp-table tt field f1 as int index idx f1 index idx2 f2.

do transaction:
    create tt. tt.f1 = 1. tt.f2 = 3.
    create tt. tt.f1 = 5. tt.f2 = 2.
    create tt. tt.f1 = 10. tt.f2 = 1.
end.

message "example 5".
do transaction:
   find next tt where tt.f1 = 4 no-error.
   message avail(tt). // no
   find next tt no-error. 
   message avail(tt) tt.f1. // yes 5
   find first tt where tt.f1 > -1 use-index idx2 no-error.
   message avail(tt) tt.f1. // yes 10
   find next tt no-error.
   message avail(tt). // no
end.

On last message, if the index was idx2, the FIND NEXT should have gone to f1 = 5, but it doesn't. It still iterates on the idx1.

ATM, I think the FIND NEXT statement freezes the index. Constantin, did you find otherwise?

• reintroduced off-end on RAQ to properly support invalidated AdaptiveQuery to avoid running infinitely.
• use java.util.Date in Record instead of presuming using the sql one.
• fixed unique-index-lookup checker from FQL preprocessor. This was relying on the property matcher that was changed in the meantime - it can return an empty list of matches instead on null.

I fixed this was some problems on a large customer application unit tests. There are still tests failing now and then, but the real problem is that it gets stuck at some point. Continuing investigation.

I will create a new 9030c branch to work on this as the a/b branches are quite old. I will checkout them only to pick up some changes.

Not taken: 9030a's record-based cursor: #9030-8 shows the reference is not a record.

I think the core discovery of 9030a is that the reference is not a record. I don't understand why it should not be taken. I prefer to port this as well.

Not taken: 9030b's removal of the buffer's offEnd/sortIndex and current-record fallback for all queries: trunk's AdaptiveQuery.quickRevalidate() relies on them, so only FIND statements will change.

This is a technical dept I want purged. The offEnd and sortIndex in the buffer are non-sense in the proposed implementation. I prefer to remove them in this effort. As long as the buffer will embed the AdaptiveFind for resolving the FIND on that buffer, then having sortIndex and offEnd in buffer doesn't make sense; they are resolved from AdaptiveFind query directly.

Shared property matcher. 9030b made the FQLPreprocessor property matcher lenient, which misleads H2 direct access, cache invalidation and server-side join decisions: uniqueFld = ? and fa + fb = fc looks like a unique lookup. I plan to keep the matcher as it is and add a separate list of the FIND matches.

AFAIK, Eduard and Razvan did changes to this area in the past; they may be able to provide feedback on how to improve FQLPreprocessor detection of property matches. If this is a big effort, I suggest having a sub-task to prepare grounds for it. IMHO, FQLPreprocessor is now a monster class that does all kind of optimizations and analysis. Some of this is dependent upon the order of FQL AST manipulation, intermediary analysis, etc. I prefer a TRPL alike approach of FQLPreprocessor, so that AST manipulation can happen in a structures series of phases, including annotations.

I opened #11953. Lets see if we have a resolution on that before stressing FQLPreprocessor even more.

Problems found in the 9030b code

These look reasonable, but they should be proven with tests first.

The key is frozen when the record is found: changing its fields later doesn't move the cursor.

This is what the snapshot does now in FWD.

OpenEdge behaviour

Reasonable, but should be proven with tests.

FIND NEXT/PREV SQL can't use the index. Moving past a key generates (k > ? or k is null), which is not an index range scan. For example, FIND NEXT alternating two indexes takes 244 ms (OpenEdge 4 ms). I plan to split it into two statements, k > ? then k is null. I will skip the second one when the position is not a known value, and for mandatory and primary key fields, which can't be unknown.

This should have been fixed in trunk already, but splitting the (k > ? or k is null) in two statements is a major change. It doesn't relate to FIND NEXT, but to any usage of active bundle (RAQ, prefetching, etc.). I prefer not to touch the policy here part of this task. I rather prefer to use something like ProgressiveResults or PagedResults just like an AdaptiveQuery does. As long as AdaptiveFind extends AdativeQuery, I think that we are simply leveraging the performance of the extended class. Changing that will mean changing any query performance. I am skeptic on this point.

REPEAT: FIND NEXT ... NO-LOCK loops on permanent tables. AdaptiveFind builds its query with a SHARE lock, so it selects ids only and reloads each row by id: one database round trip per row (5000 rows: 161 ms, OpenEdge 6 ms). I plan to preselect full records when the FIND is NO-LOCK, as FOR EACH NO-LOCK does; a FIND with a lock will use the worker query. Temp-tables have no locks and are not affected.

This is another item not related to this task. This is a design choice we have for all queries, not only FIND. I don't think we should scope this task to such decisions.

In essence I want to focus only on the functional part of this task. After we build the infrastructure to move the FIND implementation within the buffer, we can profile the target application and build on top of this architecture. Unfortunately, the current architecture (separate FindQuery) is stiff and we can't improve it at all. So, the task should be to refactor rather than to optimize.

Performance problems found

Please consider each and one of this in separate tasks eventually and work on the orthogonally after fixing #9030.

I will reanalyze each point to select what should go in 9030c and what should go in separate tasks. Some of the points above are already changed a little after I did some more tests earlier.

Thank you for the review. 9030c will contain only the functional changes, plus the removal of the buffer's offEnd and sortIndex. The performance work moves to other tasks.

Review points¶

• 9030a. My plan cited the wrong note: the finding that the reference is not a record is #9030-9, not #9030-8. 9030a keeps a Record as the cursor. 9030b implements #9030-9 with a virtual record, and 9030c builds on it. From 9030a I take only the rest: the cursor is used by FIND statements only, it is reset when the outermost scope of the buffer opens, and the BUFFER:FIND-* methods leave it alone.
• Snapshot. The cursor keeps a Record.snapshot() copy of the record found, made before a setter changes an indexed field. The buffer's snapshot can't be used: any query which loads a record replaces it, including those which must not move the cursor.
• FQLPreprocessor. It collects the comparisons of the FIND bracket (equalities, ranges and indexed BEGINS among the AND-ed terms) in a separate list, getFindMatches(), by running the existing property matcher on each term. The shared property matches, used by the H2 direct access, the cache invalidation and the server-side joins, stay unchanged.

Scope¶

• FindSupport, one cursor per buffer: the last record found, or a place between records after a FIND which found nothing, computed when a later FIND NEXT/PREV needs it.
• FOR statements and FINDs in a PRESELECT block move the cursor. OPEN QUERY, dynamic queries, BREAK BY, CAN-FIND and the BUFFER:FIND-* methods don't.
• RandomAccessQuery navigates FINDs from the cursor. FIND CURRENT on a permanent table and an ambiguous FIND UNIQUE (first match) move it.
• AdaptiveFind uses its preselected results only while the cursor is where the loop left it, else it continues from the cursor.
• StatementTemplate gets values for a place before or after all the values of an index field (constant true/false conditions), instead of the "last record" query of 9030b.

Removing offEnd and sortIndex from the buffer¶

With FINDs on FindSupport, only RandomAccessQuery reads them, but they still carry state between queries: setRecord() (RandomAccessQuery, PreselectQuery), the buffer's off-end listener (block start and end), AdaptiveQuery.earlyInvalidate() (start from the first/last record), AdaptiveQuery.repositionByID(), and a worker query created after the AdaptiveQuery results ran off-end (without it, 9030b looped endlessly).

1. Each RandomAccessQuery keeps its own off-end state, as PreselectQuery already does.
2. AdaptiveQuery passes it to the worker query it creates (simple and compound): the off-end of the results it replaces, or the start from the first/last record after an early invalidation.
3. RecordBuffer loses sortIndex, offEnd, the reversible off-end and their methods; setRecord() loses its sortIndex, offEnd and inverseSorting parameters. The buffer's snapshot stays (AdaptiveQuery.quickRevalidate() uses it).

This affects every query which navigates with RandomAccessQuery, so it will be a separate commit, with new tests for invalidated AdaptiveQuery instances (early, after off-end, several tables). Related: #7004, #10026, #11686.

Tests¶

Each test comes from a 4GL probe run on OpenEdge: the probe output becomes the expected values, and every test passes on OpenEdge. The tests run the same way on FWD (database table testdb, or a temp-table).

Moved out of #9030¶

The task column is subject to change, waiting for feedback.

NEXT/PREV statements which can't use the index. To move past the current key of a field which may be unknown, a FIND NEXT/PREV (and every query which navigates by index position) uses (k > ? or k is null), which is not an index range scan. E.g. FIND NEXT alternating two indexes takes 244 ms in FWD, 4 ms in OpenEdge. Proposal: split it into a range statement and a k is null statement, no is null part for mandatory and primary key fields, skip the statements which can find nothing (a range over a field pinned by an equality to the same value, an empty bracket after a FIND which found nothing), and cache the FQL text built per FIND in StatementTemplate. This changes the SQL of all these queries, not only FIND.

I think the new implementation for PagedResults can help. It retrieves a list of N records starting from a reference row (or virtual row) comparing to a ProgressiveResults that uses an OFFSET. If N is something like 10, the (k > ? or k is null) syntax will be used to retrieve a page of records in advance, so we may afford wasting time with the or granted that we gain N rows and not only one. This is basically the AdaptiveFind embedding we were talking about.

Fewer round trips for FIND NEXT/PREV. Each FIND NEXT/PREV runs its own SQL statement, and a looped NO-LOCK FIND (AdaptiveFind) preselects only ids and reloads each record by id. Reading the next records ahead (in growing pages) or preselecting full records would save the round trips, but the records read in advance may miss later commits of other sessions, e.g. the change of a non-indexed field.

The goal for the moment is to improve #11686 that uses FIND with NO-LOCK. So the current projection vs full record is reasonable for the performance task we aim for now.

So, I have 9030c created locally and functional, but it isn't pushed yet.

On the 18 scenarios of a performance benchmark I made, 9030c is within about 20% of trunk in 16 (best of 3 runs and first run). Two temp-table scenarios are 4-5 times slower:

• After the FIND FIRST which finds nothing, the looped FIND NEXT (AdaptiveFind) continues in its own preselected results.
  The whole loop runs one statement, but each FIND NEXT returns the record before the miss instead of the one after it (k = 2i instead of 2i + 2).
• With alternating indexes, trunk returns the same records as OpenEdge until a FIND NEXT runs off the end of the index (iteration 199). Then the remaining 801 FIND NEXTs find nothing and run no statement.

9030c returns the same records as OpenEdge in both, but it is slower because of how each FIND NEXT is written in SQL. To get the next record after the cursor, it asks for k > ? or k is null (a value of k may be unknown). H2 can't jump to the right place in the index for this condition: it reads the records one by one from the start of the temp-table until it reaches the cursor. So the further the cursor is, the slower each FIND NEXT gets. For example, in a temp-table of 10000 records, finding the record after k = 5000 reads 2501 rows with the OR, and only 2 rows with k > ? alone.

An experiment which splits that statement into a k > ? statement and a k is null statement (for all queries), skips the statements which can find nothing and caches their FQL text brings the two scenarios to 20 ms and 8 ms.

1. #9030: the removal of offEnd and sortIndex from the buffer.
2. #9030: Maybe implement the (k > ? or k is null) split for the FIND statements only (the cursor queries), so the SQL of the other queries doesn't change.
3. Separate task: the split for all the queries which navigate by index position.
4. #11686, after #9030 and #9078: the FIND in the buffer, with one AdaptiveFind per buffer

I am not sure I understand why trunk and 9030 diverge.
Is trunk faster because it uses AdaptiveFind and gathers more records in memory vs 9030c that does look-ups one by one? I don't fully understand why k > ? or k is null is a problem just in 9030c.

Alexandru Lungu wrote:

I am not sure I understand why trunk and 9030 diverge.
Is trunk faster because it uses AdaptiveFind and gathers more records in memory vs 9030c that does look-ups one by one? I don't fully understand why k > ? or k is null is a problem just in 9030c.

Trunk is faster because it does not find the right records or enough records.

• FIND FIRST that finds nothing, then FIND NEXT: trunk's AdaptiveFind keeps reading its own preselected results and ignores that the FIND FIRST moved the cursor. The whole loop is 1 statement, but every FIND NEXT returns the record before the miss (k = 2i instead of 2i + 2). 9030c follows the cursor, like OpenEdge: one lookup per FIND NEXT, 2 statements per iteration.
• FIND NEXT alternating two indexes: trunk also looks up one record at a time here, with the same 3 statements per iteration as 9030c. It is faster only because it runs off the end at iteration 199: the other 801 FIND NEXTs find nothing and run no statement (198 records found, OpenEdge 991).

Trunk is faster because it does not find the right records or enough records.

Bugs are the best performance improvements :) I understand.

More detail:

Got it.

Copy timing. The change is reported after the ASSIGN triggers, and after the batch in an ASSIGN statement, so a FIND NEXT inside an ASSIGN trigger sees the cursor's record already changed. The old setter hook copied it before the change. A buffer that isn't active doesn't copy it either.

Also account for FIND triggers and their lifecycle. I wonder if we can return error or no-apply from FIND trigger and stop the buffer from being loaded. In that case, will the cursor be updated?

I ran the #9030 ABLUnit tests and the FIND benchmarks on OpenEdge, trunk rev 16787 and 9030c/r16790.

ABLUnit tests¶

• Full ABLUnit suite that I was testing on related with other tasks (3477 tests): trunk 739 failures, 9030c 631; 108 fixed, none broken.
• The FIND cursor probes I created give the same results as OpenEdge; trunk differs in 7 of 8.
• The remaining failures are the same on trunk and belong to separate tasks (or should it be fixed now ?):
  • FindLockChangeTest (26): error 142 instead of 396 on updating a NO-LOCK record, and the update checks the session's lock instead of the lock of the FIND which found the record.
  • FindUnknownBracketTest (40) and TestQueryUnknownValue (38): comparisons with the unknown value in indexed queries.

Performance¶

10 runs of each scenario per session, a fresh FWD server for each session. Cold is the mean of the first runs, warm the mean of the other runs in Milliseconds.

All the scenarios in one session¶

10 sessions per build (cold: 10 samples, warm: 90).

Each scenario alone¶

Only one scenario per session, so its first run also pays the class loading and JIT compilation which the earlier scenarios pay in the full probe. 5 sessions per scenario and build (cold: 5 samples, warm: 45).

Overall, except the tests already mentioned the timings are pretty similar and within noise caused by the system load.

9030c: changes since r16797 (committed in 9030c/r16798)

• A FIND trigger can reject a record. If the trigger does RETURN ERROR or ends with an error, the record is not loaded. FIND FIRST/LAST/NEXT/PREV, FOR EACH and PRESELECT move on to the next record, and FIND UNIQUE finds nothing. The FIND cursor still lands on the rejected record, so the next FIND NEXT/PREV continues after it.
• Error 2873. A FIND on a buffer from inside that buffer's own FIND trigger now fails with error 2873. With NO-ERROR, the error is only recorded. The message text was also missing a space.
• FIND NEXT/PREV ... NO-ERROR. When nothing is found, ERROR-STATUS:ERROR is now set, as in OpenEdge. This doesn't apply inside a PRESELECT block.
• RETURN ERROR in database triggers. RETURN ERROR, or an error that leaves the trigger, now rejects the event. Before, RETURN ERROR aborted and the error was ignored. The conversion no longer warns about RETURN ERROR in a database trigger. If a CREATE trigger rejects the record, the new record is now discarded.
• FIND cursor and changed records. When an indexed field of the record under the cursor changes, through any buffer, the cursor keeps a copy of the record as it was before the change. The copy is taken before validation, flush or any trigger runs. After an ambiguous FIND UNIQUE, the cursor is placed on the first matching record right away.

Observed FIND trigger lifecycle:

• When it fires. The FIND trigger runs every time a record is loaded into the buffer: by FIND, FOR EACH, GET or FIND-FIRST. It does not run for FIND CURRENT or CAN-FIND.
• Rejecting a record. If the trigger does RETURN ERROR or ends with an error, the record is not loaded. FIND FIRST/LAST/NEXT/PREV, FOR EACH, GET NEXT and FIND-FIRST move on to the next record, and FIND UNIQUE finds nothing.
• NO-APPLY. RETURN NO-APPLY does nothing in a FIND trigger, the same as in OpenEdge. The record is loaded as usual.
• The cursor. The FIND cursor still lands on a rejected record, also inside FOR EACH, so the next FIND NEXT/PREV continues after it. If the trigger raises STOP, the buffer is left empty and the cursor stays on the rejected record.
• FIND inside the trigger. A FIND on the same buffer from inside its own FIND trigger fails with error 2873.

Known differences from OpenEdge, still open:

• When an unhandled error in the trigger rejects the record of a FIND-UNIQUE method, FWD also shows error 138; OpenEdge shows nothing.
• When a WRITE trigger rejects a change, FWD does not undo the change. This happened before these changes too.
• Inside a buffer's own FIND trigger, FOR EACH and GET NEXT on that buffer don't report error 2873 as OpenEdge does, and FIND-FIRST returns no where OpenEdge returns yes.
• An error in a FOR EACH body inside the trigger is caught by an enclosing DO ON ERROR UNDO, LEAVE. In OpenEdge it escapes that block and rejects the record.