DBA: සමමුහුර්ත කිරීම් සහ ආනයනයන් දක්ෂ ලෙස සංවිධානය කරන්න

විශාල දත්ත කට්ටලවල සංකීර්ණ සැකසුම් සඳහා (විවිධ ETL ක්රියාවලි: ආනයන, පරිවර්තන සහ බාහිර මූලාශ්‍රයක් සමඟ සමමුහුර්ත කිරීම) බොහෝ විට අවශ්‍ය වේ තාවකාලිකව "මතක තබා ගන්න" සහ වහාම ඉක්මනින් ක්රියාවලිය විශාල දෙයක්.

මේ ආකාරයේ සාමාන්‍ය කාර්යයක් සාමාන්‍යයෙන් මෙවැනි දෙයක් ශබ්ද කරයි: "මෙහෙමයි ගිණුම්කරණ දෙපාර්තමේන්තුව සේවාදායක බැංකුවෙන් බාන ලදී අවසන් වරට ලැබුණු ගෙවීම්, ඔබ ඒවා ඉක්මනින් වෙබ් අඩවියට උඩුගත කර ඔබේ ගිණුම්වලට සම්බන්ධ කළ යුතුය.

නමුත් මෙම “යමක්” පරිමාව මෙගාබයිට් සිය ගණනකින් මැනීමට පටන් ගත් විට සහ සේවාව 24x7 දත්ත සමුදාය සමඟ දිගටම ක්‍රියා කළ යුතු විට, ඔබේ ජීවිතය විනාශ කරන බොහෝ අතුරු ආබාධ පැන නගී.

PostgreSQL හි ඔවුන් සමඟ කටයුතු කිරීමට (සහ එය තුළ පමණක් නොවේ), ඔබට සෑම දෙයක්ම වේගයෙන් සහ අඩු සම්පත් පරිභෝජනයෙන් සැකසීමට ඉඩ සලසන සමහර ප්‍රශස්තිකරණයන් භාවිතා කළ හැකිය.

1. නැව්ගත කිරීමට කොහෙද?

පළමුව, අපට “ක්‍රියාවලියට” අවශ්‍ය දත්ත උඩුගත කළ හැකි ස්ථානය තීරණය කරමු.

1.1 තාවකාලික වගු (TEMPORARY TABLE)

ප්‍රතිපත්තිමය වශයෙන්, PostgreSQL සඳහා තාවකාලික වගු වෙනත් ඕනෑම එකකට සමාන වේ. එබැවින්, මිථ්යා විශ්වාසයන් වැනි "එහි සෑම දෙයක්ම මතකයේ පමණක් ගබඩා කර ඇති අතර එය අවසන් විය හැක". නමුත් සැලකිය යුතු වෙනස්කම් කිහිපයක් ද තිබේ.

දත්ත සමුදායට එක් එක් සම්බන්ධතාවය සඳහා ඔබේම "නාම අවකාශය"

සම්බන්ධතා දෙකක් එකවර සම්බන්ධ කිරීමට උත්සාහ කරන්නේ නම් CREATE TABLE x, එතකොට කාට හරි අනිවාර්යයෙන් ලැබෙනවා අද්විතීය නොවන දෝෂය දත්ත සමුදා වස්තු.

හැබැයි දෙන්නම execute කරන්න හැදුවොත් CREATE TEMPORARY TABLE x, එවිට දෙදෙනාම එය සාමාන්යයෙන් කරනු ඇත, සහ සෑම කෙනෙකුටම ලැබෙනු ඇත ඔබේ පිටපත මේස. තවද ඔවුන් අතර පොදු කිසිවක් නොතිබෙනු ඇත.

විසන්ධි කරන විට "ස්වයං විනාශය"

සම්බන්ධතාවය වසා ඇති විට, සියලුම තාවකාලික වගු ස්වයංක්‍රීයව මකා දැමෙනු ඇත, එබැවින් අතින් DROP TABLE x හැර කිසිම තේරුමක් නැත ...

ඔබ හරහා වැඩ කරන්නේ නම් ගනුදෙනු මාදිලියේ pgbouncer, එවිට දත්ත සමුදාය මෙම සම්බන්ධතාවය තවමත් සක්‍රිය බව විශ්වාස කරන අතර එහි මෙම තාවකාලික වගුව තවමත් පවතී.

එබැවින්, pgbouncer වෙත වෙනත් සම්බන්ධතාවයකින් එය නැවත නිර්මාණය කිරීමට උත්සාහ කිරීම දෝෂයක් ඇති කරයි. නමුත් මෙය භාවිතා කිරීමෙන් මග හැරිය හැක CREATE TEMPORARY TABLE IF NOT EXISTS x.

කෙසේ වෙතත්, මෙය කෙසේ හෝ නොකිරීම වඩා හොඳය, මන්ද එවිට ඔබට "හදිසියේම" "පෙර හිමිකරුගෙන්" ඉතිරිව ඇති දත්ත එහි සොයාගත හැකිය. ඒ වෙනුවට, අත්පොත කියවා වගුවක් නිර්මාණය කිරීමේදී එය එකතු කළ හැකි බව දැකීම වඩා හොඳය ON COMMIT DROP - එනම්, ගනුදෙනුව අවසන් වූ විට, වගුව ස්වයංක්රීයව මකා දැමෙනු ඇත.

අනුකරණය නොකිරීම

ඒවා විශේෂිත සම්බන්ධතාවයකට පමණක් අයත් වන බැවින්, තාවකාලික වගු අනුකරණය නොකෙරේ. එහෙත් මෙය දත්ත ද්විත්ව පටිගත කිරීමේ අවශ්‍යතාවය ඉවත් කරයි ගොඩ + වෝල් වලින්, ඒ නිසා එයට ඇතුළු කිරීම/යාවත්කාලීන කිරීම/මකීම සැලකිය යුතු තරම් වේගවත් වේ.

නමුත් තාවකාලික වගුවක් තවමත් “පාහේ සාමාන්‍ය” වගුවක් බැවින් එය අනුරුවක් මතද නිර්මාණය කළ නොහැක. අවම වශයෙන් දැනට, අනුරූප පැච් දිගු කලක් තිස්සේ සංසරණය වුවද.

1.2 UNLOGGED Table

නමුත් ඔබ කුමක් කළ යුතුද, උදාහරණයක් ලෙස, ඔබට එක් ගනුදෙනුවක් තුළ ක්‍රියාත්මක කළ නොහැකි යම් ආකාරයක අපහසු ETL ක්‍රියාවලියක් තිබේ නම්, නමුත් ඔබට තවමත් තිබේ නම් ගනුදෙනු මාදිලියේ pgbouncer? ..

නැතහොත් දත්ත ප්‍රවාහය එතරම් විශාලය එක් සම්බන්ධතාවයක ප්‍රමාණවත් කලාප පළලක් නොමැත දත්ත සමුදායකින් (කියවන්න, CPU එකකට එක් ක්‍රියාවලියක්)?..

නැතිනම් යම් යම් මෙහෙයුම් සිදුවෙමින් පවතී අසමමුහුර්තව විවිධ සම්බන්ධතා වල? ..

මෙහි ඇත්තේ එක් විකල්පයක් පමණි - තාවකාලික නොවන වගුවක් තාවකාලිකව සාදන්න. පුන්, ඔව්. එනම්:

• කිසිවෙකු සමඟ ඡේදනය නොවන පරිදි උපරිම අහඹු නම් සහිත "මගේම" වගු නිර්මාණය කරන ලදී
• උද්ධෘතය: ඒවා බාහිර මූලාශ්‍රයකින් දත්ත පුරවා ඇත
• පරිවර්තනය: පරිවර්තනය, ප්රධාන සම්බන්ධක ක්ෂේත්ර පුරවා ඇත
• පැටවීම: සූදානම් දත්ත ඉලක්ක වගු වලට වත් කළා
• "මගේ" වගු මකා දැමුවා

දැන් - විලවුන් තුළ මැස්සෙක්. ඇත්ත වශයෙන්ම, PostgreSQL හි සියලුම ලිවීම් දෙවරක් සිදු වේ - මුලින්ම WAL හි, පසුව වගුව/දර්ශක සිරුරු තුළට. මේ සියල්ල සිදු කරනුයේ ACID සඳහා සහය දැක්වීමට සහ දත්ත දෘශ්‍යතාව නිවැරදි කිරීමට ය COMMIT'කනස්සල්ලට හා ROLLBACK'ශුන්‍ය ගනුදෙනු.

නමුත් අපට මෙය අවශ්ය නොවේ! සම්පූර්ණ ක්‍රියාවලියම අප සතුව ඇත එක්කෝ එය සම්පූර්ණයෙන්ම සාර්ථක විය, නැතහොත් එය නොවේ.. අතරමැදි ගනුදෙනු කීයක් තිබුණත් කමක් නැත - විශේෂයෙන් එය කොතැනද යන්න පැහැදිලි නැති විට “ක්‍රියාවලිය මැද සිට ඉදිරියට ගෙන යාමට” අපි උනන්දු නොවෙමු.

මෙය සිදු කිරීම සඳහා, PostgreSQL සංවර්ධකයින්, 9.1 අනුවාදයේ, එවැනි දෙයක් හඳුන්වා දුන්නේය UNLOGGED වගු:

මෙම ඇඟවීම සමඟ, වගුව ලොග් නොකළ ලෙස නිර්මාණය කර ඇත. ලොග් නොකළ වගු වෙත ලියන ලද දත්ත ඉදිරියට ලිවීමේ ලඝු-සටහන හරහා නොයනු ඇත (පරිච්ඡේදය 29 බලන්න), එවැනි වගු වලට හේතු වේ වෙනදාට වඩා වේගයෙන් වැඩ කරන්න. කෙසේ වෙතත්, ඔවුන් අසාර්ථක වීමෙන් නිදහස් නොවේ; සේවාදායකය අසමත් වීම හෝ හදිසි වසා දැමීමකදී, ලොග් නොවූ වගුවක් ස්වයංක්රීයව කපා හැරේ. අතිරේකව, ලොග් නොකළ වගුවේ අන්තර්ගතය අනුකරණය නොවේ වහල් සේවාදායකයන් වෙත. ලොග් නොවූ වගුවක් මත සාදන ලද ඕනෑම දර්ශක ස්වයංක්‍රීයව ලොග් වී නොමැත.

කෙටියෙන් කිවහොත්, එය වඩා වේගවත් වනු ඇත, නමුත් දත්ත සමුදා සේවාදායකය "වැටේ" නම්, එය අප්රසන්න වනු ඇත. නමුත් මෙය කොපමණ වාරයක් සිදු වේද, දත්ත සමුදාය "පුනර්ජීවනය" කිරීමෙන් පසු මෙය නිවැරදිව "මැද සිට" නිවැරදි කරන්නේ කෙසේදැයි ඔබේ ETL ක්‍රියාවලිය දන්නවාද?

එසේ නොවේ නම්, සහ ඉහත නඩුව ඔබේ එකට සමාන නම්, භාවිතා කරන්න UNLOGGEDනමුත් කවදාවත් සැබෑ වගු මත මෙම ගුණාංගය සබල නොකරන්න, ඔබට ප්‍රිය වන දත්ත.

1.3 කැපවීම මත { පේළි මකන්න | DROP}

වගුවක් සෑදීමේදී ගනුදෙනුවක් අවසන් වූ විට ස්වයංක්‍රීය හැසිරීම් නියම කිරීමට මෙම ඉදිකිරීම ඔබට ඉඩ සලසයි.

මත 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-values ​​(උදාහරණයක් ලෙස, ප්රාථමික ප්රධාන අගයන් පිරවීම සඳහා), ඔබට භාවිතා කළ හැකිය LIKE target_table INCLUDING DEFAULTS. හෝ සරලව - LIKE target_table INCLUDING ALL — පිටපත් පෙරනිමි, දර්ශක, සීමාවන්,...

නමුත් මෙහිදී ඔබ නිර්මාණය කළේ නම් එය තේරුම් ගත යුතුය දර්ශක සමඟ වහාම වගුව ආනයනය කරන්න, එවිට දත්ත පූරණය වීමට වැඩි කාලයක් ගතවනු ඇතඔබ මුලින්ම සියල්ල පුරවා, පසුව පමණක් දර්ශක පෙරළනවාට වඩා - උදාහරණයක් ලෙස එය කරන්නේ කෙසේදැයි බලන්න pg_dump.

පොදුවේ RTFM!

2. ලියන්නේ කෙසේද?

මම කියන්නම් - එය භාවිතා කරන්න COPY- "ඇසුරුම්" වෙනුවට ගලා INSERT, සමහර අවස්ථාවලදී ත්වරණය. ඔබට පෙර-ජනනය කරන ලද ගොනුවකින් සෘජුවම පවා හැක.

3. සකස් කරන්නේ කෙසේද?

ඉතින්, අපි අපේ හැඳින්වීම මේ වගේ දෙයක් බලමු:

• ඔබගේ දත්ත ගබඩාවේ ගබඩා කර ඇති සේවාදායක දත්ත සහිත වගුවක් ඔබ සතුව ඇත 1M වාර්තා
• සෑම දිනකම සේවාදායකයෙක් ඔබට අලුත් එකක් එවයි සම්පූර්ණ "රූපය"
• අත්දැකීමෙන් ඔබ එය විටින් විට දන්නවා වාර්තා 10K ට වඩා වෙනස් නොවේ

එවැනි තත්වයක් සඳහා සම්භාව්ය උදාහරණයක් KLADR පදනම - සම්පුර්ණයෙන්ම ලිපින විශාල ප්‍රමාණයක් ඇත, නමුත් සෑම සතිපතා උඩුගත කිරීමකදීම ජාතික පරිමාණයෙන් පවා ඉතා සුළු වෙනස්කම් (ජනවාස නැවත නම් කිරීම, වීදි ඒකාබද්ධ කිරීම, නව නිවාසවල පෙනුම) ඇත.

3.1 සම්පූර්ණ සමමුහුර්ත කිරීමේ ඇල්ගොරිතම

සරල බව සඳහා, ඔබට දත්ත ප්‍රතිව්‍යුහගත කිරීමට අවශ්‍ය නොවන බව කියමු - වගුව අපේක්ෂිත පෝරමයට ගෙන එන්න, එනම්:

• ඉවත් කරන්න තවදුරටත් නොපවතින සියල්ල
• යාවත්කාලීන කිරීම දැනටමත් පවතින සහ යාවත්කාලීන කළ යුතු සියල්ල
• ඇතුළු කරන්න තවමත් සිදු නොවූ සියල්ල

මෙම අනුපිළිවෙලෙහි මෙහෙයුම් සිදු කළ යුත්තේ ඇයි? මන්ද මේස ප්‍රමාණය අවම වශයෙන් වර්ධනය වන්නේ එලෙසයි (MVCC මතක තබා ගන්න!).

dst වෙතින් මකන්න

නැත, ඇත්ත වශයෙන්ම ඔබට මෙහෙයුම් දෙකකින් ලබා ගත හැකිය:

• ඉවත් කරන්න (DELETE) පොදුවේ සෑම දෙයක්ම
• ඇතුළු කරන්න සියල්ල නව රූපයෙන්

නමුත් ඒ සමඟම, MVCC වෙත ස්තූතියි, මේසයේ ප්රමාණය හරියටම දෙවරක් වැඩි වනු ඇත! 1K යාවත්කාලීනයක් හේතුවෙන් වගුවේ +10M වාර්තා රූප ලබා ගැනීම එතරම් අතිරික්තයකි...

TRUNCATE dst

වඩා පළපුරුදු සංවර්ධකයෙකු දන්නවා සම්පූර්ණ ටැබ්ලටය ඉතා ලාභදායී ලෙස පිරිසිදු කළ හැකි බව:

• පැහැදිලි (TRUNCATE) සම්පූර්ණ වගුව
• ඇතුළු කරන්න සියල්ල නව රූපයෙන්

ක්රමය ඵලදායී වේ, සමහර විට තරමක් අදාළ වේ, නමුත් ගැටලුවක් තිබේ... අපි දිගු කාලයක් සඳහා 1M වාර්තා එකතු කරන්නෙමු, එබැවින් මේ කාලය පුරාම මේසය හිස්ව තැබීමට අපට හැකියාවක් නැත (එය තනි ගනුදෙනුවකින් එතීමෙන් තොරව සිදුවනු ඇත).

ඒ කියන්නේ:

• අපි පටන් ගන්නවා දිගුකාලීන ගනුදෙනුව
• TRUNCATE පනවයි ප්‍රවේශය සුවිශේෂී- අවහිර කිරීම
• අපි දිගු වේලාවක් ඇතුළු කිරීම කරන්නෙමු, මේ අවස්ථාවේ අනෙක් සියල්ලන්ම පවා බැහැ SELECT

යමක් හොඳින් සිදු නොවේ ...

වගුව වෙනස් කරන්න... නැවත නම් කරන්න... / ඩ්‍රොප් වගුව...

විකල්පයක් වන්නේ සෑම දෙයක්ම වෙනම නව වගුවකට පුරවා පැරණි එක වෙනුවට එය නැවත නම් කිරීමයි. අප්රසන්න කුඩා දේවල් කිහිපයක්:

• තවමත් ද ප්‍රවේශය සුවිශේෂී, සැලකිය යුතු ලෙස අඩු කාලයක් වුවද
• මෙම වගුව සඳහා සියලු විමසුම් සැලසුම්/සංඛ්‍යාලේඛන නැවත සකසා ඇත, 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;

පාරිභෝගික හැඳුනුම්පත් සමඟ ගිණුම් නිවැරදිව සම්බන්ධ කිරීම සඳහා, අපි මුලින්ම මෙම හඳුනාගැනීම් සොයා ගැනීමට හෝ උත්පාදනය කිරීමට අවශ්‍ය වේ. අපි ඒවා යටතේ ක්ෂේත්‍ර එකතු කරමු:

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