Antipatterns PostgreSQL: ارسال مجموعه ها و انتخاب ها به SQL

از زمان به زمان، توسعه دهنده نیاز دارد مجموعه ای از پارامترها یا حتی کل انتخاب را به درخواست ارسال کنید "در ورودی". گاهی اوقات راه حل های بسیار عجیبی برای این مشکل وجود دارد.

بیایید به عقب برگردیم و ببینیم چه کاری را نباید انجام داد، چرا، و چگونه می‌توانیم آن را بهتر انجام دهیم.

"درج" مستقیم مقادیر در بدن درخواست

معمولا چیزی شبیه این به نظر می رسد:

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

... یا مثل این:

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

درباره این روش گفته شده، نوشته شده و حتی کشیده شده است کافی:

تقریبا همیشه همینطور است مسیر مستقیم به تزریق SQL و بار غیرضروری بر روی منطق تجاری، که مجبور می شود خط درخواست شما را "چسب" کند.

این رویکرد تنها در صورت لزوم می تواند تا حدی توجیه شود. از پارتیشن بندی استفاده کنید در PostgreSQL نسخه 10 و پایین تر برای یک برنامه کارآمدتر. در این نسخه ها، لیست بخش های اسکن شده بدون در نظر گرفتن پارامترهای ارسالی، تنها بر اساس بدنه درخواست تعیین می شود.

$n آرگومان

استفاده نگهدارنده های مکان پارامترها خوب است، به شما امکان استفاده از آن را می دهد بیانیه های تهیه شده، کاهش بار هم در منطق تجاری (رشته پرس و جو فقط یک بار تولید و ارسال می شود) و هم در سرور پایگاه داده (تجزیه مجدد و زمان بندی برای هر نمونه پرس و جو لازم نیست).

تعداد متغیر آرگومان

زمانی که بخواهیم تعداد ناشناخته ای از آرگومان ها را از قبل ارسال کنیم، مشکلات در انتظار ما خواهد بود:

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

اگر درخواست را در این فرم رها کنیم، اگرچه از ما در برابر تزریقات احتمالی محافظت می کند، اما همچنان منجر به نیاز به ادغام/تجزیه درخواست می شود. برای هر گزینه از تعداد آرگومان ها. در حال حاضر بهتر از انجام هر بار آن است، اما شما می توانید بدون آن انجام دهید.

کافی است فقط یک پارامتر حاوی را پاس کنید نمایش سریالی یک آرایه:

... 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;

بله، در مورد مقادیر "پیچیده" در داخل یک آرایه، آنها باید با نقل قول قاب شوند.
واضح است که از این طریق می توانید انتخاب را با تعداد دلخواه فیلد "بسط" دهید.

بی آشفتگی، بی آشفتگی، …

هر از گاهی گزینه هایی برای عبور به جای "آرایه آرایه ها" چندین "آرایه از ستون ها" وجود دارد که به آنها اشاره کردم. در آخرین مقاله:

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، PostgreSQL دارای توابع کامل برای کار با نوع json است. بنابراین، اگر تعریف پارامترهای ورودی در مرورگر شما اتفاق می افتد، می توانید آن را همانجا شکل دهید شی json برای پرس و جوی SQL:

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

برای نسخه های قبلی نیز می توان از همین روش استفاده کرد هر (hstore)، اما "تا کردن" صحیح با فرار از اشیاء پیچیده در hstore می تواند مشکلاتی ایجاد کند.

json_populate_recordset

اگر از قبل می‌دانید که داده‌های آرایه json "ورودی" برای پر کردن برخی از جدول‌ها استفاده می‌شود، می‌توانید با استفاده از تابع 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 برای ارسال به یک بلوک ناشناس استفاده کنیم. متغیرهای جلسه و تابع به ما کمک می کنند تا از این وضعیت خارج شویم. current_setting.

قبل از نسخه 9.2، باید از قبل تنظیمات را انجام می دادید فضای نام ویژه کلاسهای_متغیر_متغییر برای متغیرهای جلسه "آنها". در نسخه های فعلی، می توانید چیزی شبیه به این بنویسید:

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