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