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 -- ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
