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

  Hi again,

   It seems unfortunate, but if I can't efficiently (i.e. no copying of
whole table, no blocking and using index)  browse the table, I might not
be able to "sell" postgres into the commercial environment. :-( RAIMA
Velocis might win :-(

  Please please help me solve this or make workarounds or anything. I
would *really* like to see PosgreSQL to be playing against the Big
(commercial) Boys !

  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.

  Below is more description and questions.

On Thu, 22 Jan 1998, Dustin Sallings wrote:

> >    Well, first let the user look up the manufacturer's part, and then let
> > him "browse" from there one (look at the next / previous ones), where
> > "next" is defined as the one closest bigger than the current one (based on
> > a sort attribute(s) on which I have an index).
>
>     That's easy in a normal application type place where you keep state, if
> you're doing it as a web based application, it's a different story...  Assuming
> you're writing a standalone application, you just keep a cursor to what you're
> looking at, and move it back and forth as needed, otherwise, otherwise, you're
> going to have to do the whole query again AFAIK.

  a few questions :

   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.

   1. I assume I have to say "declare cursor mycursor for SELECT
field1,field2 FROM mytable ORDER BY one_of_fields_on_which_I_have_index"
    Am I right ? Or can I do it some other way ?

   2. Will not the step (1.) *copy* the whole 40MB table ? (that would
trash the system if several people at once want to "browse" through the
table.)

   3. Will not the step (1.) read-lock *lock* the whole table ? (i.e.
while somebody is "browsing", I need other people to be able to update the
database).

   4. Will the step (1.) cause Postgres to use index for moving forth /
back ? Because if it doesn't, it might take ages to either declare the
cursor or to move forth / back.

   5. If the step (1.) will not cause Postgres to use index, what will ?

   6. How do I do it if I don't want to start "browsing" from the first
row ?

  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