PostgreSQL એન્ટિપેટર્ન: સેટ પાસ કરીને SQL પર પસંદ કરે છે

સમય સમય પર વિકાસકર્તાની જરૂર છે વિનંતિ માટે પરિમાણોનો સમૂહ અથવા સંપૂર્ણ પસંદગી પણ પસાર કરો "પ્રવેશ દ્વાર પર". કેટલીકવાર તમે આ સમસ્યા માટે ખૂબ જ વિચિત્ર ઉકેલો આવો છો.

ચાલો પાછળ જઈએ અને જોઈએ કે શું ન કરવું જોઈએ, શા માટે અને કેવી રીતે આપણે તેને વધુ સારી રીતે કરી શકીએ.

વિનંતીના મુખ્ય ભાગમાં મૂલ્યોનું સીધું નિવેશ

તે સામાન્ય રીતે કંઈક આના જેવું લાગે છે:

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

...અથવા આના જેવું:

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

આ પદ્ધતિ કહેવામાં આવી છે, લેખિત અને દોરેલા પણ પુષ્કળ:

લગભગ હંમેશા આ છે એસક્યુએલ ઇન્જેક્શનનો સીધો માર્ગ અને બિઝનેસ લોજિક પર બિનજરૂરી ભાર, જે તમારી ક્વેરી લાઇનને "ગુંદર" કરવા માટે ફરજ પાડવામાં આવે છે.

જો જરૂરી હોય તો જ આ અભિગમને આંશિક રીતે ન્યાયી ઠેરવી શકાય પાર્ટીશનનો ઉપયોગ કરીને વધુ કાર્યક્ષમ પ્લાન મેળવવા માટે 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 પ્રકાર સાથે કામ કરવા માટે સંપૂર્ણ સુવિધાયુક્ત કાર્યો છે. તેથી, જો ઇનપુટ પરિમાણોની વ્યાખ્યા તમારા બ્રાઉઝરમાં થાય છે, તો તમે તેને ત્યાં જ બનાવી શકો છો SQL ક્વેરી માટે json ઑબ્જેક્ટ:

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

અગાઉના સંસ્કરણો માટે, સમાન પદ્ધતિનો ઉપયોગ કરી શકાય છે દરેક(hstore), પરંતુ 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 ક્વેરી માટે અગાઉના કેસમાંથી ડેટા પર પ્રક્રિયા કરવી ખૂબ જટિલ છે, પરંતુ તમે તેને ઘણી વાર કરવા માંગો છો. એટલે કે, અમે પ્રક્રિયાગત પ્રક્રિયાનો ઉપયોગ કરવા માંગીએ છીએ 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