Question: Get history list

[iambudi]

Hi,

I'm new to immudb and just tried it with go code using database/sql but need some insight.
Let's say i have product table and i update the price of product id 3 times.

1. How can i get history list of the product id so i can later compare when the update happened and what was changed.
2. When using BEFORE or AFTER TX 5 how can i know the TX number?

Thank you.

[dzlabsch]

Hi @iambudi,

a single field update in a table doesn't mean a separate TX as you can execute many SQL statements in one transaction. Therefore its often not possible to pin an update to a transaction.
To see all updates you can either use SINCE TX @initialTx or a date range SELECT * FROM products SINCE '2022-01-06 11:38' UNTIL '2022-01-06 12:00'

You can find more here: https://docs.immudb.io/master/develop/sql/querying.html#time-trave

[iambudi]

Hi @dzlabsch,

1. What is @initialTx? i get error rpc error: code = Unknown desc = missing parameter(initialtx) is it built in parameter or should i replace with a certain TX number?
2. Using SINCE and UNTIL does not return the historical list, only 1 row with latest update ($6.20).

   UPDATE products SET price = '$5.80' WHERE id = 1;
   UPDATE products SET price = '$6.00' WHERE id = 1;
   UPDATE products SET price = '$6.20' WHERE id = 1;
   
   SELECT * FROM products 
       SINCE '2022-01-06 11:38' UNTIL '2022-08-07 12:00' 
   WHERE id = 1;

   My expectation it returns 3 rows with different price values as i update the price 3 times before.
   Am i wrong understanding this?

Thank you.

[jeroiraz]

Hi @dzlabsch,

1. What is @initialTx? i get error rpc error: code = Unknown desc = missing parameter(initialtx) is it built in parameter or should i replace with a certain TX number?

2. Using SINCE and UNTIL does not return the historical list, only 1 row with latest update ($6.20).

   UPDATE products SET price = '$5.80' WHERE id = 1;
   UPDATE products SET price = '$6.00' WHERE id = 1;
   UPDATE products SET price = '$6.20' WHERE id = 1;
   
   SELECT * FROM products 
       SINCE '2022-01-06 11:38' UNTIL '2022-08-07 12:00' 
   WHERE id = 1;

   My expectation it returns 3 rows with different price values as i update the price 3 times before.
   Am i wrong understanding this?

Thank you.

Hi @iambudi, it's a good point. Currently the SQL queries are not returning the history, restricting the period based on transaction IDs or timestamp will still resolve to the latest value assigned in such period.

There is an open request I wrote but we haven't yet implemented, would you like to take a look at it and provide feedback? #930