Antipatterns PostgreSQL: Mandalo Sets sy mifidy amin'ny SQL

Indraindray dia mila ny developer Mandefa masontsivana maromaro na safidy iray manontolo amin'ny fangatahana "amin'ny fidirana". Indraindray dia misy vahaolana hafahafa amin'ity olana ity.

Andao handeha "avy amin'ny mifanohitra" ary hijery ny fomba tsy hanaovana izany, ny antony, ary ny fomba ahafahanao manao izany tsara kokoa.

"Ampidiro" mivantana ny soatoavina ao amin'ny vatana fangatahana

Matetika dia toa izao izy io:

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

... na toy izao:

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

Momba ity fomba ity dia voalaza, voasoratra ary na dia voasarika aza ampy:

Saika izany foana lalana mivantana mankany amin'ny tsindrona SQL ary enta-mavesatra fanampiny amin'ny lojikan'ny fandraharahana, izay voatery "mametaka" ny tady fangatahanao.

Ity fomba fiasa ity dia azo hamarinina amin'ny ampahany raha ilaina ihany. mampiasa partitioning ao amin'ny PostgreSQL version 10 sy ambany ho an'ny drafitra mahomby kokoa. Ao amin'ireo dikan-teny ireo, ny lisitr'ireo fizarana nosoniavina dia tapa-kevitra tsy misy fiheverana ireo mari-pamantarana alefa, afa-tsy amin'ny fototry ny vatana fangatahana.

$n hevitra

ny fampiasana ny mpihazona toerana Parameter dia tsara, mamela anao hampiasa izany FANAmbarana voaomana, mampihena ny enta-mavesatra eo amin'ny lojikan'ny fandraharahana (ny tady fangatahana dia miforona ary alefa indray mandeha monja) ary amin'ny mpizara database (tsy ilaina ny famerenana sy ny drafitra isaky ny trangan'ny fangatahana).

Variable isan'ny tohan-kevitra

Hiandry antsika ny olana rehefa te-hampiditra tohan-kevitra tsy fantatra mialoha isika:

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

Raha avelanao amin'ity endrika ity ny fangatahana, dia na dia hamonjy antsika amin'ny tsindrona mety ho azo atao aza izany, dia mbola hitarika amin'ny filàna lakaoly / parse ny fangatahana. ho an'ny safidy tsirairay avy amin'ny isan'ny hevitra. Efa tsara kokoa noho ny manao izany foana, fa afaka manao tsy misy izany.

Ampy ny mandalo parameter iray misy fampisehoana serialised amin'ny array:

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

Ny hany maha samy hafa dia ny ilaina ny mamadika mazava ny hevitra ho amin'ny karazana array tiana. Tsy miteraka olana anefa izany, satria efa fantatsika mialoha ny toerana iresahanay.

Famindrana santionany (matrix)

Matetika ireto dia karazana safidy rehetra amin'ny famindrana angon-drakitra hampidirina ao amin'ny angon-drakitra "amin'ny fangatahana iray":

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

Ho fanampin'ireo olana voalaza etsy ambony momba ny "re-gluing" ny fangatahana, dia mety hitarika antsika ihany koa izany lany tadidy ary fianjeran'ny mpizara. Ny antony dia tsotra - PG dia mitahiry fahatsiarovana fanampiny ho an'ny tohan-kevitra, ary ny isan'ny firaketana an-tsoratra dia voafetra ihany amin'ny fangatahana lojika fandraharahana Wishlist. Amin'ny toe-javatra klinika indrindra dia ilaina ny mijery hevitra "nomerao" mihoatra ny $9000 - aza manao toy izao.

Andeha hosoratana indray ny fangatahana, efa mihatra serialization "ambaratonga roa".:

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;

Eny, raha misy soatoavina "sarotra" ao anatin'ny array, dia mila fehezina miaraka amin'ny teny nindramina izy ireo.
Mazava fa amin'izany fomba izany dia azonao atao ny "manitarana" ny fifantenana miaraka amin'ny saha maromaro.

tsy misy dikany, tsy misy dikany,…

Indraindray dia misy safidy mandalo fa tsy "array of arrays" maromaro "arrays of columns" izay nolazaiko. ao amin'ny lahatsoratra farany:

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

Amin'ity fomba ity, raha manao fahadisoana ianao rehefa mamorona lisitry ny soatoavina ho an'ny tsanganana samihafa, dia tena mora ny mahazo tanteraka. vokatra tsy nampoizina, izay miankina amin'ny version server ihany koa:

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

Manomboka amin'ny version 9.3, PostgreSQL dia manana fiasa feno amin'ny fiasana amin'ny karazana json. Noho izany, raha voafaritra ao amin'ny navigateur ny mari-pamantarana fampidiranao dia azonao atao ny mamorona json object for SQL query:

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

Ho an'ny dikan-teny teo aloha dia azo ampiasaina ny fomba mitovy tsirairay (hstore), fa mety hiteraka olana ny "foritra" amin'ny fandosirana zavatra sarotra ao amin'ny hstore.

json_populate_recordset

Raha fantatrao mialoha fa ny angon-drakitra avy amin'ny "input" json array dia hameno latabatra sasany, dia afaka mitahiry betsaka amin'ny saha "dereferencing" ianao ary manipy ireo karazana tiana amin'ny fampiasana ny json_populate_recordset function:

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

json_to_recordset

Ary ity fiasa ity dia "hanitarana" fotsiny ny laharan'ireo zavatra nandalo ho safidy, tsy miankina amin'ny endrika latabatra:

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

TABLET TEMPORARY

Fa raha ny habetsaky ny angon-drakitra ao amin'ny santionany nampitaina dia tena lehibe, dia sarotra ny manipy izany ho serialized parameter, ary indraindray tsy azo atao, satria mitaky indray mandeha. famatsiana fahatsiarovana lehibe. Ohatra, mila manangona angon-drakitra hetsika marobe avy amin'ny rafitra ivelany mandritra ny fotoana maharitra ianao, ary avy eo dia te-hanao izany indray mandeha eo amin'ny lafin'ny angon-drakitra.

Amin'ity tranga ity, ny vahaolana tsara indrindra dia ny fampiasana latabatra vonjimaika:

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

Ny fomba dia tsara ho an'ny fampitana matetika ny boky be data.
Avy amin'ny fomba fijerin'ny famaritana ny firafitry ny angonany, ny latabatra vonjimaika dia tsy mitovy amin'ny latabatra "mahazatra" amin'ny endri-javatra tokana. ao amin'ny tabilao rafitra pg_class, ary ao pg_type, pg_depend, pg_attribute, pg_attrdef, ... - ary tsy misy na inona na inona.

Noho izany, amin'ny rafitra tranonkala misy fifandraisana fohy be dia be ho an'ny tsirairay amin'izy ireo, ny latabatra toy izany dia hamokatra firaketana rafitra vaovao isaky ny mandeha, izay voafafa rehefa mihidy ny fifandraisana amin'ny tahiry. Farany, Ny fampiasana tsy voafehy ny TEMP TABLE dia miteraka "fivontosana" ny latabatra ao amin'ny pg_catalog ary mampiadana ny asa maro mampiasa azy ireo.
Mazava ho azy fa azo iadiana amin'ny pass periodic VACUUM FENO araka ny tabilao katalaogin'ny rafitra.

Session Variables

Eritrereto hoe sarotra be ny fanodinana ny angon-drakitra tamin'ny tranga teo aloha ho an'ny fangatahana SQL tokana, saingy te-hanao izany matetika ianao. Izany hoe, tiantsika ny hampiasa fomba fanodinana amin'ny DO block, fa ny fampiasana ny famindrana angona amin'ny alalan'ny latabatra vonjimaika dia ho lafo loatra.

Tsy afaka mampiasa $n-parameter koa izahay mba handefasana sakana tsy fantatra anarana. Hanampy antsika hiala amin'ilay toe-javatra ny variables session sy ny fiasa. current_setting.

Talohan'ny version 9.2 dia tsy maintsy nanamboatra mialoha ianao namespace manokana custom_variable_classes ho an'ny faribolan'ny session "izy ireo". Amin'ny dikan-teny ankehitriny dia afaka manoratra zavatra toy izao ianao:

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

Misy vahaolana hafa azo alaina amin'ny fiteny fomba fiasa hafa tohana.

Mahafantatra fomba bebe kokoa? Zarao amin'ny fanehoan-kevitra!

Source: www.habr.com