I am not sure if this is the right place, or if the pgsql-patches list
is where this should go, since the CVS repositories are now in
different locations, I hoped this would be the right area.
Attached is a patch against the current CVS head for added support for
cursors in result set.
The reason for this patch I needed to iterate over a very large result
set, at first I was using PGSQL 7.3.x and the query crashed each time.
I believe out of memory errors were the cause. Later I realized that
the entire result set was being sent over, this was several millions of
rows.
I upgraded to PGSQL 7.4.2 since I saw it has support for cursors in
result sets, but it didn't work with my code. The problem was, I would
jump to last() and obtain the row for the total count, then jump back
to first() to begin my iteration. When setting the fetch size to 50, it
would only report 50 rows total. The last(), first(), absolute()
methods only looked at the current 'chunk' of rows. I think this may
have been a bug since later it appears that when scrollable was set it
was supposed to ignore the fetch size and grab all rows. This just will
not do.
This patch implements the absolute() method, using the cursor. The code
which checks for scrollable now creates the cursor with the SCROLL
keyword rather then avoiding cursors all together.
The patch passes the entire test suite, and I added a new test to test
absolute positioning. I am happy to answer any questions, and if anyone
sees any problems with the code please let me know. I think this will
be a great addition to PostgreSQL as I am having users switch to MySQL
MaxDB (SAPDB) due to performance problems. This patch solved a good
number of those problems!
Again, let me know if you see anything wrong and I will try to get it
corrected ASAP so this can get included with the next release!
Thanks,
-Andy
