1

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.

Note:

Just to make things more clear if my explanation was a little confusing, let's say I have in the table rows A, B and 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 C.

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 C.

0

2 Answers 2

1

What you discovered is collectively called Pessimistic Locking or Mutual Exclusion (mutex). For it to work we need to choose an object to lock on. It's possible to lock on some record in DB (select ... for update) or you can ask PostgreSQL to create such object outside of tables (pg_advisory_xact_lock()).

While pg_advisory_xact_lock() is a valid solution it's specific to PostgreSQL - and therefore it should be considered as the last resort. First solution that you should think of is finding records that we can lock on inside of existing tables. E.g. in your case it seems like you only append data and therefore you could lock on the oldest record with select ... for update.

If this isn't practical (e.g. because it's possible that the table is completely empty), then yes - pg_advisory_xact_lock() would be a good choice.

0

I think I figured it out, I read about advisory locks and they seem to fit the bill perfectly.

Basically for each query one of the three queries I talked about in the question, I first all a query with select pg_advisory_xact_lock(id), where id is a unique identifier for this query.

This makes all three queries impossible to run at the same time, giving me the guarantees I need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.