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 ۾، تنهنڪري ان ۾ INSERT/UPDATE/DELETE خاص طور تي تيز آهي.

پر جيئن ته هڪ عارضي ٽيبل اڃا تائين هڪ "تقريبا عام" ٽيبل آهي، ان کي هڪ نقل تي ٺاهي نٿو سگهجي. گهٽ ۾ گهٽ هن وقت، جيتوڻيڪ لاڳاپيل پيچ گهڻو وقت تائين گردش ڪري رهيو آهي.

1.2. اڻ لاگ ٿيل ٽيبل

پر توهان کي ڇا ڪرڻ گهرجي، مثال طور، جيڪڏهن توهان وٽ ڪجهه قسم جو مشڪل ETL عمل آهي جيڪو هڪ ٽرانزيڪشن ۾ لاڳو نه ٿو ڪري سگهجي، پر توهان اڃا تائين pgbouncer ٽرانزيڪشن موڊ ۾؟ ..

يا ڊيٽا جي وهڪري ايتري وڏي آهي ته هڪ ڪنيڪشن تي ڪافي بينڊوڊٿ نه آهي ڊيٽابيس مان (پڙهو، هڪ پروسيس في سي پي يو)؟...

يا ڪجهه آپريشن ٿي رهيا آهن هم وقت سازي سان مختلف رابطن ۾؟ ..

هتي صرف هڪ اختيار آهي - عارضي طور تي غير عارضي ٽيبل ٺاھيو. پن، ها. اهو آهي:

• وڌ ۾ وڌ بي ترتيب نالن سان ”منهنجو پنهنجو“ ٽيبل ٺاهيو ته جيئن ڪنهن سان ٽڪراءُ نه ٿئي
• ختم ڪريو: انھن کي ھڪڙي خارجي ذريعن کان ڊيٽا سان ڀريو
• منتقلي: ڪنورٽ ٿيل، ڀرجي ويو اھم ڳنڍڻ وارن شعبن ۾
• بار: تيار ڪيل ڊيٽا کي ٽارگيٽ ٽيبل ۾ داخل ڪيو
• ختم ٿيل "منهنجو" ٽيبل

۽ هاڻي - عطر ۾ هڪ مکڻ. حقيقت ۾، PostgreSQL ۾ سڀ لکن ٿا ٻه ڀيرا - WAL ۾ پهريون، پوءِ ٽيبل/انڊيڪس باڊيز ۾. اهو سڀ ڪجهه ڪيو ويو آهي ACID کي سپورٽ ڪرڻ ۽ وچ ۾ ڊيٽا جي نمائش کي درست ڪرڻ COMMIT'نوٽي ۽ ROLLBACK'نال ٽرانزيڪشن.

پر اسان کي هن جي ضرورت ناهي! اسان وٽ سڄو عمل آهي يا ته اهو مڪمل طور تي ڪامياب هو يا اهو نه هو.. اهو مسئلو ناهي ته ڪيترا وچولي ٽرانزيڪشن موجود هوندا - اسان کي "وچ کان عمل جاري رکڻ" ۾ دلچسپي نه آهي، خاص طور تي جڏهن اهو واضح ناهي ته اهو ڪٿي هو.

هن کي ڪرڻ لاء، PostgreSQL ڊولپرز، واپس ورزن 9.1 ۾، هڪ اهڙي شيء متعارف ڪرايو جيئن UNLOGGED ٽيبل:

ھن اشاري سان، جدول ٺاھيو ويو آھي unlogged طور. اڻ-لاگ ٿيل جدولن تي لکيل ڊيٽا رائٽ-ايڊ لاگ (ڏسو باب 29) جي ذريعي نه ٿو وڃي، جنهن جي ڪري اهڙين جدولن کي معمول کان گهڻو تيز ڪم. تنهن هوندي به, اهي ناڪامي لاء مدافعتي نه آهن; سرور جي ناڪامي يا ايمرجنسي بند ٿيڻ جي صورت ۾، هڪ اڻ لاگ ٿيل ٽيبل خودڪار طور تي ڪٽيل. اضافي طور تي، unlogged ٽيبل جي مواد نقل نه ڪيو ويو غلام سرورن ڏانهن. اڻ لاگ ٿيل ٽيبل تي ٺاهيل ڪي به انڊيڪس پاڻمرادو اڻ لاگ ٿيل ٿي ويندا.

مختصر طور تي، اهو تمام گهڻو تيز ٿيندو، پر جيڪڏهن ڊيٽابيس سرور "گري ٿو"، اهو ناپسنديده ٿيندو. پر اهو ڪيترو وقت ٿئي ٿو، ۽ ڇا توهان جي 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-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 جي مهرباني، ٽيبل جي سائيز بلڪل ٻه ڀيرا وڌي ويندي! 1 ڪلو اپڊيٽ جي ڪري ٽيبل ۾ رڪارڊ جون +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;

صحيح طريقي سان اڪائونٽن کي ڪسٽمر 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