Hi,
I've hit a little problem and was wondering if anyone might be able to
give some help.
Set-up:
- JBoss appserver using entity beans to access database
- Using sequence pattern to update primary keys for several tables.
Basically it is a method of getting primary keys without hitting the
database. It actually hits the database every 10th go and updates the
counter by 10 in the database.
(now the bold bit)
- We have a stored procedure that actually updates one of the sequences
as well. It increments one at a time using this code:
SELECT INTO NewPK pkIndex FROM PrimaryKeyGenerator WHERE Name =
SequenceName FOR UPDATE;
UPDATE PrimaryKeyGenerator SET pkIndex = NewPK + 1 WHERE Name =
SequenceName;
I believe the "FOR UPDATE" won't actually do any good inside a plpgsql
call. Am I right?
Problem:
I have just called this stored procedure from outside the system using
10 threads and have got some errors due to duplicate entries on the
unique index. If it was only the stored procedures using this pk
generator then I could use a postgresql sequence but it isn't. If the
entity beans were to use the sequence, they'd have to make a database
call every time.
Any thoughts?
I'm thinking I may need to switch to using a sequence because the entity
beans don't actually update this particular table very often but would
prefer not to for portability reasons.
Thanks,
Tim