Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2S6BBw72724; Wed, 28 Mar 2001 01:11:11 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f2S6B8w17073; Wed, 28 Mar 2001 01:11:08 -0500 (EST) To: Forest Wilkinson cc: pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org Subject: Re: possible row locking bug in 7.0.3 & 7.1 In-reply-to: <01032722080000.08138@bartok> References: <01032713101600.07241@bartok> <15828.985734842@sss.pgh.pa.us> <01032722080000.08138@bartok> Comments: In-reply-to Forest Wilkinson message dated "Tue, 27 Mar 2001 22:08:00 -0800" Date: Wed, 28 Mar 2001 01:11:08 -0500 Message-ID: <17070.985759868@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200103/570 X-Sequence-Number: 2240 Forest Wilkinson writes: > If I remember correctly, UPDATE establishes a lock on the affected rows, > which will block another UPDATE on the same rows for the duration of the > transaction. If that's true, shouldn't I be able to achieve my desired > behavior by removing the initial as follows: > create function nextid( varchar(32)) returns int8 as ' > update idseq set id = id + 1 where name = $1::text; > select id from idseq where name = $1::text; > ' language 'sql'; > Or, would I still have to add FOR UPDATE to that final SELECT? You're right, the initial SELECT FOR UPDATE is a waste of cycles considering that you're not using the value it returns. But you'll still need the last select to be FOR UPDATE so that it plays by the same rules as the UPDATE does. regards, tom lane