Re: Browsing the tables / why pgsql does not perform well (with temp fix)

On Sat, 24 Jan 1998, The Hermit Hacker wrote:

> On Sat, 24 Jan 1998, Jan Vicherek wrote:
>
> > On Sat, 24 Jan 1998, Hannu Krosing wrote:
> >
> > > > if aggregates were able to use indexes you could do:
> > > >
> > > > select min(n) from table where rowid >n;
> > > >
> > > > and then
> > > >
> > > > select * from table where n = n_found by_last_previous_select;
> > > >
> > > > but as they don't you would get very poor performance from the first
> > > > select;
> >
> >   This looks good. No transactions necessary, no locking, no mutliple rows
> > copying.
>
>     The SELECT above will create a READ lock on the table, preventing
> UPDATES from happening for the duration of the SELECT.  There is *no* way
> of getting around or away from this lock...

   Yes, you are correct.
 In addition, there will be no long-lasting "begin - declare cursor -
end" statement, so the table will not get locked against updates for
minutes / hours when a person wants to "browse" the table ...

  .. hmm , but there still may be copying of mutliple rows if the "rowid"
field is not unique.

   Again, accessing the "next" item in the index is the only solution. The
next item in index has a tid which points to next row in the browsed
table.

       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