Re: Read table rows in chunks - Mailing list pgsql-hackers

From Kashif Zeeshan
Subject Re: Read table rows in chunks
Date
Msg-id CAAPsdhcbdxhCsHDsVGf7gwb_x6UtjxYUaePoX7e9QWCJkR2rjQ@mail.gmail.com
Whole thread Raw
In response to Read table rows in chunks  (Sushrut Shivaswamy <sushrut.shivaswamy@gmail.com>)
List pgsql-hackers
Hi

You can also use the following approaches.

1. Cursors
2. FETCH with OFFSET clause

Regards
Kashif Zeeshan
Bitnine Global

On Sat, Apr 27, 2024 at 12:47 PM Sushrut Shivaswamy <sushrut.shivaswamy@gmail.com> wrote:
Hey,

I"m trying to read the rows of a table in chunks to process them in a background worker.
I want to ensure that each row is processed only once.

I was thinking of using the `SELECT * ... OFFSET {offset_size} LIMIT {limit_size}` functionality for this but I"m running into issues.

Some approaches I had in mind that aren't working out:
 - Try to use the transaction id to query rows created since the last processed transaction id 
      - It seems Postgres does not expose row transaction ids so this approach is not feasible
 - Rely on OFFSET / LIMIT combination to query the next chunk of data 
      - SELECT * does not guarantee ordering of rows so it's possible older rows repeat or newer rows are missed in a chunk

Can you please suggest any alternative to periodically read rows from a table in chunks while processing each row exactly once.

Thanks,
Sushrut



pgsql-hackers by date:

Previous
From: Sushrut Shivaswamy
Date:
Subject: Re: Background Processes in Postgres Extension
Next
From: "David G. Johnston"
Date:
Subject: Re: Read table rows in chunks