🥇DBA. գրագետ կազմակերպել համաժամացման և ներմուծման

Տվյալների մեծ հավաքածուների բարդ մշակման համար (տարբեր ETL գործընթացներՆերմուծում, փոխարկում և համաժամացում արտաքին աղբյուրի հետ) հաճախ անհրաժեշտություն կա ժամանակավորապես «հիշել» և անմիջապես արագ մշակել ինչ-որ ծավալուն բան.

Այս տեսակի տիպիկ առաջադրանքը սովորաբար հնչում է այսպես. "Հենց այստեղ հաճախորդի բանկից բեռնաթափված հաշվապահական հաշվառման բաժին վերջին ստացված վճարումները, դուք պետք է արագ վերբեռնեք դրանք կայք և կապեք ձեր հաշիվներին»

Բայց երբ այս «ինչ-որ բանի» ծավալը սկսում է չափվել հարյուրավոր մեգաբայթերով, և ծառայությունը պետք է շարունակի աշխատել տվյալների բազայի հետ 24x7, առաջանում են բազմաթիվ կողմնակի ազդեցություններ, որոնք կկործանեն ձեր կյանքը:

PostgreSQL-ում (և ոչ միայն դրանում) դրանց հետ գործ ունենալու համար կարող եք օգտագործել որոշ օպտիմալացումներ, որոնք թույլ կտան ամեն ինչ մշակել ավելի արագ և ավելի քիչ ռեսուրսների սպառմամբ:

1. Որտեղ առաքել:

Նախ, եկեք որոշենք, թե որտեղ կարող ենք վերբեռնել այն տվյալները, որոնք ցանկանում ենք «մշակել»:

1.1. Ժամանակավոր սեղաններ (ժամանակավոր սեղան)

Սկզբունքորեն, PostgreSQL-ի համար ժամանակավոր աղյուսակները նույնն են, ինչ ցանկացած այլ: Հետեւաբար, սնահավատությունները նման են «Այնտեղ ամեն ինչ պահվում է միայն հիշողության մեջ, և այն կարող է ավարտվել». Բայց կան նաև մի քանի էական տարբերություններ.

Ձեր սեփական «անվանատարածքը» տվյալների բազայի յուրաքանչյուր կապի համար

Եթե երկու կապ փորձեն միանալ միաժամանակ CREATE TABLE x, ապա ինչ-որ մեկը անպայման կստանա ոչ եզակիության սխալ տվյալների բազայի օբյեկտներ.

Բայց եթե երկուսն էլ փորձեն կատարել CREATE TEMPORARY TABLE x, հետո երկուսն էլ նորմալ կանեն, ու բոլորը կստանան ձեր պատճենը սեղաններ. Եվ նրանց միջեւ ոչ մի ընդհանուր բան չի լինի։

«Ինքնաոչնչացում»՝ անջատելիս

Երբ կապը փակ է, բոլոր ժամանակավոր աղյուսակները ինքնաբերաբար ջնջվում են, ուստի ձեռքով DROP TABLE x իմաստ չկա, բացի...

Եթե դուք աշխատում եք pgbouncer գործարքի ռեժիմում, ապա տվյալների բազան շարունակում է հավատալ, որ այս կապը դեռ ակտիվ է, և դրանում այս ժամանակավոր աղյուսակը դեռ գոյություն ունի։

Հետևաբար, այն նորից ստեղծելու փորձը՝ pgbouncer-ի այլ կապից, կհանգեցնի սխալի։ Բայց դա կարելի է շրջանցել՝ օգտագործելով CREATE TEMPORARY TABLE IF NOT EXISTS x.

Ճիշտ է, դա ամեն դեպքում ավելի լավ է չանել, քանի որ այդ դեպքում դուք կարող եք «հանկարծ» գտնել այնտեղ «նախորդ սեփականատիրոջից» մնացած տվյալները: Փոխարենը շատ ավելի լավ է կարդալ ձեռնարկը և տեսնել, որ աղյուսակ ստեղծելիս հնարավոր է ավելացնել ON COMMIT DROP - այսինքն, երբ գործարքն ավարտվի, աղյուսակը ավտոմատ կերպով կջնջվի:

Չկրկնվող

Քանի որ դրանք պատկանում են միայն կոնկրետ կապին, ժամանակավոր աղյուսակները չեն կրկնօրինակվում: Բայց սա վերացնում է տվյալների կրկնակի գրանցման անհրաժեշտությունը կույտ + WAL-ում, այնպես որ ներդիր/ԹԱՐՄԱՑՆԵԼ/ՋՆԵԼ դրա մեջ զգալիորեն ավելի արագ է:

Բայց քանի որ ժամանակավոր աղյուսակը դեռևս «գրեթե սովորական» աղյուսակ է, այն չի կարող ստեղծվել նաև կրկնօրինակի վրա: Գոնե առայժմ, թեեւ համապատասխան կարկատանը վաղուց է շրջանառվում։

1.2. ՉԼՐԱՆՑՎԱԾ ՍԵՂԱՆ

Բայց ինչ պետք է անեք, օրինակ, եթե ունեք ինչ-որ ծանրաբեռնված ETL գործընթաց, որը չի կարող իրականացվել մեկ գործարքի ընթացքում, բայց դուք դեռ ունեք pgbouncer գործարքի ռեժիմում.....

Կամ տվյալների հոսքն այնքան մեծ է, որ Մեկ կապի վրա բավարար թողունակություն չկա տվյալների բազայից (կարդա, մեկ պրոցես մեկ պրոցեսորի համար):

Կամ ինչ-որ վիրահատություններ են ընթանում ասինխրոն կերպով տարբեր կապերի մեջ...

Այստեղ կա միայն մեկ տարբերակ. ժամանակավորապես ստեղծել ոչ ժամանակավոր աղյուսակ. Խոսք, այո: Այն է:

ստեղծել եմ «իմ սեփական» աղյուսակներ՝ մաքսիմալ պատահական անուններով, որպեսզի չհատվեն որևէ մեկի հետ
Էքստրակտ: լրացրեց դրանք արտաքին աղբյուրի տվյալներով
Փոխակերպելփոխարկված, լրացված առանցքային կապող դաշտերում
Բեռ: լցրեց պատրաստի տվյալները թիրախային աղյուսակներում
ջնջված «իմ» աղյուսակները

Իսկ հիմա՝ ճանճը քսուքի մեջ: Իրականում, PostgreSQL-ում բոլոր գրությունները տեղի են ունենում երկու անգամ - առաջինը WAL-ում, այնուհետև՝ աղյուսակի/ինդեքսային մարմինների մեջ: Այս ամենը արվում է ACID-ին աջակցելու և դրանց միջև տվյալների տեսանելիությունը շտկելու համար COMMIT«ընկույզ ու ROLLBACK«զրոյական գործարքներ.

Բայց մեզ սա պետք չէ! Մենք ունենք ամբողջ գործընթացը Կամ լիովին հաջողակ էր, կամ ոչ:. Կարևոր չէ, թե որքան միջանկյալ գործարքներ կլինեն, մենք շահագրգռված չենք «գործընթացը կեսից շարունակել», հատկապես, երբ պարզ չէ, թե որտեղ էր այն:

Դա անելու համար PostgreSQL մշակողները, դեռևս 9.1 տարբերակում, ներկայացրել են այնպիսի բան, ինչպիսին է ՉԳՐԱՆՑՎԱԾ սեղաններ:

Այս նշումով աղյուսակը ստեղծվում է որպես չգրանցված: Չգրանցված աղյուսակներում գրված տվյալները չեն անցնում նախնական գրման մատյանում (տե՛ս Գլուխ 29), ինչը հանգեցնում է նման աղյուսակների աշխատել սովորականից շատ ավելի արագ. Այնուամենայնիվ, նրանք անձեռնմխելի չեն ձախողումից. սերվերի ձախողման կամ վթարային անջատման դեպքում՝ չգրանցված աղյուսակ ինքնաբերաբար կտրված. Բացի այդ, չգրանցված աղյուսակի բովանդակությունը չի կրկնօրինակվում ստրուկ սերվերներին: Չգրանցված աղյուսակում ստեղծված ցանկացած ինդեքս ինքնաբերաբար դառնում է չգրանցված:

Մի խոսքով, դա շատ ավելի արագ կլինի, բայց եթե տվյալների բազայի սերվերը «ընկնի», դա տհաճ կլինի։ Բայց որքա՞ն հաճախ է դա տեղի ունենում, և արդյոք ձեր ETL գործընթացը գիտի՞, թե ինչպես դա ճիշտ ուղղել «մեջտեղից»՝ տվյալների բազան «վերակենդանացնելուց» հետո:

Եթե ոչ, և վերը նշված դեպքը նման է ձերին, օգտագործեք UNLOGGEDբայց երբեք մի միացրեք այս հատկանիշը իրական աղյուսակներում, որից ստացված տվյալները ձեզ համար թանկ են։

1.3. ON COMMIT {ՋՋՆԵԼ ՏՈՂԵՐԸ | ԱՆԿՈՒՄ}

Այս կառուցվածքը թույլ է տալիս աղյուսակ ստեղծելիս նշել ավտոմատ վարքագիծը, երբ գործարքն ավարտվում է:

Մոտ ON COMMIT DROP Վերևում արդեն գրել եմ, դա առաջացնում է DROP TABLE, բայց հետ ON COMMIT DELETE ROWS Իրավիճակն ավելի հետաքրքիր է՝ այստեղ է ստեղծվում TRUNCATE TABLE.

Քանի որ ժամանակավոր աղյուսակի մետա նկարագրությունը պահելու ամբողջ ենթակառուցվածքը ճիշտ նույնն է, ինչ սովորական աղյուսակը, ապա Ժամանակավոր աղյուսակների անընդհատ ստեղծումն ու ջնջումը հանգեցնում է համակարգի աղյուսակների խիստ «ուռեցման»: pg_class, pg_attribute, pg_attrdef, pg_depend,…

Հիմա պատկերացրեք, որ դուք ունեք տվյալների բազայի հետ ուղիղ միացված աշխատող, որը ամեն վայրկյան բացում է նոր գործարք, ստեղծում, լրացնում, մշակում և ջնջում է ժամանակավոր աղյուսակ... Համակարգի աղյուսակներում կուտակված աղբի ավելցուկ կլինի, և սա լրացուցիչ արգելակներ կառաջացնի յուրաքանչյուր գործողության համար:

Ընդհանրապես, մի արեք սա: Այս դեպքում դա շատ ավելի արդյունավետ է CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS դուրս հանել այն գործարքի ցիկլից, այնուհետև յուրաքանչյուր նոր գործարքի սկզբում աղյուսակներն արդեն կան գոյություն կունենա (պահպանել զանգը CREATE), բայց դատարկ կլինի, շնորհիվ TRUNCATE (մենք նույնպես պահպանել ենք նրա զանգը) նախորդ գործարքն ավարտելիս:

1.4. ԼԱՅՔ... ՆԵՐԱՌՅԱԼ...

Սկզբում ես նշեցի, որ ժամանակավոր աղյուսակների տիպիկ օգտագործման դեպքերից մեկը տարբեր տեսակի ներմուծումն է, և մշակողը հոգնած կերպով copy-past է անում թիրախային աղյուսակի դաշտերի ցանկը իր ժամանակավոր հայտարարագրում...

Բայց ծուլությունը առաջընթացի շարժիչն է։ Ահա թե ինչու ստեղծել նոր աղյուսակ «նմուշի հիման վրա» դա կարող է լինել շատ ավելի պարզ.

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Քանի որ այս աղյուսակում դուք կարող եք ստեղծել շատ տվյալներ, դրա մեջ որոնումը երբեք արագ չի լինի: Բայց սրա ավանդական լուծում կա՝ ինդեքսներ։ Եվ, այո, ժամանակավոր աղյուսակը կարող է ունենալ նաև ինդեքսներ.

Քանի որ հաճախ պահանջվող ինդեքսները համընկնում են թիրախային աղյուսակի ինդեքսների հետ, կարող եք պարզապես գրել LIKE target_table INCLUDING INDEXES.

Եթե դուք նույնպես պետք է DEFAULT-արժեքներ (օրինակ, հիմնական բանալիների արժեքները լրացնելու համար), կարող եք օգտագործել LIKE target_table INCLUDING DEFAULTS. Կամ պարզապես - LIKE target_table INCLUDING ALL — պատճենում է կանխադրվածները, ինդեքսները, սահմանափակումները,...

Բայց այստեղ դուք պետք է հասկանաք, որ եթե դուք ստեղծել եք ներմուծեք աղյուսակը անմիջապես ինդեքսներով, այնուհետև տվյալների բեռնումը ավելի երկար կպահանջիքան եթե դուք նախ լրացնեք ամեն ինչ, և միայն այնուհետև հավաքեք ինդեքսները, տեսեք, թե ինչպես է դա անում որպես օրինակ pg_dump.

Ընդհանուր առմամբ RTFM!

2. Ինչպե՞ս գրել:

Միայն ասեմ՝ օգտագործիր COPY- հոսք «փաթեթի» փոխարեն INSERT, արագացում երբեմն. Դուք կարող եք նույնիսկ անմիջապես նախապես ստեղծված ֆայլից:

3. Ինչպե՞ս մշակել:

Այսպիսով, եկեք թույլ տանք, որ մեր ներածությունը այսպիսի տեսք ունենա.

դուք ունեք աղյուսակ, որտեղ ձեր տվյալների բազայում պահվում են հաճախորդի տվյալները 1M ռեկորդներ
ամեն օր հաճախորդը ձեզ նորն է ուղարկում ամբողջական «պատկեր»
փորձից դուք գիտեք, որ ժամանակ առ ժամանակ ոչ ավելի, քան 10 հազար գրառումները փոխվում են

Նման իրավիճակի դասական օրինակ է KLADR բազա — Հասցեներն ընդհանուր առմամբ շատ են, բայց ամեն շաբաթական բեռնման մեջ շատ քիչ փոփոխություններ են լինում (բնակավայրերի անվանափոխում, փողոցների համատեղում, նոր տների տեսք) նույնիսկ ազգային մասշտաբով։

3.1. Ամբողջական համաժամացման ալգորիթմ

Պարզության համար ասենք, որ ձեզ հարկավոր չէ նույնիսկ վերակազմավորել տվյալները, պարզապես աղյուսակը բերեք ցանկալի ձևի, այսինքն.

հեռացնել այն ամենը, ինչ այլևս գոյություն չունի
թարմացում այն ամենը, ինչ արդեն եղել է և պետք է թարմացվի
տեղադրելու համար այն ամենը, ինչ դեռ չի եղել

Ինչու՞ պետք է գործողությունները կատարվեն այս հերթականությամբ: Որովհետև այսպես սեղանի չափը կաճի նվազագույնը (հիշեք MVCC!).

Ջնջել dst

Ոչ, իհարկե, դուք կարող եք հաղթահարել ընդամենը երկու գործողություն.

հեռացնել (DELETE) ընդհանրապես ամեն ինչ
տեղադրելու համար բոլորը նոր կերպարից

Բայց միևնույն ժամանակ, MVCC-ի շնորհիվ, Սեղանի չափը կավելանա ուղիղ երկու անգամ! Աղյուսակում գրառումների +1M պատկեր ստանալը 10K թարմացման շնորհիվ այդքան ավելորդություն է...

Կտրվել դստ

Ավելի փորձառու մշակողը գիտի, որ ամբողջ պլանշետը կարելի է մաքրել բավականին էժան.

պարզ (TRUNCATE) ամբողջ աղյուսակը
տեղադրելու համար բոլորը նոր կերպարից

Մեթոդը արդյունավետ է, երբեմն բավականին կիրառելի է, բայց խնդիր կա... Մենք դեռ երկար ժամանակ կավելացնենք 1M ձայնագրություններ, ուստի չենք կարող մեզ թույլ տալ այս ամբողջ ընթացքում աղյուսակը դատարկ թողնել (ինչպես դա տեղի կունենա առանց այն մեկ գործարքի մեջ փաթաթելու):

Ինչը նշանակում է:

մենք սկսում ենք երկարաժամկետ գործարք
TRUNCATE պարտադրում է AccessExclusive- արգելափակում
մենք կատարում ենք ներդիրը երկար ժամանակ, իսկ մնացած բոլորը այս պահին նույնիսկ չի կարող SELECT

Ինչ-որ բան լավ չի ընթանում...

ՓՈՓՈԽԵԼ ՍԵՂԱՆԱԿԸ… ՓՈԽԱՆՎԵԼ ԱՆՎԱՆԱՎՈՐԵԼ… / ԳՈՐԾԵԼ ՍԵՂԱՆԱԿԸ…

Այլընտրանք է ամեն ինչ լրացնել առանձին նոր աղյուսակում, այնուհետև պարզապես վերանվանել այն հինի փոխարեն: Մի քանի տհաճ փոքրիկ բան.

դեռ նույնպես AccessExclusive, չնայած զգալիորեն ավելի քիչ ժամանակ
այս աղյուսակի բոլոր հարցումների պլանները/վիճակագրությունները զրոյացված են, պետք է գործարկել ANALYZE-ը
բոլոր արտաքին բանալիները կոտրված են (FK) սեղանին

Սայմոն Ռիգսից կար WIP կարկատել, որն առաջարկում էր պատրաստել ALTER-Աղյուսակի մարմինը ֆայլի մակարդակով փոխարինելու գործողություն, առանց վիճակագրության և FK-ի շոշափելու, բայց քվորում չի հավաքել:

ՋՆՋԵԼ, ԹԱՐՄԱՑՆԵԼ, ՆԵՐԴՐԵԼ

Այսպիսով, մենք լուծում ենք երեք գործողությունների չարգելափակման տարբերակը: Գրեթե երեք... Ինչպե՞ս դա անել ամենաարդյունավետը:

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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;

3.2. Ներմուծում հետմշակման

Նույն KLADR-ում բոլոր փոփոխված գրառումները պետք է լրացուցիչ գործարկվեն հետմշակման միջոցով՝ նորմալացվեն, հիմնաբառերը ընդգծվեն և կրճատվեն մինչև պահանջվող կառուցվածքները: Բայց որտեղի՞ց գիտես... թե կոնկրետ ինչ է փոխվելառանց համաժամացման կոդը բարդացնելու, իդեալապես առանց դրան ընդհանրապես շոշափելու:

Եթե համաժամացման պահին միայն ձեր պրոցեսն ունի գրելու հասանելիություն, ապա կարող եք օգտագործել ձգան, որը կհավաքի բոլոր փոփոխությունները մեզ համար.

-- целевые таблицы
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 TABLE client(
  client_id
    serial
      PRIMARY KEY
, inn
    varchar
      UNIQUE
, name
    varchar
);

CREATE TABLE invoice(
  invoice_id
    serial
      PRIMARY KEY
, client_id
    integer
      REFERENCES client(client_id)
, number
    varchar
, dt
    date
, sum
    numeric(32,2)
);

Բայց արտաքին աղբյուրից ներբեռնումը մեզ է հասնում «բոլորը մեկում» ձևով.

CREATE TEMPORARY TABLE invoice_import(
  client_inn
    varchar
, client_name
    varchar
, invoice_number
    varchar
, invoice_dt
    date
, invoice_sum
    numeric(32,2)
);

Ակնհայտ է, որ հաճախորդի տվյալները կարող են կրկնօրինակվել այս տարբերակում, և հիմնական գրառումը «հաշիվն» է.

0123456789;Вася;A-01;2020-03-16;1000.00
9876543210;Петя;A-02;2020-03-16;666.00
0123456789;Вася;B-03;2020-03-16;9999.00

Մոդելի համար մենք պարզապես կտեղադրենք մեր թեստի տվյալները, բայց հիշեք. COPY ավելի արդյունավետ!

INSERT INTO invoice_import
VALUES
  ('0123456789', 'Вася', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'Петя', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'Вася', 'B-03', '2020-03-16', 9999.00);

Նախ՝ առանձնացնենք այն «կրճատումները», որոնց վերաբերում են մեր «փաստերը»։ Մեր դեպքում հաշիվ-ապրանքագրերը վերաբերում են հաճախորդներին.

CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
  client_inn inn
, client_name "name"
FROM
  invoice_import;

Հաշիվները հաճախորդների ID-ների հետ ճիշտ կապելու համար մենք նախ պետք է պարզենք կամ ստեղծենք այդ նույնացուցիչները: Եկեք դրանց տակ դաշտեր ավելացնենք.

ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;

Եկեք օգտագործենք վերը նկարագրված աղյուսակի համաժամացման մեթոդը փոքր փոփոխությամբ. մենք նպատակային աղյուսակում ոչինչ չենք թարմացնի կամ ջնջի, քանի որ հաճախորդներ ներմուծում ենք «միայն հավելվածով».

-- проставляем в таблице импорта 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, որով կտեղադրենք հաշիվ-ապրանքագիրը։

Source: www.habr.com

DBA. գրագետ կազմակերպել համաժամացումները և ներմուծումները