Clojurians Log v2

Interesting, can you give a minimal example of the query (or subquery)? What do you see if you run the ~same thing in XT Play? e.g. https://play.xtdb.com/?version=2.0.0-SNAPSHOT&type=sql&txs=W3sic3lzdGVtLXRpbWUiOm51bGwsInR4cyI6IklOU0VSVCBJTlRPIGRvY3MgKF9pZCwgZm9vKSBWQUxVRVMgKDEsICdiYXInKTtcbklOU0VSVCBJTlRPIGRvY3MgKF9pZCwgZm9vKSBWQUxVRVMgKDIsICdiYXonKTsifV0%3D&query=U0VMRUNUIF9pZCwgTkVTVF9NQU5ZIChTRUxFQ1QgcWF6IEZST00gZG9jcykgRlJPTSBkb2Nz I see {} repeated for each row

SELECT a._id, a.name, a.orderlines, NEST_MANY(SELECT *, 'orderline' as "@type" FROM orderline b  WHERE b.product_id = a._id) AS orderlines, 'products' as "@type" FROM products a

[
  {
    "_id": 2,
    "name": "Log from Blammo",
    "orderlines": [
      null
    ],
    "@type": "products"
  },
  {
    "_id": 1,
    "name": "Fine leather jacket",
    "orderlines": [
      {
        "quantity": 1,
        "@type": "orderline",
        "product_id": 1,
        "_id": 1,
        "order_id": 1
      }
    ],
    "@type": "products"
  }, ...

running against the docker in-memory test image, just pulled the latest

here’s the 2 transactions to setup the data if you like

## Post multiple
POST :url/tx
Content-Type: application/json

{"txOps": [{"sql": "INSERT INTO products (_id, name, description, price) VALUES ($1, $2, $3, $4)",
            "argRows": [[1, "Fine leather jacket", "A fine leather jacket for all pirating needs", 29.95],
                        [2,"Log from Blammo", "It's big, it's heavy, it's wood", 4.20],
                        [3,"Illudium Q-36 explosive space modulator", "Is that pesky planet in the way of your view of Uranus? Blow it up.", 42069],
                        [4,"Ball", "It's a ball... nothing more to it", 7.99]]}]}

## Post customer, order and some order lines
## order 1 fine leather jacket and 10 balls
POST :url/tx
Content-Type: application/json

{"txOps": [{"sql": "INSERT INTO customer (_id, name, address) VALUES ($1,$2,$3)",
            "argRows": [[1, "Max Feedpressure", {"street": "Somestreet 6", "postalcode": "90210",
                                                 "country": "FI"}]]},
           {"sql": "INSERT INTO orders (_id, datetime, customer_id) VALUES ($1,$2,$3)",
            "argRows": [[1, {"@type": "xt:timestamp", "@value": "2024-08-14T16:40:55.666"}, 1]]},
           {"sql": "INSERT INTO orderline (_id, order_id, product_id, quantity) VALUES ($1,$2,$3,$4)",
            "argRows": [[1, 1, 1, 1],
                        [2, 1, 4, 10]]}]}

I’ll try in xt play

no need - have https://play.xtdb.com/?version=2.0.0-SNAPSHOT&type=sql&txs=W3sic3lzdGVtLXRpbWUiOm51bGwsInR4cyI6IklOU0VSVCBJTlRPIHByb2R1Y3RzIChfaWQsIG5hbWUsIGRlc2NyaXB0aW9uLCBwcmljZSkgVkFMVUVTICgxLCAnRmluZSBsZWF0aGVyIGphY2tldCcsICdBIGZpbmUgbGVhdGhlciBqYWNrZXQgZm9yIGFsbCBwaXJhdGluZyBuZWVkcycsIDI5Ljk1KSxcbiAgKDIsJ0xvZyBmcm9tIEJsYW1tbycsICdJdCcncyBiaWcsIGl0JydzIGhlYXZ5LCBpdCcncyB3b29kJywgNC4yMCksXG4gICgzLCdJbGx1ZGl1bSBRLTM2IGV4cGxvc2l2ZSBzcGFjZSBtb2R1bGF0b3InLCAnSXMgdGhhdCBwZXNreSBwbGFuZXQgaW4gdGhlIHdheSBvZiB5b3VyIHZpZXcgb2YgVXJhbnVzPyBCbG93IGl0IHVwLicsIDQyMDY5KSxcbiAgKDQsJ0JhbGwnLCAnSXQnJ3MgYSBiYWxsLi4uIG5vdGhpbmcgbW9yZSB0byBpdCcsIDcuOTkpO1xuSU5TRVJUIElOVE8gY3VzdG9tZXIgKF9pZCwgbmFtZSwgYWRkcmVzcykgVkFMVUVTICgxLCAnTWF4IEZlZWRwcmVzc3VyZScsIHtcInN0cmVldFwiOiAnU29tZXN0cmVldCA2JywgXCJwb3N0YWxjb2RlXCI6ICc5MDIxMCcsXCJjb3VudHJ5XCI6ICdGSSd9KTtcbklOU0VSVCBJTlRPIG9yZGVycyAoX2lkLCBkYXRldGltZSwgY3VzdG9tZXJfaWQpIFZBTFVFUyAoMSwgVElNRVNUQU1QICcyMDI0LTA4LTE0VDE2OjQwOjU1LjY2NicsIDEpO1xuSU5TRVJUIElOVE8gb3JkZXJsaW5lIChfaWQsIG9yZGVyX2lkLCBwcm9kdWN0X2lkLCBxdWFudGl0eSkgVkFMVUVTICgxLCAxLCAxLCAxKSxcbiAgKDIsIDEsIDQsIDEwKTsifV0%3D&query=U0VMRUNUIGEuX2lkLCBhLm5hbWUsIGEub3JkZXJsaW5lcywgTkVTVF9NQU5ZKFNFTEVDVCAqLCAnb3JkZXJsaW5lJyBhcyAiQHR5cGUiIEZST00gb3JkZXJsaW5lIGIgIFdIRVJFIGIucHJvZHVjdF9pZCA9IGEuX2lkKSBBUyBvcmRlcmxpbmVzLCAncHJvZHVjdHMnIGFzICJAdHlwZSIgRlJPTSBwcm9kdWN0cyBh myself 🙂

thanks

I've raised https://github.com/xtdb/xtdb/issues/3636 to make sure we investigate soon

> checkpointing generates new checkpoints, even when no new data is inserted which version of XT are you using here? we resolved this in https://github.com/xtdb/xtdb/releases/tag/1.24.0 (in theory)

is the :checkpointerconfig repeated identically for Lucene? or does that look different? that would need to have the retention logic configured also - the checkpointers are effectively independent processes for the index-store and lucene

I had a feeling I made a mistake and would end up facepalming myself, but thanks so much for your help, and I apologize for the trouble. I specified the wrong dependency version facepalm

aha, no worries at all - glad we got to the bottom of it 🙂

Hey @lomin can you confirm that the issue is not caused by the usage within the temporary directory? if it is, then you could initially try to mitigate that with :keep-dir-between-checkpoints? false

do you see this log message at all? https://github.com/xtdb/xtdb/blob/06132bc1fad63944d144d79a90053b05d78a41c8/core/src/xtdb/checkpoint.clj#L77

The dir that is growing indefinitely in prod is defined like this in the system.edn:

:db.xtdb/checkpoint-dir                 #profile {:prod "/var/lib/myfolder/checkpoint"
                                                   :dev  "checkpoint"}

okay, thanks for confirming that, and have you tried configuring (e.g.) :retain-newer-than (Duration/ofDays 7) at the same time as a workaround? Or does that take no effect either?

Out of interest, are you unable to use real (~limitless) object storage for the checkpoints?

> have you tried configuring (e.g.) :retain-newer-than (Duration/ofDays 7) at the same time as a workaround? Yes, I tried different permutations of the config, but the results were the same. I started with the config in the docs. > Out of interest, are you unable to use real (~limitless) object storage for the checkpoints? I could, but since it is a side-project, I went with the cheapest possible configuration. I can also make a cron job that deletes the oldest files and directories, but I thought maybe I’m doing something wrong.

how about this log message https://github.com/xtdb/xtdb/blob/06132bc1fad63944d144d79a90053b05d78a41c8/core/src/xtdb/checkpoint.clj#L95

I suppose it's likely either a bug in the checkpoint retention logic or the delete-path command and its interaction with your filesystem

I found this log message. There were also some exceptions, but only in the lucene-checkpointing. The lucene checkpoint-folder also grows infinitely.

Aug 27 00:50:49 gongbuai java[1714049]: 16003116 INFO  xtdb.checkpoint - Uploading checkpoint at '{:xtdb.api/tx-time #inst "2024-08-27T00:48:11.489-00:00", :xtdb.api/tx-id 118770}'
Aug 27 00:50:49 gongbuai java[1714049]: 16003437 INFO  xtdb.checkpoint - Uploaded checkpoint: {:xtdb.checkpoint/cp-format {:index-version 22, :xtdb.rocksdb/version "7"}, :tx {:xtdb.api/tx-time #inst "2024-08-27T00:48:11.489-00:00", :xtdb.api/tx-id 118770}, :xtdb.checkpoint/cp-uri "/var/lib/gongbuai/checkpoint/checkpoint-118770-2024-08-27T00:50:49.230-00:00", :xtdb.checkpoint/checkpoint-at #inst "2024-08-27T00:50:49.230-00:00"}
Aug 27 01:58:28 gongbuai java[1714049]: 20061977 INFO  xtdb.checkpoint - Uploading checkpoint at '{:xtdb.api/tx-id 118989}'
Aug 27 01:58:28 gongbuai java[1714049]: 20062038 INFO  xtdb.checkpoint - Uploaded checkpoint: {:xtdb.checkpoint/cp-format "lucene-8", :tx {:xtdb.api/tx-id 118989}, :xtdb.checkpoint/cp-uri "/var/lib/gongbuai/lucene-checkpoint/checkpoint-118989-2024-08-27T01:58:28.109-00:00", :xtdb.checkpoint/checkpoint-at #inst "2024-08-27T01:58:28.109-00:00"}
Aug 27 06:14:07 gongbuai java[1714049]: 35400903 WARN  xtdb.checkpoint - Checkpointing failed
Aug 27 06:14:07 gongbuai java[1714049]: java.nio.file.FileAlreadyExistsException: /tmp/checkpointing7369762625654246037/_fzk_Lucene84_0.doc
Aug 27 06:14:07 gongbuai java[1714049]:         at xtdb.lucene$checkpoint_src$reify__69414$fn__69415.invoke(lucene.clj:291)
Aug 27 06:14:07 gongbuai java[1714049]:         at xtdb.lucene$checkpoint_src$reify__69414.save_checkpoint(lucene.clj:289)
Aug 27 06:14:07 gongbuai java[1714049]:         at xtdb.checkpoint$checkpoint.invokeStatic(checkpoint.clj:44)
Aug 27 06:14:07 gongbuai java[1714049]:         at xtdb.checkpoint$checkpoint.invoke(checkpoint.clj:41)
Aug 27 06:14:07 gongbuai java[1714049]:         at xtdb.checkpoint.ScheduledCheckpointer$run__68403$fn__68407.invoke(checkpoint.clj:83)
Aug 27 06:14:07 gongbuai java[1714049]:         at xtdb.checkpoint.ScheduledCheckpointer$run__68403.invoke(checkpoint.clj:82)
Aug 27 06:14:07 gongbuai java[1714049]:         at xtdb.checkpoint.ScheduledCheckpointer$schedule__68414$fn__68415.invoke(checkpoint.clj:104)
Aug 27 07:59:08 gongbuai java[1714049]: 41702485 INFO  xtdb.checkpoint - Uploading checkpoint at '{:xtdb.api/tx-time #inst "2024-08-27T07:58:17.853-00:00", :xtdb.api/tx-id 120378}'
Aug 27 07:59:08 gongbuai java[1714049]: 41702865 INFO  xtdb.checkpoint - Uploaded checkpoint: {:xtdb.checkpoint/cp-format {:index-version 22, :xtdb.rocksdb/version "7"}, :tx {:xtdb.api/tx-time #inst "2024-08-27T07:58:17.853-00:00", :xtdb.api/tx-id 120378}, :xtdb.checkpoint/cp-uri "/var/lib/gongbuai/checkpoint/checkpoint-120378-2024-08-27T07:59:08.603-00:00", :xtdb.checkpoint/checkpoint-at #inst "2024-08-27T07:59:08.603-00:00"}
Aug 27 09:30:22 gongbuai java[1714049]: 47176154 WARN  xtdb.checkpoint - Checkpointing failed
Aug 27 09:30:22 gongbuai java[1714049]: java.nio.file.FileAlreadyExistsException: /tmp/checkpointing7369762625654246037/_fzk.fdx
Aug 27 09:30:22 gongbuai java[1714049]:         at xtdb.lucene$checkpoint_src$reify__69414$fn__69415.invoke(lucene.clj:291)
Aug 27 09:30:22 gongbuai java[1714049]:         at xtdb.lucene$checkpoint_src$reify__69414.save_checkpoint(lucene.clj:289)
Aug 27 09:30:22 gongbuai java[1714049]:         at xtdb.checkpoint$checkpoint.invokeStatic(checkpoint.clj:44)
Aug 27 09:30:22 gongbuai java[1714049]:         at xtdb.checkpoint$checkpoint.invoke(checkpoint.clj:41)
Aug 27 09:30:22 gongbuai java[1714049]:         at xtdb.checkpoint.ScheduledCheckpointer$run__68403$fn__68407.invoke(checkpoint.clj:83)
Aug 27 09:30:22 gongbuai java[1714049]:         at xtdb.checkpoint.ScheduledCheckpointer$run__68403.invoke(checkpoint.clj:82)
Aug 27 09:30:22 gongbuai java[1714049]:         at xtdb.checkpoint.ScheduledCheckpointer$schedule__68414$fn__68415.invoke(checkpoint.clj:104)
Aug 27 15:04:10 gongbuai java[1714049]: 67204003 INFO  xtdb.checkpoint - Uploading checkpoint at '{:xtdb.api/tx-id 123662}'
Aug 27 15:04:10 gongbuai java[1714049]: 67204139 INFO  xtdb.checkpoint - Uploaded checkpoint: {:xtdb.checkpoint/cp-format "lucene-8", :tx {:xtdb.api/tx-id 123662}, :xtdb.checkpoint/cp-uri "/var/lib/gongbuai/lucene-checkpoint/checkpoint-123662-2024-08-27T15:04:10.136-00:00", :xtdb.checkpoint/checkpoint-at #inst "2024-08-27T15:04:10.136-00:00"}
Aug 27 16:45:36 gongbuai java[1714049]: 73290333 INFO  xtdb.checkpoint - Uploading checkpoint at '{:xtdb.api/tx-time #inst "2024-08-27T15:56:13.827-00:00", :xtdb.api/tx-id 123715}'
Aug 27 16:45:36 gongbuai java[1714049]: 73290725 INFO  xtdb.checkpoint - Uploaded checkpoint: {:xtdb.checkpoint/cp-format {:index-version 22, :xtdb.rocksdb/version "7"}, :tx {:xtdb.api/tx-time #inst "2024-08-27T15:56:13.827-00:00", :xtdb.api/tx-id 123715}, :xtdb.checkpoint/cp-uri "/var/lib/gongbuai/checkpoint/checkpoint-123715-2024-08-27T16:45:36.450-00:00", :xtdb.checkpoint/checkpoint-at #inst "2024-08-27T16:45:36.450-00:00"}
Aug 28 00:15:59 gongbuai java[1714049]: 100312914 WARN  xtdb.checkpoint - Checkpointing failed
Aug 28 00:15:59 gongbuai java[1714049]: java.nio.file.FileAlreadyExistsException: /tmp/checkpointing7369762625654246037/_jtx_i.liv
Aug 28 00:15:59 gongbuai java[1714049]:         at xtdb.lucene$checkpoint_src$reify__69414$fn__69415.invoke(lucene.clj:291)
Aug 28 00:15:59 gongbuai java[1714049]:         at xtdb.lucene$checkpoint_src$reify__69414.save_checkpoint(lucene.clj:289)
Aug 28 00:15:59 gongbuai java[1714049]:         at xtdb.checkpoint$checkpoint.invokeStatic(checkpoint.clj:44)
Aug 28 00:15:59 gongbuai java[1714049]:         at xtdb.checkpoint$checkpoint.invoke(checkpoint.clj:41)
Aug 28 00:15:59 gongbuai java[1714049]:         at xtdb.checkpoint.ScheduledCheckpointer$run__68403$fn__68407.invoke(checkpoint.clj:83)
Aug 28 00:15:59 gongbuai java[1714049]:         at xtdb.checkpoint.ScheduledCheckpointer$run__68403.invoke(checkpoint.clj:82)
Aug 28 00:15:59 gongbuai java[1714049]:         at xtdb.checkpoint.ScheduledCheckpointer$schedule__68414$fn__68415.invoke(checkpoint.clj:104)
Aug 28 01:13:37 gongbuai java[1714049]: 103771731 INFO  xtdb.checkpoint - Uploading checkpoint at '{:xtdb.api/tx-time #inst "2024-08-27T15:56:13.827-00:00", :xtdb.api/tx-id 123715}'
Aug 28 01:13:38 gongbuai java[1714049]: 103772026 INFO  xtdb.checkpoint - Uploaded checkpoint: {:xtdb.checkpoint/cp-format {:index-version 22, :xtdb.rocksdb/version "7"}, :tx {:xtdb.api/tx-time #inst "2024-08-27T15:56:13.827-00:00", :xtdb.api/tx-id 123715}, :xtdb.checkpoint/cp-uri "/var/lib/gongbuai/checkpoint/checkpoint-123715-2024-08-28T01:13:37.855-00:00", :xtdb.checkpoint/checkpoint-at #inst "2024-08-28T01:13:37.855-00:00"}
Aug 28 03:59:22 gongbuai java[1714049]: 113715918 WARN  xtdb.checkpoint - Checkpointing failed
Aug 28 03:59:22 gongbuai java[1714049]: java.nio.file.FileAlreadyExistsException: /tmp/checkpointing7369762625654246037/_jzu.cfe
Aug 28 03:59:22 gongbuai java[1714049]:         at xtdb.lucene$checkpoint_src$reify__69414$fn__69415.invoke(lucene.clj:291)
Aug 28 03:59:22 gongbuai java[1714049]:         at xtdb.lucene$checkpoint_src$reify__69414.save_checkpoint(lucene.clj:289)
Aug 28 03:59:22 gongbuai java[1714049]:         at xtdb.checkpoint$checkpoint.invokeStatic(checkpoint.clj:44)
Aug 28 03:59:22 gongbuai java[1714049]:         at xtdb.checkpoint$checkpoint.invoke(checkpoint.clj:41)
Aug 28 03:59:22 gongbuai java[1714049]:         at xtdb.checkpoint.ScheduledCheckpointer$run__68403$fn__68407.invoke(checkpoint.clj:83)
Aug 28 03:59:22 gongbuai java[1714049]:         at xtdb.checkpoint.ScheduledCheckpointer$run__68403.invoke(checkpoint.clj:82)
Aug 28 03:59:22 gongbuai java[1714049]:         at xtdb.checkpoint.ScheduledCheckpointer$schedule__68414$fn__68415.invoke(checkpoint.clj:104)

What is interesting to me is that the checkpointing generates new checkpoints, even when no new data is inserted in XTDB. If it wouldn’t, I would have not noticed that the retention does not work, because there is no traffic on my server.