I have an insert-only table in my database that contains time-series data.
When my system starts, I need to populate an in-memory state with the last-month data (each row contains data for one minute) meaning that I first do a select to get the latest row date and then select all rows from the start of that date month until the date itself.
After that, if the DateTime for the last row is not the current DateTime, I start a process that fetches historical data and inserts it into the database until it reaches the current DateTime.
The issue I'm having is that if the fetch historical process I'm running crashes right when doing an insert to the database, it will restart, do the select to get the latest row and there is a small chance that the latest row will not be the last actually inserted data (I'm guessing probably because that insert transaction didn't finish yet or was not committed by PG yet) and that will corrupt my state and generate wrong data.
So, my question is, is there any way that I can make my select to get the latest row from the database (and the select to get the range of data too) wait for all insert queries that are "pending" to be committed before retrieving any data so I can guarantee that I will really get the latest row?
I do not mind if the solution would have some performance impact since these queries are only done once during system startup.
Thanks for the help.
Just to make things more clear if my explanation was a little confusing, let's say I have in the table rows
C, now the system is inserting row
D, but it crashes, but the insert request was sent to PG, so the row
D will be inserted.
Right after that, the system restarts and requests the latest data from the table which should be
D, but since that row is (I'm guessing) not committed yet, the row that I get is
What I want is some way for that select to detect that there is an ongoing insert transaction wait for it to finish and then retrieve the data to guarantee that I will get
D and not