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

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

Actually, here i believe you are wrong. Bruce, please correct me
if I'm wrong, but it would be faster for you to do the
begin;declare...;move...;fetch...;end; then doing a straight SELECT.

I'm not *certain* about this, but the way I believe that it works
is that if you do:

begin;
declare cursor mycursor for select * from table order by field;
move forward 20;
fetch 20;
end;

The SELECT/ORDER BY is done in the backend, as is the MOVE/FETCH
before returning any data to the front end. So, now you are returning
let's say 100 records to the front end, instead of the whole table. If
you do a SELECT, it will return *all* the records to the front end.

So, I would imagine that it would be slightly longer to SELECT all
records and send them all to the front end then it would be to SELECT all
records and just return the 100 that you want.

Bruce, is this a correct assessment?

Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org