-------------------------------------------------------------------------------- -- Copyright Richard Emberson -- Use but remember me (copyright must appear in all copies). -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -- group limits table -- keeps the available ranges of group bounds -- starts from 0 to 9223372036854775807 -- hold negative in reserve -------------------------------------------------------------------------------- DROP INDEX group_limits_lower_bound_index; DROP INDEX group_limits_upper_bound_index; DROP TABLE group_limits; CREATE TABLE group_limits ( lower_bound BIGINT NOT NULL, size BIGINT NOT NULL, upper_bound BIGINT NOT NULL ); CREATE INDEX group_limits_lower_bound_index ON group_limits (lower_bound); CREATE INDEX group_limits_upper_bound_index ON group_limits (upper_bound); -------------------------------------------------------------------------------- -- load initial values INSERT INTO group_limits ( lower_bound, size, upper_bound ) VALUES ( 0, 9223372036854775807, 9223372036854775807 ); -------------------------------------------------------------------------------- /* Get a group range of the size specified by the input parameter. This gets from the group_limits table the range from the row that has the smallest range greater than size. If it is equals to the size, the row is deleted. If it is greater than the size, the row's range is adjusted. input: size return: if > 0 then it is the lower_bound of the segment -1100011 - no more segments; select group_limits_get(10); select * from group_limits; */ CREATE OR REPLACE FUNCTION group_limits_get(BIGINT) RETURNS BIGINT AS ' DECLARE -- parameters size_p ALIAS FOR $1; -- variables lb_v BIGINT; ub_v BIGINT; size_v BIGINT; row_count_v INTEGER; BEGIN SELECT gl1.lower_bound, gl1.size, gl1.upper_bound INTO lb_v, size_v, ub_v FROM group_limits gl1 WHERE gl1.size IN (SELECT MIN(gl2.size) FROM group_limits gl2 WHERE gl2.size >= size_p); IF size_v IS NULL THEN -- some sort of fatal error return -1100011; ELSIF size_v = size_p THEN -- exact match so remove the whole row DELETE FROM group_limits WHERE lower_bound = lb_v; ELSE -- its bigger than needed so modify row UPDATE group_limits SET lower_bound = (lb_v+size_p), size = (size_v-size_p) WHERE lower_bound = lb_v; END IF; GET DIAGNOSTICS row_count_v = ROW_COUNT; IF row_count_v <> 1 THEN RETURN -row_count_v; END IF; RETURN lb_v; END; ' LANGUAGE 'plpgsql'; /* Return a group range. If a row exists such that the lb_p+size_p == lower_bound, then modify the row. Also then check if there is another row where its lower_bound+size == lb_p+size_p. If so, then coalesce the two rows. If no row exists such that lb_p+size_p == lower_bound, then add a new row. input: lower_bound size return: 1 - no higher, no lower, insert new row 2 - coalesce with lower 3 - coalesce with higher 4 - coalesce with both lower and higher -1100012 - lower_bound < lb_p AND upper_bound > lb_p; -1100013 -- lower_bound < (lb_p+size_p) AND upper_bound > (lb_p+size_p); \i group_limits.sql select * from group_limits; tests select group_limits_get(10); select group_limits_return(0,10); 0-100 select group_limits_get(10); select group_limits_get(10); select group_limits_return(0,10); 0-10, 10,100 select group_limits_return(10,10); 0-100 select group_limits_get(10); select group_limits_get(10); select group_limits_return(10,10); 10-100 select group_limits_return(0,10); 0-100 select group_limits_return(0,10); select group_limits_return(10,10); select group_limits_return(20,10); */ CREATE OR REPLACE FUNCTION group_limits_return(BIGINT, BIGINT) RETURNS BIGINT AS ' DECLARE -- parameters lb_p ALIAS FOR $1; size_p ALIAS FOR $2; -- variables lb_h_v BIGINT; size_h_v BIGINT; lb_l_v BIGINT; size_l_v BIGINT; row_count_v INTEGER; BEGIN -- These two check are just in case someone at a high application level -- tries to do something bad. It would be a real screw up if bad -- data was passed in. -- check: is lb_p between an existing rows lower and upper values SELECT count(*) INTO row_count_v FROM group_limits WHERE lower_bound < lb_p AND upper_bound > lb_p; IF row_count_v <> 0 THEN RETURN -1100012; END IF; -- check: is lb_p+size_p between an existing rows lower and upper values SELECT count(*) INTO row_count_v FROM group_limits WHERE lower_bound < (lb_p+size_p) AND upper_bound > (lb_p+size_p); IF row_count_v <> 0 THEN RETURN -1100013; END IF; -- look for the higher row SELECT lower_bound, size INTO lb_h_v, size_h_v FROM group_limits WHERE lower_bound = (lb_p+size_p); -- look for the lower row SELECT lower_bound, size INTO lb_l_v, size_l_v FROM group_limits WHERE upper_bound = lb_p; IF lb_h_v IS NULL THEN -- no higher row, so see if there is a lower row IF lb_l_v IS NULL THEN -- no lower row, so just add a new row INSERT INTO group_limits ( lower_bound, size, upper_bound ) VALUES ( lb_p, size_p, size_p+lb_p ); RETURN 1; ELSE -- lower row, so coalesce -- lower_bound remains the same -- size += size_p -- upper_bound += size_p UPDATE group_limits SET size = (size_l_v+size_p), upper_bound = (lb_p+size_p) WHERE upper_bound = lb_p; RETURN 2; END IF; ELSE -- higher row IF lb_l_v IS NULL THEN -- no lower row, so coalesce with higher -- lower_bound = lb_p -- size += size_p UPDATE group_limits SET lower_bound = lb_p, size = (size_h_v+size_p) WHERE lower_bound = (lb_p+size_p); RETURN 3; ELSE -- lower row, coalesce with both lower and higher -- delete higher row (must delete one of them) DELETE FROM group_limits WHERE lower_bound = (lb_p+size_p); -- lower_bound = lb_l_v -- size += size_p+size_h_v -- upper_bound = rb_h_v = lb_h_v+size_h_v UPDATE group_limits SET size = (size_h_v+size_l_v+size_p), upper_bound = (lb_h_v+size_h_v) WHERE lower_bound = lb_l_v; RETURN 4; END IF; END IF; /* GET DIAGNOSTICS row_count_v = ROW_COUNT; IF row_count_v <> 1 THEN RETURN -row_count_v; END IF; RETURN lb_v; */ END; ' LANGUAGE 'plpgsql';