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

    • Денормализация заебала. Просто взяла, блядь, — и заебала!
      Ответить
    • в триггере ты обращаешься к 2 таблицам, а кто третья? для которой after update for each row собсно
      Ответить
      • Только две есть: в 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) Думал об этом, но решил не ебаться лишний раз. Схема всё равно делается для ГК, а у нас тут больше двух-трёх комментов за период проверки всё равно практически никогда не набегает, обычно один ровно.
            Ответить
            • В «Питоне», соответственно, тестовый код выглядит просто (хотя вот несоответствие порядка создания комментария и ревизии в коде и в запросах раздражает):
              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()
              Ответить
            • Подрочи, кися.
              Ответить

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