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

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@hub.org           secondary: scrappy@{freebsd|postgresql}.org