pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: cn <cn@mail.sinyih.com.tw>
To: pgsql-sql@postgresql.org
Subject: How To Increment A Field Value By Function
Date: Wed, 01 Nov 2000 11:17:52 +0800
Message-ID: <39FF8B60.12A6F545@mail.sinyih.com.tw> (raw)

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



Message-ID: <39FF8B60.12A6F545@mail.sinyih.com.tw>
Permalink:  ../39FF8B60.12A6F545@mail.sinyih.com.tw/
Also on:    postgresql.org/message-id/39FF8B60.12A6F545@mail.sinyih.com.tw

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: cn@mail.sinyih.com.tw
  Subject: Re: How To Increment A Field Value By Function
  In-Reply-To: <39FF8B60.12A6F545@mail.sinyih.com.tw>
  References: <39FF8B60.12A6F545@mail.sinyih.com.tw>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox