Re: Sorting by related tables

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

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

--
Bill Moseley
moseley(at)hank(dot)org