ProHoster > блог > Администрација > PostgreSQL Antipatterns: пренесување поставува и избира во SQL

PostgreSQL Antipatterns: пренесување поставува и избира во SQL

Одвреме-навреме има потреба од развивач помине сет на параметри или дури и цел избор на барањето „на влезот“. Понекогаш наидувате на многу чудни решенија за овој проблем.
PostgreSQL Antipatterns: пренесување поставува и избира во SQL
Ајде да се вратиме наназад и да видиме што да не правиме, зошто и како можеме подобро.

Директно вметнување на вредности во телото на барањето

Обично изгледа вака:

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

...или вака:

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

Овој метод е кажано, напишано и дури и нацртани многу:

PostgreSQL Antipatterns: пренесување поставува и избира во SQL

Речиси секогаш ова е директна патека до 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 може да предизвика проблеми.

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 и забавување на многу операции кои ги користат.
Се разбира, ова може да се справи со користење периодичен премин ВАКУУМ ПОЛН според табелите на системскиот каталог.

Променливи на сесија

Да претпоставиме дека обработката на податоците од претходниот случај е доста сложена за едно 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

Додадете коментар