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(at)ied(dot)com