Clojurians Log v2

Hey @aviflax execute-tx could definitely be added as an HTTP endpoint and behave like you're looking for, but for the moment querying the _txs table as a separate request is the way to go. However we're only a few days away from announcing (and documenting!) first-class Postgres wire protocol compatibility fairly soon which is the other route to go down here - would that be of interest? Or do you prefer the idea of building against the HTTP API in particular? Our near term goal is that enough Postgres-compatible tooling (drivers, BI integrations etc.) 'just work' and we can consolidate our efforts around that

@ake @taylor.jeremydavid thanks so much for the timely, thorough, and clear responses! In the near term, my immediate concern would be solved if, were I to switch to the Kotlin or Java drivers and use executeTx, I’d get the desired behavior — blocking until the insert is 100% successful. For the element of my system that’s writing (we’re using CQRS) a native driver would be fine. In fact the only reason I haven’t already switched to give that a try is that I did some cursory searches of and peeks into the codebase and it seemed that maybe those two methods are in fact using the same code path. But looking again now, it seems maybe I was looking in the wrong place. So I’ll give the Kotlin driver a try right now. Maybe just confirm that makes sense in the meantime? (Although in general I would think that if you’re going to support writes via HTTP, then the default behavior should be block-until-outcome-is-known (I’m sure there’s a much more elegant term for that 🤦.)) FWIW: as far as the element of my system that’s doing reads (`select` queries) — that’s running in a Web browser, so I would strongly prefer that continue to communicate with the DB via HTTP. I’m probably putting a proxy in between for various reasons, but I’m hoping that the proxy will use zero, or very little, custom code. Thanks!

Quick question on the Kotlin driver… I must be missing something (I’m new to Kotlin)… this code:

val operation = TxOp.Sql(statement)
    val result = client.executeTx(operation)

    when (result) {
        is TransactionCommitted -> ("Tx succeeded with ID ${result.txId}")
        is TransactionAborted -> throw result.error
    }

java.lang.ClassCastException: class xtdb.serde.TxKey cannot be cast to class xtdb.api.TransactionResult (xtdb.serde.TxKey is in unnamed module of loader clojure.lang.DynamicClassLoader @70329364; xtdb.api.TransactionResult is in unnamed module of loader java.net.URLClassLoader @3a273e53)
	at xtdb.client.impl.XtdbClient.executeTx(impl.clj:95)
	at xtdb.api.IXtdb.executeTx(IXtdb.kt:68)
	at Entity_writer_main.writeEntityToXtdb(entity-writer.main.kts:110)

yeah, that doesn't look right to me - will take a look. have raised https://github.com/xtdb/xtdb/issues/3491

Thank you!

Hi, I’m trying out v2 and I think I must be missing something about how /tx works in the Web API. It seems like sometimes I sent a POST request to this URL with an insert statement and I get back a 200 OK, but the record isn’t in fact inserted into the database. For example:

% cat insert.json
{
  "txOps": [
    {
      "sql": "insert into shows (name) values ('Monty Python')"
    }
  ]
}

% curl -v --json @insert.json 
> POST /tx HTTP/1.1
> Host: localhost:3000
> User-Agent: curl/8.6.0
> Content-Type: application/json
> Accept: application/json
> Content-Length: 97
> 
< HTTP/1.1 200 OK
< Content-Type: application/json;charset=utf-8
< Content-Length: 58
< Server: Jetty(11.0.18)
< 
{"txId":199725,"systemTime":"2024-07-11T02:06:06.813610Z"}

% curl --json '{"sql": "select * from shows"}' 
[]

% curl --json '{"sql": "select * from _txs order by _id desc limit 1"}' 
[{"_id":199725,"committed":false,"error":{"@type":"xt:error","@value":{"xtdb.error/message":"Errors planning SQL statement:\n  - INSERT does not contain mandatory _id column","xtdb.error/class":"xtdb.IllegalArgumentException","xtdb.error/error-key":"xtdb/sql-error","xtdb.error/data":{"errors":[{}]}}},"tx_time":{"@type":"xt:timestamptz","@value":"2024-07-11T02:06:06.813610Z[UTC]"}}]

I think you've hit the nail on the head here. Afaik /tx returns 200 for successful receipt of the transaction like awaitTx. executeTx's blocking functionality is implemented in the client rather than there being special config. If you want similar functionality I believe you'll have to poll /status to check it was indexed, but even then the transaction may be indexed but have failed so you'll still end up having to query the _txs table.