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),...

അഭ്യർത്ഥനയുടെ "റീ-ഗ്ലൂയിംഗ്" ഉപയോഗിച്ച് മുകളിൽ വിവരിച്ച പ്രശ്നങ്ങൾക്ക് പുറമേ, ഇത് ഞങ്ങളെ നയിക്കും ഓർമ്മയില്ല കൂടാതെ സെർവർ തകരാറും. കാരണം ലളിതമാണ് - ആർഗ്യുമെന്റുകൾക്കായി പിജി അധിക മെമ്മറി റിസർവ് ചെയ്യുന്നു, കൂടാതെ സെറ്റിലെ റെക്കോർഡുകളുടെ എണ്ണം ബിസിനസ് ലോജിക് ആപ്ലിക്കേഷൻ വിഷ്‌ലിസ്റ്റിൽ മാത്രം പരിമിതപ്പെടുത്തിയിരിക്കുന്നു. പ്രത്യേകിച്ച് ക്ലിനിക്കൽ കേസുകളിൽ അത് കാണേണ്ടത് ആവശ്യമാണ് $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 മുതൽ, പോസ്റ്റ്‌ഗ്രെഎസ്‌ക്യുഎല്ലിന് 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 ഫംഗ്‌ഷൻ ഉപയോഗിച്ച് നിങ്ങൾക്ക് "ഡിറഫറൻസിംഗ്" ഫീൽഡുകളിൽ ധാരാളം സംരക്ഷിക്കാനും ആവശ്യമുള്ള തരങ്ങളിലേക്ക് കാസ്റ്റുചെയ്യാനും കഴിയും:

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-ന് മുമ്പ്, നിങ്ങൾ മുൻകൂട്ടി കോൺഫിഗർ ചെയ്യേണ്ടതുണ്ട് പ്രത്യേക നെയിംസ്പേസ് custom_variable_classes "അവരുടെ" സെഷൻ വേരിയബിളുകൾക്കായി. നിലവിലെ പതിപ്പുകളിൽ, നിങ്ങൾക്ക് ഇതുപോലൊന്ന് എഴുതാം:

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