DBA: Когда почти закончился serial
«Шеф, всё пропало, у нас serial на мегатаблице почти закончился!» — а это значит, что либо вы его неаккуратно накрутили сами, либо у вас действительно данных столько, что разрядности integer-столбца уже не хватает для вашей большой и активной таблицы в PostgreSQL-базе.
Да и столбец этот не простой, а целый PRIMARY KEY, на который еще и ряд других немаленьких таблиц по FOREIGN KEY завязан. А еще и приложение останавливать совсем не хочется, ибо клиентам 24×7 обещано…
В общем, надо как-то с минимальными блокировками увеличить размер PK-поля в большой таблице, на которое многое завязано.
Организуем небольшой тестовый полигон:
CREATE TABLE tblpk(
pk
serial
PRIMARY KEY
, valx
integer
);
INSERT INTO tblpk(valx)
SELECT generate_series(1, 1e6);
CREATE TABLE tblfk(
fk
integer
REFERENCES tblpk
, valy
integer
);
INSERT INTO tblfk(fk, valy)
SELECT (random() * (1e6 - 1))::integer + 1, generate_series(1, 1e6);
-- не забываем, что для FK нужно создавать индекс "вручную"
CREATE INDEX ON tblfk(fk);Подготовительные работы
Первую часть работы можно сделать без наложения каких-либо блокировок вообще.
Добавляем новое поле:
ALTER TABLE tblpk ADD COLUMN _pk bigint;
ALTER TABLE tblfk ADD COLUMN _fk bigint;Универсальный копирующий триггер
Чтобы для всех добавляемых и изменяемых записей состояние нового и старого полей у нас не разбегалось, повесим на таблицу копирующий триггер — на вставку новой записи или изменение отслеживаемого поля: BEFORE INSERT OR UPDATE OF .
Ровно такую же задачу нам придется решать и для таблицы tblfk, поэтому сразу напишем триггерную функцию, которую можно будет универсально применять на любой таблице, использовав немного SQL-магии:
CREATE OR REPLACE FUNCTION copy_fld() RETURNS trigger AS $$
DECLARE
fld_src text := quote_ident(TG_ARGV[0]); -- имя исходного поля
fld_dst text := quote_ident(TG_ARGV[1]); -- имя целевого поля
BEGIN
EXECUTE $q$ -- собираем тело запроса как текст
SELECT
(
json_populate_record( -- наполняем запись данными из JSON
$1 -- NEW
, json_build_object( -- {[fld_dst] : NEW[fld_src]}::json
'$q$ || fld_dst || $q$'
, $1.$q$ || fld_src || $q$::text
)
)
).* -- "разворачиваем" record по столбцам
$q$
USING NEW -- используем NEW в качестве $1-аргумента
INTO NEW; -- результат складываем обратно в NEW
RETURN NEW; -- не забываем вернуть NEW, иначе изменения не применятся
END $$ LANGUAGE plpgsql;Теперь мы можем передать синхронизируемые поля как аргументы триггера — разные для каждой из таблиц:
CREATE TRIGGER copy BEFORE INSERT OR UPDATE OF pk
ON tblpk
FOR EACH ROW
EXECUTE PROCEDURE copy_fld('pk', '_pk'); -- откуда/куда
CREATE TRIGGER copy BEFORE INSERT OR UPDATE OF fk
ON tblfk
FOR EACH ROW
EXECUTE PROCEDURE copy_fld('fk', '_fk');Массовое обновление записей
Самый простой вариант — обновить значение добавленного поля во всех уже существующих записях за один запрос:
UPDATE tblpk SET _pk = pk WHERE _pk IS NULL;
UPDATE tblfk SET _fk = fk WHERE _fk IS NULL;Он же — самый проблемный, поскольку повлечет за собой возникновение длительных блокировок всех запросов, которые тоже захотят что-то изменить в этих записях.
Лучше всего воспользоваться сегментным обновлением, как это описано в статье «PostgreSQL Antipatterns: обновляем большую таблицу под нагрузкой». В результате единый UPDATE превратится в серию быстрых запросов, которые отлично садятся на индекс первичного ключа:
UPDATE
tblpk
SET
_pk = pk
WHERE
pk BETWEEN $1 AND $1 + 999 AND -- перебираем сегменты значений по 1K
_pk IS NULL;Создаем новый индекс
В неблокирующем режиме создаем индекс, который будет выполнять роль нового первичного ключа:
CREATE UNIQUE INDEX CONCURRENTLY _pk ON tblpk(_pk); -- индекс под новый PK
CREATE INDEX CONCURRENTLY _fk ON tblfk(_fk); -- индекс под новый FKВ принципе, индексы можно было создать и раньше, но тогда все наши UPDATE писали бы еще и в него, поэтому работали бы существенно дольше.
Быстрая неблокирующая* конвертация
Сначала поймем, как примерно должен выглядеть наш целевой результат в самом простом варианте:
снимаем все autovacuum/autoanalyze, которые блокируют наши таблицы
Эти процессы запустятся с очень большой вероятностью практически сразу, поскольку мы
UPDATE'нули все записи в каждой из таблиц. Если мы не снимем их и накладываемые ими блокировки, все нашиALTER TABLEбудут ждать получения блокировки сами (Access Exclusive), а за ними будет копиться очередь всех остальных запросов, дажеSELECT(Access Share) по этим таблицам.блокируем таблицы в монопольном режиме
Если этого не сделать, какой-нибудь настырный
SELECTиз параллельного подключения вполне может вклиниться между нашимиALTER TABLE, что опять-таки приведет к длительным блокировкам.Пытаемся наложить блокировку в
NOWAIT-режиме, чтобы при наличии активногоSELECT-запроса (их-то мы не снимали) по любой из таблиц не висеть и ждать его, создавая за собой очередь, а отвалиться сразу.модифицируем последовательность: привязываем ее к новому столбцу (
OWNED BY) и снимаем ограничение на максимальное значение (NO MAXVALUE)модифицируем основную таблицу:
удаляем старый столбец каскадно, что заодно удалит и ненужный нам более copy-триггер, старый первичный ключ вместе с индексом и все смотрящие на него внешние ключи
переименовываем новый столбец в старый
назначаем DEFAULT для нового столбца именно здесь, поскольку назначение раньше могло бы привести к двойному выполнению выражения
создаем новый первичный ключ с использованием заранее подготовленного уникального индекса, что заодно этот индекс и переименует
аналогично модифицируем связанную таблицу в чуть другом порядке:
удаляем и переименовываем столбцы
восстанавливаем внешний ключ в
NOT VALID-режиме без фактической проверки уже содержащихся в таблице данныхвосстанавливаем имя индекса под внешним ключом
BEGIN;
-- снимаем все процессы autovacuum/autoanalyze по нашим таблицам
SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity sa
WHERE
CASE
WHEN backend_type = 'autovacuum worker' THEN
EXISTS(
SELECT
NULL
FROM
pg_locks
WHERE
locktype = 'relation' AND
relation = ANY(ARRAY['tblpk', 'tblfk']::regclass[])
)
END;
-- сразу блокируем все таблицы, чтобы никто не влез
LOCK TABLE tblpk, tblfk IN ACCESS EXCLUSIVE MODE NOWAIT;
-- sequence
ALTER SEQUENCE tblpk_pk_seq OWNED BY tblpk._pk;
ALTER SEQUENCE tblpk_pk_seq NO MAXVALUE;
-- tblpk
ALTER TABLE tblpk
DROP COLUMN pk CASCADE; -- сносит заодно copy-триггер, PK и все FK
ALTER TABLE tblpk
RENAME COLUMN _pk TO pk;
ALTER TABLE tblpk
ALTER COLUMN pk SET DEFAULT nextval('tblpk_pk_seq');
ALTER TABLE tblpk
ADD CONSTRAINT tblpk_pkey PRIMARY KEY USING INDEX _pk;
-- tblfk
ALTER TABLE tblfk
DROP COLUMN fk CASCADE;
ALTER TABLE tblfk
RENAME COLUMN _fk TO fk;
ALTER TABLE tblfk
ADD CONSTRAINT tblfk_fk_fkey
FOREIGN KEY(fk)
REFERENCES tblpk
NOT VALID; -- без проверки ограничения по существующим данным
ALTER INDEX _fk RENAME TO tblfk_fk_fkey;
COMMIT;Все эти действия происходят единым куском под общей блокировкой, поэтому, благодаря транзакционности DDL в PostgreSQL, либо успешно выполнятся целиком, либо целиком же — нет. Однако, за счет того, что тут нет ни одной длительной операции, весь скрипт должен отработать за минимальное время.
При этом все внешние ключи будут пересозданы с признаком «невалидности», хотя все данные под ними заведомо корректны. Но если очень хочется отвалидировать FK настолько сильно, что даже готовы на ExclusiveLock, то делаем так:
ALTER TABLE tblfk
VALIDATE CONSTRAINT tblfk_fk_fkey;Что мы забыли?
Приведенный выше код вполне работает, но только в простейших случаях.
Связанные объекты
В базе достаточно просто увидеть, на кого ссылается сама таблица, но весьма сложно обнаружить, кто ссылается на нее. Чтобы ничего не пропустить, напишем запрос, который их все найдет и подготовит скрипт для дальнейшей замены полей.
Тут мы встречаем нескольких персонажей, чьи имена мы нигде ранее не упоминали, не задавали, и потому знать не можем:
tblpk_pkey— имя ограничения первичного ключаtblfk_fk_fkey— имя ограничения внешнего ключаtblpk_pk_seq— имя serial-последовательности
Собственно, их имена могли быть как присвоены автоматически самим PostgreSQL, так и заданы владельцами базы — поэтому достоверно ориентироваться на то, что они окажутся именно такими, мы не можем.
Сложные индексы
Аналогично, мы исходили из предположения, что индексы у нас самые простые, из единственного поля и без всяких условий. Но FK-индекс запросто может иметь вид tblfk(fk) WHERE fk IS NOT NULL, чтобы NULL-строки не замусоривали его, а PK включать в себя и другие поля, кроме serial.
Действия внешних ключей
Внешние ключи также могут быть определены существенно более сложно, чем в нашей модели — там может оказаться что-то вроде MATCH PARTIAL INITIALLY DEFERRED или ON DELETE SET NULL ON UPDATE RESTRICT.
Триггеры
Удалив каскадно старый столбец, мы снесли также и copy-триггер. А что если он был не один на этом поле?…
Имена и комментарии
Имя индекса внешнего ключа мы восстанавливали «по наитию», но нет абсолютно никакой гарантии, что оно совпадает с именем FK-ограничения.
А еще мы забыли восстановить комментарии объектов, которые могли быть наложены через COMMENT ON.
Скрипт миграции
По этим причинам самый правильный вариант — использовать генерирующий запрос, который сформирует скрипт миграции для всех связанных таблиц. Чтобы понять, почему же он получается настолько сложным, представим связи наших объектов графически:
sequence ссылается на поле через
OWNED BY, а оно обратно черезDEFAULTиндексы и триггеры ссылаются на поле напрямую
FK-constraint связывает поля пары таблиц и уникальный индекс на ведущей таблице
и все это может быть откомментировано
Создадим для теста максимально-проблемную для переноса ситуацию — сложные имена таблиц и полей, комментарии, триггеры и «хитрые» именованные FK:
CREATE TABLE "1st table"(
"primary key col"
serial
PRIMARY KEY
, valx
integer
);
COMMENT ON COLUMN "1st table"."primary key col"
IS 'col-comment';
INSERT INTO "1st table"(valx)
SELECT generate_series(1, 1e5);
CREATE TABLE "2nd table"(
fk
integer
CONSTRAINT "FK-name" REFERENCES "1st table"
ON UPDATE SET NULL
ON DELETE RESTRICT
, valy
integer
);
COMMENT ON CONSTRAINT "FK-name" ON "2nd table"
IS 'con-comment';
INSERT INTO "2nd table"(fk, valy)
SELECT (random() * (1e5 - 1))::integer + 1, generate_series(1, 1e5);
CREATE INDEX "FK-idx-name" ON "2nd table"(fk);
COMMENT ON INDEX "FK-idx-name"
IS 'idx-comment';
CREATE OR REPLACE FUNCTION tmp() RETURNS trigger AS $$
BEGIN
RAISE NOTICE 'NEW : %', NEW::text;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER tmp AFTER INSERT OR UPDATE OF "primary key col"
ON "1st table"
FOR EACH ROW
EXECUTE PROCEDURE tmp();
COMMENT ON TRIGGER tmp ON "1st table"
IS 'trg-comment';Ну, а теперь дело за малым! Вот наш скрипт:
Скрипт расширения serial → bigserial-- $1 : '"1st table"' - с кавычками!
-- $2 : 'primary key col' - без кавычек!
WITH src(rel, fld) AS (
VALUES($1::regclass, $2::name)
)
, fld AS (
SELECT
*
FROM
src
JOIN
pg_attribute at
ON (at.attrelid, at.attname) = (src.rel, src.fld)
)
, idx AS (
SELECT
idx.*
FROM
fld
JOIN
pg_index idx
ON indrelid = attrelid AND
indkey::smallint[] && ARRAY[attnum]
)
, con AS (
SELECT
CASE contype
WHEN 'p' THEN attnum
WHEN 'f' THEN conkey[array_position(confkey, attnum)]
END idkey
, con.*
FROM
fld
JOIN
pg_constraint con
ON (conrelid = attrelid AND conkey && ARRAY[attnum]) OR
(confrelid = attrelid AND confkey && ARRAY[attnum])
)
-- столбцы, входящие в PK или FK
, colkey AS (
SELECT
*
, attrelid::regclass::text _attrel
, '_' || md5(attname) _attname
, quote_ident(attname) _qiattname
, replace(col_description(attrelid, attnum), '''', '''''') dsccol
FROM
con
INNER JOIN
pg_attribute at
ON (attrelid, attnum) = (conrelid, idkey)
WHERE
atttypid <> 'bigint'::regtype
)
, code_col AS (
SELECT
string_agg(
$$-- $$ || _attrel || $$
ALTER TABLE $$ || _attrel || $$
ADD COLUMN $$ || _attname || $$ bigint;
$$ ||
CASE
WHEN dsccol IS NOT NULL THEN
$$COMMENT ON COLUMN $$ || _attrel || '.' || _attname || $$
IS '$$ || dsccol || $$';
$$
ELSE ''
END ||
$$CREATE TRIGGER copy
BEFORE INSERT OR UPDATE OF $$ || _qiattname || $$
ON $$ || _attrel || $$
FOR EACH ROW
EXECUTE PROCEDURE copy_fld('$$ || attname || $$', '$$ || _attname || $$');
UPDATE $$ || _attrel || $$ SET $$ || _attname || $$ = $$ || _qiattname || $$ WHERE $$ || _attname || $$ IS NULL; -- лучше сегментно!!!
$$
, ''
) code
FROM
colkey
)
-- индексы
, indkey AS (
SELECT
*
, quote_ident('_' || md5(sch || '.' || rel || '.' || idxname)) _idxname
FROM
(
SELECT
pg_get_indexdef(indexrelid) def
, cli.relnamespace::regnamespace::text sch
, idx.indrelid::regclass::text rel
, quote_ident(cli.relname) idxname
, replace(obj_description(cli.oid, 'pg_class'), '''', '''''') dscidx
, *
FROM
colkey
JOIN
pg_index idx
ON indrelid = attrelid AND
indkey::smallint[] && ARRAY[attnum]
JOIN
pg_class cli
ON cli.oid = idx.indexrelid
) T
)
, code_idx AS (
SELECT
string_agg(
E'-- ' || idxname || E'\n' ||
regexp_replace(
regexp_replace(
def
, E'(CREATE(?: UNIQUE)? INDEX ).*?( ON ).*?( USING )'
, E'\\1CONCURRENTLY ' || _idxname || E'\n ON ' || sch || '.' || rel || E'\n USING '
)
, E'(USING \\S+ \\(.*)' || _qiattname || E'(.*\\))'
, E'\\1' || _attname || E'\\2'
, 'g'
) || E';\n'
|| CASE
WHEN dscidx IS NOT NULL THEN
$$COMMENT ON INDEX $$ || _idxname || $$
IS '$$ || dscidx || $$';
$$
ELSE ''
END
, ''
) code
FROM
indkey
)
-- тфблицы
, code_rel AS (
SELECT
$q$-- зачищаем мешающие autovacuum
SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity sa
WHERE
CASE
WHEN backend_type = 'autovacuum worker' THEN
EXISTS(
SELECT
NULL
FROM
pg_locks
WHERE
locktype = 'relation' AND
relation = ANY('$q$ || array_agg(rel)::text || $q$'::regclass[])
)
END;
-- блокируем все таблицы
LOCK TABLE $q$ || string_agg(rel, ', ') || $q$ IN ACCESS EXCLUSIVE MODE NOWAIT;
$q$ code
FROM
(
SELECT DISTINCT
_attrel rel
FROM
colkey
) T
)
-- последовательность
, seqkey AS (
SELECT
pg_get_serial_sequence(attrelid::regclass::text, attname) seq
, *
FROM
colkey
)
, code_seq AS (
SELECT
$q$ALTER SEQUENCE $q$ || seq || $q$
OWNED BY $q$ || _attrel || '.' || _attname || $q$;
ALTER SEQUENCE $q$ || seq || $q$
NO MAXVALUE;
$q$
FROM
seqkey
WHERE
seq IS NOT NULL
)
-- столбцы
, code_col_tx AS (
SELECT
string_agg(
$$-- $$ || _attrel || $$
ALTER TABLE $$ || _attrel || $$
DROP COLUMN $$ || _qiattname || $$ CASCADE;
ALTER TABLE $$ || _attrel || $$
RENAME COLUMN $$ || _attname || $$ TO $$ || _qiattname || $$;
$$ ||
CASE
WHEN adsrc IS NOT NULL THEN
$$ALTER TABLE $$ || _attrel || $$
ALTER COLUMN $$ || _qiattname || $$
SET DEFAULT $$ || adsrc || $$;
$$
ELSE ''
END
, ''
) code
FROM
colkey
LEFT JOIN
pg_attrdef ad
ON (adrelid, adnum) = (attrelid, attnum)
)
-- индексы
, code_idx_tx AS (
SELECT
string_agg(
$$ALTER INDEX $$ || _idxname || $$
RENAME TO $$ || idxname || $$;
$$
, '')
FROM
indkey
)
-- ключи
, code_con_tx AS (
SELECT
string_agg(
(
SELECT
string_agg(
'ALTER TABLE ' || conrelid::regclass::text || E'\n ADD ' ||
CASE con.contype
WHEN 'p' THEN
'PRIMARY KEY USING INDEX ' || idxname
WHEN 'u' THEN
'UNIQUE USING INDEX ' || idxname
WHEN 'f' THEN
'CONSTRAINT ' || quote_ident(con.conname) || ' ' || pg_get_constraintdef(con.oid) || CASE WHEN pg_get_constraintdef(con.oid) !~* 'NOT VALID' THEN E'\n NOT VALID' ELSE '' END
END || E';\n' ||
CASE
WHEN obj_description(con.oid, 'pg_constraint') IS NOT NULL THEN
$$COMMENT ON CONSTRAINT $$ || quote_ident(conname) || $$ ON $$ || conrelid::regclass::text || $$
IS '$$ || replace(obj_description(con.oid, 'pg_constraint'), '''', '''''') || $$';
$$
ELSE ''
END
, ''
ORDER BY
CASE con.contype
WHEN 'p' THEN 0
WHEN 'u' THEN 1
WHEN 'f' THEN 2
END
)
FROM
pg_constraint con
WHERE
conindid = indexrelid
)
, ''
) code
FROM
indkey
)
-- триггеры
, trgkey AS (
SELECT
pg_get_triggerdef(trg.oid) def
, replace(obj_description(trg.oid, 'pg_trigger'), '''', '''''') dsctrg
, *
FROM
colkey
JOIN
pg_trigger trg
ON tgrelid = attrelid AND
tgattr::smallint[] && ARRAY[attnum]
WHERE
NOT tgisinternal
)
, code_trg AS (
SELECT
string_agg(
def || E';\n'
|| CASE
WHEN dsctrg IS NOT NULL THEN
$$COMMENT ON TRIGGER $$ || quote_ident(tgname) || $$ ON $$ || _attrel || $$
IS '$$ || dsctrg || $$';
$$
ELSE ''
END
, ''
) code
FROM
trgkey
)
SELECT
E'-- столбцы\n' ||
(TABLE code_col) ||
E'\n-- индексы\n' ||
(TABLE code_idx) ||
E'\nBEGIN;\n' ||
regexp_replace(
(TABLE code_rel) ||
E'\n-- последовательность\n' ||
(TABLE code_seq) ||
E'\n-- столбцы\n' ||
(TABLE code_col_tx) ||
E'\n-- индексы\n' ||
(TABLE code_idx_tx) ||
E'\n-- ключи\n' ||
(TABLE code_con_tx) ||
E'\n-- триггеры\n' ||
(TABLE code_trg)
, E'^(.)'
, E' \\1'
, 'gm'
) ||
E'COMMIT;\n';Надеюсь, когда-то этот скрипт пригодится и вам.
