Thread: 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
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: > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
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 > ---------------- > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
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 > 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 > > ---------------- > > 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 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > >