How to make PDO (pdo_pgsql) to lazily fetch rows

Question

With the following code even though I fetch a single row - the whole dataset is downloaded (which takes few seconds):

$query = 'SELECT * FROM xxx WHERE id > :position ORDER BY id';
$stmt = $db->prepare($query);

$stmt->execute([
    ':position' => $position,
]);

while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
    break;
}

I tried to find an option in PDO that would make it fetch rows on demand (or by reasonable batches), but failed to do so, at least I could not find it in the PDO documentation.

Postgresql for the given query is able to serve the first row in 0.28ms as per the EXPLAIN ANALYZE.

And my aim is to start processing rows as quicker as possible, even if further batches of rows will come with some network overhead.

How I determined that it fetches all the rows:

1. Indirectly: it takes the same time to break after the first row or to iterate over the whole dataset
2. I captured the traffic with tcpdump and checked that it fetches all the rows.

So, the question is: is it possible to make PDO to fetch in a row-by-row (or some reasonable small batches) on demand mode?

Answer 1

I found the answer:

$stmt = $db->prepare($query, [\PDO::ATTR_CURSOR => \PDO::CURSOR_SCROLL]);

So the statement must be created with a \PDO::CURSOR_SCROLL option

References:

• http://php.net/manual/en/pdo.prepare.php