Re: finding reusable ids - Mailing list pgsql-general

Actually, I already have a resource table that stores the uid of the item in
question.  The checkout table does double duty as a history mechanism and a
check-out mechanism.  I think you are on the right track, I should seperate
these two tasks and possibly create another table.  The actual design is a
bit more complicated as we actually don't have a a checked-in flag, but a
start and finish time where users can actually store multiple overlapping
records.

Kenji

On Tue, Aug 07, 2007 at 12:23:00PM +0200, Nis Jørgensen wrote:
> 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@postgresql.org so that your
>        message can get through to the mailing list cleanly