Як використовувати UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) у PostgreSQL?

Найчастіше запитане питання полягає в тому, як виконати оновлення, тобто MySQL називає INSERT ... ON DUPLICATE UPDATE та стандартну підтримку як частину операції MERGE .

Оскільки PostgreSQL безпосередньо не підтримує (до pg 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? , але мова йде про альтернативи синтаксису MySQL, і це виросли якраз небагато деталей з плином часу. Я працюю над остаточними відповідями.

Ці методи також корисні для "вставки, якщо не існує, інакше нічого не роблять", тобто "Вставити ... на дублікат ключ ігнорувати".

203
Я виявив це більш корисним - stackoverflow.com/a/36799500/80428
додано Автор Jay, джерело
@MichaelHampton тут була мета - створити остаточну версію, яку не плутають декілька застарілих відповідей - і заблоковані, тому ніхто з неї нічого не може зробити. Я не згоден з близьким голосом.
додано Автор Craig Ringer, джерело
@MichaelHampton Якщо ви стурбовані, можливо, ви можете позначити той, з яким ви зв'язали, і попросити його розблокувати, щоб його можна було очистити, тоді ми зможемо об'єднати це. Мені просто незрозуміло, як-дюп для upsert бути таким заплутаним і неправильним безлад.
додано Автор Craig Ringer, джерело
додано Автор Michael Hampton, джерело
Чому тоді це незабаром стане застарілим - і заблоковане, щоб ніхто нічого не міг зробити про це.
додано Автор Michael Hampton, джерело
Цей запитання та відповіді не заблоковано!
додано Автор Michael Hampton, джерело

6 Відповіді

9.5 та новіша:

PostgreSQL 9.5 та новіша підтримка INSERT ... ON CONFLICT UPDATE (та ON CONFLICT DO NOTHING ), тобто upsert.

Comparison with ON DUPLICATE KEY UPDATE.

Quick explanation.

Для використання див. керівництво , зокрема conflict_action в синтаксичній діаграмі та пояснювальний текст .

На відміну від рішень для версій 9.4 та вище, які наведені нижче, ця функція працює з кількома конфліктуючими рядками, і вона не потребує ексклюзивного блокування або циклі повторів.

The commit adding the feature is here and the discussion around its development is here.


If you're on 9.5 and don't need to be backward-compatible you can stop reading now.


9.4 років і старше:

PostgreSQL не має вбудованого об'єкта UPSERT (або MERGE ), і це дуже важко виконувати в умовах одночасного використання.

This article discusses the problem in useful detail.

Загалом ви повинні вибрати один з двох варіантів:

  • Індивідуальні операції вставки/оновлення у циклі повторів; або
  • Блокування таблиці та злиття пакетів

Індивідуальна петля повторити рядок

Використання індивідуальних викликів рядка у циклі повторів - це розумний варіант, якщо ви хочете одночасно підключати багато підключень до вставок.

The PostgreSQL documentation contains a useful procedure that'll let you do this in a loop inside the database. It guards against lost updates and insert races, unlike most naive solutions. It will only work in READ COMMITTED mode and is only safe if it's the only thing you do in the transaction, though. The function won't work correctly if triggers or secondary unique keys cause unique violations.

Ця стратегія дуже неефективна. Кожного разу, коли це практично, ви повинні чергувати роботу і зробити основну витримку, як описано нижче.

Багато спроб вирішення цієї проблеми не враховують відмов, тому вони призводять до неповних оновлень. Дві транзакції змагаються один з одним; один з них успішно INSERT s; інший отримує дублікат помилки ключа і замість цього робить UPDATE . Блоки UPDATE очікують на INSERT , щоб відмовитися або здійснити. Коли він повертається назад, повторна перевірка стану UPDATE відповідає нульовим рядкам, так що, навіть якщо команда UPDATE зобов'язується, вона фактично не зробила оновлення, яке ви очікували. Ви повинні перевірити кількість рядків результатів і спробувати, де це необхідно.

Деякі спроби рішення також не враховують ВИБРАТИ гонок. Якщо спробувати очевидне і просте:

-- 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;

то, коли два бігають відразу, існує кілька режимів відмови. Одне з них - це вже обговорюване питання з повторною перевіркою оновлення. Інший варіант - це те, що одночасно UPDATE , що відповідає нульовим рядкам і продовжує. Потім вони обидва проводять тест EXISTS , який перед має значення INSERT . Обидва отримують нульові рядки, тому обидва роблять INSERT . Не вдається виконати дублікат помилки клавіш.

Ось чому вам потрібно повторно спробувати цикл. Ви можете подумати, що ви можете запобігти повторюванню ключових помилок або втратити оновлення за допомогою розумного SQL, але ви не можете. Вам слід перевірити кількість рядків або обробляти повторювані ключові помилки (залежно від вибраного підходу) і повторно спробувати.

Будь ласка, не вирушайте для цього власне рішення. Як і в черг повідомлень, це, мабуть, неправильно.

Об'ємний виклик із замком

Іноді ви хочете зробити масове виправлення, де ви маєте новий набір даних, який ви хочете об'єднати в більш старий існуючий набір даних. Це значно значно ефективніше, ніж індивідуальні виправлення рядків, і воно має бути кращим, коли це практично.

У цьому випадку ви зазвичай виконуєте наступний процес:

  • CREATE таблиця TEMPORARY

  • COPY або масово вставити нові дані в таблицю temp

  • LOCK цільової таблиці IN EXCLUSIVE MODE . Це дозволяє інші транзакції виконувати SELECT , але не вносити жодних змін до таблиці.

  • Зробіть UPDATE ... FROM наявних записів, використовуючи значення у таблиці temp;

  • Зробіть INSERT рядків, які ще не існують у цільовій таблиці;

  • COMMIT , випустивши замок

Наприклад, для прикладу, наведеного в питанні, використовуйте багатозначний INSERT , щоб заповнити таблицю temp:

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 насправді має погано визначену семантику паралелізму і не підходить для виправлення без першого блокування таблиці.

It's a really useful OLAP statement for data merging, but it's not actually a useful solution for concurrency-safe upsert. There's lots of advice to people using other DBMSes to use MERGE for upserts, but it's actually wrong.

Other DBs:

332
додано
Postgres тепер підтримує UPSERT - git.postgresql.org/ gitweb/& hellip;
додано Автор Chris, джерело
Дякуємо за роз'яснення щодо паралельних вставок .. видалив мою відповідь.
додано Автор Matt Bannert, джерело
@DenisGorbachev Так, але, мабуть, я б, напевно, використовував таблицю, що не завантажується, щоб не втратити буферизовані дані, якщо ваш сеанс відключається. Будь-ласка, опублікуйте нове запитання, а не коментарі тут.
додано Автор Craig Ringer, джерело
@ Chris Or це буде в 9,5, і це не написано UPSERT . Але так. Величезні прихильники до Петра, щоб отримати це.
додано Автор Craig Ringer, джерело
@ jasonmp85 Документи . SHARE ROW EXCLUSIVE допускає ROW SHARE , який не має EXCLUSIVE . Оскільки ROW SHARE набуває лише SELECT ... FOR UPDATE і SELECT ... FOR SHARE , це не призведе до помилок даних, але це може дозволити тупикові виклики за рахунок різниці між замовленнями рядка-блокування. Що трапилось з EXCLUSIVE ? Він як і раніше дозволяє SELECT , і SHARE ROW EXCLUSIVE не набрав вам багато чого.
додано Автор Craig Ringer, джерело
@danb Оскільки не існує виправлення, зазначеного в стандарті SQL (і ні, MERGE не враховується, це операція OLAP, яка не враховує паралельності), дійсно не існує простого портативного способу зробити це. JDBC також нічого не визначає на рівні Java API - прочитайте специфікацію та api doc, ви не побачите нічого подібного до того, що шукаєте. Поки комітет SQL не зможе визначити специфікацію upsert (без сумніву, жахливий синтаксис та дивні поведінкові примхи) вам доведеться використовувати специфічні для постачальників підходи.
додано Автор Craig Ringer, джерело
@danb Це стосується лише PostgreSQL. Немає рішення для перехресного постачальника. Рішення для PostgreSQL не працює для декількох рядків, але, на жаль, потрібно зробити одну транзакцію на рядок. "Рішення", які використовують MERGE для SQL Server та Oracle, є неправильними та схильні до умов перегонів, як зазначено вище. Ви повинні будете спеціально досліджувати кожну СУБД, щоб з'ясувати, як з ними працювати, я дійсно можу порадитися лише з PostgreSQL. Єдиний спосіб зробити безпечний багаторазовий випадок на PostgreSQL буде, якщо підтримка власного виправлення буде додано до основного сервера.
додано Автор Craig Ringer, джерело
Це неможливо виконати як збережену процедуру, однак, ви потенційно можете реалізувати метод, який, наприклад, реалізує доповнення, вставляючи значення та виконуючи оновлення, якщо ви спіймали виняток з копією ключового слова. Більш дбайливо потрібно встановити рівні ізоляції транзакцій та повторити спробу у випадку безвиході.
додано Автор dan b, джерело
Навіть для PostGresQL рішення не працює, якщо таблиця має кілька унікальних ключів (оновлення лише одного рядка). У такому випадку потрібно вказати, який ключ буде оновлено. Наприклад, може існувати рішення крос-постачальника, яке використовує jdbc.
додано Автор dan b, джерело
Є ще невирішені питання, а для інших продавців незрозуміло, що працює, а що ні. 1. Поліграфічне рішення Postgres, як зазначено, не працює у випадку декількох унікальних ключів. 2. Дублікат ключа для MySQL також не працює для кількох унікальних ключів. 3. Чи працюють інші рішення для MySQL, SQL Server та Oracle? Чи можливі винятки в цих випадках і ми повинні співати?
додано Автор dan b, джерело
У масовому випуску, чи можливе значення при видаленні з newvals, а не фільтрування INSERT? Наприклад, З оновленням AS (UPDATE ... RETURNING newvals.id) DELETE FROM newvals ВИКОРИСТАННЯ Оновлення WHERE newvals.id = upd.id, а потім голий INSERT INTO testtest SELECT * FROM newvals? Моя ідея з цим: замість фільтрування двічі в INSERT (для JOIN/WHERE та для унікального обмеження) повторно використовуйте результати перевірки існування з UPDATE, які вже в оперативній пам'яті, і можуть бути набагато меншими. Це може бути перемога, якщо кількість рядків відповідає і/або newvals значно менше, ніж testtable.
додано Автор Gunnlaugur Briem, джерело
Я знайшов деяку додаткову допомогу щодо того, як виконати оновлення за допомогою FROM, з відповіддю, розміщеною на stackoverflow.com/questions/3845718/ …
додано Автор zerocog, джерело
Чи прийнятно для заповнення TEMPORARY TABLE newvals за межами транзакції? У мене є система, яка вимагає витримки великої кількості даних протягом тривалого періоду часу. Таким чином, я можу або очистити дані в пам'яті, а потім розпочати транзакцію, посилити, приєднати - або вставити дані в тимчасову таблицю, коли вона потрапить на мою сторону. Отже, чи правильно створити таблицю temp за межами транзакції?
додано Автор Denis Gorbachev, джерело
@ CraigRinger, @ jasonmp85: однією з причин, що SHARE ROW EXCLUSIVE може бути кращим, є те, що це дозволяє виконувати операції VACUUM . Рано чи пізно вам знадобиться VACUUM , і ви насправді не хочете, щоб всі ваші оновлення були заблоковані позаду.
додано Автор richvdh, джерело
Швидке запитання ... це повідомлення в блозі пропонує SHARE ROW EXCLUSIVE замість цього, і виглядає так, ніби що слабший замок буде працювати так само добре. Чи є певна причина, з якої ви використовуєте EXCLUSIVE тут?
додано Автор jasonmp85, джерело
Нічого не траплялося з EXCLUSIVE , мені було цікаво лише про невідповідність і не змогли розібратися, яким є ефект використання слабшого блокування. Ви добре це пояснили. Дякую!
додано Автор jasonmp85, джерело

I am trying to contribute with another solution for the single insertion problem with the pre-9.5 versions of PostgreSQL. The idea is simply to try to perform first the insertion, and in case the record is already present, to update it:

do $$
begin 
  insert into testtable(id, somedata) values(2,'Joe');
exception when unique_violation then
  update testtable set somedata = 'Joe' where id = 2;
end $$;

Зверніть увагу, що це рішення можна застосувати лише у тому випадку, якщо немає стержень таблиці .

Я не знаю про ефективність цього рішення, але мені здається, це досить розумно.

25
додано
Так. Це спрощення працює, якщо і тільки якщо відсутні видалення.
додано Автор Craig Ringer, джерело
Спасибі, це саме те, що я шукав. Не можу зрозуміти, чому так важко було знайти.
додано Автор isapir, джерело
Дякую, що вказали на це. Дуже корисно для нас!
додано Автор 1111161171159459134, джерело

Ось кілька прикладів для insert ... on conflict ... ( pg 9.5 + ):

  • Вставити в конфлікт - нічого не робити .
    вставити в макет (id, name, size) значення (1, 'new_name', 3) на конфлікті нічого не роблять;

  • Вставте, у конфлікті - виконайте оновлення , вкажіть ціль конфлікту через стовпець .
    вставити в макет (id, name, size) значення (1, 'new_name', 3) на конфлікті (id) виконайте оновлення set name = 'new_name', size = 3;

  • Вставити, у конфлікті - виконати оновлення , вказати ціль конфлікту за допомогою назви обмеження .
    вставити в макет (id, name, size) значення (1, 'new_name', 3) на конфлікті з обмеженням dummy_pkey виконайте оновлення set name = 'new_name', size = 4;

4
додано
WITH UPD AS (UPDATE TEST_TABLE SET SOME_DATA = 'Joe' WHERE ID = 2 
RETURNING ID),
INS AS (SELECT '2', 'Joe' WHERE NOT EXISTS (SELECT * FROM UPD))
INSERT INTO TEST_TABLE(ID, SOME_DATA) SELECT * FROM INS

Протестовано на Postgresql 9.3

3
додано
Все ще помиляється при одночасному завантаженні.
додано Автор Craig Ringer, джерело

SQLAlchemy upsert for Postgres >=9.5

Оскільки великий пост вище охоплює безліч різних підходів до SQL для версій Postgres (не тільки не 9,5 як у питанні), я хотів би додати, як це зробити у SQLAlchemy, якщо ви використовуєте Postgres 9.5. Замість того, щоб реалізувати свій власний поштовх, ви також можете використовувати функції SQLAlchemy (які були додані в SQLAlchemy 1.1). Особисто я б рекомендував використовувати їх, якщо це можливо. Не тільки через зручність, але і тому, що PostgreSQL дозволяє обробляти будь-які умови перегонів, які можуть виникнути.

Перехрестування з іншої відповіді, яку я дав вчора ( https://stackoverflow.com/a/44395983/2156909 )

SQLAlchemy підтримує ON CONFLICT за допомогою двох методів on_conflict_do_update() і on_conflict_do_nothing() :

Копіювання з документації:

from sqlalchemy.dialects.postgresql import insert

stmt = insert(my_table).values(user_email='[email protected]', data='inserted data')
stmt = stmt.on_conflict_do_update(
    index_elements=[my_table.c.user_email],
    index_where=my_table.c.user_email.like('%@gmail.com'),
    set_=dict(data=stmt.excluded.data)
    )
conn.execute(stmt)

http://docs. sqlalchemy.org/en/latest/dialects/postgresql.html?highlight=conflict#insert-on-conflict-upsert

1
додано
Python і SQLAlchemy не згадуються в питанні.
додано Автор Alexander Emelianov, джерело

Оскільки це запитання було закрито, я розміщую тут, як ви це робите, використовуючи SQLAlchemy. Через рекурсію він повторює пакетну вставку чи оновлення, щоб боротися з умовами перегонів та помилками перевірки.

По-перше, імпорт

import itertools as it

from functools import partial
from operator import itemgetter

from sqlalchemy.exc import IntegrityError
from app import session
from models import Posts

Тепер пара допоміжних функцій

def chunk(content, chunksize=None):
    """Groups data into chunks each with (at most) `chunksize` items.
    https://stackoverflow.com/a/22919323/408556
    """
    if chunksize:
        i = iter(content)
        generator = (list(it.islice(i, chunksize)) for _ in it.count())
    else:
        generator = iter([content])

    return it.takewhile(bool, generator)


def gen_resources(records):
    """Yields a dictionary if the record's id already exists, a row object 
    otherwise.
    """
    ids = {item[0] for item in session.query(Posts.id)}

    for record in records:
        is_row = hasattr(record, 'to_dict')

        if is_row and record.id in ids:
            # It's a row but the id already exists, so we need to convert it 
            # to a dict that updates the existing record. Since it is duplicate,
            # also yield True
            yield record.to_dict(), True
        elif is_row:
            # It's a row and the id doesn't exist, so no conversion needed. 
            # Since it's not a duplicate, also yield False
            yield record, False
        elif record['id'] in ids:
            # It's a dict and the id already exists, so no conversion needed. 
            # Since it is duplicate, also yield True
            yield record, True
        else:
            # It's a dict and the id doesn't exist, so we need to convert it. 
            # Since it's not a duplicate, also yield False
            yield Posts(**record), False

І, нарешті, функція підвищення

def upsert(data, chunksize=None):
    for records in chunk(data, chunksize):
        resources = gen_resources(records)
        sorted_resources = sorted(resources, key=itemgetter(1))

        for dupe, group in it.groupby(sorted_resources, itemgetter(1)):
            items = [g[0] for g in group]

            if dupe:
                _upsert = partial(session.bulk_update_mappings, Posts)
            else:
                _upsert = session.add_all

            try:
                _upsert(items)
                session.commit()
            except IntegrityError:
                # A record was added or deleted after we checked, so retry
                # 
                # modify accordingly by adding additional exceptions, e.g.,
                # except (IntegrityError, ValidationError, ValueError)
                db.session.rollback()
                upsert(items)
            except Exception as e:
                # Some other error occurred so reduce chunksize to isolate the 
                # offending row(s)
                db.session.rollback()
                num_items = len(items)

                if num_items > 1:
                    upsert(items, num_items//2)
                else:
                    print('Error adding record {}'.format(items[0]))

Ось як ви це використовуєте

>>> data = [
...     {'id': 1, 'text': 'updated post1'}, 
...     {'id': 5, 'text': 'updated post5'}, 
...     {'id': 1000, 'text': 'new post1000'}]
... 
>>> upsert(data)

Перевага цього полягає в bulk_save_objects полягає в тому, що він може обробляти відносини, перевірку помилок тощо на вставці (на відміну від масові операції ).

0
додано
Транзакції не є магічним рішенням усіх питань паралельності. Ви можете використовувати транзакції SERIALIZABLE та обробляти невдалі серіалізацію, але це повільно. Вам потрібна обробка помилок та повторити цикл. Подивіться на мою відповідь і розділ "відповідне читання".
додано Автор Craig Ringer, джерело
Це також виглядає неправильно для мене. Що робити, якщо паралельний сеанс вставляє рядок після того, як ви збираєте свій список ідентифікаторів? Або видаляє один?
додано Автор Craig Ringer, джерело
@ CraigRinger gotcha. Я насправді реалізував цикл повтору в моєму власному випадку через інші помилки перевірки. Я відповідь оновлю відповідь.
додано Автор reubano, джерело
Хороший пункт @CraigRinger Я роблю щось подібне до цього, але тільки 1 сесія виконує роботу. Який найкращий спосіб обробляти кілька сеансів? Можливо, транзакція?
додано Автор reubano, джерело