1. SQL / Говнокод #27148

    0

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    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-х.

    Запостил: gost, 04 Декабря 2020

    Комментарии (8) RSS

    • ava gost 04.12.2020 09:44 # 0

      Денормализация заебала. Просто взяла, блядь, — и заебала!
      Ответить

    • ava defecate-plusplus 04.12.2020 09:59 # 0

      в триггере ты обращаешься к 2 таблицам, а кто третья? для которой after update for each row собсно
      Ответить

      • ava gost 04.12.2020 10:09 # 0

        Только две есть: в 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). Что с ними делать — хуй знает, пока думаем.
        Ответить

        • ava defecate-plusplus 04.12.2020 10:18 # 0

          ну хз
          1) кто-то другой вставит в comments значение last_revision_id, если это первая ревизия?
          2) джонни с comment_revisions можно избежать, если в comment хранить время фетчя комента
          3) если у тебя бывают групповые операции вставки (узнал о новых 50 - заинсертил все одним стейтментом), то логичнее всего переделать на тригр for each statement
          Ответить

          • ava gost 04.12.2020 10:29 # 0

            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) Думал об этом, но решил не ебаться лишний раз. Схема всё равно делается для ГК, а у нас тут больше двух-трёх комментов за период проверки всё равно практически никогда не набегает, обычно один ровно.
            Ответить

            • ava gost 04.12.2020 10:32 # 0

              В «Питоне», соответственно, тестовый код выглядит просто (хотя вот несоответствие порядка создания комментария и ревизии в коде и в запросах раздражает):
              comment = Comment(id_ru=2222, post_id=post.id, user_id=user.id, posted=datetime.datetime.now())
rev = CommentRevision(id=CommentRevision.get_new_revision_id(), comment_id=comment.id_ru, text='Ololo', source=Source.RU, fetch_time=datetime.datetime.now())
sess.add(rev)
sess.add(comment)
sess.commit()
              Ответить

            • ava guest3 05.12.2020 19:47 # 0

              Подрочи, кися.
              Ответить

    Добавить комментарий