I'm creating a data queue on top of postgres and I'm wondering if I've made an incorrect assumption about isolation or synchronization or some similar issue.
Every item in the queue is given a unique ID from a sequence.
I read from the queue by passing in the last _highest_seen_sequence_num to a stored procedure: SELECT * from data_queue WHERE sequence_num > _highest_seen_sequence_num ORDER BY sequence_num ASC
Obviously with readers and writers racing I need some sort of synchronization. I've found the advisory locks and those did seem to be my best bet. I used explicit locking for a while but ran into an issue with our daily backups and ACCESS_EXCLUSIVE (which I might be able to revisit) I'm also trying to create a setup where there is basically no blocking, writers can always write, readers are not blocked by writers (though there may be a delay in what is visible to the reader).
Before I dump a bunch of SQL on the list, my plan in short is to stage writes to a similar table: stage_data_queue, and then copy them all into a table visible by readers.
1 Writers get a shared advisory lock, get the next sequence_num and Insert one row, then release a shared advisory lock (in one stored procedure)
2 At some point there is a 'tick' and another thread gets the corresponding exclusive advisory lock (letting all in flight writes finish). Then copy all rows into another table visible to the readers, then Truncate the staging table, and release the exclusive lock. (all in one stored procedure)
My fear is that there is still a race here because the writer (1) calls unlock at the end of the stored procedure, and thus there is a window before the row is committed, and (2) may end up truncating that data...
I think I could fix this by leaving the (1) shared lock locked through the end of the stored procedure, and calling back unlocking it later. I might also be able to fix this with Explicit Locks because I assume those will get properly unlocked after the Insert is truly committed.
