PostgreSQL মধ্যে নকল আপডেট, সন্নিবেশ?




upsert sql-merge (11)

PostgreSQL 9.1 এর সাথে এটি একটি লিখনযোগ্য CTE ( সাধারণ টেবিল অভিব্যক্তি ) ব্যবহার করে অর্জন করা যেতে পারে:

WITH new_values (id, field1, field2) as (
  values 
     (1, 'A', 'X'),
     (2, 'B', 'Y'),
     (3, 'C', 'Z')

),
upsert as
( 
    update mytable m 
        set field1 = nv.field1,
            field2 = nv.field2
    FROM new_values nv
    WHERE m.id = nv.id
    RETURNING m.*
)
INSERT INTO mytable (id, field1, field2)
SELECT id, field1, field2
FROM new_values
WHERE NOT EXISTS (SELECT 1 
                  FROM upsert up 
                  WHERE up.id = new_values.id)

এই ব্লগ এন্ট্রি দেখুন:

উল্লেখ্য যে এই সমাধানটি একটি অনন্য কী লঙ্ঘনকে বাধা দেয় না তবে এটি হারিয়ে যাওয়া আপডেটগুলির জন্য দুর্বল নয়।
Dba.stackexchange.com এ ক্রেগ রিঙ্গার দ্বারা অনুসরণ করুন দেখুন

কয়েক মাস আগে আমি স্ট্যাক ওভারফ্লোে একটি উত্তর থেকে শিখেছি কিভাবে নিম্নলিখিত বাক্যমালা ব্যবহার করে একাধিক আপডেট মাইএসকিউএল করতে হয়:

INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z)
ON DUPLICATE KEY UPDATE field=VALUES(Col1), field2=VALUES(Col2);

আমি এখন PostgreSQL উপর সুইচ করেছি এবং দৃশ্যত এই সঠিক নয়। এটি সমস্ত সঠিক টেবিলে উল্লেখ করা হচ্ছে তাই আমি মনে করি এটি কীওয়ার্ডগুলির ব্যবহার করা একটি বিষয়, তবে আমি নিশ্চিত নই যে PostgreSQL ডকুমেন্টেশনে এটি কোথায় আচ্ছাদিত।

স্পষ্ট করার জন্য, আমি কয়েকটি জিনিস সন্নিবেশ করতে চাই এবং তারা যদি ইতিমধ্যে তাদের আপডেট করার জন্য বিদ্যমান থাকে।


PostgreSQL 9.5 এবং নতুন আপনি INSERT ... ON CONFLICT UPDATE ব্যবহার করতে পারেন INSERT ... ON CONFLICT UPDATE

ডকুমেন্টেশন দেখুন।

একটি মাইএসকিউএল INSERT ... ON DUPLICATE KEY UPDATE সরাসরি একটি কনফ্লিক্ট আপডেটে সরাসরি প্রকাশ করা যেতে পারে। এসকিউএল-স্ট্যান্ডার্ড সিনট্যাক্স নয়, তারা উভয় ডাটাবেস-নির্দিষ্ট এক্সটেনশান। এর জন্য MERGE ব্যবহার করা ভাল কারণ নেই , একটি নতুন সিনট্যাক্স মজা করার জন্য তৈরি করা হয় নি। (মাইএসকিউএল এর সিনট্যাক্সেও এমন সমস্যা রয়েছে যার মানে এটি সরাসরি গৃহীত হয়নি)।

উদাহরণস্বরূপ সেটআপ:

CREATE TABLE tablename (a integer primary key, b integer, c integer);
INSERT INTO tablename (a, b, c) values (1, 2, 3);

MySQL প্রশ্ন:

INSERT INTO tablename (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

হয়ে:

INSERT INTO tablename (a, b, c) values (1, 2, 10)
ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1;

পার্থক্য:

  • স্বতন্ত্রতা যাচাইয়ের জন্য আপনাকে কলামের নাম (অথবা অনন্য সীমাবদ্ধতা নাম) নির্দিষ্ট করতে হবে। যে ON CONFLICT (columnname) DO

  • কীওয়ার্ড SET অবশ্যই ব্যবহার করা উচিত, যেমন এটি একটি স্বাভাবিক UPDATE বিবৃতি ছিল

এটা খুব কিছু চমৎকার বৈশিষ্ট্য আছে:

  • আপনি আপনার ON CONFLICT IGNORE (নির্দিষ্ট মূল্যের জন্য আপনাকে কার্যকর ON CONFLICT IGNORE করতে দেয়)

  • প্রস্তাবিত-জন্য-সন্নিবেশ মানগুলি সারি-পরিবর্তনশীল EXCLUDED হিসাবে উপলব্ধ, যা লক্ষ্য টেবিলে একই গঠন রয়েছে। আপনি টেবিল নাম ব্যবহার করে টেবিলের মূল মানগুলি পেতে পারেন। তাই এই ক্ষেত্রে EXCLUDED.c 10 হবে (কারণ আমরা এটি ঢোকানোর চেষ্টা করেছি) এবং "table".c হবে 3 কারণ এটি টেবিলের বর্তমান মান। আপনি SET এক্সপ্রেশন এবং WHERE ধারা উভয় বা উভয় ব্যবহার করতে পারেন।

আপগ্রেড উপর ব্যাকগ্রাউন্ড জন্য কিভাবে ইউপিএসআরটি দেখুন (মার্জ, ইনসার্ট ... আপডেট আপডেট উপর) PostgreSQL মধ্যে?


আপডেট সংশোধিত সারি সংখ্যা ফিরে আসবে। যদি আপনি জেডিবিসি (জাভা) ব্যবহার করেন, তবে আপনি 0 এর বিপরীতে এই মানটি পরীক্ষা করতে পারেন এবং, যদি কোনও সারি প্রভাবিত হয় না তবে পরিবর্তে INSERT। আপনি যদি অন্য কোন প্রোগ্রামিং ভাষা ব্যবহার করেন, তবে সংশোধিত সারির সংখ্যা এখনও পাওয়া যাবে, ডকুমেন্টেশন চেক করুন।

এটি মার্জিত হিসাবে নাও হতে পারে তবে আপনার কাছে অনেক সহজ এসকিউএল রয়েছে যা কলিং কোড থেকে আরও বেশি তুচ্ছ। পৃথকভাবে, যদি আপনি পিএল / পিএসকিউএল-এ দশ লাইন স্ক্রিপ্ট লিখেন, তবে সম্ভবত আপনার জন্য এক বা একাধিক ধরনের ইউনিট পরীক্ষা থাকা উচিত।


আমি উপরে কাস্টম "আপস" ফাংশন, আপনি প্রবেশ করতে চান এবং প্রতিস্থাপন করতে চান:

`

 CREATE OR REPLACE FUNCTION upsert(sql_insert text, sql_update text)

 RETURNS void AS
 $BODY$
 BEGIN
    -- first try to insert and after to update. Note : insert has pk and update not...

    EXECUTE sql_insert;
    RETURN;
    EXCEPTION WHEN unique_violation THEN
    EXECUTE sql_update; 
    IF FOUND THEN 
        RETURN; 
    END IF;
 END;
 $BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;
 ALTER FUNCTION upsert(text, text)
 OWNER TO postgres;`

এবং কার্যকর করার পরে, এমন কিছু করুন:

SELECT upsert($$INSERT INTO ...$$,$$UPDATE... $$)

কম্পাইলার ত্রুটি এড়াতে ডবল ডলার-কমা রাখা গুরুত্বপূর্ণ

  • গতি পরীক্ষা করুন ...

আমি এখানে আসার জন্য একই জিনিস খুঁজছিলাম, কিন্তু একটি জেনেরিক "আপস্ট" ফাংশনের অভাব আমাকে কিছুটা বিরক্ত করেছিল তাই আমি ভেবেছিলাম যে আপনি শুধু আপডেটটি পাস করতে পারেন এবং SQL এ সার্কিট সন্নিবেশ করান যে ফাংশন ফর্মের উপর আর্গুমেন্ট হিসাবে

যে এই মত চেহারা হবে:

CREATE FUNCTION upsert (sql_update TEXT, sql_insert TEXT)
    RETURNS VOID
    LANGUAGE plpgsql
AS $$
BEGIN
    LOOP
        -- first try to update
        EXECUTE sql_update;
        -- check if the row is found
        IF FOUND THEN
            RETURN;
        END IF;
        -- not found so insert the row
        BEGIN
            EXECUTE sql_insert;
            RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- do nothing and loop
        END;
    END LOOP;
END;
$$;

এবং সম্ভবত আপনি যা করতে চান তা করতে, "আপসেট" ব্যাচ করুন, আপনি sql_update বিভক্ত করতে এবং পৃথক আপডেটগুলি লুপ করতে Tcl ব্যবহার করতে পারেন, পূর্বরূপ হিটটি খুব ছোট দেখতে হবে http://archives.postgresql.org/pgsql-performance/2006-04/msg00557.php

সর্বোচ্চ খরচ আপনার কোড থেকে অনুসন্ধান চালানো হয়, ডাটাবেস পাশে মৃত্যুদন্ডের খরচ অনেক ছোট


আমি নাম মান জোড়া হিসাবে অ্যাকাউন্ট সেটিংস পরিচালনার জন্য একই সমস্যা আছে। নকশা মানদণ্ড বিভিন্ন ক্লায়েন্ট বিভিন্ন সেটিংস সেট থাকতে পারে।

আমার সমাধান, JWP এর মতো, আপনার অ্যাপ্লিকেশনের মধ্যে একত্রীকরণ রেকর্ড তৈরি করে, মুছে ফেলার এবং প্রতিস্থাপন করা হয়।

এটি বেশ বুলেটপ্রুফ, প্ল্যাটফর্ম স্বাধীন এবং ক্লায়েন্ট প্রতি প্রায় ২0 টিরও বেশি সেটিংস না থাকার কারণে, এটি কেবলমাত্র 3 মোটামুটি কম লোড ডিবি কল - সম্ভবত দ্রুততম পদ্ধতি।

পৃথক সারি হালনাগাদ করার বিকল্প - তারপরে ব্যতিক্রমগুলি পরীক্ষা করা - বা কিছু সংমিশ্রণ ক্ষতিকারক কোড, ধীরে ধীরে এবং প্রায়ই বিরতি কারণ (উপরে উল্লিখিত) নন স্ট্যান্ডার্ড এসকিউএল ব্যতিক্রম হ্যান্ডলিংকে ডিবি থেকে ডিবি থেকে পরিবর্তন করা হয় - বা এমনকি প্রকাশ করার জন্য ছেড়ে দেওয়া হয়।

 #This is pseudo-code - within the application:
 BEGIN TRANSACTION - get transaction lock
 SELECT all current name value pairs where id = $id into a hash record
 create a merge record from the current and update record
  (set intersection where shared keys in new win, and empty values in new are deleted).
 DELETE all name value pairs where id = $id
 COPY/INSERT merged records 
 END TRANSACTION

ছোট সেট মার্জ করার জন্য, উপরের ফাংশন ব্যবহার করে জরিমানা। যাইহোক, যদি আপনি প্রচুর পরিমাণে ডেটা মার্জ করছেন তবে আমি http://mbk.projects.postgresql.org এ দেখানোর প্রস্তাব দিই।

আমি সচেতন যা বর্তমান সর্বোত্তম অনুশীলন:

  1. টেম্প টেবিলে নতুন / আপডেট হওয়া তথ্য কপি করুন (নিশ্চিত, অথবা যদি খরচ ঠিক থাকে তবে আপনি INSERT করতে পারেন)
  2. অ্যাক্সেস লক [ঐচ্ছিক] (উপদেষ্টা টেবিল তালা, আইএমও থেকে বেশি পছন্দযোগ্য)
  3. মার্জ। (মজা অংশ)

ব্যক্তিগতভাবে, আমি সন্নিবেশ বিবৃতি সংযুক্ত একটি "নিয়ম" সেট আপ করেছি। বলুন আপনার একটি "ডিএনএস" টেবিল রয়েছে যা প্রতি গ্রাহকের প্রতি প্রতি গ্রাহককে ডিএনএস হিট রেকর্ড করেছে:

CREATE TABLE dns (
    "time" timestamp without time zone NOT NULL,
    customer_id integer NOT NULL,
    hits integer
);

আপনি আপডেট মানগুলির সাথে সারি পুনরায় সন্নিবেশ করতে সক্ষম হতে চান, অথবা যদি তারা ইতিমধ্যে বিদ্যমান না থাকে তবে সেগুলি তৈরি করুন। গ্রাহক_আইড এবং সময় উপর keyed। এটার মতো কিছু:

CREATE RULE replace_dns AS 
    ON INSERT TO dns 
    WHERE (EXISTS (SELECT 1 FROM dns WHERE ((dns."time" = new."time") 
            AND (dns.customer_id = new.customer_id)))) 
    DO INSTEAD UPDATE dns 
        SET hits = new.hits 
        WHERE ((dns."time" = new."time") AND (dns.customer_id = new.customer_id));

আপডেট: একযোগে প্রবেশগুলি ঘটছে যদি এটি ব্যর্থ হওয়ার সম্ভাব্যতা রয়েছে, এটি অনন্য_অভ্যুত্থান ব্যতিক্রম তৈরি করবে। যাইহোক, অ-সমাপ্তি লেনদেন চলতে থাকবে এবং সফল হবে এবং আপনাকে কেবল বাতিল হওয়া লেনদেনটি পুনরাবৃত্তি করতে হবে।

যাইহোক, যদি প্রচুর পরিমাণে সন্নিবেশ ঘটে থাকে তবে আপনি সন্নিবেশ বিবৃতিগুলির চারপাশে একটি টেবিল লক রাখতে চান: ভাগ করুন রোল একচেটিয়া লকিং আপনার লক্ষ্য টেবিলের সারিগুলি সন্নিবেশ, মুছে ফেলতে বা আপডেট করতে পারে এমন কোনও ক্রিয়াকলাপকে আটকাবে। যাইহোক, এমন আপডেটগুলি যা অনন্য কীটি আপডেট করে না সেগুলি নিরাপদ, তাই যদি আপনি কোনও ক্রিয়াকলাপ না করেন তবে পরিবর্তে অ্যাডভাইসারির লক ব্যবহার করুন।

এছাড়াও, COPY কমান্ড RULES ব্যবহার করে না, তাই আপনি যদি কপিআই সহ ঢোকাচ্ছেন তবে আপনাকে পরিবর্তে ট্রিগারগুলি ব্যবহার করতে হবে।



সতর্কতা: একই সময়ে একাধিক সেশন থেকে কার্যকর হলে এটি নিরাপদ নয় (নীচের caveats দেখুন)।

Postgresql এ একটি "ইউপিএসইআরটি" করার আরেকটি চতুর উপায় হল দুইটি ক্রমিক আপডেট / ইনসার্ট স্টেটমেন্ট যা প্রত্যেকে সফল হতে বা কোনও প্রভাব ফেলার জন্য ডিজাইন করা হয়।

UPDATE table SET field='C', field2='Z' WHERE id=3;
INSERT INTO table (id, field, field2)
       SELECT 3, 'C', 'Z'
       WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);

যদি "id = 3" সহ একটি সারি ইতিমধ্যে বিদ্যমান থাকে তবে আপডেটটি সফল হবে, অন্যথায় এটির কোনো প্রভাব নেই।

"Id = 3" সহ সারিটি ইতিমধ্যে বিদ্যমান না থাকলে INSERT সফল হবে।

আপনি এই দুইটিকে একক স্ট্রিংয়ে একত্র করতে পারেন এবং আপনার অ্যাপ্লিকেশন থেকে কার্যকর একক SQL বিবৃতি সহ উভয়কে চালাতে পারেন। একক লেনদেন একসঙ্গে তাদের চলমান অত্যন্ত সুপারিশ করা হয়।

বিচ্ছিন্নতা বা লক করা টেবিলে চালানোর সময় এটি খুব ভাল কাজ করে, তবে রেস শর্তগুলির সাপেক্ষে এটি একটি সদৃশ সারির সাথে একযোগে সন্নিবেশ করা হলে সদৃশ কী ত্রুটি সহকারে ব্যর্থ হতে পারে, অথবা সারিটি সারিবদ্ধভাবে মুছে ফেলা হলে কোনও সারির সাথে সরাতে পারে না । PostgreSQL 9.1 বা তার উপরে একটি SERIALIZABLE লেনদেন খুব বেশি সিরিয়ালাইজেশন ব্যর্থতার হারে এটি নির্ভরযোগ্যভাবে পরিচালনা করবে, যার অর্থ আপনাকে অনেকগুলি পুনরায় চেষ্টা করতে হবে। কেন এত জটিল আপ upsert দেখুন, যা আরো বিস্তারিতভাবে এই ক্ষেত্রে আলোচনা।

এই পদ্ধতিটি read committed বিচ্ছিন্নতার মধ্যে হারিয়ে যাওয়া আপডেটগুলির সাপেক্ষে, যদি না অ্যাপ্লিকেশনটি প্রভাবিত সারির সংখ্যা যাচাই করে এবং insert বা update সারিতে প্রভাবিত হয় তা যাচাই করে


CREATE OR REPLACE FUNCTION save_user(_id integer, _name character varying)
  RETURNS boolean AS
$BODY$
BEGIN
    UPDATE users SET name = _name WHERE id = _id;
    IF FOUND THEN
        RETURN true;
    END IF;
    BEGIN
        INSERT INTO users (id, name) VALUES (_id, _name);
    EXCEPTION WHEN OTHERS THEN
            UPDATE users SET name = _name WHERE id = _id;
        END;
    RETURN TRUE;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE STRICT




sql-merge