Thread: Cursors: getting the number of tuples; moving backwards

Hello everyone!

I have 2 questions:

--1-- Some days ago, I've been trying to get the number of tuples
that FETCH ALL would return,  *before* fetching anything.
(the program is written in C++, using libpq ; PostgreSQL 7.2.3).

The solution i've found was something like:

    int nr_tuples;

    res = PQexec(conn, "MOVE ALL in CURS");
    sscanf(PQcmdStatus(res),"MOVE %i",&nr_tuples);
    PQclear(res);

I'm wondering: is there any better way to get that number?

( just an idea:  maybe it would be useful to make PQcmdTuples
  work for MOVE commands ... ? )


--2-- I found out that if i reach the end of the cursor, and want
to move backwards, i have to increase the MOVE command's argument by 1:

  MOVE ALL in CURS    --> i get the number of tuples: 590

  MOVE -590 in CURS
  FETCH ALL           --> i get all tuples except the first one

  MOVE -591 in CURS
  FETCH ALL           --> i get all the tuples

  MOVE -1 in CURS
  FETCH ALL           --> i get nothing !

  MOVE -2 in CURS
  FETCH ALL           --> i get the last tuple

This happens only if the current position is at the end of the cursor.

Is this the normal behaviour?



Best regards,
Adrian Maier
(am@fx.ro)

On Fri, Nov 01, 2002 at 12:43:48PM +0200, am@fx.ro wrote:
> Hello everyone!
>
> I have 2 questions:
>
> --1-- Some days ago, I've been trying to get the number of tuples
> that FETCH ALL would return,  *before* fetching anything.
> (the program is written in C++, using libpq ; PostgreSQL 7.2.3).

Well, to get an answer, the server needs to execute the entire query. It
won't do that unless you explicitly ask for it.

> The solution i've found was something like:
>
>     int nr_tuples;
>
>     res = PQexec(conn, "MOVE ALL in CURS");
>     sscanf(PQcmdStatus(res),"MOVE %i",&nr_tuples);
>     PQclear(res);

That would work. But why do you need to know the total beforehand? You could
just do a FETCH ALL and then use PQntuples to get the number. If you're
using it to decide whether to provide a Next link, just FETCH one more item
than you intend to display and if you get it you display the link.

> I'm wondering: is there any better way to get that number?
>
> ( just an idea:  maybe it would be useful to make PQcmdTuples
>   work for MOVE commands ... ? )

Interesting idea. I'm not sure whether MOVE actually executes the query or
not.

> --2-- I found out that if i reach the end of the cursor, and want
> to move backwards, i have to increase the MOVE command's argument by 1:

No idea, the cursor has probably moved off the end to indicate the query is
done. So you need the extra one to move it back. That's just a guess though.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

On Fri, Nov 01, 2002 at 07:23:29PM +1100, Martijn van Oosterhout wrote:
> > The solution i've found was something like:
> >
> >     int nr_tuples;
> >
> >     res = PQexec(conn, "MOVE ALL in CURS");
> >     sscanf(PQcmdStatus(res),"MOVE %i",&nr_tuples);
> >     PQclear(res);
>
> That would work. But why do you need to know the total beforehand? You could
> just do a FETCH ALL and then use PQntuples to get the number.

If the table has, let's say, 10000 rows, it's unlikely that the user
will ever browse all of them ( my program permits the user to set some
filters ; the interface is ncurses-based).  Fetching everything
would be unnecessary.

So, for speed reasons, i prefer to fetch maximum 500 rows.
But i want to display in the screen's corner the total number
of rows .

> > I'm wondering: is there any better way to get that number?
> >
> > ( just an idea:  maybe it would be useful to make PQcmdTuples
> >   work for MOVE commands ... ? )
>
> Interesting idea. I'm not sure whether MOVE actually executes the query or
> not.

I guess it doesn't execute the whole query. MOVE ALL is *much*
faster than FETCH ALL + PQcmdTuples

> > --2-- I found out that if i reach the end of the cursor, and want
> > to move backwards, i have to increase the MOVE command's argument by 1:
>
> No idea, the cursor has probably moved off the end to indicate the query is
> done. So you need the extra one to move it back. That's just a guess though.

Yeah, this could be the explanation.


Thanks for your answer

Adrian Maier

On Fri, Nov 01, 2002 at 08:14:33PM +0200, am@fx.ro wrote:
> On Fri, Nov 01, 2002 at 07:23:29PM +1100, Martijn van Oosterhout wrote:
> > That would work. But why do you need to know the total beforehand? You could
> > just do a FETCH ALL and then use PQntuples to get the number.
>
> If the table has, let's say, 10000 rows, it's unlikely that the user
> will ever browse all of them ( my program permits the user to set some
> filters ; the interface is ncurses-based).  Fetching everything
> would be unnecessary.
>
> So, for speed reasons, i prefer to fetch maximum 500 rows.
> But i want to display in the screen's corner the total number
> of rows .

Maybe do what google does. If there's lots of rows, give an estimate. I
don't know how they do it but if there are more than 1000 rows then the user
probably won't care if you wrote 1000, 2000 or a million.

Maybe some whacky curve fitting. If there's still a 98% match after 100
matches, there must be around 5000 matches.

> > Interesting idea. I'm not sure whether MOVE actually executes the query or
> > not.
>
> I guess it doesn't execute the whole query. MOVE ALL is *much*
> faster than FETCH ALL + PQcmdTuples

Curious. I wonder how it does it then.

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

Martijn van Oosterhout <kleptog@svana.org> writes:
>> I guess it doesn't execute the whole query. MOVE ALL is *much*
>> faster than FETCH ALL + PQcmdTuples

> Curious. I wonder how it does it then.

MOVE does execute the query, it just doesn't ship the tuples to the
client.  This would save some formatting overhead (no need to run
the datatype I/O conversion procedures), but unless you have a slow
network link between client and server I would not expect it to be
"much" faster ...

            regards, tom lane

On Fri, Nov 01, 2002 at 10:03:17PM -0500, Tom Lane wrote:
> MOVE does execute the query, it just doesn't ship the tuples to the
> client.  This would save some formatting overhead (no need to run
> the datatype I/O conversion procedures), but unless you have a slow
> network link between client and server I would not expect it to be
> "much" faster ...

It must be the fact that the computer is quite old : Cyrix 6x86 166Mhz.
( this is not the deplyoment machine ).

Using MOVE is about 5 times faster in my case :
For 150784 tuples in the table, FETCH-ing took about 1m30 ,
while MOVE-ing took only about 17sec.

                           |  Real       |  User        |  Sys
-------------------------------------------------------------------
select * from PRODTEST     |  1m30.843s  |  0m42.960s   |  0m1.720s
-------------------------------------------------------------------
declare cursor... + FETCH  |  1m32.835s  |  0m42.680s   |  0m1.780s
-------------------------------------------------------------------
declare cursor... + MOVE   |  0m17.215s  |  0m0.030s    |  0m0.030s
-------------------------------------------------------------------
( i used commands like:   time psql -f test.sql db_rex
  to get those timings )


The difference must be smaller on fast machines.

So i guess that my computer is pretty good when it comes to finding
performance problems in applications   ;-)


Bye,
Adrian Maier
(am@fx.ro)