Postgres - run a request in batches?

Question

Is it possible to program the query so that if (for example) 500,000 rows are found, it will return the results for the first 10,000 and then run the query again?

So what I want to do is run a query and build an array, for example:

$result = pg_query("SELECT * FROM myTable"); $i = 0; while($row = pg_fetch_array($result) ) { $myArray[$i]['id'] = $row['id']; $myArray[$i]['name'] = $row['name']; $i++; } 

But I know that there will be several hundred thousand lines, so I wanted to do it in batches like 10 000 ... 1 - 9,999, and then 10 000 - 10 999, etc. .... The reason is that I continue getting this error:

 Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 3 bytes) 

Which, by the way, I don’t understand how 3 bytes could exhaust 512M ... So, if something that I can just change, it would be great, although perhaps it would be better to do it in batches?

Answer 1

You can use LIMIT (x) and OFFSET (y)

Answer 2

These last 3 bytes were the straw that broke the camel. Probably trying to highlight a long line of distributions leads to an error.

Unfortunately, libpq will try to completely cache the result sets in memory before giving up managing the application. This is in addition to any memory you use in $myArray .

It was suggested to use LIMIT ... OFFSET ... to reduce the envelope of the memory; this one will work, but is inefficient , because it can unnecessarily duplicate server-side sorting efforts every time a request is reissued with a different offset (for example, to respond with LIMIT 10 OFFSET 10000 , Postgres will still have to sort the entire result set, only to return rows 10000..10010.)

Instead, use DECLARE ... CURSOR to create a server cursor , and then FETCH FORWARD x to get the next lines of x . Repeat as many times as necessary, or until rows less than x are returned. Remember to CLOSE cursor when you are done, even if / if an exception is raised.

Also, not SELECT * ; if you only need id and name , create a cursor FOR SELECT id, name (otherwise libpq will uselessly retrieve and cache columns that you never use, increasing the amount of memory and the total query time.)

Using cursors as shown above, libpq will hold no more than x lines in memory at any given time. However, make sure you also clear $myArray between FETCH es, if possible, or you can still lose memory due to $myArray .

Answer 3

The PostgreSQL server caches the results of the query until you retrieve them, so adding them to an array in such a loop will run out of memory no matter what. Either process the results one line at a time, or check the length of the array, process the results so far, and then clear the array.

Answer 4

What the error means is that PHP is trying to allocate 3 bytes, but the entire available portion of this 512 MB is less than 3 bytes.

Even if you do it in batches, depending on the size of the resulting array, you can still run out of available memory.

Perhaps you really do not need all the records?