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 - ანუ, როდესაც ტრანზაქცია დასრულდება, ცხრილი ავტომატურად წაიშლება.

არარეპლიკაცია

იმის გამო, რომ ისინი ეკუთვნის მხოლოდ კონკრეტულ კავშირს, დროებითი ცხრილები არ განმეორდება. მაგრამ ეს გამორიცხავს მონაცემთა ორმაგი ჩაწერის აუცილებლობას heap + WAL-ში, ამიტომ მასში ჩასმა/განახლება/წაშლა გაცილებით სწრაფია.

მაგრამ იმის გამო, რომ დროებითი ცხრილი ჯერ კიდევ "თითქმის ჩვეულებრივი" ცხრილია, ის არ შეიძლება შეიქმნას რეპლიკაზეც. ყოველ შემთხვევაში, ჯერჯერობით, თუმცა შესაბამისი პატჩი დიდი ხანია ტრიალებს.

1.2. ამოღებული მაგიდა

მაგრამ რა უნდა გააკეთოთ, მაგალითად, თუ გაქვთ რაიმე სახის შრომატევადი ETL პროცესი, რომელიც ვერ განხორციელდება ერთი ტრანზაქციის ფარგლებში, მაგრამ მაინც გაქვთ pgbouncer ტრანზაქციის რეჟიმში? ..

ან მონაცემთა ნაკადი იმდენად დიდია, რომ არ არის საკმარისი გამტარობა ერთ კავშირზე მონაცემთა ბაზიდან (წაიკითხეთ, ერთი პროცესი თითო პროცესორზე)?..

ან რაღაც ოპერაციები მიმდინარეობს ასინქრონულად სხვადასხვა კავშირებში?..

აქ მხოლოდ ერთი ვარიანტია - დროებით შექმენით არადროებითი ცხრილი. Pun, დიახ. ანუ:

• შევქმენი "ჩემი საკუთარი" ცხრილები მაქსიმალურად შემთხვევითი სახელებით, რათა არ გადაიკვეთოს ვინმესთან
• ამონაწერი: შეავსო ისინი გარე წყაროს მონაცემებით
• Transform: გადაყვანილი, შევსებული გასაღების დამაკავშირებელი ველები
• Load: ჩაასხა მზა მონაცემები სამიზნე ცხრილებში
• წაიშალა "ჩემი" ცხრილები

ახლა კი - ბუზი მალამოში. Სინამდვილეში, PostgreSQL-ში ყველა ჩაწერა ორჯერ ხდება - პირველად WAL-ში, შემდეგ ცხრილის/ინდექსის ორგანოებში. ეს ყველაფერი კეთდება ACID-ის მხარდასაჭერად და მონაცემთა კორექტირების მიზნით COMMIT'თხილიანი და ROLLBACKნულოვანი ტრანზაქციები.

მაგრამ ჩვენ არ გვჭირდება ეს! ჩვენ გვაქვს მთელი პროცესი ან იყო სრულიად წარმატებული ან არა.. არ აქვს მნიშვნელობა რამდენი შუალედური ტრანზაქცია იქნება - ჩვენ არ გვაინტერესებს „პროცესის შუა მდგომარეობით გაგრძელება“, განსაკუთრებით მაშინ, როდესაც გაურკვეველია სად იყო.

ამისათვის PostgreSQL დეველოპერებმა, ჯერ კიდევ 9.1 ვერსიაში, შემოიღეს ისეთი რამ, როგორიცაა ამოღებული მაგიდები:

ამ მითითებით, ცხრილი იქმნება, როგორც გაუთვალისწინებელი. ამოღებულ ცხრილებში ჩაწერილი მონაცემები არ გადის წინასწარ ჩაწერის ჟურნალში (იხ. თავი 29), რაც იწვევს ასეთ ცხრილებს ჩვეულებრივზე ბევრად სწრაფად მუშაობენ. თუმცა, ისინი არ არიან იმუნური წარუმატებლობისგან; სერვერის უკმარისობის ან გადაუდებელი გათიშვის შემთხვევაში, გამორიცხული ცხრილი ავტომატურად შეკვეცა. გარდა ამისა, გამოტოვებული ცხრილის შინაარსი არ არის გამეორებული მონ სერვერებზე. ნებისმიერი ინდექსები, რომლებიც შექმნილია არალოგირებულ ცხრილში, ავტომატურად ხდება გაუქმებული.

მოკლედ, ეს იქნება ბევრად უფრო სწრაფი, მაგრამ თუ მონაცემთა ბაზის სერვერი "დავარდება", ეს უსიამოვნო იქნება. მაგრამ რამდენად ხშირად ხდება ეს და იცის თუ არა თქვენმა ETL პროცესმა როგორ გამოასწოროს ეს სწორად „შუადან“ მონაცემთა ბაზის „აღორძინების“ შემდეგ?..

თუ არა და ზემოთ მოცემული შემთხვევა თქვენსას ჰგავს, გამოიყენეთ UNLOGGEDმაგრამ არასდროს არ ჩართოთ ეს ატრიბუტი რეალურ ცხრილებზე, მონაცემები საიდანაც ძვირფასია თქვენთვის.

1.3. ON COMIT { მწკრივების წაშლა | ვარდნა}

ეს კონსტრუქცია საშუალებას გაძლევთ მიუთითოთ ავტომატური ქცევა, როდესაც ტრანზაქცია დასრულდება ცხრილის შექმნისას.

on 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. მოწონება...მათ შორის...

დასაწყისში აღვნიშნე, რომ დროებითი ცხრილების გამოყენების ერთ-ერთი ტიპიური შემთხვევაა სხვადასხვა სახის იმპორტი - და დეველოპერი დაღლილად აკოპირებს სამიზნე ცხრილის ველების ჩამონათვალს მისი დროებითი დეკლარაციაში...

მაგრამ სიზარმაცე არის პროგრესის ძრავა! Ამიტომაც შექმენით ახალი ცხრილი "ნიმუშზე დაყრდნობით" ეს შეიძლება იყოს ბევრად უფრო მარტივი:

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. როგორ დავამუშავოთ?

მოდით, ჩვენი შესავალი ასე გამოიყურებოდეს:

• თქვენ გაქვთ ცხრილი კლიენტის მონაცემებით, რომელიც ინახება თქვენს მონაცემთა ბაზაში 1 მილიონი ჩანაწერი
• ყოველდღე კლიენტი გიგზავნით ახალს სრული "სურათი"
• გამოცდილებიდან თქვენ იცით, რომ დროდადრო არაუმეტეს 10K ჩანაწერი იცვლება

ასეთი სიტუაციის კლასიკური მაგალითია KLADR ბაზა — სულ ბევრი მისამართია, მაგრამ ყოველკვირეულ ატვირთვაში ძალიან ცოტა ცვლილებაა (დასახლებების გადარქმევა, ქუჩების გაერთიანება, ახალი სახლების გამოჩენა) თუნდაც ეროვნული მასშტაბით.

3.1. სრული სინქრონიზაციის ალგორითმი

სიმარტივისთვის, ვთქვათ, რომ თქვენ არც კი გჭირდებათ მონაცემების რესტრუქტურიზაცია - უბრალოდ მიიტანეთ ცხრილი სასურველ ფორმაში, ეს არის:

• ამოიღე ყველაფერი რაც აღარ არსებობს
• განახლება ყველაფერი, რაც უკვე არსებობდა და განახლებას საჭიროებს
• ჩადეთ ყველაფერი რაც ჯერ არ მომხდარა

რატომ უნდა გაკეთდეს ოპერაციები ამ თანმიმდევრობით? რადგან ასე გაიზრდება მაგიდის ზომა მინიმალურად (დაიმახსოვრე MVCC!).

წაშლა dst

არა, რა თქმა უნდა, თქვენ შეგიძლიათ გაუმკლავდეთ მხოლოდ ორი ოპერაციით:

• ამოიღე (DELETE) ზოგადად ყველაფერი
• ჩადეთ ყველაფერი ახალი სურათიდან

მაგრამ ამავე დროს, MVCC-ის წყალობით, მაგიდის ზომა ზუსტად ორჯერ გაიზრდება! ცხრილში ჩანაწერების +1M გამოსახულების მიღება 10K განახლების გამო ძალიან ზედმეტია...

TRUNCATE დსტ

უფრო გამოცდილმა დეველოპერმა იცის, რომ მთელი ტაბლეტის გაწმენდა საკმაოდ იაფად შეიძლება:

• ნათელია (TRUNCATE) მთელი მაგიდა
• ჩადეთ ყველაფერი ახალი სურათიდან

მეთოდი ეფექტურია, ზოგჯერ საკმაოდ გამოყენებადია, მაგრამ არის პრობლემა... ჩვენ ვამატებთ 1M ჩანაწერებს დიდი ხნის განმავლობაში, ასე რომ, არ შეგვიძლია მაგიდის ცარიელი დატოვება მთელი ამ ხნის განმავლობაში (როგორც მოხდება მისი ერთ ტრანზაქციაში შეფუთვის გარეშე).

Რაც ნიშნავს:

• ჩვენ ვიწყებთ გრძელვადიანი გარიგება
• TRUNCATE აწესებს AccessExclusive- დაბლოკვა
• ჩვენ ვაკეთებთ ჩასმას დიდი ხნის განმავლობაში და ყველა დანარჩენი ამ დროს ვერც კი SELECT

რაღაც არ მიდის კარგად...

ცხრილის შეცვლა… გადარქმევა… / ცხრილის ჩამოშლა…

ალტერნატივა არის ყველაფრის შევსება ცალკე ახალ ცხრილში და შემდეგ უბრალოდ გადარქმევა ძველის ნაცვლად. რამდენიმე საზიზღარი პატარა რამ:

• ჯერ კიდევ AccessExclusive, თუმცა მნიშვნელოვნად ნაკლები დრო
• ყველა შეკითხვის გეგმა/სტატისტიკა ამ ცხრილისთვის გადატვირთულია, საჭიროა ANALYZE-ის გაშვება
• ყველა უცხოური გასაღები გატეხილია (FK) მაგიდასთან

იყო WIP პატჩი Simon Riggs-ისგან, რომელიც შესთავაზა დამზადებას 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, რომლითაც ჩავსვამთ ინვოისს.

წყარო: www.habr.com