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@xxxxxxxx ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
