Как узнать, был ли upsert обновлением PostgreSQL 9.5+ UPSERT?

Записываемые CTE считались решением для UPSERT до версии 9.5, как описано в Вставить, о повторяющихся обновлениях в PostgreSQL?

Можно выполнить UPSERT с информацией, закончился ли он как UPDATE или INSERT со следующей идиомой записываемых CTE:

WITH
    update_cte AS (
        UPDATE t SET v = $1 WHERE id = $2 RETURNING 'updated'::text status
    ),
    insert_cte AS (
        INSERT INTO t(id, v) SELECT $2, $1 WHERE NOT EXISTS
            (SELECT 1 FROM update_cte) RETURNING 'inserted'::text status
    )
 (SELECT status FROM update_cte) UNION (SELECT status FROM insert_cte)

Этот запрос вернет либо «обновлено», либо «вставлено», либо может (редко) завершиться ошибкой с нарушением ограничения, как описано в https://dba.stackexchange.com/questions/78510/why-is-cte-open-to-lost-updates

Можно ли добиться чего-то подобного, используя новый синтаксис «UPSERT» PostgreSQL 9.5+, используя его оптимизацию и избегая возможного нарушения ограничений?


postgresql postgresql-9.5 upsert
person Paul Guyot    schedule 13.01.2016    source источник

Ответы (4)


arrow_upward
27
arrow_downward

Я считаю, что xmax::text::int > 0 было бы самым простым трюком:

so=# DROP TABLE IF EXISTS tab;
NOTICE:  table "tab" does not exist, skipping
DROP TABLE
so=# CREATE TABLE tab(id INT PRIMARY KEY, col text);
CREATE TABLE
so=# INSERT INTO tab(id, col) VALUES (1,'a'), (2, 'b');
INSERT 0 2
so=# INSERT INTO tab(id, col)
VALUES (3, 'c'), (4, 'd'), (1,'aaaa')
ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col
returning *,case when xmax::text::int > 0 then 'updated' else 'inserted' end,ctid;
 id | col  |   case   | ctid
----+------+----------+-------
  3 | c    | inserted | (0,3)
  4 | d    | inserted | (0,4)
  1 | aaaa | updated  | (0,5)
(3 rows)

INSERT 0 3
so=# INSERT INTO tab(id, col)
VALUES (3, 'c'), (4, 'd'), (1,'aaaa')
ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col
returning *,case when xmax::text::int > 0 then 'updated' else 'inserted' end,ctid;
 id | col  |  case   | ctid
----+------+---------+-------
  3 | c    | updated | (0,6)
  4 | d    | updated | (0,7)
  1 | aaaa | updated | (0,8)
(3 rows)

INSERT 0 3
person Vao Tsun    schedule 29.10.2017
comment
Вы можете просто использовать: `` ВОЗВРАЩЕНИЕ (xmax = 0) КАК вставлено '' - person Brendan Maguire; 13.11.2017
comment
Есть ли способ узнать, какие строки не были затронуты из-за предложения DO NOTHING? - person user; 18.05.2021

arrow_upward
9
arrow_downward

Исходя из ответа @ lad2025, результат может быть достигнут путем злоупотребления settings и настраиваемые параметры с связанных функций в предложениях WHERE, чтобы получить требуемый побочный эффект.

CREATE TABLE t(id INT PRIMARY KEY, v TEXT);

INSERT INTO t (id, v)
    SELECT $1, $2
    WHERE 'inserted' = set_config('upsert.action', 'inserted', true)
    ON CONFLICT (id) DO UPDATE
        SET v = EXCLUDED.v
        WHERE 'updated' = set_config('upsert.action', 'updated', true)
RETURNING current_setting('upsert.action') AS "upsert.action";

Третий параметр set_config - is_local: true означает, что настройка исчезнет в конце транзакции. Точнее, current_setting('upsert.action') вернет NULL (и не выдаст ошибку) до конца сеанса.

person Paul Guyot    schedule 13.01.2016
comment
Ой, это уродливо! Из-за этого мое зрение затуманивается. Я точно не хочу об этом помнить, не говоря уже о применении! ~ - person Patrick; 29.01.2016
comment
вы можете сделать это с xmax::text::int > 0 - без настройки уровня транзакции (что действительно является оригинальным взломом) - person Vao Tsun; 29.10.2017
comment
@VaoTsun прав. См. stackoverflow.com/a/38858662/454126, чтобы узнать, как использовать xmax для этой цели. Не очень красиво, но определенно красивее, чем set_config / current_setting. - person Julian Mehnle; 15.03.2018

arrow_upward
6
arrow_downward

В SQL Server MERGE есть оператор $action, который возвращает строку 'INSERT', 'UPDATE', or 'DELETE'.

Для Postgresql я не могу найти функцию / переменную, которая делает похожую вещь для RETURNING.

Один из способов решения этой проблемы - добавить в таблицу столбец is_updated:

DROP TABLE IF EXISTS tab;

CREATE TABLE tab(id INT PRIMARY KEY, col VARCHAR(100),
                 is_updated BOOLEAN DEFAULT false);
INSERT INTO tab(id, col) VALUES (1,'a'), (2, 'b');


-- main query
INSERT INTO tab(id, col)
VALUES (3, 'c'), (4, 'd'), (1,'aaaa')
ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col, is_updated = true
RETURNING id,col,
          CASE WHEN is_updated THEN 'UPDATED' ELSE 'INSERTED' END AS action;

Демонстрация Rextester

Вывод:

╔════╦══════╦══════════╗
║ id ║ col  ║  action  ║
╠════╬══════╬══════════╣
║  3 ║ c    ║ INSERTED ║
║  4 ║ d    ║ INSERTED ║
║  1 ║ aaaa ║ UPDATED  ║
╚════╩══════╩══════════╝
person Lukasz Szozda    schedule 13.01.2016
comment
Где VALUES (2, 'b') в выводе? - person kometen; 13.01.2016
comment
@kometen В этом нет необходимости. Он существовал раньше. Почему вы хотите вернуть всю таблицу? Только записи, которые вставлены / обновлены - person Lukasz Szozda; 13.01.2016
comment
Но также вставляется (2, 'b'). По умолчанию is_updated - false. Итак, я решил, что он будет отображаться как ВСТАВЛЕННЫЙ. - person kometen; 13.01.2016
comment
@kometen Дело в следующем: представьте, что у вас есть таблица с 2 миллионами записей. Теперь вы выполняете основной запрос. Возврат вернет только 3 записи (2 вставлены, 1 обновлена). Первая вставка предназначена для предварительного заполнения данных (для демонстрации). - person Lukasz Szozda; 13.01.2016
comment
Хорошо, это относится к конкретному INSERT. Очень полезно, спасибо. - person kometen; 13.01.2016

arrow_upward
2
arrow_downward

(xmax::text::bigint > 0) или (NOT xmax = 0). Преобразование типов в целое число будет прервано, когда счетчик транзакций достигнет целочисленного переполнения.

person Misha    schedule 03.09.2018