Re: READ UNCOMMITTED in postgres

On Thu, Dec 19, 2019 at 9:20 AM Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> On Wed, 18 Dec 2019 at 23:13, Matthew Phillips <mphillips34(at)gmail(dot)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/