Есть задача пересоздать таблицу с 1 млрд строк с минимальным простоем. Я придумал навесить на старую таблицу триггер, который будет обновлять или добавлять данные в новой таблице при изменении данных в старой и после этого пакетно копировать данные из старой таблицы в новую. В таблице есть индексированное поле created_at (дата вставки записи) и я решил использовать его для разбиения строк на пакеты и копировать данные по одному дню. Для того, чтобы процесс можно было распараллелить я создал табличку, в которую записывается дата, данные за которую сейчас переносятся. На момент вставки в эту таблицу берётся advisory lock и освобождается сразу же после вставки. Заодно логируется некоторая элементарная статистика. Процедура выглядит следующим образом:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
create or replace
function public.packages_catchup() returns integer as $$
declare
vResult integer; vCurrentDate date;
vAdvisoryLockID integer := 1984;
vMinDate date := '2017-07-18'; /* select min(created_at) from packages; */
begin
/* In order to hold lock as less as possible we
use advisory locks instead of common locks, because
command locks are always held until end of transaction */
perform pg_advisory_lock(vAdvisoryLockID);
select coalesce(max(day) + interval '1 day', vMinDate)
from public.package_catchup_log
into vCurrentDate;
insert into public.package_catchup_log (day)
values (vCurrentDate);
perform pg_advisory_unlock(vAdvisoryLockID);
insert into public.packages_new
select * from public.packages p
where created_at >= vCurrentDate and
created_at < vCurrentDate + interval '1 day' and
not exists (
select *
from public.packages_new pn
where pn.id = p.id and
created_at >= vCurrentDate and
created_at < vCurrentDate + interval '1 day'
);
get diagnostics vResult := ROW_COUNT;
raise warning 'public.packages_catchup(): % rows have been moved', vResult;
update public.package_catchup_log
set rows_count = vResult,
dt_end = clock_timestamp()
where day = vCurrentDate;
return vResult;
end;
$$ language plpgsql;
Проблема в том, что если запустить процедуру в двух параллельных транзакциях, одна из транзакций начинает висеть в ожидании, пока не закончится первая. И судя по pg_locks проблема не в advisory locks. Я не понимаю, откуда вообще берётся блокировка при вставке. Может, знающие умы что-нибудь дельное посоветуют? Где и что я упустил?
