READ UNCOMMITTED in postgres

Greetings,

* Matthew Phillips (mphillips34@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.

There's the LISTEN/NOTIFY system, which at a high level is a better
approach than using a polling system.

Thanks,

Stephen

Matthew Phillips schrieb am 19.12.2019 um 00:12:

Hi, 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.

In my understanding READ UNCOMMITTED in other databases is typically used to avoid read-locks which Postgres doesn't have.
So I wonder what benefits READ UNCOMMITTED would have to begin with.

But, if you want to poll for new rows, then why don't you use a timestamp column?

select *
from the_table
where created_at >= <last check time>

On Wed, 18 Dec 2019 at 23:13, Matthew Phillips <mphillips34@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.

Not sure it helps much. The new records aren't truly there until commit.

Using max_id alone is not an effective technique. It's just an optimization.

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.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/&gt;
PostgreSQL Solutions for the Enterprise

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

On Wed, 18 Dec 2019 at 23:13, Matthew Phillips <mphillips34@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/&gt;
PostgreSQL Solutions for the Enterprise

--
Olivier Gautherot
Tel: +33 6 02 71 92 23
https://www.linkedin.com/in/ogautherot/