With the current READ UNCOMMITTED discussion happening on pgsql-hackers [1], It did raise a question/use-case I recently encountered and could not find a satisfactory solution for. If someone is attempting to poll for new records on a high insert volume table that has a monotonically increasing id, what is the best way to do it? As is, with a nave implementation, rows are not guaranteed to appear in monotonic order; so if you were to keep a $MAX_ID, and SELECT WHERE p_id > $MAX_ID, you would hit gaps. Is there a clean way to do this? I've seen READ UNCOMMITTED used for this with DB2.
If READ UNCOMMITTED returns data belonging to transactions in process, there is a risk that you consider data that will end up in a ROLLBACK.
Not sure it helps much. The new records aren't truly there until commit.
True. And to make things worse, the timestamp (probably invocation of now() ) will record the beginning of the transaction. So if your transaction takes a few seconds, or does not always take the same time, you will face a challenge.
Using max_id alone is not an effective technique. It's just an optimization.
I would recommend to manage p_id with a sequence... as long as you're not in multi-master (you will find out that each master handles its own set of values and you could end up with some surprises). Doing it with MAX(p_id) + 1 is looking for concurrency problems.
Just be careful to not advance max_id too quickly, and remember which ones you've already checked. Or wait for the next monontonic value each time, accepting the lag.
Again, as long as you can ensure that there won't be any ROLLBACK. Otherwise you could end up waiting for ever...