Re: Browsing the tables / why pgsql does not perform well (with temp fix)
От | Jan Vicherek |
---|---|
Тема | Re: Browsing the tables / why pgsql does not perform well (with temp fix) |
Дата | |
Msg-id | Pine.LNX.3.96.980125000511.497i-100000@ann.ied.com обсуждение исходный текст |
Ответ на | Re: Browsing the tables / why pgsql does not perform well (with temp fix) (The Hermit Hacker <scrappy@hub.org>) |
Список | pgsql-hackers |
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@ied.com
В списке pgsql-hackers по дате отправления:
Предыдущее
От: The Hermit HackerДата:
Сообщение: Re: [HACKERS] Re: Copyright question: GPL patches for non-GPL packages (fwd)