Мы продолжаем публиковать видео и расшифровки лучших докладов с конференции 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-й версии, когда делаем всю таблицу парционированной, а потом просто детачим партицию и оставляем ее как архив. К ней тоже можно осуществлять доступ.
— Конечно, почему бы нет. Уступаю место следующему докладчику.
Расскажу такую историю. Наш клиент говорит: «Медленно работает база, а наше приложение занимается обслуживаем населения. Мы боимся, что нас тут поднимут на вилы». Выяснилось, что у них было очень много процессов в состоянии 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-й версии, когда делаем всю таблицу парционированной, а потом просто детачим партицию и оставляем ее как архив. К ней тоже можно осуществлять доступ.
— Конечно, почему бы нет. Уступаю место следующему докладчику.