72 lines
3.6 KiB
MySQL
72 lines
3.6 KiB
MySQL
|
-- Keep a slot's high watermark after its ScreenRow is deleted. Allocation takes
|
||
|
|
-- the counter row lock, so an INSERT waiting on another transaction cannot
|
||
|
|
-- later publish an epoch allocated before that transaction's newer assignment.
|
||
|
|
CREATE TABLE computer_display_epochs (
|
||
|
|
computer_id TEXT NOT NULL REFERENCES computers(id) ON DELETE CASCADE,
|
||
|
|
slot INTEGER NOT NULL CHECK (slot >= 0 AND slot < 8),
|
||
|
|
epoch BIGINT NOT NULL CHECK (epoch > 0),
|
||
|
|
PRIMARY KEY (computer_id, slot)
|
||
|
|
);
|
||
|
|
|
||
|
|
INSERT INTO computer_display_epochs(computer_id, slot, epoch)
|
||
|
|
SELECT computer_id, slot, GREATEST(execution_fence::BIGINT, 0) + 1
|
||
|
|
FROM computer_screens;
|
||
|
|
|
||
|
|
ALTER TABLE computer_screens ADD COLUMN display_epoch BIGINT;
|
||
|
|
UPDATE computer_screens s SET display_epoch=e.epoch
|
||
|
|
FROM computer_display_epochs e WHERE e.computer_id=s.computer_id AND e.slot=s.slot;
|
||
|
|
ALTER TABLE computer_screens ALTER COLUMN display_epoch SET NOT NULL;
|
||
|
|
ALTER TABLE computer_screens ADD CONSTRAINT positive_display_epoch CHECK (display_epoch > 0);
|
||
|
|
|
||
|
|
CREATE FUNCTION next_display_epoch(target_computer TEXT, target_slot INTEGER)
|
||
|
|
RETURNS BIGINT LANGUAGE SQL AS $$
|
||
|
|
INSERT INTO computer_display_epochs(computer_id,slot,epoch)
|
||
|
|
VALUES (target_computer,target_slot,1)
|
||
|
|
ON CONFLICT (computer_id,slot) DO UPDATE SET epoch=computer_display_epochs.epoch+1
|
||
|
|
RETURNING epoch
|
||
|
|
$$;
|
||
|
|
|
||
|
|
CREATE FUNCTION fence_screen_assignment() RETURNS TRIGGER LANGUAGE plpgsql AS $$
|
||
|
|
BEGIN
|
||
|
|
IF TG_OP='DELETE' THEN
|
||
|
|
-- A parent Computer deletion cascades here after the parent is gone.
|
||
|
|
IF EXISTS(SELECT 1 FROM computers WHERE id=OLD.computer_id) THEN
|
||
|
|
PERFORM next_display_epoch(OLD.computer_id,OLD.slot);
|
||
|
|
END IF;
|
||
|
|
RETURN OLD;
|
||
|
|
END IF;
|
||
|
|
IF TG_OP='INSERT' THEN
|
||
|
|
NEW.display_epoch := next_display_epoch(NEW.computer_id,NEW.slot);
|
||
|
|
ELSIF (NEW.computer_id,NEW.slot) IS DISTINCT FROM (OLD.computer_id,OLD.slot) THEN
|
||
|
|
-- Deterministic counter order for a move between slots/computers.
|
||
|
|
IF (OLD.computer_id,OLD.slot) < (NEW.computer_id,NEW.slot) THEN
|
||
|
|
PERFORM next_display_epoch(OLD.computer_id,OLD.slot);
|
||
|
|
NEW.display_epoch := next_display_epoch(NEW.computer_id,NEW.slot);
|
||
|
|
ELSE
|
||
|
|
NEW.display_epoch := next_display_epoch(NEW.computer_id,NEW.slot);
|
||
|
|
PERFORM next_display_epoch(OLD.computer_id,OLD.slot);
|
||
|
|
END IF;
|
||
|
|
ELSIF (NEW.id,NEW.bot_id,NEW.display,NEW.view_port,NEW.profile_mode,NEW.profile_path,
|
||
|
|
NEW.control_holder,NEW.control_lease_id,NEW.execution_run_id,NEW.execution_fence)
|
||
|
|
IS DISTINCT FROM
|
||
|
|
(OLD.id,OLD.bot_id,OLD.display,OLD.view_port,OLD.profile_mode,OLD.profile_path,
|
||
|
|
OLD.control_holder,OLD.control_lease_id,OLD.execution_run_id,OLD.execution_fence)
|
||
|
|
OR NEW.display_epoch IS DISTINCT FROM OLD.display_epoch
|
||
|
|
OR (OLD.control_lease_expires_at IS NOT NULL AND
|
||
|
|
(NEW.control_lease_expires_at IS NULL OR NEW.control_lease_expires_at < OLD.control_lease_expires_at))
|
||
|
|
OR (OLD.execution_lease_expires_at IS NOT NULL AND
|
||
|
|
(NEW.execution_lease_expires_at IS NULL OR NEW.execution_lease_expires_at < OLD.execution_lease_expires_at)) THEN
|
||
|
|
-- An explicit bump requests a fresh epoch; it cannot assign an old value.
|
||
|
|
NEW.display_epoch := next_display_epoch(NEW.computer_id,NEW.slot);
|
||
|
|
END IF;
|
||
|
|
RETURN NEW;
|
||
|
|
END
|
||
|
|
$$;
|
||
|
|
|
||
|
|
CREATE TRIGGER screen_assignment_epoch
|
||
|
|
BEFORE INSERT OR UPDATE ON computer_screens
|
||
|
|
FOR EACH ROW EXECUTE FUNCTION fence_screen_assignment();
|
||
|
|
CREATE TRIGGER deleted_screen_epoch
|
||
|
|
AFTER DELETE ON computer_screens
|
||
|
|
FOR EACH ROW EXECUTE FUNCTION fence_screen_assignment();
|