SQL-like transactions (BEGIN/COMMIT/ROLLBACK)

I couldn’t find any information about support for SQL-like transactions. Specifically, is it possible to start, commit, and rollback transactions?

Using xtsql.py, there seems to be no implementation for BEGIN/COMMIT/ROLLBACK, they just cause an error on the server that “xtdb.sql.plan.StmtVisitor” doesn’t implement visitors for those statements.

Sorry if this is a dumb question, I’m very much a beginner to XTDB. For context, I am looking to create an XTDB driver for Elixir, and to use a popular library it expects this kind of functionality for transactions.

EDIT:
I may be wrong to say it “expects this kind of functionality”, it may not be required. Am still learning about Elixir’s DBConnection

1 Like

Hey @zackattackz great questions! XTDB’s transaction model is inherently non-interactive, but you can submit an atomically committed group of statements over HTTP (the xtsql.py demo script doesn’t make use of this, but the OpenAPI endpoint supports it) or via the existing client drivers.

However, we also have a ‘pgwire’ Postgres wire protocol server module which at least partially implements the SQL you are looking for. This module will soon be promoted into the core and documented as a first-class means of interacting with XTDB (where previously it has existed for demonstration purposes only, e.g. it currently only supports returning JSON types).

You can try XTDB like this already using the regular Docker container if you connect to the default Postgres port (5432), or you can see it in action using this public demo endpoint to understand how it works:

 ~ psql -h pg.xtdb.com
psql (16.2, server 14)
Type "help" for help.

jdt=> select * from foo;                                                           
--
(0 rows)

jdt=> begin read write;
BEGIN
jdt=*> insert into foo (xt$id, bar) values (1, 'baz');
INSERT 0 0
jdt=*> select * from foo;
ERROR:  queries are unsupported in a READ WRITE transaction

jdt=!> rollback;
ROLLBACK
jdt=> select * from foo;
--
(0 rows)

jdt=> begin read write;
BEGIN
jdt=*> insert into foo (xt$id, bar) values (1, 'baz');
INSERT 0 0
jdt=*> commit;
COMMIT
jdt=> select * from foo;
 xt$id |  bar  
-------+-------
 1     | "baz"
(1 row)

jdt=> 

^ this is actually connecting to a real, temporary XTDB instance.

Also note that transactions are automatic/implicit if you don’t use an explicit begin read write; <...> ; commit; transaction (i.e. you can send raw INSERTs or whatever as individual single-statement transactions). You can also see there that queries after a begin; won’t work, and this reflects the underlying reality of the system (and how HTTP / the other API surface area works).

Our hope is that the pgwire compatibility will simplify using XTDB from various ecosystems, including Elixir, because you can lean on the existing Postgres-related tooling (even if the dialects aren’t 100% compatible - CockroachDB is similar although attempts much harder to be truly Postgres-dialect compatible).

Hopefully those explanations help :slightly_smiling_face:

Out of interest, which Elixir libs are you hoping to use?

1 Like

Thank you! I see… So basically you can only make a transaction as a list of “puts” (putDocs), but you can’t really make a traditional SQL “transaction” that would allow querying along with inserting. I guess in most use cases this is OK because you can always just query what you need first, and then do your inserts after. I can’t really think of a use case where reading after an insert would be necessary, but it is still a notable deviation from other SQL DBs. But still, just because I’m curious, could you possibly explain more why this is the case in XTDB?

Also, I am hoping to first write an implementation of a DBConnection to make a driver for XTDB which you could pass maps of XTQL which could just be converted to JSON and sent to the HTTP API. Could then use that to create an Adapter for Ecto which would implement a converter from ecto’s query DSL to XTQL.

Ecto Queries have some very similar philosophies to XTQL queries, similar to XTDB’s clojure library with thread-first macros, Ecto Queries can be composed as pipelines.

Not really sure the feasibility of all this, but I wanted to try making this project as a way to learn more about XTDB and Elixir too! :slight_smile:

Now that you mention it though… I wonder if the already existing postgres driver would “just work” with xtdb via pgwire. Will test it out. Likely will need some kind of extensions for bitemporality stuff, but i wouldnt need to reinvent the database driver and ecto adapter wheel.

One more question, would you say that the XTDB SQL interface is just as expressive as the XTQL and the datalog ones? Like are there any examples of things that you could do with the latter that just arent possible with SQL?

Not really sure the feasibility of all this, but I wanted to try making this project as a way to learn more about XTDB and Elixir too! :slight_smile:

:raised_hands:

you can’t really make a traditional SQL “transaction” that would allow querying along with inserting

Not quite - each insert/update/delete can contain still query the database, but you’re just not able externally observe what those queries (“reads”) are returning until after the transaction has finished processing. You can only determine once the transaction has completed - whether it either succeeded or aborted - what the queries must have returned.

XT’s fully sequential transaction ordering is intentional and implies that mixing reads and writes interactively is a bad idea (because concurrency would be too limited to be usable). This is certainly a divergence from more typical SQL systems, but is in line with how XT1 already operates, chiefly inspired by Datomic and Rich Hickey’s ideas about mutability and information modelling - for more context on this I recommend starting with Rich’s Deconstructing the Database talk or skimming through this Jepsen report: Jepsen: Datomic Pro 1.0.7075

I wonder if the already existing postgres driver would “just work” with xtdb via pgwire

There will undoubtedly be caveats, but that’s the general hope. Even if it ultimately requires forking the Postgres client stack for each given ecosystem it should still give everyone involved a good head start. Watch this space!

would you say that the XTDB SQL interface is just as expressive as the XTQL and the datalog ones?

Essentially yes - SQL and XTQL are strictly equivalent in power because they both compile to the same internal Intermediate Representation (although actually there are some TODOs still in XTQL that mean SQL is in fact the more featureful language currently). The differences are purely in expressiveness/conciseness and “data-orientation”, and we’ve recently been taking steps to make the SQL dialect flexible enough that it replicates most of the value that XTQL delivers (perhaps only asides from “data-orientation” …but the true value of that is certainly debatable), see
XTQL-in-SQL · Issue #3307 · xtdb/xtdb · GitHub for more specifics.

Please do let us know how you get on, and if you would value more input during your explorations I would be happy to assist :pray:

1 Like

@refset This is all very helpful information, thank you very much! I will be sure to report in somewhere on my progress once I get further!

I do have another question but I’ll post a new thread as it is separate :slight_smile:

1 Like