Clojurians Log v2
xtdb 2024-09-02
morning folks - another XT2 https://github.com/xtdb/xtdb/milestone/46?closed=1 going up this morning 🚀
• Main change here is a change to the XT2 infrastructural reqmts - previously, on cloud deployments, we required that the object store be able to notify the nodes when new files were added; we now take responsibility for this ourselves by adding an extra topic on the existing log, so there's no longer any need to set up extra pub/sub infrastructure.
◦ this is a breaking change to the config, though - see https://github.com/xtdb/xtdb/pull/3651 for migration instructions.
• In terms of functionality, we've been spending more time on the Postgres wire-protocol server. we're quite keen that you can use whatever tooling you previously used with Postgres to connect to XT
◦ In our experience, though, each different tool makes different assumptions about what the server on the other end of the wire supports. So, please do https://docs.xtdb.com/quickstart/sql-overview.html, and let us know what issues you run into 🐛
• 'Periods' are now first-class in XT2 - this is an extension to the SQL spec (which only requires that they be usable in specific period predicates). You can now calculate a period 'intersection' using the * operator, which is really useful for temporal joins: if you're joining two tables together across time, and you want to know when both facts were valid, you can request foo._valid_time * bar._valid_time.
• On the performance side, we've put through a significant change that ensures we can more quickly rule out large swathes of historical data for both as-of-now and recent historical queries.
As always - any trouble, give us a shout 🙂
James & the XT Team
congrats on the new snapshot 🙂 between last night and this morning I’m now seeing this:
Syntax error compiling at (xtdb/serde.clj:275:27).
Unable to find static field: new in class xtdb.types.ClojureForm(require '[xtdb.node.impl])(require '[xtdb.node :as xtn])
(def node (xtn/start-node))ah, that'd be the Clojure version bump - are you able to upgrade to 1.12.0-rc1 if you're running XT in-process?
question about HTTP API, the zoned datetime in particular: https://docs.xtdb.com/drivers/http/openapi/index.html#/schemas/TypedZoneDateTime
the documentation description uses zone like +01:00 but the example uses [Europe/Paris] , as an API user I would much rather have the +hh:mm zone format
it wouldn’t matter otherwise, but I parse those types into prolog compound terms, so I would need to use the locale database to know what the offset is
reckon you might 'just' be able to use the offset too? 🤔
is it guaranteed that the API will never return a place name?
if I make a parser that round trips data to/from that format, I have to support anything it can throw at me 😄
if you only ever supply offsets, yes, we shouldn't add zones - we couldn't, in fact
FYI: Looks like the orange "Get Started" link on the homepage needs an update, currently pointing at https://docs.xtdb.com/intro/getting-started which is not found
hey @ander - thank you for mentioning this! A victim of the new docs being pushed out the morning 😬 The link should be: https://docs.xtdb.com/index.html I'll get right on it now 🙂 🏃♂️
I picked new snapshot day for my initial foray into XTDBv2 - been a happy v1 user for some time now. Really excited about everything I've seen
This is probably answered obviously in the docs but I've been away from XT2 for a while...
If I stand up an in-process node, is it accessible as if it were a localhost:5432 PG database without any additional work, or is there some specific config needed to enable that?
(background: I've archived next.jdbc.xt now that pgwire is the focus and plan to update the usermanager example app to use next.jdbc and the PG driver instead)
If you're going via the in-process Clojure API then you'll need to add the pgwire-server module explicitly: https://github.com/xtdb/xtdb/blob/a2664a5d86b91df74513b43da523c3f6cc9d2164/src/dev/clojure/dev.clj#L38 See also the Docker config: https://github.com/xtdb/xtdb/blob/main/docker/standalone/local_config.yaml
Is the minimal startup then (xtn/start-node {:pgwire-server {:port 5432}}) to use localhost:5432 as a PG database for that in-process node?
(the answer seems to be "yes")
The usermanager example for XTDB has been updated to use the PostgreSQL JDBC driver directly, instead of next.jdbc.xt (which has been archived): https://github.com/seancorfield/usermanager-example/tree/xtdb
One thing I noticed was that sql/insert! must have {:return-keys false} provided, otherwise you get:
org.postgresql.util.PSQLException: ERROR: Errors parsing SQL statement:
line 2:0 mismatched input 'RETURNING' expecting {, ';'} RETURNING (it's not in my code and not in next.jdbc). Since you have to pre-generate _id (formerly xt$id) that makes sense but it is kind of a PITA as far as being able to treat XTDB as a "normal" PG JDBC database.FWIW, I just confirmed on XT Play (via the SQL Overview docs) that INSERT INTO people (_id, name) VALUES (6, 'fred') RETURNING * fails with that same exception.
hey @seancorfield, thanks for the update ☺️
We're not likely to be able to support RETURNING in XT in the general case, because write transactions are non-interactive and async - they all have to go via the transaction log to be processed with the single writer.
That said, we could see if we could return an empty relation, for example, so that at least it runs without erroring
> it is kind of a PITA as far as being able to treat XTDB as a "normal" PG JDBC database we're finding a lot of these at the moment - turns out most tools make quite a few assumptions that it's 'real' PG on the other end of the wire 😅
does :return-keys call executeQuery rather than executeUpdate, perhaps?
definitely seems like pgjdbc is able to add RETURNING to the query, just trying to figure out under what conditions it does so
> We're not likely to be able to support RETURNING in XT in the general case, because write transactions are non-interactive and async
I have been wondering whether there is a special case we could handle here though, which is to only support running a query as a the final statement in a transaction. Behind the scenes the node would simply run that query locally after blocking and waiting for the DML (the previous non-query statements) to succeed, while applying the relevant basis to ensure consistency.
I would be curious to know whether you think that behaviour could be useful or whether it breaks some basic assumptions about how SQL should work - there's an issue https://github.com/xtdb/xtdb/issues/3571
next.jdbc calls .execute (only). c.j.j used to call the various different .execute* methods and that caused all sorts of weird edge cases.
{:return-keys true} causes this to be called to create the statement (.prepareStatement con sql Statement/RETURN_GENERATED_KEYS) and then .execute is called on that (as opposed to just (.prepareStatement con sql).
The "problem" here, for me with next.jdbc usage, is that .prepareStatement succeeds (doesn't throw) but then XT rejects the statement. The code is already setup to handle JDBC drivers that don't support returning keys -- but the PG driver supports that... by modifying the INSERT ... to add RETURNING ... (don't know what follows that but I suspect it's *).
I think if RETURNING * was supported and just returned the data being inserted -- since XTDB requires PKs to be provided on insert, they're not auto-generated -- that would be sufficient to satisfy most uses?
Thanks Sean, we'll review what can be done here. Somewhat off-topic, but have you encountered/used the SQL standard "data change delta table" syntax before? e.g. per https://h2database.com/html/grammar.html#data_change_delta_table and https://www.postgresql.org/message-id/5fc8e4cb-8e8f-4e0f-9173-b31d48a8bc28%40postgresfriends.org
@seancorfield it likely would - the 'general case' I was talking about above was more along the lines of insert into foo select * from bar, which we wouldn't be able to support
I am only talking about simple INSERT .. RETURNING * (bear in mind, all of my regular work is with MySQL, where an INSERT can return any generated keys which is not applicable here).
if it's Just™ a case that we need to return a resultset (albeit an empty one), then we could certainly do that
(defn- stmt->result-set
"Given a `PreparedStatement` and options, execute it and return a `ResultSet`
if possible."
^ResultSet
[^PreparedStatement stmt opts]
(if (.execute stmt)
(.getResultSet stmt)
(when (:return-keys opts)
(try
(.getGeneratedKeys stmt)
(catch Exception _))))).execute returns true, the expectation is that .getResultSet will "work" and return something -- i.e., for queries. If it returns false and you've asked next.jdbc to return keys, it'll try to call .getGeneratedKeys (but allows that to fail and returns nil). So, even if XTDB returned no results but allowed (and ignored) RETURNING *, that would be an improvement.cool, sounds good, let's see what we can do 🙂