Re: Sorting by related tables

Bill Moseley schrob:

> On Sat, Aug 13, 2005 at 06:44:09PM +0200, Andreas Seltenreich wrote:
>> > 3) Oh, and I have also this for checking IF there are items in
>> > "region" that are "above" the item in question -- to see IF an item
>> > can or cannot be moved up in the sort order relative to others.
>> >
>> > SELECT id FROM __TABLE__
>> > WHERE
>> > sort_order <= (SELECT sort_order FROM __TABLE__ WHERE id = ?)
>> > AND id != ?;
>> >
>> > If that returns any rows then I know I can call the UPDATE to move the
>> > item up.
>>
>> I guess you want a boolean value here? SELECT EXISTS around your above
>> query as a subselect should do the trick. You also want to use LIMIT 1
>> in the statement, to avoid fetching unnecessary records.
>
> Is there much of a difference between using LIMIT 1 and using an
> EXISTS subselect?

LIMIT 1 does reduce the cost, EXISTS AFAIK only makes the result
boolean and doesn't stop the execution of the subselect by itself when
the first record is found.

> Frankly, I'm not clear what you are specifically
> suggestion with EXISTS. I'm using Perl's Class::DBI object mapping module so
> returning a single row is an easy way to check this as a boolean
> result in Perl.

Uups, this wasn't question number three yet, and I wrongly inferred
from your uppercase-ifs that you wanted a boolean result here :-/

>> > Again, a very basic question: What method should be used to be sure
>> > that nothing changes between the SELECT and the UPDATE?
>>
>> You can achieve that using transactions. Concurrency control is
>> explained here: <http://www.postgresql.org/docs/8.0/static/mvcc.html>.
>
> My comment was that I want to do the above SELECT and then *only* do
> an UPDATE if the SELECT returns at least one row.
>
> So, I should do:
>
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>
> Before the SELECT. And when I UPDATE I need to be prepared to do a
> ROLLBACK if I get an error and repeat the process. (And, I assume,
> take some precaution to give up after some number of tries.)
>
> Does that seem reasonable?

This would be one possibility. If you don't want your application to
deal with transactions being aborted because of non-serializable
transactions, you could alternatively use explicit locking (SELECT ...
FOR UPDATE) combined with the Read Committed isolation level (the
default).

Explicit locking is documented here:
<http://www.postgresql.org/docs/8.0/static/explicit-locking.html#LOCKING-ROWS>

regards
Andreas
--