From aa70325c11d004c13d73046e11de17066c281206 Mon Sep 17 00:00:00 2001 From: phiresky <phireskyde+git@gmail.com> Date: Fri, 7 Jul 2023 11:27:47 +0200 Subject: [PATCH] Make hot rank not crash on future (#3517) * make hot rank zero for future * parallel safe --- .../2023-07-06-151124_hot-rank-future/down.sql | 11 +++++++++++ .../2023-07-06-151124_hot-rank-future/up.sql | 16 ++++++++++++++++ 2 files changed, 27 insertions(+) create mode 100644 migrations/2023-07-06-151124_hot-rank-future/down.sql create mode 100644 migrations/2023-07-06-151124_hot-rank-future/up.sql diff --git a/migrations/2023-07-06-151124_hot-rank-future/down.sql b/migrations/2023-07-06-151124_hot-rank-future/down.sql new file mode 100644 index 00000000..b8f9a63f --- /dev/null +++ b/migrations/2023-07-06-151124_hot-rank-future/down.sql @@ -0,0 +1,11 @@ +CREATE OR REPLACE FUNCTION hot_rank(score numeric, published timestamp without time zone) + RETURNS integer + AS $$ +BEGIN + -- hours_diff:=EXTRACT(EPOCH FROM (timezone('utc',now()) - published))/3600 + RETURN floor(10000 * log(greatest(1, score + 3)) / power(((EXTRACT(EPOCH FROM(timezone('utc', now()) - published)) / 3600) + 2), 1.8))::integer; +END; +$$ +LANGUAGE plpgsql +IMMUTABLE; + diff --git a/migrations/2023-07-06-151124_hot-rank-future/up.sql b/migrations/2023-07-06-151124_hot-rank-future/up.sql new file mode 100644 index 00000000..41baa673 --- /dev/null +++ b/migrations/2023-07-06-151124_hot-rank-future/up.sql @@ -0,0 +1,16 @@ +CREATE OR REPLACE FUNCTION hot_rank(score numeric, published timestamp without time zone) + RETURNS integer + AS $$ +DECLARE + hours_diff numeric := EXTRACT(EPOCH FROM (timezone('utc', now()) - published)) / 3600; +BEGIN + IF (hours_diff > 0) THEN + RETURN floor(10000 * log(greatest(1, score + 3)) / power((hours_diff + 2), 1.8))::integer; + ELSE + RETURN 0; + END IF; +END; +$$ +LANGUAGE plpgsql +IMMUTABLE PARALLEL SAFE; + -- 2.44.1