Агар шумо кор карда истода бошед pgbouncer дар ҳолати транзаксия, он гоҳ базаи маълумот боварӣ дорад, ки ин пайвастшавӣ ҳоло ҳам фаъол аст ва дар он ин ҷадвали муваққатӣ ҳанӯз вуҷуд дорад.
Аз ин рӯ, кӯшиши аз нав сохтани он аз пайвасти дигар ба pgbouncer боиси хатогӣ мегардад. Аммо бо истифода аз ин метавон канорагирӣ кард CREATE TEMPORARY TABLE IF NOT EXISTS x.
Дуруст аст, ки ба ҳар ҳол ин корро накунед, зеро он гоҳ шумо метавонед "ногаҳон" маълумоти боқимондаи "соҳиби қаблӣ"-ро дар он ҷо пайдо кунед. Ба ҷои ин, беҳтар аст, ки дастурро хонед ва бубинед, ки ҳангоми сохтани ҷадвал илова кардан мумкин аст ON COMMIT DROP - яъне вақте ки муомилот анҷом мешавад, ҷадвал ба таври худкор нест карда мешавад.
Аммо азбаски ҷадвали муваққатӣ то ҳол ҷадвали "қариб оддӣ" аст, онро дар нусхабардорӣ низ сохтан мумкин нест. Ҳадди аққал ҳоло, гарчанде ки ямоқи мувофиқ муддати тӯлонӣ паҳн шудааст.
1.2. ЉАДВАЛИ БОШАД
Аммо чӣ бояд кард, масалан, агар шумо як намуди мураккаби ETL дошта бошед, ки онро дар як транзаксия иҷро кардан ғайриимкон аст, аммо шумо ба ҳар ҳол доред pgbouncer дар ҳолати транзаксия? ..
Ё ҷараёни маълумот он қадар калон аст, ки Дар як пайваст фарохмаҷрои кофӣ нест аз пойгоҳи додаҳо (хондан, як раванд барои як CPU)?..
ба ON COMMIT DROP Ман аллакай дар боло навишта будам, он тавлид мекунад DROP TABLE, балки бо ON COMMIT DELETE ROWS вазъият ҷолибтар аст - он дар ин ҷо тавлид мешавад TRUNCATE TABLE.
Азбаски аксар вақт индексҳои зарурӣ бо индексҳои ҷадвали мақсаднок мувофиқат мекунанд, шумо метавонед танҳо нависед LIKE target_table INCLUDING INDEXES.
Агар ба шумо хам лозим бошад DEFAULT-арзишҳо (масалан, барои пур кардани арзишҳои асосии калидӣ), шумо метавонед истифода баред LIKE target_table INCLUDING DEFAULTS. Ё оддӣ - LIKE target_table INCLUDING ALL — нусхабардории пешфарзҳо, индексҳо, маҳдудиятҳо,...
Аммо дар ин ҷо шумо бояд фаҳмед, ки агар шумо эҷод кунед Ҷадвалро фавран бо индексҳо ворид кунед, пас боркунии маълумот вақти зиёдтар мегирадАгар шумо аввал ҳама чизро пур кунед ва танҳо пас аз он индексҳоро ҷамъ кунед - бубинед, ки ин чӣ гуна ин корро мекунад. pg_dump.
-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
BEGIN;
-- создаем временную таблицу с импортируемыми данными
CREATE TEMPORARY TABLE tmp(
LIKE dst INCLUDING INDEXES -- по образу и подобию, вместе с индексами
) ON COMMIT DROP; -- за рамками транзакции она нам не нужна
-- быстро-быстро вливаем новый образ через COPY
COPY tmp FROM STDIN;
-- ...
-- .
-- удаляем отсутствующие
DELETE FROM
dst D
USING
dst X
LEFT JOIN
tmp Y
USING(pk1, pk2) -- поля первичного ключа
WHERE
(D.pk1, D.pk2) = (X.pk1, X.pk2) AND
Y IS NOT DISTINCT FROM NULL; -- "антиджойн"
-- обновляем оставшиеся
UPDATE
dst D
SET
(f1, f2, f3) = (T.f1, T.f2, T.f3)
FROM
tmp T
WHERE
(D.pk1, D.pk2) = (T.pk1, T.pk2) AND
(D.f1, D.f2, D.f3) IS DISTINCT FROM (T.f1, T.f2, T.f3); -- незачем обновлять совпадающие
-- вставляем отсутствующие
INSERT INTO
dst
SELECT
T.*
FROM
tmp T
LEFT JOIN
dst D
USING(pk1, pk2)
WHERE
D IS NOT DISTINCT FROM NULL;
COMMIT;
-- целевые таблицы
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);
-- таблицы с историей изменений
CREATE TABLE kladr$log(
ro kladr, -- тут лежат целые образы записей старой/новой
rn kladr
);
CREATE TABLE kladr_house$log(
ro kladr_house,
rn kladr_house
);
-- общая функция логирования изменений
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
dst varchar = TG_TABLE_NAME || '$log';
stmt text = '';
BEGIN
-- проверяем необходимость логгирования при обновлении записи
IF TG_OP = 'UPDATE' THEN
IF NEW IS NOT DISTINCT FROM OLD THEN
RETURN NEW;
END IF;
END IF;
-- создаем запись лога
stmt = 'INSERT INTO ' || dst::text || '(ro,rn)VALUES(';
CASE TG_OP
WHEN 'INSERT' THEN
EXECUTE stmt || 'NULL,$1)' USING NEW;
WHEN 'UPDATE' THEN
EXECUTE stmt || '$1,$2)' USING OLD, NEW;
WHEN 'DELETE' THEN
EXECUTE stmt || '$1,NULL)' USING OLD;
END CASE;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Акнун мо метавонем пеш аз оғози ҳамоҳангсозӣ триггерҳоро истифода барем (ё онҳоро тавассути ALTER TABLE ... ENABLE TRIGGER ...):
CREATE TRIGGER log
AFTER INSERT OR UPDATE OR DELETE
ON kladr
FOR EACH ROW
EXECUTE PROCEDURE diff$log();
CREATE TRIGGER log
AFTER INSERT OR UPDATE OR DELETE
ON kladr_house
FOR EACH ROW
EXECUTE PROCEDURE diff$log();
Ва он гоҳ мо оромона тамоми тағиротҳои ба мо лозимиро аз ҷадвалҳои гузоришҳо хориҷ мекунем ва онҳоро тавассути коркардкунандагони иловагӣ иҷро мекунем.
3.3. Воридоти маҷмӯаҳои алоқаманд
Дар боло мо ҳолатҳоеро баррасӣ кардем, ки сохторҳои додаҳои манбаъ ва макони таъинот якхела бошанд. Аммо чӣ мешавад, агар боргузорӣ аз системаи беруна формати аз сохтори нигоҳдории пойгоҳи додаи мо фарқкунанда дошта бошад?
CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
client_inn inn
, client_name "name"
FROM
invoice_import;
-- проставляем в таблице импорта ID уже существующих записей
UPDATE
client_import T
SET
client_id = D.client_id
FROM
client D
WHERE
T.inn = D.inn; -- unique key
-- вставляем отсутствовавшие записи и проставляем их ID
WITH ins AS (
INSERT INTO client(
inn
, name
)
SELECT
inn
, name
FROM
client_import
WHERE
client_id IS NULL -- если ID не проставился
RETURNING *
)
UPDATE
client_import T
SET
client_id = D.client_id
FROM
ins D
WHERE
T.inn = D.inn; -- unique key
-- проставляем ID клиентов у записей счетов
UPDATE
invoice_import T
SET
client_id = D.client_id
FROM
client_import D
WHERE
T.client_inn = D.inn; -- прикладной ключ
Дар асл, ҳама чиз аст invoice_import Ҳоло мо майдони тамосро пур кардаем client_id, ки бо он мо ҳисобнома-фактураро ворид мекунем.