PostgreSQLでUPSERT(MERGE、INSERT ... ON DUPLICATE UPDATE)する方法は?

クレイグリンガー

ここで非常によくある質問は、アップサートを実行する方法です。これは、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構文の代替案であり、時間の経過とともにかなりの無関係な詳細が増えてきました。私は決定的な答えに取り組んでいます。

これらの手法は、「存在しない場合は挿入、それ以外の場合は何もしない」、つまり「重複キーの無視で...を挿入する」場合にも役立ちます。

クレイグリンガー

9.5以降:

PostgreSQL 9.5以降のサポートINSERT ... ON CONFLICT (key) DO UPDATE(およびON CONFLICT (key) DO NOTHING)、つまりアップサート。

との比較ON DUPLICATE KEY UPDATE

簡単な説明

使用法については、マニュアル、具体的にはシンタックスダイアグラムのconflict_action句と説明テキストを参照してください

以下に示す9.4以前のソリューションとは異なり、この機能は複数の競合する行で機能し、排他的ロックや再試行ループを必要としません。

機能を追加するコミットはここにあり、その開発に関する議論はここにあります


9.5を使用していて、下位互換性が必要ない場合は、今すぐ読むのをやめることができます


9.4以前:

PostgreSQLには組み込みUPSERT(またはMERGE)機能がなく、同時使用に直面して効率的にそれを行うことは非常に困難です。

この記事では、問題について詳細に説明します。

一般に、次の2つのオプションから選択する必要があります。

  • 再試行ループでの個々の挿入/更新操作。または
  • テーブルをロックしてバッチマージを実行する

個々の行の再試行ループ

挿入を同時に実行しようとする多くの接続が必要な場合は、再試行ループで個々の行のアップサートを使用するのが妥当なオプションです。

PostgreSQLのドキュメントには、データベース内のループでこれを実行できる便利な手順が含まれていますほとんどの単純なソリューションとは異なり、更新の喪失や競合の挿入を防ぎます。ただし、これはREAD COMMITTEDモードでのみ機能し、トランザクションで実行するのがそれだけである場合にのみ安全です。トリガーまたは2次一意キーが一意の違反を引き起こす場合、この関数は正しく機能しません。

この戦略は非常に非効率的です。実用的な場合はいつでも、作業をキューに入れ、代わりに以下に説明するように一括アップサートを実行する必要があります。

この問題に対して試行された解決策の多くはロールバックを考慮していないため、更新が不完全になります。2つのトランザクションは互いに競合します。それらの1つは正常にINSERTs; もう一方は重複キーエラーを受け取り、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ます。両方ともゼロ行を取得するため、両方ともINSERT1つは重複キーエラーで失敗します。

これが、再試行ループが必要な理由です。巧妙なSQLを使用すると、キーの重複エラーや更新の損失を防ぐことができると思うかもしれませんが、できません。行数を確認するか、重複するキーエラーを処理して(選択したアプローチに応じて)、再試行する必要があります。

このために独自のソリューションを展開しないでください。メッセージキューの場合と同様に、おそらく間違っています。

ロック付きバルクアップサート

バルクアップサートを実行したい場合があります。この場合、古い既存のデータセットにマージする新しいデータセットがあります。これは、個々の行のアップサートよりもはるかに効率的であり、実用的な場合は常に優先する必要があります。

この場合、通常は次のプロセスに従います。

  • CREATETEMPORARYテーブル

  • 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ですか?

SQL標準では、MERGE実際には同時実行セマンティクスが十分に定義されておらず、最初にテーブルをロックせずにアップサーティングするのには適していません。

これは、データのマージに非常に役立つOLAPステートメントですが、実際には、並行性に安全なアップサートには有用なソリューションではありません。MERGEアップサートに使用する他のDBMSを使用ている人々へのアドバイスはたくさんありますが、実際には間違っています。

その他のDB:

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]

編集
0

コメントを追加

0

関連記事

分類Dev

INSERT INTO ... ON DUPLICATE KEY UPDATEは、PHPとmySQLで重複する値を更新しません

分類Dev

UPDATE, INSERT and ON DUPLICATE KEY UPDATE in one query

分類Dev

Mysql INSERT INTO ... SELECT .... ON DUPLICATE KEY UPDATE

分類Dev

PostgreSQL BIGSERIAL and "duplicate key" on insert

分類Dev

INSERT ON DUPLICATE KEYUPDATEで「join」を使用することは可能ですか?

分類Dev

INSERT ... ON DUPLICATE KEY UPDATEがMySQLのキー「PRIMARY」に重複エントリ「x」を与えるのはなぜですか?

分類Dev

MySQL-INSERT、INSERT IGNORE、ON DUPLICATE UPDATEを1つの有効なクエリに組み合わせる方法は?

分類Dev

Postgresql:競合を定義するUPSERT / INSERT INTO

分類Dev

Postgresql:競合を定義するUPSERT / INSERT INTO

分類Dev

Go:ON DUPLICATE KEY UPDATEでSQL RowsAffected

分類Dev

PostgreSQLの「INSERT ... ONCONFLICT」(UPSERT)機能をflask_sqlalchemyで使用するにはどうすればよいですか?

分類Dev

SQL Insert multiple record while using ON DUPLICATE KEY UPDATE

分類Dev

mysql insert on duplicate key update, check which one occurred

分類Dev

Mysql insert on duplicate key update and delete if key not exists

分類Dev

PostgreSQL INSERT ON CONFLICT UPDATE(upsert)はすべての除外値を使用します

分類Dev

MySQL Update if duplicate row

分類Dev

How to On Duplicate Key Update

分類Dev

ON DUPLICATE update nothing

分類Dev

MySQLは、INSERT INTO SELECT ... ON DUPLICATE KEY UPDATE ... SQLステートメントを使用すると、更新する代わりに新しい行を作成します。

分類Dev

INSERT INTO ... ON DUPLICATE KEYUPDATEが機能していないようです

分類Dev

duplicate()の動作が異なる原因は何ですか?

分類Dev

VB.NETで「INSERT INTO table VALUES() ON DUPLICATE KEY UPDATE」を使用できません

分類Dev

INSERT INTO ... SELECT ... ON DUPLICATE KEYUPDATEでSQLを使用する場合のエラー1064

分類Dev

Merge duplicate values in django query

分類Dev

Merge list of maps with duplicate keys

分類Dev

PHP Foreach SQL、INSERT INTO ON DUPLICATE KEY UPDATEは最後のクエリのみを挿入しますか?

分類Dev

INSERT ON DUPLICATE KEY UPDATEで、挿入値を指定するときにデフォルト値が使用されているかどうかを検出します

分類Dev

mysql query to update duplicate entries

分類Dev

Why am I receiving intermittent duplicate update errors on a MERGE statement, where it shouldn't be possible?

Related 関連記事

  1. 1

    INSERT INTO ... ON DUPLICATE KEY UPDATEは、PHPとmySQLで重複する値を更新しません

  2. 2

    UPDATE, INSERT and ON DUPLICATE KEY UPDATE in one query

  3. 3

    Mysql INSERT INTO ... SELECT .... ON DUPLICATE KEY UPDATE

  4. 4

    PostgreSQL BIGSERIAL and "duplicate key" on insert

  5. 5

    INSERT ON DUPLICATE KEYUPDATEで「join」を使用することは可能ですか?

  6. 6

    INSERT ... ON DUPLICATE KEY UPDATEがMySQLのキー「PRIMARY」に重複エントリ「x」を与えるのはなぜですか?

  7. 7

    MySQL-INSERT、INSERT IGNORE、ON DUPLICATE UPDATEを1つの有効なクエリに組み合わせる方法は?

  8. 8

    Postgresql:競合を定義するUPSERT / INSERT INTO

  9. 9

    Postgresql:競合を定義するUPSERT / INSERT INTO

  10. 10

    Go:ON DUPLICATE KEY UPDATEでSQL RowsAffected

  11. 11

    PostgreSQLの「INSERT ... ONCONFLICT」(UPSERT)機能をflask_sqlalchemyで使用するにはどうすればよいですか?

  12. 12

    SQL Insert multiple record while using ON DUPLICATE KEY UPDATE

  13. 13

    mysql insert on duplicate key update, check which one occurred

  14. 14

    Mysql insert on duplicate key update and delete if key not exists

  15. 15

    PostgreSQL INSERT ON CONFLICT UPDATE(upsert)はすべての除外値を使用します

  16. 16

    MySQL Update if duplicate row

  17. 17

    How to On Duplicate Key Update

  18. 18

    ON DUPLICATE update nothing

  19. 19

    MySQLは、INSERT INTO SELECT ... ON DUPLICATE KEY UPDATE ... SQLステートメントを使用すると、更新する代わりに新しい行を作成します。

  20. 20

    INSERT INTO ... ON DUPLICATE KEYUPDATEが機能していないようです

  21. 21

    duplicate()の動作が異なる原因は何ですか?

  22. 22

    VB.NETで「INSERT INTO table VALUES() ON DUPLICATE KEY UPDATE」を使用できません

  23. 23

    INSERT INTO ... SELECT ... ON DUPLICATE KEYUPDATEでSQLを使用する場合のエラー1064

  24. 24

    Merge duplicate values in django query

  25. 25

    Merge list of maps with duplicate keys

  26. 26

    PHP Foreach SQL、INSERT INTO ON DUPLICATE KEY UPDATEは最後のクエリのみを挿入しますか?

  27. 27

    INSERT ON DUPLICATE KEY UPDATEで、挿入値を指定するときにデフォルト値が使用されているかどうかを検出します

  28. 28

    mysql query to update duplicate entries

  29. 29

    Why am I receiving intermittent duplicate update errors on a MERGE statement, where it shouldn't be possible?

ホットタグ

アーカイブ