ProHoster > Pūnaewele > Nā Administration > Nā Antipatterns PostgreSQL: hele i nā hoʻonohonoho a koho iā SQL

Nā Antipatterns PostgreSQL: hele i nā hoʻonohonoho a koho iā SQL

Pono ka mea hoʻomohala i kēlā me kēia manawa e hāʻawi i kahi hoʻonohonoho o nā ʻāpana a i ʻole kahi koho holoʻokoʻa i ka noi "ma ka puka komo". I kekahi manawa ʻike ʻoe i nā hoʻonā ʻano ʻē loa i kēia pilikia.
Nā Antipatterns PostgreSQL: hele i nā hoʻonohonoho a koho iā SQL
E hoʻi kākou i hope a ʻike i ka mea e hana ʻole ai, no ke aha, a pehea e hana maikaʻi ai.

Hoʻokomo pololei i nā waiwai i loko o ke kino noi

He mea maʻamau e like me kēia:

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

...a i ʻole penei:

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

Ua ʻōlelo ʻia kēia ʻano, kākau ʻia a huki ʻia nui:

Nā Antipatterns PostgreSQL: hele i nā hoʻonohonoho a koho iā SQL

ʻaneʻane mau kēia ala pololei i nā hoʻokolo SQL a me ka ukana pono ʻole i ka loiloi ʻoihana, i koi ʻia e "hoʻopili" i kāu laina nīnau.

Hiki ke ho'āpono hapa wale ʻia kēia ala inā pono ka hoʻohana ʻana i ka ʻāpana i nā mana PostgreSQL 10 a ma lalo e kiʻi i kahi hoʻolālā ʻoi aku ka maikaʻi. Ma kēia mau mana, ua hoʻoholo ʻia ka papa inoa o nā ʻāpana scanned me ka ʻole e noʻonoʻo i nā ʻāpana i hoʻouna ʻia, ma ke kumu wale nō o ke kino noi.

$n-hoʻopaʻapaʻa

E hoʻohana nā mea kau wahi maikaʻi nā ʻāpana, hiki iā ʻoe ke hoʻohana OLELO HOOLAHA, e ho'ēmi ana i ka ukana ma luna o ka 'oihana logic (ua ho'okumu 'ia ke kaula nīnau a ho'ouna 'ia ho'okahi wale nō) a ma ka waihona waihona ('a'ole koi 'ia ka ho'oponopono hou 'ana a me ka ho'onohonoho 'ana no kēlā me kēia nīnau nīnau).

Ka helu hoʻopaʻapaʻa

E kali ana nā pilikia iā mākou ke makemake mākou e hāʻawi i kahi helu ʻike ʻole o nā hoʻopaʻapaʻa:

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

Inā haʻalele mākou i ka noi ma kēia palapala, ʻoiai e pale aku ia iā mākou mai nā injections hiki ke alakaʻi ʻia i ka pono e hoʻohui / parse i ka noi. no kēlā me kēia koho ma muli o ka helu o nā manaʻo. ʻOi aku ka maikaʻi ma mua o ka hana ʻana i kēlā me kēia manawa, akā hiki iā ʻoe ke hana me ka ʻole.

Ua lawa ka hele ʻana i hoʻokahi ʻāpana i loaʻa hōʻike hōʻike serialized array:

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

ʻO ka ʻokoʻa wale nō ka pono e hoʻololi i ka hoʻopaʻapaʻa i ke ʻano array makemake. ʻAʻole naʻe kēia pilikia, ʻoiai ua ʻike mua mākou i kahi e hele ai.

Ka hoʻoili ʻana i kahi laʻana (matrix)

ʻO ka maʻamau kēia nā ʻano koho āpau no ka hoʻoili ʻana i nā pūʻulu ʻikepili no ka hoʻokomo ʻana i loko o ka waihona "ma kahi noi hoʻokahi":

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

Ma waho aʻe o nā pilikia i ho'ākākaʻia ma luna nei me ka "hoʻopili hou" i ka noi, hiki iā ia ke alakaʻi iā mākou pau ka hoomanao ana a hāʻule ka server. He maʻalahi ke kumu - mālama ʻo PG i kahi hoʻomanaʻo hou no nā hoʻopaʻapaʻa, a ʻo ka helu o nā moʻolelo i ka hoʻonohonoho i kaupalena ʻia e nā pono noi o ka loiloi ʻoihana. Ma nā hihia lapaʻau pono wau e ʻike ʻOi aku ka nui o nā manaʻo "helu" ma mua o $9000 - mai hana pela.

E kākau hou i ka noi me ka hoʻohana mua "ʻelua-pae" serialization:

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;

ʻAe, i ka hihia o nā waiwai "paʻakikī" i loko o kahi ʻano, pono lākou e hoʻopuni ʻia e nā ʻōlelo.
ʻIke ʻia ma kēia ala hiki iā ʻoe ke "hoʻonui" i kahi koho me ka helu ʻole o nā māla.

ʻoluʻolu ʻole, ʻaʻole pono,…

Mai kēlā manawa kēia manawa aia nā koho no ka hele ʻana ma kahi o kahi "array of arrays" i kekahi mau "arrays of columns" aʻu i ʻōlelo ai. ma ka ʻatikala hope loa:

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

Me kēia ʻano, inā hana hewa ʻoe i ka wā e hana ai i nā papa inoa o nā waiwai no nā kolamu like ʻole, maʻalahi loa ke kiʻi. nā hopena i manaʻo ʻole ʻia, pili pū i ka mana kikowaena:

-- $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

Mai ka mana 9.3, ua loaʻa iā PostgreSQL nā hana piha no ka hana ʻana me ke ʻano json. No laila, inā loaʻa ka wehewehe ʻana o nā ʻāpana hoʻokomo i kāu polokalamu kele pūnaewele, hiki iā ʻoe ke hana ma laila json mea no ka nīnau SQL:

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

No nā mana mua, hiki ke hoʻohana ʻia ke ʻano like kēlā me kēia(hstore), akā hiki ke hoʻopilikia i ka "convolution" me ka pakele ʻana i nā mea paʻakikī i ka hale kūʻai.

json_populate_recordset

Inā ʻike mua ʻoe e hoʻohana ʻia ka ʻikepili mai ka "input" json array e hoʻopiha i kahi papaʻaina, hiki iā ʻoe ke mālama nui i nā kahua "dereferencing" a hoʻolei iā lākou i nā ʻano i koi ʻia ma ka hoʻohana ʻana i ka hana 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

A ʻo kēia hana e "hoʻonui" i ke ʻano o nā mea i koho ʻia, me ka ʻole o ka hilinaʻi ʻana i ke ʻano papa:

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

PAPA KUHIKUHI

Akā inā he nui loa ka nui o ka ʻikepili i ka laʻana i hoʻoili ʻia, a laila paʻakikī ka hoʻolei ʻana iā ia i hoʻokahi parameter serialized a i kekahi manawa hiki ʻole, no ka mea e koi ana i hoʻokahi manawa. hoʻokaʻawale i ka nui o ka hoʻomanaʻo. Eia kekahi laʻana, ponoʻoe e hōʻiliʻili i kahi pūʻolo nui o kaʻikepili ma nā hanana mai kahi pūnaewele waho no ka manawa lōʻihi, a laila makemakeʻoe e hana i hoʻokahi manawa ma kaʻaoʻao waihona.

I kēia hihia, ʻo ka hopena maikaʻi loa e hoʻohana nā papa ʻaina manawa:

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

Maikaʻi ke ʻano no ka hoʻoili ʻana i nā puke nui i kekahi manawa ʻikepili.
Mai ka manaʻo o ka wehewehe ʻana i ke ʻano o kāna ʻikepili, ʻokoʻa ka papa ʻaina pōkole mai kahi "maʻamau" ma ke ala hoʻokahi. ma ka papa pg_class system, a in pg_type, pg_depend, pg_attribute, pg_attrdef, … - ʻaʻohe mea.

No laila, i loko o nā pūnaewele pūnaewele me ka nui o nā pilina pōkole no kēlā me kēia o lākou, e hoʻopuka kēlā papaʻaina i nā moʻolelo pūnaewele hou i kēlā me kēia manawa, i holoiʻia i ka wā e paniʻia ai ka pilina i ka waihona. I ka hopena, ʻO ka hoʻohana ʻole ʻia o TEMP TABLE ke alakaʻi i ka "huhū" o nā papa ma pg_catalog a hoʻolohi i nā hana he nui e hoʻohana iā lākou.
ʻOiaʻiʻo, hiki ke hana i kēia me ka hoʻohana pauku manawa VACUUM PIHA e like me nā papa helu ʻōnaehana.

Nā Hoʻololi Kau

E noʻonoʻo kākou he paʻakikī ka hoʻoponopono ʻana i ka ʻikepili mai ka hihia mua no hoʻokahi nīnau SQL, akā makemake ʻoe e hana pinepine. ʻO ia, makemake mākou e hoʻohana i ke kaʻina hana i loko HANA poloka, akā, ʻoi aku ka pipiʻi o ka hoʻohana ʻana i ka hoʻoili ʻikepili ma o nā papa ʻaina.

ʻAʻole hiki iā mākou ke hoʻohana i $n-parameters no ka hele ʻana i kahi poloka inoa ʻole. Na nā ʻano hoʻololi a me ka hana e kōkua iā mākou e haʻalele i kēia kūlana hoʻonohonoho_keia manawa.

Ma mua o ka mana 9.2 pono e hoʻonohonoho mua wahi inoa kūikawā nā papa_pili_pili no nā mea hoʻololi kau "kau". Ma nā mana o kēia manawa hiki iā ʻoe ke kākau i kekahi mea e like me kēia:

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

Hiki ke loaʻa nā haʻina ʻē aʻe ma nā ʻōlelo kaʻina hana i kākoʻo ʻia.

ʻIke paha ʻoe i nā ala ʻē aʻe? Kaʻana like ma nā manaʻo!

Source: www.habr.com

Pākuʻi i ka manaʻo hoʻopuka