From Jan.van.der.Weijde@attachmate.com Thu Jan  11 10:49:26 2007
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 0FE4F9FA435
	for <pgsql-interfaces-postgresql.org@postgresql.org>;
	Thu, 11 Jan 2007 06:49:26 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 57080-08 for
	<pgsql-interfaces-postgresql.org@postgresql.org>;
	Thu, 11 Jan 2007 06:48:59 -0400 (AST)
X-Greylist: delayed 00:18:08.795359 by SQLgrey-1.7.4
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id 50D9C9FB21D
	for <pgsql-interfaces@postgresql.org>;
	Thu, 11 Jan 2007 06:49:14 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3
Received: from Exch-Sea0.attachmate.com (unknown [150.215.8.31])
	by svr4.postgresql.org (Postfix) with ESMTP id CF75D5AF990
	for <pgsql-interfaces@postgresql.org>;
	Thu, 11 Jan 2007 10:30:56 +0000 (GMT)
Received: from exch-shan1.attachmate.com ([149.82.99.9]) by
	Exch-Sea0.attachmate.com with Microsoft SMTPSVC(6.0.3790.1830);
	Thu, 11 Jan 2007 02:30:54 -0800
Received: from exch-lei1.attachmate.com ([150.215.225.5]) by
	exch-shan1.attachmate.com with Microsoft SMTPSVC(6.0.3790.1830);
	Thu, 11 Jan 2007 10:30:51 +0000
X-MimeOLE: Produced By Microsoft Exchange V6.5
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C7356B.8F019DF1"
Subject: How do I detect End-of-table or End-of-limit ?
Date: Thu, 11 Jan 2007 11:30:48 +0100
Message-ID: <4B9C73D1EB78FE4A81475AE8A553B3C67DC52B@exch-lei1.attachmate.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: How do I detect End-of-table or End-of-limit ?
Thread-Index: Acc1a48M63/j3v6nQm6cGEktiKuOEA==
From: "Jan van der Weijde" <Jan.van.der.Weijde@attachmate.com>
To: <pgsql-interfaces@postgresql.org>
X-OriginalArrivalTime: 11 Jan 2007 10:30:51.0568 (UTC)
	FILETIME=[911C6B00:01C7356B]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.184 tagged_above=0 required=5 tests=AWL, BAYES_50,
	HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200701/14
X-Sequence-Number: 6392

This is a multi-part message in MIME format.

------_=_NextPart_001_01C7356B.8F019DF1
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

Hello all,
=20
I am using the libpq interface method PQexec() to execute SELECT
statements with a LIMIT clause. I declare a cursor with hold and use
PQecec() to execute FETCH NEXT on that cursor.
At a certain moment PQntuples() returns 0 after a fetch, but I cannot
check for the actual reason: is the physical end of the table (or
selection) reached or is the end of my limit reached.
In the former case I'm done. But in the latter case I can continue
selecting records by using SELECT with a LIMIT and OFFSET clause.=20
=20
Of course there are workarounds:
count the number of records (but if the size of the table or select set
is a multiple of my LIMIT value that does not work)
or
just try another SELECT and if the FECTH NEXT will fail again I'm done
=20
However I am looking for something more elegant like a status flag in
libpq that indicate EOF or End-Of-limit.
=20
Thank you,
Jan van der Weijde

------_=_NextPart_001_01C7356B.8F019DF1
Content-Type: text/html;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<META content=3D"MSHTML 6.00.2900.2995" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D137241210-11012007>Hello=20
all,</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D137241210-11012007></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D137241210-11012007>I am =
using the libpq=20
interface method PQexec() to execute SELECT statements with a LIMIT =
clause. I=20
declare a cursor with hold and use PQecec() to execute FETCH NEXT on =
that=20
cursor.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D137241210-11012007>At a =
certain moment=20
PQntuples() returns 0 after a fetch, but I cannot check for the actual =
reason:=20
is the&nbsp;physical end of the table&nbsp;(or selection) reached or is =
the end=20
of my limit reached.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D137241210-11012007>In the =
former case=20
I'm done. But in the latter case I can continue selecting records by =
using=20
SELECT with a LIMIT and OFFSET clause. </SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D137241210-11012007></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D137241210-11012007>Of =
course there are=20
workarounds:</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D137241210-11012007>count =
the number of=20
records (but if the size of the table or select set is a =
multiple&nbsp;of my=20
LIMIT value that does not work)</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D137241210-11012007>or</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D137241210-11012007>just =
try another=20
SELECT and if the FECTH NEXT will fail again I'm =
done</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D137241210-11012007></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D137241210-11012007>However I am looking=20
for something more elegant&nbsp;like a status flag in libpq that =
indicate EOF or=20
End-Of-limit.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D137241210-11012007></SPAN></FONT>&nbsp;</DIV>
<DIV>
<DIV><SPAN style=3D"COLOR: windowtext"><?xml:namespace prefix =3D o ns =
=3D=20
"urn:schemas-microsoft-com:office:office" /><o:p><SPAN=20
class=3D655322512-20092006><FONT face=3DArial size=3D2>Thank=20
you,</FONT></SPAN></o:p></SPAN></DIV>
<DIV><SPAN style=3D"COLOR: windowtext"><o:p><SPAN =
class=3D655322512-20092006><FONT=20
face=3DArial size=3D2>Jan van der=20
Weijde</FONT></SPAN></o:p></SPAN></DIV></DIV></BODY></HTML>

------_=_NextPart_001_01C7356B.8F019DF1--



From alvherre@commandprompt.com Fri Jan  12 00:38:15 2007
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 54D799FB26B
	for <pgsql-interfaces-postgresql.org@postgresql.org>;
	Thu, 11 Jan 2007 20:38:15 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024)
	with ESMTP id 85070-08 for
	<pgsql-interfaces-postgresql.org@postgresql.org>;
	Thu, 11 Jan 2007 20:37:53 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from lists.commandprompt.com (host-254.commandprompt.net
	[207.173.203.254])
	by postgresql.org (Postfix) with ESMTP id 5E7A89FB269
	for <pgsql-interfaces@postgresql.org>;
	Thu, 11 Jan 2007 20:37:58 -0400 (AST)
Received: from perhan.alvh.no-ip.org (200-126-100-202.bk7-dsl.surnet.cl
	[200.126.100.202]) (authenticated bits=0)
	by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l0C0akVU011034;
	Thu, 11 Jan 2007 16:36:51 -0800
Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000)
	id 7EB4332A02; Thu, 11 Jan 2007 21:36:41 -0300 (CLST)
Date: Thu, 11 Jan 2007 21:36:41 -0300
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Jan van der Weijde <Jan.van.der.Weijde@attachmate.com>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: How do I detect End-of-table or End-of-limit ?
Message-ID: <20070112003641.GI10464@alvh.no-ip.org>
Mail-Followup-To: Jan van der Weijde <Jan.van.der.Weijde@attachmate.com>,
	pgsql-interfaces@postgresql.org
References: <4B9C73D1EB78FE4A81475AE8A553B3C67DC52B@exch-lei1.attachmate.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4B9C73D1EB78FE4A81475AE8A553B3C67DC52B@exch-lei1.attachmate.com>
User-Agent: Mutt/1.5.13 (2006-08-11)
X-Virus-Scanned: ClamAV version 0.88.5,
	clamav-milter version 0.88.5 on projects.commandprompt.com
X-Virus-Status: Clean
X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by
	milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]);
	Thu, 11 Jan 2007 16:36:51 -0800 (PST)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/15
X-Sequence-Number: 6393

Jan van der Weijde wrote:

> However I am looking for something more elegant like a status flag in
> libpq that indicate EOF or End-Of-limit.

I don't think there's an actual "EOF".  What you see is an "end of
tuples in the result set".  Whether it is because you ran into the
LIMIT, or because you fell off the end of the table, you can't tell (nor
can the server actually).

Maybe it's more appropriate for you to open a cursor for the whole
table, and then do FETCH from it until it runs out of tuples; you will
know then that it's the end of the table.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.



From lbayuk@pobox.com Fri Jan  12 01:44:44 2007
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 27F809FB1DC
	for <pgsql-interfaces-postgresql.org@postgresql.org>;
	Thu, 11 Jan 2007 21:44:44 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024)
	with ESMTP id 37572-03 for
	<pgsql-interfaces-postgresql.org@postgresql.org>;
	Thu, 11 Jan 2007 21:44:31 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from elasmtp-spurfowl.atl.sa.earthlink.net
	(elasmtp-spurfowl.atl.sa.earthlink.net [209.86.89.66])
	by postgresql.org (Postfix) with ESMTP id CFC979FA398
	for <pgsql-interfaces@postgresql.org>;
	Thu, 11 Jan 2007 21:44:36 -0400 (AST)
Received: from [4.238.210.194] (helo=mindspring.com)
	by elasmtp-spurfowl.atl.sa.earthlink.net with asmtp
	(TLSv1:AES256-SHA:256) (Exim 4.34)
	id 1H5BT7-0002Rz-PE; Thu, 11 Jan 2007 20:44:34 -0500
Received: (from lbayuk@localhost)
	by mindspring.com (8.13.7/8.13.7) id l0C0fjWa000362;
	Thu, 11 Jan 2007 19:41:45 -0500
Date: Thu, 11 Jan 2007 19:41:45 -0500
From: L Bayuk <lbayuk@pobox.com>
To: Jan van der Weijde <Jan.van.der.Weijde@attachmate.com>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: How do I detect End-of-table or End-of-limit ?
Message-ID: <20070112004145.GA356@mail.mindspring.com>
References: <4B9C73D1EB78FE4A81475AE8A553B3C67DC52B@exch-lei1.attachmate.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4B9C73D1EB78FE4A81475AE8A553B3C67DC52B@exch-lei1.attachmate.com>
User-Agent: Mutt/1.4.2.2i
X-ELNK-Trace: 
 7a0205cf9d42df0a5e89bb4777695beb69025943178dce0bd8fe479c4c05b28b704f8b2e5ac6bcae350badd9bab72f9c350badd9bab72f9c350badd9bab72f9c
X-Originating-IP: 4.238.210.194
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.217 tagged_above=0 required=5 tests=AWL, BAYES_50,
	DNS_FROM_RFC_ABUSE, FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200701/16
X-Sequence-Number: 6394

On Thu, Jan 11, 2007 at 11:30:48AM +0100, Jan van der Weijde wrote:
> Hello all,
>  
> I am using the libpq interface method PQexec() to execute SELECT
> statements with a LIMIT clause. I declare a cursor with hold and use
> PQecec() to execute FETCH NEXT on that cursor.
> At a certain moment PQntuples() returns 0 after a fetch, but I cannot
> check for the actual reason: is the physical end of the table (or
> selection) reached or is the end of my limit reached.
> In the former case I'm done. But in the latter case I can continue
> selecting records by using SELECT with a LIMIT and OFFSET clause. 
>  ...

I'm not sure if this applies to you, but what I do is if I want N records,
I select with limit N+1. If I get N or fewer records back, it reached end
of table.  If I get N+1 back, I process the first N of them and discard the
last, and I know there are more records. Next query starts at the discarded
one.  So there is 1 record overlap on the queries. And unless the data
changes between queries, you will never get 0 records back.



From Jan.van.der.Weijde@attachmate.com Fri Jan  12 13:36:13 2007
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id DCE7C9FB51F
	for <pgsql-interfaces-postgresql.org@postgresql.org>;
	Fri, 12 Jan 2007 09:36:13 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024)
	with ESMTP id 26340-02 for
	<pgsql-interfaces-postgresql.org@postgresql.org>;
	Fri, 12 Jan 2007 09:35:38 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id EADA69FB524
	for <pgsql-interfaces@postgresql.org>;
	Fri, 12 Jan 2007 09:35:37 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3
Received: from Exch-Sea0.attachmate.com (automail.attachmate.com
	[150.215.8.31])
	by svr4.postgresql.org (Postfix) with ESMTP id D217F5AFA01
	for <pgsql-interfaces@postgresql.org>;
	Fri, 12 Jan 2007 13:35:36 +0000 (GMT)
Received: from exch-shan1.attachmate.com ([149.82.99.9]) by
	Exch-Sea0.attachmate.com with Microsoft SMTPSVC(6.0.3790.1830);
	Fri, 12 Jan 2007 05:35:34 -0800
Received: from exch-lei1.attachmate.com ([150.215.225.5]) by
	exch-shan1.attachmate.com with Microsoft SMTPSVC(6.0.3790.1830);
	Fri, 12 Jan 2007 13:35:17 +0000
X-MimeOLE: Produced By Microsoft Exchange V6.5
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
Subject: Re: How do I detect End-of-table or End-of-limit ?
Date: Fri, 12 Jan 2007 14:35:08 +0100
Message-ID: <4B9C73D1EB78FE4A81475AE8A553B3C67DC530@exch-lei1.attachmate.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [INTERFACES] How do I detect End-of-table or End-of-limit ?
Thread-Index: Acc14epEdtOWr4rFReifJ7Nblo/AmQAarVkw
From: "Jan van der Weijde" <Jan.van.der.Weijde@attachmate.com>
To: <pgsql-interfaces@postgresql.org>
X-OriginalArrivalTime: 12 Jan 2007 13:35:18.0206 (UTC)
	FILETIME=[7FC11DE0:01C7364E]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.268 tagged_above=0 required=5 tests=AWL, BAYES_50,
	SARE_MILLIONSOF
X-Spam-Level: 
X-Archive-Number: 200701/17
X-Sequence-Number: 6395

Thank you, however I need to use LIMIT. The table that is being accessed
has millions of records and a simple SELECT without LIMIT takes very
long.
Unless you have a solution to that performance problem I cannot leave
out LIMIT.

Regards,
Jan van der Weijde

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]=20
Sent: Friday, January 12, 2007 01:37
To: Jan van der Weijde
Cc: pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] How do I detect End-of-table or End-of-limit ?

Jan van der Weijde wrote:

> However I am looking for something more elegant like a status flag in=20
> libpq that indicate EOF or End-Of-limit.

I don't think there's an actual "EOF".  What you see is an "end of
tuples in the result set".  Whether it is because you ran into the
LIMIT, or because you fell off the end of the table, you can't tell (nor
can the server actually).

Maybe it's more appropriate for you to open a cursor for the whole
table, and then do FETCH from it until it runs out of tuples; you will
know then that it's the end of the table.

--=20
Alvaro Herrera
http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.