• MVCC-1. Изоляция
    0

    Ну, первый-то сценарий совсем клинический.
    А так мне кажется, что интерпретации зависят от того, считаем мы r и w одной SQL-операцией (UPDATE), или двумя (SELECT + UPDATE). Если одной, то второй сценарий в Постгресе вообще невозможен (транзакция 2 будет заблокирована до окончания 1). А если двумя, то увы, возможен, потому что в этом случае база ничего не знает о связи w с r. Речь про Read Committed, конечно.

  • MVCC-1. Изоляция
    0
    А в CTE, подозреваю, все блокировки будут браться сразу

    Нет, тоже постепенно.


    Вот кстати есть формальный способ записи истории транзакций буквами r1(x), w1(x), c1, a1

    Есть. Я его сознательно не стал тут приводить, чтобы не уводить разговор совсем в сторону теории. Почему? См. ниже.


    непонятно как записывать self-join, CTE, "set value = value + 1" с помощью такой нотации. Нет ли тут проблемы с формализацией?

    Есть такая проблема. Теория использует простую модель с элементарными операциями. Как эти операции сгруппированы по SQL-операторам, теорию не интересует.


    Вот давайте посмотрим на пример потерянного обновления, про который вы пишите ниже:


    r1(x) r2(x) w1(x) w2(x)

    Пара операций r1(x) w1(x) подразумевает, что вы читаете объект x (допустим, строку таблицы), а потом записываете что-то в этот же x. Что именно — теория никак не определяет.


    Если мы реализуем эти операции так, как в том примере (https://github.com/ept/hermitage/blob/master/postgres.md#lost-update-p4):


    begin; set transaction isolation level read committed; -- T1
    begin; set transaction isolation level read committed; -- T2
    select * from test where id = 1; -- T1
    select * from test where id = 1; -- T2
    update test set value = 11 where id = 1; -- T1
    update test set value = 11 where id = 1; -- T2, BLOCKS
    commit; -- T1. This unblocks T2, so T1's update is overwritten
    commit; -- T2

    то мне непонятно, что именно мы тут потеряли? Оба оператора UPDATE записывают в x фиксированное значение — их в принципе не интересует, что в x было до этого.


    Но если мы переделаем этот пример вот так:


    ...
    update test set value = value + 11 where id = 1; -- T1
    update test set value = value + 11 where id = 1; -- T2, BLOCKS
    ...

    то увидим, что потери не случится: второй оператор UPDATE перечитает строку после того, как разблокируется.


    Но вообще по-моему лучше по-другому интерпретировать эту запись. Я бы считал, что пара r1(x) w1(x) соответствует одному оператору


    update t set x = f(x);

    потому что такой оператор как раз сначала читает x, а потом записывает в него что-то на основе полученного значения.


    Но так или иначе — результат один. Если у нас Read Committed, то мы не теряем обновление, но (поскольку перечитываем только одну строку) можем получить несогласованное чтение. А если Repeatable Read (или Serializable), то транзакция просто обрывается во избежание проблем.


    Это два разных подхода.
    В случае Read Committed мы предпочитаем получить аномалию, и никогда не обрываем из-за этого транзакции. Задача обеспечения согласованности ложится на плечи разработчика приложения.
    В случае Serializable (и отчасть Repeatable Read) мы предотвращаем потенциальные проблемы тем, что обрываем транзакции, и приложение должно их просто повторять.


    Ну вот, много написал, но не уверен, что что-то прояснил (:

  • MVCC-1. Изоляция
    +1
    Получается, что глядя на бизнес-транзакцию и уровень изоляции не получится понять будет ли приложение работать корректно.

    Вне контекста конкретной СУБД — не получится. Ну, есть какие-то общие знаменатели, но в общем случае точно нет.


    Внутри СУБД нужно что-то заблокировать

    Если внутри CTE выполняются изменения, то конечно нужно. Но и если бы CTE выполнялся по шагам, тоже надо было бы блокировать. И в этом смысле разницы никакой нет, ведь блокировки снимаются только в конце транзакции (а не оператора).
    Блокировкам отдельный цикл посвящен, кстати.

  • MVCC-1. Изоляция
    +1

    Стандарт не слишком много чего определяет, поэтому ориентироваться всегда надо на особенности конкретной реализации. За MS SQL не скажу, а в Постгресе весь запрос целиком видит согласованные данные.


    то таким образом с помощью CTE можно хорошенько «просадить» конкурентность

    Не понимаю, почему? Запрос (если мы о SELECT говорим), выполняясь, никому не мешает.

  • MVCC-1. Изоляция
    +1

    Рад, что статья понравилась! Свет попробую пролить.


    какой уровень изоляции у одного «SQL-оператора»

    Уровень изоляции — это свойство транзакции, а не отдельных операторов. Можете считать, что все операторы одной транзакции имеют один и тот же уровень, установленный для этой транзакции.


    являются ли запросы CTE (а также подзапросы) отдельными операторами, в том числе рекурсивные

    Нет, это составные части одного и того же запроса.


    Да и что будет с банальным JOIN? Например, если мы соединяем тяжелую таблицу с собой по первичному ключу, а параллельно пролетает транзакция на обновление записи в этой таблице, может ли получится так, что значения в левой и правой части результата будут разными?

    Ни в коем случае. Это один оператор, он всегда* видит только согласованные данные (на момент начала оператора или на момент начала транзакции, смотря по уровню изоляции).


    * Ну, не совсем всегда. В статье я привожу два случая, когда в PostgreSQL внутри одного оператора можно увидеть несогласованные данные на уровне Read Committed:


    • если в запросе вызывается volatile-функция, содержащая другой запрос;
    • оператор UPDATE, который перечитывает только одну строку, а не весь набор.

    Никакие другие ситуации мне неизвестны.


    К моему удивлению, не удается найти какой-либо авторитетной информации по этим вопросам.

    Зачем вам авторитеты — проверьте сами. Вдруг и я вас обманываю? (:
    Вот скажем вас join интересует. Возьмите таблицу, соедините ее саму с собой, параллельно запустите обновление и сравните результат с известным правильным.

  • АМА с Хабром, #13: важные новости для пользователей и компаний
    0

    Ну, чтобы не сделать хуже, чем есть, можно всегда подключать «основные» языки плюс те, что используются в статье.

  • АМА с Хабром, #13: важные новости для пользователей и компаний
    +1

    Да я тоже не фронтендер, просто пока прикручивал подсветку для PostgreSQL, угораздило записаться в мейнтейнеры highlightjs — так что обращайтесь, если что (:


    А вообще мне вот как видится. Когда статья редактируется, можно подгружать просто все языки (автор как-нибудь переживет лишний мегабайт трафика). А когда статью сохраняют, список языков уже известен. Можно это знание сохранить в метаинформации о странице и потом для показа генерировать подключение только нужных языков (их можно по одному цеплять).
    Надо только не забыть про языки, внутри которых могут быть фрагменты на других языках, но и в этом случае набор ограничен.

  • АМА с Хабром, #13: важные новости для пользователей и компаний
    0

    Highlightjs поддерживает, так что не должно быть проблемой добавить. В библиотеке вообще много чего есть, но, возможно, на Хабре список языков ограничен, чтобы трафик экономить?
    Может надо что-то придумать, чтобы загружать только те языки, которые используются на странице? Если со стороны библиотеки для этого что-то нужно, можем попробовать сделать.

  • pg_stat_statements + pg_stat_activity + loq_query = pg_ash?
    0

    Там только с размером запроса может быть засада и возможно придется выкручивать track_activity_query_size.
    А дыры — ну дыры, можно ж и логи потерять с тем же успехом.

  • Блокировки в PostgreSQL: 3. Блокировки других объектов
    0

    К чему может приводить обнаружение взаимоблокировок при большом количестве сеансов: https://www.postgresql.org/message-id/flat/c9f840f4-b7fe-19c6-76e6-65c02a0c230c%40postgrespro.ru

  • pg_stat_statements + pg_stat_activity + loq_query = pg_ash?
    0

    Но планы вы смотрите только при выкачке очередного файла с сервера, то есть они несвежие получаются?
    Я понимаю, что все это не от хорошей жизни и было бы куда проще, если бы ядро само давало идентификатор запроса и актуальный план выполнения. Просто зыбко уж очень это все, слишком легко что-нибудь пропустить.

  • pg_stat_statements + pg_stat_activity + loq_query = pg_ash?
    0

    Парсить журнал — это, конечно, смело, но примерно безнадежно. pgBadger этом вон уж сколько занимается, и то не все гладко… А смысл? Почему pg_stat_statements не ограничиться?

  • Музей DataArt. КУВТ2 — учеба и игра
    0

    О! Листаю комменты и думаю — точно ж где-то здесь тебя встречу (:

  • Попытка создать аналог ASH для PostgreSQL
    0

    По практике — история pg_locks пригождается? Кажется, это огромный объем, а польза не очень ясна. (В целом был бы интересен разбор какого-то реального случая на основе собранных данных.)


    Не очень понятно, откуда берутся queryid. Не нашел в коде.


    Нет ли проблем с тем, что pid будут повторяться?

  • Блокировки в PostgreSQL: 4. Блокировки в памяти
    0

    Хм, так и не появилась статья. Пригласил вас так. Публикуйте, интересно почитать.
    Кстати, видели https://pgconf.ru/2018/99643?

  • Блокировки в PostgreSQL: 4. Блокировки в памяти
    0

    Что-то не появляется ваша статья в песочнице. Заботливые лучи НЛО не пускают или я как-то неправильно ищу?

  • Блокировки в PostgreSQL: 4. Блокировки в памяти
    +1

    Владимир, спасибо. Продолжение будет, но, наверное, немного погодя.


    По вопросу. Блокировки и запросы — разные штуки. Соответственно и инструменты разные. Есть два пути:


    1. Использовать стандартное расширение pg_stat_statements. Если запросы частые, они будут видны в табличке. Тут никакое семплирование не нужно.
    2. Установить log_min_duration_statement = 0 и в журнале сообщений будет полная картина происходящего, которую можно потом проанализировать pgBadger-ом, например.


      Правда, если сервер выполняет много запросов, ему может и поплохеть. В 12-й версии для этого появится возможность писать в журнал не все транзакции (log_transaction_sample_rate) — как раз своего рода семплинг. Если запросы частые, они все равно будут попадаться.



    А если говорить о блокировках, то мое мнение такое: семплирование — костыль. Встраивать его в ядро СУБД не нужно. Вместо этого ядро должно давать точную информацию о том, какие ожидания были у сеанса, сколько их было и сколько времени они заняли. Разработчики PostgreSQL этого почему-то не понимают, но мой опыт работы с Ораклом однозначно говорит о том, что это ценнейшая информация.

  • WAL в PostgreSQL: 1. Буферный кеш
    0

    Запишу тут еще про массовое вытеснение (aka вымывание кеша). Несмотря на буферные кольца, оно вполне возможно в ряде ситуаций.


    • Если при последовательном сканировании (Seq Scan) страницы изменяются, то они "отцепляются" от буферного кольца, а к кольцу добавляется новая страница (она выбирается обычным алгоритмом поиска очередной "жертвы"). То есть фактически буферное кольцо не работает. Это имеет место, когда мы обновляем много строк в таблице командой UPDATE.
    • Если мы читает данные из toast-таблицы, это всегда происходит по индексу независимо об объема. Тут буферное кольцо вообще не используется.

    С другой стороны, это не значит, что в буферном кеше моментально не останется нужных данных. Буферы со счетчиком обращений 0 или 1, конечно, будут вытеснены, но горячие буферы имеют шанс сохраниться.

  • Блокировки в PostgreSQL: 3. Блокировки других объектов
    +1
    В общем, я веду к тому, что рекомендательным блокировкам можно найти полезное применение, но я за то, чтобы обходиться без странных костылей везде, где возможно (:

    Кстати, не думаю, что организовывать rate limit в базе данных — хорошая идея. Это по идее надо на более высоком уровне делать.
  • Блокировки в PostgreSQL: 3. Блокировки других объектов
    +1
    Все-таки не очень понял. Вот у нас таблица с JSONB, ну ок. Прилетает новое событие. Мы заглядываем в JSONB, видим, что событие относится к голосованию. Тогда мы а) захватываем блокировку по пользователю, б) проверяем по всей таблице, что в ней нет дублирующего события, в) если нет, то вставляем запись, г) отпускаем блокировку.

    Если так, то да, конечно можно. Но как вы проверяете отсутствие дубликата без подходящего индекса? А если есть такой индекс, то почему не объявить его уникальным?

    test=# CREATE TABLE events(id serial, content jsonb);
    test=# CREATE UNIQUE INDEX ON events( (content->>'referenced_id') )
      WHERE content->>'type' = 'vote';
    test=# INSERT INTO events(content) VALUES (
      '{ "type": "vote", "score": 1, "referenced_id": 12345 }'::jsonb
    );
    test=# INSERT INTO events(content) VALUES (
      '{ "type": "comment", "text": "I like it!", "referenced_id": 12345 }'::jsonb
    );
    test=# INSERT INTO events(content) VALUES (
      '{ "type": "vote", "score": -1, "referenced_id": 12345 }'::jsonb
    );
    
    ERROR:  duplicate key value violates unique constraint "events_expr_idx"
    DETAIL:  Key ((content ->> 'referenced_id'::text))=(12345) already exists.
    
  • Блокировки в PostgreSQL: 3. Блокировки других объектов
    0
    del
  • Блокировки в PostgreSQL: 3. Блокировки других объектов
    +1

    Я правильно понял, что смысл этой блокировки был в том, чтобы сериализовать вставку, то есть чтобы никто не вставлял новые значения, пока идет проверка уникальности? А как была устроена проверка? И что мешало просто объявить уникальность в базе?

  • Блокировки в PostgreSQL: 3. Блокировки других объектов
    +1

    Владимир, добрый день!


    Если строки добавляются в таблицу многими процессами параллельно, то "строка за строкой" все равно не получится, независимо от того, на сколько страниц за раз расширяется таблица. Все равно строки будут как-то произвольно перемешаны.
    Но BRIN-у это не должно мешать, потому что локальность все равно будет соблюдаться. Ведь даже если в пределах страницы строки перемешаны, все id страницы P окажутся меньше любого id страницы Q, если Q немного отстоит от P.


    и расширились на 512 страниц за раз из разных процессов

    Только не из разных, а из одного. Для того и блокировка.

  • Блокировки в PostgreSQL: 1. Блокировки отношений
    0

    Да, вы правы. Это я упустил из вида.

  • Блокировки в PostgreSQL: 2. Блокировки строк
    +1

    Хм, век живи…
    Я был уверен, что нет, но решил проверить. И оказалось, что если при обновлении значение фактически не меняется, то HOT работает. Более того, и в журнал ничего лишнего не пишется.
    Так что обманул я вас, нет (почти) никакой разницы.

  • Блокировки в PostgreSQL: 2. Блокировки строк
    +1
    Постгрес достатчно умный, чтобы понять, что KEY поля не менялись, или нет?

    Достаточно (:


    И вообще практика при апдейте указывать только нужные поля реально несёт какую-то пользу

    Безусловно несет.
    Если вы обновляете поля, которых нет ни в одном индексе, у вас работает HOT. Но если вы обновляете все поля (и у вас на таблице есть хотя бы один индекс) — увы.
    Ну и журнальные записи будет меньше.

  • Блокировки в PostgreSQL: 2. Блокировки строк
    +1

    Рад, что понравилось.

  • Блокировки в PostgreSQL: 1. Блокировки отношений
    +2
    Почему система, если видит, что индекс сломался во время создания, просто не дропнет его сама же? Зачем это приходится делать руками?

    Не знаю. Наверное, чтобы не городить что-то вокруг стандартной процедуры обнаружения и разрешения взаимоблокировок. Убили процесс и убили, ну а все что он успел зафиксировать — осталось.

  • Блокировки в PostgreSQL: 1. Блокировки отношений
    +2
    Почему, например, его создание может сломаться

    Из-за взаимоблокировок. Чтобы объяснить, придется углубиться в детали… Там происходит хитросложный трехфазный процесс.




    Фаза 1.


    Начинаем транзакцию.
    Блокируем таблицу, на которой собираемся строить индекс, в режиме ShareUpdateExclusive.
    Регистрируем индекс в системном каталоге, помечаем его как не готовый для вставок и не валидный.
    Фиксируем транзакцию (но блокировка ShareUpdateExclusive остается, она берется до конца сеанса).


    Если этого не сделать, то есть не показать остальным транзакциям, что на таблице появился новый индекс, то транзакции будут продолжать делать HOT-обновления, что недопустимо.


    Фаза 2.


    Начинаем транзакцию.


    (Ожидание 1) Поскольку еще могут быть транзакции со старыми снимками данных, еще не увидевшие появление индекса, необходимо их подождать. Фактически мы ждем все, что конфликтует с уровнем Share, то есть любые пишущие транзакции.


    Строим индекс, не заботясь о том, как меняется таблица в процессе построения. То есть в индекс попадут только те строки, которые были на момент начала сканирования таблицы.
    Помечаем индекс как готовый ко вставкам, но еще не валидный.
    Фиксируем транзакцию.


    Готовый ко вставкам (pg_index.indisready) означает, что теперь все транзакции будут обновлять этот индекс.


    Фаза 3.


    Начинаем транзакцию.


    (Ожидание 2) Поскольку еще могут быть транзакции со старыми снимками данных, не успевшие понять, что индекс надо обновлять, необходимо их подождать.


    Полностью сканируем таблицу и обновляем построенный индекс так, чтобы в него попало то, что мы упустили во время фазы 2. (А все новые строки уже будут вставляться, так как мы объявили индекс готовым ко вставкам.)


    (Ожидание 3) Поскольку еще могут быть транзакции со старыми снимками данных, необходимо их подождать.


    Фиксируем транзакцию.
    Помечаем индекс как валидный (pg_index.indisvalid), чтобы его можно было использовать.
    Освобождаем блокировку таблицы SharedUpdateExclusive.




    Собственно, проблема в тех трех местах, в которых происходит ожидание.
    Например:


    • начнет выполняться CREATE INDEX CONCURRENTLY и захватывает блокировку ShareUpdateExclusive;
    • какая-то транзакция пытается выполнить что-нибудь несовместимое (например, ANALYZE или какой-нибудь DDL) и повисает;
    • CREATE INDEX CONCURRENTLY доходит до точки ожидания, видит транзакцию со старым снимком и ждет ее завершения — deadlock detected!

    Если хочется еще подробностей, можно заглянуть в commands/indexcmds.c и catalog/index.c (функция validate_index).

  • Блокировки в PostgreSQL: 1. Блокировки отношений
    0

    На здоровье!

  • Блокировки в PostgreSQL: 1. Блокировки отношений
    0

    Если б все так просто. Блокировки объектов в статье тоже есть. Но эти объекты также включают номера транзакций, версии строк, страницы и проч. А мне надо выделить именно отношения.
    Можно, конечно, вместо блокировки объектов говорить просто блокировки (надеясь не спутать их с другими видами блокировок), а вместо отношенийобъекты, но что-то мне кажется, что ясности это не прибавит.

  • Блокировки в PostgreSQL: 1. Блокировки отношений
    +2

    Не от хорошей жизни. Я вот не знаю вразумительного слова, которое бы объединяло и таблицы, и индексы, и последовательности, и материализованные представления. Поэтому приходится дословно переводить жаргонное употребление relation.


    Можете что-то лучше предложить?

  • Тюнинг производительности запросов в PostgreSQL
    +1

    Для тех, кто тоже не сразу врубился в перевод:


    Стоимость — это комбинация из 5 рабочих компонентов, используемых для оценки требуемой работы: последовательная выборка, непоследовательная (случайная) выборка, обработка строки, оператор (функция) обработки и запись индекса обработки.

    Тут речь о том, что есть пять параметров конфигурации, которые определяют стоимость отдельных операций: последовательной выборки (seq_page_cost), случайной выборки (random_page_cost), обработки табличной строки (cpu_tuple_cost), выполнения операции (cpu_operator_cost) и обработки индексной строки (cpu_index_tuple_cost). Итоговая стоимость любого узла плана оценивается как комбинация из энного количества этих примитивных операций.

  • WAL в PostgreSQL: 4. Настройка журнала
    +1

    Куда ж без них. Есть древний pgtune, вот postgres-checkup активно развивается, а если погуглить, то и другие найдутся.


    Но учитывайте, что


    1. ряд параметров зависит от задачи, которую никто, кроме вас, не знает (например, synchronous_commit),
    2. многие важные параметры зависят не только от конфигурации, но и от данных и от нагрузки (например, shared_buffers, work_mem), и для их настройки нужна обратная связь от мониторинга.

    По уму СУБД должна уметь самоподстраиваться под нагрузку, и это направление активно исследуется (например, Энди Павло), но до этого PostgreSQL пока не дожил.

  • WAL в PostgreSQL: 4. Настройка журнала
    0
    Вроде бы, судя по документации, можно подписываться на изменения отдельно по разным таблицам? Т.е. если шардить/партицировать таблицу (как Скайп делал), то можно параллельно читать, как я надеюсь.

    Так, конечно, можно получить несколько потоков, но, боюсь, это будет менее эффективно, чем просто читать в один поток. Да и с точки зрения поддержки неудобно.


    Посмотрел на ABC — да, они используют логическую репликацию и преобразуют поток сознания Постгреса во что-то удобоваримое для Эластика.

  • WAL в PostgreSQL: 4. Настройка журнала
    0

    Всегда пожалуйста.


    Как проверить — тестировать, только так. Верить никому нельзя (:
    То есть надо реально отключать питание и смотреть, что получится. Документация предлагает diskchecker.pl в помощь.


    А бывают еще всякие интересные штуки. Не так давно большой шум стоял из-за того, что — как выяснилось — в некоторых ОС после ошибки при fsync некорректно отрабатывается следующий вызов fsync (описание). Как это можно протестировать, я даже не представляю.

  • WAL в PostgreSQL: 4. Настройка журнала
    0

    Рад, что понравилось! Дойдем и до репликации постепенно.


    Распараллелить вычитывание нельзя, это ж по сути своей один поток. Вот обработку вычитанного, наверное, можно распараллелить, но это с базой уже никак не связано.


    А расскажите в трех словах про Эластик — ему можно поток изменений скармливать, а не сами данные? Или там вокруг придется еще много всякого нагородить? Не слишком сложно?

  • WAL в PostgreSQL: 3. Контрольная точка
    +1

    Закрепление (aka pin) используется, когда процесс работает со страницей. При этом страницу можно не только читать, но и изменять в некоторых пределах. Но вытеснять нельзя — закрепление этого не позволяет.


    Но в данном случае используется не закрепление, а другая блокировка, которая не дает странице меняться. Я про блокировки планирую подробно написать, как только с журналом закончу.

  • WAL в PostgreSQL: 3. Контрольная точка
    +1

    Нет, никаких других ограничителей, кроме названных, нет.


    Насчет деградации вопрос спорный. Сама по себе контрольная точка не начнет молотить быстрее, только если не возрастет нагрузка и можно будет не успеть. Поэтому теоретически должна быть не деградация, а просто некий предел производительности системы.


    А деградация (как внезапный провал) будет в том случае, если ОС долго откладывает физическую запись на диск и потом начинает писать сразу и много. Мне это так видится.


    В этом смысле любой фоновый асинхронный процесс — это хорошо, потому что нагружает систему равномерно. А любая синхронная деятельность (когда нельзя продолжать, пока что-то не сделано) — наоборот, плохо.

  • WAL в PostgreSQL: 3. Контрольная точка
    +1

    Ускорение возможно за счет того, что в обычном режиме контрольная точка не пишет данные на максимальной скорости. Она должна успеть записать все грязные буферы за время checkpoint_timeout × checkpoint_completion_target. Процесс сам регулирует задержки, чтобы уложиться в этот интервал. А когда надо ускориться, уменьшает задержку.


    Проблема будет в том случае, когда даже на максимальной скорости контрольная точка не успевает уложиться в заданные рамки. Тогда надо либо сознательно увеличивать интервал (если он неоправданно маленький), либо колдовать с настройками ОС и железом. А со стороны PostgreSQL там оптимизировать-то нечего. Только не делать контрольные точки чаще, чем нужно.