Table Lock issue - Mailing list pgsql-general

-----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-----