On Thu, Dec 19, 2019 at 9:20 AM Simon Riggs <si...@2ndquadrant.com> wrote:
> On Wed, 18 Dec 2019 at 23:13, Matthew Phillips <mphillip...@gmail.com> > wrote: > > >> 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... > > -- > Simon Riggs http://www.2ndQuadrant.com/ > <http://www.2ndquadrant.com/> > PostgreSQL Solutions for the Enterprise > -- Olivier Gautherot Tel: +33 6 02 71 92 23 https://www.linkedin.com/in/ogautherot/
