I am using postgresql database. And previous tables which are created by my seniors and they are not used sequence concept, they added data manually. Now i need to insert data with my java application. for that i need last inserted ID. I have no permission to add sequence.
please anybody help me.
thanks in advance...
-
2Any solution that does not use sequences is either not scalable or not safe when multiple-transactions are runninguser330315– user3303152011-05-09 12:02:27 +00:00Commented May 9, 2011 at 12:02
-
3Look for a new job when you can. "Senior" database people should know better.Andrew Lazarus– Andrew Lazarus2011-05-09 21:03:00 +00:00Commented May 9, 2011 at 21:03
-
@Andrew Lazarus, "senior" means previous developer. Not Admin, and etc..Vissu– Vissu2011-05-10 10:41:42 +00:00Commented May 10, 2011 at 10:41
Add a comment
|
1 Answer
You'd ideally ask for permission to create the needed sequences.
If it's not an option for some bureaucratic reason you might manage to work around concurrency issues using advisory locks. Pseudo-code:
loop
select id as last_id, pg_try_advisory_lock('yourtable'::regclass, id) as locked
from yourtable
order by id desc limit 1
if not locked then sleep .01 else exit end if
end loop
new_id = last_id + 1
insert...
select pg_advisory_unlock('yourtable'::regclass, last_id)
answered May 9, 2011 at 12:00
Denis de Bernardy
79.1k14 gold badges138 silver badges158 bronze badges
4 Comments
Scott Marlowe
I'd stick to actual locks on the whole table. Without sequences it's the only way to be sure you get it right. But yeah, if the idiot bosses won't let the OP add sequences they need to be replaced with house plants, as at least a house plant makes oxygen and does something useful
Denis de Bernardy
@peufeu: I'm not so sure... The advisory lock should ensure that he's always dealing with the last id, since no two transactions can acquire it at the same time for the same row, and the sleep should ensure that concurrent transactions will find a new last_id if it had to try again. That said, the keywords here are might and should, not will. The question's author really ought to get the sequence created. ;-)
bobflux
Nono, it isn't your solution that's insane, it's his boss !
Scott Marlowe
The problem with advisory locks I worry about is race condition and error handling. If they aren't up to snuff then you get dup ids. Locking the table seems extreme, but it only happens for a split second and then it's gone. As long as there are no long running selects for it to get stuck behind you're ok. And let's face it, the guys who said "no sequences" don't deserve the amount of code, troubleshooting, and QA to make sure something fancier than table locks work. They painted themselves in the corner...