Test workload for PostgreSQL MultiXact: create new mxact and select older

mxact_create_and_select.sql

CREATE OR REPLACE FUNCTION mxact_create_and_select(SCALE integer) RETURNS void AS $body$
declare
  default_rows_per_scale integer := 100000;
  gap integer := 3000;
  range integer := 100;
  create_base_id integer;
  read_base_id integer;
  segment_id integer;
  insert_timsetamp timestamp;
  read_timestamp timestamp;
begin
  -- Generate an ID for the multixact creation workload to start at
  select (trunc(extract(millisecond from current_timestamp) * default_rows_per_scale, 0) % (100000 * scale)) into create_base_id;

  -- Generate an ID in the past (based on milliseconds) for the multixact read workload to start at
  select (trunc(extract(millisecond from (current_timestamp -  justify_interval(random() * (interval '1 millisecond')))) * default_rows_per_scale, 0) % (100000 * scale)) into read_base_id;

  -- Generate a random number to use as a segment to offset concurrent connections from hitting the same exact range of ids
 select floor(random() * 10 + 1)::integer into segment_id;

  -- Execute the SQL that will generate the multixact
  perform * from pgbench_accounts where aid > (create_base_id + (segment_id * gap)) and aid < ((create_base_id + (segment_id * gap)) + range) for share;

  -- Read in rows with previously generated multixacts
  perform * from pgbench_accounts where aid > (read_base_id + (segment_id * gap)) and aid < ((read_base_id + (segment_id * gap)) + range);
end;
$body$ LANGUAGE PLPGSQL VOLATILE;