Antipatterns PostgreSQL: tacair a rith agus a roghnaíonn go SQL

Ó am go chéile tá gá le forbróir sraith paraiméadair nó fiú rogha iomlán a chur ar aghaidh chuig an iarratas "ag an mbealach isteach". Uaireanta tagann tú trasna ar réitigh an-aisteach ar an bhfadhb seo.

A ligean ar dul ar gcúl agus a fheiceáil cad nach bhfuil le déanamh, cén fáth, agus conas is féidir linn é a dhéanamh níos fearr.

Luachanna a chur isteach go díreach isteach sa chomhlacht iarratais

De ghnáth breathnaíonn sé rud éigin mar seo:

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

...nó mar seo:

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

Tá an modh seo ráite, scríofa agus fiú tharraingt neart:

Beagnach i gcónaí é seo cosán díreach chuig instealltaí SQL agus ualach neamhriachtanach ar loighic ghnó, a bhfuil iachall air do líne fiosrúcháin a “gliú”.

Ní féidir an cur chuige seo a chosaint go páirteach ach amháin más gá ag baint úsáide as deighilt i leaganacha PostgreSQL 10 agus thíos chun plean níos éifeachtaí a fháil. Sna leaganacha seo, déantar liosta na n-alt scanadh a chinneadh gan na paraiméadair tarchurtha a chur san áireamh, ach amháin ar bhonn an chomhlachta iarratais.

$n-argóintí

Úsáid áitshealbhóirí paraiméadair atá go maith, is féidir leat é a úsáid RÁITIS ULLMHAITHE, an t-ualach a laghdú ar an loighic ghnó (ní ghintear agus tarchuirtear teaghrán na gceist ach uair amháin) agus ar fhreastalaí an bhunachair sonraí (ní gá athphasáil agus sceidealú a dhéanamh do gach cás fiosrúcháin).

Líon athraitheach argóintí

Beidh fadhbanna ag fanacht linn agus muid ag iarraidh líon anaithnid argóintí a rith:

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

Má fhágann muid an t-iarratas san fhoirm seo, cé go gcosnóidh sé sinn ó instealladh féideartha, beidh sé mar thoradh fós ar an ngá an t-iarratas a chumasc/parsáil do gach rogha ag brath ar líon na n-argóintí. Tá sé níos fearr ná é a dhéanamh gach uair, ach is féidir leat a dhéanamh gan é.

Is leor chun pas a fháil ach paraiméadar amháin ina bhfuil léiriú sraitheach sraithe:

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

Is é an t-aon difríocht atá ann ná an gá an argóint a thiontú go sainráite chuig an gcineál eagar inmhianaithe. Ach ní chuireann sé seo ina chúis le fadhbanna, ós rud é go bhfuil a fhios againn cheana féin roimh ré cá bhfuil muid ag dul.

Sampla a aistriú (maitrís)

De ghnáth is roghanna iad seo go léir chun tacair sonraí a aistriú le cur isteach sa bhunachar sonraí “in aon iarratas amháin”:

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

Chomh maith leis na fadhbanna a bhfuil cur síos orthu thuas maidir leis an iarratas a “athghlúáil”, féadann sé seo a bheith mar thoradh orainn freisin as chuimhne agus timpiste freastalaí. Is é an chúis simplí - coimeádann PG cuimhne breise le haghaidh argóintí, agus tá líon na dtaifead sa tacar teoranta ach amháin ag riachtanais iarratais an loighic ghnó. I gcásanna cliniciúla go háirithe bhí orm a fheiceáil tá níos mó ná $9000 ag argóintí “líon”. - ná déan ar an mbealach seo.

Déanaimis an t-iarratas a athscríobh le húsáid cheana féin sraithiú "dhá leibhéal".:

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;

Sea, i gcás luachanna “casta” taobh istigh d’eagair, ní mór comharthaí athfhriotail a bheith timpeall orthu.
Is léir gur féidir ar an mbealach seo rogha a “leathnú” le líon treallach réimsí.

anacair, anacair, …

Ó am go chéile tá roghanna pas ann in ionad “eagar eagair” roinnt “sraitheanna colún” a luaigh mé san alt roimhe seo:

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

Leis an modh seo, má dhéanann tú botún agus tú ag giniúint liostaí luachanna do cholúin éagsúla, tá sé an-éasca a fháil torthaí gan choinne, atá ag brath ar leagan an fhreastalaí freisin:

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

Ón leagan 9.3, tá feidhmeanna lán-chuimsitheacha ag PostgreSQL chun oibriú leis an gcineál json. Dá bhrí sin, má tharlaíonn an sainmhíniú ar pharaiméadair ionchuir i do bhrabhsálaí, is féidir leat é a fhoirmiú ceart ansin réad json le haghaidh ceist SQL:

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

I gcás leaganacha roimhe seo, is féidir an modh céanna a úsáid chun gach(hstore), ach is féidir fadhbanna a chur faoi deara "convolution" ceart le rudaí casta a éalú i hstore.

json_populate_recordset

Má tá a fhios agat roimh ré go n-úsáidfear na sonraí ón eagar json “ionchuir” chun roinnt tábla a líonadh, is féidir leat go leor a shábháil i réimsí “dereferencing” agus iad a réitigh chuig na cineálacha riachtanacha trí úsáid a bhaint as an bhfeidhm 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_go_thaifeadta

Agus ní dhéanfaidh an fheidhm seo ach “leathnú” ar an raon rudaí a ritheadh ​​i rogha, gan a bheith ag brath ar fhormáid an tábla:

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

TÁBLA SEALADACH

Ach má tá an méid sonraí sa sampla aistrithe an-mhór, ansin tá sé deacair agus uaireanta dodhéanta é a chur isteach i bparaiméadar sraitheach amháin, ós rud é go n-éilíonn sé aon-uaire. leithroinnt cuid mhór de chuimhne. Mar shampla, ní mór duit pacáiste mór sonraí a bhailiú ar imeachtaí ó chóras seachtrach ar feadh i bhfad, agus ansin ba mhaith leat é a phróiseáil aon-uaire ar thaobh an bhunachair shonraí.

Sa chás seo, bheadh ​​​​an réiteach is fearr a úsáid táblaí sealadacha:

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

Tá an modh go maith chun méideanna móra a aistriú ó am go chéile sonraí.
Ó thaobh cur síos a dhéanamh ar struchtúr a shonraí, níl difríocht idir tábla sealadach agus tábla “rialta” ar aon bhealach amháin. sa tábla córais pg_classagus i pg_type, pg_depend, pg_attribute, pg_attrdef, ... - tada ar bith.

Dá bhrí sin, i gcórais gréasáin le líon mór naisc ghearrshaolacha do gach ceann acu, ginfidh tábla den sórt sin taifid chórais nua gach uair, a scriostar nuair a dhúntar an nasc leis an mbunachar sonraí. Faoi dheireadh, fágann úsáid neamhrialaithe TÁBLA TEMP “at” táblaí sa pg_catalog agus go leor oibríochtaí a úsáideann iad a mhoilliú.
Ar ndóigh, is féidir déileáil leis seo ag baint úsáide as sliocht tréimhsiúil VACUUM FULL de réir táblaí catalóg an chórais.

Athróga Seisiúin

Glacaimid leis go bhfuil próiseáil na sonraí ón gcás roimhe seo casta go leor le haghaidh ceist SQL amháin, ach ba mhaith leat é a dhéanamh sách minic. Is é sin, ba mhaith linn próiseáil nós imeachta a úsáid i DO blocáil, ach beidh sé ró-chostasach úsáid a bhaint as aistriú sonraí trí tháblaí sealadacha.

Chomh maith leis sin ní bheidh muid in ann $n-paraiméadair a úsáid chun dul ar aghaidh chuig bloc gan ainm. Cabhróidh athróga seisiúin agus an fheidhm linn teacht amach as an gcás seo socrú_reatha.

Roimh leagan 9.2 bhí sé riachtanach a réamh-chumrú ainmspás speisialta ranganna_inathraithe_saincheaptha le haghaidh athróg seisiúin “do”. Ar leaganacha reatha is féidir leat rud éigin mar seo a scríobh:

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

Is féidir teacht ar réitigh eile i dteangacha eile nós imeachta a dtacaítear leo.

An bhfuil aon bealaí eile ar eolas agat? Comhroinn sna tuairimí!

Foinse: will.com