Make MapiPP.pm fetch results progressively

[monetdb-team]

Date: 2011-09-28 10:41:58 +0200
From: Rémy Chibois <>
To: clients devs <>
Version: 11.5.1 (Aug2011) [obsolete]
CC: @drstmane

Last updated: 2012-01-26 15:31:59 +0100

Comment 16333

Date: 2011-09-28 10:41:58 +0200
From: Rémy Chibois <>

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_1) AppleWebKit/534.48.3 (KHTML, like Gecko) Version/5.1 Safari/534.48.3
Build Identifier:

In the current implementation, MapiPP.pm fetches all results from a query which could result in high memory consumption, if not exhaust.
Fetching results only when needed would allow processing large result sets.

Reproducible: Always

Steps to Reproduce:

1. Use the following snippet on a large table (millions of rows)

=====
!/usr/bin/env perl

use strict;
use warnings;

$|++;

use DBI();

my $dbh = DBI->connect(
'dbi:monetdb:database=big_db', 'monetdb', 'monetdb'
);

my $query = qq{
SELECT
*
FROM
big_table
};

my $sth = $dbh->prepare($query);
$sth->execute;

my $r = $sth->fetchall_arrayref();

print scalar(@{$r}) . " rows\n";

$dbh->disconnect();

Actual Results:

While functioning correctly if there's enough RAM, MapiPP.pm fetches all results before returning control to DBI.

Expected Results:

Nearly instant return from $sth->execute() and progressive fetch when calling $sth->fetchall_arrayref(), which can also be used to fetch batch of rows if used as:

=====
Iterate over result set by batches of 10000 rows
while (my $rows = $sth->fetchall_arrayref(undef, 10000)) {
}

You'll find attached a patch against current MapiPP.pm (from MonetDB-11.5.3) which tries to implement progressive fetch.

Comment 16334

Date: 2011-09-28 10:42:52 +0200
From: Rémy Chibois <>

Created attachment 77
Patch implementing progressive fetch of results

Attached file: MapiPP.pm-progressive_fetch.patch (text/plain, 1174 bytes)
Description: Patch implementing progressive fetch of results

Comment 16335

Date: 2011-09-28 11:00:36 +0200
From: @grobian

How does this affect moving with the cursor in both directions? Or does DBD not support this?

Comment 16336

Date: 2011-09-28 11:13:57 +0200
From: Rémy Chibois <>

As far as I can tell, there's no direct support for SQL's CURSOR in DBI/DBD, apart from using database (possibly specific) statements like:

=====
$dbh->do("DECLARE my_cursor CURSOR FOR SELECT...");

and then issuing:

while (...) {
my $sth = $dbh->prepare("FETCH 10000 FROM my_cursor");
$sth->execute();

  use $sth->fetch*

}

So, for me, DBI/DBD "fetch" is a forward only operation.

Comment 16339

Date: 2011-09-28 13:08:07 +0200
From: Rémy Chibois <>

Changeset f584fadb4847 made by R?my Chibois rchibois@gmail.com in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=f584fadb4847

Changeset description:

MapiPP: fetch results progressively, bug #2891

In the current implementation, MapiPP.pm fetches all results from a
query which could result in high memory consumption, if not exhaust.
Fetching results only when needed allows processing large result sets.

Comment 16340

Date: 2011-09-28 13:08:48 +0200
From: @grobian

Thanks, I applied your patch in the current development branch.

Comment 16558

Date: 2011-11-14 11:02:39 +0100
From: @drstmane

See also bug #2931.

Comment 16573

Date: 2011-11-26 16:23:08 +0100
From: @grobian

This change makes the code fail. It must be fixed before Dec2011 release, or else it has to be reverted.

Comment 16575

Date: 2011-11-26 16:23:33 +0100
From: @grobian

*** Bug #2931 has been marked as a duplicate of this bug. ***

Comment 16576

Date: 2011-11-26 16:45:29 +0100
From: Rémy Chibois <>

Could you please tell me what's failing ?

Comment 16577

Date: 2011-11-26 16:46:48 +0100
From: @grobian

see bug #2931.

I'm currently working on a fix, which I think I can finish shortly.

Comment 16578

Date: 2011-11-27 10:40:43 +0100
From: @grobian

Changeset 3ee6b959a958 made by Fabian Groffen fabian@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=3ee6b959a958

Changeset description:

MonetDB-CLI: fix MAL server interaction

Since the progressive fetching enhancement from bug #2891, retrieving
results from a MAL server failed, because the progressive code relies on
the metadata headers as sent by the SQL server.  In case we detect a
result without these metadata headers, fall back to the old buffered way
of reading, such that the total tuple count is available.
This fixes the failing MAL perl_dbi (malsample.pl) test.

Comment 16579

Date: 2011-11-27 10:41:58 +0100
From: @grobian

ok, fixed the regression now by just prefetching the results for non-SQL answers.

Comment 16820

Date: 2012-01-26 15:31:59 +0100
From: @sjoerdmullender

The Dec2011 version has been release, so declaring this bug as FIXED.