Uploaded image for project: 'Qt'
  1. Qt
  2. QTBUG-63714

Implement QSqlQuery::setForwardOnly() for PostgreSQL to reduce memory usage

    XMLWordPrintable

Details

    • Suggestion
    • Resolution: Done
    • P2: Important
    • 5.11.0 Alpha
    • 5.9.2
    • SQL Support
    • Qt 5.9.2 + MSVC2015 x86

      Windows 10

      PostgreSQL 9.6

       

    • I15db8c8fd664f2a1f719329f5d113511fa69010c

    Description

      The current implementation of PSQL plugin doesn't support forward only queries.

      This is a problem for applications working with large resultsets - the entire resultset is stored in memory, resulting in high memory usage.

      For example, the following code consumes about 200MB:

      int value;
      QSqlQuery query(db);
      query.setForwardOnly(true);
      query.exec("select generate_series(1, 10000000)");
      while (query.next()) {
          value = query.value(0).toInt();
      }
      

      Postgresql 9.2 introducted SingleRowMode that can be used to implement setForwardOnly() feature.

      Here is code that works the same as above example, but is faster and uses only 9MB:

      PQsendQuery(conn, "select generate_series(1, 10000000)");
      PQsetSingleRowMode(conn);
       
      int i, value;
      PGresult *res;
      while (res = PQgetResult(conn)) {
          for (i = 0; i < PQntuples(res); i++) {
              value = atoi(PQgetvalue(res, i, 0));
          }
          PQclear(res);
      }
      

       Some useful links:

      https://www.postgresql.org/docs/9.2/static/libpq-single-row-mode.html

      https://www.postgresql.org/docs/9.2/static/libpq-async.html

       

      Attachments

        No reviews matched the request. Check your Options in the drop-down menu of this sections header.

        Activity

          People

            amiart Robert Szefner
            amiart Robert Szefner
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes