PostgreSQL Antipatterns: پاسنگ سيٽ ۽ چونڊيو SQL ڏانهن

وقت بوقت هڪ ڊولپر جي ضرورت آهي پيرا ميٽرن جو هڪ سيٽ پاس ڪريو يا درخواست تي مڪمل چونڊ "دروازو تي". ڪڏهن ڪڏهن توهان کي هن مسئلي لاء تمام عجيب حل ملن ٿا.

اچو ته پوئتي وڃون ۽ ڏسون ته ڇا نه ڪرڻ گهرجي، ڇو، ۽ ڪيئن اسان ان کي بهتر ڪري سگهون ٿا.

درخواست جي جسم ۾ قدر جي سڌي داخل ٿيڻ

اهو عام طور تي ڪجهه هن طرح نظر اچي ٿو:

query = "SELECT * FROM tbl WHERE id = " + value

... يا هن وانگر:

query = "SELECT * FROM tbl WHERE id = :param".format(param=value)

اهو طريقو چيو ويو آهي، لکيو ويو آهي ۽ جيتوڻيڪ ٺهيل ڪافي:

تقريبن هميشه اهو آهي SQL انجيڪشن ڏانھن سڌو رستو ۽ ڪاروباري منطق تي غير ضروري لوڊ، جيڪو توهان جي سوال واري لائن کي "گلو" ڪرڻ تي مجبور ڪيو ويو آهي.

اهو طريقو جزوي طور تي جائز ٿي سگهي ٿو صرف جيڪڏهن ضروري هجي ورهاڱي کي استعمال ڪندي PostgreSQL ورزن 10 ۽ هيٺان وڌيڪ ڪارائتو منصوبو حاصل ڪرڻ لاءِ. انهن نسخن ۾، اسڪين ٿيل حصن جي فهرست، منتقل ٿيل پيٽرولن کي حساب ۾ رکڻ کان سواء، صرف درخواست جي جسم جي بنياد تي طئي ڪيو ويندو آهي.

$n-دليل

استعمال ڪريو جڳهه رکندڙ parameters سٺو آهي، اهو توهان کي استعمال ڪرڻ جي اجازت ڏئي ٿو تيار ڪيل بيان, لوڊ کي گھٽائڻ ٻنهي ڪاروباري منطق تي (سوال جو اسٽرنگ صرف هڪ ڀيرو ٺاهي ۽ منتقل ڪيو ويو آهي) ۽ ڊيٽابيس سرور تي (هر سوال جي مثال لاءِ ٻيهر پارس ڪرڻ ۽ شيڊول ڪرڻ جي ضرورت ناهي).

دليلن جو متغير تعداد

مسئلا اسان جو انتظار ڪندا جڏهن اسان اڻڄاتل دليلن کي پاس ڪرڻ چاهيون ٿا:

... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...

جيڪڏهن اسان هن فارم ۾ درخواست کي ڇڏي ڏيو، جيتوڻيڪ اهو اسان کي امڪاني انجيڪشن کان بچائيندو، اهو اڃا تائين درخواست کي ضم ڪرڻ / پارس ڪرڻ جي ضرورت کي ڏسندو. هر آپشن لاءِ دليلن جي تعداد تي منحصر ڪري ٿو. اهو هر وقت ڪرڻ کان بهتر آهي، پر توهان ان کان سواء ڪري سگهو ٿا.

اهو ڪافي آهي ته پاس ڪرڻ لاء صرف هڪ پيٽرولر تي مشتمل آهي serialized صف جي نمائندگي:

... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'

فرق صرف اهو آهي ته واضح طور تي دليل کي گهربل صف جي قسم ۾ تبديل ڪرڻ جي ضرورت آهي. پر اهو مسئلو ناهي، ڇو ته اسان اڳ ۾ ئي ڄاڻون ٿا ته اسان ڪيڏانهن وڃي رهيا آهيون.

نموني جي منتقلي (ميٽرڪس)

عام طور تي اهي سڀ قسم جا اختيار آهن ڊيٽا سيٽ کي منتقل ڪرڻ لاءِ ڊيٽابيس ۾ داخل ڪرڻ لاءِ “هڪ درخواست ۾”:

INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...

مٿي بيان ڪيل مسئلن کان علاوه "ٻيهر گلو ڪرڻ" جي درخواست سان، اهو پڻ اسان جي اڳواڻي ڪري سگهي ٿو ميموري ختم ٿي وئي آهي ۽ سرور حادثو. سبب سادو آهي - PG دليلن لاءِ اضافي ياداشت رکي ٿو، ۽ سيٽ ۾ رڪارڊ جو تعداد صرف ڪاروباري منطق جي ايپليڪيشن جي ضرورتن جي ڪري محدود آهي. خاص طور تي ڪلينڪل ڪيسن ۾ مون کي ڏسڻو پوندو "نمبر" دليل $9000 کان وڌيڪ آهن - ائين نه ڪريو.

اچو ته اڳ ۾ ئي استعمال ڪندي درخواست کي ٻيهر لکون "ٻه-سطح" سيريلائيزيشن:

INSERT INTO tbl
SELECT
  unnest[1]::text k
, unnest[2]::integer v
FROM (
  SELECT
    unnest($1::text[])::text[] -- $1 : '{"{a,1}","{b,2}","{c,3}","{d,4}"}'
) T;

ها، هڪ صف جي اندر "پيچيده" قدرن جي صورت ۾، انهن کي حوالن سان گهيرو ڪيو وڃي.
اهو واضح آهي ته هن طريقي سان توهان "وڌايو" ڪري سگهو ٿا هڪ چونڊ کي صوابديدي انگن اکرن سان.

اڻڄاتل، اڻڄاڻ، ...

وقت بوقت پاسنگ جا آپشن موجود آهن "Arays of arrays" جي بدران "Arays of arrays" جن جو مون ذڪر ڪيو آهي آخري مضمون ۾:

SELECT
  unnest($1::text[]) k
, unnest($2::integer[]) v;

هن طريقي سان، جيڪڏهن توهان غلطي ڪريو ٿا جڏهن مختلف ڪالمن جي قيمتن جي فهرست ٺاهي، اهو حاصل ڪرڻ بلڪل آسان آهي غير متوقع نتيجا، جيڪو پڻ سرور ورزن تي منحصر آهي:

-- $1 : '{a,b,c}', $2 : '{1,2}'
-- PostgreSQL 9.4
k | v
-----
a | 1
b | 2
c | 1
a | 2
b | 1
c | 2
-- PostgreSQL 11
k | v
-----
a | 1
b | 2
c |

JSON

نسخو 9.3 کان وٺي، پوسٽ گري ايس ايس ايل کي json قسم سان ڪم ڪرڻ لاء مڪمل ڪم ڪيو ويو آهي. تنهن ڪري، جيڪڏهن ان پٽ پيٽرولر جي تعريف توهان جي برائوزر ۾ ٿئي ٿي، توهان ان کي اتي ئي ٺاهي سگهو ٿا json اعتراض SQL سوال لاءِ:

SELECT
  key k
, value v
FROM
  json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'

پوئين نسخن لاء، ساڳيو طريقو استعمال ڪري سگهجي ٿو هر هڪ (هڪ اسٽور)، پر hstore ۾ پيچيده شيون فرار ٿيڻ سان صحيح "convolution" مسئلا پيدا ڪري سگھن ٿا.

json_populate_recordset

جيڪڏهن توهان اڳ ۾ ڄاڻو ٿا ته "انپٽ" json صف مان ڊيٽا ڪجهه ٽيبل ڀرڻ لاءِ استعمال ڪئي ويندي، توهان "dereferencing" فيلڊ ۾ گهڻو ڪجهه بچائي سگهو ٿا ۽ json_populate_recordset فنڪشن استعمال ڪندي انهن کي گهربل قسمن ۾ ڪاسٽ ڪري سگهو ٿا:

SELECT
  *
FROM
  json_populate_recordset(
    NULL::pg_class
  , $1::json -- $1 : '[{"relname":"pg_class","oid":1262},{"relname":"pg_namespace","oid":2615}]'
  );

json_to_recordset

۽ هي فنڪشن صرف "وڌايو" ڪندو "وڌايو" شين جي پاس ڪيل صف کي چونڊ ۾، بغير ٽيبل فارميٽ تي ڀروسو ڪرڻ جي:

SELECT
  *
FROM
  json_to_recordset($1::json) T(k text, v integer);
-- $1 : '[{"k":"a","v":1},{"k":"b","v":2}]'
k | v
-----
a | 1
b | 2

عارضي ٽيبل

پر جيڪڏهن منتقل ٿيل نموني ۾ ڊيٽا جو مقدار تمام وڏو آهي، ته پوء ان کي هڪ سيريل ٿيل پيٽرولر ۾ اڇلائڻ ڏکيو ۽ ڪڏهن ڪڏهن ناممڪن آهي، ڇاڪاڻ ته اهو هڪ وقت جي ضرورت آهي. ميموري جي وڏي مقدار کي مختص ڪريو. مثال طور، توهان کي ضرورت آهي هڪ وڏي پئڪيج تي ڊيٽا جو هڪ وڏي پئڪيج گڏ ڪرڻ جي واقعن تي هڪ خارجي سسٽم کان ڊگھي وقت تائين، ۽ پوء توهان چاهيو ٿا ان کي پروسيس ڪرڻ چاهيو ٿا هڪ ڀيرو ڊيٽابيس جي پاسي تي.

هن معاملي ۾، استعمال ڪرڻ لاء بهترين حل آهي عارضي ٽيبل:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком

طريقو سٺو آهي ڪڏهن ڪڏهن وڏي مقدار جي منتقلي لاء ڊيٽا.
ان جي ڊيٽا جي جوڙجڪ کي بيان ڪرڻ جي نقطي نظر کان، هڪ عارضي جدول صرف هڪ طريقي سان "باقاعده" هڪ کان مختلف آهي. pg_class سسٽم ٽيبل ۾، ۽ ۾ pg_type, pg_depend, pg_attribute, pg_attrdef, ... - ڪجه‍ہ بہ نہ.

تنهن ڪري، ويب سسٽم ۾ انهن مان هر هڪ لاء مختصر-رهندڙ ڪنيڪشن جي وڏي تعداد سان، اهڙي ٽيبل هر وقت نئين سسٽم رڪارڊ ٺاهي سگهندا، جيڪي ڊاهي ويندا آهن جڏهن ڊيٽابيس سان ڪنيڪشن بند ٿي ويندي آهي. آخرڪار، TEMP TABLE جو بي قابو استعمال pg_catalog ۾ جدولن جي ”سوجن“ جو سبب بڻجي ٿو ۽ انهن کي استعمال ڪندڙ ڪيترن ئي عملن کي سست ڪرڻ.
يقينن، اهو استعمال ڪرڻ سان معاملو ڪري سگهجي ٿو دوراني گذرڻ VACUUM FULL سسٽم جي فهرست جي جدولن جي مطابق.

سيشن متغير

اچو ته فرض ڪريون ته اڳوڻي ڪيس مان ڊيٽا کي پروسيسنگ هڪ SQL سوال لاءِ ڪافي پيچيده آهي، پر توهان ان کي اڪثر ڪرڻ چاهيو ٿا. اهو آهي، اسان پروسيسنگ پروسيسنگ ۾ استعمال ڪرڻ چاهيون ٿا DO بلاڪ، پر عارضي جدولن ذريعي ڊيٽا جي منتقلي کي استعمال ڪرڻ تمام مهانگو هوندو.

اسان پڻ استعمال ڪرڻ جي قابل نه هوندا $n-پيراميٽر هڪ گمنام بلاڪ ڏانهن منتقل ڪرڻ لاء. سيشن متغير ۽ فنڪشن اسان کي هن صورتحال مان نڪرڻ ۾ مدد ڪندي موجوده_سيٽنگ.

نسخو 9.2 کان اڳ ان کي اڳ ۾ ترتيب ڏيڻ ضروري هو خاص نالي جي جڳھ custom_variable_classes "توهان" سيشن متغير لاء. موجوده نسخن تي توهان هن طرح ڪجهه لکي سگهو ٿا:

SET my.val = '{1,2,3}';
DO $$
DECLARE
  id integer;
BEGIN
  FOR id IN (SELECT unnest(current_setting('my.val')::integer[])) LOOP
    RAISE NOTICE 'id : %', id;
  END LOOP;
END;
$$ LANGUAGE plpgsql;
-- NOTICE:  id : 1
-- NOTICE:  id : 2
-- NOTICE:  id : 3

ٻيا حل ڳولي سگهجن ٿا ٻين سپورٽ ڪيل طريقيڪار ٻولين ۾.

ڇا توهان ڪي ٻيا طريقا ڄاڻو ٿا؟ تبصرن ۾ حصيداري ڪريو!

جو ذريعو: www.habr.com