FWD » Core Development » Database

Alexandru: can there other state which we need to ensure is applied when doing 'direct write' to a buffer's record?

I see WRITE triggers are armed and changes are now reported. ASSIGN triggers, indexed fields and mandatory constraints rule out direct assign, so nothing to worry about. Before tables are forcing classical slow copy. What do double check:

• I see that mandatory constraints do not rule out fastCopy. Is it possible to bypass mandatory checks if using fastCopy?
• noLobs rules out fast-copy, but does not rule out direct assign. I can't recall why I decided to rule out LOBs from fastCopy though. I guess it was related to the mutability of lobs and the fact that copying them was not doing any deep-copy. But I think this happens with OrmUtils.set, so it should be investigated. In other words, buffer-copying a LOB using direct assign is doing a shallow copy instead of deep copy?
• Why is an indexed failed ruled out from OrmUtils.set? Maybe it relates to validation? Even so, validation is done at endBatch anyway.
  • RB.getIndexedProperties(unique) returns unique indexes only if called as getIndexedProperties(false) and returns all indexes if called as getIndexedProperties(true) - this is the opposite. I spotted this accidentally. Even if this is made to be the other way around, isPropertyIndexed is used only for creating BUFFER-COPY or COPY-TEMP-TABLE mappings and I wonder if these actually relied on the fact that they wanted "unique indexes" in the first place - and mistakenly they got those :)
  • I am OK with deferring this point to another task.

copy method from RB class receives srcDMO and dstDMO parameters as DataModelObject, but they could be instances of BufferReference for which case we have access to the underlying buffer or instances of BaseRecord for which we don't have access to the buffer.

We get the parameters as BaseRecords only from TB.copyAllRows method called either from TB.copyTempTable or TB.associateImpl). If for normal (slow) copy this case can be resolved easily, by passing the buffers as parameters to RB.copy method. In this case the unreportedChanges of destBuf gets changes and all it's listeners gets notified by that.

But for fast copy, I am not sure how we could capture any unreportedChanges in the destBuffer. Since in fast copy, we are just selecting all the records from source table and insert them in destination table.

I have a test case that recreates the problem:

def temp-table ttBook field name as char
                      field name1 as int
                      field dir as int
                      field recordStatus as int.

def temp-table ttBook1 field name as char
                       field dir as int
                       field recordStatus as int.

create ttBook1.
ttBook1.name = "book1".
ttBook1.dir = 1.
ttBook1.recordStatus = 0.
release ttBook1.

create ttBook1.
ttBook1.name = "book2".
ttBook1.dir = 1.
ttBook1.recordStatus = 0.
release ttBook1.

create ttBook1.
ttBook1.name = "book3".
ttBook1.dir = 1.
ttBook1.recordStatus = 0.
release ttBook1.

def var a as int.
a = 1.
for each ttBook1 where ttBook1.dir = 1 and ttBook1.recordStatus = 0:
   message a ttBook1.name.
   a = a + 1.

   create ttBook.
   ttBook.name = ttBook1.name.
   ttBook.name1 = 10.
   ttBook.dir  = ttBook1.dir.
   ttBook.recordStatus = 1.
   release ttBook1.

   TEMP-TABLE ttBook1:handle:COPY-TEMP-TABLE(temp-table ttBook:HANDLE, no, no, yes).
end.

In OE the output is:

1 book1

In FWD the output is:

1 book1
2 book2
3 book3

In this case a fast copy is performed from source table to destination table. However changes on ttBook1 buffer is not reported and results of the for-each query does not get invalidated. And the query continues its iteration with the cached result.

I came to the conclusion that the test from #10526-9 is falling because of another reason.

Before doing the performing the copy of all the records from the source table to destination, all the records from destination have to be deleted. This is of course if not the copy-temp-table is not performed in append mode.

When doing the delete of the destination table, the listeners on that buffer should be notified and invalidated if this is the case. However is case of an AdpativeQuery performed on temp-table such a listener will never be added. This happens because in PQ.registerRecordChangeListeners there is a stop condition before adding the listener registerRecordChangeListeners which will return the result of isLazyMode method. Looking at the Java doc of this method:

* Helper method to understand if this query is going to use lazy query results mode.
* Such mode automatically solves invalidation at the database side, so there is no
* need to invalidate in the server-side.

However for this case the results are invalidation is not solved on database side, and the results of the query continues to iterated over deleted records.

I tried removing the stop condition in PQ.registerRecordChangeListeners and it does solve the issue.

However for this case the results are invalidation is not solved on database side, and the results of the query continues to iterated over deleted records.

Is this because the FOR EACH doesn't iterate an index, but rather the implicit recid asc index? If so, it is similar to #10480.
Can you clarify why the DB fails to solve the laziness?

You can see that there is a release ttBook1 of the query buffer.

for each ttBook1 where ttBook1.dir = 1 and ttBook1.recordStatus = 0:
   ...
   release ttBook1.

   TEMP-TABLE ttBook1:handle:COPY-TEMP-TABLE(temp-table ttBook:HANDLE, no, no, yes).
end.

Results query created by H2 dialect is of LazyResultQueryFlat type.

While doing the RB.copyAllRows, all the records are deleted from the ttBook1 table and the current row from LazyResultQueryFlat.topTableFilter.cursor should be invalidated (active set on false).

Part of H2 code (TreeIndex.next) along with the comments:

 if (node != null && node.active == false) {
    // This node was affected by a delete, but the Cursor was still pointing to it.
    Row copy = node.row;

    node = tree.getNonInvalidatedNode(node, true); // will get the newly inserted row
  }
  else
  {
     node = next(node); // will get the next deleted node
  }

However the invalidation of the node row from LazyResultQueryFlat.topTableFilter.cursor does not happen because the buffer is empty form previous release statement.

And it seems that H2 will set active flag on false only on very specific delete statement (delete ttBook1 where ttBook1.recid = ?) executed from TB.clearBuffers called before deletion of all records. Subsequent delete ttBook1 statement that is executed after clearBuffers does not affect this flag at all.

But in my particular case because ttBook1 is empty from the release, any logic in clearBuffers will not be executed. This is why the active flag remains set, and subsequent next retrieves a deleted record.

However the invalidation of the node row from LazyResultQueryFlat.topTableFilter.cursor does not happen because the buffer is empty form previous release statement.

I can't tell how FWD buffers are related to H2 internals. The invalidation is a H2 internal mechanism that is unaware of what happens in FWD. H2 internals are triggers only by SQLs. The RELEASE causes an insert I suspect (?)

And it seems that H2 will set active flag on false only on very specific delete statement (delete ttBook1 where ttBook1.recid = ?) executed from TB.clearBuffers called before deletion of all records. Subsequent delete ttBook1 statement that is executed after clearBuffers does not affect this flag at all.

H2 active flag is set to false when the row is effectively removed from the database, with any DELETE, not necessary with a WHERE condition. If you do a DELETE ttbook1 where ttBook1.f1 = ?, it will mark the nodes as inactive.

But in my particular case because ttBook1 is empty from the release, any logic in clearBuffers will not be executed. This is why the active flag remains set, and subsequent next retrieves a deleted record.

As long as you run a DELETE statement to remove a record in the H2 database => the active flag is set to false => it can't be selected anymore. If this cycle is not correct, than H2 implementation has a flaw. Otherwise, FWD has a flaw.

Alexandru Lungu wrote:

I can't tell how FWD buffers are related to H2 internals. The invalidation is a H2 internal mechanism that is unaware of what happens in FWD. H2 internals are triggers only by SQLs. The RELEASE causes an insert I suspect (?)

They are not related directly. When executing COPY-TEMP-TABLE the destination table records has to completely wiped out, for that dstBuf.removeRecords has to be called.

Before deleting all the records in dstBuf.removeRecords with a delete someTable where someTable.multiplex = ? statement, the current buffers has to be cleared. For that clearBuffer has to be called. Which in the end will call @. And I am suspecting something strange in with this queries behaviour.

As long as you run a DELETE statement to remove a record in the H2 database => the active flag is set to false => it can't be selected anymore. If this cycle is not correct, than H2 implementation has a flaw. Otherwise, FWD has a flaw.

I looked a bit into H2 source code to see how these 2 queries are performed. In both cases I ended up in org.h2.command.dml.Delete.update method.

But the differences came up here:

1) delete someTable where someTable.recid = ?

try (RowList rows = new RowList(session)) {
            ...
            int rowScanCount = 0;
            for (rows.reset(); rows.hasNext();) {
                if ((++rowScanCount & 127) == 0) {
                    checkCanceled();
                }
                Row row = rows.next();
                table.removeRow(session, row);
                session.log(table, UndoLogRecord.DELETE, row);
            }
            ....
            table.fire(session, Trigger.DELETE, false);
            return count;
        }

For this query this part is executed, and the active flag is invalidated by table.removeRow(session, row);.

2) delete someTable where someTable.multiplex = ?

  ValueInt multiplex = condition instanceof Comparison ? ((Comparison) condition).getMultiplexId() : null;

        if (multiplex != null)
        {
           ArrayList<Row> rows = table.truncate(session, multiplex);
           boolean skipRefCascade = table.getRowCount(session) == 0;
           if (rows != null)
           {
              int rowCount = rows.size();
              for (int i = 0; i < rowCount; i++)
              {
                 Row row = rows.get(i);
                 session.log(table, UndoLogRecord.DELETE, row);
              }
              if (table.fireRow()) {
                 for (int i = 0; i < rowCount; i++)
                 {
                     Row row = rows.get(i);
                     table.removeRow(session, row);
                     table.fireAfterRow(session, row, null, false, skipRefCascade);
                 }
                 if (skipRefCascade)
                 {
                     table.deleteAllRefCascade(session);
                 }
              }
              table.fire(session, Trigger.DELETE, false);
              // the delete has performed
              return rowCount;
           }
        }

For remove all records query, this part of code is executed where table.removeRow(session, row); is not called by any method in this part of code.

I see. this is because ArrayList<Row> rows = table.truncate(session, multiplex); is wiping everything if it is the last index.

 if (prevCount == removedCount)
            {
                index.truncate(session);
            }

• registering to the log in order to support ROLLBACK if needed
• firing row delete triggers if any
• remove CASCADE constraints (usually for normalized extent tables).
• fire table delete trigger if any

• make active long. 0 means deleted, greater than 1 means the "epoch" in which the node was inserted (initially 1).
• each time we truncate the index, the epoch is incremented.
• an active node is one that has the active equal to current epoch.
• a deleted node is one that has a lower active than current epoch.

This way, the truncate will still be very fast and the functionality will be fixed to support bulk delete (aka truncate).

I committed the soltion for this case in 10526a_h2, rev. 63.

Eduard, what's the state of 10526a_h2 ? I assume this is mandatory for the original customer scenario to be solved?

Constantin Asofiei wrote:

Eduard, what's the state of 10526a_h2 ? I assume this is mandatory for the original customer scenario to be solved?

Run FWD_H2 unit-tests and no regression spotted on that side.

Alexandru: can we merge 10526a_h2 to fwd-h2-trunk so 10526a can upgrade the fwd-h2 rev?

Constantin Asofiei wrote:

Alexandru: can we merge 10526a_h2 to fwd-h2-trunk so 10526a can upgrade the fwd-h2 rev?

Yes, I am OK with this.

Eduard: please merge 10526a_h2 to fwd-h2 trunk.

Constantin Asofiei wrote:

Eduard: please merge 10526a_h2 to fwd-h2 trunk.

Done, is there a task for notification of h2 branches merges?

Eduard Soltan wrote:

Constantin Asofiei wrote:

Eduard: please merge 10526a_h2 to fwd-h2 trunk.

Done, is there a task for notification of h2 branches merges?

No, that merge is not official until FWD trunk includes it.

For now, please upgrade build.gradle to 1.63 in 10526a.

Constantin Asofiei wrote:

For now, please upgrade build.gradle to 1.63 in 10526a.

Sure. In rev. 16151.

Rebased to trunk 16358.