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

On Sun, 25 Jan 1998, The Hermit Hacker wrote:

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

If I was to select whole table at once, I would be wrong.
However, with this "browsing" business I would only do (if aggregates
used indeces) :

CurrentValue = NextValue;
SELECT min(IndexedField) as NextValue from MyTable where IndexedField > CurrentValue;
SELECT * from MyTable where IndexedField = NextValue;
<return * to user he he can look at it 5 hours>

Since the above locks the table only during the SELECTs (which are very
short [ < .5 seconds ]), other people can update the table while one users
stares at the results for 5 hours. The first SELECT is very short because
we assume that aggregates can use indeces. The second SELECT is very short
because we have "WHERE" which uses only a field on which we have an index.

After the 5 hours the user decides to see the next record, so we execute
those 2 SELECT statements again.

The only problem with the above is that if IndexedField isn't unique, the
SELECT will return multiple records instead of only one. (The user can
see only one record on the screan at a time.) So this would require the
code between the database and the application to handle multiple-row
results of the second SELECT statement. An unnecessary complication :

This situation would be solved if I could retrieve a (valid) record
based not on the (non-unique) IndexedField, but based on the "tid" of the
record. This cannot currently be done through SQL (as the FAQ says).

<joke_that_might_become_a_reality>
I'm tempted to implement an extension to the current postgres' SQL
language that would allow me to do :

CREATE INDEX MyIndex ON MyTable (IndexField);
SELECT FIRST(Tid) AS MyFirstTid FROM MyIndex WHERE IndexField = 'what_user_requests';
SELECT * from MyTable where Tid = MyFirstTid;
SELECT NEXT(Tid) AS MyNextTid FROM MyIndex WHERE Tid = MyFirstTid;

</joke_that_might_become_a_reality>

I'll ask on developers list whether this is technically possible, given
the access that the SQL processing routines have to the back-end index
processing routines.

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

the application would have to handle in this case multiple-row results
coming from the backend.

Thanx for all your intpt,

keep it coming,

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