3

I am building a procedure to label database rows if certain records meet some conditions.

I am looping over rows using cursor and for every row I check for some conditions, if conditions are met I update that row. I need to check around 150 million rows.

The thing is that I need to check, for every row that is in cursor, its spatial relations with every other row from that table. I am using postgis for this task. Basically I need to do some spatial join between current record and other rows in a database. This may result in another set of rows that I need to update. I am planning to open new cursor for this task, loop over and update them.

My question is: If I update rows,in the same table, using second (nested) cursor will changes be visible to the first cursor at the moment of update, because some of the rows updated using second cursor can be also in the first cursor?

Improve this question
4
  • 1
    Cursor sounds like a really slow way of doing this then. How about you show us the code and we'll have a go at improving it? In answer to your question, I believe Postgres cursors are cached when they are opened Commented Aug 22, 2021 at 21:22
  • @Charlieface Cursors are not cached (unless they are defined WITH HOLD). But I agree that we need to see more of the code before we can give good advice. Commented Aug 23, 2021 at 3:29
  • i don't think that the request is unclear. We define a cursor c1 and run some code that modify some rows after the current row. When you fetch the next row, us it t´still the orinak from when you defined the cursor or the updated ones, which simply meant that the needs to make a new rquest to know which row he now needs to fetch Commented Aug 23, 2021 at 8:12
  • Please add a minimal reproducible example showing the table DDL and cursor code, even if it doesn't work. Commented Aug 23, 2021 at 13:53

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.