I need to do a get and then delete multiple documents in the same query.
select * from bucket where meta().id in [document1, document2, documentX]; delete from bucket where meta().id in [document1, document2, documentX]
as these documents may be updated from the moment of getting the data to the moment of deleting it by other workers, what do you think is the best approach to do this so that I make sure that the data I am going to receive would not be updated before deleting it?
later edit: came up with this idea:
delete from bucket where meta().id in [ "document1", "document2"] returning *
I would use KV gets and remove for this (ctx.get() and ctx.remove()) inside the transaction, since you know the keys. This will give you automatic protection - if any of the documents change between the get and the remove, this is automatically detected and the transaction will be retried (your lambda gets rolled back and rerun).
SQL++ even inside a transaction wouldn’t give you this same automatic protection that you get with KV operations. The documents can still change between the SELECT and the DELETE. There is a clever trick you can use to solve that though - you turn the initial SELECT into an UPDATE RETURNING instead. But if you know the keys, it’s going to be easiest to use KV operations.