Received: from linux.sinyih.com.tw (mail.sinyih.com.tw [203.66.130.1]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eA13HDl85832 for ; Tue, 31 Oct 2000 22:17:19 -0500 (EST) (envelope-from cn@mail.sinyih.com.tw) Received: from mail.sinyih.com.tw (cn [203.66.130.25]) by linux.sinyih.com.tw (8.10.1/8.10.1) with ESMTP id eA1BL9w23313 for ; Wed, 1 Nov 2000 19:21:18 +0800 Message-ID: <39FF8B60.12A6F545@mail.sinyih.com.tw> Date: Wed, 01 Nov 2000 11:17:52 +0800 From: cn Organization: Sinyih Ceramic Co., Ltd X-Mailer: Mozilla 4.74 [en] (Win95; U) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: How To Increment A Field Value By Function Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200010/31 X-Sequence-Number: 31 Hello! I have read pgsql online docs but still need helps. (And pardon me for cross posting to pgsql-novice as I have not received responses to my problem.) I want to increment the value of field SocialSecurityNo in a centrally controlled table SocialSecurityTable. SocialSecurityTable contains only one row and one field - SocialSecurityNo. Because, I think, SocialSecurityNo in SocialSecurityTable should not even be read by any other user when it is being incremented by a user (or the number will duplicate), and setting transaction isolation level seems to be not restrictive enough in this case, I use table lock as follows: CREATE FUNCTION GetAndSetNextNumber () RETURNS TEXT AS ' DECLARE UsedNumber TEXT; BEGIN BEGIN WORK; LOCK SocialSecurityTable IN ROW EXCLUSIVE MODE; SELECT SocialSecurityNo INTO UsedNumber FROM SocialSecurityTable; --Do a lot of calculation on UsedNumber -- and generate the next free SocialSecurityNo -- and assign this free value to UsedNumber. UPDATE SocialSecurityTable SET SocialSecurityNo=UsedNumber; COMMIT WORK; RETURN UsedNumber; END;' LANGUAGE 'plpgsql'; Question A: Is this above function apporpriately designed, or are there more efficient ways than table locking? Question B: I think statement "LOCK SocialSecurityTable IN ROW EXCLUSIVE MODE;" will fail when this user (user A) executed this statement AFTER another user (user B) and user B has not yet COMMITed his transaction? What are the solutions for this (LOCK TABLE fails in function)? Regards, CN