I cannot get locking to operate as documented and as I understand it to work. I have created a test block of code below that should allow multiple processes to execute the "TestInsert()" concurrently. However, I get an error "cannot insert duplicate key". My source code follows....
/* create the test table */
DROP TABLE Test;
CREATE TABLE Test ( CONSTRAINT Test_Id PRIMARY KEY (Id), Id int8 NOT NULL );
/* insert test record with unique Id value */
DROP FUNCTION TestInsert();
CREATE FUNCTION TestInsert() RETURNS int8
AS '
DECLARE
newid int8;
BEGIN
LOCK TABLE Test IN EXCLUSIVE MODE;
SELECT INTO newid Id FROM Test ORDER BY Id DESC FOR UPDATE OF Test LIMIT 1;
IF NOT FOUND THEN newid=1; ELSE newid=newid+1; END IF;
INSERT INTO Test (Id) VALUES (newid);
RETURN 1;
END; '
LANGUAGE 'plpgsql';
/* call TestInsert() */
/* This function should be able to operate concurrently BUT CANNOT */
BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT TestInsert(); END;
Thanks for any help,
Jeff
Jeff Martin
704 Torrey Lane, Apartment D
Boalsburg, PA 16827
H814-466-7791
jeff@dgjc.org
www.dgjc.org