Hello
I've met exactly the same problem as described here http://www.postgresql.org/message-id/95862fdc-eb2e-4533-8331-d49775b0ef8f@xxxxxxxxxxxxxxxxxxxxxxxxxxx . For now, I use the same solution that was presented in the response http://www.postgresql.org/message-id/077DA5F9-F783-4388-BF19-42E582DC89EE@xxxxxxxxx .
In my case, I have a composite key composed of session ID and object ID. Session ID is a unique value. I'd like to have a separate auto incremental key for each session ID value. Therefore, I create a sequence for each session ID. The problem is that once the session has ended there won't be new values with its session ID, so, we have to clear obsolete sequences related to this ID from time to time.
It's said in the last message that there are other solutions. It would be helpful for me to know the others if they exist in terms of PostgreSQL.
"...so, we have to clear obsolete sequences..." - why?
Your problem statement is too vague but I suspect you already have a "session" table in your schema. You should have a column on that table named something like "next available object id" and just serialize read/write access to it. Depending on your needs (namely gap-less requirements) whatever front-end session management layer you have could acquire blocks of N integers and increment the value of "next...id" by 10 when it does so.
Honestly, the index for session+object is probably going to be fast enough that you just query it when you need a new id.
Sessions themselves are not usually highly concurrent with themselves...unless your architecture is novel in this respect you should be able write a function that you can install as a BEFORE INSERT trigger that will accomplish your goal without any serious performance hit. If that is not good enough I would move assignment to the application layer and provide a central location for the application to obtain the keys it needs for the sessions that are active.
David J.
