Re: Multiple Sequence Number for One Column p.2

On Tue, Feb 2, 2016 at 4:28 AM, alexander <aleksiyantsa(at)gmail(dot)com> wrote:

> Hello
>
> I've met exactly the same problem as described here
> http://www.postgresql.org/message-id/95862fdc-eb2e-4533-8331-d49775b0ef8f@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-42E582DC89EE@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.

​