From: Eric E <whalesuit@bonbon.net>
To: pgsql-general@postgresql.org
Subject: Sequence question
Date: Mon, 18 Oct 2004 19:16:46 -0400
Message-ID: <3sydnUgMwqQ80-ncRVn-uA@speakeasy.net> (raw)
Hi,
I have a question about sequences. I need a field to have values with
no holes in the sequence. However, the values do not need to be in order.
My users will draw a number or numbers from the sequence and write to
the field. Sometimes, however, these sequence numbers will be discarded
(after a transaction is complete), and thus available for use. During
the transaction, however, any drawn numbers need to be unavailable.
I would like the next user who draws a number to draw the lowest number
she can, starting with the holes in the sequence.
This continuous sequence is absolutely required by our company, as the
fact that the sequence has no holes is used to check for much more
serious problems.
So my question is:
what's the most effective way to get the next available number?
My present method is to do a query that finds the first and last number
in each of the holes, step through those holes, and then start
generating new numbers. Unfortunately, this involves doing a table scan
each time - before I generate the number, and does not produce the
transaction-safety I want.
Does anyone have any better ideas? Places I should look?
Thanks,
Eric
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: whalesuit@bonbon.net
Subject: Re: Sequence question
In-Reply-To: <3sydnUgMwqQ80-ncRVn-uA@speakeasy.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox