Clojurians Log v2

Hi folks, apologies for resurrecting an old thread, but just in case you didn't already read the latest release notes - we now have working Postgrex support: https://docs.xtdb.com/clients/elixir.html

@torkan https://github.com/xtdb/xtdb/issues/3737 if you'd like to follow its progress

Excellent! 😄 Thanks!

Exciting that my days of the insanity that is update-in-place might be coming to an end in the not too distant future 😛

Have you guys landed on "how much of postgres" you will aim to emulate btw?

Enough™ haha

joking aside, it'll be enough to get the tools working and understanding what's in the database, but we're saying that the end-user should be expecting to talk XTDB rather than Postgres

Yeah, completely understandable really

i.e. we're explicitly not going down the 'we're drop-in compatible, you won't be able to tell the difference' route (as a number of other Postgres wire-compatible dbs have) - it's a completely reasonable avenue, just not the one we want to go down 🙂

Yes, I'll definitely miss pg_trgrm for easily-setup searching, but other than that it's still an excellent trade-off imo 😛

well, I won't rule it out, there's nothing technical stopping us (or someone else, given it's F/OSS!) implementing that on top of XT - it's the usual "time/resources" issue 🙂 (and indeed, we've added a number of Postgres SQL functions/extensions already)

Ah, so you're doing a little of that as well, very cool!

Not sure how familiar you are with the Elixir ecosystem, but if setting up a new Phoenix project with postgres works, then you're golden 😛

personally, not at all, I'm afraid 😅 but we have a few Elixir folk at JUXT I'm hoping to entice into writing the test harness

Haha, well then they'll set you straight once they start trying it out 😆

Ecto (the main db lib/wrapper) is fairly opinionated, and assumes it will be handling automatic migrations etc when using Postgres, but all of that can be easily bypassed

yes, I did an Erlang library for xtdb v1 that mapped erlang records and/or elixir structs to documents… and I didn’t use ecto at all. but perhaps v2 can use it better due to being SQL

Yeah, writing an ecto-adapter for v1 would be quite an undertaking I'm guessing 😅

xtdb v2 does more than postgresql, as you can so neatly use NEST_MANY to pull linked data… no need to do what most SQL abstractions spend lots of code doing: turning rectangular resutls back into nested data

it was definitely a missing piece for us, yeah ☺️ > spend lots of code doing: turning rectangular results back into nested data spent far too much of my pre-XTDB life doing exactly that 😅

Haha yes

That one will be probably off limits when just using the standard ecto postgres-adapter though, due to all the automatic type casting etc that relies on the table schema introspections

But I don't see it as unlikely that there will be enough community excitement to make that happen 🙏

Fwiw I just reached out to the ecto-team in the elixir slack, if I hear anything worthwhile back from them I'll post it here

hey, thanks @torkan ☺️ tbh, we're not as likely to now that we have better PostgreSQL tooling compatibility - we're already using XT2 from both NodeJS and Python using their normal Postgres drivers. We're currently going through a bug bash on the queries the different drivers/tools throw at us, so if you run into anything with yours, please do let us know 🙂

A very understandable approach 😄 Some of the de-facto drivers/client libs in other languages have quite a bit of very postgres-specific behavior that in practice makes xtdb incompatible atm

For example, Postgrex in Elixir queries the db for expected types, which makes it just crash 😅

Heh, yep, we've been running into all of those 😅 we should be significantly better on this now though, so if you're hitting any specific issues with your driver of choice, could you file a bug?

aha, Elixir, niice 🙂

one we haven't tried, openly 😄

we also have a 'nightly' Docker label which contains a good few fixes over the b1 tag - could you give that a try?

Ah nice, will definitely try it out

are you using Postgrex?

Yup!

I assume 99% of all elixir users would want to be able to use Ecto (a db-agnostic abstraction for defining schemas/queries/etc), which in turn has an adapter for using Postgrex

On nightly:

09:21:18 | DEBUG xtdb.pgwire | Interpreting SQL:  SELECT t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,
       coalesce(d.typelem, t.typelem), coalesce(r.rngsubtype, 0), ARRAY (
  SELECT a.atttypid
  FROM pg_attribute AS a
  WHERE a.attrelid = t.typrelid AND a.attnum > 0 AND NOT a.attisdropped
  ORDER BY a.attnum
)

FROM pg_type AS t
LEFT JOIN pg_type AS d ON t.typbasetype = d.oid
LEFT JOIN pg_range AS r ON r.rngtypid = t.oid OR r.rngmultitypid = t.oid OR (t.typbasetype <> 0 AND r.rngtypid = t.typbasetype)
WHERE (t.typrelid = 0)
AND (t.typelem = 0 OR NOT EXISTS (SELECT 1 FROM pg_catalog.pg_type s WHERE s.typrelid != 0 AND s.oid = t.typelem))

I'm assuming this is due to Postgrex using binary-mode, and wants to know how to handle the response and map the types

right, yep - my guess would be that we don't have pg_range yet, but will file an issue for it and see what we can repro 🙂 thanks for getting in touch!

Ah right, so your long-term ambition is to basically be more or less completely "pg-compatible"?

I'd imagine it'll be hard to replicate the pg_type table for example, since it's all built on the assumption that tables conform to a given schema 😅

yep, that's the plan (at least for outside of Clojure) - the idea being we get so much benefit from tooling/libraries if we can achieve that

pg_type's one of the better ones - the difference with us is that rather than returning the schema specified through create/alter table, we return the schema inserted through the documents themselves

fwiw, I’ve been using the HTTP API (not in production) and it is very simple to use from basically any language that has http client and json support

Being pg-compatible is definitely the way I would go as well, so makes total sense!

@tatut mm, it's good, and I can't pretend I don't enjoy being able to 'curl' my database - but the API itself is bespoke to us, so we'd also need to have written database exploration UIs, BI tools, etc etc, which (in addition to the database itself!) is realistically too much for our relatively small team

And yes, I was wondering about the http api as well, it would probably be a nice workaround until regular wire protocol "just works" 😛

well, or rely on open-source contributions like xtdb-inspector, of course 🙂

@torkan exactly, yep - it was our quickest route to being able to get data in and out of XT2

There's no docs for it, right? I'm fine with just reading the code for the remote client, just wondering if I've missed it somewhere 😅

yes, the API is obviously xtdb2 specific… I see that, but a maximal xtdb2 client library probably would go ways beyond “generic SQL” imo

I enjoyed making my Prolog library… https://github.com/tatut/swixt and even that still doesn’t make use of everything xtdb2

Nice, thanks for sharing!

@tatut we don't have a lot of extensions in the HTTP API outside of the query languages themselves - it's mostly status information (which we're looking to expose through more standard monitoring interfaces too)

why not expose those as virtual tables?

that too, yep 🙂

I really liked that the API is drop dead simple, query and tx… that’s it