Bug #93266 for DBD-Pg: DBD::Pg to set the fetch size

Sat Feb 22 15:39:03 2014 matshyeq [...] gmail.com - Ticket created

Subject:	DBD::Pg to set the fetch size
Date:	Sat, 22 Feb 2014 20:38:33 +0000
To:	bug-DBD-Pg [...] rt.cpan.org
From:	matshyeq <matshyeq [...] gmail.com>

Hello, I've found an issue when selecting rows from big table (2mln rows) I discovered the execute() statement seems to prefetch all rows first which breaks my script due to the excessive memory consumption. I believe this has been well described at: http://stackoverflow.com/questions/21960121/perl-dbdpg-script-fails-when-selecting-data-from-big-table Could you please give me some update as I have no idea how to get around this issue. Kind Regards Msciwoj

Fri Mar 07 21:37:49 2014 greg [...] turnstep.com - Correspondence added

Unfortunately, this is a limitation in the underlying driver (libpq) rather than DBD::Pg itself. There have been talks over the years of supporting this, but nothing concrete yet. Your best bet would be to ask about this on the Postgres lists, such as pgsql-general@postgresql.org. http://www.postgresql.org/community/lists/subscribe/

Fri Mar 07 21:37:50 2014 The RT System itself - Status changed from 'new' to 'open'

Fri Mar 07 21:37:50 2014 greg [...] turnstep.com - Status changed from 'open' to 'stalled'

Fri Mar 07 21:37:51 2014 greg [...] turnstep.com - Taken

Fri Mar 07 21:39:09 2014 greg [...] turnstep.com - Correspondence added

As to how to get around, the link you gave already has the best two answers: use a cursor, or break the query into smaller chunks. If you do the former, make sure you set a better transaction isolation level than the default.

Fri Mar 07 21:39:09 2014 The RT System itself - Status changed from 'stalled' to 'open'

Sun Mar 09 09:49:29 2014 matshyeq [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #93266] DBD::Pg to set the fetch size
Date:	Sun, 9 Mar 2014 13:48:59 +0000
To:	bug-DBD-Pg [...] rt.cpan.org
From:	matshyeq <matshyeq [...] gmail.com>

Thank you for your response! As you see from my comments at stackoverflow I've dealt with this problem a while ago, indeed using cursor. On a separate note, as you suggested I've sent some notification to pgsql-general@postgresql.org as I believe this should be addressed at the root so it would be possible to achieve the objective in a much more elegant way. Thanks and Regards, Msciwoj On Sat, Mar 8, 2014 at 2:39 AM, Greg Sabino Mullane via RT < bug-DBD-Pg@rt.cpan.org> wrote: Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=93266 > > > As to how to get around, the link you gave already has the best two > answers: use a cursor, or break the query into smaller chunks. If you do > the former, make sure you set a better transaction isolation level than the > default.

On Sat, Mar 8, 2014 at 2:37 AM, Greg Sabino Mullane via RT < bug-DBD-Pg@rt.cpan.org> wrote: Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=93266 > > > Unfortunately, this is a limitation in the underlying driver (libpq) > rather than DBD::Pg itself. There have been talks over the years of > supporting this, but nothing concrete yet. Your best bet would be to ask > about this on the Postgres lists, such as pgsql-general@postgresql.org. > > http://www.postgresql.org/community/lists/subscribe/ >

Tue Mar 11 08:49:54 2014 matshyeq [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #93266] DBD::Pg to set the fetch size
Date:	Tue, 11 Mar 2014 12:49:21 +0000
To:	bug-DBD-Pg [...] rt.cpan.org
From:	matshyeq <matshyeq [...] gmail.com>

Hi Greg, The feedback I've got from pgsql-general@postgresql.org group is basically that the functionality to limit fetching has been there (in pqlib) for about 1.5 year now: "...the feature needed to do this without even using a cursor was added 1.5 years ago (PQsetSingleRowMode). The DBD::Pg was just not taught how to use it yet." If you think there's still something missing in pqlib that prevents from building the functionality in question to DBD::Pg, maybe you could directly respond to that group (I believe you would express that much better than myself) http://www.postgresql.org/list/pgsql-general/2014-03/ Thank you, Kind Regards Msciwoj

Wed Apr 02 18:12:09 2014 greg [...] turnstep.com - Correspondence added

It looks like PQsetSingleRowMode is as good as we are going to get, so we'll probably move ahead with using that to implement RowCacheSize. And by "we" I mean me if/when I get the time, or anyone else that wants to help out.

Wed Apr 02 18:12:24 2014 greg [...] turnstep.com - Severity Wishlist added

Thu Apr 03 04:00:19 2014 matshyeq [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #93266] DBD::Pg to set the fetch size
Date:	Thu, 3 Apr 2014 08:59:42 +0100
To:	bug-DBD-Pg [...] rt.cpan.org
From:	matshyeq <matshyeq [...] gmail.com>

Sure, I fully understand and appreciate! On the other hand if anything could be improved there (not just limited to "as good as we are going to get") you, asa person who knows what is talking about are welcome to take a stand in that postgres discussion thread ( http://www.postgresql.org/message-id/CAONr5=vYNp1TbFN4hLHkni23gANcP-Fp=6699CMzAi7Ogk=96Q@mail.gmail.com ). I was pushed back due to my lack of 'low level' knowledge to defend the point On Wed, Apr 2, 2014 at 11:12 PM, Greg Sabino Mullane via RT < bug-DBD-Pg@rt.cpan.org> wrote: Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=93266 > > > It looks like PQsetSingleRowMode is as good as we are going to get, so > we'll probably move ahead with using that to implement RowCacheSize. And by > "we" I mean me if/when I get the time, or anyone else that wants to help > out. >

-- Thank you, Kind Regards ~Maciek