PostgreSQL antipatternlari: to'plamlar va tanlashlarni SQL-ga o'tkazish

Vaqti-vaqti bilan ishlab chiquvchi kerak so'rovga parametrlar to'plamini yoki hatto butun tanlovni o'tkazish "kirishda". Ba'zida bu muammoning juda g'alati echimlari mavjud.

Keling, "teskari tomondan" boramiz va buni qanday qilmaslik kerakligini, nima uchun va qanday qilib yaxshiroq qilish mumkinligini ko'rib chiqamiz.

Qiymatlarni so'rov tanasiga to'g'ridan-to'g'ri "qo'shish"

Odatda shunday ko'rinadi:

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

... yoki shunga o'xshash:

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

Bu usul haqida aytilgan, yozilgan va hatto chizilgan yetarli:

Deyarli har doim shunday SQL in'ektsiyasiga to'g'ridan-to'g'ri yo'l va sizning so'rovlar qatoringizni "yopishtirishga" majbur bo'lgan biznes mantig'iga qo'shimcha yuk.

Bunday yondashuv faqat kerak bo'lganda qisman oqlanishi mumkin. qismlarga ajratishdan foydalaning yanada samarali reja uchun PostgreSQL 10 va undan past versiyalarida. Ushbu versiyalarda skanerlangan bo'limlar ro'yxati uzatilgan parametrlarni hisobga olmagan holda, faqat so'rov organi asosida aniqlanadi.

$n argumentlar

foydalanish joy egalari parametrlari yaxshi, u foydalanish imkonini beradi Tayyorlangan bayonotlar, ham biznes mantig'iga (so'rovlar qatori faqat bir marta shakllantiriladi va uzatiladi) va ma'lumotlar bazasi serveridagi yukni kamaytirish (so'rovning har bir misoli uchun qayta tahlil qilish va rejalashtirish talab qilinmaydi).

Argumentlarning o'zgaruvchan soni

Noma'lum miqdordagi argumentlarni oldindan o'tkazmoqchi bo'lganimizda, bizni muammolar kutmoqda:

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

Agar siz so'rovni ushbu shaklda qoldirsangiz, u bizni potentsial in'ektsiyalardan qutqarsa ham, bu so'rovni yopishtirish / tahlil qilish zarurligiga olib keladi. argumentlar sonidan har bir variant uchun. Buni har safar qilishdan ko'ra allaqachon yaxshiroq, lekin siz usiz ham qila olasiz.

O'z ichiga olgan faqat bitta parametrni o'tkazish kifoya massivning seriyali tasviri:

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

Yagona farq argumentni kerakli massiv turiga aniq aylantirish zaruratidir. Ammo bu muammo tug'dirmaydi, chunki biz qaerga murojaat qilayotganimizni oldindan bilamiz.

Namuna uzatish (matritsa)

Odatda bu ma'lumotlar to'plamini ma'lumotlar bazasiga "bir so'rovda" kiritish uchun uzatishning barcha turlari:

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

Yuqorida tavsiflangan so'rovni "qayta yopishtirish" bilan bog'liq muammolarga qo'shimcha ravishda, bu bizni ham olib kelishi mumkin xotiradan va server ishdan chiqishi. Sababi oddiy - PG argumentlar uchun qo'shimcha xotirani saqlab qoladi va to'plamdagi yozuvlar soni faqat Wishlist biznes mantiqiy ilovasi bilan cheklanadi. Ayniqsa, klinik holatlarda ko'rish kerak edi $9000 dan ortiq "raqamli" argumentlar - bunday qilish kerak emas.

Keling, so'rovni allaqachon qo'llash orqali qayta yozamiz "ikki darajali" ketma-ketlashtirish:

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;

Ha, massiv ichidagi "murakkab" qiymatlar bo'lsa, ular tirnoq bilan belgilanishi kerak.
Shu tarzda tanlovni ixtiyoriy sonli maydonlar bilan "kengaytirish" mumkinligi aniq.

unnest, unnest, …

Vaqti-vaqti bilan men aytib o'tgan bir nechta "massivlar massivi" o'rniga o'tish variantlari mavjud. oxirgi maqolada:

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

Ushbu usul yordamida, agar siz turli ustunlar uchun qiymatlar ro'yxatini yaratishda xatoga yo'l qo'ysangiz, uni to'liq olish juda oson. kutilmagan natijalar, bu ham server versiyasiga bog'liq:

-- $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 versiyasidan boshlab PostgreSQL json turi bilan ishlash uchun to'liq funksiyalarga ega. Shuning uchun, agar sizning kiritish parametrlaringiz brauzerda aniqlangan bo'lsa, siz o'sha erda va shakllantirishingiz mumkin SQL so'rovi uchun json ob'ekti:

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

Oldingi versiyalar uchun xuddi shu usuldan foydalanish mumkin har bir (hstore), lekin hstore'da murakkab ob'ektlardan qochish bilan to'g'ri "katlama" muammolarga olib kelishi mumkin.

json_populate_recordset

Agar siz "input" json massividagi ma'lumotlar qandaydir jadvalni to'ldirish uchun ketishini oldindan bilsangiz, json_populate_recordset funktsiyasidan foydalanib, "dereferencing" maydonlarida ko'p narsalarni saqlashingiz va kerakli turlarga uzatishingiz mumkin:

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

json_to_recordset

Va bu funktsiya jadval formatiga tayanmasdan, o'tkazilgan ob'ektlar qatorini shunchaki "kengaytiradi":

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

Vaqtinchalik stol

Ammo agar uzatilgan namunadagi ma'lumotlar miqdori juda katta bo'lsa, uni bitta seriyali parametrga tashlash qiyin va ba'zan imkonsizdir, chunki bu bir martalik talab qiladi. katta xotira ajratish. Misol uchun, siz uzoq, uzoq vaqt davomida tashqi tizimdan voqea ma'lumotlarining katta partiyasini to'plashingiz kerak va keyin siz ma'lumotlar bazasi tomonida bir martalik ishlov berishni xohlaysiz.

Bunday holda, eng yaxshi yechim foydalanish bo'ladi vaqtinchalik jadvallar:

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

Usul yaxshi katta hajmlarning kamdan-kam uzatilishi uchun ma'lumotlar.
O'z ma'lumotlarining tuzilishini tavsiflash nuqtai nazaridan, vaqtinchalik jadval "oddiy" jadvaldan faqat bitta xususiyat bilan farq qiladi. pg_class tizim jadvalidavaqt ichida pg_type, pg_depend, pg_attribute, pg_attrdef, ... - va umuman hech narsa.

Shuning uchun, ularning har biri uchun qisqa muddatli ulanishlar soni ko'p bo'lgan veb-tizimlarda bunday jadval har safar yangi tizim yozuvlarini hosil qiladi, ular ma'lumotlar bazasiga ulanish yopilganda o'chiriladi. Natijada, TEMP TABLE dan nazoratsiz foydalanish pg_catalogdagi jadvallarning "shishishiga" olib keladi. va ulardan foydalanadigan ko'plab operatsiyalarni sekinlashtirish.
Albatta, bu bilan kurashish mumkin davriy o'tish VACUUM FULL tizim katalogi jadvallariga muvofiq.

Seans o'zgaruvchilari

Aytaylik, oldingi holatdan ma'lumotlarni qayta ishlash bitta SQL so'rovi uchun juda murakkab, lekin siz buni tez-tez qilishni xohlaysiz. Ya'ni, biz protsessual ishlov berishdan foydalanmoqchimiz Bloklash, lekin vaqtinchalik jadvallar orqali ma'lumotlarni uzatishdan foydalanish juda qimmatga tushadi.

Biz anonim blokga o'tish uchun $n-parametrlardan ham foydalana olmaymiz. Seans o'zgaruvchilari va funksiya bizga vaziyatdan chiqishga yordam beradi. joriy_sozlama.

9.2 versiyasidan oldin siz oldindan sozlashingiz kerak edi maxsus nom maydoni moslashtirilgan_o'zgaruvchan_sinflar "ularning" seans o'zgaruvchilari uchun. Joriy versiyalarda siz shunday yozishingiz mumkin:

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

Boshqa qo'llab-quvvatlanadigan protsessual tillarda boshqa echimlar mavjud.

Ko'proq usullarni bilasizmi? Izohlarda baham ko'ring!

Manba: www.habr.com