Table Lock issue

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

maybe my mind is stuck, but here's something strange.
This is the classic "counter" thing, where you can't / won't use sequences.
Basically I need to assemble an identifier like

AAA-000012

where AAA- is the prefix and the number is sequencially counted up.

The table is

CREATE TABLE ib_counter (
name character varying(64) NOT NULL,
prefix character varying(64) NOT NULL,
last_value integer NOT NULL,
display_length integer DEFAULT 0,
renewal_prefix character varying(64),
renewal_extension boolean,
display_initially boolean,
renewal_start integer
);

I use a stored proc to get the next identifier:

CREATE OR REPLACE FUNCTION ib_nextval (character varying) RETURNS character
varying
AS 'DECLARE
countername ALIAS FOR $1;
cprefix varchar;
counter integer;
dlen integer;
complete varchar;
format varchar;

BEGIN
LOCK TABLE ib_counter IN ACCESS EXCLUSIVE MODE;
UPDATE ib_counter SET last_value=last_value+1 WHERE name=countername;
SELECT INTO cprefix,counter,dlen prefix,last_value,display_length FROM
ib_counter WHERE name=countername;
format := ''FM'';
FOR i IN 1..dlen LOOP
format := format || ''0'';
END LOOP;
complete := cprefix || to_char(counter,format);
RAISE NOTICE ''result is %,%,%,%'',complete,cprefix,counter,dlen;
RETURN complete;
END;

Here the point. I can create duplicate identifiers. The stored proc is called
within a transaction like (pseudocode)

Begin
value=ib_nextval('mycounter')
do something with value
commit

My assumption would be that if I do an exclusive lock on the table I can't do
the update or a second exclusive lock, so the stored proc should block (or
fail).
Obviously it doesn't work that way, since as said I get duplicates.

Any ideas anyone ?

Thanks

UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAftXwjqGXBvRToM4RAiZEAKDRlceKo84vzQZ82iT4R45+gYPamgCfbQYT
9cqaTBxsn1aiPni9+X4j1MM=
=2tXJ
-----END PGP SIGNATURE-----