-- M7.3 fenced region ownership (ADR-0060). Owner-only definitions; the runtime -- role receives schema USAGE and EXECUTE on the guarded functions, never tables. -- Every mutation locks the region lease row first and decides expiry with the -- database clock read after that lock. Clients never supply timestamps. SELECT pg_catalog.set_config('evolution.runtime_role', :'runtime_role', true); DO $$ DECLARE runtime name := pg_catalog.current_setting('evolution.runtime_role'); BEGIN IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_roles r WHERE r.rolname = runtime AND NOT r.rolsuper AND NOT r.rolcreaterole AND NOT r.rolcreatedb AND NOT r.rolreplication AND NOT r.rolbypassrls) OR runtime = current_user OR pg_catalog.pg_has_role(runtime, current_user, 'MEMBER') THEN RAISE EXCEPTION 'runtime role must exist, be unprivileged and not inherit the owner'; END IF; END $$; CREATE TABLE evolution.region_heads ( world_id evolution.u64 NOT NULL, region_id evolution.u64 NOT NULL, commit_sequence evolution.u64 NOT NULL, commit_id uuid NOT NULL, digest bytea NOT NULL CHECK (octet_length(digest) = 32), end_tick evolution.u64 NOT NULL, published_epoch evolution.u64 NOT NULL, PRIMARY KEY (world_id, region_id), FOREIGN KEY (world_id, region_id) REFERENCES evolution.region_leases, -- Genesis is sequence 0, tick 0, provisioned outside any lease epoch. CHECK ((commit_sequence = 0) = (end_tick = 0)), CHECK ((commit_sequence = 0) = (published_epoch = 0)) ); REVOKE ALL ON evolution.region_heads FROM PUBLIC; -- Owner-only provisioning; recovery and runtime can never create genesis. CREATE FUNCTION evolution.provision_region(p_world evolution.u64, p_region evolution.u64, p_frame evolution.u64, p_genesis_commit uuid, p_genesis_digest bytea) RETURNS void LANGUAGE sql SECURITY INVOKER SET search_path = pg_catalog, pg_temp AS $$ INSERT INTO evolution.region_leases(world_id, region_id, frame_id) VALUES (p_world, p_region, p_frame); INSERT INTO evolution.region_heads(world_id, region_id, commit_sequence, commit_id, digest, end_tick, published_epoch) VALUES (p_world, p_region, 0, p_genesis_commit, p_genesis_digest, 0, 0); $$; CREATE FUNCTION evolution.acquire_region_lease(p_world evolution.u64, p_region evolution.u64, p_duration_ms integer) RETURNS TABLE (lease_holder uuid, lease_epoch evolution.u64, region_frame evolution.u64, granted_ms integer) LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$ DECLARE l evolution.region_leases%ROWTYPE; t timestamptz; BEGIN IF p_world IS NULL OR p_region IS NULL OR p_duration_ms IS NULL OR p_duration_ms NOT BETWEEN 1000 AND 60000 THEN RAISE EXCEPTION 'invalid lease request' USING ERRCODE = '22023'; END IF; SELECT * INTO l FROM evolution.region_leases r WHERE r.world_id = p_world AND r.region_id = p_region FOR UPDATE; IF NOT FOUND THEN RAISE EXCEPTION 'region not provisioned' USING ERRCODE = 'EVL04'; END IF; t := clock_timestamp(); IF l.holder IS NOT NULL AND l.expires_at > t THEN RAISE EXCEPTION 'region lease held' USING ERRCODE = 'EVL01'; END IF; IF l.epoch = 18446744073709551615 THEN RAISE EXCEPTION 'lease epochs exhausted' USING ERRCODE = 'EVL05'; END IF; UPDATE evolution.region_leases r SET epoch = l.epoch + 1, holder = gen_random_uuid(), expires_at = t + p_duration_ms * interval '1 millisecond' WHERE r.world_id = p_world AND r.region_id = p_region RETURNING r.holder, r.epoch, r.frame_id INTO lease_holder, lease_epoch, region_frame; granted_ms := p_duration_ms; RETURN NEXT; END $$; -- Locks the lease row and requires the exact unexpired holder/epoch. Internal: -- callable only by the owner and the SECURITY DEFINER functions below. CREATE FUNCTION evolution.check_region_lease(p_world evolution.u64, p_region evolution.u64, p_holder uuid, p_epoch evolution.u64) RETURNS timestamptz LANGUAGE plpgsql SECURITY INVOKER SET search_path = pg_catalog, pg_temp AS $$ DECLARE l evolution.region_leases%ROWTYPE; t timestamptz; BEGIN IF p_world IS NULL OR p_region IS NULL OR p_holder IS NULL OR p_epoch IS NULL THEN RAISE EXCEPTION 'invalid lease request' USING ERRCODE = '22023'; END IF; SELECT * INTO l FROM evolution.region_leases r WHERE r.world_id = p_world AND r.region_id = p_region FOR UPDATE; IF NOT FOUND THEN RAISE EXCEPTION 'region not provisioned' USING ERRCODE = 'EVL04'; END IF; -- Read after the lock wait: a queued stale writer sees current database time. t := clock_timestamp(); IF l.holder IS DISTINCT FROM p_holder OR l.epoch <> p_epoch OR l.expires_at <= t THEN RAISE EXCEPTION 'region lease fenced' USING ERRCODE = 'EVL02'; END IF; RETURN t; END $$; CREATE FUNCTION evolution.renew_region_lease(p_world evolution.u64, p_region evolution.u64, p_holder uuid, p_epoch evolution.u64, p_duration_ms integer) RETURNS integer LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$ DECLARE t timestamptz; BEGIN IF p_duration_ms IS NULL OR p_duration_ms NOT BETWEEN 1000 AND 60000 THEN RAISE EXCEPTION 'invalid lease request' USING ERRCODE = '22023'; END IF; -- Renewal never revives an expired or superseded lease. t := evolution.check_region_lease(p_world, p_region, p_holder, p_epoch); UPDATE evolution.region_leases r SET expires_at = t + p_duration_ms * interval '1 millisecond' WHERE r.world_id = p_world AND r.region_id = p_region; RETURN p_duration_ms; END $$; CREATE FUNCTION evolution.release_region_lease(p_world evolution.u64, p_region evolution.u64, p_holder uuid, p_epoch evolution.u64) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$ BEGIN PERFORM evolution.check_region_lease(p_world, p_region, p_holder, p_epoch); -- The epoch is retained so the next acquisition can never reuse it. UPDATE evolution.region_leases r SET holder = NULL, expires_at = NULL WHERE r.world_id = p_world AND r.region_id = p_region; END $$; CREATE FUNCTION evolution.load_region_head(p_world evolution.u64, p_region evolution.u64) RETURNS TABLE (region_frame evolution.u64, head_sequence evolution.u64, head_commit uuid, head_digest text, head_tick evolution.u64) LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$ BEGIN RETURN QUERY SELECT l.frame_id, h.commit_sequence, h.commit_id, encode(h.digest, 'hex'), h.end_tick FROM evolution.region_heads h JOIN evolution.region_leases l USING (world_id, region_id) WHERE h.world_id = p_world AND h.region_id = p_region; IF NOT FOUND THEN RAISE EXCEPTION 'region not provisioned' USING ERRCODE = 'EVL04'; END IF; END $$; -- The fenced transition kernel. M7.4/M7.5 publication wraps it with manifest, -- decision and outbox rows in the same transaction; the lease lock is taken first. CREATE FUNCTION evolution.advance_region_head(p_world evolution.u64, p_region evolution.u64, p_holder uuid, p_epoch evolution.u64, p_expected_sequence evolution.u64, p_expected_commit uuid, p_expected_digest bytea, p_commit uuid, p_digest bytea, p_end_tick evolution.u64) RETURNS TABLE (region_frame evolution.u64, head_sequence evolution.u64, head_commit uuid, head_digest text, head_tick evolution.u64) LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$ DECLARE h evolution.region_heads%ROWTYPE; BEGIN IF p_expected_sequence IS NULL OR p_expected_commit IS NULL OR p_commit IS NULL OR p_end_tick IS NULL OR p_commit = p_expected_commit OR octet_length(p_expected_digest) IS DISTINCT FROM 32 OR octet_length(p_digest) IS DISTINCT FROM 32 THEN RAISE EXCEPTION 'invalid head advance' USING ERRCODE = '22023'; END IF; PERFORM evolution.check_region_lease(p_world, p_region, p_holder, p_epoch); SELECT * INTO h FROM evolution.region_heads r WHERE r.world_id = p_world AND r.region_id = p_region FOR UPDATE; IF NOT FOUND THEN RAISE EXCEPTION 'region not provisioned' USING ERRCODE = 'EVL04'; END IF; IF h.commit_sequence <> p_expected_sequence OR h.commit_id <> p_expected_commit OR h.digest <> p_expected_digest THEN RAISE EXCEPTION 'region head moved' USING ERRCODE = 'EVL03'; END IF; IF h.commit_sequence = 18446744073709551615 THEN RAISE EXCEPTION 'commit sequences exhausted' USING ERRCODE = 'EVL05'; END IF; IF p_end_tick <= h.end_tick OR p_end_tick - h.end_tick > 64 THEN RAISE EXCEPTION 'invalid head advance' USING ERRCODE = '22023'; END IF; UPDATE evolution.region_heads r SET commit_sequence = h.commit_sequence + 1, commit_id = p_commit, digest = p_digest, end_tick = p_end_tick, published_epoch = p_epoch WHERE r.world_id = p_world AND r.region_id = p_region; RETURN QUERY SELECT l.frame_id, n.commit_sequence, n.commit_id, encode(n.digest, 'hex'), n.end_tick FROM evolution.region_heads n JOIN evolution.region_leases l USING (world_id, region_id) WHERE n.world_id = p_world AND n.region_id = p_region; END $$; REVOKE ALL ON FUNCTION evolution.provision_region(evolution.u64, evolution.u64, evolution.u64, uuid, bytea), evolution.acquire_region_lease(evolution.u64, evolution.u64, integer), evolution.check_region_lease(evolution.u64, evolution.u64, uuid, evolution.u64), evolution.renew_region_lease(evolution.u64, evolution.u64, uuid, evolution.u64, integer), evolution.release_region_lease(evolution.u64, evolution.u64, uuid, evolution.u64), evolution.load_region_head(evolution.u64, evolution.u64), evolution.advance_region_head(evolution.u64, evolution.u64, uuid, evolution.u64, evolution.u64, uuid, bytea, uuid, bytea, evolution.u64) FROM PUBLIC; GRANT USAGE ON SCHEMA evolution TO :"runtime_role"; GRANT EXECUTE ON FUNCTION evolution.acquire_region_lease(evolution.u64, evolution.u64, integer), evolution.renew_region_lease(evolution.u64, evolution.u64, uuid, evolution.u64, integer), evolution.release_region_lease(evolution.u64, evolution.u64, uuid, evolution.u64), evolution.load_region_head(evolution.u64, evolution.u64), evolution.advance_region_head(evolution.u64, evolution.u64, uuid, evolution.u64, evolution.u64, uuid, bytea, uuid, bytea, evolution.u64) TO :"runtime_role"; INSERT INTO evolution.schema_migrations(version, sha256) VALUES (2, :'migration_sha_2');