Fork me on GitHub
#xtdb
<
2021-09-20
>
lepistane09:09:00

Hi, this might be uber basic question but i am one of 'i dont know what i dont know' people who heard about xtdb (crux) but never had the pleasure of getting to know it. I am thinking about introducing it into already existing project that uses next.jdbc to query Postgres. I was thinking about creating few entities and linking them but it's fuzzy if it can/should be done maybe the question is can i mix and match approaches? I am assuming it's not wise to query tables created by xtdb by using anything other than xtdb fns?

tatut10:09:57

well the tx_events table has binary serialized representations of the tx/doc data, so not very queryable directly using SQL

lepistane10:09:58

this pretty much nails it. You either use xtdb or not. Thank you for the fast response! 🙂

richiardiandrea15:09:57

@U45SLGVHVand @U11SJ6Q0K there should be a Calcite module to query using SQL - if I remember correctly - this could mitigate some of the issues. We have introduced xtdb here with the above caveat but so far nobody has requested to enable the SQL plugin

🙏 2
richiardiandrea15:09:20

> I was thinking about creating few entities and linking them but it's fuzzy if it can/should be done This definitely can be done and the pull syntax can query relations https://xtdb.com/reference/queries.html#pull

lepistane17:09:24

@U0C8489U6 i can query relations created with xtdb. I can't mix and match approaches right? with pull syntax u can query relations created with xtdb but you can't create xtdb entity and put reference (foreign key) to it manually in another table. right? am i missing something?

richiardiandrea17:09:33

> create xtdb entity and put reference (foreign key) to it manually in another table If I understand what you are asking correctly - I would say that there is no table in crux, the documents are the things that are first class so if you want to "add a reference" to a document you can do:

{:crux.db/id "XYZ"
 :type :person
 :name "John"
 :is-vip true
 :addresses ["8ab5259f-a7d1-4b83-8c55-7e483d7417da", "08ce588e-6464-4d7b-874b-4918efefa582"]
}
Where your docs for addresses are:
{:crux.db/id "08ce588e-6464-4d7b-874b-4918efefa582"
 :type :address
 ...}
Note that the :type is what defines the entity and can really be any piece of data - depending on what queries you want to support. The pull for querying then would be:
{:find [(pull ?e [* {:addresses [*]}])]
 :in [?id]
 :where [[?e :type :person]
         [?e :crux.db/id ?id]]}

tatut05:09:36

yes, you can use SQL to query xtdb, I don't know how convenient that is when mixing info from xtdb documents and normal postgresql tables

tatut05:09:05

but there are other concerns in mixing and matching... how do you handle transactions when you have some data in pg and some xtdb

richiardiandrea15:09:06

yeah I am not even sure you can mix and match, it just provides an SQL layer over the xtdb data layout as far as I know

rschmukler21:09:57

Is there any way to rebuild just a secondary index using the new 1.18 APIs? It would be nice if the new latest-completed-tx-id was used to potentially replay the needed subset of the transaction log to that index so that it could be rebuilt (or added later) without having to rebuild the whole node

jarohen21:09:08

hey @UEC8W94AE 🙂 it should 'just work that way' - if your secondary index reports a nil latest-completed-tx-id XT will pass it transactions from the beginning and wait for your index to catch up before continuing on with new transactions

rschmukler21:09:12

1. That’s awesome 🙂 2. I didn’t get that behavior in my latest tests but let me double check and make sure my tests aren’t doing something silly

jarohen22:09:33

it's based on the after-tx-id parameter you pass to register-index!