What about LIMIT in SELECT ?

Hi, my 2 cents...

I agree completely, LIMIT would be VERY usefull in web based apps, which
is all I run. It does not matter to me if it is not part of a formal
standard. The idea is so common that it is a defacto standard.

I would not expect it for this release, but could it get put on the TODO
list for next time? I am even willing to work at an apprentise level on
this with a more expeireanced person that knows this stuff.

A note on implimentation:
I *used to* :) work with VFP on NT's :(
And the way VFP did LIMIT, it would only return the number of rows asked
for, BUT it still did the WHOLE search!
So on a larger table, which we had (property tax database for the county),
if some one put in too vague a query, it would try to collect ALL of the
rows as the initial result set, then give you the first x rows of that.

This did save on pushing mass amounts of data out to the browser, but it
would have been even better if it could have simply aborted the select
after having found x rows.

Also, it did not have the concept of an offset, so one could not select
100 rows, starting 200 rows in, which would be REALLY usefull for "paging"
through data. I do not know if mySQL or any other has such a concept
either, but it would be nice.

So a properly implemented "LIMIT" could:
1. Save pushing mass amounts of data across the web, that no one wants
any way.
2. Stop vague queries from bogging down the server.
(On very larg tables this could be critical!)
3. Enable "Paging" of data. (easyer then now (app. level))
4. Would be a very nice feather in PostgreSQL's cap that could make it
even more attractive to those looking at all sorts of databases out there.

Have a great day.

On Tue, 13 Oct 1998, Oleg Bartunov wrote:

Hi,

I took a look at mysql and was very impressed with possibility
to limit number of rows returned from select. This is very useful
feature for Web applications when user need to browse results of
selection page by page. In my application I have to do full
select every time user press button [Next] and show requested page
using perl. This works more or less ok for several thousands rows but
totally unusable for large selections. But now I'm about to work
with big database and I don't know how I'll stay with postgres :-)
It'll just doesn't work if customer will wait several minutes just browse
next page. Mysql lacks some useful features postgres has
(subselects, transaction ..) but for most Web applications I need
just select :-) I dont' know how LIMIT is implemented in Mysql and
I know it's not in SQL92 standart, but this makes Mysql very popular.

Is it difficult to implement this feature in postgres ?

Regards,

Oleg

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Terry Mackintosh <terry@terrym.com> http://www.terrym.com
sysadmin/owner Please! No MIME encoded or HTML mail, unless needed.

Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3
-------------------------------------------------------------------
Success Is A Choice ... book by Rick Patino, get it, read it!

What is wrong with the already implemented FETCH command?

On Tue, 13 Oct 1998, Terry Mackintosh wrote:

Hi, my 2 cents...

I agree completely, LIMIT would be VERY usefull in web based apps, which
is all I run. It does not matter to me if it is not part of a formal
standard. The idea is so common that it is a defacto standard.

I would not expect it for this release, but could it get put on the TODO
list for next time? I am even willing to work at an apprentise level on
this with a more expeireanced person that knows this stuff.

A note on implimentation:
I *used to* :) work with VFP on NT's :(
And the way VFP did LIMIT, it would only return the number of rows asked
for, BUT it still did the WHOLE search!
So on a larger table, which we had (property tax database for the county),
if some one put in too vague a query, it would try to collect ALL of the
rows as the initial result set, then give you the first x rows of that.

This did save on pushing mass amounts of data out to the browser, but it
would have been even better if it could have simply aborted the select
after having found x rows.

Also, it did not have the concept of an offset, so one could not select
100 rows, starting 200 rows in, which would be REALLY usefull for "paging"
through data. I do not know if mySQL or any other has such a concept
either, but it would be nice.

So a properly implemented "LIMIT" could:
1. Save pushing mass amounts of data across the web, that no one wants
any way.
2. Stop vague queries from bogging down the server.
(On very larg tables this could be critical!)
3. Enable "Paging" of data. (easyer then now (app. level))
4. Would be a very nice feather in PostgreSQL's cap that could make it
even more attractive to those looking at all sorts of databases out there.

Have a great day.

On Tue, 13 Oct 1998, Oleg Bartunov wrote:

Hi,

I took a look at mysql and was very impressed with possibility
to limit number of rows returned from select. This is very useful
feature for Web applications when user need to browse results of
selection page by page. In my application I have to do full
select every time user press button [Next] and show requested page
using perl. This works more or less ok for several thousands rows but
totally unusable for large selections. But now I'm about to work
with big database and I don't know how I'll stay with postgres :-)
It'll just doesn't work if customer will wait several minutes just browse
next page. Mysql lacks some useful features postgres has
(subselects, transaction ..) but for most Web applications I need
just select :-) I dont' know how LIMIT is implemented in Mysql and
I know it's not in SQL92 standart, but this makes Mysql very popular.

Is it difficult to implement this feature in postgres ?

Regards,

Oleg

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Terry Mackintosh <terry@terrym.com> http://www.terrym.com
sysadmin/owner Please! No MIME encoded or HTML mail, unless needed.

Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3
-------------------------------------------------------------------
Success Is A Choice ... book by Rick Patino, get it, read it!

Marc G. Fournier scrappy@hub.org
Systems Administrator @ hub.org
scrappy@{postgresql|isc}.org ICQ#7615664

Hi, my 2 cents...

I agree completely, LIMIT would be VERY usefull in web based apps, which
is all I run. It does not matter to me if it is not part of a formal
standard. The idea is so common that it is a defacto standard.

i'm not familiar with mysql and using "LIMIT" but wouldn't this same effect
be achieved by declaring a cursor and fetching however many records in the
cursor? it's a very noticeable improvement when you only want the first 20
out of 500 in a 200k record database, at least.

jeff

On Tue, 13 Oct 1998, Jeff Hoffmann wrote:

I agree completely, LIMIT would be VERY usefull in web based apps, which
is all I run. It does not matter to me if it is not part of a formal
standard. The idea is so common that it is a defacto standard.

i'm not familiar with mysql and using "LIMIT" but wouldn't this same effect
be achieved by declaring a cursor and fetching however many records in the
cursor? it's a very noticeable improvement when you only want the first 20
out of 500 in a 200k record database, at least.

The problem with declaring a cursor vs. the "LIMIT" clause is that the
"LIMIT" clause, if used properly by the database engine (along with the
database engine using indexes in "ORDER BY" clauses) allows the database
engine to short-circuit the tail end of the query. That is, if you have 25
names and the last one ends with BEAVIS, the database engine doesn't have
to go through the BUTTHEADS and KENNYs and etc.

Theoretically a cursor is superior to the "LIMIT" clause because you're
eventually going to want the B's and K's and etc. anyhow -- but only in a
stateful enviornment. In the stateless web environment, a cursor is
useless because the connection can close at any time even when you're
using "persistent" connections (and of course when the connection closes
the cursor closes).

I wanted very badly to use PostgreSQL for a web project I'm working on,
but it just wouldn't do the job :-(.

--
Eric Lee Green eric@linux-hw.com http://www.linux-hw.com/~eric
"To call Microsoft an innovator is like calling the Pope Jewish ..."
-- James Love (Consumer Project on Technology)

On Tue, 13 Oct 1998, Eric Lee Green wrote:

On Tue, 13 Oct 1998, Jeff Hoffmann wrote:

I agree completely, LIMIT would be VERY usefull in web based apps, which
is all I run. It does not matter to me if it is not part of a formal
standard. The idea is so common that it is a defacto standard.

i'm not familiar with mysql and using "LIMIT" but wouldn't this same effect
be achieved by declaring a cursor and fetching however many records in the
cursor? it's a very noticeable improvement when you only want the first 20
out of 500 in a 200k record database, at least.

The problem with declaring a cursor vs. the "LIMIT" clause is that the
"LIMIT" clause, if used properly by the database engine (along with the
database engine using indexes in "ORDER BY" clauses) allows the database
engine to short-circuit the tail end of the query. That is, if you have 25
names and the last one ends with BEAVIS, the database engine doesn't have
to go through the BUTTHEADS and KENNYs and etc.

Theoretically a cursor is superior to the "LIMIT" clause because you're
eventually going to want the B's and K's and etc. anyhow -- but only in a
stateful enviornment. In the stateless web environment, a cursor is
useless because the connection can close at any time even when you're
using "persistent" connections (and of course when the connection closes
the cursor closes).

Ookay, I'm sorry, butyou lost me here. I haven't gotten into using
CURSORs/FETCHs yet, since I haven't need it...but can you give an example
of what you would want to do using a LIMIT? I may be missing something,
but wha is the different between using LIMIT to get X records, and
definiing a cursor to FETCH X records?

Practical example of *at least* the LIMIT side would be good, so that we
can at least see a physical example of what LIMIT can do that
CURSORs/FETCH can't...

Marc G. Fournier scrappy@hub.org
Systems Administrator @ hub.org
scrappy@{postgresql|isc}.org ICQ#7615664

Hi, my 2 cents...

I agree completely, LIMIT would be VERY usefull in web based apps, which
is all I run. It does not matter to me if it is not part of a formal
standard. The idea is so common that it is a defacto standard.

I would not expect it for this release, but could it get put on the TODO
list for next time? I am even willing to work at an apprentise level on
this with a more expeireanced person that knows this stuff.

I assume everyone has read the FAQ item:

How do I <I>select</I> only the first few rows of a query?

See the fetch manual page.<P>

This only prevents all row results from being transfered to the client.
The entire query must be evaluated, even if you only want just the first
few rows. Consider a query that has an order by. There is no way
to return any rows until the entire query is evaluated and sorted.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Theoretically a cursor is superior to the "LIMIT" clause because you're
eventually going to want the B's and K's and etc. anyhow -- but only in a
stateful enviornment. In the stateless web environment, a cursor is
useless because the connection can close at any time even when you're
using "persistent" connections (and of course when the connection closes
the cursor closes).

I wanted very badly to use PostgreSQL for a web project I'm working on,
but it just wouldn't do the job :-(.

See my other posting mentioning the FAQ item on this subject. If you
are going after only one table(no joins), and have no ORDER BY, we could
short-circuit the evaluation, but how many queries could use LIMIT in
that case? Zero, I think.

What we could do is _if_ there is only one table(no joins), and an index
exists that matches the ORDER BY, we could use the index to
short-circuit the query.

I have added this item to the TODO list:

* Allow LIMIT ability on single-table queries that have no ORDER BY or
a matching index

This looks do-able, and a real win. Would this make web applications
happier? If there is an ORDER BY and no index, or a join, I can't
figure out how we would short-circuit the query.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Ookay, I'm sorry, butyou lost me here. I haven't gotten into using
CURSORs/FETCHs yet, since I haven't need it...but can you give an example
of what you would want to do using a LIMIT? I may be missing something,
but wha is the different between using LIMIT to get X records, and
definiing a cursor to FETCH X records?

Practical example of *at least* the LIMIT side would be good, so that we
can at least see a physical example of what LIMIT can do that
CURSORs/FETCH can't...

My guess in a web application is that the transaction is started for
every new page, so you can't have transactions spanning SQL sessions.

LIMIT theoretically would allow you to start up where you left off.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Tue, 13 Oct 1998, Eric Lee Green wrote:

On Tue, 13 Oct 1998, Jeff Hoffmann wrote:

I agree completely, LIMIT would be VERY usefull in web based apps,

which

is all I run. It does not matter to me if it is not part of a formal
standard. The idea is so common that it is a defacto standard.

i'm not familiar with mysql and using "LIMIT" but wouldn't this same

effect

be achieved by declaring a cursor and fetching however many records in

the

cursor? it's a very noticeable improvement when you only want the

first 20

out of 500 in a 200k record database, at least.

The problem with declaring a cursor vs. the "LIMIT" clause is that the
"LIMIT" clause, if used properly by the database engine (along with the
database engine using indexes in "ORDER BY" clauses) allows the database
engine to short-circuit the tail end of the query. That is, if you have

25

names and the last one ends with BEAVIS, the database engine doesn't have
to go through the BUTTHEADS and KENNYs and etc.

Theoretically a cursor is superior to the "LIMIT" clause because you're
eventually going to want the B's and K's and etc. anyhow -- but only in a
stateful enviornment. In the stateless web environment, a cursor is
useless because the connection can close at any time even when you're
using "persistent" connections (and of course when the connection closes
the cursor closes).

Ookay, I'm sorry, butyou lost me here. I haven't gotten into using
CURSORs/FETCHs yet, since I haven't need it...but can you give an example
of what you would want to do using a LIMIT? I may be missing something,
but wha is the different between using LIMIT to get X records, and
definiing a cursor to FETCH X records?

Practical example of *at least* the LIMIT side would be good, so that we
can at least see a physical example of what LIMIT can do that
CURSORs/FETCH can't...

fetch with cursors should work properly (i.e., you can short circuit it by
just ending your transaction) my understanding on how this works is exactly
how you explained LIMIT to work. here's some empirical proof from one of my
sample databases:

the sample table i'm using has 156k records (names of people)
i'm using a PP180 with 128MB RAM and some old slow SCSI drives.

public_mn=> select count(*) from public_ramsey;
count
------
156566
(1 row)

i did the following query:
public_mn=> select * from public_ramsey where ownerlname ~ 'SMITH';

which returned 711 matches and took about 12 seconds.

i did the same thing with a cursor:

public_mn=> begin;
BEGIN
public_mn=> declare test cursor for select * from public_ramsey where
ownerlname ~ 'SMITH';
SELECT

the select was instantaneous.

public_mn=> fetch 20 in test;

returns 20 records almost instantaneously. each additional 20 took less
than a second, as well.

if this isn't what you're talking about, i don't understand what you're
saying.

jeff

On Tue, 13 Oct 1998, Marc G. Fournier wrote:

On Tue, 13 Oct 1998, Eric Lee Green wrote:

Theoretically a cursor is superior to the "LIMIT" clause because you're
eventually going to want the B's and K's and etc. anyhow -- but only in a
stateful enviornment. In the stateless web environment, a cursor is
useless because the connection can close at any time even when you're

Ookay, I'm sorry, butyou lost me here. I haven't gotten into using
CURSORs/FETCHs yet, since I haven't need it...but can you give an example
of what you would want to do using a LIMIT? I may be missing something,

Whoops! Sorry, I goofed in my post (typing faster than my brain :-).
What I *MEANT* to say was that this superiority of cursors was not
applicable in a web environment.

but wha is the different between using LIMIT to get X records, and
definiing a cursor to FETCH X records?

From a logical point of view, none. From an implementation point of

view, it is a matter of speed. Declaring a cursor four times, doing a
query four times, and fetching X records four times takes more time
than just doing a query with a LIMIT clause four times (assuming your
query results in four screenfulls of records).

Practical example of *at least* the LIMIT side would be good, so that we
can at least see a physical example of what LIMIT can do that
CURSORs/FETCH can't...

You can do everything with CURSORs/FETCH that you can do with LIMIT.
In a non-web environment, where you have stateful connections, a FETCH
is always going to be faster than a SELECT...LIMIT statement. (Well,
it would be if implemented correctly, but I'll leave that to others to
haggle over). However: In a CGI-type environment, cursors are a huge
performance drain because in the example above you end up doing this
huge query four times, with its results stored in the cursor four
times, and only a few values are ever fetched from the cursor before it
is destroyed by the end of the CGI script.

Whereas with the SELECT...LIMIT paradigm, the database engine does NOT
process the entire huge query, it quits processing once it reaches the
limit. (Well, at least MySQL does so, if you happen to be using an
"ORDER BY" supported by an index). Obviously doing 1/4th the work four times
is better than doing the whole tamale four times :-}.

--
Eric Lee Green eric@linux-hw.com http://www.linux-hw.com/~eric
"To call Microsoft an innovator is like calling the Pope Jewish ..."
-- James Love (Consumer Project on Technology)

On Tue, 13 Oct 1998, Eric Lee Green wrote:

Whoops! Sorry, I goofed in my post (typing faster than my brain :-).
What I *MEANT* to say was that this superiority of cursors was not
applicable in a web environment.

S'alright...now please backup your statement with the *why*...

but wha is the different between using LIMIT to get X records, and
definiing a cursor to FETCH X records?

From a logical point of view, none. From an implementation point of

view, it is a matter of speed. Declaring a cursor four times, doing a
query four times, and fetching X records four times takes more time
than just doing a query with a LIMIT clause four times (assuming your
query results in four screenfulls of records).

I'm going to be brain-dead here, since, as I've disclaimered
before, I've not used CURSORs/FETCHs as of yet...one person came back
already and stated that, for him, CURSOR/FETCH results were near
instantaneous with a 167k+ table...have you tested the two to ensure that,
in fact, one is/isn't faster then the other?

Marc G. Fournier scrappy@hub.org
Systems Administrator @ hub.org
scrappy@{postgresql|isc}.org ICQ#7615664

On Tue, 13 Oct 1998, Bruce Momjian wrote:

Theoretically a cursor is superior to the "LIMIT" clause because you're
eventually going to want the B's and K's and etc. anyhow -- but only in a
stateful enviornment. In the stateless web environment, a cursor is
useless because the connection can close at any time even when you're
using "persistent" connections (and of course when the connection closes

What we could do is _if_ there is only one table(no joins), and an index
exists that matches the ORDER BY, we could use the index to
short-circuit the query.

This is exactly what MySQL does in this situation, except that it can use
the ORDER BY to do the short circuiting even if there is a join involved
if all of the elements of the ORDER BY belong to one table. Obviously if
I'm doing an "ORDER BY table1.foo table2.bar" that isn't going to work!
But "select table1.fsname,table1.lname,table2.receivables where
table2.receivables > 0 and table1.custnum=table2.custnum order by
(table1.lname,table1.fsname) limit 50" can be short-circuited by fiddling
with the join order -- table1.fsname table1.lname have to be the first two
things in the join order.

Whether this is feasible in PostgreSQL I have no earthly idea. This would
seem to conflict with the join optimizer.

happier? If there is an ORDER BY and no index, or a join, I can't
figure out how we would short-circuit the query.

If there is an ORDER BY and no index you can't short-circuit the query.
MySQL doesn't either. Under certain circumstances (such as above) you can
short-circuit a join, but it's unclear whether it'd be easy to add such
a capability to PostgreSQL given the current structure of the query
optimizer. (And I certainly am not in a position to tackle it, at the
moment MySQL is sufficing for my project despite the fact that it is
quite limited compared to PostgreSQL, I need to get my project finished
first).

--
Eric Lee Green eric@linux-hw.com http://www.linux-hw.com/~eric
"To call Microsoft an innovator is like calling the Pope Jewish ..."
-- James Love (Consumer Project on Technology)

On Tue, 13 Oct 1998, Marc G. Fournier wrote:

On Tue, 13 Oct 1998, Eric Lee Green wrote:

Whoops! Sorry, I goofed in my post (typing faster than my brain :-).
What I *MEANT* to say was that this superiority of cursors was not
applicable in a web environment.

S'alright...now please backup your statement with the *why*...

Okay. It is because CGI is a stateless environment. You cannot just keep a
cursor open and walk up and down it, which is the superiority of cursors
(it is always faster to walk up and down a pre-SELECT'ed list than it is
to perform additional SELECTs). You have to destroy it upon exiting the
CGI script (which presumably just fetched 25 items or so to display on
an HTML page -- think DejaNews).

Creating a cursor and destroying a cursor take time. Less time, in a
normal environment, than it would take to make multiple SELECT statements,
which is the superiority of cursors in a normal environment. But, like I
said, CGI isn't normal -- the CGI script exits at the end of displaying 25
items, at which point the cursor is destroyed, thus destroying any benefit
you could have gotten while adding additional overhead.

In addition there is the possibility of short-circuiting the SELECT if
there is a LIMIT clause and there is no ORDER BY clause or the ORDER BY
clause is walking down an index (the later being a possibility only if
there is no 'join' involved or if the 'join' is simple enough that it can
be done without running afoul of the join optimizer). Cursors, by their
nature, require performing the entire tamale first.

From a logical point of view, none. From an implementation point of

view, it is a matter of speed. Declaring a cursor four times, doing a

already and stated that, for him, CURSOR/FETCH results were near
instantaneous with a 167k+ table...have you tested the two to ensure that,
in fact, one is/isn't faster then the other?

CURSOR/FETCH *SHOULD* be nearly instantaneous, because you're merely
fetching values from a pre-existing query result. As I said, in normal
(non-CGI) use, a cursor is FAR superior to a "LIMIT" clause.

But the question of whether declaring a cursor four times and destroying
it four times takes a sizable amount of time compared to a LIMIT
clause... it's really hard to test, unfortunately, due to the differing
natures of MySQL and PostgreSQL. MySQL starts up a connection very fast
while PostgreSQL takes awhile (has anybody done work on the "herd of
servers" concept to tackle that?). It is hard, in a CGI environment, to
detirmine if the poor speed (in terms of number of hits the server can
take) is due to the slow connection startup time or due to the cursor
overhead. I could write a benchmark program that kept the connection open
and did just the cursor timings, but I'm not particularly motivated.

I think RMS has a point when he decries the fact that non-free software is
becoming more available for Linux (MySQL is definitely non-free)... i.e.,
that it takes away people's motivation to improve the free software. The
only good part there is that MySQL is hardly suitable for normal database
work -- it is very much optimized for web serving and other applications
of that sort where speed and CGI-friendliness are more important than
functionality.

--
Eric Lee Green eric@linux-hw.com http://www.linux-hw.com/~eric
"To call Microsoft an innovator is like calling the Pope Jewish ..."
-- James Love (Consumer Project on Technology)

I can't speak to the relative efficiencies of the methods, but I do
perform queries that present data subsets to web browsers using postgresql
with the following method:

1) collect data input; do cgi query; write tuples to temporary file
2) html page index sent back to browser contains page specific
references to temporary file name and tuple range.
3) Subsequent data retrievals reference temporary file using sed and
tuple range
4) temporary file is destroyed 15min after last access time by a
background process.
This consumes disk space, but I assume it conserves memory compared to
a cursor/fetch sequence performed in a persistent db connection.

For a general purpose query, I'm not sure if there is any other
alternative to this method unless you wish to reperform the query
for each retrieved html page.

Marc Zuckman
marc@fallon.classyad.com

_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
_ Visit The Home and Condo MarketPlace _
_ http://www.ClassyAd.com _
_ _
_ FREE basic property listings/advertisements and searches. _
_ _
_ Try our premium, yet inexpensive services for a real _
_ selling or buying edge! _
_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

Whereas with the SELECT...LIMIT paradigm, the database engine does NOT
process the entire huge query, it quits processing once it reaches the
limit. (Well, at least MySQL does so, if you happen to be using an
"ORDER BY" supported by an index). Obviously doing 1/4th the work four times
is better than doing the whole tamale four times :-}.

And no join, I assume.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

This might be off-topic, but...

I've found ExecutorLimit() (in executor/execMain.c) is useful for me
especially when issuing an ad-hock query via psql. I personally use
the function with customized set command.

set query_limit to 'num';

limit the max number of results returned by the backend

show query_limit;

display the current query limit

reset query_limit;

disable the query limit (unlimited number of results allowed)
--
Tatsuo Ishii
t-ishii@sra.co.jp

I've found ExecutorLimit() (in executor/execMain.c) is useful for me
especially when issuing an ad-hock query via psql. I personally use
the function with customized set command.

Looks interesting. So where are the patches? :)

- Tom

I've found ExecutorLimit() (in executor/execMain.c) is useful for me
especially when issuing an ad-hock query via psql. I personally use
the function with customized set command.

Looks interesting. So where are the patches? :)

I'll post pacthes within 24 hours:-)
--
Tatsuo Ishii
t-ishii@sra.co.jp

I've found ExecutorLimit() (in executor/execMain.c) is useful for me
especially when issuing an ad-hock query via psql. I personally use
the function with customized set command.

Looks interesting. So where are the patches? :)

I'll post pacthes within 24 hours:-)

Here it is.
--
Tatsuo Ishii
t-ishii@sra.co.jp
----------------------------------------------------------------
*** backend/commands/variable.c.orig	Fri Oct  9 09:56:51 1998
--- backend/commands/variable.c	Wed Oct 14 13:06:15 1998
***************
*** 18,23 ****
--- 18,27 ----
  #ifdef MULTIBYTE
  #include "mb/pg_wchar.h"
  #endif
+ #ifdef QUERY_LIMIT
+ #include "executor/executor.h"
+ #include "executor/execdefs.h"
+ #endif

  static bool show_date(void);
  static bool reset_date(void);
***************
*** 40,45 ****
--- 44,54 ----
  static bool show_ksqo(void);
  static bool reset_ksqo(void);
  static bool parse_ksqo(const char *);
+ #ifdef QUERY_LIMIT
+ static bool show_query_limit(void);
+ static bool reset_query_limit(void);
+ static bool parse_query_limit(const char *);
+ #endif

  extern Cost _cpu_page_wight_;
  extern Cost _cpu_index_page_wight_;
***************
*** 546,551 ****
--- 555,600 ----
  }	/* reset_timezone() */

  /*-----------------------------------------------------------------------*/
+ #ifdef QUERY_LIMIT
+ static bool
+ parse_query_limit(const char *value)
+ {
+   int32 limit;
+ 
+   if (value == NULL) {
+     reset_query_limit();
+     return(TRUE);
+   }
+   limit = pg_atoi(value, sizeof(int32), '\0');
+   if (limit <= -1) {
+     elog(ERROR, "Bad value for # of query limit (%s)", value);
+   }
+   ExecutorLimit(limit);
+   return(TRUE);
+ }
+ 
+ static bool
+ show_query_limit(void)
+ {
+   int limit;
+ 
+   limit = ExecutorGetLimit();
+   if (limit == ALL_TUPLES) {
+     elog(NOTICE, "No query limit is set");
+   } else {
+     elog(NOTICE, "query limit is %d",limit);
+   }
+   return(TRUE);
+ }
+ 
+ static bool
+ reset_query_limit(void)
+ {
+   ExecutorLimit(ALL_TUPLES);
+   return(TRUE);
+ }
+ #endif
+ /*-----------------------------------------------------------------------*/
  struct VariableParsers
  {
  	const char *name;
***************
*** 584,589 ****
--- 633,643 ----
  	{
  		"ksqo", parse_ksqo, show_ksqo, reset_ksqo
  	},
+ #ifdef QUERY_LIMIT
+ 	{
+ 		"query_limit", parse_query_limit, show_query_limit, reset_query_limit
+ 	},
+ #endif
  	{
  		NULL, NULL, NULL, NULL
  	}
*** backend/executor/execMain.c.orig	Thu Oct  1 11:03:58 1998
--- backend/executor/execMain.c	Wed Oct 14 11:24:06 1998
***************
*** 83,94 ****
  #undef ALL_TUPLES
  #define ALL_TUPLES queryLimit

- int ExecutorLimit(int limit);
-
int
ExecutorLimit(int limit)
{
return queryLimit = limit;
}

  #endif
--- 83,98 ----
  #undef ALL_TUPLES
  #define ALL_TUPLES queryLimit

  int
  ExecutorLimit(int limit)
  {
  	return queryLimit = limit;
+ }
+ 
+ int
+ ExecutorGetLimit()
+ {
+ 	return queryLimit;
  }

  #endif
*** include/executor/executor.h.orig	Fri Oct  9 10:02:07 1998
--- include/executor/executor.h	Wed Oct 14 11:24:07 1998
***************
*** 86,91 ****
--- 86,95 ----
  extern TupleTableSlot *ExecutorRun(QueryDesc *queryDesc, EState *estate, int feature, int count);
  extern void ExecutorEnd(QueryDesc *queryDesc, EState *estate);
  extern HeapTuple ExecConstraints(char *caller, Relation rel, HeapTuple tuple);
+ #ifdef QUERY_LIMIT
+ extern int ExecutorLimit(int limit);
+ extern int ExecutorGetLimit(void);
+ #endif

/*
* prototypes from functions in execProcnode.c