[GH-ISSUE #225] Live query and indexing

gitea-mirror commented

2026-05-23 08:30:48 -06:00

Owner

Originally created by @TELECI-PROJECT on GitHub (May 6, 2023).
Original GitHub issue: https://github.com/appy-one/acebase/issues/225

Originally assigned to: @appy-one on GitHub.

Hi! I’m facing the following issue. It seems it is related to live queries and index updates.
I did some testing, my setup was working well when indexing by keys that are not included as parameters in live queries.

As I indexed my data by key/keys included in a live query my node script started

This is my system setup:
acebase-server@1.16.2
acebase-client@1.20.1
acebase@1.28.3

This is what works well:
I have data objects stored in ‘items’ folder, each one has unique ID.
Item/uniqueid/{category: category1, location: location1, status: 1}
Item/uniqueid/{category: category2, location: location1, status: 3}
Item/uniqueid/{..}
Etc.

Then I have two indexes set up:
Index(‘item’, ‘category’);
Index(‘item’, ‘location);

Also, I have a running a live query listening to changes on ‘status’ property (on my app client).

I’m adding new items, then updating them (status is updated) and later some items are removed. Data removal is done periodically (every 10min). All updates are done sequentially.

This is what not working well:
I have the same setup as above:

The difference is: now I have added an index:
Index(‘item’, ‘status);
I still have a running live query listening to changes on ‘status’ property as before (on my app client).
This works well until the data removal script has started to remove items.

Now I see the following error and node script restarts:
/loc1/xxx/xxxx/nodejs/node_modules/acebase-client/dist/cjs/request/index.js:89
reject(new error_1.AceBaseRequestError(request, null, err.code || err.name, err.message));
^

AceBaseRequestError: connect ECONNRESET XXXXXXXXXXXXXXXXXXX:1234
at ClientRequest. (/loc1/xxx/xxxx/nodejs/node_modules/acebase-client/dist/cjs/request/index.js:89:20)
at ClientRequest.emit (node:events:390:28)
at TLSSocket.socketErrorListener (node:_http_client:447:9)
at TLSSocket.emit (node:events:390:28)
at emitErrorNT (node:internal/streams/destroy:157:8)
at emitErrorCloseNT (node:internal/streams/destroy:122:3)
at processTicksAndRejections (node:internal/process/task_queues:83:21) {
request: {
method: 'PUT',
protocol: 'https:',
host: 'xxxxxxxxx',
port: '1234',
path: '/data/acebase_test/items/1197171',
headers: {
'AceBase-Context': '{"skipProcess":true,"acebase_mutation":{"client_id":"xxxxxxxxx","id":"xxxxxxxxxxxxx","op":"set","path":"items/1197171","flow":"server"}}',
'Content-Type': 'application/json',
'Content-Length': 12,
Authorization: 'Bearer xxxxxxxxxx=='
},
body: undefined
},
response: null,
code: 'ECONNRESET'
}

I don’t see any errors when my data removal script is disabled.
It seems deletion process interferes with the index. The index (on status key) gets corrupted and I have to recreate it. I have tried deleting data items using delete query as well.

Originally created by @TELECI-PROJECT on GitHub (May 6, 2023). Original GitHub issue: https://github.com/appy-one/acebase/issues/225 Originally assigned to: @appy-one on GitHub. Hi! I’m facing the following issue. It seems it is related to live queries and index updates. I did some testing, my setup was working well when indexing by keys that are not included as parameters in live queries. As I indexed my data by key/keys included in a live query my node script started This is my system setup: acebase-server@1.16.2 acebase-client@1.20.1 acebase@1.28.3 **This is what works well:** I have data objects stored in ‘items’ folder, each one has unique ID. Item/uniqueid/{category: category1, location: location1, status: 1} Item/uniqueid/{category: category2, location: location1, status: 3} Item/uniqueid/{..} Etc. Then I have two indexes set up: Index(‘item’, ‘category’); Index(‘item’, ‘location); Also, I have a running a live query listening to changes on ‘status’ property (on my app client). I’m adding new items, then updating them (status is updated) and later some items are removed. Data removal is done periodically (every 10min). All updates are done sequentially. **This is what not working well:** I have the same setup as above: The difference is: now I have added an index: Index(‘item’, ‘status); I still have a running live query listening to changes on ‘status’ property as before (on my app client). This works well until the data removal script has started to remove items. Now I see the following error and node script restarts: /loc1/xxx/xxxx/nodejs/node_modules/acebase-client/dist/cjs/request/index.js:89 reject(new error_1.AceBaseRequestError(request, null, err.code || err.name, err.message)); ^ AceBaseRequestError: connect ECONNRESET XXXXXXXXXXXXXXXXXXX:1234 at ClientRequest.<anonymous> (/loc1/xxx/xxxx/nodejs/node_modules/acebase-client/dist/cjs/request/index.js:89:20) at ClientRequest.emit (node:events:390:28) at TLSSocket.socketErrorListener (node:_http_client:447:9) at TLSSocket.emit (node:events:390:28) at emitErrorNT (node:internal/streams/destroy:157:8) at emitErrorCloseNT (node:internal/streams/destroy:122:3) at processTicksAndRejections (node:internal/process/task_queues:83:21) { request: { method: 'PUT', protocol: 'https:', host: 'xxxxxxxxx', port: '1234', path: '/data/acebase_test/items/1197171', headers: { 'AceBase-Context': '{"skipProcess":true,"acebase_mutation":{"client_id":"xxxxxxxxx","id":"xxxxxxxxxxxxx","op":"set","path":"items/1197171","flow":"server"}}', 'Content-Type': 'application/json', 'Content-Length': 12, Authorization: 'Bearer xxxxxxxxxx==' }, body: undefined }, response: null, code: 'ECONNRESET' } I don’t see any errors when my data removal script is disabled. It seems deletion process interferes with the index. The index (on status key) gets corrupted and I have to recreate it. I have tried deleting data items using delete query as well.

gitea-mirror added the

bug

indexes

labels 2026-05-23 08:30:48 -06:00

gitea-mirror commented

2026-05-23 08:30:51 -06:00

Author

Owner

@appy-one commented on GitHub (May 8, 2023):

Thanks for reporting, I'll dive into it asap!

@appy-one commented on GitHub (May 8, 2023): Thanks for reporting, I'll dive into it asap!

gitea-mirror commented

2026-05-23 08:30:52 -06:00

Author

Owner

@TELECI-PROJECT commented on GitHub (May 9, 2023):

Thanks, I did some more testing. I was able to catch another error.

The setup is the same as above:

I have an index on 'status' running,
Live query is running as before,
Then I update (status value changes) /items/itemd/{ status: other status value }

This corrupts 'status' index and I see this error message:

Lock "./acebase_xxxxxxx3v_2.acebase/items-status.idx" timed out! lock.release() was not called in a timely fashion
[32m[acebase_xxxxxxx3v_2][39m failed to set "items/1278796": Error: Could not achieve lock because the current lock ("./acebase_xxxxxxx3v_2.acebase/items-status.idx") was not released in time (and lock is flagged critical)
at /loc1/yyy/ccccc3/nodejs/node_modules/acebase/dist/cjs/thread-safe.js:52:33
at Array.forEach ()
at Timeout.timeoutHandler [as _onTimeout] (/home/yyy/ccccc3/nodejs/node_modules/acebase/dist/cjs/thread-safe.js:50:29)
at listOnTimeout (node:internal/timers:571:11)
at process.processTimers (node:internal/timers:512:7)
/loc1/yyy/ccccc3/nodejs/node_modules/acebase-client/dist/cjs/request/index.js:84
return reject(new error_1.AceBaseRequestError(request, response, code, message));

@TELECI-PROJECT commented on GitHub (May 9, 2023): Thanks, I did some more testing. I was able to catch another error. The setup is the same as above: 1) I have an index on 'status' running, 2) Live query is running as before, 3) Then I update (status value changes) /items/itemd/{ status: other status value } This corrupts 'status' index and I see this error message: Lock "./acebase_xxxxxxx3v_2.acebase/items-status.idx" timed out! lock.release() was not called in a timely fashion [32m[acebase_xxxxxxx3v_2][39m failed to set "items/1278796": Error: Could not achieve lock because the current lock ("./acebase_xxxxxxx3v_2.acebase/items-status.idx") was not released in time (and lock is flagged critical) at /loc1/yyy/ccccc3/nodejs/node_modules/acebase/dist/cjs/thread-safe.js:52:33 at Array.forEach (<anonymous>) at Timeout.timeoutHandler [as _onTimeout] (/home/yyy/ccccc3/nodejs/node_modules/acebase/dist/cjs/thread-safe.js:50:29) at listOnTimeout (node:internal/timers:571:11) at process.processTimers (node:internal/timers:512:7) /loc1/yyy/ccccc3/nodejs/node_modules/acebase-client/dist/cjs/request/index.js:84 return reject(new error_1.AceBaseRequestError(request, response, code, message));

gitea-mirror commented

2026-05-23 08:30:53 -06:00

Author

Owner

@appy-one commented on GitHub (May 18, 2023):

@TELECI-PROJECT I managed to reproduce the issue, looking into it now

@appy-one commented on GitHub (May 18, 2023): @TELECI-PROJECT I managed to reproduce the issue, looking into it now

gitea-mirror commented

2026-05-23 08:30:53 -06:00

Author

Owner

@appy-one commented on GitHub (May 18, 2023):

I spent the entire day on this issue, I am pretty sure #226 fixes it!
The fix has been published with v1.28.6, let me know if it works!

@appy-one commented on GitHub (May 18, 2023): I spent the entire day on this issue, I am pretty sure #226 fixes it! The fix has been published with v1.28.6, let me know if it works!

gitea-mirror commented

2026-05-23 08:30:54 -06:00

Author

Owner

@TELECI-PROJECT commented on GitHub (May 20, 2023):

Hi, thanks, I don't see any errors anymore.

What I noticed is there is an issue with the live query events emission.

When index on 'status' is enabled, live query is not emitting 'change' events. It seems 'remove' events are working fine. (I don't use 'add' event).

Everything works fine when there is no index on 'status'.

@TELECI-PROJECT commented on GitHub (May 20, 2023): Hi, thanks, I don't see any errors anymore. What I noticed is there is an issue with the live query events emission. When index on 'status' is enabled, live query is not emitting 'change' events. It seems 'remove' events are working fine. (I don't use 'add' event). Everything works fine when there is no index on 'status'.

gitea-mirror commented

2026-05-23 08:30:55 -06:00

Author

Owner

@appy-one commented on GitHub (May 20, 2023):

Are you sure this is not the right behavior? If you have a query with a filter on status == 1, and the status changes to 2, this will cause a remove event on your query because it does not meet the filter requirement. If you change any other field that is in the result set of your query (and still meets any filter requirements) that should emit change events.

The fact that you mention in works differently without the index in place does require further investigation on my end though!
It would be helpful if you can submit minimal code that reproduces it and demonstrates this faulty behavior, that would save me a lot of time trying to reproduce it. You can use my test code created for this issue as boilerplate code if that helps: https://github.com/appy-one/acebase/blob/master/src/test/issue-225.spec.ts

@appy-one commented on GitHub (May 20, 2023): Are you sure this is not the right behavior? If you have a query with a filter on `status == 1`, and the `status` changes to `2`, this will cause a `remove` event on your query because it does not meet the filter requirement. If you change any other field that is in the result set of your query (and still meets any `filter` requirements) that should emit `change` events. The fact that you mention in works differently without the index in place does require further investigation on my end though! It would be helpful if you can submit minimal code that reproduces it and demonstrates this faulty behavior, that would save me a lot of time trying to reproduce it. You can use my test code created for this issue as boilerplate code if that helps: https://github.com/appy-one/acebase/blob/master/src/test/issue-225.spec.ts

gitea-mirror commented

2026-05-23 08:30:55 -06:00

Author

Owner

@TELECI-PROJECT commented on GitHub (May 20, 2023):

Ok, I will think of the code reproduction soon.

It seems I was wrong about remove event.

I have two live queries, one is listening to status == 1, the other is listening to status == 2. (events on CHANGE and on REMOVE).

Test 1:
Index 'status' is enabled: my live query is firing no events.

Test 2:
No 'status' index enabled: my live query is firing events (CHANGE and REMOVE)

@TELECI-PROJECT commented on GitHub (May 20, 2023): Ok, I will think of the code reproduction soon. It seems I was wrong about remove event. I have two live queries, one is listening to status == 1, the other is listening to status == 2. (events on CHANGE and on REMOVE). Test 1: Index 'status' is enabled: my live query is firing no events. Test 2: No 'status' index enabled: my live query is firing events (CHANGE and REMOVE)

gitea-mirror commented

2026-05-23 08:30:56 -06:00

Author

Owner

@TELECI-PROJECT commented on GitHub (Jun 3, 2023):

Hi, please find information about my tests below:

This is the test code was using:
https://github.com/TELECI-PROJECT/acebase-testing/blob/main/testing-live-query-and-indexing
(this is based on your test code https://github.com/appy-one/acebase/blob/master/src/test/issue-225.spec.ts)

I was running it on Nodejs v18.14.0.
This is my setup:
acebase@1.29.0
acebase-server@1.18.0
acebase-client@1.21.0

The database folder was removed for every test.

Testing results:

TEST 1
I was running the code with all tree indexes:
await db.indexes.create('items', 'location');
await db.indexes.create('items', 'category');
await db.indexes.create('items', 'status');

There were no events fired by Acebase live query
Test result:
0 added, 0 changed, 0 removed

TEST 2
I was running the code with one index:
await db.indexes.create('items', 'location');
//await db.indexes.create('items', 'category');
//await db.indexes.create('items', 'status');

Acebase live query was firing events as expected
Test result:
1000 added, 144 changed, 1000 removed

Conclusion:
Acebase live query is firing events as expected if none of query filter parameters is indexed. In this case they are params ‘category’ and ‘status’. The live query won’t fire events if any of filter parameters is indexed.

The following two test scenario won’t work either (meaning the live query won’t emit any events):
await db.indexes.create('items', 'location');
await db.indexes.create('items', 'category');
//await db.indexes.create('items', 'status');

OR
await db.indexes.create('items', 'location');
//await db.indexes.create('items', 'category');
await db.indexes.create('items', 'status');

@TELECI-PROJECT commented on GitHub (Jun 3, 2023): Hi, please find information about my tests below: This is the test code was using: https://github.com/TELECI-PROJECT/acebase-testing/blob/main/testing-live-query-and-indexing (this is based on your test code https://github.com/appy-one/acebase/blob/master/src/test/issue-225.spec.ts) I was running it on Nodejs v18.14.0. This is my setup: acebase@1.29.0 acebase-server@1.18.0 acebase-client@1.21.0 The database folder was removed for every test. Testing results: TEST 1 I was running the code with all tree indexes: await db.indexes.create('items', 'location'); await db.indexes.create('items', 'category'); await db.indexes.create('items', 'status'); There were no events fired by Acebase live query Test result: 0 added, 0 changed, 0 removed TEST 2 I was running the code with one index: await db.indexes.create('items', 'location'); //await db.indexes.create('items', 'category'); //await db.indexes.create('items', 'status'); Acebase live query was firing events as expected Test result: 1000 added, 144 changed, 1000 removed Conclusion: Acebase live query is firing events as expected if none of query filter parameters is indexed. In this case they are params ‘category’ and ‘status’. The live query won’t fire events if any of filter parameters is indexed. The following two test scenario won’t work either (meaning the live query won’t emit any events): await db.indexes.create('items', 'location'); await db.indexes.create('items', 'category'); //await db.indexes.create('items', 'status'); OR await db.indexes.create('items', 'location'); //await db.indexes.create('items', 'category'); await db.indexes.create('items', 'status');

Rows
Columns

[GH-ISSUE #225] Live query and indexing #103