Search Postgresql Archives

Table Lock issue

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux