Подтвердить что ты не робот

Возврат pre-UPDATE Значения столбца с использованием только SQL - версия PostgreSQL

У меня есть связанный вопрос, но это еще одна часть моей головоломки.

Я хотел бы получить OLD VALUE из столбца из строки, которая была UPDATEd - БЕЗ использования триггеров (и хранимых процедур, а также каких-либо других дополнительных, не-SQL/-query-сущностей).

Запрос, который у меня есть, выглядит следующим образом:

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
    WHERE trans_nbr IN (
                        SELECT trans_nbr
                          FROM my_table
                         GROUP BY trans_nbr
                        HAVING COUNT(trans_nbr) > 1
                         LIMIT our_limit_to_have_single_process_grab
                       )
RETURNING row_id;

Если бы я мог сделать "ДЛЯ ОБНОВЛЕНИЯ НА my_table" в конце подзапроса, это было бы devine (и исправить мой другой вопрос/проблему). Но это не сработает: не может быть этого AND "GROUP BY" (что необходимо для вычисления COUNT of trans_nbr). Тогда я мог бы просто взять эти trans_nbr и сначала выполнить запрос, чтобы получить (скоро будущие) прежние значения process_by.

Я пробовал делать:

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
     FROM my_table old_my_table
     JOIN (
             SELECT trans_nbr
               FROM my_table
           GROUP BY trans_nbr
             HAVING COUNT(trans_nbr) > 1
              LIMIT our_limit_to_have_single_process_grab
          ) sub_my_table
       ON old_my_table.trans_nbr = sub_my_table.trans_nbr
    WHERE     my_table.trans_nbr = sub_my_table.trans_nbr
      AND my_table.processing_by = old_my_table.processing_by
RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by

Но это не сработает; old_my_table не отображается за пределами соединения; предложение RETURNING слепо.

Я давно потерял счет всех попыток, которые я сделал; Я изучаю это буквально в течение нескольких часов.

Если бы я мог найти пуленепробиваемый способ блокировки строк в моем подзапросе - и ТОЛЬКО эти строки, а КОГДА - в подзапрос - все проблемы concurrency, которые я пытаюсь избежать, исчезнут...

ОБНОВЛЕНИЕ: [WIPES EGG OFF FACE] Хорошо, поэтому у меня была опечатка в неэквивалентном коде выше, который я написал "не работает"; он делает... благодаря Эрвину Брандстетеру, ниже, кто заявил, что я это сделал (после ночного сна, освеженных глаз и банана для bfast). Поскольку мне потребовалось столько времени/трудно найти такое решение, возможно, мое смущение стоит того? По крайней мере, это сейчас на SO для потомков...: >

Теперь у меня есть (что работает):

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
     FROM my_table AS old_my_table
    WHERE trans_nbr IN (
                          SELECT trans_nbr
                            FROM my_table
                        GROUP BY trans_nbr
                          HAVING COUNT(*) > 1
                           LIMIT our_limit_to_have_single_process_grab
                       )
      AND my_table.row_id = old_my_table.row_id
RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by AS old_processing_by

COUNT (*) соответствует предложению Flimzy в комментарии к моему другому (связанному выше) вопросу. (Я был более конкретным, чем необходимо. [В этом случае.])

См. мой другой вопрос для правильной реализации concurrency и даже неблокирующей версии; Этот запрос просто показывает, как получить старые и новые значения из обновления, игнорировать неправильные/неправильные бит concurrency.

4b9b3361

ОТВЕТЫ

Ответ 1

Проблема

Руководство объясняет:

Необязательное предложение RETURNING вызывает UPDATE для вычисления и возврата значения на основе каждой обновленной строки. Любое выражение, использующее столбцы таблицы и/или столбцы других таблиц, упомянутых в FROM, могут вычисляться. Значения нового (пост-обновления) столбцов таблицы используется. Синтаксис списка RETURNING идентичен синтаксису выводить список SELECT.

Акцент мой. Нет способа получить доступ к старой строке в предложении RETURNING. Вы можете сделать это в триггере или с помощью отдельного SELECT до UPDATE, завернутого в транзакцию, как комментировали @Flimzy и @wildplasser, или завернули в CTE как отправленный @MattDiPasquale.

Решение

Однако то, что вы пытаетесь достичь , отлично работает, если вы присоединяетесь к другому экземпляру таблицы в предложении FROM:

UPDATE tbl x
SET    tbl_id = 23
     , name = 'New Guy'
FROM   tbl y                -- using the FROM clause
WHERE  x.tbl_id = y.tbl_id  -- must be unique
AND    x.tbl_id = 3
RETURNING y.tbl_id AS old_id, y.name AS old_name
        , x.tbl_id          , x.name;

Возврат:

 old_id | old_name | tbl_id |  name
--------+----------+--------+---------
  3     | Old Guy  | 23     | New Guy

SQL Fiddle.

Я тестировал это с версиями PostgreSQL с 8.4 до 9.6.

Он отличается для INSERT:

Работа с одновременной нагрузкой

Существует несколько способов избежать условий гонки с одновременными операциями записи. Простой, медленный и надежный (но дорогостоящий) метод заключается в том, чтобы выполнить транзакцию с уровнем изоляции SERIALIZABLE.

BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE ..;
COMMIT;

Но это, вероятно, перебор. И вам нужно быть готовым повторить операцию, если вы получите отказ в сериализации.
Упрощение и ускорение (и столь же надежное с одновременной загрузкой) - это явная блокировка для одной строки, подлежащей обновлению:

UPDATE tbl x
SET    tbl_id = 24
     , name = 'New Gal'
FROM  (SELECT tbl_id, name FROM tbl WHERE tbl_id = 4 FOR UPDATE) y 
WHERE  x.tbl_id = y.tbl_id
RETURNING y.tbl_id AS old_id, y.name AS old_name, x.tbl_id, x.name;

Дополнительные пояснения, примеры и ссылки по этому связанному вопросу:

Ответ 2

Вы можете использовать подзапрос SELECT.

Пример: Update пользовательское письмо RETURNING старое значение.

  • RETURNING Подзапрос

    UPDATE users SET email = '[email protected]' WHERE id = 1
RETURNING (SELECT email FROM users WHERE id = 1);

  • PostgreSQL с запросом (общие выражения таблицы)

    WITH u AS (
    SELECT email FROM users WHERE id = 1
)
UPDATE users SET email = '[email protected]' WHERE id = 1
RETURNING (SELECT email FROM u);

    Это работало несколько раз в моей локальной базе данных, но я не уверен, что SELECT in WITH гарантированно будет выполняться до UPDATE, поскольку "выполняются подзаголовки в WITH одновременно друг с другом и с основным запросом.

Ответ 3

Вариант CTE как предложенный @MattDiPasquale, тоже должен работать.
С удобными средствами CTE я бы стал более явным:

WITH sel AS (
   SELECT tbl_id, name FROM tbl WHERE tbl_id = 3  -- assuming unique tbl_id
   )
, upd AS (
   UPDATE tbl SET name = 'New Guy' WHERE tbl_id = 3
   RETURNING tbl_id, name
   )
SELECT s.tbl_id AS old_id, s.name As old_name
     , u.tbl_id, u.name
FROM   sel s, upd u;

Без тестирования я утверждаю, что это работает: SELECT и UPDATE см. тот же снимок базы данных. SELECT обязан вернуть старые значения (даже если вы поместите CTE после CTE с помощью UPDATE), а UPDATE возвращает новые значения по определению. Вуаля.

Но это будет медленнее, чем мой первый ответ.