On Tue, 18 Jan 2005 10:38:24 -0800, Chris wrote:
> The DBD::Pg man page say's this about cursors. What I don't
> understand is the nested selects being in a different transactions.
> Can someone clarify for me what is being said here? Does this
> basically mean that I can use cursors in DBD::Pg, I just can't use
> the built in DBI cursor functions? And If I'm reading this correctly
> you can't do nested selects within cursors period?
>
> "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.
Well, it's accurate up to this point. However, it then goes on to
explain why DBD::Pg doesn't use cursors behind the scenes to reduce
memory usage, and it starts to get misleading.
> Because only one transaction block at a time is allowed, this
> would have implied the restriction, not to use any nested SELECT
> statements.
Cursors can certainly be used on queries which contain subselects,
which is what I think you're concerned about. What I think the man
page it trying to say though, is that DBD::Pg couldn't use cursors
becuase then you could only have one active query at a time. But if it
was going to use cursors, it could just use a separate cursor for each
query, so I don't understand what the problem is.
> 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!"
This bit is accurate. The fetching of the entire result set is a
feature of the underlying C library, so it's a bit tricky for DBD::Pg
to work around without using cursors. However, I don't think it should
start using cursors behind the scenes, because it would make the
module much more complex and error prone. For one, there's the issue
of transactions; either al users would be forced to turn AutoCommit
off, or there would have to be different code paths for AutoCommit on
and off. Secondly, cursors are a handy thing to be able to use
explicitly, so either DBD::Pg would have to bar their use so as not to
interfere with its own usage, or it would have to examine every query
to see whether it involved cursors, and behave differently on the cursor/non-
cursor cases.
My vote would be for a documentation change which mentioned how to use
cursors explicitly to avoid excessive memory usage.
--
Peter Haworth pmh@edison.ioppublishing.com
Q: Why do ducks have flat feet? A: To stamp out forest fires.
Q: Why do elephants have flat feet? A: To stamp out flaming ducks
-- UNIX fortune
