Skip to content
Snippets Groups Projects

Draft: refactor follows and update_nofications indexing

Open Draft: refactor follows and update_nofications indexing
267-update-notification-cache into develop
Open Dan Notestein requested to merge 267-update-notification-cache into develop
Viewing commit e26892d5
Prev Next
Show latest version
12 files
+ 28
− 73
Compare changes
  • Side-by-side
  • Inline
Files
12
  • e26892d5
    Krzysztof Leśniak
    Fix calculating rshares · e26892d5
    Krzysztof Leśniak authored 
    In previous commit vote notifications were changed to be inserted after
processing each block. But in massive sync rshares for posts were
calculated only at the end of massive sync.
This resulted in vote notifications to not be inserted, because score
were always calculated as 0.
To fix this, calculate rshares for modified posts after processing each
block.
`hive_posts` are now modified in posts and votes indexer. An advisory
lock was added to synchronise, because deadlock was detected by postgres
failing transactions.
hive/db/sql_scripts/update_posts_rshares.sql
+ 3
− 37
DROP FUNCTION IF EXISTS hivemind_app.update_posts_rshares;
CREATE OR REPLACE FUNCTION hivemind_app.update_posts_rshares(
_first_block hivemind_app.blocks_view.num%TYPE
, _last_block hivemind_app.blocks_view.num%TYPE
_post_ids INTEGER[]
)
RETURNS VOID
LANGUAGE 'plpgsql'
@@ -11,34 +10,7 @@ $BODY$
BEGIN
SET LOCAL work_mem='4GB';
SET LOCAL enable_seqscan = off;
IF (_last_block - _first_block) > 10000 THEN
WITH votes_rshares_view AS MATERIALIZED
(
SELECT
hv.post_id
, SUM( hv.rshares ) as rshares
, SUM( ABS( hv.rshares ) ) as abs_rshares
, SUM( CASE hv.is_effective WHEN True THEN 1 ELSE 0 END ) as total_votes
, SUM( CASE
WHEN hv.rshares > 0 THEN 1
WHEN hv.rshares = 0 THEN 0
ELSE -1
END ) as net_votes
FROM hivemind_app.hive_votes hv
GROUP BY hv.post_id
)
UPDATE hivemind_app.hive_posts hp
SET
abs_rshares = votes_rshares.abs_rshares
,vote_rshares = votes_rshares.rshares
,sc_hot = CASE hp.is_paidout OR hp.parent_id > 0 WHEN True Then 0 ELSE hivemind_app.calculate_hot( votes_rshares.rshares, hp.created_at) END
,sc_trend = CASE hp.is_paidout OR hp.parent_id > 0 WHEN True Then 0 ELSE hivemind_app.calculate_trending( votes_rshares.rshares, hp.created_at) END
,total_votes = votes_rshares.total_votes
,net_votes = votes_rshares.net_votes
FROM votes_rshares_view votes_rshares
WHERE hp.id = votes_rshares.post_id
AND hp.counter_deleted = 0;
ELSE
UPDATE hivemind_app.hive_posts hp
SET
abs_rshares = votes_rshares.abs_rshares
@@ -60,12 +32,7 @@ ELSE
ELSE -1
END ) as net_votes
FROM hivemind_app.hive_votes hv
WHERE EXISTS
(
SELECT NULL
FROM hivemind_app.hive_votes hv2
WHERE hv2.post_id = hv.post_id AND hv2.block_num BETWEEN _first_block AND _last_block
)
WHERE hv.post_id = ANY(_post_ids)
GROUP BY hv.post_id
) as votes_rshares
WHERE hp.id = votes_rshares.post_id
@@ -76,7 +43,6 @@ ELSE
OR hp.total_votes != votes_rshares.total_votes
OR hp.net_votes != votes_rshares.net_votes
);
END IF;
RESET enable_seqscan;
RESET work_mem;