Нашли или выдавили из себя код, который нельзя назвать нормальным,
на который без улыбки не взглянешь?
Не торопитесь его удалять или рефакторить, — запостите его на
говнокод.ру, посмеёмся вместе!
CREATE OR REPLACE FUNCTION update_last_comment_ids_on_rev_update() RETURNS trigger AS $$
BEGIN
UPDATE comments
SET last_revision_id = NEW.id
FROM comment_revisions
WHERE comments.id_ru = NEW.comment_id
AND comment_revisions.id = comments.last_revision_id
AND comment_revisions.fetch_time < NEW.fetch_time;
IF (NEW.source = 'XYZ') THEN
UPDATE comments
SET last_xyz_revision_id = NEW.id
FROM comment_revisions
WHERE comments.id_ru = NEW.comment_id
AND comment_revisions.id = comments.last_xyz_revision_id AND comment_revisions.fetch_time < NEW.fetch_time;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
Извини, defecate-plusplus, снова я на тебя ору из 1970-х.
Только две есть: в comments лежит last_revision_id, указывающий на comment_revisions, при добавлении (или обновления времени — на всякий случай) новой ревизии вызывается этот триггер и обновляет соответствующий комментарий.
CREATE TRIGGER last_comment_ids_update
AFTER INSERT OR UPDATE OF fetch_time ON comment_revisions
FOR EACH ROW EXECUTE FUNCTION update_last_comment_ids_on_rev_update();
Основная проблема сейчас — в возможности существования ревизий-сирот, а именно комментариев гуеста с «Хуза», соответствующие комментарии на ру для которых ещё не загружены (собственно, из-за этого нам в своё время пришлось вводить то самое commentIdStorage). Что с ними делать — хуй знает, пока думаем.
ну хз
1) кто-то другой вставит в comments значение last_revision_id, если это первая ревизия?
2) джонни с comment_revisions можно избежать, если в comment хранить время фетчя комента
3) если у тебя бывают групповые операции вставки (узнал о новых 50 - заинсертил все одним стейтментом), то логичнее всего переделать на тригр for each statement
1) Подразумевается, что last_revision_id вообще никогда вручную не указывается: для добавления совсем нового комментария есть триггер before insert on comments, который автоматически вычисляет самые последние ревизии:
CREATE OR REPLACE FUNCTION update_last_comment_ids_on_comment_insert() RETURNS trigger AS $$
BEGIN
NEW.last_revision_id :=
(SELECT id FROM comment_revisions WHERE comment_id = NEW.id_ru ORDER BY fetch_time DESC LIMIT 1);
NEW.last_xyz_revision_id :=
(SELECT id FROM comment_revisions WHERE comment_id = NEW.id_ru AND source = 'XYZ' ORDER BY fetch_time DESC LIMIT 1);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
То есть мы в одной транзакции добавляем ревизий, потом суём туда коммент и течём (комментов без ревизий существовать не может). В принципе, для оптимизации можно в этом триггере сделать WHEN last_revision_id IS NULL, но пока похуй.
2) Да, как вореант, но что-то не хочется ещё сильнее это говно денормализовывать. В текущем вореанте оно и так заебись работает (с учётом того, что добавления довольно редки):
Update on comments (cost=0.30..16.41 rows=1 width=72) (actual time=0.003..0.003 rows=0 loops=1)
-> Nested Loop (cost=0.30..16.41 rows=1 width=72) (actual time=0.003..0.003 rows=0 loops=1)
-> Index Scan using ix_comments_id_ru on comments (cost=0.15..8.17 rows=1 width=66) (actual time=0.003..0.003 rows=0 loops=1)
Index Cond: (id_ru = 3333)
-> Index Scan using idx_comment_revisions_id_fetch_time on comment_revisions (cost=0.15..8.17 rows=1 width=22) (never executed)
Index Cond: ((id = comments.last_revision_id) AND (fetch_time < '2020-12-04 10:38:44.16195'::timestamp without time zone))
3) Думал об этом, но решил не ебаться лишний раз. Схема всё равно делается для ГК, а у нас тут больше двух-трёх комментов за период проверки всё равно практически никогда не набегает, обычно один ровно.
В «Питоне», соответственно, тестовый код выглядит просто (хотя вот несоответствие порядка создания комментария и ревизии в коде и в запросах раздражает):
Основная проблема сейчас — в возможности существования ревизий-сирот, а именно комментариев гуеста с «Хуза», соответствующие комментарии на ру для которых ещё не загружены (собственно, из-за этого нам в своё время пришлось вводить то самое commentIdStorage). Что с ними делать — хуй знает, пока думаем.
1) кто-то другой вставит в comments значение last_revision_id, если это первая ревизия?
2) джонни с comment_revisions можно избежать, если в comment хранить время фетчя комента
3) если у тебя бывают групповые операции вставки (узнал о новых 50 - заинсертил все одним стейтментом), то логичнее всего переделать на тригр for each statement
То есть мы в одной транзакции добавляем ревизий, потом суём туда коммент и течём (комментов без ревизий существовать не может). В принципе, для оптимизации можно в этом триггере сделать WHEN last_revision_id IS NULL, но пока похуй.
2) Да, как вореант, но что-то не хочется ещё сильнее это говно денормализовывать. В текущем вореанте оно и так заебись работает (с учётом того, что добавления довольно редки):
3) Думал об этом, но решил не ебаться лишний раз. Схема всё равно делается для ГК, а у нас тут больше двух-трёх комментов за период проверки всё равно практически никогда не набегает, обычно один ровно.