XT2 snapshot 20240902

Users
,
1

Morning folks - another XT2 snapshot going up this morning :rocket:

  • 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 this PR 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 give it a spin, and let us know what issues you run into :bug:

  • As part of this, we’ve added initial support for SQL’s ROW_NUMBER window function (with more to come) - I say ‘as part of this’ because there are a good few tools whose first metadata-discovery query uses it :sweat_smile:

  • ‘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 :slight_smile:

James & the XT Team

4 Likes
2

I updated the XTDB version of the seancorfield/usermanager-example at xtdb (github.com) to use the PostgreSQL JDBC driver and the pgwire server on XTDB (instead of the, now archived, next.jdbc.xt interop library) and the only thing I ran into was that next.jdbc.sql/insert! assumes it can specify :return-keys true – which passes Statement/RETURN_GENERATED_KEYS to .prepareStatement – and that seems to cause the PG JDBC driver to append RETURNING * to the SQL, which XTDB rejects.

org.postgresql.util.PSQLException: ERROR: Errors parsing SQL statement:
line 2:0 mismatched input 'RETURNING' expecting {<EOF>, ';'}

I updated all the insert! calls in the usermanager code to pass {:return-keys false} which solves the “problem” but it would be nice if XTDB’s SQL supporting RETURNING.

2 Likes
3

Here’s an example of using the new period intersections along with the overlaps function.
This would have been a lot tougher previously!

1 Like