Hi guys,

What is the limit on the number of rows in a PostgreSQL table?  If it's 
more than MAXINT, we have a problem:

phppgadmin# select * from test limit 2147483648;
ERROR:  integer out of range

Same problem with OFFSET.

Chris



Wed, 22 Oct 2003 16:01:40 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
integer ceiling in LIMIT and OFFSET
On Wed, 2003-10-22 at 04:01, Christopher Kings-Lynne wrote:
> Hi guys,
> 
> What is the limit on the number of rows in a PostgreSQL table?  If it's 
> more than MAXINT, we have a problem:
> 
> phppgadmin# select * from test limit 2147483648;
> ERROR:  integer out of range
> 
> Same problem with OFFSET.

I see you're point, but nobody is going to be interested in the first 2
billion rows of a table without using a cursor and having some other
process do the math in the background.

That said, perhaps the TODO for changing LIMIT / OFFSET to be expression
based should also mention bumping them to int8.
Wed, 22 Oct 2003 08:34:56 -0400
Rod Taylor <rbt@rbt.ca>
Re: integer ceiling in LIMIT and OFFSET
Rod Taylor <rbt@rbt.ca> writes:
> That said, perhaps the TODO for changing LIMIT / OFFSET to be expression
> based should also mention bumping them to int8.

Can't get excited about it ... this would slow down the normal use of
the facility for what seems a completely hypothetical need.

			regards, tom lane

Wed, 22 Oct 2003 09:44:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: integer ceiling in LIMIT and OFFSET 
> I see you're point, but nobody is going to be interested in the first 2
> billion rows of a table without using a cursor and having some other
> process do the math in the background.

You have the same problem:

test=# begin;
BEGIN
test=# declare c cursor for select * from a;
DECLARE CURSOR
test=# move 2147483647 in c;
MOVE 0
test=# move 2147483648 in c;
ERROR:  syntax error at or near "2147483648" at character 6
test=#
test=# fetch absolute 2147483648 in c;
ERROR:  syntax error at or near "2147483648" at character 16
test=# fetch relative 2147483648 in c;
ERROR:  syntax error at or near "2147483648" at character 16

> That said, perhaps the TODO for changing LIMIT / OFFSET to be expression
> based should also mention bumping them to int8.

It is fairly hypothetical, but there are some people starting to put 
some rather large databases in Postgres these days. Shouldn't it at 
least be an unsigned integer?

Chris



Wed, 22 Oct 2003 22:22:18 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: integer ceiling in LIMIT and OFFSET
On Wed, 22 Oct 2003, Christopher Kings-Lynne wrote:

> > I see you're point, but nobody is going to be interested in the first 2
> > billion rows of a table without using a cursor and having some other
> > process do the math in the background.
>
> You have the same problem:
>
> test=# begin;
> BEGIN
> test=# declare c cursor for select * from a;
> DECLARE CURSOR
> test=# move 2147483647 in c;
> MOVE 0
> test=# move 2147483648 in c;
> ERROR:  syntax error at or near "2147483648" at character 6

> > That said, perhaps the TODO for changing LIMIT / OFFSET to be expression
> > based should also mention bumping them to int8.
>
> It is fairly hypothetical, but there are some people starting to put
> some rather large databases in Postgres these days. Shouldn't it at
> least be an unsigned integer?

It can't be for move/fetch since negative numbers are meaningful, and imho
it'd be fairly unintuitive for limit/offset to allow unsigned range but
move/fetch to only allow signed, although at least with move/fetch
multiple statements should work to get the position/count you want
(breaking it up into portions of no more than maxint).

Wed, 22 Oct 2003 07:57:21 -0700 (PDT)
Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: integer ceiling in LIMIT and OFFSET
On Wed, 2003-10-22 at 10:22, Christopher Kings-Lynne wrote:
> > I see you're point, but nobody is going to be interested in the first 2
> > billion rows of a table without using a cursor and having some other
> > process do the math in the background.
> 
> You have the same problem:

> test=# move 2147483648 in c;
> ERROR:  syntax error at or near "2147483648" at character 6

You're simply not going to be interested in doing this. Fetching rows in
blocks of 1 million for processing is probably the highest sane maximum.

Now, if we have a problem with a cursor doing 1 million loops pulling 1
million rows each iteration then we have a problem.
Wed, 22 Oct 2003 11:03:09 -0400
Rod Taylor <rbt@rbt.ca>
Re: integer ceiling in LIMIT and OFFSET
On Wed, 22 Oct 2003, Tom Lane wrote:

> Rod Taylor <rbt@rbt.ca> writes:
> > That said, perhaps the TODO for changing LIMIT / OFFSET to be expression
> > based should also mention bumping them to int8.
> 
> Can't get excited about it ... this would slow down the normal use of
> the facility for what seems a completely hypothetical need.

While I'm pretty sure 

select * from sometable limit 2147483648

isn't gonna be common, maybe someone would be likely to do something like:

select * from sometable limit 10 offset 2147483648

I wouldn't do it, but who knows what shadows lurk in men's minds?


Wed, 22 Oct 2003 10:08:23 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: integer ceiling in LIMIT and OFFSET 
On Wed, 2003-10-22 at 12:08, scott.marlowe wrote:
> On Wed, 22 Oct 2003, Tom Lane wrote:
> 
> > Rod Taylor <rbt@rbt.ca> writes:
> > > That said, perhaps the TODO for changing LIMIT / OFFSET to be expression
> > > based should also mention bumping them to int8.
> > 
> > Can't get excited about it ... this would slow down the normal use of
> > the facility for what seems a completely hypothetical need.
> 
> While I'm pretty sure 
> 
> select * from sometable limit 2147483648
> 
> isn't gonna be common, maybe someone would be likely to do something like:
> 
> select * from sometable limit 10 offset 2147483648

> I wouldn't do it, but who knows what shadows lurk in men's minds?

The overhead in simply getting to that offset is going to be significant
and I would place my bets against anyone attempting that.

A cursor pulling small chunks of a multi-billion tuple set is probably
much more common, so we should ensure those work.
Wed, 22 Oct 2003 13:25:23 -0400
Rod Taylor <rbt@rbt.ca>
Re: integer ceiling in LIMIT and OFFSET
Rod Taylor <rbt@rbt.ca> writes:
> A cursor pulling small chunks of a multi-billion tuple set is probably
> much more common, so we should ensure those work.

This should work ... but I haven't personally had the patience to test
it ...

			regards, tom lane

Wed, 22 Oct 2003 13:27:55 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: integer ceiling in LIMIT and OFFSET 
On Wed, 22 Oct 2003, scott.marlowe wrote:

> > > That said, perhaps the TODO for changing LIMIT / OFFSET to be expression
> > > based should also mention bumping them to int8.
> > 
> > Can't get excited about it ... this would slow down the normal use of
> > the facility for what seems a completely hypothetical need.
> 
> While I'm pretty sure 
> 
> select * from sometable limit 2147483648
> 
> isn't gonna be common, maybe someone would be likely to do something like:
> 
> select * from sometable limit 10 offset 2147483648
> 
> I wouldn't do it, but who knows what shadows lurk in men's minds?

Yeah, the thing is that it may never enter "men's minds" -- an application 
would be written that grabs, say, 10 rows at random from the whole table, 
and ends up plugging in big numbers as in your example. Rarely do people 
think ahead to what happens when the tables get really big.

Of course I don't imagine it'll be common either. :)

Jon

Wed, 22 Oct 2003 19:12:41 +0000 (UTC)
Jon Jensen <jon@endpoint.com>
Re: integer ceiling in LIMIT and OFFSET 
Rod Taylor wrote:
-- Start of PGP signed section.
> On Wed, 2003-10-22 at 04:01, Christopher Kings-Lynne wrote:
> > Hi guys,
> > 
> > What is the limit on the number of rows in a PostgreSQL table?  If it's 
> > more than MAXINT, we have a problem:
> > 
> > phppgadmin# select * from test limit 2147483648;
> > ERROR:  integer out of range
> > 
> > Same problem with OFFSET.
> 
> I see you're point, but nobody is going to be interested in the first 2
> billion rows of a table without using a cursor and having some other
> process do the math in the background.
> 
> That said, perhaps the TODO for changing LIMIT / OFFSET to be expression
> based should also mention bumping them to int8.

Added to TODO:

	* Change LIMIT/OFFSET to use int8

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sun, 26 Oct 2003 00:43:51 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: integer ceiling in LIMIT and OFFSET