PostgreSQL unbuffered query

Question

I am producing large query results that can exceed a sizable PHP memory limit (128 Mb). I've been trying to address this and came across unbuffered queries. Unfortunately, these only seem to be supported by MYSQL while I'm using PostgreSQL. There does seem to be a similar process for PostgreSQL involving cursors, but I'm unsure how to implement them within the Drupal framework using db_query(). Demo implementation with plain PHP can be found here.

My alternative solution would be to call a query multiple times with restrictions to LIMIT and OFFSET. Query performance is reasonably good, so this may be a viable solution as well. With this method I have yet to figure out how to divide queries into chunks by memory size. Instead, I would have to use a fixed row range. This could be a problematic approach given rows can significantly differ in size.

Answer 1

There are certain cases where db_query may not be able to do certain things, and this very well could be one of those cases.

That said, nothing is stopping you from leveraging PDO just like that blog post you linked to.

Here is an example from a project of mine in 2015 in Drupal 7 that performs a function using PDO:

/**
 * This takes the provided uploaded file and imports it directly into a temporary table in the database.
 * Drupal has some issues executing the statement, so the PDO class is leveraged with some different options to allow it without error.
 *
 * @param $table_name
 * @param $uri
 */
function populate_mysql_table_with_infile($table_name, $uri) {
  $database = Database::getConnectionInfo()['default'];

  $data_source = 'mysql:host=' . $database['host'] . ';dbname=' . $database['database'];
  $db_user = $database['username'];
  $db_password = $database['password'];

  $connection = new PDO($data_source, $db_user, $db_password,
    array(
      PDO::ATTR_EMULATE_PREPARES => TRUE,
      PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE,
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_PERSISTENT
    )
  );

  $statement = $connection->prepare("LOAD DATA LOCAL INFILE '" . drupal_realpath($uri) . "' INTO TABLE " . $table_name . "
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES");
  $statement->execute();
  $statement->closeCursor();
}

Answer 2

If you need a PDO object, the object returned from Database::getConnection() (the method called from db_query()) is an instance of a class that extends the PDO class. See the documentation for the DatabaseConnection class.

abstract class DatabaseConnection extends PDO {
 // ...
}

Instead of calling db_query(), calls Database::getConnection('default'). On the object you get, you can call setAttribute() and prepare() as described in PostgreSQL unbuffered queries and PHP (cursors).