[Please don't top post as it makes the discussion more difficult to
follow.]
On Aug 7, 2007, at 9:05 , Kenji Morishige wrote:
On Tue, Aug 07, 2007 at 12:23:00PM +0200, Nis Jørgensen wrote:
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;
I don't believe this is concurrency safe, even if wrapped in a
transaction. Two concurrent transactions could end up calculating the
same next co_id. This could be caught by having appropriate
constraints on checked_out and retrying on error.
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.
I agree that you should probably tweak the schema a bit. Also, as you
want to reuse your checkout ids, you're actually considering them a
separate resoure, so you might consider putting them in a separate
table. Here's what I came up with:
As an aside, I wouldn't call them checkout_ids (even though I
did :)), as id often connotes a unique identifier something (like
your uids), and you're reusing them. I might call them
checkout_reference or checkout_number or something.
CREATE TABLE checkout_ids
(
checkout_id INTEGER PRIMARY KEY
, is_checked_out BOOLEAN NOT NULL
DEFAULT FALSE
, UNIQUE (checkout_id, is_checked_out)
);
-- populate the table with the values you'll use
INSERT INTO checkout_ids (checkout_id)
SELECT generate_series(1,999999);
CREATE TABLE checkouts
(
checkout_id INTEGER PRIMARY KEY
, is_checked_out BOOLEAN NOT NULL
CHECK (is_checked_out)
DEFAULT TRUE
, FOREIGN KEY (checkout_id, is_checked_out)
REFERENCES checkout_ids (checkout_id, is_checked_out)
, uid INTEGER NOT NULL -- with some fk
);
-- Of course, you can add the checkout start and end dates/timestamps
to this table:
-- they're independent of managing the checkout_id resource
-- I've added is_checked_out to this table to ensure that all
checkouts (checkout_id) have
-- is_checked_out set to true (via the CHECK constraint). This could
also be done with a
-- trigger.
-- And a couple quick functions to handle the process of checking in
and checking out.
-- The SELECT ... FOR UPDATE in checkout should ensure that
concurrent transactions
-- aren't grabbing the same checkout_id.
CREATE FUNCTION checkout (p_uid INTEGER)
RETURNS INTEGER -- checkout_id
LANGUAGE plpgsql AS $body$
DECLARE
v_checkout_id INTEGER;
BEGIN
SELECT INTO v_checkout_id
checkout_id
FROM checkout_ids
WHERE NOT is_checked_out
LIMIT 1
FOR UPDATE;
UPDATE checkout_ids
SET is_checked_out = TRUE
WHERE checkout_id = v_checkout_id;
INSERT INTO checkouts (checkout_id, uid)
VALUES (v_checkout_id, p_uid);
RETURN v_checkout_id;
END;
$body$;
CREATE FUNCTION checkin (p_checkout_id INTEGER)
RETURNS VOID
LANGUAGE plpgsql AS $body$
BEGIN
DELETE FROM checkouts
WHERE checkout_id = p_checkout_id;
UPDATE checkout_ids
SET is_checked_out = FALSE
WHERE checkout_id = p_checkout_id;
RETURN;
END;
$body$;
Hope this helps.
Michael Glaesemann
grzm seespotcode net
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
