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 માં, તેથી તેમાં INSERT/UPDATE/DELETE કરવું વધુ ઝડપી છે.

પરંતુ કામચલાઉ ટેબલ હજી પણ "લગભગ સામાન્ય" ટેબલ હોવાથી, તે પ્રતિકૃતિ પર પણ બનાવી શકાતું નથી. ઓછામાં ઓછા હમણાં માટે, જો કે અનુરૂપ પેચ લાંબા સમયથી ફરતો રહ્યો છે.

1.2. અનલૉગ કરેલ કોષ્ટક

પરંતુ તમારે શું કરવું જોઈએ, ઉદાહરણ તરીકે, જો તમારી પાસે અમુક પ્રકારની બોજારૂપ ETL પ્રક્રિયા છે જે એક વ્યવહારમાં લાગુ કરી શકાતી નથી, પરંતુ તમારી પાસે હજુ પણ છે ટ્રાન્ઝેક્શન મોડમાં pgbouncer? ..

અથવા ડેટા ફ્લો એટલો મોટો છે કે એક કનેક્શન પર પૂરતી બેન્ડવિડ્થ નથી ડેટાબેઝમાંથી (વાંચો, CPU દીઠ એક પ્રક્રિયા)?..

અથવા અમુક કામગીરી ચાલી રહી છે અસુમેળ રીતે વિવિધ જોડાણોમાં? ..

અહીં ફક્ત એક જ વિકલ્પ છે - અસ્થાયી રૂપે બિન-અસ્થાયી ટેબલ બનાવો. પન, હા. તે જ:

• મહત્તમ રેન્ડમ નામો સાથે "મારા પોતાના" કોષ્ટકો બનાવ્યાં જેથી કોઈની સાથે છેદાય નહીં
• અર્ક: તેમને બાહ્ય સ્ત્રોતના ડેટાથી ભર્યા
• રૂપાંતરણ: રૂપાંતરિત, કી લિંકિંગ ફીલ્ડ્સમાં ભરેલ
• લોડ: લક્ષ્ય કોષ્ટકોમાં તૈયાર ડેટા રેડ્યો
• "મારા" કોષ્ટકો કાઢી નાખ્યા

અને હવે - મલમ માં ફ્લાય. હકિકતમાં, PostgreSQL માં બધા લખે છે તે બે વાર થાય છે - WAL માં પ્રથમ, પછી ટેબલ/ઇન્ડેક્સ બોડીમાં. આ બધું ACID ને સપોર્ટ કરવા અને વચ્ચેની માહિતીની યોગ્યતા માટે કરવામાં આવે છે COMMIT'મીંજવાળું અને ROLLBACK'નલ વ્યવહારો.

પરંતુ અમને આની જરૂર નથી! અમારી પાસે આખી પ્રક્રિયા છે કાં તો તે સંપૂર્ણપણે સફળ હતું અથવા તે ન હતું.. કેટલા મધ્યવર્તી વ્યવહારો હશે તેનાથી કોઈ ફરક પડતો નથી - અમને "મધ્યમથી પ્રક્રિયા ચાલુ રાખવામાં" રસ નથી, ખાસ કરીને જ્યારે તે સ્પષ્ટ ન હોય કે તે ક્યાં હતું.

આ કરવા માટે, PostgreSQL વિકાસકર્તાઓએ, આવૃત્તિ 9.1 માં, આવી વસ્તુ રજૂ કરી અનલૉગ કરેલ કોષ્ટકો:

આ સંકેત સાથે, ટેબલ અનલોગ કરેલ તરીકે બનાવવામાં આવે છે. અનલૉગ કરેલા કોષ્ટકો પર લખાયેલ ડેટા લખાણ-આગળના લોગમાંથી પસાર થતો નથી (જુઓ પ્રકરણ 29), જેના કારણે આવા કોષ્ટકો સામાન્ય કરતાં વધુ ઝડપથી કામ કરો. જો કે, તેઓ નિષ્ફળતા માટે રોગપ્રતિકારક નથી; સર્વર નિષ્ફળતા અથવા કટોકટી શટડાઉનના કિસ્સામાં, અનલોગ થયેલ ટેબલ આપમેળે કાપવામાં આવે છે. વધુમાં, અનલોગ કરેલ કોષ્ટકની સામગ્રીઓ નકલ નથી સર્વરોને ગુલામ કરવા માટે. અનલોગ કરેલ ટેબલ પર બનાવેલ કોઈપણ ઈન્ડેક્સ આપમેળે અનલોગ થઈ જાય છે.

ટૂંકમાં કહીએ તો, તે ખૂબ ઝડપી હશે, પરંતુ જો ડેટાબેઝ સર્વર "પડે", તો તે અપ્રિય હશે. પરંતુ આ કેટલી વાર થાય છે, અને શું તમારી ETL પ્રક્રિયા જાણે છે કે ડેટાબેઝને "પુનર્જીવિત" કર્યા પછી તેને "મધ્યમથી" કેવી રીતે યોગ્ય રીતે સુધારવું?...

જો નહીં, અને ઉપરનો કેસ તમારા જેવો જ છે, તો ઉપયોગ કરો UNLOGGEDપરંતુ ક્યારેય નહીં વાસ્તવિક કોષ્ટકો પર આ લક્ષણને સક્ષમ કરશો નહીં, જેમાંથી ડેટા તમને પ્રિય છે.

1.3. કમીટ પર { પંક્તિઓ કાઢી નાખો | ડ્રોપ}

જ્યારે ટેબલ બનાવતી વખતે વ્યવહાર પૂર્ણ થાય ત્યારે આ રચના તમને સ્વચાલિત વર્તણૂકનો ઉલ્લેખ કરવાની મંજૂરી આપે છે.

પર 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. કેવી રીતે પ્રક્રિયા કરવી?

તો, ચાલો આપણો પરિચય કંઈક આના જેવો જોઈએ:

• તમારી પાસે તમારા ડેટાબેઝમાં સંગ્રહિત ક્લાયંટ ડેટા સાથેનું ટેબલ છે 1M રેકોર્ડ
• દરરોજ ગ્રાહક તમને એક નવું મોકલે છે સંપૂર્ણ "છબી"
• અનુભવ પરથી તમે જાણો છો કે સમય સમય પર 10K કરતાં વધુ રેકોર્ડ્સ બદલાતા નથી

આવી પરિસ્થિતિનું ઉત્તમ ઉદાહરણ છે KLADR આધાર — કુલ મળીને ઘણાં બધાં સરનામાં છે, પરંતુ દરેક સાપ્તાહિક અપલોડમાં રાષ્ટ્રીય સ્તરે પણ બહુ ઓછા ફેરફારો (વસાહતોનું નામ બદલવું, શેરીઓનું સંયોજન, નવા મકાનોનો દેખાવ) જોવા મળે છે.

3.1. સંપૂર્ણ સિંક્રનાઇઝેશન અલ્ગોરિધમ

સરળતા માટે, ચાલો કહીએ કે તમારે ડેટાનું પુનર્ગઠન કરવાની પણ જરૂર નથી - ફક્ત ટેબલને ઇચ્છિત સ્વરૂપમાં લાવો, એટલે કે:

• દૂર કરો બધું જે હવે અસ્તિત્વમાં નથી
• અપડેટ પહેલાથી અસ્તિત્વમાં છે અને અપડેટ કરવાની જરૂર છે તે બધું
• દાખલ કરો બધું જે હજુ સુધી થયું નથી

આ ક્રમમાં કામગીરી શા માટે કરવી જોઈએ? કારણ કે આ રીતે ટેબલનું કદ ન્યૂનતમ વધશે (MVCC યાદ રાખો!).

dst થી કાઢી નાખો

ના, અલબત્ત તમે માત્ર બે ઓપરેશન દ્વારા મેળવી શકો છો:

• દૂર કરો (DELETE) સામાન્ય રીતે બધું
• દાખલ કરો બધી નવી છબીમાંથી

પરંતુ તે જ સમયે, MVCC નો આભાર, ટેબલનું કદ બરાબર બમણું વધશે! 1K અપડેટને કારણે કોષ્ટકમાં રેકોર્ડ્સની +10M છબીઓ મેળવવી ખૂબ જ રીડન્ડન્સી છે...

TRUNCATE તા

વધુ અનુભવી વિકાસકર્તા જાણે છે કે સમગ્ર ટેબ્લેટ તદ્દન સસ્તી રીતે સાફ કરી શકાય છે:

• ચોખ્ખુ (TRUNCATE) સમગ્ર ટેબલ
• દાખલ કરો બધી નવી છબીમાંથી

પદ્ધતિ અસરકારક છે, ક્યારેક તદ્દન લાગુ પડે છે, પરંતુ એક સમસ્યા છે... અમે લાંબા સમય માટે 1M રેકોર્ડ્સ ઉમેરીશું, તેથી અમે ટેબલને આટલા સમય માટે ખાલી રાખવાનું પરવડી શકીએ નહીં (જેમ કે તેને એક વ્યવહારમાં લપેટી લીધા વિના થશે).

મતલબ કે:

• અમે શરૂ કરી રહ્યા છીએ લાંબા સમયથી ચાલતો વ્યવહાર
• TRUNCATE લાદે છે એક્સેસ એક્સક્લુઝિવ- અવરોધિત કરવું
• અમે લાંબા સમય સુધી નિવેશ કરીએ છીએ, અને આ સમયે બીજા બધા પણ કરી શકતા નથી SELECT

કંઈક સારું નથી ચાલી રહ્યું...

ટેબલ બદલો... નામ બદલો... / ટેબલ છોડો...

એક વિકલ્પ એ છે કે દરેક વસ્તુને એક અલગ નવા ટેબલમાં ભરો, અને પછી તેને જૂનાની જગ્યાએ ફક્ત નામ આપો. બે બીભત્સ નાની વસ્તુઓ:

• હજુ પણ એક્સેસ એક્સક્લુઝિવ, જોકે નોંધપાત્ર રીતે ઓછો સમય
• આ કોષ્ટક માટેની તમામ ક્વેરી યોજનાઓ/આંકડાઓ રીસેટ છે, ANALYZE ચલાવવાની જરૂર છે
• બધી વિદેશી ચાવીઓ તૂટી ગઈ છે (FK) ટેબલ પર

સિમોન રિગ્સનો WIP પેચ હતો જેણે બનાવવાનું સૂચન કર્યું હતું ALTER-આંકડા અને એફકેને સ્પર્શ્યા વિના, ફાઇલ સ્તરે ટેબલ બોડીને બદલવાની કામગીરી, પરંતુ કોરમ એકત્રિત થયો ન હતો.

કાઢી નાખો, અપડેટ કરો, દાખલ કરો

તેથી, અમે ત્રણ કામગીરીના નોન-બ્લોકીંગ વિકલ્પ પર પતાવટ કરીએ છીએ. લગભગ ત્રણ... આ સૌથી અસરકારક રીતે કેવી રીતે કરવું?

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

ગ્રાહક IDs સાથે એકાઉન્ટ્સને યોગ્ય રીતે સાંકળવા માટે, અમારે પહેલા આ ઓળખકર્તાઓને શોધવા અથવા જનરેટ કરવાની જરૂર છે. ચાલો તેમની નીચે ફીલ્ડ્સ ઉમેરીએ:

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