Skip to content

Instantly share code, notes, and snippets.

@onderkalaci
Created July 3, 2020 08:58
Show Gist options
  • Save onderkalaci/2a6c2a32c2381d17f9606c049e066fcd to your computer and use it in GitHub Desktop.
Save onderkalaci/2a6c2a32c2381d17f9606c049e066fcd to your computer and use it in GitHub Desktop.
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment