Unbuffered queries using Postgres+PHP?

Question

Is there a way to execute unbuffered queries against PostgreSQL from PHP, or at least limit the size of the local buffer that a driver uses?

Answer 1

I far as I could tell from the time I looked into this, there are no unbuffered queries using the pgsql driver on PHP. But the following code can help you out with this problem:

$curSql = "DECLARE cursor1 CURSOR FOR SELECT * FROM big_table";
$con = new PDO("pgsql:host=whatever dbname=whatever", "user", "pass");
$con->beginTransaction(); // cursors require a transaction.
$stmt = $con->prepare($curSql);
$stmt->execute();

$innerStatement = $con->prepare("FETCH 1 FROM cursor1");

while($innerStatement->execute() && $row = $innerStatement->fetch(PDO::FETCH_ASSOC)) {
    echo $row['field'];
}

Source: http://codepoets.co.uk/2014/postgresql-unbuffered-queries/#more-936

Answer 2

Since PHP 8.4

PHP 8.4 introduced a PDO::ATTR_PREFETCH attribute which, set to 0 (either on the PDO, or the Statement) allows fetch() to work on-demand, unbuffered.

There are still side effects (that prevents introspecting while popping the results, for example with getColumnMeta()), but it is fully usable in a dump-to-CSV scenario.