Newbie DBD::Pg question
Just started on postgres a few days back and was having a few questions
about the perl interface to it. According to perldoc DBD::Pg
----------------
Cursors
Although PostgreSQL has a cursor concept, it has not been
used in the current implementation. Cursors in PostgreSQL
can only be used inside a transaction block. Because only
one transaction block at a time is allowed, this would
have implied the restriction, not to use any nested SELECT
statements. Hence the execute method fetches all data at
once into data structures located in the frontend
application. This has to be considered when selecting
large amounts of data !
-----------------
Frankly speaking I am not sure what the Author is saying here. Anybody
cares to point me in the right direction ??? I do have to extract large
amount of data but I am not sure whether it affects me or not.
Mithun
I haven't gotten into it yet but you could try using the sendQuery method
and looping on the new data as it becomes available.
Josh
On Fri, 6 Jul 2001, Mithun Bhattacharya wrote:
Show quoted text
Just started on postgres a few days back and was having a few questions
about the perl interface to it. According to perldoc DBD::Pg
----------------
CursorsAlthough PostgreSQL has a cursor concept, it has not been
used in the current implementation. Cursors in PostgreSQL
can only be used inside a transaction block. Because only
one transaction block at a time is allowed, this would
have implied the restriction, not to use any nested SELECT
statements. Hence the execute method fetches all data at
once into data structures located in the frontend
application. This has to be considered when selecting
large amounts of data !
-----------------
Frankly speaking I am not sure what the Author is saying here. Anybody
cares to point me in the right direction ??? I do have to extract large
amount of data but I am not sure whether it affects me or not.Mithun
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Hi,
Extraction of large amount of data is not realistic especially with
CGI, then DBI/DBD::Pg always return all data received from a query
so if you don't want to tired your machine, the better way is to use
cursor in a transaction (or query) or the LIMIT+OFFSET keywords.
Regards
Gilles DAROLD
Mithun Bhattacharya wrote:
Show quoted text
Just started on postgres a few days back and was having a few questions
about the perl interface to it. According to perldoc DBD::Pg
----------------
CursorsAlthough PostgreSQL has a cursor concept, it has not been
used in the current implementation. Cursors in PostgreSQL
can only be used inside a transaction block. Because only
one transaction block at a time is allowed, this would
have implied the restriction, not to use any nested SELECT
statements. Hence the execute method fetches all data at
once into data structures located in the frontend
application. This has to be considered when selecting
large amounts of data !
-----------------
Frankly speaking I am not sure what the Author is saying here. Anybody
cares to point me in the right direction ??? I do have to extract large
amount of data but I am not sure whether it affects me or not.Mithun
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
It means that all rows are fetched to the memory space of the perl
application. That's all fine and good for several hundred rows. However,
if your query returns 10's of thousands you're going to get a lot of disk
swapping, and perl will hog up all the ram it can. (read bad performance).
You aren't trying to do this as CGI are you?
--rob
----- Original Message -----
From: "Joshua Jore" <moomonk@daisy-chan.org>
To: "Mithun Bhattacharya" <mithun.b@egurucool.com>
Cc: "PostgreSQL general" <pgsql-general@postgresql.org>
Sent: Friday, July 06, 2001 1:32 PM
Subject: Re: Newbie DBD::Pg question
Show quoted text
I haven't gotten into it yet but you could try using the sendQuery method
and looping on the new data as it becomes available.Josh
On Fri, 6 Jul 2001, Mithun Bhattacharya wrote:
Just started on postgres a few days back and was having a few questions
about the perl interface to it. According to perldoc DBD::Pg
----------------
CursorsAlthough PostgreSQL has a cursor concept, it has not been
used in the current implementation. Cursors in PostgreSQL
can only be used inside a transaction block. Because only
one transaction block at a time is allowed, this would
have implied the restriction, not to use any nested SELECT
statements. Hence the execute method fetches all data at
once into data structures located in the frontend
application. This has to be considered when selecting
large amounts of data !
-----------------
Frankly speaking I am not sure what the Author is saying here. Anybody
cares to point me in the right direction ??? I do have to extract large
amount of data but I am not sure whether it affects me or not.Mithun
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?