Sequence question

[Eric E]

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

[Tino Wildenhain]

Hi,

On Tue, 2004-10-19 at 01:16, Eric E wrote:

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.
I would recheck this requirement. What should actually be achieved
with the check for no holes in the numbering?
Remember you can always enumerate using a set returning function
or by means of a temporary sequence for a query.
So my question is:
what's the most effective way to get the next available number?
There is none.
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.

You cannot eat the cake and keep it - either you have holes
or you have transaction security or you have bad performance
by locking the whole table on insert.

Regards
Tino

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Eric E]

Hi Tino,
Many thanks for helping me.

I know that the sequence issue is a troubling one for many on the list.
Perhaps if I explain the need for a continuous sequence I can circumvent
some of that:

This database is for a laboratory, and the numbers in sequence
determine storage locations for a sample. Having a physical space in
our storage boxes tells us something has happened - the sample was used
up, broken, in use, etc - and account for that missing sample. If the
generated sequence has holes in it, we cannot tell if a sample is
properly not in the rack, or if that hole was simply generated by the
database. Allowing empties would also fill up limited box space with
spaces generated by the database.
If anyone has a brilliant idea for how a non-continuous sequence could
address the needs, I'd be delighted to hear it, but short of that I
think I have to keep this requirement.

One thought I had, and I'd love to hear what people think of this, is to
build a table of storage location numbers that are available for use.
That way the search for new numbers could be pushed off until some
convenient moment well after the user requests them.

Thanks again for any ideas.

Cheers,

Eric

Tino Wildenhain wrote:

Hi,

On Tue, 2004-10-19 at 01:16, Eric E wrote:

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.

I would recheck this requirement. What should actually be achieved
with the check for no holes in the numbering?
Remember you can always enumerate using a set returning function
or by means of a temporary sequence for a query.

So my question is:
what's the most effective way to get the next available number?

There is none.

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.

You cannot eat the cake and keep it - either you have holes
or you have transaction security or you have bad performance
by locking the whole table on insert.

Regards
Tino

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Andrew Sullivan]

On Wed, Oct 20, 2004 at 01:52:59PM -0400, Eric E wrote:

One thought I had, and I'd love to hear what people think of this, is to
build a table of storage location numbers that are available for use.
That way the search for new numbers could be pushed off until some
convenient moment well after the user requests them.

That very application is how I heard the idea for the "second method"
I sent in another email. In the case I was thinking of, someone used
it for room allocation. It worked pretty well, as long as you can
tolerate occasional periods where you _do_ have gaps.

A

--
Andrew Sullivan | aj*@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tino Wildenhain]

Hi,

Am Mi, den 20.10.2004 schrieb Eric E um 19:52:

Hi Tino,
Many thanks for helping me.

I know that the sequence issue is a troubling one for many on the list.
Perhaps if I explain the need for a continuous sequence I can circumvent
some of that:

This database is for a laboratory, and the numbers in sequence
determine storage locations for a sample. Having a physical space in
our storage boxes tells us something has happened - the sample was used
up, broken, in use, etc - and account for that missing sample. If the
generated sequence has holes in it, we cannot tell if a sample is
properly not in the rack, or if that hole was simply generated by the
database. Allowing empties would also fill up limited box space with
spaces generated by the database.
If anyone has a brilliant idea for how a non-continuous sequence could
address the needs, I'd be delighted to hear it, but short of that I
think I have to keep this requirement.

Maybe you skip the sequence thingy alltogether in this case and
use an approach like this:

initialize a table with all possible locations and mark them
as empty.

CREATE TABLE locations (location_id int2,taken bool);

(you might want to have a timestamp for changes too)

Whenever you change state of a location, do it like this
(perhaps in a function)

SELECT INTO loc_id location_id FROM locations WHERE taken
FOR UPDATE;
IF FOUND THEN
UPDATE location SET taken=true WHERE location_id=loc_id;
ELSE
RAISE EXCEPTION 'no free location anymore';

....

AND the other way round for freeing a location.
The SELECT ... FOR UPDATE should lock the candidate
position in the table so concurrent
transactions have to wait then then find another
free cell when they wake up.

Advantage: not a full table scan. Only the first
matching row should be used and locked.

Not this is only a rough sketch and you should
look for the actual syntax and more flesh for
the function.

Regards
Tino
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Eric E]

Hmm.... that's a really intesting idea, Tino. Since we're probably
talking about 1000000 numbers max, a query on this table would work
fairly fast, and operationally simple. I'll think about that.

Thanks,

Eric
Tino Wildenhain wrote:

Hi,

Am Mi, den 20.10.2004 schrieb Eric E um 19:52:

Hi Tino,
Many thanks for helping me.

I know that the sequence issue is a troubling one for many on the list.
Perhaps if I explain the need for a continuous sequence I can circumvent
some of that:

This database is for a laboratory, and the numbers in sequence
determine storage locations for a sample. Having a physical space in
our storage boxes tells us something has happened - the sample was used
up, broken, in use, etc - and account for that missing sample. If the
generated sequence has holes in it, we cannot tell if a sample is
properly not in the rack, or if that hole was simply generated by the
database. Allowing empties would also fill up limited box space with
spaces generated by the database.
If anyone has a brilliant idea for how a non-continuous sequence could
address the needs, I'd be delighted to hear it, but short of that I
think I have to keep this requirement.

Maybe you skip the sequence thingy alltogether in this case and
use an approach like this:

initialize a table with all possible locations and mark them
as empty.

CREATE TABLE locations (location_id int2,taken bool);

(you might want to have a timestamp for changes too)

Whenever you change state of a location, do it like this
(perhaps in a function)

SELECT INTO loc_id location_id FROM locations WHERE taken
FOR UPDATE;
IF FOUND THEN
UPDATE location SET taken=true WHERE location_id=loc_id;
ELSE
RAISE EXCEPTION 'no free location anymore';

...

AND the other way round for freeing a location.
The SELECT ... FOR UPDATE should lock the candidate
position in the table so concurrent
transactions have to wait then then find another
free cell when they wake up.

Advantage: not a full table scan. Only the first
matching row should be used and locked.

Not this is only a rough sketch and you should
look for the actual syntax and more flesh for
the function.

Regards
Tino

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster