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

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.

>  Most of us here have
> access to an one or the other (me, Oracle) to use as a sample system...if
> we can prove that it does work on another system, then we have something
> to work with

> 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 ?

> >   What the whole problem really reduces to, is to be able to get the next
> > / previous value in an index. If I can do that, I win. No SELECT (and thus
> > no locking) and no huge copying !). It seems to me like something
> > primitive, so it should be easy. But it isn't obvious to me. Please help.
>
>     If there is no SELECT, how do you get data out of an SQL database?
> *raised eyebrow*

   Sorry, I meant no SELECT that couln't be done through index and
so wouldn't copy huge amounts of data into a result table. And wouldn't
lock the whole table.

> >    0. having a value of a field on which there is an index, how can I do :
> >        a) current_pointer = some_function("value_I_have");
> >        b) next_pointer    = some_other_function(current_pointer);
> >        c) one_tupple      = yet_another_function(next_pointer);
> >    If I can accomplish a,b,c, then I win and I don't have to do questions
> >    1..5 below.
>
>     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.

  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.

 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")

  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.

   Thanx,

       Jan


 -- Gospel of Jesus is the saving power of God for all who believe --
Jan Vicherek ## To some, nothing is impossible. ##  www.ied.com/~honza
    >>>    Free Software Union President  ...  www.fslu.org    <<<
Interactive Electronic Design Inc.    -#-    PGP: finger honza@ied.com