READ UNCOMMITTED in postgres

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

READ UNCOMMITTED in postgres

Matthew Phillips
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.

Thanks
Matt

Reply | Threaded
Open this post in threaded view
|

Re: READ UNCOMMITTED in postgres

Stephen Frost
Greetings,

* Matthew Phillips ([hidden email]) 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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: READ UNCOMMITTED in postgres

Thomas Kellerer
In reply to this post by Matthew Phillips
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>

 


Reply | Threaded
Open this post in threaded view
|

Re: READ UNCOMMITTED in postgres

Simon Riggs
In reply to this post by Matthew Phillips
On Wed, 18 Dec 2019 at 23:13, Matthew Phillips <[hidden email]> 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/
PostgreSQL Solutions for the Enterprise
Reply | Threaded
Open this post in threaded view
|

Re: READ UNCOMMITTED in postgres

Olivier Gautherot-2

On Thu, Dec 19, 2019 at 9:20 AM Simon Riggs <[hidden email]> wrote:
On Wed, 18 Dec 2019 at 23:13, Matthew Phillips <[hidden email]> 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/
PostgreSQL Solutions for the Enterprise


--
Olivier Gautherot
Tel: +33 6 02 71 92 23