PHP: How to Cleanup after Generator Function

Question

After some research, I have been sold on the idea of generators (more generally, iterators) for lots of tasks that would normally buffer results into an array, since the memory usage is O(1) instead of O(n).

So I plan to use generators to handle database results queried via mysqli. I have 2 questions regarding this approach that I have not been able to find answers on, and I am hoping the community can give me some creative solutions:

1. Is there a way to release resources opened by a generator, if the consuming code chooses to not fully iterate the results? Using an Iterator class, one might do this in the __desctruct method. But, from my tests, a generator will simply not execute code following an iteration sequence if it does not conclude naturally. I am looking for workarounds to this that will prevent having to create an Iterator subclass. See code below.

2. Does using a generator or iterator even provide any benefit for database results? Some of my snooping seemed to indicate that mysqli might be loading the resultset into memory anyway (MYSQLI_STORE_RESULT), defeating the purpose of an iterator. If the results are not buffered, I am curious whether multiple queries can be executed while their resultsets are being iterated (fetched) at the same time (think nested loops where you are iterating over a set of items and then query for child items for each parent). This seems like the database cursor might lock during the entire iteration.

Example

The below is a simplification of what I mean by cleanup. From my tests, the result only gets freed if the entire result is iterated. If there is an exception or break in the consuming loop, the results never get freed. Perhaps I am overthinking this and the garbage collector is good enough?

function query($mysqli, $sql){
  $result = $mysqli->query($sql);
  foreach($result as $row){
    yield $row;
  }
  $result->free(); //Never reached if break, exception, take first n rows, etc.
}

tl;dr is I am just curious how to free resources used by a generator, and subsequently if generators for database access really saves anything, or if the results are buffered anyway

UPDATE

It looks here (http://www.php.net/manual/en/mysqlinfo.concepts.buffering.php) like PHP buffers queries by default, possibly defeating the purpose of generators. Although the argument could be made that buffering only one array is better than creating a copy of the buffered array and then having two buffered sets.

I am looking for anyone with experience in the matter to weigh in. Your thoughts are appreciated!

Answer 1

I may be a little late to the party, but if you are using generators and need to clean up when finished (say you break you parent loop before you are finished looping through everything), you can just use a try/catch/finally with the cleanup in the finally block:

function query($mysqli, $sql) {
  $result = $mysqli->query($sql);
  try {
    if ($result) {
      foreach($result as $row) {
        yield $row;
      }
    }
  } catch (Exception $e) {
    throw $e; // send this up the stack (or you could handle here)
  } finally {
    $result->free(); // clean up when the loop is finished.
  }
}

Answer 2

Here's how to detect loop breaks, and how to handle or cleanup after an interruption.

function generator()
{
    $complete = false;
    try {

        while (($result = some_function())) {
            yield $result;
        }
        $complete = true;

    } finally {
        if (!$complete) {
            // cleanup when loop breaks 
        } else {
            // cleanup when loop completes
        }
    }

    // Do something only after loop completes
}