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