coordinator_evaluation.sql

-- when we have INSERT INTO dist SELECT stable_func() from dist; the function is evaluated on the coordinator
-- for  INSERT INTO local SELECT stable_function from dist; the function is evaluated on the workers.
-- it seems acceptable to me, check with the team



CREATE OR REPLACE FUNCTION evaluate_on_master()
RETURNS int LANGUAGE plpgsql STABLE
AS $function$
BEGIN
  RAISE NOTICE 'evaluating on master';
  RETURN 0;
END;          
$function$;



CREATE TABLE raw_events_first (user_id int, time timestamp, value_1 int, value_2 int, value_3 float, value_4 bigint, UNIQUE(user_id, value_1));
SELECT create_distributed_table('raw_events_first', 'user_id');
 
INSERT INTO raw_events_first (user_id, time, value_1, value_2, value_3, value_4) VALUES
                         (1, now(), 10, 100, 1000.1, 10000);
INSERT INTO raw_events_first (user_id, time, value_1, value_2, value_3, value_4) VALUES
                         (2, now(), 20, 200, 2000.1, 20000);
INSERT INTO raw_events_first (user_id, time, value_1, value_2, value_3, value_4) VALUES
                         (3, now(), 30, 300, 3000.1, 30000);
INSERT INTO raw_events_first (user_id, time, value_1, value_2, value_3, value_4) VALUES
                         (4, now(), 40, 400, 4000.1, 40000);
INSERT INTO raw_events_first (user_id, time, value_1, value_2, value_3, value_4) VALUES
                         (5, now(), 50, 500, 5000.1, 50000);
INSERT INTO raw_events_first (user_id, time, value_1, value_2, value_3, value_4) VALUES
                         (6, now(), 60, 600, 6000.1, 60000);


 CREATE TABLE raw_events_second (user_id int, time timestamp, value_1 int, value_2 int, value_3 float, value_4 bigint, UNIQUE(user_id, value_1));


INSERT INTO raw_events_second (user_id, value_1)
SELECT
  user_id, random()
FROM
  raw_events_first
RETURNING *;

ERROR:  function public.evaluate_on_master() does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
CONTEXT:  while executing command on localhost:9700


SELECT create_distributed_table('raw_events_second', 'user_id');


INSERT INTO raw_events_second (user_id, value_1)
SELECT
    user_id, evaluate_on_master()
FROM
    raw_events_first
WHERE
    user_id < 0;
NOTICE:  evaluating on master
INSERT 0 0