SQL selects retrieve partial data

CrateDB
1

Hey guys,

We are experiencing weird behavior in our single-node 4.5.5 CrateDB deployment.

Problem description: the first time we execute a SQL query CrateDB returns a subset of selected data, while the second time, executing the exact same query, returns the entire dataset.

Here is a query example, but I think you will find it quite normal:

SELECT *
FROM mtairqualityraw.etairqualityobservedraw
WHERE time_index BETWEEN '2023-05-23 10:45:00+00:00' AND '2023-05-23 11:00:00+00:00'
ORDER BY time_index ASC, entity_id ASC

23 May 2023-11:00:24.512 - [root] → First fetchall 9 lines:
23 May 2023-11:00:24.521 - [root] → Second fetchall 22 lines:

I would like to provide more information but the problem is difficult to catch.

One more thing, the problem has arisen recently, but the machine where Crate is running doesn’t seem too stressed to point to that as the problem.

Do you have any ideas or suggestions on how to proceed? We are already considering upgrading the node to the last 4.x version, but we would like to know whether this is something documented or at least identified.

As always, thanks in advance :slight_smile:

1 Like
2

Dear Felipe,

thank you for writing in. Let me start by citing our documentation on this topic, where I believe you are observing the corresponding behaviour, when quickly querying the table directly after inserting.

By default, the table-level setting refresh_interval is set to 1000ms, but that setting can be changed to fit your needs.

Other than the documentation, @proddata also shared a good explanation at CrateDB need timeout to see changes from insert/update/delete queries, which may be useful.

Please let us know if this helps already, or if you think you are observing a different issue.

With kind regards,
Andreas.

3

Dear Felipe,

was my response helpful to you in any way? Please let me know if you have further questions around this topic.

With kind regards,
Andreas.