Обсуждение: Getting ROW_COUNT from MOVE in 8.3
Hi, everyone. I'm working on an application that needs to perform a
query twice -- once to retrieve the total number of rows in a table, and
a second that then retrieves chunks of rows. The current implementation
executes the query twice, once with COUNT(*) and another with OFFSET and
LIMIT being passed to a pl/pgsql function. Needless to say, the
performance isn't so hot.
I've managed to improve things quite a bit by using cursors, but I've
been stumped in trying to find a replacement for the COUNT(*). I wrote
a function that works great on 9.0:
CREATE OR REPLACE FUNCTION count_the_rows() RETURNS INTEGER AS $$
DECLARE
mycursor CURSOR FOR SELECT * FROM test_table;
number_of_rows integer := 0;
BEGIN
OPEN mycursor;
MOVE ALL IN mycursor;
GET DIAGNOSTICS number_of_rows := ROW_COUNT;
RETURN number_of_rows;
END;
$$ LANGUAGE 'plpgsql';
Unfortunately, the project is using 8.3, and the function refuses to
even compile, due to the "MOVE ALL". When we change it to something
else (such as MOVE 20000000, which returns the actual number of rows
skipped to psql), or ABSOLUTE -1, we get 0 back from ROW_COUNT.
I know (from the recent discussion on pgsql-performance, among other
places) that count(*) is inherently slow. I'm excited to have found a
solution that at least allows us to avoid the execution of a complex
query twice in a row. But is there any way for me to get, in 8.3, the
number of rows over which a cursor has skipped? Keep in mind that after
this count has executed, we're then going to rewind the cursor, chunking
through the result set with a separate function.
Thanks in advance,
Reuven
--
Reuven M. Lerner -- Web development, consulting, and training
Mobile: +972-54-496-8405 * US phone: 847-230-9795
Skype/AIM: reuvenlerner
On sön, 2010-10-24 at 15:41 +0200, Reuven M. Lerner wrote: > But is there any way for me to get, in 8.3, the > number of rows over which a cursor has skipped? Keep in mind that > after > this count has executed, we're then going to rewind the cursor, > chunking > through the result set with a separate function. You could store the query result in a temporary table.
Hi, Peter. You wrote: >> But is there any way for me to get, in 8.3, the >> number of rows over which a cursor has skipped? Keep in mind that >> after >> this count has executed, we're then going to rewind the cursor, >> chunking >> through the result set with a separate function. > You could store the query result in a temporary table. Yeah, that's what we're doing now. But (a) I'm hoping that move will be faster, because we're not copying data, but just moving through it, and (b) I wanted to benchmark the different options. But I'm glad to know that this isn't a bad way to go, given the lack of information. That said, I'm sort of surprised that the psql client is able to show us the number of rows through which MOVE operated, while we're unable to access that number in pl/pgsql. But hey, that's yet another welcome addition to 9.0... Reuven -- Reuven M. Lerner -- Web development, consulting, and training Mobile: +972-54-496-8405 * US phone: 847-230-9795 Skype/AIM: reuvenlerner
On 24 Oct 2010, at 15:41, Reuven M. Lerner wrote: > I've managed to improve things quite a bit by using cursors, but I've been stumped in trying to find a replacement forthe COUNT(*). I wrote a function that works great on 9.0: > > CREATE OR REPLACE FUNCTION count_the_rows() RETURNS INTEGER AS $$ > DECLARE > mycursor CURSOR FOR SELECT * FROM test_table; > number_of_rows integer := 0; > BEGIN > OPEN mycursor; > MOVE ALL IN mycursor; > GET DIAGNOSTICS number_of_rows := ROW_COUNT; > RETURN number_of_rows; > END; > $$ LANGUAGE 'plpgsql'; > > Unfortunately, the project is using 8.3, and the function refuses to even compile, due to the "MOVE ALL". When we changeit to something else (such as MOVE 20000000, which returns the actual number of rows skipped to psql), or ABSOLUTE-1, we get 0 back from ROW_COUNT. I recall movable cursors weren't supported in pl/pgsql until 8.4. You can use (movable) cursors in SQL though. That probably requires some client-side code, as things like GET DIAGNOSTICSaren't available in sql functions, but it may be of use to you. I've done this in a PHP web-application a coupleof times. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4cc531ef10289398819850!
Please leave the ML in the reply-list, so that others might benefit from the answers, comment on the solution, etc etc. ;) On 25 Oct 2010, at 13:55, AI Rumman wrote: > I am using Postgresql 8.1 and facing the similar problem. > Can you provide the link where I get the information what you did with PHP? Not really, as I wrote it myself at a company that no longer exists; There is no link. You can however look at the documentation, specifically: http://www.postgresql.org/docs/8.1/interactive/sql-declare.html and http://www.postgresql.org/docs/8.1/interactive/sql-fetch.html You can retrieve the number of rows after MOVE FORWARD ALL (similar to the below posting) either from pg_num_rows() or byreading out the return value (not the result set!) of the query. I don't remember exactly how I did it, I used this method several years ago and have since dropped PHP as a programming languageI support. > On Mon, Oct 25, 2010 at 1:29 PM, Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote: > On 24 Oct 2010, at 15:41, Reuven M. Lerner wrote: > > > I've managed to improve things quite a bit by using cursors, but I've been stumped in trying to find a replacement forthe COUNT(*). I wrote a function that works great on 9.0: > > > > CREATE OR REPLACE FUNCTION count_the_rows() RETURNS INTEGER AS $$ > > DECLARE > > mycursor CURSOR FOR SELECT * FROM test_table; > > number_of_rows integer := 0; > > BEGIN > > OPEN mycursor; > > MOVE ALL IN mycursor; > > GET DIAGNOSTICS number_of_rows := ROW_COUNT; > > RETURN number_of_rows; > > END; > > $$ LANGUAGE 'plpgsql'; > > > > Unfortunately, the project is using 8.3, and the function refuses to even compile, due to the "MOVE ALL". When we changeit to something else (such as MOVE 20000000, which returns the actual number of rows skipped to psql), or ABSOLUTE-1, we get 0 back from ROW_COUNT. > > > I recall movable cursors weren't supported in pl/pgsql until 8.4. > > You can use (movable) cursors in SQL though. That probably requires some client-side code, as things like GET DIAGNOSTICSaren't available in sql functions, but it may be of use to you. I've done this in a PHP web-application a coupleof times. > > Alban Hertroys > > -- > Screwing up is an excellent way to attach something to the ceiling. > > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4cc5778810291816532262!