Clojurians Log v2

I found this link following this tutorial: https://docs.xtdb.com/intro/installation-via-docker

Also, is this correct?

When I INSERT something, it says that it inserted 0 records, always...

yes, unfortunately - XTDB's DML is async, so we don't have an accurate row count when the statement's submitted. ideally we'd just display INSERT , but suspect that will break PG clients that expect to see a row count, so we put 0 instead

we have https://github.com/xtdb/xtdb/issues/3813 open to collect thoughts and ideas about how we can do this better, feel free to vote, comment and/or subscribe 🙂

Nice! Also, another question: how do I use PATCH? I tried something like this: PATCH INTO people FOR VALID_TIME FROM '2020-01-01T00:00:00Z' RECORDS RECORD(_id: 1, name: 'fred', address: 'Road of Nowhere'); and also like this: PATCH INTO people FOR VALID_TIME FROM '2020-01-01T00:00:00Z' RECORDS {_id: 1, name: 'fred', address: 'Road of Nowhere'}; And that just locked my psql client and caused the following error on the server:

java.lang.AbstractMethodError: Receiver class xtdb.pgwire$interpret_sql$reify__29928 does not define or inherit an implementation of the resolved method 'abstract java.lang.Object visitPatchStmt(xtdb.antlr.Sql$PatchStmtContext)' of interface xtdb.antlr.SqlVisitor.

ah man, don't tell me I didn't write a test for that through a pg client facepalm

@mauricio.szabo if you're able to, could you try the edge Docker image version?

(if you're using in-process, lmk and I'll release a snapshot)

Sure, let me test it 🙂

xtdb=> PATCH INTO people FOR VALID_TIME FROM '2020-01-01T00:00:00Z' RECORDS {_id: 1, name: 'fred', address: 'Road of Nowhere'};
PATCH 0
ERROR:  unexpected error on tx submit (report as a bug)

have you got anything in your server logs?

PATCH INTO people FOR VALID_TIME FROM TIMESTAMP '2020-01-01T00:00:00Z' RECORDS {_id: 1, name: 'fred', address: 'Road of Nowhere'}; perhaps? (but we should definitely propagate the error better if so)

This containing the FROM TIMESTAMP worked 🙂

If I remove the TIMESTAMP this error happens:

Caused by: java.lang.Exception: Not supported: class java.lang.Class
        at com.cognitect.transit.impl.AbstractEmitter.marshal(AbstractEmitter.java:195)
        at com.cognitect.transit.impl.AbstractEmitter.emitArray(AbstractEmitter.java:103)

aha, I've seen that one before, an error in getting the error message sent back over the wire

The problem is that as soon as this error happens, the server is somehow corrupted. I can restart psql and try to submit a new PATCH, or INSERT, or anything, really, and it will always fail with ERROR: unexpected error on tx submit (report as a bug)

incoming fix for that one, thanks for the report 🙏

Thanks for this feedback. I think the correct intuition here is understanding that PATCH ... FOR VALID_TIME FROM ... simply does not 'cascade' any updates to future versions that may be in the timeline, it simply overrides everything that might be in the timeline from that point onwards (i.e. an implicit FOR VALID_TIME FROM ts TO NULL ...where NULL means 'end of time'). In your example, the third PATCH will make it seems as if the second PATCH never happened (unless you look back in system time) The semantics you were expecting / are potentially looking for lead into the realm of https://citeseerx.ist.psu.edu/document?repid=rep1&type=pdf&doi=80862066aadd3788576243ca1d0477739cc3b0fe where you want the PATCH to somehow cascade into the future. The question then, is whether a simple last-writer-wins model is sufficient for all cases or whether you would need more complex transformations - in which you case you potentially end up building towards a kind of bitemporal event sourcing, and at that point maybe PATCH is perhaps not the appropriate name 🙂 We're definitely keen to hear opinions on what's useful / needed here

Well... no - in my example, the second patch happened to set name to NULL, which is the weird part...

What actually happened is that my third PATCH behaved like an INSERT without VALID_TIME:

xtdb=> PATCH INTO people FOR VALID_TIME FROM TIMESTAMP '2020-01-01T00:00:00Z' RECORDS {_id: 1, name: 'fred', address: 'Road of Nowhere'};
PATCH INTO people FOR VALID_TIME FROM TIMESTAMP '2022-01-01T00:00:00Z' RECORDS {_id: 1, name: 'fred gregger'};
PATCH INTO people FOR VALID_TIME FROM TIMESTAMP '2021-01-01T00:00:00Z' RECORDS {_id: 1, address: 'Road of Somewhere'};
PATCH 0
PATCH 0
PATCH 0
xtdb=> SELECT * FROM people;
 _id |      address      | name 
-----+-------------------+------
   1 | Road of Somewhere | 
(1 row)

xtdb=> SETTING DEFAULT VALID_TIME AS OF TIMESTAMP '2020-01-01T00:00:00Z' SELECT * FROM people;
 _id |     address     | name 
-----+-----------------+------
   1 | Road of Nowhere | fred
(1 row)

xtdb=> SETTING DEFAULT VALID_TIME AS OF TIMESTAMP '2021-01-01T00:00:00Z' SELECT * FROM people;
 _id |      address      | name 
-----+-------------------+------
   1 | Road of Somewhere | fred
(1 row)

xtdb=> SETTING DEFAULT VALID_TIME AS OF TIMESTAMP '2022-01-01T00:00:00Z' SELECT * FROM people;
 _id |      address      | name 
-----+-------------------+------
   1 | Road of Somewhere | 
(1 row)

perhaps this makes it clearer:

xtdb=> PATCH INTO people FOR VALID_TIME FROM TIMESTAMP '2020-01-01T00:00:00Z' RECORDS {_id: 1, name: 'fred', address: 'Road of Nowhere', note: 'initial data'};
PATCH 0
xtdb=> PATCH INTO people FOR VALID_TIME FROM TIMESTAMP '2022-01-01T00:00:00Z' RECORDS {_id: 1, name: 'fred gregger', note: 'fred tells us his surname'};
PATCH 0
xtdb=> PATCH INTO people FOR VALID_TIME FROM TIMESTAMP '2021-01-01T00:00:00Z' RECORDS {_id: 1, address: 'Road of Somewhere', note: 'just found freds earlier address change'};
PATCH 0
xtdb=> select *, _valid_from from people FOR ALL VALID_TIME;
 _id |      address      | name |                  note                   |        _valid_from        
-----+-------------------+------+-----------------------------------------+---------------------------
   1 | Road of Somewhere |      | just found freds earlier address change | 2022-01-01 00:00:00+00:00
   1 | Road of Somewhere | fred | just found freds earlier address change | 2021-01-01 00:00:00+00:00
   1 | Road of Nowhere   | fred | initial data                            | 2020-01-01 00:00:00+00:00
(3 rows)

Wouldn't also note be "fred tells us his surname" instead of "just found freds earlier address change"?

no I think that part is correct, because of what I stated earlier: > it simply overrides everything that might be in the timeline from that point onwards (i.e. an implicit FOR VALID_TIME FROM ts TO NULL ...where NULL means 'end of time'). In your example, the third PATCH will make it seems as if the second PATCH never happened (unless you look back in system time)

Ah, yes, I see now. I think the name becoming NULL should be considered a bug, but I also think that transacting something into the past should not assume "from here to the end of time", because things can get confusing - in this scenario, we have basically two transactions that are valid from a point to the end of time, and it's not clear which one will be used as the source of truth

Also, I think even considering that the third patch overrides the second one... it's still a bug, because if we add the _valid_to field, it does says that just found freds earlier address change is valid until 2022-01-01 00:00:00+00:00. And the transaction that's valid from 2022-01-01 00:00:00+00:00 is something that we never actually did (set the name to NULL)

thanks for the repro @mauricio.szabo - we have test cases that should have covered this, will try to identify the difference between yours and ours 👀

spotted it - we were relying on an implicit ordering in one of our intermediate relations within the patch operator 🐛

released a fix to edge @mauricio.szabo

so:

xtdb=> 
PATCH INTO patches FOR VALID_TIME FROM TIMESTAMP '2020-01-01T00:00:00Z' 
RECORDS {_id: 1, version: 1};
PATCH 0

xtdb=> 
PATCH INTO patches FOR VALID_TIME FROM TIMESTAMP '2022-01-01T00:00:00Z' 
RECORDS {_id: 1, version: 2, patched: 2022};
PATCH 0

xtdb=> 
SELECT *, EXTRACT(YEAR FROM _valid_from) _valid_from, 
       _system_from::time _system_from
FROM patches FOR ALL VALID_TIME 
ORDER BY _valid_from;

 _id | patched | version | _valid_from |   _system_from    
-----+---------+---------+-------------+-------------------
   1 |       ¤ |       1 |        2020 | "20:34:21.814865"
   1 |    2022 |       2 |        2022 | "20:34:35.934452"

xtdb=> 
PATCH INTO patches FOR VALID_TIME FROM TIMESTAMP '2021-01-01T00:00:00Z' 
RECORDS {_id: 1, patched: 2021};
PATCH 0


xtdb=> 
SELECT *, EXTRACT(YEAR FROM _valid_from) _valid_from,
       _system_from::time _system_from
FROM patches FOR ALL VALID_TIME 
ORDER BY _valid_from;

 _id | patched | version | _valid_from |   _system_from    
-----+---------+---------+-------------+-------------------
   1 |       ¤ |       1 |        2020 | "20:34:21.814865"
   1 |    2021 |       1 |        2021 | "20:34:47.142189"
   1 |    2021 |       2 |        2022 | "20:34:47.142189"
(3 rows)

in @taylor.jeremydavid's example, after the first two patches:

_id |     address     |     name     |           note            | _valid_from 
-----+-----------------+--------------+---------------------------+-------------
   1 | Road of Nowhere | fred         | initial data              |        2020
   1 | Road of Nowhere | fred gregger | fred tells us his surname |        2022

_id |      address      |     name     |      note      | _valid_from 
-----+-------------------+--------------+----------------+-------------
   1 | Road of Nowhere   | fred         | initial data   |        2020
   1 | Road of Somewhere | fred         | just found ... |        2021
   1 | Road of Somewhere | fred gregger | just found ... |        2022

thanks, confirmed at my end also:

xtdb=> PATCH INTO people FOR VALID_TIME FROM TIMESTAMP '2020-01-01T00:00:00Z' RECORDS {_id: 1, name: 'fred', address: 'Road of Nowhere', note: 'Initial data'};
PATCH 0
xtdb=> PATCH INTO people FOR VALID_TIME FROM TIMESTAMP '2022-01-01T00:00:00Z' RECORDS {_id: 1, name: 'fred gregger', note: 'fred tells us his surname'};
PATCH 0
xtdb=> PATCH INTO people FOR VALID_TIME FROM TIMESTAMP '2021-01-01T00:00:00Z' RECORDS {_id: 1, address: 'Road of Somewhere', note: 'just found freds earlier address change'};
PATCH 0
xtdb=> select *, _valid_from, _valid_to from people FOR ALL VALID_TIME;
 _id |      address      |     name     |                  note                   |        _valid_from        |         _valid_to         
-----+-------------------+--------------+-----------------------------------------+---------------------------+---------------------------
   1 | Road of Somewhere | fred gregger | just found freds earlier address change | 2022-01-01 00:00:00+00:00 | 
   1 | Road of Somewhere | fred         | just found freds earlier address change | 2021-01-01 00:00:00+00:00 | 2022-01-01 00:00:00+00:00
   1 | Road of Nowhere   | fred         | Initial data                            | 2020-01-01 00:00:00+00:00 | 2021-01-01 00:00:00+00:00
(3 rows)

next (and arguably the $1M) question - is that 'intuitive'?

I think so, but I'm probably the wrong person to ask 😅

it's at least the same as what an SQL update would do in that situation, which fits with patch effectively being an update sugar

(the value here being when you're using JDBC and you're submitting the patch doc as 'just a map', PATCH INTO people RECORDS ?, rather than needing to generate the SQL depending on what cols you want to update; less so when you're writing all the SQL out)

I suspect in the general case if you want more 'safety' here by default you will also need to do an ASSERT at the same time, alongside every PATCH (CAS, more or less), otherwise you might clobber unrelated changes (like the note, in the example above) ...but then you somewhat lose the benefit of PATCHing, and might actually be better off modelling domain events and computing derived state later (e.g. as a stored procedure or view), or moving to a stricter normalisation (6NF / ~triples) instead I think PATCH is therefore quite a sharp tool, and possibly only appropriate when you can be very confident about the requirements for its use (e.g. it's guaranteed to be constrained to monotonic merging of non-conflicting keys, or the retroactive cascading last-writer-wins semantics are desirable)

yeah, note here wants to be maintained separately, I'd say

should also point out that, for the usual case of patching the current version, this risk doesn't apply 🙂 if you're patching back in time and don't want this behaviour, yes, you'll need to give the operation an upper bound too (again, same as if you were UPDATEing it)

In v1, the Datalog query engine and indexes are only really intended to make the equivalent of SELECT * FROM all_triples FOR VALID_TIME AS OF <datetime> FOR SYSTEM_TIME AS OF <datetime> work efficiently. This means that if you want to access past/future versions of some triples, beyond the single point-in-time version associated with those timestamps, you have to use the entity-history API. We did later add some functions you could call to access the history within the Datalog, if really needed, but it was fairly opaque to query planning/optimization. In v2, you can do much more than simply a global AS OF <datetime> - you can look at bounded time ranges, all versions from any time, and apply different 'temporal filters' to various base relations as desired, etc. (e.g. all the FROM ... TO ... in example 4 onwards https://docs.xtdb.com/tutorials/financial-usecase/time-in-finance.html would be challenging to express efficiently in v1)

My understanding is that cross-time queries let you ask things about overlapping time ranges, e.g., find all members who had platinum status while our 30% discount was active.