Хочу поделиться решением проблемы «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"В итоге, при запуске в параллельном режиме, ошибка больше не возникает, и мы получаем корректный результат:
