On Tue, Feb 2, 2016 at 4:28 AM, alexander <aleksiyan...@gmail.com> wrote:
> Hello > > I've met exactly the same problem as described here > http://www.postgresql.org/message-id/95862fdc-eb2e-4533-8331-d49775b0e...@f2g2000yqf.googlegroups.com > . For now, I use the same solution that was presented in the response > http://www.postgresql.org/message-id/077da5f9-f783-4388-bf19-42e582dc8...@yahoo.com > . > > 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.
