Hi @USJT4C8BT thanks for the report. Is everything else looking like it's working okay?

Or do you mean the retry never succeeds and you see this message + error repeatedly?

Thanks for your reply. Starting the memory node (def xtdb-mem-node (xt/start-node {})) is working okey. Yes, the retry never succeeded and above error flashing very fast (like PT0.1S) even I already have the setting :poll-sleep-duration "PT1S" .

I tried to use jdbc, hikari and postgres separately. They can work fine as follows:

(ns user.db-pg
  (:require [honey.sql :as sql]
            [juxt.clojars-mirrors.nextjdbc.v1v2v674.next.jdbc :as jdbc]
            [juxt.clojars-mirrors.nextjdbc.v1v2v674.next.jdbc.connection :as jdbcc]
            [clojure.java.data :as jd]
            [clojure.string :as str]
            [ :as xio]
            [xtdb.jdbc.psql :as psql])
  (:import (clojure.lang MapEntry)
           (com.zaxxer.hikari HikariConfig HikariDataSource)
           ( Closeable)
           (java.sql Timestamp ResultSet)
           (java.time Duration)
           (java.util Date)))

(def db-spec
  {:host     "localhost"
   :port     5432
   :user     "user"
   :password "pass"
   :dbname   "xtdb"
   :dbtype   "postgres"})

(def ds (jdbc/get-datasource db-spec))

(defn r [m] (jdbc/execute! ds (sql/format m)))

(defrecord HikariConnectionPool [^HikariDataSource pool dialect]
  Closeable
  (close [_]
    (xio/try-close pool)))

(comment
  (r {:select [:table_name] :from [:'information_schema.tables] :where [:= :table_schema "public"]})

  (let [jdbc-url (-> (jdbcc/jdbc-url db-spec) (str/replace #"\?$" ""))
        pool-opts {:jdbcUrl jdbc-url}
        pool (HikariDataSource. (jd/to-java HikariConfig pool-opts))]
    (try
      (catch Throwable t
        (xio/try-close pool)
        (throw t)))
    (->HikariConnectionPool pool {:xtdb/module psql/->dialect}))
  )

(subscribe [this after-tx-id f]
    (tx-sub/handle-polling-subscription this after-tx-id {:poll-sleep-duration (Duration/ofMillis 100)} f))

I think your config is wrong, as the pool is nil as per the error

can you share the full config map?

it should look more like https://docs.xtdb.com/storage/jdbc/#_jdbc_as_a_transaction_log

Thanks. My config is like this:

(defn jdbc-module
  [module]
  {:xtdb/module module
   :connection-pool
   {:dialect {:xtdb/module psql/->dialect}
    :db-spec {:host     "localhost"
              :port     5432
              :user     "user"
              :password "pass"
              :dbname   "xtdb"
              :dbtype   "postgres"}}
   :poll-sleep-duration "PT1S"})

(def node
  (xt/start-node
    {:xtdb/tx-log (jdbc-module jdbc/->tx-log)
     :xtdb/document-store (jdbc-module jdbc/->document-store)}))

hmm, maybe there are some missing quotes :thinking_face: perhaps try with (jdbc-module 'jdbc/->tx-log) (jdbc-module 'jdbc/->document-store) and {:xtdb/module 'psql/->dialect} ?

Thanks! Problem is fixed.

Yay 🙂

for example

(xt/q db '{:find [(count e)] :where [[e :type :some-type]]})
vs
(xt/q db '{:find [(count e)] :where [[e :type :some-type] [e :rows]]})

If I just find e and count the returned ids, I get the correct number

as well as with count-distinct aggregate… what am I missing in the behaviour of count

there is a warning of > Return a single count of all values including any duplicates (note: this always performs a scan - no statistics or other stateful materializations are used) in the docs, but I’m unable to determine what this means

ah, now I get it… because [e :rows] being a multi valued vector, it will count each row it finds for e?

the thing count is actually aggregating over is the output of the result stream (which is approximately the ~same thing as you see consuming from open-q without the aggs in :find)

in practice this result stream may not correspond with the bag or set semantics you are looking for, and it can be affected by the join order

btw, should this table have count-distinct? https://docs.xtdb.com/language-reference/datalog-queries/#find-aggregate it is only mentioned in the example below

ah yep, that's an oversight, thanks for mentioning it

for your specific example however, I'm struggling to understand the behaviour. Is the value under :type ever a set or vector?

no the type is a keyword, but the :rows is a vector

if I do (xt/q db '{:find [(count r)] :where [[e :type :some-type] [e :rows r]]}) I get the same count value as the latter of the above examples

so it seems to be counting all the rows

right, that makes sense, IIRC [e :rows] is just sugar for [e :rows _] which means it still uses the AEV index, so it will produce multiple (duplicate, because V is ignored) outputs when iterating

maybe the docs for count could be clearer as well, it wasn’t obvious to me that it wouldn’t have the same set semantics

arguably [e :rows] could use the AE index and then it would give the 'correct' result you are expecting

I think most people who are doing pagination will see count and think that is the correct aggregate to use (if you need a list of pages and have to have a total count of items)

ah it does say “including any duplicates”

I agree, I think it's not unreasonable to be expecting bag->set transformation to be happening before aggregates are applied

but I still didn’t get it

and arguably XT therefore lacks an easy way to perform set-based aggregates, although you can workaround it by wrapping your query in an outer query and performing the aggregation at that level (since subquery results get converted to sets before being returned up)

tbh, ideally we'd return bags, we get too many questions around the set behaviour, and the different behaviours of q with/without limit/offset/etc are counter-intuitive

but that'd be quite a breaking change

now introduce q2 function, as Rich would say to do 😄

For valid timestamp we recently added this. See the 1.22.1 release notes.

see also https://docs.xtdb.com/language-reference/datalog-queries/#custom-functions

There also is history predicate section on that doc page.

ah, yeah that would have been confusing without mentioning that I wasn't talking about the new built-in history predicates https://docs.xtdb.com/language-reference/datalog-queries/#_history_predicates - I was just showing what the alternative workaround approach (for tt)

Thank you!