Clojurians Log v2

Reproduced this in isolation, with HTTP client and server. Server config:

;; xtdb.edn
{:log         [:local {:path ".../server/tx-log"}]

 :storage     [:local {:path ".../server/storage"}]

 :http-server {:port 3000}}

;; deps.edn
{:paths     []

 :mvn/repos {"ossrh-snapshots" {:url ""}}

 :deps      {org.clojure/clojure       {:mvn/version "1.11.4"}
             org.slf4j/slf4j-nop       {:mvn/version "2.0.12"}
             com.xtdb/xtdb-core        {:mvn/version "2.0.0-SNAPSHOT"}
             com.xtdb/xtdb-api         {:mvn/version "2.0.0-SNAPSHOT"}
             com.xtdb/xtdb-http-server {:mvn/version "2.0.0-SNAPSHOT"}}

 :aliases   {:xtdb {:jvm-opts ["--add-opens=java.base/java.nio=ALL-UNNAMED"
                               "-Dio.netty.tryReflectionSetAccessible=true"]}}}

Client config:

;; deps.edn
{:paths     ["src"]

 :mvn/repos {"ossrh-snapshots" {:url ""}}

 :deps      {org.clojure/clojure           {:mvn/version "1.11.4"} 
             org.slf4j/slf4j-nop           {:mvn/version "2.0.12"}
             com.xtdb/xtdb-core            {:mvn/version "2.0.0-SNAPSHOT"}
             com.xtdb/xtdb-api             {:mvn/version "2.0.0-SNAPSHOT"}
             com.xtdb/xtdb-http-client-jvm {:mvn/version "2.0.0-SNAPSHOT"}}

 :aliases   {:xtdb {:jvm-opts ["--add-opens=java.base/java.nio=ALL-UNNAMED"
                               "-Dio.netty.tryReflectionSetAccessible=true"]}}}

;; src/mcre/core.clj
(ns mcre.core
  (:require [xtdb.client :as xtc]
            [xtdb.api :as xt]))

(def xt-node-url "")

(comment
  (with-open [node (xtc/start-client xt-node-url)]
    (xt/q node
          "SELECT *
           FROM INFORMATION_SCHEMA.TABLES")) ; This SQL query succeeds

  (with-open [node (xtc/start-client xt-node-url)]
    (xt/submit-tx node
                  [[:sql "INSERT INTO foo (_id, a, b)
                          VALUES ('bar', ?, ?)"
                    [0 1]]])) ; Transaction succeeds

  (with-open [node (xtc/start-client xt-node-url)]
    (xt/q node 
          "SELECT * FROM foo")) ; Encounter error with SQL query

  (with-open [node (xtc/start-client xt-node-url)]
    (xt/q node
          '(from :foo {:bind [{:xt/id "foo"} *]}))) ; Encounter error with XTQL query
  :rcf)

After another transaction, query fails with the following error message:

; Execution error (ExceptionInfo) at xtdb.serde/fn (serde.clj:269).
; clojure.lang.ExceptionInfo: Ingestion stopped: class xtdb.indexer.live_index.LiveIndex$reify__24421$fn__24461 cannot be cast to class java.util.function.Function (xtdb.indexer.live_index.LiveIndex$reify__24421$fn__24461 is in unnamed module of loader clojure.lang.DynamicClassLoader @7ed91434; java.util.function.Function is in module java.base of loader 'bootstrap') {}

(Did delete .m2 to start from clean slate.)

Not sure where I might be going astray. 🤔

hey Stef - could you try upgrading to Clojure 1.12.0-rc1? we're starting to make use of the new SAM functionality because we've got quite a lot of interop

Hadn't thought of that. Thanks @jarohen!

yep I think it's fair to say that the SQL temporal table spec and all the various half-baked implementations just haven't been sufficiently simple or easy for users

SELECT *,
       NEST_MANY(SELECT *,
                        NEST_ONE(SELECT p.name,p.price
                                   FROM products FOR SYSTEM_TIME AS OF '2024-08-14 20:00' AS p
                                  WHERE p._id=ol.product_id) as product
                   FROM orderline ol
                  WHERE ol.order_id=o._id) AS orderlines
  FROM orders o

if I use a reference like AS OF o.datetime that fails to parse

querying just products at a parameterized time works ok, like:

{"sql": "SELECT p.name,p.price FROM products FOR SYSTEM_TIME AS OF $1 p",
 "queryOpts": {"args": [{"@type": "xt:timestamp",
                         "@value": "2024-08-14T20:00:00.000"}]}}

I was able to work around by

SELECT *,
       NEST_MANY(SELECT *,
                        NEST_ONE(SELECT p.name,p.price
                                   FROM products FOR ALL VALID_TIME p
                                  WHERE p._id=ol.product_id
                                    AND p._valid_from < o._valid_from
                               ORDER BY p._valid_from DESC LIMIT 1) as product
                   FROM orderline ol
                  WHERE ol.order_id=o._id) AS orderlines
  FROM orders o

for reference, my example case is here https://github.com/tatut/swixt/blob/main/xtdb-api.restclient#L76

column references aren't supported for AS OF currently, but we have debated it a little again recently, see https://github.com/xtdb/xtdb/issues/3447 and https://github.com/xtdb/xtdb/issues/3552

your original example's malformed-query indicates a poorly handled error but I can see you have an incorrect literal, try: (i.e. add TIMESTAMP and the :00 component)

SELECT *,
       NEST_MANY(SELECT *,
                        NEST_ONE(SELECT p.name,p.price
                                   FROM products FOR SYSTEM_TIME AS OF TIMESTAMP '2024-08-14 20:00:00' AS p
                                  WHERE p._id=ol.product_id) as product
                   FROM orderline ol
                  WHERE ol.order_id=o._id) AS orderlines
  FROM orders o

if you squint, in the middle of the malformed-query error you will see :pred ["clojure.core/some-fn" "xtdb.logical-plan/util-date?" "xtdb.logical-plan/temporal?"], :val "2024-08-14 20:00"

I would think this use-case would be perfect fit for “time travel”, so you don’t need to keep track of when a product’s price has changed… but can always fetch the price it had at a given order time

so AS OF would be much neater than doing all time and filtering

or perhaps even better if there was a way to refer to a specific row in time, instead of just referring to id 1 you could refer to 1@12312 (like in git you can permalink to some file at a particular commit hash)

mm, syntax like SELECT * FROM products._id.101@[12312, 234324] could be fun! for now though I would suggest building a compiler on top of HoneySQL or something if you want some short-hand 🙂

well a syntax isn’t really necessary, just a SQL datatype that references a particular row at a particular time

idk what it would look like, but it would be very handy for this example

like “I want this orderline A to refer to a product B as it exists currently” kind of like a permalink

perhaps a computed column like products._row_id that is an alias-like tuple composite key of _id + _system_from + _valid_from

I like that the temporality in v2 is more a first class citizen, than in v1… a permalink reference type would make it even better, much more convenient for the programmer than filtering

I see there’s temporal table support in some relational engines, but for some reason they haven’t really caught on. For example in mariadb, you need to turn off versioning for any schema change in the table, making it mostly useless as you lose history whenever the table changes