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@xxxxxxxxxxxxxxxxxxxxxxxx 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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
