2024-11-26 xtdb | Clojure Slack Archive

xtdb

Panel 2024-11-26T09:33:32.108799Z

I can't get select * table where _id = to work, via psql of clj + jdbc.

=> select * from bdr_submission limit 5;
                 _id                  |          bdr$submission$id           | bdr$submission$state |  bdr$submission$submitted_at  |   bdr$submission$updated_at   |          project$identifier
--------------------------------------+--------------------------------------+----------------------+-------------------------------+-------------------------------+--------------------------------------
 0018b249-118c-470b-87e7-a9ea69e07eeb | 6c1479f8-3bb1-434c-9d32-88cd05a2fd53 | Submission complete  | 2024-06-09 19:56:13.828+00:00 | "2024-06-09T19:58:44.222450Z" | d349b423-c76d-4567-8774-2faf2167f2fe
 0027603b-8ba4-412a-a5a1-489106dbb435 | 348f98c7-8b3e-4922-837b-eb9e9e49b4d2 | Submission complete  | 2024-06-09 05:30:36.071+00:00 | "2024-06-09T05:31:20.643995Z" | 4bb6401a-8a1a-488d-9d1e-ea98eb86b879
 002f21e4-de36-49bd-a1f9-f0eef34b8c36 | a6b64c04-6deb-4ede-a6c8-c184f80e1103 | Submission complete  | 2024-06-09 21:44:13.647+00:00 | "2024-06-09T21:48:14.371748Z" | 11259d22-7819-49b2-bbaf-9151d05beb7e
 00382caa-ffe6-4ad4-9204-e7087c8bffb3 | eafcd1fe-36d0-4d60-ab8d-79ce1e0999be | Submission complete  | 2024-06-09 19:30:15.188+00:00 | "2024-06-09T19:30:29.015255Z" | 52234baf-f2f1-4884-a331-8a0763d3b7ec
 003ea21c-b682-437d-9c32-04f1a2834a39 | b0c9c342-4492-46d6-bd22-0aed44fb3704 | Submission complete  | 2024-06-05 00:27:00.056+00:00 | "2024-06-05T00:29:47.853312Z" | 273dad3c-bb47-4b2a-947d-bcb2418f2bf2
(5 rows)

=> select * from bdr_submission where _id = '0027603b-8ba4-412a-a5a1-489106dbb435';
 _id | bdr$submission$id | bdr$submission$state | bdr$submission$submitted_at | bdr$submission$updated_at | project$identifier
-----+-------------------+----------------------+-----------------------------+---------------------------+--------------------
(0 rows)

thomas 2024-11-26T09:34:22.576769Z

Maybe double quoutes? " " ?

Panel 2024-11-26T09:48:24.066579Z

Same thing, it's not even specific to the _id / xt/id column, I can't retrieve anything by uuid

jarohen 2024-11-26T09:48:51.863759Z

try _id = UUID '<uuid>', if you've put UUIDs in, otherwise it'll be comparing a UUID to a string

🙂 1

thomas 2024-11-26T09:49:26.163179Z

☝️

Panel 2024-11-26T09:55:21.702519Z

Good call, I tried with '<uuid>'uuid for some reason...

jarohen 2024-11-26T09:58:52.974989Z

that should also work, but I don't recall whether that was before or since our most recent release 🙂

mpisanko 2024-11-28T16:48:48.988299Z

hi @avocabio the UUID should now work (if you get a new image): https://github.com/xtdb/xtdb/pull/3911

👏 1

Panel 2024-11-26T10:18:48.296769Z

I remember a recent video showcasing a trading web app demo backed by xtdb, is this online to play with / read source ?

refset 2024-11-26T11:26:24.504269Z

yep 🙂 see https://github.com/xtdb/traderx

🔥 1

refset 2024-11-26T11:27:26.094329Z

there's a couple of blog posts coming out about it soon - and if you fancy taking a look at a recent draft: https://deploy-preview-163--juxt-website.netlify.app/blog/bitemporal-traderx/ (feedback welcome 🙏)

Panel 2024-11-26T11:45:15.978849Z

And the new video is cool too https://www.youtube.com/watch?v=xaYHUKAzwUo

🙂 1

Panel 2024-11-26T11:50:28.010089Z

This is very cool, I can get a sub query to return all versions of a joined entity...

SELECT p.*, 
       NEST_MANY(
           SELECT bs.bdr$submission$state as state
           FROM bdr_submission FOR ALL SYSTEM_TIME bs
           WHERE bs.project$identifier = p._id
       ) AS submission_history
FROM project p limit 5

But it failed when I try to select * on the sub query

SELECT p.*, 
       NEST_MANY(
           SELECT bs.*
           FROM bdr_submission FOR ALL SYSTEM_TIME bs
           WHERE bs.project$identifier = p._id
       ) AS submission_history
FROM project p limit 5

From docker:

2024-11-26 22:15:21 11:15:21 | ERROR xtdb.pgwire | #error {
2024-11-26 22:15:21  :cause Unexpected type encountered by pgwire (class java.util.UUID)
2024-11-26 22:15:21  :via
2024-11-26 22:15:21  [{:type java.lang.Exception
2024-11-26 22:15:21    :message Unexpected type encountered by pgwire (class java.util.UUID)
2024-11-26 22:15:21    :at [xtdb.pgwire$json_clj invokeStatic pgwire.clj 558]}]

I also tried rename and exclude the _id column but same error.

jarohen 2024-11-26T12:00:52.854739Z

UUID...? I'm sure we've used those over pgwire before

refset 2024-11-26T12:01:22.647199Z

I'm surprised EXCLUDE wouldn't help - but I've just opened https://github.com/xtdb/xtdb/issues/3909 in any case

Panel 2024-11-26T12:09:48.997599Z

It also fail when using psql directly, same error from the docker container. Maybe relevant, I'm using next.jdbc.sql and the column name returned by the nest_many don't get translated back to keyword:

SELECT p.*, 
       NEST_MANY(
           SELECT bs.bdr$submission$state
           FROM bdr_submission FOR ALL SYSTEM_TIME bs
           WHERE bs.project$identifier = p._id
           
       ) AS submission_history
FROM project p limit 5

{:submission-history
 [{"bdr$submission$state" "Submission complete"}
  {"bdr$submission$state" "Submission in progress"}],
 :project/comment nil,
 :project/start-date #inst "1984-10-31T13:00:00.000-00:00",
 :project/date-modified nil,
 :project/contact-point nil,
 :project/name "Quadra Forest Block",
 :xt/id #uuid "0004f309-728f-479d-a93d-3743ac5ea9dc",
 :project/description nil,
 :project/date-created #inst "2011-08-15T14:00:00.000-00:00",
 :project/vba-identifier 132,
 :project/license nil,
 :project/status "Published",
 :project/end-date #inst "1984-10-31T13:00:00.000-00:00"}

refset 2024-11-26T12:21:29.586629Z

> It also fail when using psql directly, same error from the docker container. good to know, thanks, I think that's expected considering the error the NEST_MANY issue is definitely separate, but have you configured the :builder-fn per https://github.com/xtdb/xtdb/pull/3886/files#diff-23eaea0515eaa95e7e73b79d824b6cea2b1676e3491e587ad1ef2857a5e3da85R43-R47 ?

Panel 2024-11-26T12:27:13.989779Z

Yes I have the exact same setup, and cols get properly converted for top level and when nested in records but not from NEST_MANY

👍 1

refset 2024-11-26T12:52:23.396549Z

thanks for confirming - have opened a ticket for that also https://github.com/xtdb/xtdb/issues/3910

seancorfield 2024-11-26T19:14:46.936279Z

next.jdbc's builder will only operate on the top-level columns in a ResultSet -- it does not consider the values of those columns: those are just opaque values.

👍 1

refset 2024-11-29T09:53:06.126449Z

hey @avocabio UUID's are now fixed in edge (per https://github.com/xtdb/xtdb/pull/3911) > next.jdbc's builder will only operate on the top-level columns thanks Sean, good point - with that in mind, any thoughts on the best approach for to us to address the requirement? 🤔

🔥 1

jarohen 2024-11-29T10:03:41.454439Z

it will, yeah, but our builder should be doing a walk of the top-level values - I'll double-check when I'm back this afternoon as it's likely a derp on my part

👍🏻 1

Clojurians Log v2

xtdb