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 d9692197
Prev Next
Show latest version
1 file
+ 5
− 5
Compare changes
  • Side-by-side
  • Inline
  • d9692197
    Krzysztof Leśniak
    Left joins on following columns · d9692197
    Krzysztof Leśniak authored 
    The issue was that resets were not effective and were not deleting rows.
This is because following is set to NULL in the incoming data.
So, when the query was joining against accounts table, the resets rows
were skipped, because there was no matching account.
To fix this the JOIN on the following column needs to be LEFT JOIN.
hive/db/sql_scripts/follow_ops.sql
+ 5
− 5
@@ -278,7 +278,7 @@ AS $BODY$
FROM UNNEST(_follow_updates) AS upd
CROSS JOIN LATERAL UNNEST(upd.changes) AS ch(follower, following, block_num)
JOIN accounts_id AS r ON ch.follower = r.name
JOIN accounts_id AS g ON ch.following = g.name
LEFT JOIN accounts_id AS g ON ch.following = g.name
GROUP BY upd.id, upd.mode
ORDER BY upd.id
) AS upd_with_ids
@@ -305,7 +305,7 @@ AS $BODY$
FROM UNNEST(_muted_updates) AS upd
CROSS JOIN LATERAL UNNEST(upd.changes) AS ch(follower, following, block_num)
JOIN accounts_id AS r ON ch.follower = r.name
JOIN accounts_id AS g ON ch.following = g.name
LEFT JOIN accounts_id AS g ON ch.following = g.name
GROUP BY upd.id, upd.mode
ORDER BY upd.id
) AS upd_with_ids
@@ -332,7 +332,7 @@ AS $BODY$
FROM UNNEST(_blacklisted_updates) AS upd
CROSS JOIN LATERAL UNNEST(upd.changes) AS ch(follower, following, block_num)
JOIN accounts_id AS r ON ch.follower = r.name
JOIN accounts_id AS g ON ch.following = g.name
LEFT JOIN accounts_id AS g ON ch.following = g.name
GROUP BY upd.id, upd.mode
ORDER BY upd.id
) AS upd_with_ids
@@ -359,7 +359,7 @@ AS $BODY$
FROM UNNEST(_follow_muted_updates) AS upd
CROSS JOIN LATERAL UNNEST(upd.changes) AS ch(follower, following, block_num)
JOIN accounts_id AS r ON ch.follower = r.name
JOIN accounts_id AS g ON ch.following = g.name
LEFT JOIN accounts_id AS g ON ch.following = g.name
GROUP BY upd.id, upd.mode
ORDER BY upd.id
) AS upd_with_ids
@@ -386,7 +386,7 @@ AS $BODY$
FROM UNNEST(_follow_blacklisted_updates) AS upd
CROSS JOIN LATERAL UNNEST(upd.changes) AS ch(follower, following, block_num)
JOIN accounts_id AS r ON ch.follower = r.name
JOIN accounts_id AS g ON ch.following = g.name
LEFT JOIN accounts_id AS g ON ch.following = g.name
GROUP BY upd.id, upd.mode
ORDER BY upd.id
) AS upd_with_ids