Search Postgresql Archives

Re: finding reusable ids

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Kenji Morishige skrev:
> I have a table that creates "check-out" records that stores information when
> a particular resource is being utilized.  I want to maintain a friendly
> shortened ID so people can reference these check outs.
> 
> At any given time, there should not be more than 999999 or so check-outs, so
> as the check-outs get checked in, the old IDs would become available.  What
> is the best method to query for these resusable IDs that would not be
> assigned to checked out items?  It seems that it would be quite inefficient
> to look at the entire table to see which ids exist, then increment
> accordingly.  For some reason, I feel that there would be something already
> available to solve this.
> 
> example set:
> 
> uid  co-id  checked-in?
> 1    1      n
> 2    2      n
> 3    3      y
> 4    4      n
> 5    3      n   
> 
> obviously, this is a small sample set, but the id 3 can be reused, so I'd
> like to reuse it without using a external tracking mechansm.  My table has
> 1,000,000+ records.

Do you need the co-id once the item is checked in? If not, I would split
this into two tables:

resources
uid <more data>
1
2
3
4
5

checked_out
uid co_id
1   1
2   2
4   4
5   3

Where the existence of the row in the second table doubles as the
checked-in flag.

This doesn't solve your problem, but might simplify the query to find a
new id - something like this (untested):

SELECT min(q.co_id) +1
FROM (
SELECT (co_id + 1) as co_id FROM checked_out
EXCEPT
SELECT co_id FROM checked_out
) q;

(you need a special case when the table is empty)

The same method can of course be used with your original table layout.

Nis


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux