This queue is for tickets about the DBD-Pg CPAN distribution.

Report information
The Basics
Id:
93266
Status:
open
Priority:
Low/Low
Queue:

People
Owner:
greg [...] turnstep.com
Requestors:
matshyeq [...] gmail.com
Cc:
AdminCc:

BugTracker
Severity:
Wishlist
Broken in:
(no value)
Fixed in:
(no value)



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:

Could you please give me some update as I have no idea how to get around this issue.

Kind Regards
Msciwoj
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/
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.
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/
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)


Thank you,
Kind Regards
Msciwoj
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.
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


This service runs on Request Tracker, is sponsored by The Perl Foundation, and maintained by Best Practical Solutions.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.