Справочная информация
На моем сервере Postgres у меня есть несколько таблиц, к которым постоянно обращаются приложения бизнес-аналитики, поэтому в идеале они должны оставаться доступными большую часть времени. Таблицы очищаются и перезагружаются нашим конвейером ETL каждую ночь (я знаю... из-за некоторых устаревших настроек я не могу использовать здесь добавочные обновления). Процесс загрузки занимает довольно много времени и пока не является пуленепробиваемым.
Чтобы повысить доступность таблиц, я решил использовать промежуточные таблицы, чтобы сначала загрузить нижестоящие данные из нашего конвейера ETL, и, если они будут успешно загружены, скопировать данные в фактические рабочие таблицы.
Вот функция копирования, которую я создал для этой цели:
CREATE OR REPLACE FUNCTION guarded_copy(src text, dest text) RETURNS void AS
$$
DECLARE
c1 INTEGER;
c2 INTEGER;
BEGIN
EXECUTE FORMAT('SELECT COUNT(*) FROM %I', src) INTO c1;
EXECUTE FORMAT('SELECT COUNT(*) FROM %I', dest) INTO c2;
IF c1>=c2 THEN
EXECUTE FORMAT('TRUNCATE TABLE %I CASCADE;', dest);
EXECUTE FORMAT('INSERT INTO %I SELECT * FROM %I;', dest, src);
END IF;
END
$$
LANGUAGE plpgsql VOLATILE;
Идея состоит в том, чтобы обрезать таблицу dest
и загрузить в нее данные из таблицы src
, если таблица src
(промежуточная таблица) содержит больше строк, чем таблица dest
(фактическая производственная таблица). И это действительно работает.
Обратите внимание, что фактическая рабочая таблица (dest
) имеет ограничения и индексы, в то время как промежуточная таблица (src
) настроена без индексов или ограничений, чтобы ускорить процесс загрузки из ETL.
Проблема
Проблема с моей функцией выше заключается в том, что копирование данных может быть очень дорогим из-за индексов и ограничений в таблице dest
.
Вопрос
- Какой лучший способ достичь той же цели?
- Я думаю об удалении/отключении индекса на
dest
перед этапом копирования данных и сразу же добавляю его обратно. Как это сделать в функции SQL? - Я также думаю о том, чтобы поменять местами две таблицы, переименовав их, но для этого требуется, чтобы индексы одной таблицы копировались в другую. Как я могу сделать это внутри функции?
РЕДАКТИРОВАТЬ 1
Версия Postgres:
PostgreSQL 9.2.6 на x86_64-unknown-linux-gnu, скомпилированная gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-разрядная версия
Ограничения таблицы:
В таблице dest
у меня есть (уникальный) первичный ключ для столбца id
и индексы для столбцов с метками времени.
РЕДАКТИРОВАТЬ 2
Этот вопрос и этот вопрос действительно помог. Для варианта 3 выше я думаю, что приведенный ниже код близок к тому, что я хочу.
CREATE OR REPLACE FUNCTION guarded_swap(src text, dest text) RETURNS void AS
$$
DECLARE
c1 INTEGER;
c2 INTEGER;
_query TEXT;
BEGIN
EXECUTE FORMAT('SELECT COUNT(*) FROM %I', src) INTO c1;
EXECUTE FORMAT('SELECT COUNT(*) FROM %I', dest) INTO c2;
IF c1>=c2 THEN
-- create indexes in src table
FOR _query IN
SELECT FORMAT('%s;', REPLACE(pg_get_indexdef(ix.indexrelid), dest, src))
FROM pg_class t, pg_class i, pg_index ix
WHERE t.oid = ix.indrelid
AND i.oid = ix.indexrelid
AND t.relkind = 'r' and i.relkind = 'i'
AND t.oid= dest::regclass
ORDER BY
t.relname, i.relname
LOOP
EXECUTE _query;
END LOOP;
-- drop indexes in dest table
FOR _query IN
SELECT FORMAT('DROP INDEX %s;', i.relname)
FROM pg_class t, pg_class i, pg_index ix
WHERE t.oid = ix.indrelid
AND i.oid = ix.indexrelid
AND t.relkind = 'r' and i.relkind = 'i'
AND t.oid= dest::regclass
ORDER BY
t.relname, i.relname
LOOP
EXECUTE _query;
END LOOP;
-- create constraints in src table
FOR _query IN
SELECT
FORMAT ('ALTER TABLE %s ADD CONSTRAINT %s %s;', src,
REPLACE(conname, dest, src),
pg_get_constraintdef(oid))
FROM pg_constraint
WHERE contype = 'p' AND conrelid = dest::regclass
LOOP
EXECUTE _query;
END LOOP;
-- drop all constraints in dest table
FOR _query IN
SELECT
FORMAT ('ALTER TABLE %s DROP CONSTRAINT IF EXISTS %s;', dest, conname)
FROM pg_constraint
WHERE conrelid = dest::regclass
LOOP
EXECUTE _query;
END LOOP;
-- swap the table names
EXECUTE FORMAT('ALTER TABLE %I RENAME TO %I;', dest, CONCAT(dest, '_old'));
EXECUTE FORMAT('ALTER TABLE %I RENAME TO %I;', src, dest);
EXECUTE FORMAT('ALTER TABLE %I RENAME TO %I;', CONCAT(dest, '_old'), src);
END IF;
END
$$
LANGUAGE plpgsql VOLATILE;
РЕДАКТИРОВАТЬ 3
Еще одна мысль: PK и FK могут быть ненужными для таблиц, которые используются только для целей аналитики. Таким образом, индексы являются единственной проблемой здесь.
CREATE OR REPLACE FUNCTION guarded_swap(src text, dest text) RETURNS void AS
$$
DECLARE
c1 INTEGER;
c2 INTEGER;
_idx_name TEXT;
_query TEXT;
_qs TEXT[];
BEGIN
EXECUTE FORMAT('SELECT COUNT(*) FROM %I', src) INTO c1;
EXECUTE FORMAT('SELECT COUNT(*) FROM %I', dest) INTO c2;
IF c1>=c2 THEN
-- drop indexes in dest table
FOR _idx_name, _query IN
SELECT i.relname, FORMAT('%s;', pg_get_indexdef(ix.indexrelid))
FROM pg_class t, pg_class i, pg_index ix
WHERE t.oid = ix.indrelid
AND i.oid = ix.indexrelid
AND t.relkind = 'r' and i.relkind = 'i'
AND t.oid= dest::regclass
ORDER BY
t.relname, i.relname
LOOP
_qs := array_append(_qs, _query);
EXECUTE FORMAT('DROP INDEX IF EXISTS %s;', _idx_name);
END LOOP;
-- swap the table names
EXECUTE FORMAT('ALTER TABLE %I RENAME TO %I;', dest, CONCAT(dest, '_old'));
EXECUTE FORMAT('ALTER TABLE %I RENAME TO %I;', src, dest);
EXECUTE FORMAT('ALTER TABLE %I RENAME TO %I;', CONCAT(dest, '_old'), src);
-- build indexes in dest table
FOREACH _query IN ARRAY _qs
LOOP
EXECUTE _query;
END LOOP;
END IF;
END
$$
LANGUAGE plpgsql VOLATILE;
table(dest) has constraints and indexes
: какие ограничения? И, очевидно, вам нужно предоставить свою версию Postgres. - person Erwin Brandstetter   schedule 15.04.2015dest
у меня есть (уникальный) первичный ключ в столбцеid
и индекс в столбце меток времени. -- Просто отредактировал вопрос. - person xiaolong   schedule 15.04.2015