Типичные ошибки при работе с PostgreSQL. Часть 2

    Мы продолжаем публиковать видео и расшифровки лучших докладов с конференции PGConf.Russia 2019. В первой части доклада Ивана Фролкова речь шла о непоследовательном именовании, о constraints, о том, где лучше сосредоточить логику — в базе или в приложении. В этой части вас ждет разбор обработки ошибок, конкурентного доступа, неотменяемых операций, CTE и JSON.



    Расскажу такую историю. Наш клиент говорит: «Медленно работает база, а наше приложение занимается обслуживаем населения. Мы боимся, что нас тут поднимут на вилы». Выяснилось, что у них было очень много процессов в состоянии idle in transaction. Приложение начало транзакцию, ничего не делает, но и транзакцию не завершает. Если вы взаимодействуете с какими-то внешними сервисами, то, в принципе, это нормальная ситуация. Другое дело, что если у вас состояние idle in transaction длится долго (больше минуты уже подозрительно), то это плохо потому, что PostgreSQL очень не любит долгие транзакции: VACUUM не сможет почистить все те строки, которые он мог бы увидеть, и долго висящая транзакция эффективно блокирует VACUUM. Начинают разбухать таблицы, индексы становятся всё менее эффективными.



    В данном случае люди не вполне корректно писали запросы и получали декартовы произведения — такие запросы выполнялись по несколько дней. Ну а пользователь, он же нажмет кнопку, подождет результата и, если результата нет, снова нажмет кнопку.

    Но это не объясняло, почему у них появляется столько процессов в idle in transaction. А появлялись они вот в какой ситуации: приложение лезет в базу, начинает транзакцию, лезет на какой-то внешний сервис, получает там ошибку, а дальше всё просто осыпается, печатаем в лог stack trace, и на этом успокаиваемся. Соединение остается заброшенным, висит и мешается.

    Что с этим делать? Во-первых, надо обрабатывать ошибки всегда. Если к вам прилетела ошибка, пожалуйста, не игнорируйте ее. Хорошо еще, если PostgreSQL потерял соединение: он откатит транзакцию, переживём. На этом я еще остановлюсь. Ну а если есть код, который править совсем нет времени, то у нас еще есть max idle in transaction — можно поставить, и будет просто вышибать неактивные транзакции.



    Типичный случай «обработки» ошибок: EXCEPTION WHEN OTHERS THAN NULL. Как-то мы спорили с коллегой о терминологии. Я говорил, что это переводится как «гори оно всё синим пламенем», а он — «пропади оно всё пропадом». Если у нас нечто плохое произошло, то, даже если всё с руганью осыпалось в лог, это всё же лучше, чем полная тишина — как здесь.



    Если вы не знаете, что делать с ошибкой, то не надо ее и перехватывать. Очень распространенная практика: перехватили ошибку, записали в лог и побежали дальше, как будто ничего не произошло. Если вы, опять же, занимайтесь денежными операциями, и у вас произошла ошибка, которую вы проигнорировали, результаты могут быть непредсказуемые. В 90-е годы могли в лес, например, вывезти в багажнике. Сейчас времена помягче стали, но тоже мало приятного.



    Если делаем операцию на клиенте, то, обычно, возвращаем значение: всё прошло удачно либо неудачно. И каждую ошибку обрабатываем. Я видел, как люди специально писали код plpgsql, где перехватывали ошибку, писали в лог, что, мол, да, была ошибка и довольно грубая, вставляли свой текст сообщения. Но SQLSTATE не возвращали. Это выполняется всегда, поэтому, если они забывали что-то проверить, то у них начинались проблемы.

    Все, почему-то боятся исключений — как в plpgsql, так и в других языках. А если не придумывать что-то свое, а пользоваться стандартными возможностями языка, все обычно получается хорошо. Особенно эта проблема часто встречается, когда падает соединение. Оно упало, процесс idle in transaction, база заполняется, падает производительность. Между прочим, такая транзакция может еще оставить блокировки, но это, почему-то, встречается не так часто. Поэтому добавляйте в код обработки ошибки finally и там вычищайте соединение, отдавайте его обратно серверу.



    Более того, в случае, если у вас хорошо, правильно поименованы constraint-ы, вы можете уже при обработке ошибки выкинуть исключение не из базы, а из приложения. В spring есть exception translation, в php, соответственно, set_exception_handler. Обратите внимание на те средства, которые вам предоставляет ваш фреймворк, они там неспроста появились.

    Итак: не надо перехватывать ошибку, с которым не знаете что делать; именуйте ошибки тщательно и аккуратно; классифицируйте ошибки.



    Лично я классифицирую по таким критериям: операцию можно повторить (например, у нас возник deadlock); операцию повторить нельзя, она уже выполнена; операция не может быть выполнена в принципе.

    Как ни парадоксально, с точки зрения приложения, ситуации, когда возникнет deadlock, когда потеряно соединение и когда у нас кончились деньги для выплаты, — это ситуации одинаковые: обработчик ошибки попытается через некоторое время выполнить операцию снова.



    С другой стороны, что пишут в приложении, в общем-то, не мое дело: я занимаюсь базой. Я лишь призываю аккуратно обрабатывать ошибки, иначе: idle in transaction, залоченные строки, пухнущие базы и так далее.

    Большинство разработчиков считает, что они работают с базой одни, и их приложение выполняет операции строго последовательно. И это плюс всем реляционным СУБД потому, что, как ни странно, при этом всё работает, как правило, очень хорошо, даже со стандартным уровнем изоляции READ COMMITTED, а не SERIALIZABLE. В то же время, случаются ситуации, когда теряются обновления: один грузит форму, другой грузит эту же форму, один написал и сохранил, другой сохранил старую — изменения стерли. Первый пришел ругаться: «как же так, я столько написали, и всё потеряно».



    Из моего опыта: раз в неделю по пятницам два менеджера проводили выплаты. Они должны
    были меняться через раз, но, тем не менее, однажды полезли одновременно и сделали две выплаты на одного человека. Если у вас есть хоть какая-то возможность ошибки конкурентного доступа, она рано или поздно случится. Вопрос когда.

    Кроме того, обращаю ваше внимание на ограничения. Я неоднократно видел, как уникальность пытались обеспечить триггерами. Сходу триггерами уникальность в таблице вы не обеспечите. Либо вам тогда нужно будет блокировать всю таблицу, либо делать еще какие-то сложные телодвижения. Вы рано или поздно на этом споткнетесь.



    Пару раз натыкался на совершенно кошмарную вещь: из базы вызывается внешний web-сервис. Там проводились какие-то операции, изменяющие внешние сущности. Это плохо тем, что в базе транзакция может откатиться, но операции на удаленном сервисе откачены не будут.

    Еще более тонкий момент — deadlock-и. Давайте представим: мы обрабатываем транзакцию, вызываем внешний web-сервис, что-то поменяли, после этого у нас возникнет deadlock, и мы откатываемся, потом пытаемся выполнить операцию еще раз, вызываем еще раз, при хорошем стечении обстоятельств еще раз возникает deadlock, опять откатываемся — так может
    происходить много раз (я натыкался на пару сотен повторов). И вот вы обрабатываете эти deadlock-и более-менее корректно, повторяете операции и вдруг обнаруживаете, что уже в течении двух месяцев выплачиваете кому-то двойную сумму.



    Я встречался с платежными сервисами, у которых был небогатый API: «выплатить такую-то сумму такому-то пользователю»; функция возвращает результат — выплачено / не выплачено. Во-первых, возникает проблема в случае повтора, во-вторых, непонятно, что делать, если прервалось соединение. Почему-то на эту тему тоже очень мало кто заморачивается.



    На слайде пример: такая операция должна выполняться в два этапа: как бы предупреждение — «будем сейчас что-то делать»; сама операция.



    Если мы вдруг прервёмся — мало ли, выключили питание — мы сможем повторно выполнить операцию. Если мы сдохли на втором этапе, то, по крайней мире, второй раз мы это не выполним, и это можно будет разобрать вручную. На самом деле подавляющее большинство таких операций нормально отрабатывает первый раз, но эти меры не теоретические измышления. Всё может нормально работать месяцами, и вдруг админ начинает мудрить с сетью, сервис начинает активно мигать — и начались проблемы.


    На слайде 4 типа неотменяемых операций. Последний — неидемпотентные операции. Это совсем грустный случай. Я в начале говорил о товарище, который всё делал на триггерах именно чтобы обеспечить идемпотентность своих операций.


    На конференции люди будут рассказать о Common Table Expressions, о том, как это хорошо. К сожалению, CTE в PostgreSQL не бесплатны: они требуют под себя work_mem. Если у вас выборка небольшая, то, в общем, ничего страшного. А если вдруг у вас она большая, то у вас начинаются проблемы. Люди очень часто используют CTE в качестве этаких мини-вьюшек — для того, чтобы можно было как-то структурировать приложение. CTE очень востребованные.





    Можно сделать временные view, но, к сожалению, каждое занимает строчку в pg_class, и если это очень активно используется, то возможны проблемы с распуханием каталога.
    В этом случае можно посоветовать сделать параметризированное view, либо динамически формировать запрос, но, к сожалению, в PostgreSQL изнутри с этим не очень здорово.



    О JSON обычно рассказывают в превосходных тонах, но есть тенденция в приложении в JSON пихать вообще все что угодно. В принципе, всё работает неплохо. С другой стороны, из JSON-а данные достаются хоть и быстро, но не так быстро, как из колонок. Еще хуже, если у вас JSON большой, и его вынесло в TOAST. Чтобы JSON оттуда взять, его нужно поднять из TOAST-а.

    Если все колонки в JSON-е, по ним даже построен функциональный индекс, то все равно оттуда доставать надо. Еще хуже получается при большом объеме, когда база большая, когда у вас bitmap index scan. Тогда у нас ссылки не на строки, а на целую страницу, и, для того, чтобы понять, что со страницы брать, PostgreSQL сделает Recheck, то есть он поднимает строчку из TOAST и проверяет, есть там это значение или нет, и соответственно уже пропускает или не пропускает. Если с небольшими колонками это работает хорошо, то с JSON это большая проблема. Слишком увлекаться JSON-ами не надо.



    — Как проверять, когда со строкой работают несколько пользователей? Какие варианты есть?

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

    — Вы говорите, что что надо именовать constraint-ы хорошими именами, чтобы пользователь мог понять, что происходит. Но есть ограничение в 60 символов на имя constraint-а. Этого часто не хватает. Как с этим бороться?

    — Думаю, бороться самоограничением. В PostgreSQL это специальный тип длиной 64. В принципе можно перекомпилировать на бОльшую длину, но это не очень хорошо.

    — В докладе вы заинтриговали нас тем, что нам надо делать что-то с архивами. Какой механизм вынесения устаревших данных в архив считается самым правильным?

    — Как я в самом начале уже говорил, при должном усердии работает всё. Какой способ вам наболее удобен, тем и пользуйтесь.


    Timing: 2-я часть доклада начинается с 25:16

    — Есть некая процедура, которую вызывают параллельно несколько пользователей. Как ограничить параллельное выполнение этой процедуры, то есть выстроить всех
    пользователей в очередь так, чтобы, пока один не закончит выполнение процедуры, следующий не мог начать ее использовать?


    — Именно процедура? Или достаточно транзакции?

    — Именно процедура, которая вызывается в некоторой транзакции.

    — Вы можете поставить блокировку на объект. Были бы сложности, если б у вас было условие, скажем, не больше 3 одновременно. Но и это реализуемо. Я обычно использую транзакционные блокировки, но можно и внетранзакционные.

    — Я бы хотела все-таки еще раз вернуться к архивным данным. Вы говорили о
    возможности хранения архива так, чтобы из приложения данные были также доступны. Мне приходила в голову мысль просто сделать отдельную архивную базу. Какие еще есть варианты?


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

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

    — Конечно, почему бы нет. Уступаю место следующему докладчику.
    Postgres Professional
    208,00
    Разработчик СУБД Postgres Pro
    Поделиться публикацией

    Комментарии 31

      +1
      Прочитал на одном дьіхании. Спасибо!
        0
        — Как проверять, когда со строкой работают несколько пользователей? Какие варианты есть?

        В MSSQL есть столбец типа timestamp, в pg можно заюзать xmin, например. Отдаём на форму его значение, при апдейте проверяем не только ключ, но и сравниваем с xmin. Если кто-то изменил строчку — не обновим ничего, сообщаем пользователю (а он уж пусть решает, насильно перенакатить, бросить всё, попытаться смёржить).
          +1
          Не надо xmin. Это внутренная механика, не используйте ее в своих целях (если, конечно, не хочется развлечений при обновлениях). Не хочется проверять все поля — сделайте свой номер версии.
            0
            Ну а что делать, если в PG нет встроенных средств :( Если использование сущности ограничено, то можно и своё версионирование написать, но потом придёт какой-нибудь разработчик, и забудет в каком-нибудь апдейте проинкрементить версию. В результате всё замечательно «сломается», хотя работать будет, но будет бага вида «у меня пропали данные», которую вообще не возможно будет отследить.
            Уж лучше всё упадёт при обновлении и можно накрыть это костылями, чем будет странно себя вести из-за ошибки программиста в каком-нибудь второстепенном модуле.
              0
              Простите, а какие встроенные средства вы хотели бы видеть? Чем вариант с хешом от всех колонок не нравится?
                0
                В MSSQL есть столбец типа timestamp (и нет, там не время). В результате при любом обновлении записи значение этого столбца меняется автоматически. Ничего не надо считать ручками, нельзя забыть его обновить. В postgre для этих целей можно использовать xmin, но он считается не очень труЪ способом.
                  0
                  Ну наверное да, неплохо, что есть.
                    0
                    Дык сделайте поле и повесьте триггер, проблема-то.
                    А если боитесь что-то забыть, то все это можно автоматизировать, вплоть до создания триггеров на новых таблицах из событийного триггера.
                      0
                      Ну еще проще смотреть md5(t::text), разница по большому счету просто синтаксическая.
                        0
                        Да понятно что проще, кто ж спорит.
              0
              В Oracle есть select for update

              неужели в Pg нет ничего столь же удобного?
                +1
                Не стоит это использовать для данной задачи.
                Представьте, пользователь открывает форму на редактирование и… уходит на обед. Строчка залочена. Больше никто не может ничего с ней сделать.
                  0
                  Так незачем держать транзакцию открытой когда пользователь открыл форму. Транзакцию открывают когда пользователь сохраняет форму.
                  select for update блокирует чтение строки другими транзакциями.
                    +1
                    А смысл этого действия? Форму открыли два пользователя в 12:00. Редактируют, первый сохранил в 12:05, второй в 12:10 и затёр все действия первого. Чем нам в этой ситуации поможет SELECT FOR UPDATE?
                      0
                      А для этого либо
                      • проверяют поля до/после
                      • timestamp
                      • version


                      select for update нужен например что бы другие транзакции не делали ненужные действия, а потом долго их не откатывали.
                      0
                      Ну что вы, чтение не блокируется. Блокируется только изменение.
                      Но транзакцию, конечно, все равно не надо держать открытой.
                        –1
                        Разве SELECT FOR UPDATE не для того что бы блокировать чтение данной строки для паралельных SELECT FOR UPDATE?
                          +2
                          SELECT FOR UPDATE же не просто читает, он блокирует. Просто чтение (SELECT) будет работать, а SELECT FOR UPDATE или UPDATE — будут ждать.
                      0
                      Собственно для задачи гарантированного избавления от совместных правок это и придумано.
                      Как мне показалось в докладе обсуждается именно проблема совместных правок.

                      Впрочем топик про Pg. И, судя по всему, в нем нельзя на уровне базы гарантировать неизменяемость данных другими транзакциями на необходимое время.
                        0
                        Ну это как бы логично — кто первый успел, того и тапки. Хуже будет, если пользователь 1 открыл форму и ушёл на обед. Пользователь 2 открыл ту же форму, внёс изменения и записал данные. Пользователь 1 вернулся с обеда, внёс в форму данные и при попытке записать данные система говорит, что пользователь 2 уже изменил данные. Поэтому нужно переоткрыть форму (перечитать данные из бд) и все, что вносил пользователь 1 в форму, нужно теперь внести заново
                          0
                          Хуже будет, если пользователь 1 открыл форму и ушёл на обед

                          Что вы тут предлагаете? Не давать редактировать форму никому, или просто сделать хороший код, который это как-то обрабатывает. Второй подход отличный, но очень дорогой в плане времени программистов (форм много, все разные, ситуация не очень частая).
                            0
                            Это хороший вопрос. Я скажу лишь как сейчас реализовано, например в 1С. До недавнего времени, если пользователь открыл форму документа, то никто больше не мог работать с этим документом. Теперь идет проверка самой платформой (не на уровне бд) признака модифицированности. Если пользователь внес изменения и не записал их — просмотр и редактирование документа по прежнему невозможно, а если модифицированности формы нет, то другие пользователи могут работать с документом. То есть, если пользователь 1 открыл форму и ушел на обед, при этом не внеся никаких правок, то пользователь 2 спокойно может работать, если правки пользователь 1 успел сделать — то пользователь 2 работать с документом не сможет
                              0
                              Это плохое решение. Пользователь может не на обед, а куда-то убежать по делам (любимый хомячок простудился) и банально позвонить другому с просьбой доделать. Может просто случайно нажать какую-то кнопку (сохранять не планировал) и уйти домой. В небольшой организации это может решиться организационными методами и пенделями работникам, но больше шансов, что пендели получать разработчики.
                                0
                                Мы, разработчики, нередко полагаем, что можем программным методом направить, так сказать, пользователей на путь истинный. Написать свой «ИИ» чтобы данные вводились только корректные и бизнес процесс шёл идеально. Но в реальности самыми действенными способами оказываются именно организационные. Главное не забывать, что лучше всего на человека влияет другой человек, а любое ограничение системы пользователь найдёт как обойти
                    –2
                    — Как проверять, когда со строкой работают несколько пользователей? Какие варианты есть?

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

                    Серьезно, это вопрос на конференции?
                      0
                      Абсолютно серьезно. А что такого? Вон выше select for update предлагают.
                        0
                        Поправьте меня, может, я не понимаю.
                        Все эти select for update — это же про то, как блокировать данные в транзакции при изменении (т.е. сначала select, какая-то обработка, тут же update). А не про то, что Вася открыл окно, select-ом for update выбрал и заблокировал строку на длительное время, а потом через час сделал update.
                        В вопросе речь идет про non-lock concurrency control. Это когда на уровне логики приложения проверяется, не изменена ли запись кем-то относительно момента, когда мы ее вывели в окно. И это не в транзакции.
                        Мне кажется, это не вопрос о PostgreSQL.
                          0
                          Все эти select for update — это же про то, как блокировать данные в транзакции при изменении...
                          Да, все верно.
                          Мне кажется, это не вопрос о PostgreSQL.
                          Безусловно, но тем не менее иметь в виду это необходимо.
                      0

                      Партиции для архивных данных работают прекрасно. Делал так, например, базу ФИАС разбивая на партиции по регионам. Рекурсивный CTE вытаскивает данные очень быстро.

                        +1
                        у нас еще есть max idle in transaction

                        Возможно, имелось ввиду idle_in_transaction_session_timeout?

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