-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi.
I have the following little stored proc:
CREATE OR REPLACE FUNCTION public.ib_nextval(varchar)
RETURNS varchar 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;
SELECT INTO cprefix,counter,dlen prefix,last_value,display_length FROM
ib_counter WHERE name=countername;
counter := counter + 1;
UPDATE ib_counter SET last_value=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;
It's basically a counter incremental thing that is independant from any serial
value, but it behaves like a serial.So everytime the func is called, it
increments a counter and returns the new "key".
This works nice and throws no errors, however the line
LOCK TABLE ib_counter IN ACCESS EXCLUSIVE MODE;
seems to be ignored, since it's possible to create the same counter twice when
the func is run twice at virtually the same time.
To my understanding the access exclusive mode should lock the table for read
access also, so it should be impossible to get the same result twice. (btw
the result looks like ABC-0000123)
Why doesn't this lock the table for read ?
Thx
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.1 (GNU/Linux)
iD8DBQE/svHPjqGXBvRToM4RApv/AJ9BrDgWVYmFahr0dUJ1kxbJpbjzkQCgvhfW
9sv+WWSlOuf8+FZA/F9nD/c=
=Cl1k
-----END PGP SIGNATURE-----
