PostgreSQL యాంటీప్యాటర్న్‌లు: సెట్‌లను పాస్ చేయడం మరియు SQLకి ఎంపిక చేయడం

ఎప్పటికప్పుడు డెవలపర్ అవసరం అభ్యర్థనకు పారామితుల సమితిని లేదా మొత్తం ఎంపికను కూడా పాస్ చేయండి "ప్రవేశద్వారం వద్ద". కొన్నిసార్లు మీరు ఈ సమస్యకు చాలా విచిత్రమైన పరిష్కారాలను చూస్తారు.

వెనుకకు వెళ్లి, ఏమి చేయకూడదు, ఎందుకు, మరియు మనం దీన్ని ఎలా బాగా చేయగలమో చూద్దాం.

అభ్యర్థన శరీరంలోకి నేరుగా విలువలను చొప్పించడం

ఇది సాధారణంగా ఇలా కనిపిస్తుంది:

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

...లేదా ఇలా:

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

ఈ పద్ధతి చెప్పబడింది, వ్రాయబడింది మరియు డ్రా కూడా పుష్కలంగా:

దాదాపు ఎల్లప్పుడూ ఇదే 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;

అవును, శ్రేణిలోని “సంక్లిష్ట” విలువల విషయంలో, వాటి చుట్టూ కోట్‌లు ఉండాలి.
ఈ విధంగా మీరు ఏకపక్ష ఫీల్డ్‌లతో ఎంపికను "విస్తరించవచ్చు" అని స్పష్టంగా తెలుస్తుంది.

unnest, unnest,…

కాలానుగుణంగా నేను పేర్కొన్న “శ్రేణుల శ్రేణి” అనేక “నిలువు వరుసల” బదులుగా ఉత్తీర్ణత కోసం ఎంపికలు ఉన్నాయి చివరి వ్యాసంలో:

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 రకంతో పని చేయడానికి పూర్తి స్థాయి ఫంక్షన్‌లను కలిగి ఉంది. అందువల్ల, ఇన్‌పుట్ పారామితుల నిర్వచనం మీ బ్రౌజర్‌లో ఏర్పడితే, మీరు దాన్ని అక్కడే రూపొందించవచ్చు SQL ప్రశ్న కోసం json ఆబ్జెక్ట్:

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 ఫంక్షన్‌ని ఉపయోగించడం ద్వారా “dereferencing” ఫీల్డ్‌లలో చాలా సేవ్ చేయవచ్చు మరియు వాటిని అవసరమైన రకాలకు ప్రసారం చేయవచ్చు:

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 ప్రశ్న కోసం మునుపటి కేసు నుండి డేటాను ప్రాసెస్ చేయడం చాలా క్లిష్టంగా ఉంటుందని అనుకుందాం, కానీ మీరు దీన్ని చాలా తరచుగా చేయాలనుకుంటున్నారు. అంటే, మేము విధానపరమైన ప్రాసెసింగ్‌ని ఉపయోగించాలనుకుంటున్నాము బ్లాక్ చేయండి, కానీ తాత్కాలిక పట్టికల ద్వారా డేటా బదిలీని ఉపయోగించడం చాలా ఖరీదైనది.

మేము అనామక బ్లాక్‌కి వెళ్లడానికి $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