PostgreSQL Antipatterns: 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 იტოვებს დამატებით მეხსიერებას არგუმენტებისთვის, ხოლო ჩანაწერების რაოდენობა ნაკრებში შემოიფარგლება მხოლოდ ბიზნეს ლოგიკის აპლიკაციის Wishlist-ით. განსაკუთრებით კლინიკურ შემთხვევებში საჭირო იყო ნახვა $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-ში რთული ობიექტების გაქცევით სწორმა „დაკეცვამ“ შეიძლება გამოიწვიოს პრობლემები.

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-პარამეტრები ანონიმურ ბლოკზე გადასასვლელად. სესიის ცვლადები და ფუნქცია დაგვეხმარება სიტუაციიდან გამოსვლაში. მიმდინარე_პარამეტრები.

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