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