PostgreSQL অ্যান্টিপ্যাটার্নস: এসকিউএল-এ সেট এবং সিলেক্ট পাস করা

সময়ে সময়ে, বিকাশকারীর প্রয়োজন অনুরোধে প্যারামিটারের একটি সেট বা এমনকি একটি সম্পূর্ণ নির্বাচন পাস করুন "অনুপ্রবেশ". কখনও কখনও এই সমস্যার খুব অদ্ভুত সমাধান আছে।

আসুন "বিপরীত থেকে" যান এবং দেখুন কীভাবে এটি করবেন না, কেন এবং কীভাবে আপনি এটি আরও ভাল করতে পারেন।

অনুরোধের অংশে মানগুলির সরাসরি "সন্নিবেশ"

এটি সাধারণত এই মত কিছু দেখায়:

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

অনুরোধের "পুনরায় আঠালো" এর সাথে উপরে বর্ণিত সমস্যাগুলি ছাড়াও, এটি আমাদেরকেও নিয়ে যেতে পারে জ্ঞানের বাহিরে এবং সার্ভার ক্র্যাশ। কারণটি সহজ - পিজি আর্গুমেন্টের জন্য অতিরিক্ত মেমরি সংরক্ষণ করে, এবং সেটে রেকর্ডের সংখ্যা শুধুমাত্র ব্যবসায়িক লজিক অ্যাপ্লিকেশন উইশলিস্ট দ্বারা সীমাবদ্ধ। বিশেষ করে ক্লিনিকাল ক্ষেত্রে এটি দেখতে প্রয়োজন ছিল "সংখ্যাযুক্ত" আর্গুমেন্ট $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 টাইপের সাথে কাজ করার জন্য সম্পূর্ণ ফাংশন রয়েছে। অতএব, যদি আপনার ইনপুট পরামিতিগুলি ব্রাউজারে সংজ্ঞায়িত করা হয়, আপনি সেখানে এবং গঠন করতে পারেন এসকিউএল কোয়েরির জন্য 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-এ টেবিলের "ফোলা"র দিকে নিয়ে যায় এবং সেগুলি ব্যবহার করে এমন অনেকগুলি অপারেশনকে মন্থর করে।
অবশ্যই, এর সাথে লড়াই করা যেতে পারে পর্যায়ক্রমিক পাস ভ্যাকুয়াম পূর্ণ সিস্টেম ক্যাটালগ টেবিল অনুযায়ী.

সেশন ভেরিয়েবল

ধরুন পূর্ববর্তী কেস থেকে ডেটা প্রক্রিয়াকরণ একটি একক এসকিউএল কোয়েরির জন্য বেশ জটিল, কিন্তু আপনি এটি প্রায়শই করতে চান। অর্থাৎ, আমরা পদ্ধতিগত প্রক্রিয়াকরণ ব্যবহার করতে চাই ব্লক করুন, কিন্তু অস্থায়ী টেবিলের মাধ্যমে ডেটা স্থানান্তর ব্যবহার করা খুব ব্যয়বহুল হবে।

আমরা একটি বেনামী ব্লকে পাস করতে $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