ここで非常によくある質問は、アップサートを実行する方法です。これは、MySQLが呼び出しINSERT ... ON DUPLICATE UPDATE
、標準がMERGE
操作の一部としてサポートするものです。
PostgreSQLが直接サポートしていない場合(9.5ページより前)、これをどのように行いますか?次のことを考慮してください。
CREATE TABLE testtable (
id integer PRIMARY KEY,
somedata text NOT NULL
);
INSERT INTO testtable (id, somedata) VALUES
(1, 'fred'),
(2, 'bob');
今、あなたは「アップサート」にタプルをしたいことを想像し(2, 'Joe')
、(3, 'Alan')
新しいテーブルの内容は次のようになりので、:
(1, 'fred'),
(2, 'Joe'), -- Changed value of existing tuple
(3, 'Alan') -- Added new tuple
それは人々が議論するときに話していることupsert
です。重要なのは、明示的なロックを使用するか、結果として生じる競合状態から防御することにより、同じテーブルで複数のトランザクションが機能している場合、どのアプローチも安全でなければなりません。
このトピックは、PostgreSQLでの重複更新について、Insertで詳しく説明されていますか?、しかしそれはMySQL構文の代替案であり、時間の経過とともにかなりの無関係な詳細が増えてきました。私は決定的な答えに取り組んでいます。
これらの手法は、「存在しない場合は挿入、それ以外の場合は何もしない」、つまり「重複キーの無視で...を挿入する」場合にも役立ちます。
PostgreSQL 9.5以降のサポートINSERT ... ON CONFLICT (key) DO UPDATE
(およびON CONFLICT (key) DO NOTHING
)、つまりアップサート。
使用法については、マニュアル、具体的にはシンタックスダイアグラムのconflict_action句と説明テキストを参照してください。
以下に示す9.4以前のソリューションとは異なり、この機能は複数の競合する行で機能し、排他的ロックや再試行ループを必要としません。
機能を追加するコミットはここにあり、その開発に関する議論はここにあります。
9.5を使用していて、下位互換性が必要ない場合は、今すぐ読むのをやめることができます。
PostgreSQLには組み込みUPSERT
(またはMERGE
)機能がなく、同時使用に直面して効率的にそれを行うことは非常に困難です。
一般に、次の2つのオプションから選択する必要があります。
挿入を同時に実行しようとする多くの接続が必要な場合は、再試行ループで個々の行のアップサートを使用するのが妥当なオプションです。
PostgreSQLのドキュメントには、データベース内のループでこれを実行できる便利な手順が含まれています。ほとんどの単純なソリューションとは異なり、更新の喪失や競合の挿入を防ぎます。ただし、これはREAD COMMITTED
モードでのみ機能し、トランザクションで実行するのがそれだけである場合にのみ安全です。トリガーまたは2次一意キーが一意の違反を引き起こす場合、この関数は正しく機能しません。
この戦略は非常に非効率的です。実用的な場合はいつでも、作業をキューに入れ、代わりに以下に説明するように一括アップサートを実行する必要があります。
この問題に対して試行された解決策の多くはロールバックを考慮していないため、更新が不完全になります。2つのトランザクションは互いに競合します。それらの1つは正常にINSERT
s; もう一方は重複キーエラーを受け取り、UPDATE
代わりに実行します。UPDATE
以下のためのブロックが待っているINSERT
ロールバックまたはコミットします。ロールバックすると、UPDATE
条件の再チェックはゼロ行に一致するため、UPDATE
コミットしても、実際には期待したアップサートが実行されていません。結果の行数を確認し、必要に応じて再試行する必要があります。
いくつかの試みられた解決策はまた、SELECTレースを考慮していません。明白で単純なものを試してみると:
-- THIS IS WRONG. DO NOT COPY IT. It's an EXAMPLE.
BEGIN;
UPDATE testtable
SET somedata = 'blah'
WHERE id = 2;
-- Remember, this is WRONG. Do NOT COPY IT.
INSERT INTO testtable (id, somedata)
SELECT 2, 'blah'
WHERE NOT EXISTS (SELECT 1 FROM testtable WHERE testtable.id = 2);
COMMIT;
次に、2つを同時に実行すると、いくつかの障害モードが発生します。1つは、更新の再チェックに関するすでに説明した問題です。もう1つは、両方UPDATE
が同時に、ゼロ行に一致して続行する場合です。次に、両方がEXISTS
テストを実行します。これは、の前に行われINSERT
ます。両方ともゼロ行を取得するため、両方ともINSERT
。1つは重複キーエラーで失敗します。
これが、再試行ループが必要な理由です。巧妙なSQLを使用すると、キーの重複エラーや更新の損失を防ぐことができると思うかもしれませんが、できません。行数を確認するか、重複するキーエラーを処理して(選択したアプローチに応じて)、再試行する必要があります。
このために独自のソリューションを展開しないでください。メッセージキューの場合と同様に、おそらく間違っています。
バルクアップサートを実行したい場合があります。この場合、古い既存のデータセットにマージする新しいデータセットがあります。これは、個々の行のアップサートよりもはるかに効率的であり、実用的な場合は常に優先する必要があります。
この場合、通常は次のプロセスに従います。
CREATE
TEMPORARY
テーブル
COPY
または、新しいデータを一時テーブルに一括挿入します
LOCK
ターゲットテーブルIN EXCLUSIVE MODE
。これにより、他のトランザクションは許可されSELECT
ますが、テーブルは変更されません。
UPDATE ... FROM
一時テーブルの値を使用して、既存のレコードを実行します。
やるINSERT
すでにターゲットテーブルに存在しない行のを。
COMMIT
、ロックを解除します。
たとえば、質問で与えられた例では、複数値INSERT
を使用して一時テーブルにデータを入力します。
BEGIN;
CREATE TEMPORARY TABLE newvals(id integer, somedata text);
INSERT INTO newvals(id, somedata) VALUES (2, 'Joe'), (3, 'Alan');
LOCK TABLE testtable IN EXCLUSIVE MODE;
UPDATE testtable
SET somedata = newvals.somedata
FROM newvals
WHERE newvals.id = testtable.id;
INSERT INTO testtable
SELECT newvals.id, newvals.somedata
FROM newvals
LEFT OUTER JOIN testtable ON (testtable.id = newvals.id)
WHERE testtable.id IS NULL;
COMMIT;
MERGE
PostgreSQLwikiのSQLMERGE
ですか?SQL標準では、MERGE
実際には同時実行セマンティクスが十分に定義されておらず、最初にテーブルをロックせずにアップサーティングするのには適していません。
これは、データのマージに非常に役立つOLAPステートメントですが、実際には、並行性に安全なアップサートには有用なソリューションではありません。MERGE
アップサートに使用する他のDBMSを使用している人々へのアドバイスはたくさんありますが、実際には間違っています。
INSERT ... ON DUPLICATE KEY UPDATE
MySQLでMERGE
MS SQL Serverから(ただし、MERGE
問題については上記を参照)MERGE
Oracleから(ただし、MERGE
問題については上記を参照)この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加