Хочу поделиться решением проблемы «duplicate key value violates unique constraint «pg_class_relname_nsp_index»» в PostgreSQL, с которой я столкнулся при добавлении новых секций в секционированные таблицы, с использованием распараллеливания.
Суть проблемы заключается в дублировании имен для индексов секций, автоматически генерируемых сервером Postgres в одновременно исполняемых пользовательских параллельных процессах, для исходных двух и более секционированных таблиц, независимых друг от друга, но имеющих длинные имена, с совпадающей, в значительной мере, начальной частью.
Первоначальной моей реакцией на возникшую ситуацию, была простая попытка заменить слишком длинные имена таблиц на более короткие. А этот exception я посчитал внутренним багом Postgres. Но далее, аналогичная ситуация возникала вновь и вновь, с другими парами, более коротких имен, длиной около 30 символов (а это в два с лишним раза меньше текущего ограничения Postgres в 63 символа). Причем ошибка имела нестабильный характер: на тестовом стенде можно было получить удачную сборку, а при дальнейшем развертывании на продуктивном стенде сборка оказывалась неудачной. Можно, конечно, рассуждать о том, что незачем использовать такие длинные имена — что можно принять условное внутрипроектное соглашение о непревышении какого-то выбранного порога для имен. Но 30 символов — это мало. В свое время, в Oracle (в теперь уже старых версиях), ограничение в 30 символов, меня лично, угнетало. В ряде случаев, достаточно длинные имена не ухудшают читабельность проекта, а улучшают ее, внося большую информативность, особенно, в реальных базах данных с большим количеством сущностей, в которых непросто ориентироваться (тем более, по прошествии длительного времени). Кроме того, я считаю, что подбор приемлемого сокращения для технического имени часто оказывается более сложным занятием, чем простой выбор расширенного наименования. И, в конце концов, выбирая наименования для создаваемых таблиц, мы закладываем основу для последующего зависимого функционала приложения — очевидно, что замена наименований — это болезненная и нежелательная процедура. Т.о. мотивации для поиска решения проблемы у меня было достаточно.
Разберем ситуацию подробно, с воспроизведением ошибки на тестовом примере. Насколько я понял, воспроизвести ошибку легче, если специально выбрать имена подлиннее, и, главное, чтобы значительная передняя часть этих имен была одинаковой (иначе, наверное, вероятность воспроизведения повышается с повышением количества создаваемых секций и/или количества параллельных сессий).
Для начала, посмотрим, что это за unique constraint:
На то, что дублирование возникает в именах индексов это пока явно не указывает — это же уникальное ограничение может сработать и при регистрации в pg_catalog.pg_class других объектов БД (в первую очередь, таблиц):
select relkind, * from pg_catalog.pg_class where not (relkind = any('{i, I}'::char[]))В обсуждениях встречается похожая ошибка, с которой можно спутать нашу: duplicate key value violates unique constraint «pg_type_typname_nsp_index», — она, как раз, связана с дублированием в именах создаваемых таблиц, и также связана с параллельным исполнением. Она возникает на другом уровне — при регистрации в системном каталоге типа для создаваемой таблицы:
Мне представляется, что попытка регистрации одних и тех же таблиц в параллельных сессиях — это явно не проблема уровня СУБД — это изначально «неправильное» распараллеливание инструкций DDL со стороны пользователя (или со стороны того middleware, которое пользователь использует). В нашем же случае, на первый взгляд, распараллеливание «правильное» — по исходным условиям, таблицы разные, физически друг от друга не зависят, и могут создаваться в независимых параллельных сессиях.
Итак, запланируем создание секционированных таблиц со следующими именами:
-
the_table_with_a_really_long_descriptive_name_1
-
the_table_with_a_really_long_descriptive_name_2
-
the_table_with_a_really_long_descriptive_name_3
В каждую таблицу будет добавлен индекс:
create table the_table_with_a_really_long_descriptive_name_1 ( key_field integer , some_attribute integer ) partition by list(key_field) ; create index i_the_table_with_a_really_long_descriptive_name_1$some_attribute on the_table_with_a_really_long_descriptive_name_1 ( some_attribute ) ;Как известно, на основе такого индекса, добавляемого в родительскую логическую таблицу, производится автоматическая генерация индексов в добавляемых секциях. Именно в рамках этого процесса и возникает наша проблема.
Составим тестовый shell-скрипт с psql, в котором наши таблицы будут создаваться в цикле, с добавлением трех секций в каждую таблицу. Для сравнения, добавим также генерацию секционированных таблиц с более короткими именами. В конце скрипта выведем из системного каталога перечень созданных индексов.
#!/bin/bash pg_connection_string="postgresql://postgres@localhost:5432/postgres" function create_partitioned_table() { echo " \set ON_ERROR_STOP true \ drop table if exists the_table_with_a_short_name_$1 ; create table the_table_with_a_short_name_$1 ( key_field integer , some_attribute integer ) partition by list(key_field) ; create index i_the_table_with_a_short_name_$1$some_attribute on the_table_with_a_short_name_$1 ( some_attribute ) ; \ do \$plsql\$ begin for i in 1..3 loop execute format( \$dml\$ create table the_table_with_a_short_name_$1\$partition%s partition of the_table_with_a_short_name_$1 for values in (%s) \$dml\$ , i , i ) ; end loop ; end \$plsql\$ ; \ drop table if exists the_table_with_a_really_long_descriptive_name_$1 ; create table the_table_with_a_really_long_descriptive_name_$1 ( key_field integer , some_attribute integer ) partition by list(key_field) ; create index i_the_table_with_a_really_long_descriptive_name_$1$some_attribute on the_table_with_a_really_long_descriptive_name_$1 ( some_attribute ) ; \ do \$plsql\$ begin for i in 1..3 loop execute format( \$dml\$ create table the_table_with_a_really_long_descriptive_name_$1\$partition%s partition of the_table_with_a_really_long_descriptive_name_$1 for values in (%s) \$dml\$ , i , i ) ; end loop ; end \$plsql\$ ; " | psql \ --dbname=$pg_connection_string \ --quiet sleep 1 } execute_in_parallel="$1" # specify "true" to execute in parallel for i in {1..3} do if [[ $execute_in_parallel = "true" ]]; then create_partitioned_table $i & # execution in the background else create_partitioned_table $i fi done wait psql \ --dbname=$pg_connection_string \ --command="\di" echo "All done"Первый запуск, с последовательным исполнением, показывает результат автоматической генерации со стороны Postgres: у имен физических индексов для секций, составляемых на основе имени логического секционированного индекса и имени секции, отбрасывается префикс «i_», и, при превышении лимита по длине (63 символа), вырезается некая внутренняя часть (где-то, в конце второй трети), а также добавляется постфикс «_idx[N]».
При запуске с параллельным исполнением (для этого предусмотрен параметр), получаем искомую ошибку (номера таблиц могут быть каждый раз разными, т.к. порядок асинхронного исполнения непредсказуем):
Как видно, проблема возникает при составлении имен для внутренних объектов, которые, в итоге, не укладываются в действующее ограничение по длине; пользователь при этом составляет наименования объектов в рамках действующего ограничения и ничего не нарушает. В то же время, exception не возникает из-за какого-то бага Postgres — при последовательном исполнении проблема не возникает, а при распараллеливании требуется необходимое упорядочивание для инструкций по добавлению секций в таблицы с такими проблемными именами — т.е. другого решения, которое можно было бы реализовать внутри СУБД, думаю, нет. Еще стоит отметить, что проблема не возникает при параллельном добавлении секций в одну таблицу — проблема именно с независимыми друг от друга двумя и более таблицами, — т.к. автогенерация индексов для секций происходит в рамках единой для пользователя инструкции по добавлению секции (create table … partition of … или alter table … attach partition …), которая требует эксклюзивной блокировки головной таблицы и, соответственно, появления дубликатов не происходит.
В основе выработанного решения данной проблемы лежит идея использования т.н. логической зависимости, имеющей отношение к необходимости упорядоченной обработки таблиц с проблемными именами. Следующий запрос, локализованный в представлении для дальнейшего использования, позволяет получить перечень таких логических зависимостей:
create or replace view v_orderliness_logical_dependency as with partitioned_table as ( select n.nspname as schema_name , t.relname as table_name , t.oid as table_id from pg_catalog.pg_class t join pg_catalog.pg_namespace n on n.oid = t.relnamespace where t.relkind = 'p'::"char" ) , name_length_limit as ( select s.setting::integer / 2 - 2 as max_len from pg_catalog.pg_settings s where s.name = 'max_identifier_length' ) , orderliness_logical_dependency as ( select t.schema_name , t.group_name , array_agg(t.table_id order by t.table_name) as table_seq from ( select t.schema_name , t.table_name , t.table_id , left(t.table_name, n.max_len) as group_name from partitioned_table t cross join name_length_limit n ) t group by t.schema_name , t.group_name having count(*) > 1 ) , dependency_group_item as ( select dep.schema_name , dep.group_name , t.table_id , pt.table_name , t.ordinal_num from orderliness_logical_dependency dep cross join lateral unnest(dep.table_seq) with ordinality as t(table_id, ordinal_num) join partitioned_table pt on pt.table_id = t.table_id ) select forward_table.schema_name , forward_table.table_name as forward_table_name , forward_table.table_id as forward_table_id , following_table.table_name as following_table_name , following_table.table_id as following_table_id from dependency_group_item forward_table join dependency_group_item following_table on following_table.schema_name = forward_table.schema_name and following_table.group_name = forward_table.group_name and following_table.ordinal_num > forward_table.ordinal_num ;Подбираются пары секционированных таблиц, имена которых совпадают по первым 29 символам. Далее, в каждой такой паре, на основе сортировки по имени, первая таблица условно определяется как главная, вторая — как зависимая.
Для наших трех таблиц мы получили полный набор логических связей: вторая и третья таблицы зависят от первой, третья зависит от второй.
Используя полученные логические зависимости, создадим конечное представление, с распределением секционированных таблиц по т.н. «цепочкам обработки».
create or replace view v_partitioned_table_processing_chain as with recursive table_dependency as ( select forward_table_id as primary_table_id , following_table_id as dependent_table_id from v_orderliness_logical_dependency ) , selected_table as ( select n.nspname as schema_name , t.relname as table_name , t.oid as table_id from pg_catalog.pg_class t join pg_catalog.pg_namespace n on n.oid = t.relnamespace where t.relkind = 'p'::"char" ) , target_table as ( select t.table_id , dep.primary_table_id , case when dep.primary_table_id is not null then 1 else 0 end as dep_level from selected_table t left join table_dependency dep on dep.dependent_table_id = t.table_id union all select t.table_id , dep.primary_table_id , t.dep_level + 1 as dep_level from target_table t join table_dependency dep on dep.dependent_table_id = t.primary_table_id ) select t.schema_name , t.table_name , t.table_id , t.dep_level , coalesce( t.primary_table_id , t.table_id ) as safe_parallel_processing_chain_id from ( select st.schema_name , st.table_name , t.table_id , t.dep_level , t.primary_table_id , row_number() over ( partition by t.table_id order by t.dep_level desc ) as ordinal_number from target_table t join selected_table st on st.table_id = t.table_id ) t where t.ordinal_number = 1 order by dep_level , table_name ;Вывод данных из этого представления показывает, что исходные таблицы распределились по четырем «цепочкам обработки»: три проблемные таблицы объединились в одну общую цепочку, которая должна обрабатываться в рамках одного потока; а каждая из оставшихся таблиц с короткими именами получила свою индивидуальную цепочку, каждая из которых будет обрабатываться отдельно.
Модифицируем первоначальный тестовый shell-скрипт, внедрив в него новую логику распараллеливания:
-
На первом этапе будет производиться создание трех секционированных таблиц, в однопоточном режиме;
-
На втором этапе, в параллельном режиме, будет производиться генерация секций — корректное распараллеливание теперь будет обеспечиваться распределением на основе получаемого перечня логических зависимостей.
#!/bin/bash pg_connection_string="postgresql://postgres@localhost:5432/postgres" function create_partitioned_table() { echo " \set ON_ERROR_STOP true \ drop table if exists the_table_with_a_short_name_$1 ; create table the_table_with_a_short_name_$1 ( key_field integer , some_attribute integer ) partition by list(key_field) ; create index i_the_table_with_a_short_name_$1$some_attribute on the_table_with_a_short_name_$1 ( some_attribute ) ; \ drop table if exists the_table_with_a_really_long_descriptive_name_$1 ; create table the_table_with_a_really_long_descriptive_name_$1 ( key_field integer , some_attribute integer ) partition by list(key_field) ; create index i_the_table_with_a_really_long_descriptive_name_$1$some_attribute on the_table_with_a_really_long_descriptive_name_$1 ( some_attribute ) ; " | psql \ --dbname=$pg_connection_string \ --quiet sleep 1 } function create_table_partitions() { echo " \set ON_ERROR_STOP true \ do \$plsql\$ declare l_table_rec record; begin for l_table_rec in ( select table_name from v_partitioned_table_processing_chain where safe_parallel_processing_chain_id = $1 order by dep_level ) loop <<partitions_creation>> for i in 1..3 loop execute format( \$dml\$ create table %s\$partition%s partition of %s for values in (%s) \$dml\$ , l_table_rec.table_name , i , l_table_rec.table_name , i ) ; end loop ; end loop ; end \$plsql\$ ; " | psql \ --dbname=$pg_connection_string \ --quiet sleep 1 } execute_in_parallel="$1" # specify "true" to execute in parallel for i in {1..3} do create_partitioned_table $i done temp_dir="$(mktemp -d)" processing_chains="$temp_dir/processing_chains" echo " select distinct safe_parallel_processing_chain_id from v_partitioned_table_processing_chain " | \ psql $pg_connection_string \ --tuples-only \ --no-align \ --field-separator=$'\t' \ > $processing_chains while IFS=$'\n\t' read -r safe_parallel_processing_chain_id; do if [[ $execute_in_parallel = "true" ]]; then create_table_partitions $safe_parallel_processing_chain_id & # execution in the background else create_table_partitions $safe_parallel_processing_chain_id fi done < $processing_chains wait rm -r "$temp_dir" psql \ --dbname=$pg_connection_string \ --command="\di" echo "All done"В итоге, при запуске в параллельном режиме, ошибка больше не возникает, и мы получаем корректный результат:
ссылка на оригинал статьи https://habr.com/ru/articles/872990/
Добавить комментарий