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

Question

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

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

Я хотел бы получить СТАРОЕ ЗНАЧЕНИЕ столбца из строки, которая была ОБНОВЛЕНА - БЕЗ использования триггеров (ни хранимых процедур, ни каких-либо дополнительных, не-SQL/-запросных сущностей).

У меня запрос такой:

   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;

Если бы я мог сделать "FOR UPDATE ON my_table" в конце подзапроса, это было бы божественным (и исправил бы мой другой вопрос / проблему). Но это не сработает: не может иметь этого И "GROUP BY" (что необходимо для определения СЧЕТА trans_nbr's). Тогда я мог бы просто взять эти trans_nbr и сделать запрос первым, чтобы получить (скоро, чтобы быть) прежние значения processing_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 пункт слеп к этому.

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

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

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

То, что у меня сейчас есть (это работает), выглядит так:

   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 в комментарии к моему другому (связанному выше) вопросу. (Я был более конкретным, чем необходимо. [В данном случае.])

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

78

sql postgresql sql-update subquery

Источник

user902825 27 окт '11 в 22:34

4 ответа

Решение

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

Пример: обновить электронную почту пользователя RETURNING старое значение

RETURNING Subquery

UPDATE users SET email = 'new@gmail.com' 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 = 'new@gmail.com' WHERE id = 1
RETURNING (SELECT email FROM u);
```
Это работало несколько раз в моей локальной базе данных в обязательном порядке, но я не уверен, что SELECT в WITH гарантированно последовательно выполнять до UPDATE поскольку "под-операторы в WITH выполняются одновременно друг с другом и с основным запросом".

29

Источник

user242933 29 мар '14 в 17:00

Вариант 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 возвращает новые значения по определению. Вуаля.

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

14

Источник

user939860 29 мар '14 в 20:57

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

3

Источник

user471930 16 июл '18 в 01:36

Другие вопросы по тегам sql postgresql sql-update subquery

user939860 28 окт '11 в 10:16 2011-10-28 10:16 · Accepted Answer · 2011-10-28 10:16

проблема

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

Необязательный 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 NOT NULL
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:

INSERT INTO... FROM SELECT... ВОЗВРАТ

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

Есть несколько способов избежать состояния гонки с одновременными операциями записи. Простой, медленный и надежный (но дорогой) метод - выполнить транзакцию с 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;

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

АТОМНОЕ ОБНОВЛЕНИЕ.. ВЫБРАТЬ в Postgres