Re: [HACKERS] Show stopper ? (was: Re: "cruising" or "browsing" through tables using an index / ordering)

On Fri, 23 Jan 1998, Jan Vicherek wrote:

> On Fri, 23 Jan 1998, The Hermit Hacker wrote:
>
> > I'm curious, but can the "Big (commercial) Boys" do this? If so,
> > can you please provide an example of which and how?
>
> Hmm, well, the one we are switching from does this ;-) (Informix 3.3
> ALL-II C interface). It's not SQL, tho.

Okay, well, ummm...now you are comparing apples to oranges
then...if you wanted a non-SQL engine to replace Informix, PostgreSQL
isn't what you are looking for :(

> > all I've seen is ... and several examples on how to accomplish it using
> > PostgreSQL, but that's it...
>
> Wait, have you seen here an example that accomplishes this which
> wouldn't need the whole table copied and wouldn't lock the table against
> updates ?

First off, *nothing* you are going to be able to accomplish in
*any* SQL engine is going to prevent locking the table against
updates...the code that Bruce put in this afternoon for v6.3 is going to
reduce the possibility of the lock causing a deadlock, but that is about
it...the lock will still be created.

> > Why not put a sequence field on the table so that you can do:
> > select * from table where rowid = n; -or-
> > select * from table where rowid = n - 1; -or-
> > select * from table where rowid = n + 1; -or-
> > select * from table where rowid >= n and rowid <= n+x;
> >
> > And create the index on rowid?
>
> Because I also need to be able to INSERT rows. That would require
> renumeration of half the table (remember, it's 40MB, 400,000 rows) every
> time I do an INSERT.

Okay, you are confusing INSERT then...INSERT in SQL just adds a
row, that's it...it doesn't perform any "sorting" on it...that's what the
ORDER BY command does...

...but, I now understand what *you* mean by INSERT...

> I *still* think that there *has to* be a way to find a value that is
> immediatelly next to one I have. This seems like such a primitive
> operation. Even the backend must be doing it on some operations, it would
> seem.

Not possible...INSERT into a table doesn't "merge" the record
between its lower/higher bounds...it just adds it to the very end of the
table. And an index doesn't "sort" the data either...that is what the
ORDER BY clause is for...

> Maybe even in SQL. Maybe something like (I'm not an SQL expert) : "SELECT
> IndexField from MyTable where InxdexField > 'my_current_value' and
> IndexField < ("all IndexFields that are bigger than the IndexField
> searched for")

From your sample above, is your first SQL call going to pull out
all 40MB of data in one select statement, then your second 40MB minus the
first X records?

What you want to do is:

begin
declare mycursor cursor for select * from pg-user order by <somefield>;
move $forward in FOO;
fetch $retrieve in FOO;
close foo;
end;

Basically, take your table, move to the $forward record in it,
grab the next $retrieve records and then release the table.

Your first time through, $forward might just equal 0...but when
you run it the second time through, you pass it back a $forward value
equal to $forward + $retrieve, so that you start at where you ended the
first time. This is how I deal with one of my projects, where I have to
do exactly what you are looking for...

About the only part of the SELECT above is the ORDER BY, and alot
of that is as much a restriction on your hardware then anything...the
major performance boost in an ORDER BY is memory..keeping it off the hard
drive.

> Important : I'm not looking for a "pure SQL" solution. I'm writing a C
> emulation library, so if it can be achieved via a call to a C Postgres
> function, it would be great.

You'd be better off looking at something like GDBM (which, by the
way, also creates a lock against updates while another is reading the
database)...unless I'm missing something, you aren't looking at doing
anything that *requires* an SQL engine :(

Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org